從檔案插入影像 (ADO.NET)
更新： November 2007
您可以將二進位大型物件 (BLOB) 做為二進位或字元資料寫入資料庫，端視資料來源的欄位型別而定。BLOB 是參考 text、ntext 和 image 資料型別的一般詞彙，其中通常包含文件和圖片。
若要將 BLOB 值寫入您的資料庫，請核發適當的 INSERT 或 UPDATE 陳述式，並將 BLOB 值傳遞為輸入參數 (請參閱設定參數和參數資料型別 (ADO.NET))。如果 BLOB 儲存為 SQL Server text 欄位之類的文字，即可將 BOLB 傳遞為字串參數。如果 BLOB 儲存為 SQL Server image 欄位之類的二進位格式，即可將型別 byte 的陣列傳遞為二進位參數。
範例
下列程式碼範例將員工資料加入至 Northwind 資料庫的 Employees 資料表中。員工的相片會從檔案中讀取，並加入資料表的 Photo 欄位 (這是影像欄位)。
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;
}