×òåíèå è çàïèñü ôàéëà â ïîëÿ òèïà BLOB ïóòåì ðàçäåëåíèÿ íà ôðàãìåíòû ñ ïîìîùüþ ADO.NET è Visual C# .NET

• Äàííàÿ ñòàòüÿ ñîäåðæèò ïðèìåðû äëÿ ïîñòàâùèêîâ äàííûõ SqlClient Data Provider è OLE DB .NET Data Provider. Åäèíñòâåííîå îòëè÷èå ìåæäó íèìè (çà èñêëþ÷åíèåì èìåíè êëàññà) çàêëþ÷àåòñÿ â ñòðîêàõ ïîäêëþ÷åíèÿ è îáúÿâëåíèè ïàðàìåòðîâ SQL. Ïðè èñïîëüçîâàíèè èíñòðóêöèé READTEXT è UPDATETEXT ïðèìåíÿåòñÿ àíàëîãè÷íûé ïîäõîä.
•  òàáëèöå Categories ó÷åáíîé áàçû äàííûõ Northwind îòñóòñòâóåò çàïèñü ñî çíà÷åíèåì «Test». Íåîáõîäèìî ñ ïîìîùüþ îáîçðåâàòåëÿ ñåðâåðîâ (èëè àíàëîãè÷íîãî ñðåäñòâà) äîáàâèòü çàïèñü, â êîòîðîé ïîëþ CategoryName ïðèñâîåíî çíà÷åíèå Test. Åñëè ïîñëå èçó÷åíèÿ ïðèâåäåííûõ ïðèìåðîâ íóæíî áóäåò óäàëèòü ýòó çàïèñü, ââåäèòå ñëåäóþùèå êîìàíäû â ïðîãðàììå SQL Query Analyzer è íàæìèòå êëàâèøó F5:

  use Northwind
  delete from Categories where CategoryName = 'Test'
  					

Òðåáîâàíèÿ

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server èëè Microsoft Windows NT 4.0 Server.
• Microsoft Visual Studio .NET.
• Microsoft SQL Server 7.0 (èëè áîëåå ïîçäíÿÿ âåðñèÿ).

Ñîçäàíèå ïðîåêòà è äîáàâëåíèå êîäà

1. Çàïóñòèòå ïðèëîæåíèå SQL Query Analyzer.
2. Ââåäèòå ñëåäóþùóþ êîìàíäó è íàæìèòå êëàâèøó F5, ÷òîáû èñïîëüçîâàòü ïî óìîë÷àíèþ áàçó äàííûõ Northwind:

   use Northwind

3. Ââåäèòå ñëåäóþùóþ êîìàíäó è íàæìèòå êëàâèøó F5, ÷òîáû âñòàâèòü íîâóþ çàïèñü â òàáëèöó Categories áàçû äàííûõ Northwind:

   Insert into categories(categoryname) values ('Test')

   Ïðèìå÷àíèå. Ýòó çàïèñü ñëåäóåò äîáàâëÿòü òîëüêî â òîì ñëó÷àå, åñëè ïðèìåðû, ïðèâåäåííûå â äàííîé ñòàòüå, íå äîëæíû èçìåíÿòü äàííûå â òàáëèöå.
4. Çàïóñòèòå Visual Studio .NET è ñîçäàéòå ïðîåêò Visual C# .NET Windows Application.
5. Ïîìåñòèòå ñëåäóþùèå äâå ñòðîêè êîäà â íà÷àëî ôàéëà Form1.cs, ÷òîáû äîáàâèòü ññûëêó íà System.Data.SQLClient è System.Data.OleDb:

   using System.Data.SqlClient;
   using System.Data.OleDb;

6. Ïîìåñòèòå íà ôîðìó Form1 ÷åòûðå êíîïêè. Ïðèñâîéòå ñâîéñòâó Text ýòèõ êíîïîê çíà÷åíèÿ SQLBlob2File, OlDbBlob2File, File2OleDbBlob è File2SqlBlob ñîîòâåòñòâåííî.
7. Â îáúÿâëåíèå êëàññà Form1 äîáàâüòå ñëåäóþùèå ñòðîêè:

   string destfilepath;
   string sourcefilepath;
   					

8. Äîáàâüòå ñëåäóþùèé êîä ê îáðàáîò÷èêó ñîáûòèÿ Load äàííîé ôîðìû:

   destfilepath = @"c:\mytest.bmp";
   sourcefilepath = @"c:\windows\coffee bean.bmp";
   					

9. Âûçîâèòå ïðîöåäóðû ñîáûòèé Click äëÿ êàæäîé êíîïêè:

   // 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 == null)
    	{
    		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, ÷òîáû çàãðóçèòü îáðàç â áàçó äàííûõ.

Ñ÷èòûâàíèå ôðàãìåíòîâ èç ïîëåé òèïà BLOB

Çàïèñü ôðàãìåíòîâ â ïîëÿ òèïà BLOB

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. Äðóãèå ñèñòåìû óïðàâëåíèÿ áàçàìè äàííûõ ìîãóò èìåòü êîìàíäû, âûïîëíÿþùèå àíàëîãè÷íûå ôóíêöèè.

Ïåðåéòè ê íà÷àëó ñòðàíèöû | Îòïðàâèòü îòçûâ