下面的代码实现向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
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 >
<% ... @ 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 >