Step 1
Verify that SQL Server 7/2000 is installed on the machine.
Create table tblImgData, stored procedure rkReadPicture, rkInsertPicture
in pubs database and if the SQL Server version is 2000 and above then allow
execute permission to two stored procedures. The SQL scripts for these
database objects have been provided in the application folder on the ZIP file.
Step 2
Unzip the ZIP file which can be downloaded from the
Downloads section at the end of this article and open the solution file in Visual
Studio .NET 2003 and run the application. For the first time the table
tblImgData will be blank. Now enter an ID and name for the image in the given
input on the windows form and select a image from the image folder available in
the application folder and click on button "Save new image to database."
The code in routine btnSave_Click() will insert the image to the table in byte
format.
Listing 1
private void btnSave_Click(object sender, System.EventArgs e)
{
if (strFn != null && ID.Text != "")
{
this.pictureBox1.Image = Image.FromFile(strFn);
FileInfo fiImage = new FileInfo(strFn);
this.lImageFileLength = fiImage.Length;
FileStream fs = new FileStream(strFn, FileMode.Open, FileAccess.Read,
FileShare.Read);
barrImg = new byte[Convert.ToInt32(this.lImageFileLength)];
int iBytesRead = fs.Read(barrImg, 0, Convert.ToInt32(this.lImageFileLength))
;
fs.Close();
SqlCommand cmdInsert = new SqlCommand("rkInsertPicture", con);
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add("@ID", System.Data.SqlDbType.Int, 4);
cmdInsert.Parameters.Add("@Name", System.Data.SqlDbType.VarChar, 50);
cmdInsert.Parameters.Add("@Picture", System.Data.SqlDbType.Image);
cmdInsert.Parameters["@ID"].Value = this.ID.Text;
cmdInsert.Parameters["@Name"].Value = this.txtName.Text;
cmdInsert.Parameters["@Picture"].Value = this.barrImg;
cmdInsert.Connection = con;
int iresult = cmdInsert.ExecuteNonQuery();
}
else
{
MessageBox.Show("Check ID and Name, these two cannot be blank");
}
}
Step 3
In order to view and verify the image enter the ID in the
provided textbox in form and click on button "Show Image," this will
read the byte stored in database table, convert to image and display on the
form.
Listing 2
private void btnShowImage_Click(object sender, System.EventArgs e)
{
label3.Text = "";
this.pictureBox1.Visible = true;
try
{
SqlCommand cmdsp = new SqlCommand("rkReadPicture", con);
cmdsp.CommandType = CommandType.StoredProcedure;
cmdsp.Parameters.Add("@ID", SqlDbType.Int, 4);
if (this.ID.Text != "")
{
cmdsp.Parameters["@ID"].Value = this.ID.Text;
cmdsp.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(cmdsp);
DataSet ds = new DataSet();
da.Fill(ds, "tblImgData");
foreach (DataRow row in ds.Tables["tblImgData"].Rows)
{
label3.Text = row[1].ToString();
}
byte[]barrImg = (byte[])cmdsp.ExecuteScalar();
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
try
{
FileStream fs = new FileStream(strfn, FileMode.CreateNew,
FileAccess.Write);
fs.Write(barrImg, 0, barrImg.Length);
fs.Flush();
fs.Close();
pictureBox1.Image = Image.FromFile(strfn);
}
catch (Exception exp)
{
this.pictureBox1.Visible = false;
MessageBox.Show("No records found for the given ID " + ID.Text);
}
finally{
}
}
else
{
MessageBox.Show("Enter a ID which is integer type for the search");
}
}
catch (Exception exp1)
{
MessageBox.Show(exp1.ToString());
}
}
There is some definite purpose of using stored procedure in
this application. Stored procedures enhance security. The permissions to
execute a stored procedure can be granted, denied, or revoked on the data
objects. Here is a code snip which tells how to grant permissions.
Listing 3
-- grant select permission on the tblImgData table to specific user account.
-- role
GRANT SELECT ON tblImgData TO XXXXXX
GO
-- deny UPDATE, DELETE and INSERT permissions
DENY UPDATE, DELETE, INSERT ON tblImgData TO XXXXXX
GO
Notes: Validation has not been implemented on the
application. This might be implemented as per requirement.