?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
?? ???? ?? Microsoft Visual Basic .NET ??????? ?? ???, ?????317034
(http://support.microsoft.com/kb/317034/EN-US/
)
. ?? Microsoft Visual C++ ?? ??? ?? ???? ?? .NET ??????? ?????317044
(http://support.microsoft.com/kb/317044/EN-US/
)
.
// 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);
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);
}
}
????? ???????? 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???????? ???
????? ??? ?? ????? ???? ??????, ??????????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:
316887
(http://support.microsoft.com/kb/316887/
)
How to read and write a file to and from a BLOB column by using ADO.NET and Visual Basic .NET
317017
(http://support.microsoft.com/kb/317017/
)
How to read and write a file to and from a BLOB column by using ADO.NET and Visual C++ .NET
317016
(http://support.microsoft.com/kb/317016/
)
How 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:
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
???? ?????? ????????
????? ?? ???? ????
