Cara menyimpan gambar atau file
binary pada database dengan VB.NET dan C#.NET, serta menampilkan gambar
atau file binary dari database dengan VB.NET dan C#.NET dapat dilakukan
dengan beberapa cara, menggunakan SqlClient, Oledb, Odbc, FileStream,
MemoryStream. Di bawah ini beberapa contoh cara-caranya :
Menyimpan gambar dengan C# pada database yang support SqlClient yaitu SQL Server :
private void btnEditPerabotan_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SPP_5922;Integrated Security=true");
MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] Pic_arr = new byte[ms.Length];
ms.Position = 0;
ms.Read(Pic_arr, 0, Pic_arr.Length);
SqlCommand cmdb = new SqlCommand("Update Perabot set Perabot_Id= @perabotid, Perabot_Name= @perabotname,Harga=@harga, Kategori_Id= @kategoriid, Model_Id= @modelid, Gambar=@gambar where Perabot_Id =@perabotid", cn);
cmdb.Parameters.AddWithValue("@perabotid", textIdPerabot.Text);
cmdb.Parameters.AddWithValue("@perabotname", textNmaPerabot.Text);
cmdb.Parameters.AddWithValue("@harga", textHarga.Text);
cmdb.Parameters.AddWithValue("@kategoriid", comboKategori.Text);
cmdb.Parameters.AddWithValue("@modelid", comboModel.Text);
cmdb.Parameters.AddWithValue("@gambar", Pic_arr);
cn.Open();
try
{
int res = cmdb.ExecuteNonQuery();
if (res > 0)
{
MessageBox.Show("data berhasil disimpan");
RefreshGridPerabot();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cn.Close();
}
}
Menyimpan gambar dengan VB.NET pada database yang support Oledb yaitu MS Access :
If Not PicCover.Image Is Nothing Then
_buku.Cover = PicCover.Image
End If
Public Function addBuku(ByVal _buku As Buku) As Boolean
Dim con As OleDbConnection = Connection.CON
Try
Dim ms As MemoryStream = New MemoryStream()
_buku.Cover.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim Pic_arr(ms.Length) As Byte
ms.Position = 0
ms.Read(Pic_arr, 0, Pic_arr.Length)
con.Open()
Dim sc As OleDbCommand = New OleDbCommand("INSERT INTO BUKU VALUES('" + _buku.IdBuku + "','" + _buku.Judul + "','" + _buku.Ringkasan + "',#" + _buku.TglTerbit.ToShortDateString + "#," + _buku.Harga.ToString + ", @cover)", con)
Console.WriteLine(sc.CommandText)
sc.Parameters.AddWithValue("@cover", Pic_arr)
sc.ExecuteNonQuery()
Return True
Catch e As System.Data.SqlClient.SqlException
Return False
Catch e2 As Exception
Return False
Finally
con.Close()
End Try
End Function
Menampilkan atau mengambil gambar dengan C# pada database yang support SqlClient yaitu SQL Server :
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
textIdPerabot.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
comboModel.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
comboKategori.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
textNmaPerabot.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
textHarga.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
Byte[] byteBLOBData = new Byte[0];
byteBLOBData = (Byte[])(dataGridView1.CurrentRow.Cells[5].Value);
MemoryStream stmBLOBData = new MemoryStream(byteBLOBData);
pictureBox1.Image = Image.FromStream(stmBLOBData);
}
Menyimpan gambar dengan VB.NET pada database yang support ODBC misal MySQL :
Private Sub BtnUpdatePic_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdatePic.Click
Dim tpic As Byte() = System.IO.File.ReadAllBytes(txtFile.Text)
Dim con As New OdbcConnection
con.ConnectionString = "driver={mysql odbc 3.51 driver};server=localhost;database=karyawan;uid=root;password= ;"
con.Open()
Dim cmd As New OdbcCommand
cmd.Connection = con
cmd.CommandType = CommandType.Text
'PARAMETER HARUS PAKAI TANDA TANYA ? KHUSUS UNTUK ODBC
cmd.CommandText = "UPDATE peserta SET gambar = ? WHERE idpeserta = '102' " ',@total)"
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
End Sub
Menampilkan atau mengambil gambar dengan VB.NET pada database yang support ODBC misal MySQL :
Menyimpan gambar dengan C# pada database yang support SqlClient yaitu SQL Server :
private void btnEditPerabotan_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SPP_5922;Integrated Security=true");
MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] Pic_arr = new byte[ms.Length];
ms.Position = 0;
ms.Read(Pic_arr, 0, Pic_arr.Length);
SqlCommand cmdb = new SqlCommand("Update Perabot set Perabot_Id= @perabotid, Perabot_Name= @perabotname,Harga=@harga, Kategori_Id= @kategoriid, Model_Id= @modelid, Gambar=@gambar where Perabot_Id =@perabotid", cn);
cmdb.Parameters.AddWithValue("@perabotid", textIdPerabot.Text);
cmdb.Parameters.AddWithValue("@perabotname", textNmaPerabot.Text);
cmdb.Parameters.AddWithValue("@harga", textHarga.Text);
cmdb.Parameters.AddWithValue("@kategoriid", comboKategori.Text);
cmdb.Parameters.AddWithValue("@modelid", comboModel.Text);
cmdb.Parameters.AddWithValue("@gambar", Pic_arr);
cn.Open();
try
{
int res = cmdb.ExecuteNonQuery();
if (res > 0)
{
MessageBox.Show("data berhasil disimpan");
RefreshGridPerabot();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cn.Close();
}
}
Menyimpan gambar dengan VB.NET pada database yang support Oledb yaitu MS Access :
If Not PicCover.Image Is Nothing Then
_buku.Cover = PicCover.Image
End If
Public Function addBuku(ByVal _buku As Buku) As Boolean
Dim con As OleDbConnection = Connection.CON
Try
Dim ms As MemoryStream = New MemoryStream()
_buku.Cover.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim Pic_arr(ms.Length) As Byte
ms.Position = 0
ms.Read(Pic_arr, 0, Pic_arr.Length)
con.Open()
Dim sc As OleDbCommand = New OleDbCommand("INSERT INTO BUKU VALUES('" + _buku.IdBuku + "','" + _buku.Judul + "','" + _buku.Ringkasan + "',#" + _buku.TglTerbit.ToShortDateString + "#," + _buku.Harga.ToString + ", @cover)", con)
Console.WriteLine(sc.CommandText)
sc.Parameters.AddWithValue("@cover", Pic_arr)
sc.ExecuteNonQuery()
Return True
Catch e As System.Data.SqlClient.SqlException
Return False
Catch e2 As Exception
Return False
Finally
con.Close()
End Try
End Function
Menampilkan atau mengambil gambar dengan C# pada database yang support SqlClient yaitu SQL Server :
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
textIdPerabot.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();
comboModel.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();
comboKategori.Text = dataGridView1.CurrentRow.Cells[2].Value.ToString();
textNmaPerabot.Text = dataGridView1.CurrentRow.Cells[3].Value.ToString();
textHarga.Text = dataGridView1.CurrentRow.Cells[4].Value.ToString();
Byte[] byteBLOBData = new Byte[0];
byteBLOBData = (Byte[])(dataGridView1.CurrentRow.Cells[5].Value);
MemoryStream stmBLOBData = new MemoryStream(byteBLOBData);
pictureBox1.Image = Image.FromStream(stmBLOBData);
}
Menyimpan gambar dengan VB.NET pada database yang support ODBC misal MySQL :
Private Sub BtnUpdatePic_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdatePic.Click
Dim tpic As Byte() = System.IO.File.ReadAllBytes(txtFile.Text)
Dim con As New OdbcConnection
con.ConnectionString = "driver={mysql odbc 3.51 driver};server=localhost;database=karyawan;uid=root;password= ;"
con.Open()
Dim cmd As New OdbcCommand
cmd.Connection = con
cmd.CommandType = CommandType.Text
'PARAMETER HARUS PAKAI TANDA TANYA ? KHUSUS UNTUK ODBC
cmd.CommandText = "UPDATE peserta SET gambar = ? WHERE idpeserta = '102' " ',@total)"
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
End Sub
Menampilkan atau mengambil gambar dengan VB.NET pada database yang support ODBC misal MySQL :
Private Sub
BtnViewPictMysql_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles BtnViewPictMysql.Click
Dim fstream As New IO.FileStream(IO.Path.GetDirectoryName(Application.ExecutablePath) & "\tmp.dat", IO.FileMode.Create)
'fstream.Write(bs.Item(bs.Position)(4), 0, System.Buffer.ByteLength(bs.Item(bs.Position)(4)))
'fstream.Write(dgv1.Rows(0).Cells("gambar").Value, 0, System.Buffer.ByteLength(dgv1.Rows(0).Cells("gambar").Value))
fstream.Write(dgv1.SelectedRows(0).Cells("gambar").Value, 0, System.Buffer.ByteLength(dgv1.SelectedRows(0).Cells("gambar").Value))
PicFoto.Image = Image.FromStream(fstream)
'PicFoto.SizeMode = PictureBoxSizeMode.Zoom
fstream.Close()
End Sub
Cara lain menyimpan gambar binary ke database dengan C#.NET :
con.open
string query = "update Jenis_Penyakit set gambar=@gambar where NAMA_PENYAKIT = '" + dgGambarPenyakit.Rows[dgGambarPenyakit.SelectedRows[0].Index].Cells[1].Value.ToString() + "'";
if (myconn.State.ToString() != "Open")
myconn.Open();
SqlCommand cmd = new SqlCommand(query, myconn);
cmd.Parameters.Add("@gambar", SqlDbType.Image, 0 , "");
cmd.Parameters[0].Value = tpic;
cmd.ExecuteNonQuery();
Cara lain mengambil/menampilkan gambar binary dari database dengan C#.NET :
private void dgGambarPenyakit_Click(object sender, EventArgs e)
{
myconn = koneksi.CON;
SqlCommand cmdSelect = new SqlCommand("SELECT GAMBAR FROM JENIS_PENYAKIT WHERE NAMA_PENYAKIT = '" + dgGambarPenyakit.Rows[dgGambarPenyakit.SelectedRows[0].Index].Cells[1].Value.ToString() + "'", this.myconn);
myconn.Open();
byte[] barrImg = (byte[])cmdSelect.ExecuteScalar();
if (barrImg != null)
{
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg, 0, barrImg.Length);
fs.Flush();
fs.Close();
this.myconn.Close();
picPenyakit.Image = Image.FromFile(strfn);
}
else
{
picPenyakit.Image = null;
}
}
Cara lain menyimpan gambar binary ke database dengan VB.NET :
tpic = File.ReadAllBytes(oSketsa.FileName)
If (tpic.GetType.ToString() <> "System.Object") Then
da.UpdateCommand.Parameters("@Sketsa").Value = tpic
End If
'cm.Parameters.Add("@Sketsa", OdbcType.VarChar, 0, "").Value = pathCAD
cm.ExecuteNonQuery()
cn.Close()
Cara lain mengambil/menampilkan gambar binary dari database dengan VB.NET :
If (bs.Item(bs.Position)(4).GetType.ToString() <> "System.DBNull") Then
Dim fstream As New FileStream(Path.GetDirectoryName(Application.ExecutablePath) & "\sket.dat", FileMode.Create)
fstream.Write(bs.Item(bs.Position)(4), 0, System.Buffer.ByteLength(bs.Item(bs.Position)(4)))
pSketsa.Image = Image.FromStream(fstream)
pSketsa.SizeMode = PictureBoxSizeMode.Zoom
fstream.Close()
Else
pSketsa.Image = Nothing
End If
Menyimpan Gambar di Database MySQL dengan MySQLOledb
Public Function SiswaInsert(ByVal Siswa As Siswa) As MySqlDataReader
Try
Dim ms As MemoryStream = New MemoryStream()
Siswa.Foto.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim Pic_arr(ms.Length) As Byte
ms.Position = 0
ms.Read(Pic_arr, 0, Pic_arr.Length)
Dim StrSQL As String = "INSERT INTO siswa" & _
" (Nis,Kelas_ID,Tahun_ID,Nama,Kota_lahir,Tanggal_lahir,Gender,Agama,Alamat,Telepon,Terima_dkelas,Tgl_terima,Id_ThnAjaran,Sekolah_asal,Anak_ke,Status_anak,Nama_Ayah,Nama_Ibu,Pekerjaan_Ayah,Pekerjaan_Ibu,Penghasilan_Ortu,Alamat_Ortu,Telp_Ortu,Foto_Siswa) " & _
" VALUES (@Nis,@Kelas_ID,@Tahun_ID,@Nama,@Kota_lahir,@Tanggal_lahir,@Gender,@Agama,@Alamat,@Telepon,@Terima_dkelas,@Tgl_terima,@Id_ThnAjaran,@Sekolah_asal,@Anak_ke,@Status_anak,@Nama_Ayah,@Nama_Ibu,@Pekerjaan_Ayah,@Pekerjaan_Ibu,@Penghasilan_Ortu,@Alamat_Ortu,@Telp_Ortu,@Foto_Siswa);"
Dim myCommand As MySqlCommand = New MySqlCommand(StrSQL, myconnection.open)
myCommand.CommandType = CommandType.Text
Dim parameterS_Nis As MySqlParameter = New MySqlParameter("@Nis", MySqlDbType.VarChar, 4)
parameterS_Nis.Value = Siswa.Nis
Dim parameterS_Kelas As MySqlParameter = New MySqlParameter("@Kelas_ID", MySqlDbType.Int16)
parameterS_Kelas.Value = Siswa.Kelas
Dim parameterS_Angkatan As MySqlParameter = New MySqlParameter("@Tahun_ID", MySqlDbType.Int16)
parameterS_Angkatan.Value = Siswa.Angkatan
Dim parameterS_Nama As MySqlParameter = New MySqlParameter("@Nama", MySqlDbType.VarChar, 50)
parameterS_Nama.Value = Siswa.Nama
Dim parameterS_KotaLahir As MySqlParameter = New MySqlParameter("@Kota_lahir", MySqlDbType.VarChar, 30)
parameterS_KotaLahir.Value = Siswa.KotaLahir
Dim parameterS_TglLahir As MySqlParameter = New MySqlParameter("@Tanggal_lahir", MySqlDbType.Date)
parameterS_TglLahir.Value = Siswa.TglLahir
Dim parameterS_Kelamin As MySqlParameter = New MySqlParameter("@Gender", MySqlDbType.VarChar, 1)
parameterS_Kelamin.Value = Siswa.Kelamin
Dim parameterS_Agama As MySqlParameter = New MySqlParameter("@Agama", MySqlDbType.VarChar, 10)
parameterS_Agama.Value = Siswa.Agama
Dim parameterS_Alamat As MySqlParameter = New MySqlParameter("@Alamat", MySqlDbType.VarChar, 100)
parameterS_Alamat.Value = Siswa.Alamat
Dim parameterS_Telepon As MySqlParameter = New MySqlParameter("@Telepon", MySqlDbType.VarChar, 15)
parameterS_Telepon.Value = Siswa.Telepon
Dim parameterS_TerimadKelas As MySqlParameter = New MySqlParameter("@Terima_dkelas", MySqlDbType.VarChar, 30)
parameterS_TerimadKelas.Value = Siswa.TerimadKelas
Dim parameterS_TglTerima As MySqlParameter = New MySqlParameter("@Tgl_terima", MySqlDbType.Date)
parameterS_TglTerima.Value = Siswa.TglTerima
Dim parameterS_ThnAjaran As MySqlParameter = New MySqlParameter("@Id_ThnAjaran", MySqlDbType.Int16)
parameterS_ThnAjaran.Value = Siswa.ThnAjaran
Dim parameterS_AsalSekolah As MySqlParameter = New MySqlParameter("@Sekolah_asal", MySqlDbType.VarChar, 30)
parameterS_AsalSekolah.Value = Siswa.AslSekolah
Dim parameterS_AnakKe As MySqlParameter = New MySqlParameter("@Anak_ke", MySqlDbType.VarChar, 30)
parameterS_AnakKe.Value = Siswa.AnakKe
Dim parameterS_StatusAnak As MySqlParameter = New MySqlParameter("@Status_anak", MySqlDbType.VarChar, 30)
parameterS_StatusAnak.Value = Siswa.StatusAnak
Dim parameterS_NamaAyah As MySqlParameter = New MySqlParameter("@Nama_Ayah", MySqlDbType.VarChar, 50)
parameterS_NamaAyah.Value = Siswa.NamaAyah
Dim parameterS_NamaIbu As MySqlParameter = New MySqlParameter("@Nama_Ibu", MySqlDbType.VarChar, 50)
parameterS_NamaIbu.Value = Siswa.NamaIbu
Dim parameterS_KerjaAyah As MySqlParameter = New MySqlParameter("@Pekerjaan_Ayah", MySqlDbType.VarChar, 30)
parameterS_KerjaAyah.Value = Siswa.KerjaAyah
Dim parameterS_KerjaIbu As MySqlParameter = New MySqlParameter("@Pekerjaan_Ibu", MySqlDbType.VarChar, 30)
parameterS_KerjaIbu.Value = Siswa.KerjaIbu
Dim parameterS_HasilOrtu As MySqlParameter = New MySqlParameter("@Penghasilan_Ortu", MySqlDbType.VarChar, 100)
parameterS_HasilOrtu.Value = Siswa.PenghaslianOrtu
Dim parameterS_AlamatOrtu As MySqlParameter = New MySqlParameter("@Alamat_Ortu", MySqlDbType.VarChar, 100)
parameterS_AlamatOrtu.Value = Siswa.AlamatOrtu
Dim parameterS_TelpOrtu As MySqlParameter = New MySqlParameter("@Telp_Ortu", MySqlDbType.VarChar, 15)
parameterS_TelpOrtu.Value = Siswa.TelponOrtu
Dim parameterS_Foto As MySqlParameter = New MySqlParameter("@Foto_Siswa", MySqlDbType.Blob)
parameterS_Foto.Value = Pic_arr
With myCommand.Parameters
.Add(parameterS_Nis)
.Add(parameterS_Kelas)
.Add(parameterS_Angkatan)
.Add(parameterS_Nama)
.Add(parameterS_KotaLahir)
.Add(parameterS_TglLahir)
.Add(parameterS_Kelamin)
.Add(parameterS_Agama)
.Add(parameterS_Alamat)
.Add(parameterS_Telepon)
.Add(parameterS_TerimadKelas)
.Add(parameterS_TglTerima)
.Add(parameterS_ThnAjaran)
.Add(parameterS_AsalSekolah)
.Add(parameterS_AnakKe)
.Add(parameterS_StatusAnak)
.Add(parameterS_NamaAyah)
.Add(parameterS_NamaIbu)
.Add(parameterS_KerjaAyah)
.Add(parameterS_KerjaIbu)
.Add(parameterS_HasilOrtu)
.Add(parameterS_AlamatOrtu)
.Add(parameterS_TelpOrtu)
.Add(parameterS_Foto)
End With
Dim result As MySqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
Catch SqlEx As MySqlException
Throw New Exception(SqlEx.Message.ToString())
End Try
End Function
Mengambil Gambar dari MySQL dengan MySQLOledb
Dim objDataTable As New DataTable
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
Try
Dim DataControl As New AccessData.DataControl
Dim myData As DataSet = DataControl.GetDataSet("Select * from siswa where nis = '" & txtNis.Text & "'")
objDataTable = myData.Tables("data")
If objDataTable.Rows.Count > 0 Then
AddSiswa.Show()
AddSiswa.txtNis.Text = objDataTable.Rows(0)("nis").ToString
AddSiswa.txtNama.Text = objDataTable.Rows(0)("nama").ToString
Dim stmBLOBData As MemoryStream = New MemoryStream()
stmBLOBData.Write(objDataTable.Rows(0)("Foto_Siswa"), 0, System.Buffer.ByteLength(objDataTable.Rows(0)("Foto_Siswa")))
AddSiswa.PictureBox1.Image = Image.FromStream(stmBLOBData)
Else
MsgBox("Data Tidak Ditemukan")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Dim fstream As New IO.FileStream(IO.Path.GetDirectoryName(Application.ExecutablePath) & "\tmp.dat", IO.FileMode.Create)
'fstream.Write(bs.Item(bs.Position)(4), 0, System.Buffer.ByteLength(bs.Item(bs.Position)(4)))
'fstream.Write(dgv1.Rows(0).Cells("gambar").Value, 0, System.Buffer.ByteLength(dgv1.Rows(0).Cells("gambar").Value))
fstream.Write(dgv1.SelectedRows(0).Cells("gambar").Value, 0, System.Buffer.ByteLength(dgv1.SelectedRows(0).Cells("gambar").Value))
PicFoto.Image = Image.FromStream(fstream)
'PicFoto.SizeMode = PictureBoxSizeMode.Zoom
fstream.Close()
End Sub
Cara lain menyimpan gambar binary ke database dengan C#.NET :
con.open
string query = "update Jenis_Penyakit set gambar=@gambar where NAMA_PENYAKIT = '" + dgGambarPenyakit.Rows[dgGambarPenyakit.SelectedRows[0].Index].Cells[1].Value.ToString() + "'";
if (myconn.State.ToString() != "Open")
myconn.Open();
SqlCommand cmd = new SqlCommand(query, myconn);
cmd.Parameters.Add("@gambar", SqlDbType.Image, 0 , "");
cmd.Parameters[0].Value = tpic;
cmd.ExecuteNonQuery();
Cara lain mengambil/menampilkan gambar binary dari database dengan C#.NET :
private void dgGambarPenyakit_Click(object sender, EventArgs e)
{
myconn = koneksi.CON;
SqlCommand cmdSelect = new SqlCommand("SELECT GAMBAR FROM JENIS_PENYAKIT WHERE NAMA_PENYAKIT = '" + dgGambarPenyakit.Rows[dgGambarPenyakit.SelectedRows[0].Index].Cells[1].Value.ToString() + "'", this.myconn);
myconn.Open();
byte[] barrImg = (byte[])cmdSelect.ExecuteScalar();
if (barrImg != null)
{
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream(strfn,
FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg, 0, barrImg.Length);
fs.Flush();
fs.Close();
this.myconn.Close();
picPenyakit.Image = Image.FromFile(strfn);
}
else
{
picPenyakit.Image = null;
}
}
Cara lain menyimpan gambar binary ke database dengan VB.NET :
tpic = File.ReadAllBytes(oSketsa.FileName)
If (tpic.GetType.ToString() <> "System.Object") Then
da.UpdateCommand.Parameters("@Sketsa").Value = tpic
End If
'cm.Parameters.Add("@Sketsa", OdbcType.VarChar, 0, "").Value = pathCAD
cm.ExecuteNonQuery()
cn.Close()
Cara lain mengambil/menampilkan gambar binary dari database dengan VB.NET :
If (bs.Item(bs.Position)(4).GetType.ToString() <> "System.DBNull") Then
Dim fstream As New FileStream(Path.GetDirectoryName(Application.ExecutablePath) & "\sket.dat", FileMode.Create)
fstream.Write(bs.Item(bs.Position)(4), 0, System.Buffer.ByteLength(bs.Item(bs.Position)(4)))
pSketsa.Image = Image.FromStream(fstream)
pSketsa.SizeMode = PictureBoxSizeMode.Zoom
fstream.Close()
Else
pSketsa.Image = Nothing
End If
Menyimpan Gambar di Database MySQL dengan MySQLOledb
Public Function SiswaInsert(ByVal Siswa As Siswa) As MySqlDataReader
Try
Dim ms As MemoryStream = New MemoryStream()
Siswa.Foto.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim Pic_arr(ms.Length) As Byte
ms.Position = 0
ms.Read(Pic_arr, 0, Pic_arr.Length)
Dim StrSQL As String = "INSERT INTO siswa" & _
" (Nis,Kelas_ID,Tahun_ID,Nama,Kota_lahir,Tanggal_lahir,Gender,Agama,Alamat,Telepon,Terima_dkelas,Tgl_terima,Id_ThnAjaran,Sekolah_asal,Anak_ke,Status_anak,Nama_Ayah,Nama_Ibu,Pekerjaan_Ayah,Pekerjaan_Ibu,Penghasilan_Ortu,Alamat_Ortu,Telp_Ortu,Foto_Siswa) " & _
" VALUES (@Nis,@Kelas_ID,@Tahun_ID,@Nama,@Kota_lahir,@Tanggal_lahir,@Gender,@Agama,@Alamat,@Telepon,@Terima_dkelas,@Tgl_terima,@Id_ThnAjaran,@Sekolah_asal,@Anak_ke,@Status_anak,@Nama_Ayah,@Nama_Ibu,@Pekerjaan_Ayah,@Pekerjaan_Ibu,@Penghasilan_Ortu,@Alamat_Ortu,@Telp_Ortu,@Foto_Siswa);"
Dim myCommand As MySqlCommand = New MySqlCommand(StrSQL, myconnection.open)
myCommand.CommandType = CommandType.Text
Dim parameterS_Nis As MySqlParameter = New MySqlParameter("@Nis", MySqlDbType.VarChar, 4)
parameterS_Nis.Value = Siswa.Nis
Dim parameterS_Kelas As MySqlParameter = New MySqlParameter("@Kelas_ID", MySqlDbType.Int16)
parameterS_Kelas.Value = Siswa.Kelas
Dim parameterS_Angkatan As MySqlParameter = New MySqlParameter("@Tahun_ID", MySqlDbType.Int16)
parameterS_Angkatan.Value = Siswa.Angkatan
Dim parameterS_Nama As MySqlParameter = New MySqlParameter("@Nama", MySqlDbType.VarChar, 50)
parameterS_Nama.Value = Siswa.Nama
Dim parameterS_KotaLahir As MySqlParameter = New MySqlParameter("@Kota_lahir", MySqlDbType.VarChar, 30)
parameterS_KotaLahir.Value = Siswa.KotaLahir
Dim parameterS_TglLahir As MySqlParameter = New MySqlParameter("@Tanggal_lahir", MySqlDbType.Date)
parameterS_TglLahir.Value = Siswa.TglLahir
Dim parameterS_Kelamin As MySqlParameter = New MySqlParameter("@Gender", MySqlDbType.VarChar, 1)
parameterS_Kelamin.Value = Siswa.Kelamin
Dim parameterS_Agama As MySqlParameter = New MySqlParameter("@Agama", MySqlDbType.VarChar, 10)
parameterS_Agama.Value = Siswa.Agama
Dim parameterS_Alamat As MySqlParameter = New MySqlParameter("@Alamat", MySqlDbType.VarChar, 100)
parameterS_Alamat.Value = Siswa.Alamat
Dim parameterS_Telepon As MySqlParameter = New MySqlParameter("@Telepon", MySqlDbType.VarChar, 15)
parameterS_Telepon.Value = Siswa.Telepon
Dim parameterS_TerimadKelas As MySqlParameter = New MySqlParameter("@Terima_dkelas", MySqlDbType.VarChar, 30)
parameterS_TerimadKelas.Value = Siswa.TerimadKelas
Dim parameterS_TglTerima As MySqlParameter = New MySqlParameter("@Tgl_terima", MySqlDbType.Date)
parameterS_TglTerima.Value = Siswa.TglTerima
Dim parameterS_ThnAjaran As MySqlParameter = New MySqlParameter("@Id_ThnAjaran", MySqlDbType.Int16)
parameterS_ThnAjaran.Value = Siswa.ThnAjaran
Dim parameterS_AsalSekolah As MySqlParameter = New MySqlParameter("@Sekolah_asal", MySqlDbType.VarChar, 30)
parameterS_AsalSekolah.Value = Siswa.AslSekolah
Dim parameterS_AnakKe As MySqlParameter = New MySqlParameter("@Anak_ke", MySqlDbType.VarChar, 30)
parameterS_AnakKe.Value = Siswa.AnakKe
Dim parameterS_StatusAnak As MySqlParameter = New MySqlParameter("@Status_anak", MySqlDbType.VarChar, 30)
parameterS_StatusAnak.Value = Siswa.StatusAnak
Dim parameterS_NamaAyah As MySqlParameter = New MySqlParameter("@Nama_Ayah", MySqlDbType.VarChar, 50)
parameterS_NamaAyah.Value = Siswa.NamaAyah
Dim parameterS_NamaIbu As MySqlParameter = New MySqlParameter("@Nama_Ibu", MySqlDbType.VarChar, 50)
parameterS_NamaIbu.Value = Siswa.NamaIbu
Dim parameterS_KerjaAyah As MySqlParameter = New MySqlParameter("@Pekerjaan_Ayah", MySqlDbType.VarChar, 30)
parameterS_KerjaAyah.Value = Siswa.KerjaAyah
Dim parameterS_KerjaIbu As MySqlParameter = New MySqlParameter("@Pekerjaan_Ibu", MySqlDbType.VarChar, 30)
parameterS_KerjaIbu.Value = Siswa.KerjaIbu
Dim parameterS_HasilOrtu As MySqlParameter = New MySqlParameter("@Penghasilan_Ortu", MySqlDbType.VarChar, 100)
parameterS_HasilOrtu.Value = Siswa.PenghaslianOrtu
Dim parameterS_AlamatOrtu As MySqlParameter = New MySqlParameter("@Alamat_Ortu", MySqlDbType.VarChar, 100)
parameterS_AlamatOrtu.Value = Siswa.AlamatOrtu
Dim parameterS_TelpOrtu As MySqlParameter = New MySqlParameter("@Telp_Ortu", MySqlDbType.VarChar, 15)
parameterS_TelpOrtu.Value = Siswa.TelponOrtu
Dim parameterS_Foto As MySqlParameter = New MySqlParameter("@Foto_Siswa", MySqlDbType.Blob)
parameterS_Foto.Value = Pic_arr
With myCommand.Parameters
.Add(parameterS_Nis)
.Add(parameterS_Kelas)
.Add(parameterS_Angkatan)
.Add(parameterS_Nama)
.Add(parameterS_KotaLahir)
.Add(parameterS_TglLahir)
.Add(parameterS_Kelamin)
.Add(parameterS_Agama)
.Add(parameterS_Alamat)
.Add(parameterS_Telepon)
.Add(parameterS_TerimadKelas)
.Add(parameterS_TglTerima)
.Add(parameterS_ThnAjaran)
.Add(parameterS_AsalSekolah)
.Add(parameterS_AnakKe)
.Add(parameterS_StatusAnak)
.Add(parameterS_NamaAyah)
.Add(parameterS_NamaIbu)
.Add(parameterS_KerjaAyah)
.Add(parameterS_KerjaIbu)
.Add(parameterS_HasilOrtu)
.Add(parameterS_AlamatOrtu)
.Add(parameterS_TelpOrtu)
.Add(parameterS_Foto)
End With
Dim result As MySqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return result
Catch SqlEx As MySqlException
Throw New Exception(SqlEx.Message.ToString())
End Try
End Function
Mengambil Gambar dari MySQL dengan MySQLOledb
Dim objDataTable As New DataTable
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
Try
Dim DataControl As New AccessData.DataControl
Dim myData As DataSet = DataControl.GetDataSet("Select * from siswa where nis = '" & txtNis.Text & "'")
objDataTable = myData.Tables("data")
If objDataTable.Rows.Count > 0 Then
AddSiswa.Show()
AddSiswa.txtNis.Text = objDataTable.Rows(0)("nis").ToString
AddSiswa.txtNama.Text = objDataTable.Rows(0)("nama").ToString
Dim stmBLOBData As MemoryStream = New MemoryStream()
stmBLOBData.Write(objDataTable.Rows(0)("Foto_Siswa"), 0, System.Buffer.ByteLength(objDataTable.Rows(0)("Foto_Siswa")))
AddSiswa.PictureBox1.Image = Image.FromStream(stmBLOBData)
Else
MsgBox("Data Tidak Ditemukan")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Tidak ada komentar:
Posting Komentar