书写本文是因为今天见到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;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public MainForm()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources 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 method for Designer support - do not modify
/// the contents of this method 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 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;
}
}
}
附录二：
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 call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
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;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public MainForm()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources 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 method for Designer support - do not modify
/// the contents of this method 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 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;
}
}
}
附录二：
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 call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
