Microsoft KB Archive/317017

= HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual C++ .NET =

Article ID: 317017

Article Last Modified on 2/7/2005

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual C++ .NET 2002 Standard Edition
 * Microsoft Visual C++ .NET 2003 Standard Edition

-



This article was previously published under Q317017



For a Microsoft Visual Basic .NET version of this article, see 316887.

For a Microsoft Visual C# .NET version of this article, see 317016.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System::Data
 * System::Data::SqlClient
 * System::Data::OleDb
 * System::IO

IN THIS TASK
SUMMARY Requirements Create the Project and Add the Code
 * Functions to Read from a BLOB Column
 * Functions to Write to a BLOB Column

Troubleshooting REFERENCES



SUMMARY
Use this step-by-step guide to read and write data to and from BLOB (LongVarBinary) columns in a database table.

Requirements
The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs you will need:
 * Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
 * Microsoft Visual Studio .NET
 * Microsoft SQL Server 7.0 or later

This article assumes that you are familiar with the following topics:
 * Visual Studio .NET
 * ADO.NET fundamentals and syntax

back to the top

Create the Project and Add the Code
 Start Visual Studio .NET. In Visual .NET 2002, create a new Managed C++ Application.

In Visual .NET 2003, create a new Console Application (.NET).

Name the project &quot;BLOBReadWrite.&quot;  In the BLOBReadWrite.cpp file, type or paste the following code. Overwrite the existing code generated by Visual Studio .NET. #include &quot;stdafx.h&quot; #using  #using  #using 

using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; using namespace System::Data::OleDb; using namespace System::IO;

// Prototypes for functions that do the actual work. void File2SqlBlob(String *); void File2OleDbBlob(String *); void SqlBlob2File(String *); void OleDbBlob2File(String *);

int direction(void); int reader (void);

// This is the entry point for the application. #ifdef _UNICODE int wmain(void) #else int main(void) #endif {       // File you will create from database. String *DestFilePath = &quot;c:\\mytest.bmp&quot;; // File you will insert into database. String *SourceFilePath = &quot;c:\\windows\\coffee bean.bmp&quot;;

// Insert BLOB into database from file using SQL provider. File2SqlBlob(SourceFilePath);

// Insert BLOB into database from file using OleDb provider. File2OleDbBlob(SourceFilePath);

// Read BLOB from database into file using SQL provider. SqlBlob2File(DestFilePath);

// Read BLOB from database into file using OleDb provider. OleDbBlob2File(DestFilePath);

return 0; }                    Copy the functions from the Functions to Read from a BLOB Column and the Functions to Write to a BLOB Column sections later in this article and paste them into the bottom of the BLOBReadWright.cpp file. Press CTRL+F5 to execute the code. Messages will appear in the Console window that confirm the success of each function.</li></ol>

back to the top

Functions to Read from a BLOB Column
The following functions use the DataReader to retrieve the BLOB value and assign it to a Byte array. Because the BLOB is in memory, you do not need to chunk the data; it is assigned to a Byte array. There are two calls to the GetBytes method. Rhe first call gets the length of the BLOB in bytes and allocates the Byte array. The second call retrieves the data. The FileStream object writes the Byte array to disk. void SqlBlob2File(String *DestFilePath) {   try{ // The column number of the BLOB field. int PictureCol = 0; SqlConnection *cn = new SqlConnection(&quot;server=localhost;integrated security=yes;database=NorthWind&quot;); SqlCommand *cmd = new SqlCommand(&quot;SELECT Picture FROM Categories WHERE CategoryName='Test'&quot;, cn); cn->Open;

// Create server-side DataReader to read BLOB from database. SqlDataReader *dr = cmd->ExecuteReader; dr->Read;

// Create buffer for BLOB and read from DataReader. Close // DataReader and Connection. Byte b[] = __gc new Byte[Convert::ToInt32((dr->GetBytes(PictureCol, 0, 0, 0, Int32::MaxValue)))]; dr->GetBytes(PictureCol, 0, b, 0, b->Length); dr->Close; cn->Close;

// Open FileStream and write buffer to file. FileStream *fs = new FileStream(DestFilePath, FileMode::Create, FileAccess::Write); fs->Write(b, 0, b->Length); fs->Close;

Console::WriteLine(&quot;SqlBlob2File completed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(SqlException *ex) {Console::Write(ex->Message);} }

void OleDbBlob2File(String *DestFilePath) {   try{ // The column number of the BLOB field. int PictureCol = 0; OleDbConnection *cn = new OleDbConnection(&quot;provider=SQLOLEDB;server=localhost;user id=user;password=pass;database=NorthWind&quot;); OleDbCommand *cmd = new OleDbCommand(&quot;SELECT Picture FROM Categories WHERE CategoryName='Test'&quot;, cn); cn->Open;

// Create server-side DataReader to read BLOB from database. OleDbDataReader *dr = cmd->ExecuteReader; dr->Read;

// Create buffer for BLOB and read from DataReader. Close // DataReader and Connection. Byte b[] = __gc new Byte[Convert::ToInt32((dr->GetBytes(PictureCol, 0, 0, 0, Int32::MaxValue)))]; dr->GetBytes(PictureCol, 0, b, 0, b->Length); dr->Close; cn->Close;

// Open FileStream and write buffer to file. FileStream *fs = new FileStream(DestFilePath, FileMode::Create, FileAccess::Write); fs->Write(b, 0, b->Length); fs->Close; Console::WriteLine(&quot;OleDbBlob2File completed successfully.\nPress return to continue.&quot;); }catch(OleDbException *ex) {Console::Write(ex->Message);} } back to the top

Functions to Write to a BLOB Column
The following functions use the Command and Parameter objects to write data from a Byte array to a BLOB column. Because it reads the file into memory, it does not need to chunk the data when it writes to the server. The Byte array and its length are passed to the Parameter constructor. void File2SqlBlob(String *SourceFilePath) {   try{ SqlConnection *cn = new SqlConnection(&quot;server=localhost;integrated security=yes;database=NorthWind&quot;); // Create SQL command containing @Picture parameter for BLOB. SqlCommand *cmd = new SqlCommand(&quot;UPDATE Categories SET Picture=@Picture WHERE CategoryName='Test'&quot;, cn); // Read FileStream into buffer and then close stream. FileStream *fs = new FileStream(SourceFilePath, FileMode::Open, FileAccess::Read); int size = Convert::ToInt32(fs->Length); Byte b[] = __gc new Byte[size]; fs->Read(b, 0, size); fs->Close;

// Create parameter for the @Picture contained in SQL statement. SqlParameter *P = new SqlParameter(&quot;@Picture&quot;, SqlDbType::VarBinary, b->Length, ParameterDirection::Input, false, 0, 0, 0, DataRowVersion::Current, b); cmd->Parameters->Add(P);

// Open connection, execute query, and close connection. cn->Open; if (cmd->ExecuteNonQuery == 1) cn->Close;

Console::WriteLine(&quot;File2SqlBlob completed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(SqlException *ex) {Console::Write(ex->Message);} }

void File2OleDbBlob(String *SourceFilePath) {   try{ OleDbConnection *cn = new OleDbConnection(&quot;provider=SQLOLEDB;user id=user;password=pass;database=NorthWind&quot;); // Create SQL command containing ? parameter for BLOB. OleDbCommand *cmd = new OleDbCommand(&quot;UPDATE Categories SET Picture=? WHERE CategoryName='Test'&quot;, cn); // Read FileStream into buffer and then close stream. FileStream *fs = new FileStream(SourceFilePath, FileMode::Open, FileAccess::Read); int size = Convert::ToInt32(fs->Length); Byte b[] = __gc new Byte[size]; fs->Read(b, 0, size); fs->Close; // Create parameter for the ? contained in the SQL statement. OleDbParameter *P = new OleDbParameter(&quot;@Picture&quot;, OleDbType::VarBinary, b->Length, ParameterDirection::Input, false, 0, 0, 0, DataRowVersion::Current, b); cmd->Parameters->Add(P); // Open connection, execute query, and close connection. cn->Open; if (cmd->ExecuteNonQuery == 1) cn->Close;

Console::WriteLine(&quot;File2OleDbBlob completed successfully.\nPress return to continue.&quot;); Console::ReadLine; }catch(OleDbException *ex) {Console::Write(ex->Message);} } back to the top

Troubleshooting

 * The code described in this article may not be suitable for use against LongVarChar or LongVarWChar columns without modification.
 * You should modify the connection string and SQL statements for your server. Also, you should add error checking in case your query returns no records.

back to the top

<div class="references_section">