向SQL SERVER 中插入二进制数据

在VB时期, 向SQL SERVER 中插入二进制数据, 是通过 ADODB.STREAM 实现, 在.NET中, 对“流”格式的操作更加强大而简单,本篇演示向SQL SERVER 中插入数据并读出的功能.

在窗体上添加一个 OPENFILEDIALOG 控件, 两个PICTUREBOX, 代码如下:

---------------------------------------------------------------------

-----------------------

Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Me.Dispose(True)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

If ofdPic.ShowDialog = DialogResult.OK Then

pbPreview.Image = Image.FromFile(ofdPic.FileName)

End If

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

If pbPreview.Image Is Nothing Then

MsgBox("请先选择图片", MsgBoxStyle.Exclamation)

Exit Sub

End If

Dim fs As FileStream = New FileStream(ofdPic.FileName, FileMode.Open, FileAccess.Read)

Dim bt(fs.Length) As Byte

fs.Read(bt, 0, fs.Length)

fs.Close()

fs = Nothing

Dim sqlConn As SqlClient.SqlConnection = New

SqlClient.SqlConnection("Server=(local);User

Id=sa;Password=123;Database=pubs")

sqlConn.Open()

Dim sqlCmd As New SqlClient.SqlCommand("sp_InsertImage", sqlConn) https://www.360docs.net/doc/f116486319.html,mandType = CommandType.StoredProcedure

sqlCmd.Parameters.Add("@img", SqlDbType.Image).Value = bt

sqlCmd.ExecuteNonQuery()

sqlCmd = Nothing

sqlConn.Close()

sqlConn = Nothing

MsgBox("图片插入成功", https://www.360docs.net/doc/f116486319.html,rmation)

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

Dim sqlConn As SqlClient.SqlConnection = New

SqlClient.SqlConnection("Server=(local);User

Id=sa;Password=123;Database=pubs")

sqlConn.Open()

Dim sqlCmd As New SqlClient.SqlCommand("SELECT img FROM test WHERE t_ID=3", sqlConn)

https://www.360docs.net/doc/f116486319.html,mandType = CommandType.Text

Dim bt() As Byte = sqlCmd.ExecuteScalar()

If Not bt Is Nothing Then

If bt.Length > 0 Then

Dim fs As MemoryStream = New MemoryStream(bt)

pbReview.Image = Image.FromStream(fs)

'fs.Close

'fs = Nothing

'可以自己试着将上面两句的注释去掉, 看有什么效果 ^_^

Else

MsgBox("无图片")

End If

Else

MsgBox("无数据")

End If

sqlCmd = Nothing

sqlConn.Close()

sqlConn = Nothing

End Sub

End Class

-----------------------------------------------------------------------------

数据库部分:

----------------

use pubs

go

Create Table test(t_ID int identity(1,1), img image)

go

Create Procedure sp_InsertImage

@img image

AS

Insert Into test (img) Values (@img)

go