Click here to Skip to main content
11,716,249 members (88,576 online)
Click here to Skip to main content

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

, 12 Aug 2014 GPL3 185.7K 13K 82
Rate this:
Please Sign up or sign in to vote.
An introduction on how to store and retrieve images from SQL Server 2008 by using Stored Procedures and C#.

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.

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.

CREATE proc [dbo].[ReadAllImage] as 
SELECT * FROM ImageData 
GO
CREATE proc [dbo].[ReadAllImageIDs] as 
SELECT ImageID FROM ImageData 
GO
CREATE proc [dbo].[ReadImage] @imgId int as 
SELECT ImageData FROM ImageData 
WHERE ImageID=@imgId 
GO 
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 Coding

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

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.

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.

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.

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);
}

Load All Image ID to Combobox

In this example I am using comman metod call loadImageIDs(), and purpose of this metod is load all IDs of the images to combobox. Reason to write this process as separate method because I am going to use it several place in this application. Those places are mention here.

  • Inside btnRefresh Button Click Event.
  • Inside btnLoadAndSave Button Click Event.
  • Inside UsingSPs Form Load Event.

Here I am going to explain the loadImageIDs() user define method process step by step.

  1. Create Connection to the Database.
  2. Create a SQL command object call cmd by passing name of the stored procedure and database connection.
  3. Set CommandType Property of the object cmd to stored procedure type.
  4. Create SQL data adapter object call adp by passing previously created cmd object.
  5. Create a data table object call dt to hold result of the cmd object.
  6. Check whether connection to database is close or not.
  7. If connection is close then only open the connection.
  8. Object dt fill with data by calling the fill method of adp objec.
  9. Check whether object dt contain any data row or not.
  10. Set the DataSource property of the object cmbImageID to object of dt.
  11. Set the ValueMember property of the object cmbImageID to ImageID column in the database table.
  12. Set the DisplayMember property of the object cmbImageID to ImageID column in the database table.
  13. Set the SelectedIndex property of the object cmbImageID to value 0.
  14. Catch if any error occur during the above code executing process.
  15. Finally Check whether connection to database is open or not, if connection is open then only close the connection.

Below Demonstrate the Complete Load All Image IDs to Combobox code.

private void loadImageIDs()
{
    #region Load Image Ids
    SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());
    SqlCommand cmd = new SqlCommand("ReadAllImageIDs", con);
    cmd.CommandType = CommandType.StoredProcedure;
    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)
        {
            cmbImageID.DataSource = dt;
            cmbImageID.ValueMember = "ImageID";
            cmbImageID.DisplayMember = "ImageID";
            cmbImageID.SelectedIndex = 0;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
    }
    #endregion
}

Points of Interest

I hope this Article will help you to understand and learn the Image Storing and Retrieving from SQL server using C#.net.

I try to explain the full process step by step in easy manner. I hope it'll helpful to you understand this article.

If you have anything to say about this article please don't hesitate to use comment and discussion board below, because your valuable ideas will be help me to improve this article or write new articles.

History

  • 27th March 2012: Initial post

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

