????? ?? ????? ???? ????? ?? ??? ?? ????? ????? ?? ADO.NET ?? ????? C# .NET ??? chunking ?? ????? ?? ???? ????

???? ?????? ???? ??????
???? ID: 317043 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?? ???? ?? Microsoft Visual Basic .NET ??????? ?? ???, ?????317034.
?? Microsoft Visual C++ ?? ??? ?? ???? ?? .NET ??????? ?????317044.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ??? ?? ??? ???? ????? ???? ?? ?? ????? ?? ???? ??????? ?????? ??? ??????? ?? ????? (LongVarBinary) ?? ???? ????? ?? ??? Microsoft SQL Server READTEXT ?? UPDATETEXT ??? ?? ????? ???? ?????

??????? ??????, ?? ???? ?? ???? ??? ?? ???? ??? ????? ???? ????? ???????????? ???? ?? ???? ??? ??? ?? ?? ??? ??? ????? ??? ?? ??? ????? ????? ??????? ???? ?? ??? ??? ???? ??? ???????, ADO.NET ???? ??????? ???? ??GetChunk, ??AppendChunk???? ????? ???????? (DAO) ?? ActiveX ???? ???????? (ADO) ?? ??? ?????? ???????Recordset???????? ??? ?? ???? ??? ???? ??? ?? ???? ?? ??????? ???? ?? ??? ????? ????? ?? ????? ???? ???

???:
  • ?? ???? ??? ?????? ????? SqlClient ???? ?? ??? ??????? ?? OLE DB .NET ???? ???????? ???? ????, ???? ?? ???, apart ?? ??????? ???????? ?? declaration SQL ???????? ?? ???? ?????? ????? READTEXT ?? UPDATETEXT ?? ??? ?? ??????? ???? ?? ??? ???? ???
  • ??????? ??? ?????? ????????? ?????? ????????? ????? ??????? ????? ???? ??? ?? ?? ????? ???? ????? ????? Explorer ?? ???? ????? ?? ??? ??? ?????? ????? ?? ???CategoryName???? ?? ??? ??? ????Test. ?? ????? ????? ?? ????? ???? ?? ??? ?? ??????? ?? ?? ??????? ?? ??????? ?? ??? ?? ????? ?????? ?? ??????? ?? ??? SQL ?????? ???????? ??? ????? ???? ???? ????, ?? ??? F5 ?????:
    use Northwind
    delete from Categories where CategoryName = 'Test'
    					

??????????

???????? ?????????, ??????????, ??????? ??????, ???? ?? ????? ?? ???? ??? ?????? ??? ?? ????? ?????? ?? ?????:
  • Microsoft Windows 2000 Professional ???, Windows 2000 ?????, Windows 2000 ????? ?? Microsoft Windows NT 4.0 ????? ????? ????
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server 7.0 ?? ??? ?? ???????
?? ???? ????? ?? ?? ADO.NET ?? ?????? ??? fundamentals ?? ?????????

