DCSIMG
Working with large objects - Wortzel's blog

Wortzel's blog

.Net (2.0, 3.0, 3.5), C#, Asp.net, Com+, GIS(ESRI Software), Management, Analysis & Design, Life, Trips, And more...
Working with large objects

In SQL server 2005 you have some options to work with large objects. If the data is binary you should use the Binary Large Object (BLOB), and if the data is textual you should use the Character Large Object (CLOB). The BLOB objects and the CLOB objects have a capacity of 2^127 bytes.

When you are dealing with a large object you should ask yourself: Isn’t this object to big to load it from the memory? If the object is too big you should consider using a streaming method. When using a streaming method, the data is being load to the stream by a helper buffer until all the data have arrived (notice: you can use the data only after all the bytes have arrived).

 

This is an example of using the streaming method:

protected void LoadBlobData()

{

int byteCount;

      long currentIndex;

      int fileNameColumn = 0;

      int dataColumn = 1;

      int bufferSize = 1000;

      byte[] buffer = new byte[bufferSize];

      ConnectionStringSettings connSetting = ConfigurationManager.

ConnectionStrings["MyConnectionString"];

      using (SqlConnection cn = new SqlConnection())

      {

            cn.ConnectionString = connSetting.ConnectionString;

            cn.Open();

            using (SqlCommand command = new SqlCommand())

            {

                command.CommandText = "Select file_name,data from

                  files";

                SqlDataReader reader =

                  command.ExecuteReader

                  (CommandBehavior.SequentialAccess);

                while (reader.Read())

                {

                    string fileName = @"\\MyDirectory\" +

reader.GetString(fileNameColumn);

                    using (FileStream fs = new FileStream(fileName,

                        FileMode.OpenOrCreate, FileAccess.Write))

                    {

                        currentIndex = 0;

                        byteCount = (int)reader.GetBytes(dataColumn,

currentIndex, buffer, 0, bufferSize);

                        while (byteCount != 0)

                        {

                            fs.Write(buffer, 0, byteCount);

                            currentIndex += byteCount;

                            byteCount =

                              (int)reader.GetBytes(dataColumn,

currentIndex, buffer, 0, bufferSize);

                        }

                    }

                }

            }

      }

}    

 

Published Monday, May 21, 2007 11:17 PM by Avi Wortzel

תגים:, ,

Comments

# re: Working with large objects@ Monday, July 09, 2007 11:28 PM

It's works very well for reading data. How about to write data to database. I want to see how to write many small chunk of data to SqlServer2005 with ADO.net

jdang

John Dang

# re: Working with large objects@ Tuesday, July 10, 2007 11:04 AM

Hi John,

I found an example to update a photo in Sql-server db(from "Programming Microsoft ADO.NET 2.0 applications advanced topics" book).

I hope it will help you.

Have a nice day,

Avi.

private void button3_Click(object sender, EventArgs e)

{

  const int bufferSize = 100;

  byte[] buffer = new byte[bufferSize];

  long currentIndex = 0;

  byte[] photoPtr;

  ConnectionStringSettings nwSetting =

     ConfigurationManager.ConnectionStrings["NwString"];

  using (SqlConnection cn = new SqlConnection())

  {

     cn.ConnectionString = nwSetting.ConnectionString;

     cn.Open();

     using (SqlCommand cmd = cn.CreateCommand())

     {

        cmd.CommandText =

           "SELECT TEXTPTR(Photo) FROM Employees WHERE EmployeeID = 1";

        photoPtr = (byte[])cmd.ExecuteScalar();

     }

     using (SqlCommand cmd = cn.CreateCommand())

     {

        cmd.CommandText =

           "UPDATETEXT Employees.Photo @Pointer @Offset null @Data";

        SqlParameter ptrParm =

           cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);

        ptrParm.Value = photoPtr;

        SqlParameter photoParm =

           cmd.Parameters.Add("@Data", SqlDbType.Image);

        SqlParameter offsetParm =

           cmd.Parameters.Add("@Offset", SqlDbType.Int);

        offsetParm.Value = 0;

        using (FileStream fs = new FileStream("Girl.gif",

           FileMode.Open, FileAccess.Read))

        {

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

           while (count != 0)

           {

              photoParm.Value = buffer;

              photoParm.Size = count;

              cmd.ExecuteNonQuery();

              currentIndex += count;

              offsetParm.Value = currentIndex;

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

           }

        }

     }

  }

  MessageBox.Show("Done");

}

Avi Wortzel

# re: Working with large objects@ Wednesday, July 11, 2007 12:08 AM

Hi Avi,

Thanks for your help. However, I got this error on this code segment:

sql = "Select TEXTPTR(data) From BinaryData Where fileName=@fileName";

cmd.CommandText = sql;

// Get reference to  Blob field

blobPtr = (byte[])cmd.ExecuteScalar();

Error: Argument data type varbinary(max) is invalid for argument 1 of textptr function.

Do you have any idea how to fix this problem?

Thanks

John Dang

John Dang

# re: Working with large objects@ Friday, July 13, 2007 4:52 PM

I think, I found a better solution for SqlServer 2005. the technique you show me in this website may works for SqlServer 2000 not for 2005. For new data types in SqlServer 2005 such as varBinary(MAX), varchar(MAX) and nVarchar(MAX), they contain write method.

sql = String.Concat("Update BinaryData Set Data.Write(@data, len(data), @length)",

"Where fileName=@fileName");

JDang

John Dang

# re: Working with large objects@ Thursday, July 19, 2007 12:41 AM

Hi,

You are right (Although that the TEXTPTR is supported in SqlServer 2005, it isn't the best practice to use).

The using of the Update statement instead (as you mention) is better.

Let me know if you have any more questions about it.

Avi.

Avi Wortzel

# re: Working with large objects@ Thursday, July 26, 2007 4:15 PM

Is TEXTPTR is supported in SqlServer 2005? I have tried to use TEXTPTR and it alway throws

Error: Argument data type varbinary(max) is invalid for argument 1 of textptr function.

JDang

John Dang

# re: Working with large objects@ Thursday, July 26, 2007 6:51 PM

Try to look in this MSDN article:

http://msdn2.microsoft.com/en-us/library/ms176068.aspx

SQL Server 2005 Books Online

TEXTPTR (Transact-SQL)

Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.

Important:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Alternative functionality is not available.

Avi Wortzel