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 == 0break;
                        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");
      }

   }

}
posted @ 2007-08-25 11:13  子午  阅读(291)  评论(0编辑  收藏  举报