IDC资讯中心
下面的代码实现向sql server数据库添加图片和文字的功能。
首先，在sql查询分析器中执行下面的语句，以创建表和存储过程。
drop table person
go
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_isp
go
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)
end
go
下面就是完整的代码，拷贝即可运行：
<%@ 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>
