C#: Storing and Retrieving Images from SQL Server Using Strored Procedures

Introduction

First of all I have to tell you I am not a Expert but I will try by best to explain the solution. In this article I am going to explain how to store and retrieve image from SQL server database by using C# as the front end programming language and Stored Procedures as the back end language for SQL server. Reason to write article about this topic is give proper understanding for the beginners.

Prerequisites

You need basic knowledge about Stored Procedures and C# language.

Tools Used

• SQL Server 2008
• Visual Studio 2010
• C# (Windows Form Application)

Preparing the Development Environment

SQL Server Environment

Creating Tables

In this example I am going to use only one table call ImageData and it only contain two fields call ImageID and other one is call ImageData, data types of this fields are int and image. Use below SQL script to create table.

 Collapse | Copy Code

 CREATE TABLE [dbo].[ImageData]
 (
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [ImageData] [image] NULL,
 CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED 
 (
    [ImageID] ASC
 )
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) 
 ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  

Creating Stored Procedures

In this example I am going to use Four(4) Stored Procedures call ReadAllImage, ReadAllImageIDs, ReadImage, SaveImage and use below SQL scripts to create those Procedures.

 Collapse | Copy Code

CREATE proc [dbo].[ReadAllImage] as 
SELECT * FROM ImageData 
GO

 Collapse | Copy Code

CREATE proc [dbo].[ReadAllImageIDs] as 
SELECT ImageID FROM ImageData 
GO

 Collapse | Copy Code

CREATE proc [dbo].[ReadImage] @imgId int as 
SELECT ImageData FROM ImageData 
WHERE ImageID=@imgId 
GO 

 Collapse | Copy Code

CREATE proc [dbo].[SaveImage] @img image as
INSERT INTO ImageData(ImageData)
VALUES (@img)
GO

Visual Studio Environment

Creating Windows Form

In this exapmle I am going to use only one Form and set basic properties according to the below table.

Control Name	Property Name	Property Value
Form	Name	UsingSPs
	Text	Storing and Retrieving Images from SQL Server using C#.NET
Button1	Name	btnLoadAndSave
	Text	<<--Load and Save Image-->>
Button2	Name	btnRefresh
	Text	Refresh
Button3	Name	btnDisplayImage
	Text	Display Image
ComboBox	Name	cmbImageID
GroupBox	Name	grbPicBox
	Text	Image Display
	Anchor	Top, Bottom, Left, Right
PictureBox	Name	picImage
	Dock	Fill

Start Cording

Now we have all the things to start our cording and this is the part we have to get better concentrate of our development. Anyway I will try my best to explain the cording, then let's start our journey.

In this example I am going to use one more class call DBHandler other than the From, purpose of this class is to handle the database connection details. Here is the code for that class.

Handling Database Connection String

 Collapse | Copy Code

public class DBHandler
{        
  public static string SrvName = @"DBSERVER"; //Your SQL Server Name
  public static string DbName = @"DB";//Your Database Name
  public static string UsrName = "us";//Your SQL Server User Name
  public static string Pasword = "xxxx";//Your SQL Server Password
        
  /// <summary>
  /// Public static method to access connection string throw out the project 
  /// </summary>
  /// <returns>return database connection string</returns>
  public static string GetConnectionString()
  {
     return "Data Source=" + SrvName + "; initial catalog=" + DbName + "; user id=" 
     + UsrName + "; password=" + Pasword + ";";//Build Connection String and Return
  }
}

Select and Store Image to Database

Before Start the Coding add below namespaces to your code.

 Collapse | Copy Code

using System.IO;
using System.Data;
using System.Data.SqlClient;

Here I am going to explain the btnLoadAndSave button click event process step by step.

