????? ????? ????? ??? ?? ???? BLOB ???????? chunking ?? ADO.NET ? Visual C# .NET

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

?? ??? ??????

??????

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

????? ???? ???? ??????? ?? ???? ??????? ??? BLOB ???? ?? ??? ???? ?? ???? ????? ??? ???? ????? ?? ??????? BLOB ??????? ?? ??? ????. ??? ???? ?? ???? ????? ???????? ADO.NET ?????? GetChunk ? AppendChunk ???????? "?????? ?????? ??? ???????? (DAO)" ? "?????? ?????? ActiveX (ADO) ?????? Recordset. ???? ??? ??????? ????? ???????? ???????? ???????? ?? ??? ????.

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

Requirements

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

Create a project and add code

  1. ???? ???? ??????? SQL.
  2. ???? ????? ?????? ?? ???? F5 ?????? ????? ???????? ?????????? ??? Northwind:
    use Northwind
  3. ???? ????? ?????? ?? ???? ??? F5 ????? ??? ???? ?? ???? ?????? ?? ????? ???????? Northwind:
    Insert into categories(categoryname) values ('Test')
    ?????? ???? ?????? ??? ????? ??? ???? ?????? ??? ??? ???? ??????? ??? ?????? ??? ????? ?? ?? ???????? ???????? ?? ??? ?????? ???.
  4. ?? Visual Studio .NET ? ????? ???? Visual C# .NET Windows ??????? ???????.
  5. ???? Athe Form1.cs ?????? ????? ??????? ???????? ?? ????????? ???????? ?????? ????? ??? ??????? ?? ??? System.Data.SQLClientSystem.Data.OleDb:
    using System.Data.SqlClient;
    using System.Data.OleDb;
  6. ????? ????? ??????? Form1. ????? ??????? Text ??????? SQLBlob2FileOlDbBlob2File ? File2OleDbBlob ?? File2SqlBlob ? ??? ???????.
  7. ????? ??????? ????? ????? ??????? ??? ????? ?????? Form1:
    string destfilepath;
    string sourcefilepath;
    					
  8. ?? ???? ???????? ???????? ??????? ??? ????? Load ???????:
    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 ??? ?????.

Read chunks from a BLOB column

??????? ??????? ??????? ????? SQL Server READTEXT DataReader ??????? ??? ?? ???? BLOB ?? ?????? ???? ?? ????, ???? ????. ??? ??????? ???????: ????? ????? ??? ????? BLOB ????? ??? ?????; ?????? ?? READTEXT ????? ?????. ????? READTEXT ??????? ?????? ???????? ?? ???? ???? ?? ?????? ?????. ????? ???? ?????? ??? ????? ???? ??????. System.IO.Filesream

Write chunks to a BLOB column

??????? ??????? ??????? ??????? ??????? ??????? ????? SQL Server UPDATETEXT ?????? ?? ???????? ??? ?? ???? ???? ??? ???? BLOB. ????? ?????? BLOB NULL ?? ??? ??????? ??? ??? ????? ????? ???? ??? ?????? ??? ??????? TEXTPTR. ??? ????? DeleteParam.Value ??? ????? ?????? ?? ??????? UPDATETEXT ??? 1. ??? ???? ???? ???????? ?? ?????? ??? ????? ?? ???????? ???? ??? BLOB ?????? ???????? ??????? ????? ????. ???? UPDATETEXT ?????? executed ????? ????? ??????? ?? ??? ?????? ?????? ??? ?? ???????. ???
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);
   }
}
				
?????????
  • ?? ?? ???? ????????? ???????? ?????? ?? ??? ??????? ????? ????????? ????? LongVarChar ?? LongVarWChar ??????? ???? ?????.
  • ??? ????? ????? ??????? ??????? SQL ??????? ?? ?????? ????? ??. ??? ????? ????? ????? ???? ????????? ?????? ??? ????? ???????.
  • READTEXT UPDATETEXT ???? ?? Microsoft SQL Server. ?? ???? ???? ????? ????? ???????? ???????? ????? ?????? ???? ????? ?????????.

?????

?????? ??? ??????? ?????? ??? ????? ????? ?????? ???????? ??? chunking ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ?? Microsoft:
316887????? ????? ????? ??? ?? ???? BLOB ???????? ADO.NET ? Visual Basic .NET
317017????? ????? ????? ??? ?? ???? BLOB ???????? ADO.NET ? Visual C++ .NET
317016????? ????? ????? ??? ??? ?? ?? ???? BLOB ???????? ADO.NET ? Visual C# .NET
?????? ??? ???? ?? ????????? ??? ????? ?? BLOBS ?? ADO.NET ?? ?????? ???? ???? ????? Microsoft (MSDN) ?????? ??? ?????:
?????? ??? ??? BLOB ?? ????? ??????
http://msdn.microsoft.com/en-us/library/87z0hy49.aspx

???????

???? ???????: 317043 - ????? ??? ??????: 13/?????/1425 - ??????: 3.3
????? ???
  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
????? ??????: 
kbmt kbsystemdata kbsqlclient kbhowtomaster KB317043 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????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