SQL SERVER数据库的Image字段来存储图片。对图片读取采用二进制和FileStream文件流。其中还要用到数据库的存储过程来插入和读取图片信息记录的内容。数据库中的图片可以另存在硬盘上。支持常见的几个格式
先看一下程序界面:
SQL存储过程:
CREATE PROCEDURE AddAndUpdateImg @ImgID int, @ImgName nvarchar(255), @ImgDes nvarchar(1000), @ImgExtName nvarchar(10), @ImgTypeID int, @UpdateTime smalldatetime, @ImgBinary image As If Exists (Select 1 From ImgTB Where ImgID=@ImgID) Begin Update [ImgTB] Set ImgName=@ImgName,ImgDes=@ImgDes,ImgExtName=@ImgExtName,ImgTypeID=@ImgTypeID,UpdateTime=@UpdateTime,ImgBinary=@ImgBinary Where ImgID=@ImgID Select @@RowCount End Else Begin
Declare @NewID smallint set @NewID=0 Select @NewID=IsNull(Max([ImgID]),0)+1 From [ImgTB] INSERT INTO [ImgTB] (ImgID,ImgName,ImgDes,ImgExtName,ImgTypeID,UpdateTime,ImgBinary) Values (@ImgID,@ImgName,@ImgDes,@ImgExtName,@ImgTypeID,@UpdateTime,@ImgBinary) Select @NewID End 操作这个存储过程的一个VB.Net的函数:
- Function AddAndUpdateImg() As Boolean
- Dim fs As FileStream
- Try
- SqlConn = New SqlConnection(SQLClass.ConnectString)
- SqlComm = New SqlCommand
- SqlComm.Connection = SqlConn
- SqlComm.CommandType = CommandType.StoredProcedure
- SqlComm.CommandText = "AddAndUpdateImg"
- pr = New SqlParameter("@ImgID", SqlDbType.Int)
- pr.Value = CInt(Me.TxtBoxImgID.Text)
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@ImgName", SqlDbType.NVarChar, 255)
- pr.Value = Me.TxtBoxImgName.Text
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@ImgDes", SqlDbType.NVarChar, 1000)
- If Me.TxtBoxImgDes.Text > "" Then
- pr.Value = Me.TxtBoxImgDes.Text
- Else
- pr.Value = ""
- End If
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@ImgExtName", SqlDbType.NVarChar, 10)
- If Me.TxtBoxImgExtName.Text > "" Then
- pr.Value = Me.TxtBoxImgExtName.Text
- Else
- pr.Value = ""
- End If
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@ImgTypeID", SqlDbType.Int)
- If Me.TxtBoxImgTypeID.Text > "" Then pr.Value = CInt(Me.TxtBoxImgTypeID.Text)
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@UpdateTime", SqlDbType.SmallDateTime)
- pr.Value = Me.TImgUpdateTime.Value
- SqlComm.Parameters.Add(pr)
- pr = New SqlParameter("@ImgBinary", SqlDbType.Image)
- If ImgPath > "" Then
- fs = New FileStream(ImgPath, FileMode.Open)
- Dim bt(fs.Length) As Byte
- fs.Read(bt, 0, fs.Length)
- pr.Value = bt
- Else
- If Me.ImgBox.Image IsNot Nothing Then
- Me.ImgBox.Image.Save(fs, Me.ImgBox.Image.RawFormat)
- Dim bt(fs.Length) As Byte
- fs.Read(bt, 0, fs.Length)
- pr.Value = bt
- End If
- End If
- SqlComm.Parameters.Add(pr)
- SqlConn.Open()
- SqlComm.ExecuteNonQuery()
- Try
- fs.Close()
- Catch ex As Exception
- End Try
- SqlConn.Close()
- SqlConn = Nothing
- Return True
- Catch ex As SqlException
- Dim errItem As SqlError
- Dim errString As String = ""
- For Each errItem In ex.Errors
- errString += ex.Message + " "
- Next
- MsgBox("SQL错误.详细内容:" & errString)
- Return False
- Catch myException As Exception
- MsgBox("异常描述:" + myException.ToString())
- Return False
- End Try
- End Function
|