insert into test(image) values"("+(byte[] image)+")"
commandtext="select image from test where "
reader=com.excutereader();
byte[] imag=reader("image");
commandtext="select image from test where "
reader=com.excutereader();
byte[] imag=reader("image");
解决方案 »
- asp.net 建立pop3邮箱
- 当前上下文中找不到控件名称
- 关于报表的问题
- .net生成静态页面
- 隐藏按钮和模拟点击
- 如何按“注销按纽(button)”删除客户端的cookie文件
- 从数据库查询得到的结果绑定到dropdownlist时发生奇怪的问题???
- 高手解释一下e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem???
- asp.net 后台如何调用OCX控件的事件?
- Word的COM组件调用时多进程问题
- 在调用 WordApp.Documents.Add() 时报 HRESULT 0x800A175D 的错,怎么办呀?
- 请问能从win2000pro上直接升级安装win2000 server吗?
下面的代码实现向SQL Server数据库添加图片和文字的功能。首先,在SQL查询分析器中执行下面的语句,以创建表和存储过程。Drop Table PersonGo
Create Table Person
(
PersonID Int Identity,
PersonEmail Varchar(255),
PersonName Varchar(255),
PersonSex Char(1),
PersonDOB DateTime,
PersonImage Image,
PersonImageType Varchar(255)
)Drop Proc sp_person_ispGo
Create Proc sp_person_isp
@PersonEmail Varchar(255),
@PersonName Varchar(255),
@PersonSex Char(1),
@PersonDOB DateTime,
@PersonImage Image,
@PersonImageType Varchar(255)
As
Begin
Insert into Person
(PersonEmail, PersonName, PersonSex,
PersonDOB, PersonImage, PersonImageType)
Values
(@PersonEmail, @PersonName, @PersonSex,
@PersonDOB, @PersonImage, @PersonImageType)
EndGo下面就是完整的代码,拷贝即可运行:<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<%@ Page Language="vb" %>
<HTML>
<HEAD>
<title>向SQL Server插入图片</title>
<script runat="server">
Public Sub AddPerson(sender As Object, e As EventArgs)
Dim intImageSize As Int64
Dim strImageType As String
Dim ImageStream As Stream
' 获得图片的大小
intImageSize = PersonImage.PostedFile.ContentLength
' 获得图片类型
strImageType = PersonImage.PostedFile.ContentType
'读取图片
ImageStream = PersonImage.PostedFile.InputStream
Dim ImageContent(intImageSize) As Byte
Dim intStatus As Integer
intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
' 创建Connection和Command对象
Dim strCnn As String = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
Dim myConnection As New SqlConnection(strCnn)
Dim myCommand As New SqlCommand("sp_person_isp", myConnection)
' 使用存储过程
myCommand.CommandType = CommandType.StoredProcedure
' 向存储过程添加参数
Dim prmEmail As New SqlParameter("@PersonEmail", SqlDbType.VarChar, 255)
prmEmail.Value = txtPersonEmail.Text
myCommand.Parameters.Add(prmEmail) Dim prmName As New SqlParameter("@PersonName", SqlDbType.VarChar, 255)
prmName.Value = txtPersonName.Text
myCommand.Parameters.Add(prmName)
Dim prmSex As New SqlParameter("@PersonSex", SqlDbType.Char, 1) If sexMale.Checked Then
prmSex.Value = "M"
Else
prmSex.Value = "F"
End If
myCommand.Parameters.Add(prmSex)
Dim prmPersonDOB As New SqlParameter("@PersonDOB", SqlDbType.DateTime)
prmPersonDOB.Value = txtPersonDob.Text
myCommand.Parameters.Add(prmPersonDOB) Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
prmPersonImage.Value = ImageContent
myCommand.Parameters.Add(prmPersonImage) Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)
prmPersonImageType.Value = strImageType
myCommand.Parameters.Add(prmPersonImageType) Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Response.Write("添加成功!")
Catch SQLexc As SqlException
Response.Write("添加失败,原因:" & SQLexc.ToString())
End Try
End Sub
</script>
</HEAD>
<body style="FONT: 9pt 宋体">
<form enctype="multipart/form-data" runat="server" ID="Form1">
<asp:Table Runat="server" Width="50%" BorderWidth="1" BackColor="Beige" ID="Table1"
Font-Name="宋体" Font-Size="9pt">
<asp:TableRow>
<asp:TableCell ColumnSpan="2" BackColor="#ff0000">
<asp:Label ForeColor="#ffffff" font-bold="True" Runat="server" Text="添加新用户" ID="Label1" />
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<asp:Label Runat="server" Text="姓名" ID="Label2" />
</asp:TableCell>
<asp:TableCell>
<asp:TextBox id="txtPersonName" Runat="server" />
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<asp:Label Runat="server" Text="电子邮件" ID="Label3" />
</asp:TableCell>
<asp:TableCell>
<asp:TextBox id="txtPersonEmail" Runat="server" />
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<asp:Label Runat="server" Text="性别" ID="Label4"/>
</asp:TableCell>
<asp:TableCell>
<asp:RadioButton GroupName="sex" Text="男" ID="sexMale" Runat="server" />
<asp:RadioButton GroupName="sex" Text="女" ID="sexFeMale" Runat="server" />
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<asp:Label Runat="server" Text="出生日期" ID="Label5"/>
</asp:TableCell>
<asp:TableCell>
<asp:TextBox id="txtPersonDOB" Runat="server" />
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell HorizontalAlign="Right">
<asp:Label Runat="server" Text="照片" ID="Label6"/>
</asp:TableCell>
<asp:TableCell>
<input type="file" id="PersonImage" runat="server" NAME="PersonImage" /></asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell ColumnSpan="2" HorizontalAlign="Center">
<asp:Button Text=" 添 加 " OnClick="AddPerson" Runat="server" ID="Button1"/>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
</form>
</body>
</HTML>
下面的代码实现从SQL Server数据库提取图片并显示在DataGrid的功能。下面就是完整的代码,拷贝即可运行:<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>用户列表</title>
<script runat=server>
Sub Page_Load(sender As Object, e As EventArgs)
If Not Page.IsPostBack Then
BindGrid()
End If
End SubPrivate Sub BindGrid()
Dim strCnn As String = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
Dim myConnection As SqlConnection = New SqlConnection(strCnn)
Dim myCommand As SqlCommand = New SqlCommand("SELECT * FROM Person", myConnection)
myCommand.CommandType = CommandType.Text
Try
myConnection.Open()
DG_Persons.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DG_Persons.DataBind()
Catch SQLexc As SqlException
Response.Write("Error occured while Generating Data. Error is " & SQLexc.ToString())
End Try
End SubFunction FormatURL(strArgument) as String
Return ("ReadImage.aspx?id=" & strArgument)
End Function
</script>
</head>
<body style="font: 9pt 宋体">
<h3 align=center>从数据库中取得照片并显示在DataGrid中</h3>
<form id="Form1" method="post" runat="server">
<asp:DataGrid ID="DG_Persons" AutoGenerateColumns=False Width="99%"
HeaderStyle-BackColor="#ff0000" HeaderStyle-Font-Bold="True" HeaderStyle-ForeColor="#ffffff"
ItemStyle-BackColor=Beige BorderColor="#000000" Runat=server HeaderStyle-HorizontalAlign=Center>
<Columns>
<asp:TemplateColumn HeaderText="姓名">
<ItemTemplate>
<asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonName") %>' ID="Label1"/>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="电子邮件">
<ItemTemplate>
<asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonEmail") %>' ID="Label2"/>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="性别">
<ItemTemplate>
<asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonSex") %>' ID="Label3"/>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="出生日期">
<ItemTemplate>
<asp:Label Runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, "PersonDOB") %>' ID="Label4"/>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="照片">
<ItemTemplate>
<asp:Image Runat=server ID="Image1" Width="150" Height="125"
ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "PersonID")) %>' />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>ReadImage.aspx<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<script runat=server>
Public Sub Page_Load(sender As Object, e As EventArgs)
Dim strImageID as String = Request.QueryString("id")
Dim myConnection As New SqlConnection("Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;")
Dim myCommand As New SqlCommand("Select PersonImageType, PersonImage from Person Where PersonID=" _
+ strImageID, myConnection) Try
myConnection.Open()
Dim myDataReader as SqlDataReader
myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Do While (myDataReader.Read())
Response.ContentType = myDataReader.Item("PersonImageType")
Response.BinaryWrite(myDataReader.Item("PersonImage"))
Loop
myConnection.Close()
Catch SQLexc As SqlException
End Try
End Sub
</script>
</HEAD>
<body>
<form runat="server" ID="Form1"></form>
</body>
</HTML>
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>另外需要注意的是:对exe,zip文件等还要进一步进行处理,以直接进行下载。
Dim ms As New MemoryStream()
If Not IsNothing(PictureBox1.Image) Then
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
End If
Dim picture() As Byte
picture = ms.GetBufferinsert 语句即可取:
Dim ms As MemoryStream
Dim etb As DataTable
Dim erw As DataRow
etb = myedataset.Tables(0)'myedataset是你填充的dataset
For Each erw In etb.Rows
If Not IsDBNull(erw("picture")) Then Dim arrPicture() As Byte = CType(erw("picture"), Byte())
ms = New MemoryStream(arrPicture)
PictureBox1.Image = Image.FromStream(ms)
End If
next
OdbcConnection Con = null;
System.IO.FileStream Fs = null;
try
{
//得到该图片的二进制流
Fs = new System.IO.FileStream("d:\\aaa.bmp",System.IO.FileMode.Open);
System.IO.BinaryReader Br = new System.IO.BinaryReader(Fs);
byte[] curImage = Br.ReadBytes((int)Fs.Length); //连接数据库
Con = new OdbcConnection("DSN=LOProSource;UID=sa;PWD=sa;");
string sInsert = "INSERT INTO aaa(aaa) VALUES(@ABCD)";
OdbcCommand Com = new OdbcCommand(sInsert,Con); OdbcParameter ParaImage = new OdbcParameter("@ABCD",OdbcType.Binary);
ParaImage.Value = curImage;
Com.Parameters.Add(ParaImage); Con.Open();
Com.ExecuteNonQuery();
Con.Close(); }
catch(Exception e1)
{
if(Fs != null)
{
Fs.Close();
}
if(Con != null)
{
Con.Close();
}
MessageBox.Show(e1.Message);
}//运行结果
出错:无效的精度值