[SQL Server][.NET]上傳檔案到SQL Server資料庫(Varbinary)

2016-06-12

12314
0
SQL Server

最近專案中，使用者有大量小型文件型檔案(每個約1MB以內)要上傳到資料庫的需求，這篇先筆記Varbinary步驟:

Table ([varbinary](max)) Binary Large Objects (BLOBs) + ADO.NET SqlCommand
FileStream
FileTable

原本其他成員打算上傳到網站目錄中，但和PM考慮到日後管理及資料封存問題，於是改變需求希望儲存到資料庫中。

因為上傳檔案不大，這次先筆記二進位儲存(varbinary)，待會筆記SQL Server 2008功能filestream。

1.首先建立儲存文件檔案的資料表:

順道增加檔案資訊、檔案名稱、檔案大小等資訊。

CREATE TABLE [dbo].[Document](
	[GUID] [int] IDENTITY(1,1) NOT NULL,
	[CONTENT_TYPE] [nvarchar](100) NULL,
	[FILE_NAME] [nvarchar](50) NULL,
	[DATA] [varbinary](max) NULL,
	[SIZE] [numeric](13, 2) NULL,
	[MNT_USER] [varchar](20) NULL,
	[MNT_DT] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GUI:

2.新增Web 表單並將需求的控制項拖曳進來，並預寫Server Side會提供的Method

控制項:FileUpload、Button及Gridview
Event Method: Upload()、Gridview item Onclick=Downloadfile()、DeleteFile()

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
    <link href="Content/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:FileUpload ID="FileUpload1" runat="server" Width="300px" />
            <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" />
            <hr />
            <asp:GridView ID="gdvData" runat="server" CssClass="table table-hover table-bordered" OnRowDataBound="gdvData_RowDataBound"
                AutoGenerateColumns="false">
                <Columns>
                    <asp:BoundField DataField="FILE_NAME" HeaderText="文件名稱" />
                    <asp:TemplateField ItemStyle-HorizontalAlign="Center" HeaderText="下載">
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
                                CommandArgument='<%# Eval("GUID") %>'></asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-HorizontalAlign="Center" HeaderText="刪除">
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" OnClick="DeleteFile"
                                CommandArgument='<%# Eval("GUID") %>'></asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="SIZE" HeaderText="檔案大小(KB)" />
                    <asp:BoundField DataField="CONTENT_TYPE" HeaderText="文件類型" />
                    <asp:BoundField DataField="MNT_USER" HeaderText="維護人員" />
                    <asp:BoundField DataField="MNT_DT" HeaderText="上傳時間" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

3.撰寫Server Side程式，這邊先寫檔案上傳

將檔案讀入二進位資料byte[]後，直接作為ADO.NET SqlCommand 參數輸入。

protected void Upload(object sender, EventArgs e)
{
    string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string contentType = FileUpload1.PostedFile.ContentType;
    using (Stream fs = FileUpload1.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            string constr = ConfigurationManager.ConnectionStrings["CN"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "insert into Document values (@CONTENT_TYPE,@FILE_NAME,@DATA,@SIZE,@MNT_USER,@MNT_DT)";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@CONTENT_TYPE", contentType);
                    cmd.Parameters.AddWithValue("@FILE_NAME", filename);
                    cmd.Parameters.Add("@SIZE", SqlDbType.Decimal);
                    cmd.Parameters["@SIZE"].Value = Convert.ToDecimal(bytes.Length / 1024);
                    cmd.Parameters.AddWithValue("@MNT_USER", "User");
                    cmd.Parameters.AddWithValue("@MNT_DT", DateTime.Now);
                    cmd.Parameters.AddWithValue("@DATA", bytes);
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}

4.接著把Server Side程式碼其他部分補完

Gridview查詢、Gridview css、下載、刪除事件等

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class FileUploadToDb : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                loadGridData();
            }
        }
        private void loadGridData()
        {
            string constr = ConfigurationManager.ConnectionStrings["CN"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "SELECT GUID,CONTENT_TYPE,FILE_NAME,SIZE,MNT_USER,MNT_DT FROM Document ";
                    cmd.Connection = con;
                    con.Open();
                    gdvData.DataSource = cmd.ExecuteReader();
                    gdvData.DataBind();
                }
            }
        }

        
        protected void DownloadFile(object sender, EventArgs e)
        {
            string GUID = (sender as LinkButton).CommandArgument;
            byte[] bytes;
            string fileName, contentType;
            string constr = ConfigurationManager.ConnectionStrings["CN"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "select * from Document where GUID=@GUID";
                    cmd.Parameters.AddWithValue("@GUID", GUID);
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        sdr.Read();
                        bytes = (byte[])sdr["Data"];
                        contentType = sdr["CONTENT_TYPE"].ToString();
                        fileName = sdr["FILE_NAME"].ToString();
                    }
                }
            }

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = contentType;
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

        protected void DeleteFile(object sender, EventArgs e)
        {
            string GUID = (sender as LinkButton).CommandArgument;
            string constr = ConfigurationManager.ConnectionStrings["CN"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "Delete Document where GUID=@GUID";
                    cmd.Parameters.AddWithValue("@GUID", GUID);
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            Response.Redirect(Request.Url.AbsoluteUri);
        }

        protected void gdvData_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string FILE_NAME = Convert.ToString(e.Row.Cells[0].Text);
                e.Row.CssClass = FILE_NAME.Contains("test") ? "success" : "danger";
            }

        }
    }
}

上傳時將上傳檔的MIME內容類型紀錄下來，這樣下載時，就可以直接使用