Saving an Image to SQL Server

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: }