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);

                    }

                }

            }

        }