BLOB

using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace LobTest
{
   public partial class Form1 : Form
   {
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)//读取BLOB数据并写入文件模式
      {
         const int employeeIdColumn = 0;
         const int employeePhotoColumn = 1;
         //bufferSize must be bigger than oleOffset
         const int bufferSize = 100;
         byte[] buffer = new byte[bufferSize];
         int byteCountRead;
         long currentIndex = 0;

         ConnectionStringSettings nwSetting =
            ConfigurationManager.ConnectionStrings["NwString"];
         using (SqlConnection cn = new SqlConnection())
         {
            cn.ConnectionString = nwSetting.ConnectionString;
            cn.Open();

            using (SqlCommand cmd = cn.CreateCommand())
            {
               cmd.CommandText =
                  "SELECT EmployeeID, Photo FROM Employees";
               SqlDataReader rdr = cmd.ExecuteReader(
                  CommandBehavior.SequentialAccess);
               while (rdr.Read())
               {
                  int employeeId =
                     rdr.GetInt32(employeeIdColumn);
                  string fileName = @"c:\Employee"
                     + employeeId.ToString().PadLeft(2, '0')
                     + ".bin";

                  // Create a file to hold the output.
                  using (FileStream fs = new FileStream(
                     fileName, FileMode.OpenOrCreate,
                     FileAccess.Write))
                  {
                     currentIndex = 0;
                     byteCountRead =
                       (int)rdr.GetBytes(employeePhotoColumn,
                       currentIndex, buffer, 0, bufferSize);
                     while (byteCountRead != 0)
                     {
                        fs.Write(buffer, 0, byteCountRead);
                        currentIndex += byteCountRead;
                        byteCountRead =
                          (int)rdr.GetBytes(employeePhotoColumn,
                          currentIndex, buffer, 0, bufferSize);
                     }
                  }
               }
            }
         }
         MessageBox.Show("Done");
      }

       private void button2_Click(object sender, EventArgs e)//读取BLOB数据并写入文件模式删除OLE头信息
      {
         const int oleOffset = 78;
         const int oleTypeStart = 20;
         const int oleTypeLength = 12;
         const int employeeIdColumn = 0;
         const int employeePhotoColumn = 1;
         const int bufferSize = 100; //must be bigger than oleOffset
         byte[] buffer = new byte[bufferSize];
         int bufferStart = 0;
         int byteCountRead;
         long currentIndex = 0;

         ConnectionStringSettings nwSetting =
            ConfigurationManager.ConnectionStrings["NwString"];
         using (SqlConnection cn = new SqlConnection())
         {
            cn.ConnectionString = nwSetting.ConnectionString;
            cn.Open();

            using (SqlCommand cmd = cn.CreateCommand())
            {
               cmd.CommandText =
                  "SELECT EmployeeID, Photo FROM Employees";
               SqlDataReader rdr = cmd.ExecuteReader(
                  CommandBehavior.SequentialAccess);
               while (rdr.Read())
               {
                  int employeeId = rdr.GetInt32(employeeIdColumn);
                  string fileName = @"c:\Employee" +
                     employeeId.ToString().PadLeft(2, '0') + ".bmp";

                  // Create a file to hold the output.
                  using (FileStream fs = new FileStream(
                     fileName, FileMode.OpenOrCreate,
                     FileAccess.Write))
                  {
                     currentIndex = 0;
                     //read until we have the oleheader, if possible
                     while (currentIndex < oleOffset)
                     {
                        byteCountRead =
                           (int)rdr.GetBytes(employeePhotoColumn,
                           currentIndex, buffer, (int)currentIndex,
                           bufferSize - (int)currentIndex);
                        if (byteCountRead == 0) break;
                        currentIndex += byteCountRead;
                     }
                     byteCountRead = (int)currentIndex;

                     //process oleheader, if it exists
                     if (byteCountRead >= oleOffset)
                     {
                        string type = Encoding.ASCII.GetString(
                           buffer, oleTypeStart, oleTypeLength);
                        if (type == "Bitmap Image")
                        {
                           bufferStart = oleOffset;
                           byteCountRead = byteCountRead - oleOffset;
                        }
                     }

                     while (byteCountRead != 0)
                     {
                        fs.Write(buffer, bufferStart, byteCountRead);
                        bufferStart = 0;
                        byteCountRead =
                           (int)rdr.GetBytes(employeePhotoColumn,
                           currentIndex, buffer, 0, bufferSize);
                        currentIndex += byteCountRead;
                     }
                  }
               }
            }
         }
         MessageBox.Show("Done");
      }

      private void button3_Click(object sender, EventArgs e)//写如BLOB数据
      {
         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");
      }
   }
}