ASP.NET存取Sql Server数据库Image字段数据
数据库中一般保存图片路径。但有些情况下,用的是Image字段,这就涉及到Sql Server数据库中Image字段数据的读取技术。
1.存入Image字段数据
if (FileUpload1.HasFile)
{
//注意:IE下有效,不同浏览器的文件类型是不同的。
if (this.FileUpload1.PostedFile.ContentType == "image/bmp" ||
this.FileUpload1.PostedFile.ContentType == "image/pjpeg" ||
this.FileUpload1.PostedFile.ContentType == "image/gif" ||
this.FileUpload1.PostedFile.ContentType == "image/x-png")
{
int bookId = Convert.ToInt32(TextBox1.Text);
byte[] bookImg = FileUpload1.FileBytes;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("insert into bookImage values(@bookId,@bookImg)", connection))
{
command.Parameters.Add(new SqlParameter("@bookId", bookId));
command.Parameters.Add(new SqlParameter("@bookImg", bookImg));
connection.Open();
command.ExecuteNonQuery();
}
}
}
}
2.读取并显示Image字段数据
2.1 用一般处理程序读取
public class Handler : IHttpHandler
{
public bool IsReusable
{
get
{
return false;
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "image/jpeg/gif/x-png";
context.Response.Cache.SetCacheability(HttpCacheability.Public);
context.Response.BufferOutput = false;
Int32 id = -1;
Stream stream = null;
if (context.Request.QueryString["bookId"] != null && context.Request.QueryString["bookId"] != "")
{
id = Convert.ToInt32(context.Request.QueryString["bookId"]);
stream = BookManager.GetImage(id);
if (stream != null)
{
const int buffersize = 1024 * 16;
byte[] buffer = new byte[buffersize];
int count = stream.Read(buffer, 0, buffersize);
while (count > 0)
{
context.Response.OutputStream.Write(buffer, 0, count);
count = stream.Read(buffer, 0, buffersize);
}
}
}
}
}
GetImage方法:
public static Stream GetImage(int bookId)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("select bookImg from bookImage where bookId=@bookId ", connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter("@bookId", bookId));
connection.Open();
object result = command.ExecuteScalar();
try
{
return new MemoryStream((byte[])result);
}
catch
{
return null;
}
}
}
}
也可以用下面的代码:
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "image/jpeg/gif/x-png";
context.Response.Cache.SetCacheability(HttpCacheability.Public);
context.Response.BufferOutput = false;
Int32 id = -1;
Stream stream = null;
if (context.Request.QueryString["bookId"] != null && context.Request.QueryString["bookId"] != "")
{
id = Convert.ToInt32(context.Request.QueryString["bookId"]);
byte[] file = (Byte[])GetImage(id); //把图片信息取出来
context.Response.BinaryWrite(file);
}
}
public static Byte[] GetImage(int bookId)
{
using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("select bookImg from bookImage where bookId=@bookId ", connection))
{
command.Parameters.Add(new SqlParameter("@bookId", bookId));
connection.Open();
SqlDataReader result = command.ExecuteReader();
if (result.Read())
{
try
{
return (byte[])result[0];
}
catch
{
return null;
}
}
else
return null;
}
}
}
2.2 页面显示
如果用Image控件,则代码为:
Image1.ImageUrl = "Handler.ashx?bookid=" + bookId;
如果用Image标签,则为:
<img alt ="<%#Eval("bookNM") %>" src ='../Handler.ashx?bookId=<%#Eval("ID") %>'/>