将图片插入数据库并使用asp.net读取出来的正确方法

将图片插入数据库并使用asp.net读取出来的正确方法 写本文是因为今天见到CSDN的首页上一篇存在明显失误的名为“在Asp.Net中从 sqlserver检索(retrieve)图片”的文章。不说其错误是因为用其方法确实能从数据库中读取出图 片并显示在浏览器，说其失误是因为代码的意图不能被完全的实现，作者也似乎对http协 议以及浏览器在处理http数据的流程一知半解。 1、如何出错 以下是这片文章提到的方法: 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 显然，编程者是想将Person中所有的中的PersonImage字段所存储的图片一次性地 输出到浏览器中，并且在输出成功地情况下在已输出的图片的下方打印出“Person info successfully retrieved!”信息。然而事实上上述代码仅仅能正确地输出第一条记录中的图片。 对于浏览器来说，一个http请求获取一个文件(html或者图片)，所以以上代码的输出将被 作为一个文件(类型依据Response.ContentType = myDataReader.Item("PersonImageType")定) 被浏览器处理。如果http相应的类型是image/jpeg之类的图片，则浏览器使用相应的图片解 析功能对这一个图片文件进行解析。因此，上述代码的显示结果只能是第一条记录 PersonImage字段的图片。后面的记录输出的图片数据将成为第一张图片的多余数据(此点 具有普遍性，但并非绝对，依图片的格式而定)，从而后面的“Person info successfully retrieved～”的信息也自然无法本显示出来，因为这些信息已经是图片文件里面的编码了。 2、正确的做法 A、将图片输入到数据库中，以下是一个将图片输入到数据库的代码片断:(完整的DEMO 程序见附录一) 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、将数据库中的图片读出来的代码片断:(完整DEMO程序见附录二) 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(); } 这段代码可置于Page_Load事件中，数据图片要注意的两点是: 一、 设置正确的ContentType(http中的content-type)，图片的content-type格式一般为image/*，如jpeg为image/jpeg，bmp为image/bmp等等。 二、 仅仅输出一张图片二进制流，asp.net 中Page_Load事件先于页面输出被触发，因此图片的输出可以在此事件中进行，直接操作Reponse对象，避免输出与图片无关的而外信息(额外的第二张图片或者文字)。图片的二进制流输出后及时使用Response.End()方法结束http响应，避免页面中的额外信息被asp.net的引擎默认输出到客户端。 附录一: 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; ///

/// Required designer variable. ///

private System.ComponentModel.Container components = null; public MainForm() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } ///

/// Clean up any resources being used. ///

protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

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 ///

/// The main entry point for the application. ///

[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; } } } 附录二: 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 description for ReadImage. ///

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 call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }