如何在sql中读取image http://expert.csdn.net/Expert/topic/2403/2403509.xml?temp=.2279474主 题: 交流--数据库中存/取文件 作 者: zjcxc (邹建) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我们可以把任意类型的文件保存到SQL Server中,在进行例子之前,先建立测试用表格,TestFile.sql:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestFiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[TestFiles]GOCREATE TABLE [dbo].[TestFiles] ( [id] [int] IDENTITY (1, 1) NOT NULL , [MyFileName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FileType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [MyFile] [image] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO下面创建上传表单:一旦提交了表单,我们使用HtmlInputFile类的PostedFile属性来访问我们上载的文件,用HttpPostedFile类的属性和方法来进行读取、保存上载文件和得到上载文件的其它信息。这里我们不使用SaveAs方法,因为它是用来保存文件的。我们要把数据保存到数据库中,我们使用InputStream属性,它用来初始化流来读取我们的数据。同时,我们使用ContentLength来读取文件大小,ContentType读取文件类型。然后创建byte数组,把文件流保存进该数组,然后保存到数据库即可。下面就是完整的代码【CS版本】UploadFile.aspx:<% @Page Language="C#" %><% @Import Namespace="System.IO" %><% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><script runat="server">public void UploadBtn_Click (Object sender, EventArgs e){//得到提交的文件Stream fileDataStream = MyFile.PostedFile.InputStream;//得到文件大小int fileLength = MyFile.PostedFile.ContentLength;//创建数组byte[] fileData = new byte[fileLength];//把文件流填充到数组fileDataStream.Read(fileData,0,fileLength);//得到文件名字string fileTitle = MyFileName.Value;//得到文件类型string fileType = MyFile.PostedFile.ContentType;//构建数据库连接,SQL语句,创建参数SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");SqlCommand command = new SqlCommand ("INSERT INTO TestFiles (MyFileName,MyFile,FileType)" + "VALUES (@MyFileName,@MyFile,@FileType)", connection);SqlParameter paramTitle = new SqlParameter ("@MyFileName", SqlDbType.VarChar,35); paramTitle.Value = fileTitle;command.Parameters.Add(paramTitle);SqlParameter paramData = new SqlParameter ("@MyFile", SqlDbType.Image);paramData.Value = fileData;command.Parameters.Add(paramData);SqlParameter paramType = new SqlParameter ("@FileType", SqlDbType.VarChar,25); paramType.Value = fileType;command.Parameters.Add(paramType);//打开连接,执行查询connection.Open();command.ExecuteNonQuery();connection.Close();Message.Text="你的文件已经成功上载";MyFileName.Value = "";}</script><hr><asp:label id="Message" Text="选择文件和文件名字:" runat="server"/><hr><form method="post" enctype="multipart/form-data" runat="server"><b>文件名字:</b><input id="MyFileName" type="text" runat="server"><P><b>文件:</b><input id="MyFile" type="file" runat="server"> <br/><br/><input type=submit value="开始上传" OnServerclick="UploadBtn_Click" runat="server"></form>一旦我们上载成功,我们可以对文件进行浏览:只需要设置页面的MIME类型,然后用Response对象的BinaryWrite()进行输出。ShowUploadFile.aspx<% @Page Language="C#" %><% @Import Namespace="System.IO" %><% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><script runat="server">private void Page_Load(Object sender, EventArgs e) {string sql="SELECT * FROM TestFiles";SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");SqlCommand command = new SqlCommand(sql, connection);connection.Open();FileList.DataSource = command.ExecuteReader();FileList.DataBind();connection.Close();}</script><form runat="server"><asp:DataGrid id="FileList" runat="server"BorderColor="orange" BorderWidth="2" CellPadding="4"AutoGenerateColumns="false" ShowHeader="true" Align="center"><HeaderStyle BorderColor="White" BackColor="black" ForeColor="White" Font-Bold="True" Font-Size="9" HorizontalAlign="Center"/><Columns><asp:TemplateColumn HeaderText="文件名字"><ItemTemplate><b><%# DataBinder.Eval(Container.DataItem, "MyFileName") %></b></ItemTemplate></asp:TemplateColumn><asp:TemplateColumn HeaderText="类型"><ItemTemplate><b><%# DataBinder.Eval(Container.DataItem, "FileType") %></b></ItemTemplate></asp:TemplateColumn><asp:TemplateColumn HeaderText="查看"><ItemTemplate><b><a href="ShowFile.aspx?ID=<%# DataBinder.Eval(Container.DataItem, "ID") %>">查看文件</a></b></ItemTemplate></asp:TemplateColumn></Columns></asp:DataGrid></form>ShowFile.aspx<% @Page Language="C#" %><% @Import Namespace="System.IO" %><% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><script runat="server">private void Page_Load(Object sender, EventArgs e) {string sql="SELECT * FROM TestFiles WHERE ID = '" + Request.QueryString["ID"] + "'";SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");SqlCommand command = new SqlCommand(sql, connection);connection.Open();SqlDataReader dr = command.ExecuteReader();if(dr.Read()){ Response.Clear(); Response.AddHeader("Content-Type",dr["FileType"].ToString()); Response.BinaryWrite((byte[])dr["MyFile"]);}dr.Close();connection.Close();}</script> '==================================================================--'' 用ASP实现无组件上传/下载文件'' 功能简介 ' 将上传的文件数据保存到数据库中,可以处理表单中的多个上传文件的情况' 适用于各种数据库,使用ADO的方法连接数据库' 本示例中使用的是ACCESS数据库:zj.mdb' 表:tb_img(id int(自增列),path text(255) 保存上传文件的目录' ,fname text(250) 保存上传的文件名,img ole对象 保存上传的文件内容' ,type text(250) 保存上传的文件类型'' 邹建 2003.10'=================================================================='=================================================================='' 上传文件的HTML页: zj_up.htm''==================================================================<html><head><title>文件上传保存到数据库中</title></head><body><form name="form1" enctype="multipart/form-data" method="post" action="zj_up.asp"> <p> <input type="file" name="file"> <input type="submit" name="Submit" value="上传"> </p></form></body></html>'=================================================================='' 上传文件保存到数据库的ASP页: zj_up.asp''==================================================================<%Response.Expires=0Function f_Bin2Str(ByVal sBin) Dim iI, iLen, iChr, iRe iRe = "" If Not IsNull(sBin) Then iLen = LenB(sBin) For iI = 1 To iLen iChr = MidB(sBin, iI, 1) If AscB(iChr) > 127 Then iRe = iRe & Chr(AscW(MidB(sBin, iI + 1, 1) & iChr)) iI = iI + 1 Else iRe = iRe & Chr(AscB(iChr)) End If Next End If f_Bin2Str = iReEnd FunctioniConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _ ";Data Source=" & server.mappath("zj.mdb")iSql="tb_img"set iRe=Server.CreateObject("ADODB.Recordset")iRe.Open iSql,iConcStr,1,3iLen=Request.TotalBytessBin=Request.BinaryRead(iLen)iCrlf1 = ChrB(13) & ChrB(10)iCrlf2 = iCrlf1 & iCrlf1iLen = InStrB(1, sBin, iCrlf1) - 1iSpc = LeftB(sBin, iLen)sBin = MidB(sBin, iLen + 34)iPos1 = InStrB(sBin, iCrlf2) - 1While iPos1 > 0 iStr = f_Bin2Str(LeftB(sBin, iPos1)) iPos1 = iPos1 + 5 iPos2 = InStrB(iPos1, sBin, iSpc) iPos3 = InStr(iStr, "; filename=""") + 12 If iPos3 > 12 Then iStr = Mid(iStr, iPos3) iPos3 = InStr(iStr, Chr(13) & Chr(10) & "Content-Type: ") - 2 iFn = Left(iStr, iPos3) If iFn <> "" Then iRe.AddNew ire("path")=left(iFn,instrrev(iFn,"\")) iRe("fname") = mid(iFn,instrrev(iFn,"\")+1) iRe("type") = Mid(iStr, iPos3 + 18) iRe("img").AppendChunk MidB(sBin, iPos1, iPos2 - iPos1) iRe.Update End If End If sBin = MidB(sBin, iPos2 + iLen + 34) iPos1 = InStrB(sBin, iCrlf2) - 1WendiRe.closeset iRe=Nothing%>'=================================================================='' 下载数据的ASP页: zj_down.asp''==================================================================<%Response.Buffer=trueResponse.CleariConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _ ";Data Source=" & server.mappath("zj.mdb")set iRe=server.createobject("adodb.recordset")iSql="tb_img"iRe.open iSql,iconcstr,1,1Response.ContentType=ire("type")Response.BinaryWrite iRe("img")iRe.closeset iRe=Nothing%> 数据库热备问题? 大虾们进来看看 一列的时间怎么统计 [提问]--库存结果(上月结存、本月入库、本月出库、库存)问题? 出家提一个问题:为什么opendatasource在sql sever2000的查询分析器里运行很快,在delphi的adoquery里运行就特别慢? 求助:讨论DATEPART和DATENAME 请教一SQL语句 [求助]我应该如何设置列的计算公式? 在线等,告诉请进 請問表結构如何進行翻轉? 为什么sum的结果不为0 如何在查询结果前增加一列序号? 开发大型的数据库软件时,数据库用什么工具来设计的?
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestFiles]
GOCREATE TABLE [dbo].[TestFiles] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[MyFileName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FileType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[MyFile] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO下面创建上传表单:一旦提交了表单,我们使用HtmlInputFile类的PostedFile属性来访问我们上载的文件,用HttpPostedFile类的属性和方法来进行读取、保存上载文件和得到上载文件的其它信息。这里我们不使用SaveAs方法,因为它是用来保存文件的。我们要把数据保存到数据库中,我们使用InputStream属性,它用来初始化流来读取我们的数据。同时,我们使用ContentLength来读取文件大小,ContentType读取文件类型。然后创建byte数组,把文件流保存进该数组,然后保存到数据库即可。下面就是完整的代码【CS版本】UploadFile.aspx:<% @Page Language="C#" %>
<% @Import Namespace="System.IO" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %><script runat="server">
public void UploadBtn_Click (Object sender, EventArgs e){//得到提交的文件
Stream fileDataStream = MyFile.PostedFile.InputStream;//得到文件大小
int fileLength = MyFile.PostedFile.ContentLength;//创建数组
byte[] fileData = new byte[fileLength];//把文件流填充到数组
fileDataStream.Read(fileData,0,fileLength);//得到文件名字
string fileTitle = MyFileName.Value;//得到文件类型
string fileType = MyFile.PostedFile.ContentType;//构建数据库连接,SQL语句,创建参数
SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");
SqlCommand command = new SqlCommand ("INSERT INTO TestFiles (MyFileName,MyFile,FileType)" +
"VALUES (@MyFileName,@MyFile,@FileType)", connection);SqlParameter paramTitle = new SqlParameter ("@MyFileName", SqlDbType.VarChar,35);
paramTitle.Value = fileTitle;
command.Parameters.Add(paramTitle);SqlParameter paramData = new SqlParameter ("@MyFile", SqlDbType.Image);
paramData.Value = fileData;
command.Parameters.Add(paramData);SqlParameter paramType = new SqlParameter ("@FileType", SqlDbType.VarChar,25);
paramType.Value = fileType;
command.Parameters.Add(paramType);//打开连接,执行查询
connection.Open();
command.ExecuteNonQuery();
connection.Close();Message.Text="你的文件已经成功上载";
MyFileName.Value = "";
}
</script>
<hr>
<asp:label id="Message" Text="选择文件和文件名字:" runat="server"/>
<hr>
<form method="post" enctype="multipart/form-data" runat="server">
<b>文件名字:</b><input id="MyFileName" type="text" runat="server">
<P>
<b>文件:</b><input id="MyFile" type="file" runat="server">
<br/><br/>
<input type=submit value="开始上传" OnServerclick="UploadBtn_Click" runat="server">
</form>一旦我们上载成功,我们可以对文件进行浏览:只需要设置页面的MIME类型,然后用Response对象的BinaryWrite()进行输出。ShowUploadFile.aspx<% @Page Language="C#" %>
<% @Import Namespace="System.IO" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
private void Page_Load(Object sender, EventArgs e) {
string sql="SELECT * FROM TestFiles";
SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
FileList.DataSource = command.ExecuteReader();
FileList.DataBind();
connection.Close();
}
</script><form runat="server">
<asp:DataGrid id="FileList" runat="server"
BorderColor="orange" BorderWidth="2" CellPadding="4"
AutoGenerateColumns="false" ShowHeader="true" Align="center">
<HeaderStyle BorderColor="White" BackColor="black" ForeColor="White"
Font-Bold="True" Font-Size="9" HorizontalAlign="Center"/>
<Columns>
<asp:TemplateColumn HeaderText="文件名字">
<ItemTemplate>
<b>
<%# DataBinder.Eval(Container.DataItem, "MyFileName") %>
</b>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="类型">
<ItemTemplate>
<b>
<%# DataBinder.Eval(Container.DataItem, "FileType") %>
</b>
</ItemTemplate>
</asp:TemplateColumn><asp:TemplateColumn HeaderText="查看">
<ItemTemplate>
<b>
<a href="ShowFile.aspx?ID=<%# DataBinder.Eval(Container.DataItem, "ID") %>">查看文件</a>
</b>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>ShowFile.aspx<% @Page Language="C#" %>
<% @Import Namespace="System.IO" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
private void Page_Load(Object sender, EventArgs e) {
string sql="SELECT * FROM TestFiles WHERE ID = '" + Request.QueryString["ID"] + "'";
SqlConnection connection = new SqlConnection("Server=.;uid=sa;pwd=;Database=TestUploadFile");
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if(dr.Read()){
Response.Clear();
Response.AddHeader("Content-Type",dr["FileType"].ToString());
Response.BinaryWrite((byte[])dr["MyFile"]);
}
dr.Close();
connection.Close();
}
</script>
'
' 用ASP实现无组件上传/下载文件
'
' 功能简介
' 将上传的文件数据保存到数据库中,可以处理表单中的多个上传文件的情况
' 适用于各种数据库,使用ADO的方法连接数据库
' 本示例中使用的是ACCESS数据库:zj.mdb
' 表:tb_img(id int(自增列),path text(255) 保存上传文件的目录
' ,fname text(250) 保存上传的文件名,img ole对象 保存上传的文件内容
' ,type text(250) 保存上传的文件类型
'
' 邹建 2003.10
'==================================================================
'==================================================================
'
' 上传文件的HTML页: zj_up.htm
'
'==================================================================
<html>
<head>
<title>文件上传保存到数据库中</title>
</head>
<body>
<form name="form1" enctype="multipart/form-data" method="post" action="zj_up.asp">
<p>
<input type="file" name="file">
<input type="submit" name="Submit" value="上传">
</p>
</form>
</body>
</html>
'==================================================================
'
' 上传文件保存到数据库的ASP页: zj_up.asp
'
'==================================================================
<%
Response.Expires=0
Function f_Bin2Str(ByVal sBin)
Dim iI, iLen, iChr, iRe
iRe = ""
If Not IsNull(sBin) Then
iLen = LenB(sBin)
For iI = 1 To iLen
iChr = MidB(sBin, iI, 1)
If AscB(iChr) > 127 Then
iRe = iRe & Chr(AscW(MidB(sBin, iI + 1, 1) & iChr))
iI = iI + 1
Else
iRe = iRe & Chr(AscB(iChr))
End If
Next
End If
f_Bin2Str = iRe
End Function
iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=" & server.mappath("zj.mdb")
iSql="tb_img"
set iRe=Server.CreateObject("ADODB.Recordset")
iRe.Open iSql,iConcStr,1,3
iLen=Request.TotalBytes
sBin=Request.BinaryRead(iLen)
iCrlf1 = ChrB(13) & ChrB(10)
iCrlf2 = iCrlf1 & iCrlf1
iLen = InStrB(1, sBin, iCrlf1) - 1
iSpc = LeftB(sBin, iLen)
sBin = MidB(sBin, iLen + 34)
iPos1 = InStrB(sBin, iCrlf2) - 1
While iPos1 > 0
iStr = f_Bin2Str(LeftB(sBin, iPos1))
iPos1 = iPos1 + 5
iPos2 = InStrB(iPos1, sBin, iSpc)
iPos3 = InStr(iStr, "; filename=""") + 12
If iPos3 > 12 Then
iStr = Mid(iStr, iPos3)
iPos3 = InStr(iStr, Chr(13) & Chr(10) & "Content-Type: ") - 2
iFn = Left(iStr, iPos3)
If iFn <> "" Then
iRe.AddNew
ire("path")=left(iFn,instrrev(iFn,"\"))
iRe("fname") = mid(iFn,instrrev(iFn,"\")+1)
iRe("type") = Mid(iStr, iPos3 + 18)
iRe("img").AppendChunk MidB(sBin, iPos1, iPos2 - iPos1)
iRe.Update
End If
End If
sBin = MidB(sBin, iPos2 + iLen + 34)
iPos1 = InStrB(sBin, iCrlf2) - 1
Wend
iRe.close
set iRe=Nothing
%>'==================================================================
'
' 下载数据的ASP页: zj_down.asp
'
'==================================================================
<%
Response.Buffer=true
Response.CleariConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=" & server.mappath("zj.mdb")
set iRe=server.createobject("adodb.recordset")
iSql="tb_img"
iRe.open iSql,iconcstr,1,1
Response.ContentType=ire("type")
Response.BinaryWrite iRe("img")iRe.close
set iRe=Nothing
%>