??? ????????? ??? ?? ??? ??????

  1. SQL ?????? ???????? ??????
  2. ????? ???? ???? ????, ?? ????????? ??????? ??????? ????????? ???? ?? ??? F5 ?????:
    use Northwind
  3. ????? ???? ???? ????, ?? ????????? ??????? ?????? ?????? ??? ?? ??? ?????? ???????? ???? ?? ??? F5 ?????:
    Insert into categories(categoryname) values ('Test')
    ???:???? ??? ???? ????? ?? ??? ?? ??????? ???? ?? ??? ????????? ?????? ??? ??? ?? ?????? ???? ?? ?? ?????? ??? ?????? ?? ???? ?? ?????? ?? ????? ???? ????? ????
  4. ??? ??? ????? C# .NET Windows ?? ????? ??? Visual Studio .NET, ????????? ??????????
  5. Athe ??? ???? Form1.cs ????? ?????? ?? ??? ???? ????????? ??? ?????? ????? ?? ??? ??? ?? ????? ?? ?????????System.Data.SQLClient, ??System.Data.OleDb:
    using System.Data.SqlClient;
    using System.Data.OleDb;
  6. Form1 ?? ??? ??? ??? ??????? ????????? ??????????? ?? ??? ??? ?? ???SQLBlob2File,OlDbBlob2File,File2OleDbBlob, ??File2SqlBlob, respectively.
  7. ????? ???????? ?? declarations Form1 ????????? ???? ?? ??????? ??????:
    string destfilepath;
    string sourcefilepath;
    					
  8. ??????? ?? ??????? ????? ??? ?????????? ?????????:
    destfilepath = @"c:\mytest.bmp";
    sourcefilepath = @"c:\windows\coffee bean.bmp";
    					
  9. ???????????? ??? ????????? ????,???????? ??? ?? ??? ?????:
    // Click event for the button labeled SqlBlob2File.
    SqlChunkBlob2File(destfilepath);
    
    // Click event for the button labeled OLDbBlob2File.
    OlDbChunkBlob2File(destfilepath);
    
    // Click event for the button labeled File2OleDbBlob.
    ChunkFile2OleDbBlob(sourcefilepath);
    
    //Click event for the button labeled File2SqlBlob.
    ChunkFile2SqlBlob(sourcefilepath);
    					
  10. Form1 ??? ????? ??? ?????? ?? ???????:
    public void SqlChunkBlob2File(string DestFilePath)
    {
       try
       {
    	int PictureCol  = 0;  // position of Picture column in DataReader
    	int BUFFER_LENGTH  = 32768; // chunk size
    	SqlConnection cn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
    			
    	// Make sure Photo is non-NULL and return TEXTPTR to it.
    			
    	SqlCommand cmdGetPointer = new SqlCommand("SELECT @Pointer=TEXTPTR(Picture), @Length=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
    	SqlParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
    	PointerOutParam.Direction = ParameterDirection.Output;
    	SqlParameter LengthOutParam  = cmdGetPointer.Parameters.Add("@Length", SqlDbType.Int);
    	LengthOutParam.Direction = ParameterDirection.Output;
    	cn.Open();
    	cmdGetPointer.ExecuteNonQuery();
    	if(PointerOutParam.Value == null) 
    	{
    		cn.Close();
    		// Add code to handle NULL BLOB.
    		return;
    	}
    			
    	// Set up READTEXT command, parameters, and open BinaryReader.
    			
    	SqlCommand cmdReadBinary = new SqlCommand("READTEXT Categories.Picture @Pointer @Offset @Size HOLDLOCK", cn);
    	SqlParameter PointerParam  = cmdReadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    	SqlParameter OffsetParam  = cmdReadBinary.Parameters.Add("@Offset", SqlDbType.Int);
    	SqlParameter SizeParam  = cmdReadBinary.Parameters.Add("@Size", SqlDbType.Int);
    	SqlDataReader dr; 
    	System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
    	int Offset= 0;
    	OffsetParam.Value = Offset;
    	Byte []Buffer = new Byte[BUFFER_LENGTH ];
    
    			
    	// Read buffer full of data and write to the file stream.
    			
    	do
    	{
    		PointerParam.Value = PointerOutParam.Value;
    				
    		// Calculate buffer size - may be less than BUFFER_LENGTH for last block.
    				
    		if( (Offset + BUFFER_LENGTH) >= System.Convert.ToInt32(LengthOutParam.Value)) 
    			SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
    		else SizeParam.Value = BUFFER_LENGTH;
    				
    		dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
    		dr.Read();
    		dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
    		dr.Close();
    		fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
    		Offset += System.Convert.ToInt32(SizeParam.Value);
    		OffsetParam.Value = Offset;
    	}while(Offset < System.Convert.ToInt32(LengthOutParam.Value));
    
    	fs.Close();
    	cn.Close();
       }
       catch(SqlException ex)
       {
       MessageBox.Show (ex.Message);
       }
    }
    
    public void OleDbChunkBlob2File(string DestFilePath)
    {
       try
       {
    	int PictureCol= 0; // Position of picture column in DataReader.
    	int BUFFER_LENGTH = 32768;// Chunk size.
    	OleDbConnection cn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
    			
    	// Make sure Photo is non-NULL and return TEXTPTR to it.
    		
    	OleDbCommand cmdGetPointer = new OleDbCommand("SELECT ?=TEXTPTR(Picture), ?=DataLength(Picture) FROM Categories WHERE CategoryName='Test'", cn);
    	OleDbParameter PointerOutParam =  cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100);
    	PointerOutParam.Direction = ParameterDirection.Output;
    	OleDbParameter LengthOutParam = cmdGetPointer.Parameters.Add("@Length", OleDbType.Integer);
    	LengthOutParam.Direction = ParameterDirection.Output;
    	cn.Open();
    	cmdGetPointer.ExecuteNonQuery();
    if(PointerOutParam.Value == DBNull.Value )
    	{
    		cn.Close();
    		// Add code to deal with NULL BLOB.
    		return;
    	}
    			
    	// Set up READTEXT command, parameters, and open BinaryReader.
    			
    	OleDbCommand cmdReadBinary = new OleDbCommand("READTEXT Categories.Picture ? ? ? HOLDLOCK", cn);
    	OleDbParameter PointerParam  = cmdReadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16);
    	OleDbParameter OffsetParam  = cmdReadBinary.Parameters.Add("@Offset", OleDbType.Integer);
    	OleDbParameter SizeParam  = cmdReadBinary.Parameters.Add("@Size", OleDbType.Integer);
    	OleDbDataReader dr;
    	System.IO.FileStream fs = new System.IO.FileStream(DestFilePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
    	int Offset= 0;
    	OffsetParam.Value = Offset;
    	Byte[] Buffer = new Byte[BUFFER_LENGTH];
    			
    	//Read buffer full of data and write to the file stream.
    			
    	do
    	{
    		PointerParam.Value = PointerOutParam.Value;
    				
    		// Calculate buffer size - may be less than BUFFER_LENGTH for last block.
    				
    		if((Offset + BUFFER_LENGTH) >= System.Convert.ToInt32(LengthOutParam.Value))
    			SizeParam.Value = System.Convert.ToInt32(LengthOutParam.Value) - Offset;
    		else SizeParam.Value = BUFFER_LENGTH;
    				
    		dr = cmdReadBinary.ExecuteReader(CommandBehavior.SingleResult);
    		dr.Read();
    		dr.GetBytes(PictureCol, 0, Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
    		dr.Close();
    		fs.Write(Buffer, 0, System.Convert.ToInt32(SizeParam.Value));
    		Offset += System.Convert.ToInt32(SizeParam.Value);
    		OffsetParam.Value = Offset;
    	}while( Offset < System.Convert.ToInt32(LengthOutParam.Value));
    
    	fs.Close();
    	cn.Close();
       }
       catch(OleDbException ex)
       {
       MessageBox.Show (ex.Message);
       }
    }
    				
  11. ???? ??? ????? ????, ?? ??? ?? ????? ?? ??? F5 ?????File2OleDbBlob????????? ???? ?? ?? ??? SQL Server ??????? ??? ??? ??? ???? ?? ??? ???. bmp ????? ?? ????? ?? ????? ?? ?????? ???? ?? ?????

??? ?? ?? ????? ????? ?? ?????

????? ???????? SQL Server READTEXT ??? ?? DataReader ????? ??? ??? ??? ??? ??????, ??? ????? rowset ?? ?? ??? ?? ???? ???? ?? ????? ????? Two commands are used: the first retrieves the size of the BLOB field and a pointer to its location; the second executes the READTEXT command. The READTEXT command retrieves the chunk of data in a Byte array and increments an Offset. The Byte array is written to the disk through theSystem.IO.Filesream???????? ???

Write chunks to a BLOB column

????? ??? ?? ????? ???? ??????, ??????????objects and the SQL Server UPDATETEXT statement to write chunks of data from a Byte array to a BLOB column. The BLOB column cannot be NULL with this method, so a single byte is assigned to the column before TEXTPTR is retrieved. On the first execution of the UPDATETEXT statement,DeleteParam.Valueis set to 1. This deletes the existing byte from the column before inserting the chunk and prevents the BLOB from having extraneous data appended to it. The UPDATETEXT statement is executed multiple times, incrementing the Offset with the size of the buffer after each call.
private void ChunkFile2SqlBlob(string SourceFilePath)
{
   try
   {
	int BUFFER_LENGTH = 32768; // Chunk size.
	SqlConnection cn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
			
	// Make sure Photo is non-NULL and return TEXTPTR to it.
		
	SqlCommand cmdGetPointer = new SqlCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
					"SELECT @Pointer=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn);
	SqlParameter PointerOutParam  = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
	PointerOutParam.Direction = ParameterDirection.Output;
	cn.Open();
	cmdGetPointer.ExecuteNonQuery();
		
	// Set up UPDATETEXT command, parameters, and open BinaryReader.
			
	SqlCommand cmdUploadBinary = new SqlCommand("UPDATETEXT Categories.Picture @Pointer @Offset @Delete WITH LOG @Bytes", cn);
	SqlParameter PointerParam  = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
	SqlParameter OffsetParam= cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int);
	SqlParameter DeleteParam = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int);
	DeleteParam.Value = 1;  // delete 0x0 character
				SqlParameter BytesParam  = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH);
	System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
	System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
	int Offset = 0;
	OffsetParam.Value = Offset;

			
	// Read buffer full of data and execute UPDATETEXT statement.
			
	Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
	while(Buffer.Length > 0)
	{
		PointerParam.Value = PointerOutParam.Value;
		BytesParam.Value = Buffer;
		cmdUploadBinary.ExecuteNonQuery();
		DeleteParam.Value = 0; //Do not delete any other data.
		Offset += Buffer.Length;
		OffsetParam.Value = Offset;
		Buffer = br.ReadBytes(BUFFER_LENGTH);
	}

	br.Close();
	fs.Close();
	cn.Close();
   }
   catch(SqlException ex)
   {
   MessageBox.Show (ex.Message);
   }			
}