1. Create Connection to the Database.
2. Create object call fop of type OpenFileDialog.
3. Set InitialDirectory Property of the object fop.
4. Set Filter Property of the object fop. (in here user can select only .jpg files)
5. Display open file dialog to user and only user select a image enter to if block.
6. Create a file stream object call FS associate to user selected file.
7. Create a byte array with size of user selected file stream length.
8. Read user selected file stream in to byte array.
9. Check whether connection to database is close or not.
10. If connection is close then only open the connection.
11. Create a SQL command object call cmd by passing name of the stored procedure and database connection.
12. Set CommandType Property of the object cmd to stored procedure type.
13. Add parameter to the cmd object and set value to that parameter.
14. Execute SQL command by calling the ExecuteNonQuery() method of the object cmd.
15. Call user defined method to load image IDs to combo box. (this method will explain later so don't worry now)
16. Display save successful message to user.
17. Catch if any error occur during the above code executing process.
18. Finally Check whether connection to database is open or not, if connection is open then only close the connection.

Below Demonstrate the Complete Select and Store image to database code.

 Collapse | Copy Code

SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());
try
 {
   OpenFileDialog fop = new OpenFileDialog();
   fop.InitialDirectory = @"C:\"; 
   fop.Filter = "[JPG,JPEG]|*.jpg";
   if (fop.ShowDialog() == DialogResult.OK)
   {
     FileStream FS = new FileStream(@fop.FileName, FileMode.Open, FileAccess.Read);
     byte[] img = new byte[FS.Length];
     FS.Read(img, 0, Convert.ToInt32(FS.Length));

     if (con.State == ConnectionState.Closed)
       con.Open();
     SqlCommand cmd = new SqlCommand("SaveImage", con);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;
     cmd.ExecuteNonQuery();
     loadImageIDs();
     MessageBox.Show("Image Save Successfully!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
   }
   else
   {
     MessageBox.Show("Please Select a Image to save!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
   }

 }
 catch (Exception ex)
 {
   MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
 }
 finally
 {
   if (con.State == ConnectionState.Open)
     con.Close();
 }

Retrieving and Display Image

Here I am going to explain the btnDisplayImage button click event process step by step.

1. Check whether user select image ID or not from combobox.
2. Check whether picture box contain image or not.
3. Clear the image of the picture box if there is image.
4. Create Connection to the Database.
5. Create a SQL command object call cmd by passing name of the stored procedure and database connection.
6. Set CommandType Property of the object cmd to stored procedure type.
7. Add parameter to the cmd object and set value to that parameter.
8. Create SQL data adapter object call adp by passing previously created cmd object.
9. Create a data table object call dt to hold result of the cmd object.
10. Check whether connection to database is close or not.
11. If connection is close then only open the connection.
12. Object dt fill with data by calling the fill method of adp objec.
13. Check whether object dt contain any data row or not.
14. Ccreate memory stream object call ms by passing byte array of the image.
15. Set image property of the picture box by creating a image from memory stream.
16. Set SizeMode property of the picture box to stretch.
17. Call refresh metod of picture box.
18. Catch if any error occur during the above code executing process.
19. Finally Check whether connection to database is open or not, if connection is open then only close the connection.

Below Demonstrate the Complete Retrieving and Display Image code.

 Collapse | Copy Code

if (cmbImageID.SelectedValue != null)
{
    if (picImage.Image != null)
        picImage.Image.Dispose();

    SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());
    SqlCommand cmd = new SqlCommand("ReadImage", con);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add("@imgId", SqlDbType.Int).Value = 
              Convert.ToInt32(cmbImageID.SelectedValue.ToString());
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    try
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        adp.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            MemoryStream ms = new MemoryStream((byte[])dt.Rows[0]["ImageData"]);
            picImage.Image = Image.FromStream(ms);
            picImage.SizeMode = PictureBoxSizeMode.StretchImage;
            picImage.Refresh();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", 
              MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
    }
}
else
{
    MessageBox.Show("Please Select a Image ID to Display!!", 
       "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

របៀបប្តូរ Keyboard Layout ក្នុង VB.NET

ចំពោះ​អ្នក​សរសេរ​កម្ម​វិធី​ទាំងឡាយ ដែលប្រើ​ប្រាស់ខ្មែរ​យូនីកូដ នៅ​ពេល​ដែល​ចង់ប្តូរពី Keyboard Layout english ot khmer Unicode នោះ លោក​អ្នក​តំរូវអោយ​អ្នក​ប្រើ​ប្រាស់​កម្ម​វិធីចុច alt + Shift ដើម្បី​ប្តូរ​។ សកម្មភាព​បែប​នេះ វា​អាចបណ្តាលអោយ​មានកំហុស​កើត​ឡើង​ក្នុង​កម្ម​វិធី ឧទាហរណ៍ Text box ដែល​ត្រូវ​តំរូវ​អោយអ្នក​ប្រើប្រាស់បញ្ចូលទិន្ន​ជាលេខជាដើម ប្រសិន​បើ​ Keyboard Layout ស្ថិត​ក្នុង​ស្ថានភាព khmer (CA or KH) នោះ​វាបណ្តាល​អោយមានកំហុស​កើ​តឡើង។ ដើ​ម្បី​ដោះ​ស្រាយ​បញ្ហានេះ​លោក​អ្នក​ត្រូវ​ប្តូរ​ Keyboard Layout តាមរយៈការសរសេរ​កូដ នៅ​ត្រង់​ព្រឹត្តិការណ៍ got Focus ទៅ​លើ​ object មួយ ។ ខាង​ក្រោមនេះ គឺ​ជាគំរូកូដ​សំរាប់ផ្លាស់ប្តូរ Keyboard Layout ទៅ​ជាភាសាខ្មែរ រី ជាភាសា​អង់គ្លេស។

Module Module1
Public Declare Function GetKeyboardLayoutName Lib “user32″ _
Alias “GetKeyboardLayoutNameA” _
(ByVal pwszKLID As String) As Long
Public Declare Function LoadKeyboardLayout Lib “user32″ _
Alias “LoadKeyboardLayoutA” _
(ByVal pwszKLID As String, ByVal flags As Long) As Long
Const KLF_ACTIVATE = &H1
‘ some languages code
Public Const LANG_ENGLISH As String = “00000409″
Public Const LANG_FRENCH As String = “0000040C”
Public Const LANG_ARABIC As String = “00000401″
Public Const LANG_GREEK As String = “00000408″
Public Const Lang_kh As String = “a0000403″
Public Const LANG_ITALIAN As String = “00000400″
Public Const LANG_GERMAN As String = “00000407″
Public Function SwitchKeyboardLang(ByVal strLangID As String) As Boolean
‘Returns TRUE when the KeyboardLayout was set properly, FALSE otherwise
Dim strRet As String
On Error Resume Next
strRet = New String(“0″, 9)
GetKeyboardLayoutName(strRet)
If strRet = (strLangID & Chr(0)) Then
‘ you are try to switch to the already selected language
‘ so return without doing anything
SwitchKeyboardLang = True
Exit Function
Else
strRet = New String(“0″, 9)
strRet = LoadKeyboardLayout((strLangID & Chr(0)), KLF_ACTIVATE)
End If
GetKeyboardLayoutName(strRet) ‘ Test if switch successed
If strRet = (strLangID) Then
SwitchKeyboardLang = True
End If
End Function
End Module

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Text = SwitchKeyboardLang(LANG_ENGLISH)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Me.Text = SwitchKeyboardLang(Lang_kh)
End Sub
End Class

Source: http://laingmoam.wordpress.com/2010/01/11/%E1%9E%9A%E1%9E%94%E1%9F%80%E1%9E%94%E1%9E%94%E1%9F%92%E1%9E%8F%E1%9E%BC%E1%9E%9A-keyboard-layout-%E1%9E%80%E1%9F%92%E1%9E%93%E1%9E%BB%E1%9E%84-vb-net/