Thursday, 18 November 2010

Saving an image to a SQL Database Table

Sometimes it’s not the best of method, to store images in to the database, since it’ll take lot of database space. But there are times, that it’s the only option on your list.
To this sample I will be using the following SQL Table.
   1: CREATE TABLE [dbo].[Employee](
   2:     [emp_id] [int] NOT NULL,
   3:     [emp_name] [varchar](50) NOT NULL,
   4:     [emp_image] [image] NULL
   5: ) 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: }

No comments:

Post a comment