將 BLOB 值寫入資料庫

您可以將二進位大型物件 (BLOB) 作為二進位或字元資料寫入資料庫，端視資料來源的欄位型別而定。若要將 BLOB 值寫入您的資料庫，請核發適當的 INSERT 或 UPDATE 陳述式，並傳遞 BLOB 值為輸入參數 (請參閱以命令使用預存程序)。如果 BLOB 儲存為 SQL Server text 欄位之類的文字，即可將 BOLB 當作字串參數來傳遞。如果 BLOB 儲存為 SQL Server image 欄位之類的二進位格式，即可將型別 byte 的陣列當作二進位參數來傳遞。

**注意   **BLOB 可能相當大，因此當作單一值寫入時會耗用大量的系統記憶體，而造成應用程式效能降低。若要在寫入 BLOB 值時減少使用的記憶體量，可以使用「區塊」(Chunk) 將 BOLB 寫入資料庫。以這種方式將 BLOB 寫入資料庫的程序需視資料來源的容量而定。如需在「區塊」中將 BLOB 值寫入 SQL Server 的範例，請參閱在將 BLOB 值寫入 SQL Server 時節約資源。

下列程式碼範例將員工資料加入至 Northwind 資料庫的 Employees 資料表中。員工的相片會從檔案讀取，並加入至資料表的 Photo 欄位 (這是 image 欄位)。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class EmployeeData

  Public Shared Sub Main()
    Dim hireDate As DateTime = DateTime.Parse("5/21/99")
    AddEmployee("Jones", "Mary", "Sales Representative", hireDate, 5, "jones.bmp")
  End Sub

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

    Dim photo() as Byte = GetPhoto(photoFilePath)

    Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;")

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

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

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

    nwindConn.Open()

    addEmp.ExecuteNonQuery()

    nwindConn.Close()
  End Sub

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

    Dim photo() As Byte = br.ReadBytes(fs.Length)

    br.Close()
    fs.Close()

    Return photo
  End Function
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
  public static void Main()
  {
    DateTime hireDate = DateTime.Parse("5/21/99");
    AddEmployee("Jones", "Mary", "Sales Representative", hireDate, 5, "jones.bmp");
  }

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

    SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");

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

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

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

    nwindConn.Open();

    addEmp.ExecuteNonQuery();

    nwindConn.Close();
  }

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

    byte[] photo = br.ReadBytes((int)fs.Length);

    br.Close();
    fs.Close();

    return photo;
  }
}

請參閱

使用 .NET Framework 資料提供者存取資料 | 從資料庫取得 BLOB 值 | OleDbCommand 類別 | OdbcCommand 類別 | SqlCommand 類別