如何读取和写入文件或 BLOB 列使用 ADO.NET 和 Visual C#.NET

读取 BLOB 列

下面的代码示例中的函数使用DataReader类来检索 BLOB 值并将 BLOB 值分配给一个字节数组。因为 BLOB 已经完全在内存中，不需要进行分块，数据和 BLOB 分配到一个字节数组。

有两次调用GetBytes方法：

• 第一次调用获取 BLOB 以字节为单位的长度，并用于分配的字节数组。
• 第二个调用检索的数据。文件流对象用于向磁盘中写入的字节数组。

public void SqlBlob2File(string DestFilePath)
{
   try
   {
	int PictureCol = 0; // the column # of the BLOB field
	SqlConnection cn = new SqlConnection("server=localhost;integrated security=yes;database=NorthWind");
	SqlCommand cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", cn);
	cn.Open();
	SqlDataReader dr  = cmd.ExecuteReader();
	dr.Read();
	Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
	dr.GetBytes(PictureCol, 0, b, 0, b.Length);
	dr.Close();
	cn.Close();
	System.IO.FileStream fs = 
     new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);

	fs.Write(b, 0, b.Length);
	fs.Close();
	MessageBox.Show("Image written to file successfully");
   }
   catch(SqlException ex)
   {
   MessageBox.Show (ex.Message);
   }   
}

public void OleDbBlob2File(string DestFilePath)
{
   try
   {
	int PictureCol = 0; // the column # of the BLOB field
	OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=localhost;" + 
     "user id=uid;password=password;database=NorthWind");

	OleDbCommand cmd = new OleDbCommand("SELECT Picture FROM Categories " + 
     "WHERE CategoryName='Test'", cn);

	cn.Open();
	OleDbDataReader dr  = cmd.ExecuteReader();
	dr.Read();
	Byte[] b = new Byte[(dr.GetBytes(PictureCol, 0, null, 0, int.MaxValue))];
	dr.GetBytes(PictureCol, 0, b, 0, b.Length);
	dr.Close();
	cn.Close();
	System.IO.FileStream fs = 
     new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);

	fs.Write(b, 0, b.Length);
	fs.Close();
	MessageBox.Show("Image written to file successfully");
   }
   catch(OleDbException ex)
   {
   MessageBox.Show (ex.Message);
   }
}
	

写入 BLOB 列

下面的代码示例中的函数使用命令对象和参数的对象，将字节数组中的数据写入 BLOB 列。以下方法完全读取到内存中的文件。因此，这种技术不必在数据写入服务器时，将数据分块。字节数组，其长度参数传递给该参数的构造函数。

private void File2SqlBlob(string SourceFilePath)
{
   try
   {
	SqlConnection cn = new SqlConnection("server=localhost;integrated security=yes;database=NorthWind");
	SqlCommand cmd = new SqlCommand("UPDATE Categories SET Picture=@Picture " + 
     "WHERE CategoryName='Test'", cn);

	System.IO.FileStream fs = 
new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

	Byte[] b = new Byte[fs.Length];
	fs.Read(b, 0, b.Length);
	fs.Close();
	SqlParameter P = new SqlParameter("@Picture", SqlDbType.VarBinary, b.Length, 
     ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, b);
	cmd.Parameters.Add(P);
	cn.Open();
	if (cmd.ExecuteNonQuery() == 1)
		MessageBox.Show("Your images stored successfully");
	cn.Close();
   }
   catch(SqlException ex)
   {
   MessageBox.Show (ex.Message);
   }
}

public void File2OleDbBlob(string SourceFilePath)
{	
   try
   {	
	OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=localhost;" + 
     "user id=uid;password=password;initial catalog=NorthWind");

	OleDbCommand cmd = new OleDbCommand("UPDATE Categories SET Picture=? WHERE CategoryName='Test'", cn);
	System.IO.FileStream fs = 
					new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

	Byte[] b = new Byte[fs.Length];
 fs.Read(b, 0, b.Length);
	fs.Close();
	OleDbParameter P = new OleDbParameter("@Picture", OleDbType.VarBinary, b.Length, 
     ParameterDirection.Input, false, 0, 0, null,DataRowVersion.Current, b);

	cmd.Parameters.Add(P);
	cn.Open();
	if (cmd.ExecuteNonQuery() == 1)
		MessageBox.Show("Your images stored successfully");
	cn.Close();						
   }
   catch(OleDbException ex)
   {
   MessageBox.Show (ex.Message);
   }
}