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

Article translations Article translations
Article ID: 317017 - View products that this article applies to.
This article was previously published under Q317017
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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'
					

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

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.

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);}
   }
				

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);}
   }
				

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.

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

Properties

Article ID: 317017 - Last Review: February 27, 2014 - Revision: 3.1
APPLIES TO
  • 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
Keywords: 
kbnosurvey kbarchive kbhowtomaster kbsqlclient kbsystemdata KB317017

Give Feedback

 

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