How to save and output pictures in SQL Server

Server Build Table

To test this example you need a table with data (you can create it in your current library or create a new one), and here's the structure:

 
  
   
  Column Name Datatype Purpose ID Integer identity column Primary key Imgtitle Varchar (m) Me user friendly title to identity the image Imgtype Varchar (m) Stores image content type. This'll be same as recognized content types of asp.net imgdata image Stores actual image or binary data.
 
  

Save images into SQL Server database

In order to save the pictures to the table you first have to upload them from the client to your Web server. You can create a Web form, use the textbox to get the caption of the picture, and use the HTML file Server control to get the picture file. Make sure you set the Enctype property of the form to Multipart/form-data.

  
   
　　 Stream imgdatastream = File1.PostedFile.InputStream; 　　 int imgdatalen = File1.PostedFile.ContentLength; 　　 string imgtype = File1.PostedFile.ContentType; 　　 string imgtitle = TextBox1.Text; 　　 byte[] Imgdata = new Byte[imgdatalen]; int n = imgdatastream. 　　 Read (Imgdata,0,imgdatalen); 　　 String connstr= ((NameValueCollection) context.getconfig ("AppSettings")) ["ConnStr"]; 　　 SqlConnection connection = new SqlConnection (CONNSTR); SqlCommand command = new SqlCommand (INSERT into Imagestore (imgtitle,imgtype,imgdata) VALUES (@imgtitle, @imgtype, 　　 @imgdata) ", connection); 　　 SqlParameter paramtitle = new SqlParameter ("@imgtitle", sqldbtype.varchar,50); 　　 Paramtitle.value = Imgtitle; Command. 　　 Parameters.Add (Paramtitle); 　　 SqlParameter paramdata = new SqlParameter ("@imgdata", sqldbtype.image); 　　 Paramdata.value = Imgdata; Command. 　　 Parameters.Add (Paramdata); 　　 SqlParameter paramtype = new SqlParameter ("@imgtype", sqldbtype.varchar,50); ParamtyPe. 　　 Value = Imgtype; Command. 　　 Parameters.Add (Paramtype); Connection. 　　 Open (); int numrowsaffected = command. 　　 ExecuteNonQuery (); Connection. 　　 Close ();
  

To output a picture from a database

Now let's take out the image we just saved from the database, and here we'll output the picture directly to the browser. You can also save it as a file or do whatever you want to do.

 
    
      private void Page_Load (object sender, System.EventArgs e) 　　 {string Imgid =request.querystring["Imgid"]; 　　 String connstr= ((NameValueCollection) context.getconfig ("AppSettings")) ["ConnStr"]; 　　 String sql= "Select Imgdata, imgtype from imagestore WHERE id =" + imgid; 　　 SqlConnection connection = new SqlConnection (CONNSTR); 　　 SqlCommand command = new SqlCommand (sql, connection); Connection. 　　 Open (); SqlDataReader dr = command. 　　 ExecuteReader (); if (Dr. Read ()) {Response.ContentType = dr["Imgtype"]. 　　 ToString (); 　　 Response.BinaryWrite ((byte[]) dr["Imgdata"); } connection. 　　 Close (); 
     

in the above code we use a database that has already been opened and select images by DataReader. Then use Response.BinaryWrite instead of Response.Write to display the image file.