Managing LOB Parameters

Before SQL Server 2005 and the introduction of LOB data types (varbinary (max), varchar (max), and Nvarchar (max)), it was very inconvenient to work with BLOBs. The only real way to operate with them was through ADO.NET. Now, with the new LOB data types, it is easier to manipulate BLOBs from Transact-SQL. However, I will show you how to work with them in a CLR procedure instead.

In the following example, a SqlDataReader object is used to get a photo, and then FileStream and BinaryWriter objects are used to save the photo in a specified file:

 [SqlProcedure] public static void cp_EqImage_Save(int EqID, string FileName)  {       //save vabinary(max) returned by stored procedure to the file       const int bufferSize = 8000; // The BLOB byte[] buffer to be filled by GetBytes. byte[] outbyte = new byte[bufferSize]; using (SqlConnection conn =     new SqlConnection("Context Connection=true")) {     conn.Open();     SqlCommand command = conn.CreateCommand();      // Set up the command to execute the stored procedure,      command.CommandText = "dbo.ap_Eq!mage_List";      command.CommandType = CommandType.StoredProcedure;     // Set up the input parameter.      SqlParameter paramID =          new SqlParameter("OEqID", SqlDbType.Int);      paramID.Value = EqID;      command.Parameters.Add(paramID);      // Execute the stored procedure,      command.ExecuteNonQuery();      using (SqlDataReader reader =          command.ExecuteReader(CommandBehavior.SequentialAccess))      {          while (reader.Read())      {              //SqlContext.Pipe.Send("reader read");              //define the file (stream)              using (FileStream fileStream =              new FileStream(FileName,                             FileMode.OpenOrCreate,                             FileAccess.Write))      {             //SqlContext.Pipe.Send("file stream created.");             //Define a writer that will stream BLOB             //to the file (FileStream).            using (BinaryWriter binaryWriter =                 new BinaryWriter(fileStream))      {             //SqlContext.Pipe.Send("BinaryWriter created"             //The starting position in the BLOB output,             long startIndex = 0;             //Read the bytes into outbyte [] array.             //Get number of bytes that were read,             long retval = reader.GetBytes(0,             startIndex, outbyte, 0, bufferSize);            //SqlContext.Pipe.Send("outbyte filled."};            //While number of bytes read is equal            // to size of buffer,            //continue reading            while (retval == bufferSize)            {                 //write to stream                 binaryWriter.Write(outbyte);                 //SqlContext.Pipe.Send("outbyte written.");                //write to file                binaryWriter.Flush();                //SqlContext.Pipe.Send("writer flushed.") ;                // Reposition the start index                startIndex += bufferSize;               //Read number of records again               //and fill the buffer,               retval = reader.GetBytes(                   0, startIndex, outbyte, 0, bufferSize);             }           //SqlContext.Pipe.Send("outbyte last time.");           // Write the remainder to the file           binaryWriter.Write(outbyte) ;           //write to file binaryWriter.Flush();           //SqlContext.Pipe.Send("flushed last time.");         }       }     }   } return;  } } 

Naturally, in order to compile this procedure, you must reference the System.IO namespace in the Using statement at the top of the file.

The stored procedure requires you to set Permission Set to External_Resources. The "Assembly External Access" section in Chapter 11 shows how to do it in Transact-SQL. If you are performing this operation in Visual Studio, you need to open Project | <project name> Properties, switch to the Database tab, and set Permission Level to External (see Figure 12-2).

Figure 12-2: Setting the permission level in Visual Studio 2005

Tip 

If you have a problem with deployment, you probably didn't set the database to Trustworthy On and you didn't give External Access Assembly permission to the database owner (as described earlier).

When the project is successfully deployed, you can test the stored procedure from Management Studio:

 Use Asset5 GO DECLARE @EqID int DECLARE @FileName nvarchar(4000) Select  @EqId = Min(EqId) from Eq where EqImage is not null Set @FileName = 'c:\download\Eq' + cast(@EqId as varchar(BO)) + '.jpg' EXECUTE  [Asset5].[dbo].[cp_EqImage_Save]          @EqID         ,@FileName 

The opposite operation is implemented by the following CLR stored procedure. cp_EqImage_Update uses the FileStream and BinaryReader objects to read the contents of the file into an array of bytes. The array is then assigned to the value of a SqlParameter. It is used (along with the other parameter) to execute a SqlCommand object and update the specified equipment image.

 [SqlProcedure] public static void cp_EqImage_Update(int EqID, string FileName) {     //read image from the file and put it in varbinary(max)     //define the file (stream)     using (FileStream fileStream =     new FileStream(FileName,                    FileMode.Open,                    FileAccess.Read))     {        //SqlContext.Pipe.Send("file stream created.");        //Define a writer that will stream BLOB        //to the file (FileStream).        using (BinaryReader binaryReader =        new BinaryReader(fileStream))      {          //SqlContext.Pipe.Send("BinaryWriter created");          byte[] blob=binaryReader.ReadBytes((int)fileStream.Length);          using (SqlConnection conn =              new SqlConnection("Context Connection=true"))      {        conn.Open();        SqlCommand command = conn.CreateCommand();        // Set up the command to execute the stored procedure,        command.CommandText = "ap_EqImage_Update";        command.CommandType = CommandType.StoredProcedure;       // Set up the input parameter.       SqlParameter paramID =            new SqlParameter("@EqID", SqlDbType.Int);       paramID.Value = EqID; command.Parameters.Add(paramID);      // Set up the output parameter to retrieve the image.      SqlParameter paramEqImage =          new SqlParameter("@EqImage",SqlDbType.VarBinary,-1);      paramEqImage.Value = blob;      command.Parameters.Add(paramEqImage);      // Execute the stored procedure, command.ExecuteNonQuery();       }     }   }   return; } 

As in the case of the previous C3 example, you must add a reference to the System.IO namespace in the Using statement at the top of your file.