在實際項目中,常需要將圖片、可執行文件、視頻等大型二進制數據存入 SQL Server。SQL Server 提供 image 數據類型(最大 2GB),配合 ADO.NET 即可實現文件的存儲與讀取。下面通過一個完整的 WinForms 示例演示核心流程。
CREATE TABLE [imgtable] (
[imgid] [int] IDENTITY(1,1) NOT NULL,
[imgname] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[imgData] [image] NULL,
PRIMARY KEY CLUSTERED ([imgid]) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
示例使用 System.Data.SqlClient、System.IO 命名空間,界面包含「選擇文件」「保存圖片」「顯示圖片」「讀取文件並打開」四個按鈕及一個 PictureBox。
用戶通過 OpenFileDialog 選擇文件後,讀取文件流轉為字節數組,通過參數化 SQL 插入:
private void openFileDialog1_FileOk(object sender, CancelEventArgs e) {
string filename = openFileDialog1.FileName;
using (SqlConnection conn = new SqlConnection("server=192.168.2.200;integrated security=sspi;database=northwind"))
using (SqlCommand cmd = new SqlCommand("INSERT INTO imgtable VALUES (@imgname, @imgData)", conn)) {
cmd.Parameters.Add("@imgname", SqlDbType.VarChar, 100).Value = filename;
SqlParameter imgParam = cmd.Parameters.Add("@imgData", SqlDbType.Image);
byte[] fileData;
using (FileStream fs = new FileStream(filename, FileMode.Open)) {
fileData = new byte[fs.Length];
fs.Read(fileData, 0, fileData.Length);
}
imgParam.Value = fileData;
conn.Open();
cmd.ExecuteNonQuery();
}
}
查詢符合條件的記錄,將 image 字段字節數組轉為 Bitmap 顯示:
private void button2_Click(object sender, EventArgs e) {
using (SqlConnection conn = new SqlConnection("server=192.168.2.200;integrated security=sspi;database=northwind"))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM imgtable WHERE imgname LIKE '%bmp%'", conn)) {
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader()) {
if (dr.Read()) {
byte[] imgBytes = (byte[])dr["imgData"];
using (MemoryStream ms = new MemoryStream(imgBytes)) {
pictureBox1.Image = Image.FromStream(ms);
}
}
}
}
}
同樣讀取字節數組,寫入本地文件:
private void button3_Click(object sender, EventArgs e) {
using (SqlConnection conn = new SqlConnection("server=192.168.2.200;integrated security=sspi;database=northwind"))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM imgtable WHERE imgname LIKE '%exe%'", conn)) {
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader()) {
if (dr.Read()) {
byte[] fileData = (byte[])dr["imgData"];
string savePath = Path.Combine(Application.StartupPath, "restored.exe");
File.WriteAllBytes(savePath, fileData);
Process.Start(savePath);
}
}
}
}
SqlDbType.Image 參數類型對應數據庫 image 列。using 確保流及時釋放。FILESTREAM(SQL Server 2008+),避免內存壓力。掌握此模式,即可在 .NET 應用中靈活實現文件的數據庫存儲與分發。