server| Data | Database The following code implements the ability to add pictures and text to a SQL Server database.
First, execute the following statement in SQL Query Analyzer to create tables and stored procedures.
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
The following is the complete code, and the copy can be run:
<%@ Import namespace= "System.IO"%>
<%@ Import namespace= "System.Data.SqlClient"%>
<%@ Import namespace= "System.Data"%>
<%@ Page language= "vb"%>
<HTML>
<HEAD>
<title> Insert Picture </title> to SQL Server
<script runat= "Server" >
Public Sub Addperson (sender as Object, E as EventArgs)
Dim Intimagesize as Int64
Dim Strimagetype as String
Dim ImageStream as Stream
' Get the size of the picture
Intimagesize = PersonImage.PostedFile.ContentLength
' Get picture type
Strimagetype = PersonImage.PostedFile.ContentType
' Read the picture
ImageStream = PersonImage.PostedFile.InputStream
Dim imagecontent (intimagesize) as Byte
Dim Intstatus as Integer
Intstatus = Imagestream.read (imagecontent, 0, Intimagesize)
' Create connection and command objects
Dim strcnn as String = "Data source=.;i Nitial catalog=mxh; User Id=sa; password=; "
Dim MyConnection as New SqlConnection (STRCNN)
Dim mycommand as New SqlCommand ("Sp_person_isp", MyConnection)
' Use stored procedures
myCommand.CommandType = CommandType.StoredProcedure
' Add parameters to a stored procedure
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 ("Add success!") ")
Catch Sqlexc as SqlException
Response.Write ("Add failed, Reason:" & Sqlexc.tostring ())
End Try
End Sub
</script>
</HEAD>
<body style= "font:9pt song body" >
<form enctype= "Multipart/form-data" runat= "Server" id= "Form1" >
<asp:table runat= "Server" width= "50%" borderwidth= "1" backcolor= "Beige" id= "Table1"
Font-name= "Song Body" font-size= "9pt" >
<asp:TableRow>
<asp:tablecell columnspan= "2" backcolor= "#ff0000" >
<asp:label forecolor= "#ffffff" font-bold= "True" runat= "Server" text= "Add New User" id= "Label1"/>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:tablecell horizontalalign= "Right" >
<asp:label runat= "Server" text= "name" 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= "e-mail" 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= "gender" id= "Label4"/>
</asp:TableCell>
<asp:TableCell>
<asp:radiobutton groupname= "Sex" text= "man" id= "Sexmale" runat= "Server"/>
<asp:radiobutton groupname= "Sex" text= "female" id= "Sexfemale" runat= "Server"/>
</asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:tablecell horizontalalign= "Right" >
<asp:label runat= "Server" text= "Birth date" 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= "photos" 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= "Add" onclick= "Addperson" runat= "Server" id= "Button1"/>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
</form>
</body>
</HTML>
