Read and Write Binary Large Object with ADO.NET

There are situations where you need to read a large object, such as a large image or text stored in database or you need to write such an object. You can do so by using ADO.NET connected classes. Here I use a SQL Server 2005 database and ASP.NET 3.5. I created a database named TestTable with 3 columns, TestID which is of type bigint, TestTitle which is of type nvarchar(50) and TestData which is of type Image. The latter is supposed to store the actual data.
To read data, I used SqlDataReader which normally reads data one row at a time. The columns are buffered if available. Then you can access them by using the columns index. To access large object, we need to change this default behavior of DataReader to a sequential stream. This way you need to go through the bytes in the stream until the end of the stream is reached. Here is the code:

string constr = ConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString;

        int bufferSize = 1024;

        byte[] buffer = new byte[bufferSize];

        long currentIndex = 0;

        int byteCount;

        using (SqlConnection conn = new SqlConnection(constr))

        {

            conn.Open();

            using (SqlCommand comm = conn.CreateCommand())

            {

                comm.CommandText = "select TestID, TestTitle, TestData from TestTable where TestID=1000";

                SqlDataReader reader = comm.ExecuteReader(CommandBehavior.SequentialAccess);

                while (reader.Read())

                {

                    string id = reader.GetInt64(0).ToString();

                    string title = reader.GetString(1);

                    using (FileStream fs = new FileStream(title + ".jpg",FileMode.Create,FileAccess.Write))

                    {

                        byteCount = (int)reader.GetBytes(2, currentIndex, buffer, 0, bufferSize);

                        while (byteCount!=0)

                        {

                            fs.Write(buffer, 0, byteCount);

                            currentIndex += byteCount;

                            byteCount = (int)reader.GetBytes(2, currentIndex, buffer, 0, bufferSize);

                        }

                    }

                }

            }

        }

This code selects a row and retrieves the columns and cycles through the bytes of the third column (TestData) and writes it into a buffer of bytes and eventually writes the content of the buffer into a FileStream Object. The result is an image file in the file system.

To write the BLOB object, you need a pointer to the column that actually stores the data. So you first get the pointer by TEXTPTR T-SQL command. The returned scalar value is used to update the column with the stream of the actual data. There is a loop in the code below that does the main job. It reads an image file on the hard disk and buffers its content and writes the buffer into the column on each iteration of the loop until the end of the file has been reached. Note that you have to provide a few paramters for the T-SQL that updates the column. A parameter holds the current index of data written into the column (offsetParam), one is the pointer that indicates the column to be updated (pointerParam) and is retreieved at the beginning of the code. The last one is the parameter that keep the actual data (dataParam).

string constr = ConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString;

        int bufferSize = 100;

        byte[] buffer = new byte[bufferSize];

        long currentIndex = 0;

        byte[] dataPtr;

        using (SqlConnection conn = new SqlConnection(constr))

        {

            conn.Open();

            using (SqlCommand comm = conn.CreateCommand())

            {

                comm.CommandText = "select textptr(TestData) from TestTable where TestID=1000";

                dataPtr = (byte[])comm.ExecuteScalar();

            }

            using (SqlCommand comm2 = conn.CreateCommand())

            {

                comm2.CommandText = "updatetext TestTable.TestData @pointer @offset null @data";

                SqlParameter pointerParam = comm2.Parameters.Add("@pointer", SqlDbType.Binary, 16);

                pointerParam.Value = dataPtr;

                SqlParameter dataParam = comm2.Parameters.Add("@data", SqlDbType.Image);

                SqlParameter offsetParam = comm2.Parameters.Add("@offset", SqlDbType.Int);

                offsetParam.Value = 0;

                using (FileStream fs = new FileStream("C:\\image.jpg", FileMode.Open, FileAccess.Read))

                {

                    int count = fs.Read(buffer, 0, bufferSize);

                    while (count != 0)

                    {

                        dataParam.Value = buffer;

                        dataParam.Size = count;

                        comm2.ExecuteNonQuery();

                        currentIndex += count;

                        offsetParam.Value = currentIndex;

                        count = fs.Read(buffer, 0, bufferSize);

                    }

                }

            }

        }

How to use a user control within a GridView control

Recently, I have been working on a web application where I have to display a list of users to the administrator. I took advantage of LinqDataSource and GridView control. The database record contained a DateTime field. Dealing with this field made me think of a simple application. I thought it would be a good sample on how to use Linq to SQL, how to truncate DateTime field and show only the date part by adding properties to the Linq generated class and how to use your own web user controls within a GridView. Needless to mention that the code I used is not optimized and these things may be done in more efficient ways. I just want to share what I experienced.

I used ASP.NET 3.5 and SQL Server 2005 for this sample code. First I create a database called TestDB containing a single table called tbMember. The table contains three columns, MemberID which is of type bigint with autoincrememnt, MemberName of type nvarchar(50) and MemberBirthday of type DateTime.

In my sample application, I add a new item of type LINQ to SQL classes and I name it MemberDataClasses. Then I use Server Explorer to browse the database I just created and drag the table over to the designer surface of the LINQ to SQL class. This will generate the code automatically and now on I can simply use LinqDataSource to display the data in any data-bound control. It couldn't be any easier!

In my sample page, I add a GridView control and in 'Choose Data Source', I select 'New data source'. A wizard pops up and I choose Linq data source type. The next wizard shows which lets me configure the data source. If  'Show only DataContext objects' checkbox is checked, the classes within the application that implements DataContext will be displayed in the drop-down list. The class that I created using LINQ to SQL is shown and I choose it. Then I can select the columns I want to display. If I select *, Advanced option will be enabled where I can enable the data source to perform automatic deletes, inserts and updates. If these options are selected on the data source, I can enable editing and deleting on the GridView control by using the smart tag and checking the appropriate checkbox.

At this moment, you can easily edit the records without even writing a single line of code. Suppose that you want to display only the date part of the DateTime field (which is a property in the Linq generated class). Simply you can add a string property to the Linq generated class which returns a short version of the DateTime column. You could use ToShortDateString method. I set the allow null on the DateTime column so that the generated property would be Nullable. Here is the property I added to return a short date.

    public string ShortMemberBirthday

    {

        get

        {

            if (MemberBirthday.HasValue)

            {

                return MemberBirthday.Value.ToShortDateString();

            }

            else

            {

                return string.Empty;

            }

        }

        set

        {

            if (value == null || value == string.Empty)

            {

                MemberBirthday = (DateTime?)null;

            }

            else

            {

                MemberBirthday = Convert.ToDateTime(value);

            }

        }

    }

I did some checks to handle null values. Now you can edit the BoundField column of the GridView control that displays the DateTime property. Instead of the original property, you can set its DataField attribute to the new property to show the short date. So far, your GridView and data source should look something like this:

<asp:GridView ID="MemberGridView" runat="server"

            DataSourceID="MemberDataSource" AllowSorting="True"

            AutoGenerateColumns="False" DataKeyNames="MemberID">

            <Columns>

                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

                <asp:BoundField DataField="MemberID" HeaderText="ID"

                    InsertVisible="False" ReadOnly="True" SortExpression="MemberID" />

                <asp:BoundField DataField="MemberName" HeaderText="Name"

                    SortExpression="MemberName" />

                <asp:BoundField DataField="ShortMemberBirthday" HeaderText="Birthday"

                    SortExpression="MemberBirthday" />

            </Columns>

            <EmptyDataTemplate>

            There are no records to display!

            </EmptyDataTemplate>

        </asp:GridView>

        <asp:LinqDataSource ID="MemberDataSource" runat="server"

            ContextTypeName="MemberDataClassesDataContext" EnableDelete="True"

            EnableInsert="True" EnableUpdate="True" TableName="tbMembers">

        </asp:LinqDataSource>

The DataField of the BoundField was originally set to MemberBirthday and I changed it to ShortMemberBirthday.

