Click here to Skip to main content
Click here to Skip to main content

Saving an image to SQL Server

, 20 Oct 2011
Rate this:
Please Sign up or sign in to vote.
How to save an image to a SQL Server table.

Sometimes it’s not the best method to store images into the database, since it’ll take up a lot of database space. But there are times when it’s the only option on your list. In this sample I will be using the following SQL table.

CREATE TABLE [dbo].[Employee](
     [emp_id] [int] NOT NULL,
     [emp_name] [varchar](50) NOT NULL,
     [emp_image] [image] NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

When inserting data use the following syntax:

   1: string fileName = @"D:\MyImage.jpg";
   2: string connectionString = "Password=PWD;Persist Security " + 
        "Info=True;User ID=USER;Initial Catalog=DATABASE;Data Source=SQLSERVER";
   3: using (SqlConnection sqlConnection = new SqlConnection(connectionString))
   4: {
   5:  
   6:     FileInfo finfo = new FileInfo(fileName);
   7:  
   8:     byte[] btImage = new byte[finfo.Length];
   9:     FileStream fStream = finfo.OpenRead();
  10:  
  11:     fStream.Read(btImage, 0, btImage.Length);
  12:     fStream.Close();
  13:  
  14:  
  15:     using (SqlCommand sqlCommand = new SqlCommand(
            "INSERT INTO Employee (emp_id, emp_name, " + 
            "emp_image) VALUES(@emp_id, @emp_name, @emp_image)", 
            sqlConnection))
  16:     {
  17:  
  18:         sqlCommand.Parameters.AddWithValue("@emp_id", 2);
  19:         sqlCommand.Parameters.AddWithValue("@emp_name", "Employee Name");
  20:         SqlParameter imageParameter = 
                 new SqlParameter("@emp_image", SqlDbType.Image);
  21:         imageParameter.Value = btImage;
  22:  
  23:         sqlCommand.Parameters.Add(imageParameter);
  24:  
  25:  
  26:         sqlConnection.Open();
  27:         sqlCommand.ExecuteNonQuery();
  28:         sqlConnection.Close();
  29:     }
  30:  
  31: }

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 20 Oct 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid