The correct way to insert a picture into a database and read it using asp.net

asp.net| Insert | data | Database Inserts a picture into a database and uses ASP.net to read the correct method



Writing this article is because today I saw Csdn's first page, an article named "Retrieving (retrieve) a picture from SQL Server in ASP.net". The mistake is not to say it is because the method can actually read out the image from the database and display in the browser, the fault is because the intent of the code can not be fully implemented, the author also seems to have a smattering of HTTP protocol and the process of processing HTTP data.



1. How to make Mistakes

The following are the methods mentioned in this article:

Public Sub Page_Load (sender as Object, E as EventArgs)
Dim MyConnection as New SqlConnection (ConfigurationSettings.AppSettings ("ConnectionString"))
Dim mycommand as New SqlCommand ("Select * from person", MyConnection)
Try
Myconnection.open ()
Dim MyDataReader as SqlDataReader
MyDataReader = Mycommand.executereader (commandbehavior.closeconnection)

Do while (Mydatareader.read ())
Response.ContentType = Mydatareader.item ("Personimagetype")
Response.BinaryWrite (Mydatareader.item ("Personimage"))
Loop

Myconnection.close ()
Response.Write ("Person info successfully retrieved!")
Catch Sqlexc as SqlException
Response.Write ("Read Failed:" & Sqlexc.tostring ())
End Try
End Sub

Obviously, programmers want to output a picture of the Personimage field stored in all records in the person table to the browser at once, and print out "person info" under the output picture if the output succeeds successfully retrieved! " Information. In fact, however, the above code only correctly prints the picture in the first record. For browsers, an HTTP request acquires a file (HTML or picture), so the output of the above code will be used as a file (type based on Response.ContentType = Mydatareader.item ("Personimagetype ) is processed by the browser. If the corresponding type of HTTP is a picture such as Image/jpeg, the browser uses the corresponding image parsing feature to parse the picture file. As a result, the above code can only be displayed as a picture of the first record personimage field. The subsequent record output of the picture data will become the redundant data of the first picture (this point is universal, but not absolute, depending on the format of the picture), thereby following the "person info successfully retrieved! "The information is naturally not shown, because the information is already encoded in the image file.



2, the right way

A, the image into the database, the following is a picture into the database code snippets: (Complete demo program See appendix I)

FileStream Fs=file.openread (Filepath.text);

Byte[] Content=new Byte[fs. Length];

Fs. Read (content, 0,content. Length);

Fs. Close ();



SqlConnection conn=new SqlConnection ("Integrated Security=sspi; Persist Security info=false;initial catalog=databaseimage;data source= (local) ");

Conn. Open ();



SqlCommand Comm=conn. CreateCommand ();

comm.commandtext= "INSERT into Images (Image, ContentType) VALUES (@image, @contentType)";

Comm.commandtype=commandtype.text;



Comm. Parameters.Add ("@image", Sqldbtype.image). Value=content;

Comm. Parameters.Add ("@contentType", SqlDbType.NVarChar). Value=

getContentType (New FileInfo (Filepath.text). Extension.remove (0,1));



if (comm. ExecuteNonQuery () ==1)

{

MessageBox.Show ("Successfully insert image into database!");

}

Else

{

MessageBox.Show ("Failed to insert image into database");

}



Conn. Close ();



B, read the picture in the database code snippets: (Full demo program see appendix II)

try{

SqlConnection conn=new SqlConnection ("Integrated Security=sspi; Persist Security info=false;initial catalog=databaseimage;data source= (local) ");

Conn. Open ();



SqlCommand Comm=conn. CreateCommand ();

comm.commandtext= "SELECT * from Images where id= @id";

Comm.commandtype=commandtype.text;



Comm. Parameters.Add ("@id", Sqldbtype.bigint). Value=int. Parse (request["id"]);



SqlDataReader Reader=comm. ExecuteReader ();

while (reader. Read ())

{

response.contenttype=reader["ContentType"]. ToString ();

Response.BinaryWrite ((byte[]) reader["Image");

}

Response.End ();



Conn. Close ();

}

Catch

{

Response.End ();

}

This code can be placed in the Page_Load event, the data picture to note that two points are:

First, set the correct contenttype (Content-type in HTTP), the picture Content-type format is generally image/*, such as JPEG for image/jpeg,bmp image/bmp and so on.

Second, only output a picture binary flow, ASP.net Page_Load event before the page output is triggered, so the image output can be carried out in this event, direct Operation Reponse Object, to avoid the output of the picture-independent information (additional second picture or text). The binary stream output of the picture is used in time to end the HTTP response using the Response.End () method to avoid the extra information in the page being exported by the ASP.net engine to the client.



Hope this article can play a useful role! ^_^



Appendix I:

MainForm.cs

Using System;

Using System.Drawing;

Using System.Collections;

Using System.ComponentModel;

Using System.Data;

Using System.Data.SqlClient;

Using System.IO;

Using System.Windows.Forms;



Namespace Insertimagetodatabase

{

public class MainForm:System.Windows.Forms.Form

{

Private System.Windows.Forms.OpenFileDialog Openfiledlg;

Private System.Windows.Forms.TextBox FilePath;

Private System.Windows.Forms.Button BrowseButton;

Private System.Windows.Forms.Button Insertbutton;

<summary>

Required designer variable.

</summary>

Private System.ComponentModel.Container components = null;



Public MainForm ()

{

//

Required for Windows Form Designer support

//

InitializeComponent ();



//

Todo:add any constructor the code after InitializeComponent call

//

}



<summary>

Clean up any being used.

</summary>

protected override void Dispose (bool disposing)

{

if (disposing)

{

if (Components!= null)

{

Components. Dispose ();

}

}

Base. Dispose (disposing);

}



#region Windows Form Designer generated code

<summary>

Required to Designer support-do not modify

The contents is with the Code Editor.

</summary>

private void InitializeComponent ()

{

This.openfiledlg = new System.Windows.Forms.OpenFileDialog ();

This.filepath = new System.Windows.Forms.TextBox ();

This.browsebutton = new System.Windows.Forms.Button ();

This.insertbutton = new System.Windows.Forms.Button ();

This. SuspendLayout ();

//

Openfiledlg

//

THIS.OPENFILEDLG.DEFAULTEXT = "*.jpg;*.gif;*.bmp;*.png";

This.openFileDlg.Filter = "Image files|*.jpg;*.gif;*.bmp;*.png| All Files|*.* ";

//

FilePath

//

This.filePath.Location = new System.Drawing.Point (16, 16);

This.filePath.Name = "FilePath";

This.filePath.ReadOnly = true;

This.filePath.Size = new System.Drawing.Size (168, 20);

This.filePath.TabIndex = 0;

This.filePath.Text = "";

//

BrowseButton

//

This.browseButton.Location = new System.Drawing.Point (200, 16);

This.browseButton.Name = "BrowseButton";

This.browseButton.TabIndex = 1;

This.browseButton.Text = "&browse";

This.browseButton.Click + = new System.EventHandler (This.browsebutton_click);

//

Insertbutton

//

this.insertButton.Enabled = false;

This.insertButton.Location = new System.Drawing.Point (200, 56);

This.insertButton.Name = "Insertbutton";

This.insertButton.TabIndex = 2;

This.insertButton.Text = "&insert";

This.insertButton.Click + = new System.EventHandler (This.insertbutton_click);

//

MainForm

//

This. AutoScaleBaseSize = new System.Drawing.Size (5, 13);

This. ClientSize = new System.Drawing.Size (292, 273);

This. Controls.Add (This.insertbutton);

This. Controls.Add (This.browsebutton);

This. Controls.Add (This.filepath);

This. FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;

This. MaximizeBox = false;

This. Name = "MainForm";

This. Text = "Insert Image to Database";

This. ResumeLayout (FALSE);



}

#endregion



<summary>

The main entry point is for the application.

</summary>

[STAThread]

static void Main ()

{

Application.Run (New MainForm ());

}



private void Browsebutton_click (object sender, System.EventArgs e)

{

if (Openfiledlg.showdialog () ==dialogresult.ok)

{

Filepath.text=openfiledlg.filename;

Insertbutton.enabled=true;

}

}



private void Insertbutton_click (object sender, System.EventArgs e)

{

FileStream Fs=file.openread (Filepath.text);

Byte[] Content=new Byte[fs. Length];

Fs. Read (content, 0,content. Length);

Fs. Close ();



SqlConnection conn=new SqlConnection ("Integrated Security=sspi; Persist Security info=false;initial catalog=databaseimage;data source= (local) ");

Conn. Open ();



SqlCommand Comm=conn. CreateCommand ();

comm.commandtext= "INSERT into Images (Image, ContentType) VALUES (@image, @contentType)";

Comm.commandtype=commandtype.text;



SqlParameter Param=comm. Parameters.Add ("@image", sqldbtype.image);

Param. Value=content;

Comm. Parameters.Add ("@contentType", SqlDbType.NVarChar). Value=

getContentType (New FileInfo (Filepath.text). Extension.remove (0,1));



if (comm. ExecuteNonQuery () ==1)

{

MessageBox.Show ("Successfully insert image into database!");

}

Else

{

MessageBox.Show ("Failed to insert image into database");

}



Conn. Close ();

}



private string getContentType (string extension)

{

String type= "JPEG";

if (extension== "JPG")

{

Type= "JPEG";

}else

{

Type=extension;

}

Return "image/" +type;

}

}

}



Appendix II:

Readimage.aspx

<%@ Page language= "C #" codebehind= "ReadImage.aspx.cs" autoeventwireup= "false" inherits= "Readimage.readimage"% >

ReadImage.aspx.cs

Using System;

Using System.Collections;

Using System.ComponentModel;

Using System.Data;

Using System.Data.SqlClient;

Using System.Drawing;

Using System.Web;

Using System.Web.SessionState;

Using System.Web.UI;

Using System.Web.UI.WebControls;

Using System.Web.UI.HtmlControls;



Namespace Readimage

{

<summary>

Summary description for Readimage.

</summary>

public class ReadImage:System.Web.UI.Page

{

private void Page_Load (object sender, System.EventArgs e)

{

try{

SqlConnection conn=new SqlConnection ("Integrated Security=sspi; Persist Security info=false;initial catalog=databaseimage;data source= (local) ");

Conn. Open ();



SqlCommand Comm=conn. CreateCommand ();

comm.commandtext= "SELECT * from Images where id> @id";

Comm.commandtype=commandtype.text;



Comm. Parameters.Add ("@id", Sqldbtype.bigint). Value=int. Parse (request["id"]);



SqlDataReader Reader=comm. ExecuteReader ();

while (reader. Read ())

{

response.contenttype=reader["ContentType"]. ToString ();

Response.BinaryWrite ((byte[]) reader["Image");

}

Response.Write ("aaaaaa");

Response.End ();



Conn. Close ();

}

Catch

{

Response.End ();

}

}



#region Web Form Designer generated code

Override protected void OnInit (EventArgs e)

{

//

Codegen:this the call are required by the ASP.net Web Form Designer.

//

InitializeComponent ();

Base. OnInit (e);

}



<summary>

Required to Designer support-do not modify

The contents is with the Code Editor.

</summary>

private void InitializeComponent ()

{

This. Load + = new System.EventHandler (this. Page_Load);



}

#endregion

}

}