向SQLServer数据库添加图片和文字

首先，在sql查询分析器中执行下面的sql语句，以创建表和存储过程。

create TABLE Photos (
?[name] varchar(50),
?[photo] image NULL
)
GO

create PROCEDURE sp_InsertPhoto
?@name AS VARCHAR(50),
?@image AS IMAGE
?AS
INSERT INTO Photos ([name],? [photo])
VALUES (@name,@image)
GO
下面就是完整的代码，拷贝即可运行：

imports System.IO
Public Class Form1
? Inherits System.Windows.Forms.Form
? Dim cn As SqlClient.SqlConnection

#region " Windows Form Designer generated code "

? Public Sub New()
??? MyBase.New()

??? 'This call is required by the Windows Form Designer.
??? InitializeComponent()

??? 'Add any initialization after the InitializeComponent() call

? End Sub

? 'Form overrides dispose to clean up the component list.
? Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
??? If disposing Then
????? If Not (components Is Nothing) Then
??????? components.Dispose()
????? End If
??? End If
??? MyBase.Dispose(disposing)
? End Sub

? 'Required by the Windows Form Designer
? Private components As System.ComponentModel.IContainer

? 'NOTE: The following procedure is required by the Windows Form Designer
? 'It can be modified using the Windows Form Designer.
? 'Do not modify it using the code editor.
? Friend WithEvents Label1 As System.Windows.Forms.Label
? Friend WithEvents Label2 As System.Windows.Forms.Label
? Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
? Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox
? Friend WithEvents Button1 As System.Windows.Forms.Button
? Friend WithEvents Button2 As System.Windows.Forms.Button
? Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
? Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
? Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
? Friend WithEvents Button3 As System.Windows.Forms.Button
? Friend WithEvents Button4 As System.Windows.Forms.Button
? Friend WithEvents Button5 As System.Windows.Forms.Button
? <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
??? Me.Label1 = New System.Windows.Forms.Label()
??? Me.Label2 = New System.Windows.Forms.Label()
??? Me.TextBox1 = New System.Windows.Forms.TextBox()
??? Me.PictureBox1 = New System.Windows.Forms.PictureBox()
??? Me.Button1 = New System.Windows.Forms.Button()
??? Me.Button2 = New System.Windows.Forms.Button()
??? Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog()
??? Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
??? Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand()
??? Me.Button3 = New System.Windows.Forms.Button()
??? Me.Button4 = New System.Windows.Forms.Button()
??? Me.Button5 = New System.Windows.Forms.Button()
??? Me.SuspendLayout()
??? '
??? 'Label1
??? '
??? Me.Label1.Location = New System.Drawing.Point(15,19)
??? Me.Label1.Name = "Label1"
??? Me.Label1.Size = New System.Drawing.Size(80,24)
??? Me.Label1.TabIndex = 0
??? Me.Label1.Text = "姓名："
??? '
??? 'Label2
??? '
??? Me.Label2.Location = New System.Drawing.Point(11,64)
??? Me.Label2.Name = "Label2"
??? Me.Label2.Size = New System.Drawing.Size(80,20)
??? Me.Label2.TabIndex = 1
??? Me.Label2.Text = "图片"
??? '
??? 'TextBox1
??? '
??? Me.TextBox1.Location = New System.Drawing.Point(75,16)
??? Me.TextBox1.Name = "TextBox1"
??? Me.TextBox1.Size = New System.Drawing.Size(173,20)
??? Me.TextBox1.TabIndex = 2
??? Me.TextBox1.Text = ""
??? '
??? 'PictureBox1
??? '
??? Me.PictureBox1.Location = New System.Drawing.Point(68,48)
??? Me.PictureBox1.Name = "PictureBox1"
??? Me.PictureBox1.Size = New System.Drawing.Size(376,222)
??? Me.PictureBox1.TabIndex = 3
??? Me.PictureBox1.TabStop = False
??? '
??? 'Button1
??? '
??? Me.Button1.Location = New System.Drawing.Point(278,13)
??? Me.Button1.Name = "Button1"
??? Me.Button1.Size = New System.Drawing.Size(96,26)
??? Me.Button1.TabIndex = 4
??? Me.Button1.Text = "浏览图片…"
??? '
??? 'Button2
??? '
??? Me.Button2.Location = New System.Drawing.Point(57,277)
??? Me.Button2.Name = "Button2"
??? Me.Button2.Size = New System.Drawing.Size(100,32)
??? Me.Button2.TabIndex = 5
??? Me.Button2.Text = "添加到数据库"
??? '
??? 'SqlConnection1
??? '
??? Me.SqlConnection1.ConnectionString = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
??? '
??? 'SqlCommand1
??? '
??? Me.SqlCommand1.CommandText = "dbo.[sp_InsertPhoto]"
??? Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
??? Me.SqlCommand1.Connection = Me.SqlConnection1
??? Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE",_
??????? System.Data.SqlDbType.Int,4,System.Data.ParameterDirection.ReturnValue,_
??????? False,CType(10,Byte),CType(0,"",System.Data.DataRowVersion.Current,Nothing))
??? Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@name",_
??????? System.Data.SqlDbType.VarChar,50))
??? Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@image",_
??????? System.Data.SqlDbType.VarBinary,2147483647))
??? '
??? 'Button3
??? '
??? Me.Button3.Location = New System.Drawing.Point(265,277)
??? Me.Button3.Name = "Button3"
??? Me.Button3.Size = New System.Drawing.Size(79,32)
??? Me.Button3.TabIndex = 6
??? Me.Button3.Text = "显示记录"
??? '
??? 'Button4
??? '
??? Me.Button4.Location = New System.Drawing.Point(362,277)
??? Me.Button4.Name = "Button4"
??? Me.Button4.Size = New System.Drawing.Size(72,32)
??? Me.Button4.TabIndex = 7
??? Me.Button4.Text = "退出"
??? '
??? 'Button5
??? '
??? Me.Button5.Location = New System.Drawing.Point(167,277)
??? Me.Button5.Name = "Button5"
??? Me.Button5.Size = New System.Drawing.Size(85,32)
??? Me.Button5.TabIndex = 8
??? Me.Button5.Text = "删除该记录"
??? '
??? 'Form1
??? '
??? Me.AutoScaleBaseSize = New System.Drawing.Size(5,13)
??? Me.ClientSize = New System.Drawing.Size(491,324)
??? Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.PictureBox1,_
??????? Me.Button5,Me.Button4,Me.Button3,Me.Button2,Me.Button1,Me.TextBox1,Me.Label1,Me.Label2})
??? Me.Name = "Form1"
??? Me.Text = "Form1"
??? Me.ResumeLayout(False)

? End Sub

#end Region
? 'Browse Button
? Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _
??? Handles Button1.Click
??? 'Display Picture File
??? OpenFileDialog1.InitialDirectory = "d:/pic"
??? OpenFileDialog1.DefaultExt = "gif"
??? OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"
??? OpenFileDialog1.ShowDialog()
??? PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
? End Sub

? 'Add Button
? Private Sub Button2_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _
??? Handles Button2.Click
??? ' To Insert Image
??? Dim st As New FileStream(OpenFileDialog1.FileName,FileMode.Open,FileAccess.Read)
??? Dim s As String = TextBox1.Text
??? Dim mbr As BinaryReader = New BinaryReader(st)
??? Dim buffer(st.Length) As Byte
??? mbr.Read(buffer,CInt(st.Length))
??? st.Close()
??? InsertImage(buffer,s)
? End Sub

? 'Function For Inserting in the Procdeure in the Database
? Public Function InsertImage(ByRef buffer,ByVal str)
??? cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)
??? cn.Open()
??? Dim cmd As New SqlClient.SqlCommand("sp_InsertPhoto",cn)
??? cmd.CommandType = CommandType.StoredProcedure
??? cmd.Parameters.Add("@name",SqlDbType.VarChar).Value = TextBox1.Text
??? cmd.Parameters.Add("@image",SqlDbType.Image).Value = buffer
??? cmd.ExecuteNonQuery()
??? MsgBox("Image inserted")
??? cn.Close()
? End Function

? 'Function to Display Image
? Private Sub ShowImage(ByVal s As String)
??? cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)
??? cn.Open()
??? Dim str As String = "SELECT photo FROM Photos WHERE name='" & s & "'"
??? Dim cmd As New SqlClient.SqlCommand(str,cn)
??? TextBox1.Text = s
??? Dim b() As Byte
??? b = cmd.ExecuteScalar()
??? If (b.Length > 0) Then
????? Dim stream As New MemoryStream(b,True)
????? stream.Write(b,b.Length)
????? DrawToScale(New Bitmap(stream))
????? stream.Close()
??? End If
??? cn.Close()
? End Sub

? 'Function to Create Instance For the Image From the Buffer
? Private Sub DrawToScale(ByVal bmp As Image)
??? PictureBox1.Image = New Bitmap(bmp)
? End Sub

? '显示处理
? Private Sub Button3_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _
??? Handles Button3.Click
??? Dim i As String = InputBox("请输入名字：")
??? ShowImage(i)
? End Sub

? '退出处理
? Private Sub Button4_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _
??? Handles Button4.Click
??? Me.Dispose()
? End Sub

? '删除处理? Private Sub Button5_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _??? Handles Button5.Click??? cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)??? cn.Open()??? Dim s As String = InputBox("请输入要删除的名字：")??? Dim cmd As New SqlClient.SqlCommand("delete from photos where name='" & s & "'",cn)??? cmd.ExecuteNonQuery()??? MsgBox("Image deleted")??? cn.Close()? End SubEnd Class?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!