向SQL Server数据库添加图片

2022-07-15 80酷酷网 80kuku.com

　　server|数据|数据库下面的代码实现向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 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>