SqlServer数据库实现图片存入与读取

最新推荐文章于 2024-10-16 10:06:37 发布

原创最新推荐文章于 2024-10-16 10:06:37 发布 · 2.3k 阅读

5 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sqlserver #数据库 #sql server #webcontrols #string

C＃实现图片存入与读取专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何在SQL Server中创建用于存储图片的数据库表，并提供了ASP.NET代码示例，展示了如何将图片上传到数据库以及从数据库读取图片并显示。主要涉及的字段包括ImageData（二进制数据）、ImageContentType（文件类型）、ImageDescription（文件说明）和ImageSize（文件长度）。

首先在SQL Server中建立一个图片存储的数库表，ImageData Column为图象二进制数据储存字段，ImageContentType Column为图象文件类型记录字段，
ImageDescription Column为储蓄图象文件说明字段,ImageSize Column为储存图象文件长度字段，结构如下：
CREATE TABLE [dbo].[ImageStore] (
   [ImageID] [int] IDENTITY (1, 1) NOT NULL ,
   [ImageData] [image] NULL ,
   [ImageContentType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
   [ImageDescription] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
   [ImageSize] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

向数据库中存入图片：

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

namespace UpLoadFile
{
/// <summary>
/// Summary description for UpLoadImage.
/// </summary>
public class UpLoadImage : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.Button btnUpload;
  protected System.Web.UI.WebControls.Label txtMessage;
  protected System.Web.UI.WebControls.TextBox txtDescription;
  protected System.Web.UI.HtmlControls.HtmlTable Table1;
  protected System.Web.UI.HtmlControls.HtmlInputFile UP_FILE;//HtmlControl、WebControls控件对象
   protected Int32 FileLength = 0;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // Put user code to initialize the page here
   if(!Page.IsPostBack)
   {
      }
  }

  #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.btnUpload.Click += new System.EventHandler(this.btnUpload_Click);
    this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  private void btnUpload_Click(object sender, System.EventArgs e)
  {
   HttpPostedFile UpFile = this.UP_FILE.PostedFile; //HttpPostedFile对象，用于读取图象文件属性
   FileLength = UpFile.ContentLength; //记录文件长度
   try
   {
    if (FileLength == 0)
    { //文件长度为零时
     txtMessage.Text = "<b>请你选择你要上传的文件</b>";
    }
    else
    {
     Byte[] FileByteArray = new Byte[FileLength]; //图象文件临时储存Byte数组
     Stream StreamObject = UpFile.InputStream; //建立数据流对像
     //读取图象文件数据，FileByteArray为数据储存体，0为数据指针位置、FileLnegth为数据长度
     StreamObject.Read(FileByteArray,0,FileLength);
     //建立SQL Server链接
     SqlConnection Con = new SqlConnection("uid=sa;pwd= ;initial catalog=EE;data source=127.0.0.1;Connect Timeout=90");
     String SqlCmd = "INSERT INTO ImageStore (ImageData, ImageContentType, ImageDescription, ImageSize) VALUES (@Image, @ContentType, @ImageDescription, @ImageSize)";
     SqlCommand CmdObj = new SqlCommand(SqlCmd, Con);
     CmdObj.Parameters.Add("@Image",SqlDbType.Binary, FileLength).Value = FileByteArray;
     CmdObj.Parameters.Add("@ContentType", SqlDbType.VarChar,50).Value = UpFile.ContentType; //记录文件类型
     //把其它单表数据记录上传
     CmdObj.Parameters.Add("@ImageDescription", SqlDbType.VarChar,200).Value = txtDescription.Text;
     //记录文件长度，读取时使用
     CmdObj.Parameters.Add("@ImageSize", SqlDbType.BigInt,8).Value = UpFile.ContentLength;
     Con.Open();
     CmdObj.ExecuteNonQuery();
     Con.Close();
     txtMessage.Text = "<p><b>OK!你已经成功上传你的图片</b>";//提示上传成功
    }
   }
   catch (Exception ex)
   {
    txtMessage.Text = ex.Message.ToString();
   }

  }

}
}

将数据库中的图片数据读出来显示：

namespace UpLoadFile
{
/// <summary>
/// Summary description for ReadImage.
/// </summary>
public class ReadImage : System.Web.UI.Page
{
  private void Page_Load(object sender, System.EventArgs e)
  {
   // Put user code to initialize the page here
   if(!Page.IsPostBack)
   {
    string id = Request.QueryString["ImgID"];   //得到图片的ID

    if (id != ""&& id != null && id != string.Empty)
    {
     ShowImage( id);
    }
   }
  }

}
#endregion

  public void ShowImage(string id)
  {
   int ImgID = Convert.ToInt32(id); //ImgID为图片ID
   //建立数据库链接
   SqlConnection Con = new SqlConnection("uid=sa;pwd= ;initial catalog=EE;data source=127.0.0.1;Connect Timeout=90");
   String SqlCmd = "SELECT * FROM ImageStore WHERE ImageID = @ImageID";
   SqlCommand CmdObj = new SqlCommand(SqlCmd, Con);
   CmdObj.Parameters.Add("@ImageID", SqlDbType.Int).Value = ImgID;
   try
   {
    Con.Open();
    SqlDataReader SqlReader = CmdObj.ExecuteReader();
    SqlReader.Read();
    Response.ContentType = (string)SqlReader["ImageContentType"];//设定输出文件类型
    //输出图象文件二进制数制
    Response.OutputStream.Write((byte[])SqlReader["ImageData"], 0, (int)SqlReader["ImageSize"]);
    Response.End();
    Con.Close();
     }
   catch
   {
    Response.Write("<script>alert('该图片不存在');</script>");
    return;
   }

}
}
}