利用ADO.NET中的OleDbConnection\OleDbCommand 可以方便的对SQL Server中的二进制文件进行更新操作,下面是详细的代码演示 演示环境:数据库机器名 :s_test 登陆名 :sa 密码 :7890 数据库名 db_test下面建立一个表: create table tb_test(id int identity(1,1),photo image ,constraint pk_tb_test primary key(id))一、将硬盘上的文件保存至数据库(C#)//---------------------------------------------------------- //---------------------------------------------------------- //下面的示例将c:\1.txt文件保存至数据库的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(); //执行 if (cmd.ExecuteNonQuery() == 1) Console.WriteLine("OK"); else Console.WriteLine("Fail"); cn.Close(); } catch(Exception ex) { Console.WriteLine(ex.Message ); } } } 三、更新数据库中保存的文件//---------------------------------------------------------- //---------------------------------------------------------- //下面的示例用将数据库的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(); //执行 if (cmd.ExecuteNonQuery() == 1) Console.WriteLine("OK"); else Console.WriteLine("Fail"); cn.Close(); } catch(Exception ex) { Console.WriteLine(ex.Message ); } } }
利用Dataset可以方便的对SQL Server中的二进制文件进行存取与更新操作,下面是详细的代码演示 演示环境:数据库机器名 :s_test 登陆名 :sa 密码 :7890 数据库名 db_test下面建立一个表: create table tb_test(id int identity(1,1),photo image ,constraint pk_tb_test primary key(id))一、将硬盘上的文件保存至数据库(VB.NET)'---------------------------------------------------------- '---------------------------------------------------------- '下面的示例将c:\1.jpg文件保存至数据库的tb_test表中 '---------------------------------------------------------- '----------------------------------------------------------Imports System.IO Imports System.Data.SqlClientPublic Class image Shared Sub Main() '读入文件数据 Dim fs = New FileStream("c:\1.jpg", IO.FileMode.Open, IO.FileAccess.Read) Dim imgData(fs.Length - 1) As Byte fs.Read(imgData, 0, fs.Length - 1) fs.close() Dim tempConnection As New SqlConnection Dim tempAdapter As SqlDataAdapter Dim tempDataset As New DataSet '打开数据库连接 tempConnection.ConnectionString = "server=s_Test;uid=sa;pwd=7890;database=db_test" tempConnection.Open() tempAdapter = New SqlDataAdapter("SELECT * FROM tb_test WHERE 1=0", tempConnection) Dim cb As New SqlCommandBuilder(tempAdapter) tempAdapter.Fill(tempDataset) '插入一条记录 Dim tempDataRow As DataRow tempDataRow = tempDataset.Tables(0).NewRow() tempDataRow("photo") = imgData tempDataset.Tables(0).Rows.Add(tempDataRow) tempAdapter.Update(tempDataset) tempConnection.Close() End Sub End Class 二、将数据库中的文件保存至硬盘(VB.NET) '---------------------------------------------------------- '---------------------------------------------------------- '下面的示例将数据库的tb_test表中第一条记录的photo保存至c:\2.jpg '---------------------------------------------------------- '----------------------------------------------------------Imports System.IO Imports System.Data.SqlClientPublic Class image Shared Sub Main() Dim tempConnection As New SqlConnection Dim tempAdapter As SqlDataAdapter Dim tempDataset As New DataSet '打开数据库连接,取出数据 tempConnection.ConnectionString = "server=s_test;uid=sa;pwd=7890;database=db_test" tempConnection.Open() tempAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM tb_test", tempConnection) tempAdapter.Fill(tempDataset) tempConnection.Close() If tempDataset.Tables(0).Rows.Count > 0 Then '将文件保存到硬盘文件c:\2.jpg Dim imgData() As Byte imgData = tempDataset.Tables(0).Rows(0).Item("photo") Dim fs As FileStream fs = File.Create("c:\2.jpg", imgData.Length - 1) fs.Write(imgData, 0, imgData.Length - 1) fs.Close() End If End Sub End Class三、更新数据库中保存的文件 '---------------------------------------------------------- '---------------------------------------------------------- '下面的示例用将数据库的tb_test表中第一条记录的photo更新为c:\2.jpg '---------------------------------------------------------- '---------------------------------------------------------- Imports System.IO Imports System.Data.SqlClientPublic Class image Shared Sub Main() '读取文件 Dim fs = New System.IO.FileStream("c:\2.jpg", IO.FileMode.Open, IO.FileAccess.Read) Dim imgData(fs.Length - 1) As Byte fs.Read(imgData, 0, fs.Length - 1) fs.close() Dim tempConnection As New SqlConnection Dim tempAdapter As SqlDataAdapter Dim tempDataset As New DataSet '打开数据库连接,取出数据 tempConnection.ConnectionString = "server=s_test;uid=sa;pwd=7890;database=db_test" tempConnection.Open() tempAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM tb_test", tempConnection) tempAdapter.Fill(tempDataset) '更新数据 Dim cb As New SqlCommandBuilder(tempAdapter) tempDataset = New DataSet tempAdapter.Fill(tempDataset) tempDataset.Tables(0).Rows(0).Item("photo") = imgData tempAdapter.Update(tempDataset) tempConnection.Close() End Sub End Class总结: 利用Dataset可以方便的对SQL Server中的二进制文件进行存取与更新操作,虽不是最有效的方法,但通过文本的介绍,可使初学者多掌握一种对数据库中的二进制文件进行操作的方法,希望对开发者有所帮助。
演示环境:数据库机器名 :s_test
登陆名 :sa
密码 :7890
数据库名 db_test下面建立一个表:
create table tb_test(id int identity(1,1),photo image ,constraint pk_tb_test primary key(id))一、将硬盘上的文件保存至数据库(C#)//----------------------------------------------------------
//----------------------------------------------------------
//下面的示例将c:\1.txt文件保存至数据库的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(); //执行
if (cmd.ExecuteNonQuery() == 1)
Console.WriteLine("OK");
else
Console.WriteLine("Fail");
cn.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message );
}
}
} 三、更新数据库中保存的文件//----------------------------------------------------------
//----------------------------------------------------------
//下面的示例用将数据库的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(); //执行
if (cmd.ExecuteNonQuery() == 1)
Console.WriteLine("OK");
else
Console.WriteLine("Fail");
cn.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message );
}
}
}
演示环境:数据库机器名 :s_test
登陆名 :sa
密码 :7890
数据库名 db_test下面建立一个表:
create table tb_test(id int identity(1,1),photo image ,constraint pk_tb_test primary key(id))一、将硬盘上的文件保存至数据库(VB.NET)'----------------------------------------------------------
'----------------------------------------------------------
'下面的示例将c:\1.jpg文件保存至数据库的tb_test表中
'----------------------------------------------------------
'----------------------------------------------------------Imports System.IO
Imports System.Data.SqlClientPublic Class image
Shared Sub Main() '读入文件数据
Dim fs = New FileStream("c:\1.jpg", IO.FileMode.Open, IO.FileAccess.Read)
Dim imgData(fs.Length - 1) As Byte
fs.Read(imgData, 0, fs.Length - 1)
fs.close() Dim tempConnection As New SqlConnection
Dim tempAdapter As SqlDataAdapter
Dim tempDataset As New DataSet
'打开数据库连接
tempConnection.ConnectionString = "server=s_Test;uid=sa;pwd=7890;database=db_test"
tempConnection.Open()
tempAdapter = New SqlDataAdapter("SELECT * FROM tb_test WHERE 1=0", tempConnection)
Dim cb As New SqlCommandBuilder(tempAdapter)
tempAdapter.Fill(tempDataset)
'插入一条记录
Dim tempDataRow As DataRow
tempDataRow = tempDataset.Tables(0).NewRow()
tempDataRow("photo") = imgData
tempDataset.Tables(0).Rows.Add(tempDataRow)
tempAdapter.Update(tempDataset)
tempConnection.Close()
End Sub
End Class
二、将数据库中的文件保存至硬盘(VB.NET)
'----------------------------------------------------------
'----------------------------------------------------------
'下面的示例将数据库的tb_test表中第一条记录的photo保存至c:\2.jpg
'----------------------------------------------------------
'----------------------------------------------------------Imports System.IO
Imports System.Data.SqlClientPublic Class image
Shared Sub Main() Dim tempConnection As New SqlConnection
Dim tempAdapter As SqlDataAdapter
Dim tempDataset As New DataSet
'打开数据库连接,取出数据
tempConnection.ConnectionString = "server=s_test;uid=sa;pwd=7890;database=db_test"
tempConnection.Open()
tempAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM tb_test", tempConnection)
tempAdapter.Fill(tempDataset)
tempConnection.Close() If tempDataset.Tables(0).Rows.Count > 0 Then
'将文件保存到硬盘文件c:\2.jpg
Dim imgData() As Byte
imgData = tempDataset.Tables(0).Rows(0).Item("photo")
Dim fs As FileStream
fs = File.Create("c:\2.jpg", imgData.Length - 1)
fs.Write(imgData, 0, imgData.Length - 1)
fs.Close()
End If
End Sub
End Class三、更新数据库中保存的文件
'----------------------------------------------------------
'----------------------------------------------------------
'下面的示例用将数据库的tb_test表中第一条记录的photo更新为c:\2.jpg
'----------------------------------------------------------
'----------------------------------------------------------
Imports System.IO
Imports System.Data.SqlClientPublic Class image
Shared Sub Main() '读取文件
Dim fs = New System.IO.FileStream("c:\2.jpg", IO.FileMode.Open, IO.FileAccess.Read)
Dim imgData(fs.Length - 1) As Byte
fs.Read(imgData, 0, fs.Length - 1)
fs.close() Dim tempConnection As New SqlConnection
Dim tempAdapter As SqlDataAdapter
Dim tempDataset As New DataSet
'打开数据库连接,取出数据
tempConnection.ConnectionString = "server=s_test;uid=sa;pwd=7890;database=db_test"
tempConnection.Open()
tempAdapter = New SqlDataAdapter("SELECT TOP 1 * FROM tb_test", tempConnection)
tempAdapter.Fill(tempDataset)
'更新数据
Dim cb As New SqlCommandBuilder(tempAdapter)
tempDataset = New DataSet
tempAdapter.Fill(tempDataset)
tempDataset.Tables(0).Rows(0).Item("photo") = imgData
tempAdapter.Update(tempDataset)
tempConnection.Close()
End Sub
End Class总结:
利用Dataset可以方便的对SQL Server中的二进制文件进行存取与更新操作,虽不是最有效的方法,但通过文本的介绍,可使初学者多掌握一种对数据库中的二进制文件进行操作的方法,希望对开发者有所帮助。