Introduction
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 a picture, a document, and more. In this article we will see how to save and output pictures in SQL Server.
Build
To test this example you need a Table containing data (you can create it in the current library, you can also create a new database), below 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 into SQL Server Database
In order to save pictures to Table, you first get from the client to your web server. You can create a web form, get the title of the picture with TextBox, get a picture file with HTML File Server Control. Confident, you set the form's EncType property for Multipart / Form-Data.
Stream IMGDataStream = file1.postedfile.inputstream;
INT IMGDATALEN = file1.postedfile.contentLENGTH;
String ImgType = file1.postedfile.contenttype;
String emgtitle = textbox1.text;
Byte [] IMGData = New byte [IMGDATALEN];
INT n = imgdatathastream.read (IMGDATA, 0, IMGDATALEN);
String connStr =
(NameValueCollection) context.getconfig
("appsettings")) ["connStr"];
SqlConnection Connection = New SqlConnection (connStr);
Sqlcommand command = new SQLCOMMAND
("INSERT IMAGTORE (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 take out our picture from our database, here, we will output pictures to your browser. You can also save it as a file or do anything 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 AMEGTORE 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 code above, we use a database that has been opened, select Images of Images of Images. Then use Response.binaryWrite instead of response.write to display the image file.
I hope you like these articles, if you have any comments and suggestions, please contact Webmaster@bipinjoshi.com.
