RSS

Category Archives: SQL

Save and Retrieve images in C# from SQL Database

Download Sourcecode

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace imageDB
{
public partial class Form1 : Form
{
DataSet ds;
string imageName;

public Form1()
{
InitializeComponent();
}

//Without using statements
/*private void insertData()
{
try
{

if (imageName != "")
{
//Initialize a file stream to read the image file
FileStream fs = new FileStream(@imageName, FileMode.Open, FileAccess.Read);
//Initialize a byte array with size of stream
byte[] imgByteArr = new byte[fs.Length];
//Read data from the file stream and put into the byte array
fs.Read(imgByteArr, 0, Convert.ToInt32(fs.Length));
fs.Close();

//Save binary data in database
string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True";
SqlConnection conn = new SqlConnection(constr);
conn.Open();
string sql = "insert into tbl_Image(id,img) values('" + textBox1.Text + "',@img)";

SqlParameter imageParameter = new SqlParameter();
imageParameter.SqlDbType = SqlDbType.Image;
imageParameter.ParameterName = "img";
imageParameter.Value = imgByteArr;
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(imageParameter);
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("Image Added");
}

cmd.Dispose();
conn.Dispose();

}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}*/

private void insertData()
{
if (imageName == "")
{
return;
}

try
{
//Initialize a file stream to read the image file
FileStream fs = new FileStream(@imageName, FileMode.Open, FileAccess.Read);
//Initialize a byte array with size of stream
byte[] imgByteArr = new byte[fs.Length];
//Read data from the file stream and put into the byte array
fs.Read(imgByteArr, 0, Convert.ToInt32(fs.Length));
fs.Close();

//Save binary data in database
string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True";
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
string sql = "insert into tbl_Image(id,img) values('" + textBox1.Text + "',@img)";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add(new SqlParameter("img", imgByteArr));
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("Image Added");
refreshImageList();
}
}
}
}
catch (Exception ex)
{

MessageBox.Show(ex.Message);
}
}

private void refreshImageList()
{
try
{
string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True";

using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();

using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tbl_Image", conn))
{
ds = new DataSet("myDataSet");
adapter.Fill(ds);
DataTable dt = ds.Tables[0];

//Alternatively can fill data into a data table directly
//DataTable dt2 = new DataTable();
//adapter.Fill(dt2);

comboBox1.Items.Clear();

foreach (DataRow dr in dt.Rows)
{
comboBox1.Items.Add(dr["id"].ToString());
}
comboBox1.SelectedIndex = 0;

//Display queried data in a grid view
dataGridView1.DataSource = dt;
}
}

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void button1_Click(object sender, EventArgs e)
{
try
{

FileDialog fldlg = new OpenFileDialog();
fldlg.InitialDirectory = Environment.SpecialFolder.MyPictures.ToString();
fldlg.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif";
if (fldlg.ShowDialog() == DialogResult.OK)
{
imageName = fldlg.FileName;
Bitmap bmp = new Bitmap(imageName);
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox1.Image = (Image)bmp;
}

fldlg = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

private void button2_Click(object sender, EventArgs e)
{
insertData();
}

private void button3_Click(object sender, EventArgs e)
{
DataTable dataTable = ds.Tables[0];
//If there is an already an image in picturebox, then delete it
if (pictureBox2.Image != null)
{
pictureBox2.Image.Dispose();
}

//Initialize a file stream to write image data
FileStream fs = new FileStream("image.jpg", FileMode.Create);

foreach (DataRow row in dataTable.Rows)
{
if (row[0].ToString() == comboBox1.SelectedItem.ToString())
{
//Store binary data read from the database in a byte array
byte[] blob = (byte[])row[1];

//Write data in image file using file stream
fs.Write(blob, 0, blob.Length);
fs.Close();
fs = null;

pictureBox2.Image = Image.FromFile("image.jpg");
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox2.Refresh();

}
}
}
}

}

 
3 Comments

Posted by on June 24, 2012 in C#.Net, SQL

 

Tags: ,