Saving and outputting images in SQL Server)

Sometimes we need to save some binary data into the database. SQL Server provides a special data type called image for us to save binary data. Binary data can be images and documents. In this article Article In SQL Server, we will see how to save and output images.

 

Create a table

 

To test this example, you need a table containing data (you can create it in the current database or a new database). The following is its structure:

 

Column name	Datatype	Purpose
ID	Integer	Identity column primary key
Imgtitle	Varchar (50)	Stores some user friendly title to identity the image
Imgtype	Varchar (50)	Stores image content type. This will be same as recognized content types of ASP. NET
Imgdata	Image	Stores actual image or binary data.

 

Save images to the SQL Server database

 

To save images to tables, you must first upload them from the client to your web server. You can create a web form, use textbox to get the image title, and use HTML file server control to get the image file. Make sure that you set the enctype attribute of 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

("Etettings") ["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 ();

 

Output images from the database

 

Now let's retrieve the image we just saved from the database. Here, we will output the image directly to the browser. You can also save it as a file or do anything you want.

 

Private void page_load (Object sender, system. eventargs E)

{

String imgid = request. querystring ["imgid"];

String connstr = (namevaluecollection)

Context. getconfig ("etettings") ["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 ();

}

 

AboveCodeWe use an opened database and select images through datareader. Then, use response. binarywrite instead of response. Write to display the image file.