Now I'm going to point out another issue you may come across dealing with DateTime. When using the default behavior of GridView, you will notice that a Label control is used to display fields in normal mode and a TextBox control to display fields in edit mode. Since the web server and the database servers we use might be geographically in different place, this will be potentially troublesome. Each server uses its own culture settings which means when you enter a DateTime value in the TextBox in edit mode, it could be interpreted in different ways depending on the CultureInfo defined on the server. If the CultureInfo object is set to "en-GB", the date-time format will be DD/MM/YYYY, while if it is set to "en-US", the format is expected to be MM/DD/YYYY. Sometimes it's difficult to handle every single situation where a DateTime value entry is expected. To avoid the headache, I thought of a web user control that simply contains three DropDownList controls for displaying day, month and year regardless of the CultureInfo information. Instead of the default TextBox that is displayed in the edit mode of the GridView control, we could use this user control. Here is my control:

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DateControl.ascx.cs" Inherits="uc_DateControl" %>

<table border="0" cellpadding="0" cellspacing="0" style="font-size: 11px">

    <tr>

        <td>

            <asp:DropDownList EnableViewState="false" ID="DayList" runat="server" Font-Size="11px"

                Width="36px">

                <asp:ListItem Selected="True"></asp:ListItem>

                <asp:ListItem Value="1">1</asp:ListItem>

                <asp:ListItem Value="2">2</asp:ListItem>

                <asp:ListItem Value="3">3</asp:ListItem>

                <asp:ListItem Value="4">4</asp:ListItem>

                <asp:ListItem Value="5">5</asp:ListItem>

                <asp:ListItem Value="6">6</asp:ListItem>

                <asp:ListItem Value="7">7</asp:ListItem>

                <asp:ListItem Value="8">8</asp:ListItem>

                <asp:ListItem Value="9">9</asp:ListItem>

                <asp:ListItem Value="10">10</asp:ListItem>

                <asp:ListItem Value="11">11</asp:ListItem>

                <asp:ListItem Value="12">12</asp:ListItem>

                <asp:ListItem Value="13">13</asp:ListItem>

                <asp:ListItem Value="14">14</asp:ListItem>

                <asp:ListItem Value="15">15</asp:ListItem>

                <asp:ListItem Value="16">16</asp:ListItem>

                <asp:ListItem Value="17">17</asp:ListItem>

                <asp:ListItem Value="18">18</asp:ListItem>

                <asp:ListItem Value="19">19</asp:ListItem>

                <asp:ListItem Value="20">20</asp:ListItem>

                <asp:ListItem Value="21">21</asp:ListItem>

                <asp:ListItem Value="22">22</asp:ListItem>

                <asp:ListItem Value="23">23</asp:ListItem>

                <asp:ListItem Value="24">24</asp:ListItem>

                <asp:ListItem Value="25">25</asp:ListItem>

                <asp:ListItem Value="26">26</asp:ListItem>

                <asp:ListItem Value="27">27</asp:ListItem>

                <asp:ListItem Value="28">28</asp:ListItem>

                <asp:ListItem Value="29">29</asp:ListItem>

                <asp:ListItem Value="30">30</asp:ListItem>

                <asp:ListItem Value="31">31</asp:ListItem>

            </asp:DropDownList>

        </td>

        <td>

            &nbsp;

        </td>

        <td>

            <asp:DropDownList EnableViewState="false" ID="MonthList" runat="server" Font-Size="11px"

                Width="42px">

                <asp:ListItem Selected="True"></asp:ListItem>

                <asp:ListItem Value="1">Jan</asp:ListItem>

                <asp:ListItem Value="2">Feb</asp:ListItem>

                <asp:ListItem Value="3">Mar</asp:ListItem>

                <asp:ListItem Value="4">Apr</asp:ListItem>

                <asp:ListItem Value="5">May</asp:ListItem>

                <asp:ListItem Value="6">Jun</asp:ListItem>

                <asp:ListItem Value="7">Jul</asp:ListItem>

                <asp:ListItem Value="8">Aug</asp:ListItem>

                <asp:ListItem Value="9">Sep</asp:ListItem>

                <asp:ListItem Value="10">Oct</asp:ListItem>

                <asp:ListItem Value="11">Nov</asp:ListItem>

                <asp:ListItem Value="12">Dec</asp:ListItem>

            </asp:DropDownList>

        </td>

        <td>

            &nbsp;

        </td>

        <td>

            <asp:DropDownList EnableViewState="false" ID="YearList" runat="server" Font-Size="11px"

                Height="16px" Width="50px">

                            </asp:DropDownList>

                <asp:ListItem Selected="True"></asp:ListItem>

                <asp:ListItem Value="1967">1967</asp:ListItem>

                <asp:ListItem Value="1968">1968</asp:ListItem>

                <asp:ListItem Value="1969">1969</asp:ListItem>

                <asp:ListItem Value="1970">1970</asp:ListItem>

                <asp:ListItem Value="1971">1971</asp:ListItem>

                <asp:ListItem Value="1972">1972</asp:ListItem>

                <asp:ListItem Value="1973">1973</asp:ListItem>

                <asp:ListItem Value="1974">1974</asp:ListItem>

                <asp:ListItem Value="1975">1975</asp:ListItem>

                <asp:ListItem Value="1976">1976</asp:ListItem>

                <asp:ListItem Value="1977">1977</asp:ListItem>

        </td>

    </tr>

</table>

Notice that I could add the days to the list in Load event of the control, but I prefered to do it declaratively. This will save me from having to set EnableViewState to true. As I explained in my post about ViewState, every programmatic change to controls needs to be persisted in view state.

In the code behind of your control class, you need a property that gets and sets the date that the control displays. I called it SelectedDate and I let it be Nullable because it will let us be more flexible with the database fields. Here is the property code:

public DateTime? SelectedDate

    {

        get

        {

            if (YearList.SelectedIndex == 0 || MonthList.SelectedIndex == 0 || DayList.SelectedIndex == 0)

            {

                return (DateTime?)null;

            }

            int year = Convert.ToInt32(YearList.SelectedValue);

            int month = Convert.ToInt32(MonthList.SelectedValue);

            int day = Convert.ToInt32(DayList.SelectedValue);

            try

            {

                DateTime test = new DateTime(year, month, day);

            }

            catch (Exception exc)

            {

                if (exc is ArgumentOutOfRangeException)

                {

                    return GetValidDate(year, month, day - 1);

                }

            }

            return new DateTime(year, month, day);

        }

        set

        {

            if (!value.HasValue)

            {

                return;

            }

            int year = value.Value.Year;

            int month = value.Value.Month;

            int day = value.Value.Day;

            YearList.SelectedValue = year.ToString();

            MonthList.SelectedValue = month.ToString();

            DayList.SelectedValue = day.ToString();

        }

    }

    DateTime GetValidDate(int year, int month, int day)

    {

        try

        {

            DateTime test = new DateTime(year, month, day);

        }

        catch (Exception exc)

        {

            if (exc is ArgumentOutOfRangeException)

            {

                return GetDateValid(year, month, day - 1);

            }

        }

        return new DateTime(year, month, day);

    }

Here I also did a little check if the selected date is valid by using GetValidDate method. At this level, I select the DateTime column and use 'Convert this field into a TemplateField'. In the design mode, I replace the TextBox control with my user control. There is a problem if you want to use the new property in edit mode in the GridView control. Since this property is of type string, there will be a casting exception. For example, if your  template field is like this in the GridView:

<asp:TemplateField HeaderText="Birthday" SortExpression="MemberBirthday">

                    <EditItemTemplate>

                        <uc1:DateControl ID="DateControl1" runat="server" SelectedDate='<%# Bind("ShortMemberBirthday")%>' />

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("ShortMemberBirthday") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

you are going to get an error message saying 'Specified cast is not valid'. This means SelectedDate property is expecting a DateTime value, while I passed in a string value. So in normal mode, you can use that string to set the Label control's Text property. But in edit mode, you will need to use the first property which is MemberBirthday. So the template field should be like this:

<asp:TemplateField HeaderText="Birthday" SortExpression="MemberBirthday">

                    <EditItemTemplate>

                        <uc1:DateControl ID="DateControl1" runat="server" SelectedDate='<%# Bind("MemberBirthday")%>' />

                    </EditItemTemplate>

                    <ItemTemplate>

                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("ShortMemberBirthday") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

Now if you run the page, in normal mode, you will see the short DateTime values in MemberBirthday column and in edit mode, you will see the three DropDownList controls with the appropriate values selected.

My GridView control looks like this in normal mode:

and this is what it looks like in edit mode: