Vb教程 Vb.net教程 Vfp教程 C/C++教程 Vc/Vc++教程 Delphi教程 Java教程 Powerbuilder
  杀毒频道 | 短信频道 | 网络电视 | 论文中心 | 学上网 | 学软件 | 网页特效 | 电脑基础 | 论坛  
  NCRE | 软考 | CET | 职称英语 | 司法考试 | 报关员 | 公务员 | CATTI | CPA考试  
  Html教程 | Css教程 | Xml教程 | Asp教程 | Asp.net | Php教程 | Jsp教程 | Linux教程 | QQ技巧  
Photoshop Illustrator ImageReady Maya教程 3D Max教程 Lightscape Coredraw教程 Authorware Autocad教程 Freehand教程
Access教程 Mysql教程 Sql server Oracle教程 Word教程 Excel教程 Powerpoint Frontpage Asp.net源码 Php源代码
Flash教程 Fireworks Dreamweaver C#教程 outlook教程 系统安装 vbscript教程 Javascript Jsp源代码 Asp源代码
您的位置:首页 >> Vb.net教程 >> 正文

向SQL Server数据库添加图片和文字

文章来源:互联网

   下面的代码实现向SQL Server数据库添加图片和文字的功能。

    首先,在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.<a href="http://dev.21tx.com/os/windows/" target="_blank">Windows</a>.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, Byte), "", 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, File<a href="http://dev.21tx.com/database/access/" target="_blank">Access</a>.Read)
    Dim s As String = TextBox1.Text
    Dim mbr As BinaryReader = New BinaryReader(st)
    Dim buffer(st.Length) As Byte
    mbr.Read(buffer, 0, 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, 0, 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 Sub
End Class

[返回]

编程语言 web开发 数据库 网络技术 操作系统 服务器 网页设计 图形设计 办公软件 常用软件 学电脑

Copyright© www.bianceng.cn Powered by 编程入门网 All Rights Reserved.
关于本站 | 版权声明 | 联系我们 | 友情链接 |
编程入门网 版权所有