Cara Menyimpan Gambar/Image ke Database SQL Server menggunakan VB 2008


Dalam contoh kode program berikut ini, akan mengupdate data categories pada database northwind. Maka sebelumnya harus dibuat dulu stored procedure untuk mengupdate tabel categories, seperti berikut ini:

create proc upCategories (@categoryId int,
@CategoryName  nvarchar(15),
@Description  ntext,
@Picture  image)  as
UPDATE [Northwind].[dbo].[Categories]
SET [CategoryName] = @CategoryName,
[Description] = @Description,
[Picture] = @Picture
WHERE categoryId=@categoryId
 

Setelah membuat stored procedure diatas, lanjutkan dengan membuat sebuah form baru. Potongan kode berikut ini adalah isi dari class form1

Imports System.IO
Imports System.Data.SqlClientPublic Class Form1
Private cnstr As String = “server=.;database=northwind;user id=userx;password=123456″

Private Function isian(ByVal ssql As String) As DataSet
Dim ds As New DataSet

Using cn As New SqlConnection(cnstr)
Try
cn.Open()
Dim da As New SqlDataAdapter(ssql, cn)
da.Fill(ds)
Catch ex As Exception
ds = Nothing
End Try
End Using
Return ds
End Function

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DataGridView1.DataSource = isian(“select * from categories”).Tables(0)
DataGridView1.Refresh()
End Sub

Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
OpenFileDialog1.FileName = “”
OpenFileDialog1.Filter = “JPG|*.jpg|BMP|*.bmp|png|*.png|GIF|*.gif”
OpenFileDialog1.ShowDialog()
Try
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)

Catch ex As Exception
PictureBox1.Image = Nothing
End Try
End Sub

Private Sub DataGridView1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.Click

Dim imgArr As Byte()
Dim ms As MemoryStream
Dim dsx As DataSet = isian(“select * from categories where categoryid=’” & DataGridView1.Item(0, DataGridView1.CurrentRow.Index).Value & “‘”)
If dsx IsNot Nothing Then
categoryId_txt.Text = dsx.Tables(0).Rows(0)(0)
categoryName_txt.Text = dsx.Tables(0).Rows(0)(1)
Description_txt.Text = dsx.Tables(0).Rows(0)(2)

Try
imgArr = CType(dsx.Tables(0).Rows(0)(3), Byte())
ms = New MemoryStream(imgArr)
PictureBox1.Image = Image.FromStream(ms)
Catch ex As Exception
PictureBox1.Image = Nothing
End Try
End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ms As New MemoryStream
PictureBox1.Image.Save(ms, PictureBox1.Image.RawFormat)
Dim imgArr() As Byte = ms.GetBuffer
ms.Close()

Using cn As New SqlConnection(cnstr)
Try
cn.Open()
Using cmd As New SqlCommand(“upCategories”, cn)
cmd.Parameters.AddWithValue(“@categoryId”, categoryId_txt.Text)
cmd.Parameters.AddWithValue(“@categoryName”, categoryName_txt.Text)
cmd.Parameters.AddWithValue(“@Description”, Description_txt.Text)
cmd.Parameters.AddWithValue(“@picture”, imgArr)
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
MsgBox(“Data saved”)
DataGridView1.DataSource = isian(“select * from categories”).Tables(0)
DataGridView1.Refresh()
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Using
End Sub
End Class

Download Code

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s