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