public void ChunkFile2OleDbBlob(string SourceFilePath)
{
   try
   {	
         int BUFFER_LENGTH = 32768; // chunk size
	OleDbConnection cn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
		
	// Make sure Photo is non-NULL and return TEXTPTR to it.
		
	OleDbCommand cmdGetPointer = new OleDbCommand("SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 WHERE CategoryName='Test';" +
				"SELECT ?=TEXTPTR(Picture) FROM Categories WHERE CategoryName='Test'", cn);
	OleDbParameter PointerOutParam = cmdGetPointer.Parameters.Add("@Pointer", OleDbType.VarBinary, 100);
	PointerOutParam.Direction = ParameterDirection.Output;
	cn.Open();
	cmdGetPointer.ExecuteNonQuery();
			
	// Set up UPDATETEXT command, parameters, and open BinaryReader.
			
	OleDbCommand cmdUploadBinary = new OleDbCommand("UPDATETEXT Categories.Picture ? ? ? WITH LOG ?", cn);
	OleDbParameter PointerParam = cmdUploadBinary.Parameters.Add("@Pointer", OleDbType.Binary, 16);
	OleDbParameter OffsetParam = cmdUploadBinary.Parameters.Add("@Offset", OleDbType.Integer);
	OleDbParameter DeleteParam   = cmdUploadBinary.Parameters.Add("@Delete", OleDbType.Integer);
	DeleteParam.Value = 1;  // delete 0x0 character
	OleDbParameter BytesParam = cmdUploadBinary.Parameters.Add("@Bytes", OleDbType.Binary, BUFFER_LENGTH);
	System.IO.FileStream fs = new System.IO.FileStream(SourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
	System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
	int Offset= 0;
	OffsetParam.Value = Offset;

			
	// Read buffer full of data and execute UPDATETEXT statement.
			
         Byte[] Buffer = br.ReadBytes(BUFFER_LENGTH);
	while(Buffer.Length > 0)
	{
		PointerParam.Value = PointerOutParam.Value;
		BytesParam.Value = Buffer;
		cmdUploadBinary.ExecuteNonQuery();
		DeleteParam.Value = 0;// Do not delete any other data.
		Offset += Buffer.Length;
		OffsetParam.Value = Offset;
		Buffer = br.ReadBytes(BUFFER_LENGTH);
	}

	br.Close();
	fs.Close();
	cn.Close();		
   }
   catch(OleDbException ex)
   {
   MessageBox.Show (ex.Message);
   }
}
				
???
  • The code that is described in this article may not be suitable for use against LongVarChar or LongVarWChar columns without modification.
  • You must modify the connection string and SQL statements to correspond to your own server. You must also add error checking if your query returns no records.
  • READTEXT ?? UPDATETEXT Microsoft SQL ???? ?? ??? ??????? ??? ?????? Different database systems may have similar commands that you can use.

??????

For additional information about how to read and write data without chunking, click the following article numbers to view the articles in the Microsoft Knowledge Base:
316887How to read and write a file to and from a BLOB column by using ADO.NET and Visual Basic .NET
317017How to read and write a file to and from a BLOB column by using ADO.NET and Visual C++ .NET
317016How to read and write a file to or from a BLOB column by using ADO.NET and Visual C# .NET
For more information about working with BLOBs in ADO.NET, visit the following Microsoft Developer Network (MSDN) Web site:
Obtaining BLOB values from a database
http://msdn.microsoft.com/en-us/library/87z0hy49.aspx

???

???? ID: 317043 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
??????: 
kbsystemdata kbsqlclient kbhowtomaster kbmt KB317043 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:317043

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com