Click here to Skip to main content
12,886,892 members (39,478 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

22.1K views
7 bookmarked
Posted 20 Oct 2011

Saving an Image to SQL Server

, 20 Oct 2011 CPOL
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:     FileInfo finfo = new FileInfo(fileName);
 6:
 7:     byte[] btImage = new byte[finfo.Length];
 8:     FileStream fStream = finfo.OpenRead();
 9:
10:     fStream.Read(btImage, 0, btImage.Length);
11:     fStream.Close();
12:
13:     using (SqlCommand sqlCommand = new SqlCommand(
          "INSERT INTO Employee (emp_id, emp_name, " +
          "emp_image) VALUES(@emp_id, @emp_name, @emp_image)",
          sqlConnection))
14:     {
15:         sqlCommand.Parameters.AddWithValue("@emp_id", 2);
16:         sqlCommand.Parameters.AddWithValue("@emp_name", "Employee Name");
17:         SqlParameter imageParameter =
               new SqlParameter("@emp_image", SqlDbType.Image);
18:         imageParameter.Value = btImage;
19:
20:         sqlCommand.Parameters.Add(imageParameter);
21:
22:         sqlConnection.Open();
23:         sqlCommand.ExecuteNonQuery();
24:         sqlConnection.Close();
25:     }
26: }

License

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

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

You may also be interested in...

Pro

Comments and Discussions

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