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


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual C++ .NET (2002)
  • Microsoft Visual C++ .NET (2003)
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

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

NOTES

This article contains examples for both the SqlClient and the OleDb DataAdapter classes. The only differences, apart from the class names, are the connection strings and the declaration of SQL parameters. The technique for retrieving BLOB data is fundamentally the same.

The Test record in the categories table does not exist. You will need to add a CategoryName named Test. To do this, execute the following code in Microsoft SQL Server Query Analyzer: 
use Northwind
Insert into categories(categoryname) values ('Test')
After you use the following samples, you may want to remove this record from the database. To remove the record from the database, type the following command in SQL Query Analyzer. Then, press F5.
use Northwind
delete from Categories where CategoryName = 'Test'
back to the top

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

  1. Start Visual Studio .NET.
  2. In Visual .NET 2002, create a new Managed C++ Application.

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

    Name the project "BLOBReadWrite."
  3. In the BLOBReadWrite.cpp file, type or paste the following code. Overwrite the existing code generated by Visual Studio .NET.
       #include "stdafx.h"
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.Data.dll>

   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 = "c:\\mytest.bmp"; 
        // File you will insert into database.
        String *SourceFilePath = "c:\\windows\\coffee bean.bmp"; 

	// 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;   
   }
  4. 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.
  5. Press CTRL+F5 to execute the code. Messages will appear in the Console window that confirm the success of each function.
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("server=localhost;integrated security=yes;database=NorthWind");
	SqlCommand *cmd = new SqlCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", 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("SqlBlob2File completed successfully.\nPress return to continue.");
	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("provider=SQLOLEDB;server=localhost;user id=user;password=pass;database=NorthWind");
	OleDbCommand *cmd = new OleDbCommand("SELECT Picture FROM Categories WHERE CategoryName='Test'", 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("OleDbBlob2File completed successfully.\nPress return to continue.");
	}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("server=localhost;integrated security=yes;database=NorthWind");
	// Create SQL command containing @Picture parameter for BLOB.
	SqlCommand *cmd = new SqlCommand("UPDATE Categories SET Picture=@Picture WHERE CategoryName='Test'", 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("@Picture", 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("File2SqlBlob completed successfully.\nPress return to continue.");
	Console::ReadLine();
	}catch(SqlException *ex)
	 {Console::Write(ex->Message);}
   }

   void File2OleDbBlob(String *SourceFilePath)
   {
	try{
	OleDbConnection *cn = new OleDbConnection("provider=SQLOLEDB;user id=user;password=pass;database=NorthWind");
	// Create SQL command containing ? parameter for BLOB.
	OleDbCommand *cmd = new OleDbCommand("UPDATE Categories SET Picture=? WHERE CategoryName='Test'", 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("@Picture", 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("File2OleDbBlob completed successfully.\nPress return to continue.");
	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

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

317044 HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and C++

For additional information about using .NET Managed Providers in Visual C++ .NET, click the article number below to view the article in the Microsoft Knowledge Base:

313480 INFO: Roadmap for .NET Data Providers

For more information about working with BLOBs in ADO.NET, visit the following Microsoft Web site:

Obtaining BLOB Values from a Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp

back to the top
Modification Type:MajorLast Reviewed:2/7/2005
Keywords:kbHOWTOmaster kbSqlClient kbSystemData KB317017 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.