SQL Server provides a special data type: Image, which is a type containing binary data. This example will show you how to put text or photos in the database. In this article we have to see how to store and read images in SQL Server.
1. Create a table:
Create a table of such a structure in SQL Server:
Column Type Type ID Integer Primary Key ID Imgtitle Varchar (50) Image Title IMGTYPE VARCHAR (50) Picture Type. ASP.NET To use identified type IMGDataImage to store binary data
2, store pictures to the SQL Server database
In order to be stored in the table, you first go upload them on your web server, you can develop a web form, which is used to enter the image in the TextBox Web Control in your web server. Set your ENCTYPE attribute to: MyLtipart / Formdata.
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.pe N (); int NumrowSaffected = Command.executenonQuery (); connection.close ();
3. Restore read from the database
Let us now read the data we put into the data from SQL Server! We will output pictures to your browser, you can also store it to your location. 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 ();
Note that Response.BinaryWrite is not response.write.
Let's give you a deposit and read programs for C # WinForm. All of them please compare themselves! (For convenience, I simplify the database field into two: imgtitle and imgdata.
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.IO; using System.Data.SqlClient; namespace WindowsApplication21 {///
/// private void initializecomponent () {this.button1 = new system.windows.Forms.Button (); this.pic1 = new system.windows.forms.picturebox (); this.button2 = new system. Windows.Forms.Button (); this.openfiledialog1 = new system.windows.forms.openfiledialog (); this.label2 = new system.windows.forms.label (); this.suspendlayout (); // // Button1 / / This.button1.location = new system.drawing.point (0, 40); this.button1.name = "button1"; this.button1.size = new system.drawing.size (264, 48); this.button1 .Tabindex = 0; this.button1.text = "Add new picture"; this.button1.click = new system.eventhandler (this.button1_click); // // pic1 // this.pic1.location = new system .Drawing.point (280, 8); this.pic1.name = "pic1"; this.pic1.size = new system.drawing.size (344, 264); this.pic1.tabindex = 3; this.pic1. TabStop = false; // // Button2 // this.button2.location = new system.drawing.point (0, 104); this.button2.name = "button2"; this.button2.size = new system.drawing. S Ize (264, 40); this.button2.tabindex = 4; this.button2.text = "Recovered images from the database"; this.button2.click = new system.eventhandler (this.button2_click); // // OpenFiledialog1 // this.openfiledialog1.filter = "/" image file (* .jpg, *. bmp, *. gif) | * .jpg | * .bmp | * .gif / "; /// Label2 // This.Label2.Location = new system.drawing.point (0, 152); this.label2.name = "label2"; this.label2.size = new system.drawing.size (264, 48); this.label2. TabINDEX = 5; // // Form1 // this.autoscalebasesize = new system.drawing.size (6, 14); this.clientsize =
New system.drawing.size (632, 273); this.controls.addrange (new system.windows.forms.control [] {this.label2, this.button2, this.pic1, this.button1}); this.name = "Form1"; this.text = "form1"; this.load = new system.Eventhandler (this.form1_load); this.ResumeLayout (false);} #endregion ///
/// [STAThread] static void Main () {Application.Run (new Form1 ());} private void button1_Click (object sender, System.EventArgs e) {openFileDialog1.ShowDialog (); if (openFileDialog1. FileName.Trim () = "") {FileInfo fi = new FileInfo (openFileDialog1.FileName);! string imgtitle = openFileDialog1.FileName; int imgdatalen = (int) fi.Length; byte [] imgdata = new byte [imgdatalen]; Stream imgdatastream = fi.OpenRead (); int n = imgdatastream.Read (imgdata, 0, imgdatalen); if (conn.State == ConnectionState.Open) conn.Close (); ConnectionString = "Integrated Security = SSPI;" "Initial Catalog = mydb;" "Data Source = localhost;"; conn.ConnectionString = ConnectionString; try {string mySelectQuery = "INSERT INTO ImageStore (imgtitle, imgdata) VALUES (@imgtitle, @imgdata)"; // string mySelectQuery = "Update imagestore set imgtitle = @ imgtitle, imgdata = @ imgdata"; sqlcommand mycommand = new SQLCommand (MySelectQuery, conn); SQLParameter paramtitle = new sqlparameter ("@ IMG title ", SqlDbType.VarChar, 50); paramTitle.Value = imgtitle; myCommand.Parameters.Add (paramTitle); SqlParameter paramData = new SqlParameter (" @imgdata ", SqlDbType.Image); paramData.Value = imgdata; myCommand.Parameters .Add (paramdata); conn.open (); int NumrowSaffected = myCommand.executenonQuery (); conn.close ();} catch (exception err) {messageBox.show ("You entered the name may be in the database or entered It is empty, please check! " Err.Tostring ());