Thilina Chandima
Software Developer (Senior)
Sri Lanka Sri Lanka
http://grabitquickly.blogspot.com/

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Ujjval Shukla26-Jun-15 0:18
memberUjjval Shukla26-Jun-15 0:18 
GeneralRe: My vote of 4 Pin
Thilina Chandima28-Jun-15 18:13
memberThilina Chandima28-Jun-15 18:13 
QuestionError Pin
Bals2575-May-15 22:01
memberBals2575-May-15 22:01 
GeneralMy vote of 4 Pin
ari o116-Mar-15 22:42
memberari o116-Mar-15 22:42 
GeneralRe: My vote of 4 Pin
Thilina Chandima16-Mar-15 22:58
memberThilina Chandima16-Mar-15 22:58 
Bug[My vote of 2] Helpfull to Beginners But Fix this Bug and Post Article again Pin
Member 1060491214-Feb-15 23:30
memberMember 1060491214-Feb-15 23:30 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun26-Jan-15 22:00
memberHumayun Kabir Mamun26-Jan-15 22:00 
GeneralRe: My vote of 5 Pin
Thilina Chandima5-Feb-15 19:10
memberThilina Chandima5-Feb-15 19:10 
QuestionGaditude Pin
Member 112809902-Dec-14 9:43
memberMember 112809902-Dec-14 9:43 
AnswerRe: Gaditude Pin
Thilina Chandima22-Dec-14 19:17
memberThilina Chandima22-Dec-14 19:17 
Questionexcellent.. Pin
Member 1108389522-Nov-14 6:10
memberMember 1108389522-Nov-14 6:10 
AnswerRe: excellent.. Pin
Thilina Chandima22-Dec-14 19:18
memberThilina Chandima22-Dec-14 19:18 
GeneralMy vote of 3 Pin
Shelke Amit22-Nov-14 3:13
memberShelke Amit22-Nov-14 3:13 
GeneralRe: My vote of 3 Pin
Thilina Chandima22-Dec-14 19:19
memberThilina Chandima22-Dec-14 19:19 
QuestionThanks Pin
Member 1048735223-Oct-14 6:25
memberMember 1048735223-Oct-14 6:25 
AnswerRe: Thanks Pin
Thilina Chandima23-Oct-14 19:09
memberThilina Chandima23-Oct-14 19:09 
QuestionGreat Pin
jessekoegler3-Jun-14 9:28
memberjessekoegler3-Jun-14 9:28 
AnswerRe: Great Pin
Thilina Chandima3-Jun-14 17:20
memberThilina Chandima3-Jun-14 17:20 
Question@Storing and Retrieving Images from SQL Server Using Strored Procedures and C#.net Pin
konanle10-Apr-14 4:17
memberkonanle10-Apr-14 4:17 
AnswerRe: @Storing and Retrieving Images from SQL Server Using Strored Procedures and C#.net Pin
Thilina Chandima8-May-14 17:53
memberThilina Chandima8-May-14 17:53 
Generalthanks Pin
babasahebaa21-Sep-13 5:15
memberbabasahebaa21-Sep-13 5:15 
GeneralRe: thanks Pin
Thilina Chandima27-Nov-13 18:11
memberThilina Chandima27-Nov-13 18:11 
GeneralMy vote of 5 Pin
ramesh02859-Jul-13 8:03
memberramesh02859-Jul-13 8:03 
GeneralRe: My vote of 5 Pin
Thilina Chandima30-Jul-13 4:25
memberThilina Chandima30-Jul-13 4:25 
QuestionံHow open sql file in C# any computer Pin
naylynn2-Jun-13 5:18
membernaylynn2-Jun-13 5:18 
AnswerRe: ံHow open sql file in C# any computer Pin
Thilina Chandima3-Jun-13 4:37
memberThilina Chandima3-Jun-13 4:37 
AnswerRe: ံHow open sql file in C# any computer Pin
Member 1077449113-Aug-14 6:05
memberMember 1077449113-Aug-14 6:05 
GeneralGreat Job! Pin
Kushan Randima21-Mar-13 1:36
memberKushan Randima21-Mar-13 1:36 
GeneralMy vote of 5 Pin
abhishek kr singh1113-Dec-12 8:51
memberabhishek kr singh1113-Dec-12 8:51 
GeneralRe: My vote of 5 Pin
Thilina Chandima13-Dec-12 17:34
memberThilina Chandima13-Dec-12 17:34 
QuestionParameter not valid Pin
Member 79072603-Dec-12 22:35
memberMember 79072603-Dec-12 22:35 
Questionparameter is not valid. Pin
WinnieMeeKun27-Nov-12 17:14
memberWinnieMeeKun27-Nov-12 17:14 
AnswerRe: parameter is not valid. Pin
Thilina Chandima27-Nov-12 19:21
memberThilina Chandima27-Nov-12 19:21 
GeneralRe: parameter is not valid. Pin
WinnieMeeKun27-Nov-12 19:36
memberWinnieMeeKun27-Nov-12 19:36 
GeneralRe: parameter is not valid. Pin
Thilina Chandima27-Nov-12 20:25
memberThilina Chandima27-Nov-12 20:25 
GeneralRe: parameter is not valid. Pin
WinnieMeeKun27-Nov-12 20:38
memberWinnieMeeKun27-Nov-12 20:38 
GeneralRe: parameter is not valid. Pin
Thilina Chandima27-Nov-12 20:58
memberThilina Chandima27-Nov-12 20:58 
GeneralRe: parameter is not valid. Pin
WinnieMeeKun27-Nov-12 21:12
memberWinnieMeeKun27-Nov-12 21:12 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:12
mvpKanasz Robert24-Sep-12 6:12 
GeneralRe: My vote of 5 Pin
Thilina Chandima25-Sep-12 3:55
memberThilina Chandima25-Sep-12 3:55 
QuestionParameter is not valid. Pin
Yasir Farooq21-Sep-12 7:48
memberYasir Farooq21-Sep-12 7:48 
AnswerRe: Parameter is not valid. Pin
Thilina Chandima21-Sep-12 16:16
memberThilina Chandima21-Sep-12 16:16 
QuestionRe: Parameter is not valid. Pin
Yasir Farooq21-Sep-12 20:59
memberYasir Farooq21-Sep-12 20:59 
AnswerRe: Parameter is not valid. Pin
Thilina Chandima22-Sep-12 3:12
memberThilina Chandima22-Sep-12 3:12 
GeneralRe: Parameter is not valid. Pin
Yasir Farooq24-Sep-12 2:31
memberYasir Farooq24-Sep-12 2:31 
GeneralRe: Parameter is not valid. Pin
Thilina Chandima24-Sep-12 3:41
memberThilina Chandima24-Sep-12 3:41 
Questionno Method FromStream Pin
sopheary sun20-Sep-12 15:10
membersopheary sun20-Sep-12 15:10 
AnswerRe: no Method FromStream Pin
sopheary sun20-Sep-12 15:16
membersopheary sun20-Sep-12 15:16 
GeneralRe: no Method FromStream Pin
Thilina Chandima21-Sep-12 3:14
memberThilina Chandima21-Sep-12 3:14 
GeneralRe: no Method FromStream Pin
sopheary sun23-Sep-12 14:43
membersopheary sun23-Sep-12 14:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150901.1 | Last Updated 12 Aug 2014
Article Copyright 2012 by Thilina Chandima
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid