ASP.NET Access SQL Server Database Picture
Author: wyhw Time: 2003-5-11 Recommended Level: ★ View author information and author anthology
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: establish a table of such a structure in SQL Server: Column Type Type Id Integer Profile Id Imgtitle Varchar (50) Title ImgType Varchar (50) Picture Type. ASP.NET To identify type IMGData Image is used to store binary data 2. Store pictures to the SQL Server database In order to be stored in the table, you first upload them on your web server, you can develop a web form, it is used to transfer the client TEXTBOX Web The image in Control is coming to your web server. Your encType property 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.param ETERS.ADD (paramtype); int NumrowSaffected = Command.executenonQuery (); connection.close (); 3, recovering from the database Now let us have to read us from SQL Server Data! 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 ["ivtype"]. TOSTRING (); response.binaryWrite ((byte []) DR ["IMGData"]);} connection.close ();} It is response.binaryWrite instead of response.write. Here you give a depositation and read programs for C # WinForm. All of them please compare it yourself! (For the sake of 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.form. Using system.data; using system.data.sqlclient; Namespace Windowsapplication21 {///
/// private System.ComponentModel.Container components = null; private string ConnectionString = "Integrated Security = SSPI; Initial Catalog =; Data Source = localhost;"; private SqlConnection conn = null; private SqlCommand cmd = null; private System.Windows.Forms.Button button2; private System.Windows.Forms.PictureBox pic1; private System.Windows.Forms.OpenFileDialog openFileDialog1; private string sql = null; private System.Windows.Forms.Label label2; private string nowId = NULL; Public Form1 () {// // Windows Form Designer Support for // InitializeComponent (); conn = new sqlconnection (connectionstring); // // Todo: Add any constructor code after INITIALIZEComponent call / /} ///
/// 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 ());
