数据库存图片和文件，客户端浏览图片或下载文件

数据库存图片和文件，客户端浏览图片或下载文件（通过Model层，多层应用） 

1、数据库：

 CREATE TABLE [TArticle] ([Pkid] [int] IDENTITY (1, 1) NOT NULL ,[ArticleTitle] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[AttachmentFile] [image] NULL ,[FileExtName] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,CONSTRAINT [PK_tArticle] PRIMARY KEY CLUSTERED ([Pkid]) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO

  

2、模型层：

using System;using System.Collections.Generic;using System.Web;namespace WebSqlImageFile.bol{ public class Article { public Article(){ } private int _pkid; private string _articletitle; private byte[] _attachmentfile; private string _fileextname; public int Pkid { set { _pkid = value; } get { return _pkid; } } public string ArticleTitle { set { _articletitle = value; } get { return _articletitle; } } public byte[] AttachmentFile { set { _attachmentfile = value; } get { return _attachmentfile; } } public string FileExtName { set { _fileextname = value; } get { return _fileextname; } } }}

 

 

3、DAL层：

using System;using System.Collections.Generic;using System.Web;using System.Data;using System.Data.SqlClient;using WebSqlImageFile.bol;using System.IO;namespace WebSqlImageFile.dal{ public class ArticleDal { const string connection_string = @"Data Source=.;Initial Catalog=NorthwindCN;Integrated Security=True"; public List<bol.Article> GetList() { List<bol.Article> list = new List<bol.Article>(); string sql = "SELECT [Pkid], [ArticleTitle], [AttachmentFile], [FileExtName] FROM [TArticle]"; using (SqlConnection objConn = new SqlConnection(connection_string)) { objConn.Open(); SqlCommand objCmd = new SqlCommand(sql,objConn); SqlDataReader objReader = objCmd.ExecuteReader(); while (objReader.Read()) { bol.Article bo = new Article(); bo.Pkid = objReader.GetInt32(0); bo.ArticleTitle = objReader.GetString(1); //字节数组读取(数据库字段类型为image类型) bo.AttachmentFile = (byte[])objReader.GetValue(2); //GetValue bo.FileExtName = objReader.GetString(3); list.Add(bo); } if (!objReader.IsClosed) objReader.Close(); objConn.Close(); } return list; } public bol.Article GetBo(int pkid) { bol.Article bo = null; string sql = "SELECT [Pkid], [ArticleTitle], [AttachmentFile], [FileExtName] FROM [TArticle] WHERE Pkid=" + pkid.ToString(); using (SqlConnection objConn = new SqlConnection(connection_string)) { objConn.Open(); SqlCommand objCmd = new SqlCommand(sql, objConn); SqlDataReader objReader = objCmd.ExecuteReader(); if (objReader.Read()) { bo = new Article(); bo.Pkid = objReader.GetInt32(0); bo.ArticleTitle = objReader.GetString(1); //字节数组读取(数据库字段类型为image类型) bo.AttachmentFile = (byte[])objReader.GetValue(2); bo.FileExtName = objReader.GetString(3); } if (!objReader.IsClosed) objReader.Close(); objConn.Close(); } return bo; } public bool Insert(bol.Article bo,out int pkid) { bool is_ok = false; pkid = 0; string sql = @"INSERT INTO [TArticle]([ArticleTitle], [AttachmentFile], [FileExtName]) VALUES(@ArticleTitle,@AttachmentFile,@FileExtName) "; sql += " ;select @@identity; "; List<SqlParameter> paras = new List<SqlParameter>(); paras.Add(new SqlParameter("@ArticleTitle", bo.ArticleTitle)); //byte数组写入(数据库字段类型为image类型) SqlParameter para_AttachmentFile = new SqlParameter("@AttachmentFile", SqlDbType.Image); //参数类型为Image para_AttachmentFile.Value = bo.AttachmentFile; paras.Add(para_AttachmentFile); paras.Add(new SqlParameter("@FileExtName", bo.FileExtName)); using (SqlConnection objConn = new SqlConnection(connection_string)) { objConn.Open(); SqlCommand objCmd = new SqlCommand(sql, objConn); objCmd.Parameters.AddRange(paras.ToArray()); object objPkid = objCmd.ExecuteScalar(); if (objPkid != null) if (Int32.TryParse(objPkid.ToString(), out pkid)) { is_ok = true; } objConn.Close(); } return is_ok; } // }}

 

4、BLL业务逻辑层（省略）

 

5、界面层

（1）、添加：

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using WebSqlImageFile.bol;using WebSqlImageFile.dal;namespace WebSqlImageFile{ public partial class Upload : System.Web.UI.Page { protected void btnSubmit_Click(object sender, EventArgs e) { //取值 string title = txtTitle.Text=txtTitle.Text.Trim(); HttpPostedFile upload_file = fileUpload.PostedFile; //判断 if (title != string.Empty && upload_file != null && upload_file.ContentLength > 0) { bol.Article bo = new Article(); //bo.Pkid= bo.ArticleTitle = title; //bo.AttachmentFile int len = upload_file.ContentLength; //读上传文件,上传文件字节大小 bo.AttachmentFile = new byte[len]; //读上传文件,初始化一个byte数组,长度为上传文件字节大小 upload_file.InputStream.Read(bo.AttachmentFile, 0, len); //读上传文件,上传文件.输入流.读(字节数组,从0开始,长度为文件大小) //bo.FileExtName= string[] ss = upload_file.FileName.Split(new char[]{'//'}); string file_name = ss[ss.Length - 1];//文件名 string[] ss2 = file_name.Split(new char[] { '.' }); if (ss2.Length > 1) bo.FileExtName = ss2[ss2.Length - 1]; else bo.FileExtName = ""; //没有扩展名 //保存 int pkid = 0; bool b = new dal.ArticleDal().Insert(bo,out pkid); if (b) { //lblInfo.Text = string.Format("提交成功,内码:{0}.",pkid); //txtTitle.Text = ""; Response.Redirect("Show.aspx?id=" + pkid.ToString(),false); } } else { lblInfo.Text = "资料不完善!"; } } // }}

 

（2）、BLOB输出页面：（图片显示或文件下载）

去除aspx文件中的所有HTML代码，只留一行声明行

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AttachF.aspx.cs" Inherits="WebSqlImageFile.AttachF" %>

 

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.IO;using System.Drawing;using System.Drawing.Imaging;namespace WebSqlImageFile{ public partial class AttachF : System.Web.UI.Page { private int pkid = 0; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if (Request["id"] != null) { if (Int32.TryParse(Request["id"], out pkid)) { //show bol.Article bo = new dal.ArticleDal().GetBo(pkid); if (bo != null) { //附件是图片 if ("jpg|jpeg|jpe｜gif".Contains(bo.FileExtName.ToLower())) ExportImage(bo);//ExportDownload(bo); else //附件是二进制 ExportDownload(bo); } } } } } private void ExportImage(bol.Article bo) { MemoryStream stream = new MemoryStream(); stream.Write(bo.AttachmentFile, 0, bo.AttachmentFile.Length); Bitmap bitmap = new Bitmap(stream); string content_type = "image/jpeg"; //默认为jpg格式 ImageFormat img_format = ImageFormat.Jpeg; //默认为jpg格式 if (bo.FileExtName.ToLower().CompareTo("gif") == 0) { content_type = "image/gif"; img_format = ImageFormat.Gif; } else if ("jpg|jpeg|jpe".Contains(bo.FileExtName.ToLower())) { content_type = "image/jpeg"; img_format = ImageFormat.Jpeg; } Response.ContentType = content_type; bitmap.Save(Response.OutputStream, img_format); } private void ExportDownload(bol.Article bo) { string fileName = string.Format("attch_{0}.{1}", bo.Pkid, bo.FileExtName); //客户端保存的文件名 Response.Clear(); Response.Buffer = true; Response.Charset = "utf-8"; Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName); Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); Response.ContentType = "application/octet-stream"; Page.EnableViewState = false; Response.BinaryWrite(bo.AttachmentFile); } // }}

 

（3）、展示页：

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using WebSqlImageFile.bol;using WebSqlImageFile.dal;namespace WebSqlImageFile{ public partial class Show : System.Web.UI.Page { protected int pkid = 0; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if (Request["id"] != null) { if (Int32.TryParse(Request["id"], out pkid)) { //show bol.Article bo = new dal.ArticleDal().GetBo(pkid); if (bo != null) { lblPkid.Text = bo.Pkid.ToString(); lblTitle.Text = bo.ArticleTitle; if ("jpg|jpeg|jpe｜gif".Contains(bo.FileExtName.ToLower())) { //img li_img.Visible = true; } li_download.Visible = true; } } } } } // }}

 

（4）、列表页

<asp:Repeater ID="rp_list" runat="server"><HeaderTemplate> <table border="0" cellpadding="0" cellspacing="0"> <col style="width:240px; text-align:center;" /> <col style="width:80px; text-align:center;" /> <col style="width:80px; text-align:center;" /> <col style="width:80px; text-align:center;" /> <tr> <th>标题</th> <th>内码</th> <th>扩展名</th> <th>下载</th> </tr></HeaderTemplate><ItemTemplate> <tr> <td><a href="Show.aspx?id=<%#((WebSqlImageFile.bol.Article)GetDataItem()).Pkid%>"><%#((WebSqlImageFile.bol.Article)GetDataItem()).ArticleTitle%> </a>   </td> <td>[ <%#((WebSqlImageFile.bol.Article)GetDataItem()).Pkid%> ]   </td> <td>[ <%#((WebSqlImageFile.bol.Article)GetDataItem()).FileExtName%> ]   </td> <td><a href="AttachF.aspx?id=<%#((WebSqlImageFile.bol.Article)GetDataItem()).Pkid%>" target="download">下载</a></td> </tr></ItemTemplate><FooterTemplate> </table></FooterTemplate></asp:Repeater>

 

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using WebSqlImageFile.bol;using WebSqlImageFile.dal;namespace WebSqlImageFile{ public partial class List : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { List<bol.Article> list = new dal.ArticleDal().GetList(); rp_list.DataSource = list; rp_list.DataBind(); } } // }}

 

 

 

 6、小结：

（1）、从上传控件到byte数组：

int len = upload_file.ContentLength;                         //读上传文件,上传文件字节大小
bo.AttachmentFile = new byte[len];                           //读上传文件,初始化一个byte数组,长度为上传文件字节大小
upload_file.InputStream.Read(bo.AttachmentFile, 0, len);     //读上传文件,上传文件.输入流.读(字节数组,从0开始,长度为文件大小)

（2）、从byte数组插入到数据库Image类型的字段里：

SqlParameter para_AttachmentFile = new SqlParameter("@AttachmentFile", SqlDbType.Image); //参数类型为Image
para_AttachmentFile.Value = bo.AttachmentFile;
paras.Add(para_AttachmentFile);

（3）、从数据库Image类型的字段读出到byte数组：
bo.AttachmentFile = (byte[])objReader.GetValue(2);

（4）、从byte数组输出到aspx页面：

    （4.1）提供下载：

private void ExportDownload(bol.Article bo)
{
    string fileName = string.Format("attch_{0}.{1}", bo.Pkid, bo.FileExtName); //客户端保存的文件名

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "utf-8";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
    Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
    Response.ContentType = "application/octet-stream";
    Page.EnableViewState = false;
    Response.BinaryWrite(bo.AttachmentFile);
}

    （4.2）图片输出显示：

private void ExportImage(bol.Article bo)
{
    MemoryStream stream = new MemoryStream();
    stream.Write(bo.AttachmentFile, 0, bo.AttachmentFile.Length);
    Bitmap bitmap = new Bitmap(stream);

    string content_type = "image/jpeg";          //默认为jpg格式
    ImageFormat img_format = ImageFormat.Jpeg;   //默认为jpg格式
    if (bo.FileExtName.ToLower().CompareTo("gif") == 0)
    {
        content_type = "image/gif";
        img_format = ImageFormat.Gif;
    }
    else if ("jpg|jpeg|jpe".Contains(bo.FileExtName.ToLower()))
    {
        content_type = "image/jpeg";
        img_format = ImageFormat.Jpeg;
    }

    Response.ContentType = content_type;
    bitmap.Save(Response.OutputStream, img_format);
}