下面的代碼實現向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>
菜鳥學堂：
