SqlDataReader Contd.. GetOrdinal(), IsDBNull()

In a previous post "ADO.Net - SqlDataReader" I have given an intro to the SQL Data Reader. Here will go through some usages in the data reader. In the Below code I use some built in methods to iterate through the items returned by the reader.

            using (var connection = new SqlConnection(@"Password=****;Persist Security Info=True;User ID=**;Initial Catalog=****;Data Source=****;Min Pool Size=100;"))
            {
                var command = new SqlCommand("SELECT contact_fax, firstname, lastname, phone from employess where empid = 'E1290'", connection);
             
                connection.Open();
                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    // display middle name only if not null
                    if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                        Console.Write(" {0}", reader.GetString(reader.GetOrdinal("firstname")));
                }
                connection.Close();
            }

Here, the IsDBNull() method is used to check if the column contains null's. It accepts an integer and returns true or false based on the column value availability.

The GetOrdinal() method returns the position of the column in the table retrieved by the data reader. Columns are numbered starting from zero. As the firstname column is the second column specified in my select statement, the method returns 1.

If the column is not available this method fires a IndexOutOfRangeException.

And now you combine these two methods together. Specify the column name in the GetOrdinal(). If the column contains a non-null value the condition becomes true, control goes in and prints the column value.

Add New Entry to Host File

Go to C:\Windows\System32\drivers\etc

1. Edit the host file
2. Add the new entry in the following format

    <IP Address> <URL>
    Eg:
    172.11.11.11 abc.shaamil.lk

3. Save and close the file

4. In the Command prompt type in ipconfig /flushdns
You will get a message as "Successfully flushed the DNS Resolver Cache."

Done.. :)

If you are unable to edit the file in the given location, copy the file somewhere else do the changes and paste it back to the original location.

T SQL - Playing around with Image Type ( Create, Insert, Select )

This post briefly describes about creating tables containing image, storing and retrieving images in SQL Server

1) Create a table containing image type

CREATE TABLE dbo.MyImages

(ImageId    INT IDENTITY(1,1) NOT NULL,

 uid             BIGINT NULL, 

 picture       IMAGE NULL

 PRIMARY KEY(ImageId ))

2) Insert an image into the DataBase table. 

INSERT INTO MyImages(picture)

SELECT * FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

OR you can go with the full row insert as:

INSERT INTO MyImages(uid, picture)

SELECT 2000, PicColumn FROM OPENROWSET(BULK N'C:\images\emp001.png', SINGLE_BLOB) AS myrow

Here, you need to specify the path to the image in the drive, for it to be captured by the insert.

3) Retrieving for display

It's best that you have a handler for retrieving images from DB. For an example could use a generic handler (.ashx file).

3.1
In the handler's (.ashx file's) ProcessRequest() method set

        context.Response.Clear();
        context.Response.ContentType = "image/jpeg";

3.2
In the ashx file create a method to retrieve image from DB. The query string would be straight forward.

Eg: "SELECT uid, picture FROM dbo.MyImages WHERE" + uid

Capture the resulting row may be in a DataTable.

3.3
get the image into a byte array

byte[] imageByteArray=null;
imageByteArray=(byte[])sampleDataTable.Rows[0][1];

3.4
Include the byte[] to  memory stream, and set it for output

MemoryStream memStream = new MemoryStream(imageByteArray, false);
System.Drawing.Image img = System.Drawing.Image.FromStream(memStream);
img .Save(context.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Png);

3.5
In the aspx page's calling the method ( Eg: inside an item data bound method ).
Set the image url by pointing to the handler

myImage.ImageUrl = "/pathtothe ashpage/sample.ashx?uid=" + uid;

Custom Field Property Display using CAML - SharePoint 2007

SharePoint 2007 Custom field rendering at List Item View is done through the CAML code. In the custom field definition XML file, there is a section where display is defined... and that is within the  <RenderPattern>. Usually you can access the filed properties in the filed through simple CAML code. For an instance:

<RenderPattern Name="DisplayPattern">
  <HTML><![CDATA[</div><br/><br/><div>Name Value:</div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Name" HTMLEncode="True" />
  </ScriptQuote>

  <HTML><![CDATA[<div>Custom Property 1 Value</div><br/>]]></HTML>
  <ScriptQuote>  
      <Property Select="MyCustomProperty1" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Custom Property 2 Value</div><br/>]]></HTML>
  <ScriptQuote>
      <Property Select="MyCustomProperty2" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Description : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Description" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Required : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Required" HTMLEncode="True" />
  </ScriptQuote>
  <Column HTMLEncode="TRUE"/>

</RenderPattern>

The code would give you the property values of the Description, the required field value and Name value. But.....not the values of the custom properties in your field......

It is possible to get the custom property values and display at DisplayPattern. For this it's required that the property schema's being updated during the field's onAdd() and onUpdate() methods. Solution s to override the two methods.

Then the custom property values become accessible.

A detailed explanation is available at this Link.