使用OleDbCommand對(duì)象更新SQL Server中的二進(jìn)制文件



作者 朱二


利用ADO.NET中的OleDbConnection\OleDbCommand 可以方便的對(duì)SQL Server中的二進(jìn)制文件進(jìn)行更新操作，下面是詳細(xì)的代碼演示

演示環(huán)境：

數(shù)據(jù)庫機(jī)器名 ：s_test
登陸名 ：sa
密碼 ：7890
數(shù)據(jù)庫名 db_test

下面建立一個(gè)表：
create table tb_test(id int identity(1,1),photo image ,constraint pk_tb_test primary key(id))
一、將硬盤上的文件保存至數(shù)據(jù)庫(C#)


//----------------------------------------------------------
//----------------------------------------------------------
//下面的示例將c:\1.txt文件保存至數(shù)據(jù)庫的tb_test表中
//----------------------------------------------------------
//----------------------------------------------------------

using System;
using System.IO;?
using System.Data;
using System.Data.OleDb;

class image_test
{
[STAThread]
static void Main(string[] args)
{
try
{
//初始化OleDbConnection和OleDbCommand
OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=s_test;user id=sa;password=7890;initial catalog=db_test");
OleDbCommand cmd = new OleDbCommand("INSERT tb_test(photo) VALUES(?)",cn);

//打開文件
FileStream fs = new FileStream("c:\\1.txt", FileMode.Open, FileAccess.Read);
Byte[] b = new Byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();

//打開連接
OleDbParameter prm = new OleDbParameter("@photo",OleDbType.VarBinary ,b.Length,?
ParameterDirection.Input, false, 0, 0, null,DataRowVersion.Current, b);
cmd.Parameters.Add(prm);
cn.Open();

//執(zhí)行
if (cmd.ExecuteNonQuery() == 1)
Console.WriteLine("OK");
else
Console.WriteLine("Fail");?
cn.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message );
}
}
}?

三、更新數(shù)據(jù)庫中保存的文件

//----------------------------------------------------------
//----------------------------------------------------------
//下面的示例用將數(shù)據(jù)庫的tb_test表中ID=1的記錄的photo更新為c:\1.txt
//----------------------------------------------------------
//----------------------------------------------------------

using System;
using System.IO;?
using System.Data;
using System.Data.OleDb;

class image_test
{
[STAThread]
static void Main(string[] args)
{
try
{
//初始化OleDbConnection和OleDbCommand
OleDbConnection cn = new OleDbConnection("provider=sqloledb;server=s_test;user id=sa;password=7890;initial catalog=db_test");
OleDbCommand cmd = new OleDbCommand("UPDATE tb_test SET photo= ? WHERE ID=1",cn);

//打開文件
FileStream fs = new FileStream("c:\\1.txt", FileMode.Open, FileAccess.Read);
Byte[] b = new Byte[fs.Length];
fs.Read(b, 0, b.Length);
fs.Close();

//打開連接
OleDbParameter prm = new OleDbParameter("@photo",OleDbType.VarBinary ,b.Length,?
ParameterDirection.Input, false, 0, 0, null,DataRowVersion.Current, b);
cmd.Parameters.Add(prm);
cn.Open();

//執(zhí)行
if (cmd.ExecuteNonQuery() == 1)
Console.WriteLine("OK");
else
Console.WriteLine("Fail");?
cn.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message );
}
}
}