Streaming BLOBs (ADO.NET)

You can write a binary large object (BLOB) to a database as either binary or character data, depending on the type of field at your data source. BLOB is a generic term that refers to the text, ntext, and image data types, which typically contain documents and pictures.

To write a BLOB value to your database, issue the appropriate INSERT or UPDATE statement and pass the BLOB value as an input parameter (see Configuring Parameters and Parameter Data Types (ADO.NET)). If your BLOB is stored as text, such as a SQL Server text field, you can pass the BLOB as a string parameter. If the BLOB is stored in binary format, such as a SQL Server image field, you can pass an array of type byte as a binary parameter.

Example

The following code example adds employee information to the Employees table in the Northwind database. A photo of the employee is read from a file and added to the Photo field in the table, which is an image field.

Public Shared Sub AddEmployee( _
  lastName As String, _
  firstName As String, _
  title As String, _
  hireDate As DateTime, _
  reportsTo As Integer, _
  photoFilePath As String, _
  connectionString As String)

  Dim photo() as Byte = GetPhoto(photoFilePath)

  Using connection As SqlConnection = New SqlConnection( _
    connectionString)

  Dim command As SqlCommand = New SqlCommand( _
    "INSERT INTO Employees (LastName, FirstName, Title, " & _
    "HireDate, ReportsTo, Photo) " & _
    "Values(@LastName, @FirstName, @Title, " & _
    "@HireDate, @ReportsTo, @Photo)", connection) 

  command.Parameters.Add("@LastName",  _
    SqlDbType.NVarChar, 20).Value = lastName
  command.Parameters.Add("@FirstName", _
    SqlDbType.NVarChar, 10).Value = firstName
  command.Parameters.Add("@Title", _
    SqlDbType.NVarChar, 30).Value = title
  command.Parameters.Add("@HireDate", _
    SqlDbType.DateTime).Value = hireDate
  command.Parameters.Add("@ReportsTo", _
    SqlDbType.Int).Value = reportsTo

  command.Parameters.Add("@Photo", _
    SqlDbType.Image, photo.Length).Value = photo

  connection.Open()
  command.ExecuteNonQuery()

  End Using
End Sub

Public Shared Function GetPhoto(filePath As String) As Byte()
  Dim stream As FileStream = new FileStream( _
     filePath, FileMode.Open, FileAccess.Read)
  Dim reader As BinaryReader = new BinaryReader(stream)

  Dim photo() As Byte = reader.ReadBytes(stream.Length)

  reader.Close()
  stream.Close()

  Return photo
End Function

public static void AddEmployee(
  string lastName, 
  string firstName, 
  string title, 
  DateTime hireDate, 
  int reportsTo, 
  string photoFilePath, 
  string connectionString)
{
  byte[] photo = GetPhoto(photoFilePath);

  using (SqlConnection connection = new SqlConnection(
    connectionString))

  SqlCommand command = new SqlCommand(
    "INSERT INTO Employees (LastName, FirstName, " +
    "Title, HireDate, ReportsTo, Photo) " +
    "Values(@LastName, @FirstName, @Title, " +
    "@HireDate, @ReportsTo, @Photo)", connection); 

  command.Parameters.Add("@LastName",  
     SqlDbType.NVarChar, 20).Value = lastName;
  command.Parameters.Add("@FirstName", 
      SqlDbType.NVarChar, 10).Value = firstName;
  command.Parameters.Add("@Title",     
      SqlDbType.NVarChar, 30).Value = title;
  command.Parameters.Add("@HireDate", 
       SqlDbType.DateTime).Value = hireDate;
  command.Parameters.Add("@ReportsTo", 
      SqlDbType.Int).Value = reportsTo;

  command.Parameters.Add("@Photo",
      SqlDbType.Image, photo.Length).Value = photo;

  connection.Open();
  command.ExecuteNonQuery();
  }
}

public static byte[] GetPhoto(string filePath)
{
  FileStream stream = new FileStream(
      filePath, FileMode.Open, FileAccess.Read);
  BinaryReader reader = new BinaryReader(stream);

  byte[] photo = reader.ReadBytes((int)stream.Length);

  reader.Close();
  stream.Close();

  return photo;
}

See Also

Concepts

Retrieving Large Data (ADO.NET)

SQL Server Data Type Mappings (ADO.NET)

Other Resources

Using Commands to Modify Data (ADO.NET)

Binary and Large-Value Data (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center