C# 多文件与二进制互转数据库写入读出(上传文件二进制方式到DB中,下载DB中的二进制文件到本地)

C# 多文件与二进制互转数据库写入读出(上传文件二进制方式到DB中,下载DB中的二进制文件到本地)

一,上传部分

1. aspx页面

<tt>
        <tt class="td_title"> 上傳異常單檔案: <asp:FileUpload ID="FileUpload2" runat="server" multiple="multiple"/></tt>支持多份文件一起上傳
        <asp:Button ID="FileUploadButton" runat="server"  Text="保存檔案資料"  οnclick="FileUploadButton_Click"/>
        <br />
        <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
        <br /> 
        <tt><asp:TextBox ID="FileAQ_SID" runat="server" ReadOnly="true" Visible="false" /></tt>  
     </tt>

2.控制层

 /// <summary>
        /// 保存上傳的文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void FileUploadButton_Click(object sender, EventArgs e)
        {
            Stream fs = null;
            BinaryReader br = null;
            QcAqFile aqFile = new QcAqFile();
           

            if (FileUpload2.HasFile)//代表有选择文件要上傳
            {
                //文件系統路徑
                string serverPath = Server.MapPath("uploadFile\\");
                //如果不存在就創建
                if (!System.IO.Directory.Exists(serverPath))
                {
                    System.IO.Directory.CreateDirectory(serverPath);
                }

                HttpFileCollection uploadFiles = Request.Files;
                for (int i = 0; i < uploadFiles.Count; i++)
                {
                    HttpPostedFile postedFile = uploadFiles[i];
                    try
                    {
                        if (postedFile.ContentLength > 0)
                        {
                            Label2.Text += "文件 #" + (i + 1) + "：" + System.IO.Path.GetFileName(postedFile.FileName) + "<br/>";
                            postedFile.SaveAs(serverPath + System.IO.Path.GetFileName(postedFile.FileName));//把文件存到服務的硬盤位置
                            try
                            {
                                //获取上傳文件的字節大小
                                int length = FileUpload2.PostedFile.ContentLength;
                                3.獲取文件類型
                                //string FileType = FileUpload1.PostedFile.ContentType;
                                4.判斷文件類型
                                //if (FileType == "image/bmp" || FileType == "image/gif" || FileType == "image/jpeg" || FileType == "image/jpg" || FileType == "image/png" ||
                                //    FileType == "application/pdf" || FileType == "application/msexcel" || FileType == "application/msword")
                                //{

                                // FileUpload1.PostedFile.SaveAs(serverPath);//把文件存到服務的硬盤位置

                                fs = new FileStream(serverPath + System.IO.Path.GetFileName(postedFile.FileName), FileMode.Open);    //文件流
                                byte[] fileBytes = null;//new byte[fs.Length];  //設定字節流陣列的大小
                                br = new BinaryReader(fs);     //讀字節對象
                                fileBytes = br.ReadBytes((int)(fs.Length));  //將字節流讀入陣列中

                                aqFile.AQ_SID = Convert.ToInt32(this.FileAQ_SID.Text);
                                aqFile.AQ_SEQ = (i + 1).ToString();
                                aqFile.ENABLE_FLAG = "T";
                                aqFile.AQ_FILE_NAME = System.IO.Path.GetFileName(postedFile.FileName);
                                aqFile.AQ_FILE_BINARY = fileBytes;
                                _MES_DAO.UploadFile1(aqFile,Session[LoginInfo.USER_NAME].ToString());
                            }
                            catch (Exception Ex)
                            {
                                Label2.Text += "發生錯誤： " + Ex.Message;
                                log.Error("讀取文件錯誤!!!===>" + Ex.Message);
                                throw;
                            }
                            finally
                            {
                                br.Close();
                                fs.Close();
                            }
                        }
                    }
                    catch (Exception Ex)
                    {
                        Label2.Text += "發生錯誤： " + Ex.Message;
                    }
                }
            }
        }

3.DAO层

//初始化数据库连接串 
private QcAbnormalQualityDao()
            : base()
        {
            DB_CONNECTION = ConfigurationManager.ConnectionStrings["MESDB"].ConnectionString;
        }
//得到SqlConnection 
private SqlConnection Connection1
        {
            get
            {
                SqlConnection SQLconn = new SqlConnection(this.DB_CONNECTION);
                return SQLconn;
            }
        }


/// <summary>
        /// 將文件寫入數據庫
        /// </summary>
        /// <param name="qcAqFile">資料群組</param>
        /// <param name="emp">員工</param>
        /// <returns></returns>

        public string UploadFile1(QcAqFile qcAqFile, string emp)
        {
            List<SqlParameter> args = new List<SqlParameter>();
            int result2 = 0;

            string sql = @"insert into [dbo].[QC_AQ_FILE] values 
                         (@AQ_SID, @AQ_SEQ, @AQ_FILE_NAME, @AQ_FILE_BINARY, @ENABLE_FLAG, @CREATE_USER, getdate(), @CREATE_USER,  getdate())";

            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@AQ_SID", DbType.String) { Value = qcAqFile.AQ_SID },
            new SqlParameter("@AQ_SEQ", DbType.String) { Value =  qcAqFile.AQ_SEQ },
            new SqlParameter("@AQ_FILE_NAME", DbType.String) { Value = qcAqFile.AQ_FILE_NAME },
            new SqlParameter("@AQ_FILE_BINARY", DbType.Byte) { Value = qcAqFile.AQ_FILE_BINARY },
            new SqlParameter("@ENABLE_FLAG", DbType.String) { Value = qcAqFile.ENABLE_FLAG },
            new SqlParameter("@CREATE_USER", DbType.String) { Value = emp }};

            using (SqlConnection con1 = this.Connection1)
            {
                SqlTransaction trans = null; //事务可以不用
                try
                {
                    con1.Open();
                    trans = con1.BeginTransaction();
                    using (SqlCommand com = new SqlCommand())
                    {
                        com.Connection = con1;
                        com.Transaction = trans;
                        com.CommandText = sql;
                        com.Parameters.AddRange(paras);
                        result2 = com.ExecuteNonQuery();

                    }
                    trans.Commit();
                }

                catch (System.Data.SqlClient.SqlException E)
                {
                    trans.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    con1.Close();
                }
            }
            return result2 > 0 ? "OK" : "ERROR";
      }

4.与数据库对应的实体类

public class QcAqFile : BaseVO
    {
        public QcAqFile(){}

        public QcAqFile(DataRow row) //帶參構造方法,作用是:填充數據
        {
            SID = getInteger(row,"SID");
            AQ_SID = getInteger(row, "AQ_SID");
            AQ_SEQ = getString(row, "AQ_SEQ");
            AQ_FILE_NAME = getString(row, "AQ_FILE_NAME");
            AQ_FILE_BINARY = getByte(row, "AQ_FILE_BINARY");
            ENABLE_FLAG = getString(row, "ENABLE_FLAG");
            CREATE_USER = getString(row, "CREATE_USER");
            UPDATE_USER = getString(row, "UPDATE_USER");
            CREATE_DATE = getDate(row,"CREATE_DATE");
            UPDATE_DATE = getDate(row, "UPDATE_DATE");
        }

        public int SID { get; set; }
        public int AQ_SID { get; set; }
        public string AQ_SEQ { get; set; }
        public string AQ_FILE_NAME { get; set; }
        public byte[] AQ_FILE_BINARY { get; set; }
        public string ENABLE_FLAG { get; set; }
        public string CREATE_USER { get; set; }
        public DateTime? CREATE_DATE { get; set; }
        public string UPDATE_USER { get; set; }
        public DateTime? UPDATE_DATE { get; set; }

    }

二.文件从DB下载并在前台页面打开查看

1.aspx页面

<asp:GridView ID="AqFileGridView" runat="server" AutoGenerateColumns="False" AllowPaging="True"
            DataKeyNames="SID" HorizontalAlign="Center" Width="100%" 
            onrowcommand="AqFile_RowCommand"  >
            <Columns>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="SID" HeaderText="編號"></asp:BoundField>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="AQ_SEQ" HeaderText="文件順序"></asp:BoundField>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="AQ_FILE_NAME" HeaderText="文件名稱"></asp:BoundField>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="ENABLE_FLAG" HeaderText="啟用狀態"></asp:BoundField>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="CREATE_USER" HeaderText="上傳人員"></asp:BoundField>
                <asp:BoundField HeaderStyle-Width="90px" ItemStyle-HorizontalAlign="Center" DataField="CREATE_DATE" HeaderText="上傳日期"> </asp:BoundField>
   
                <asp:TemplateField HeaderText="操 作" HeaderStyle-HorizontalAlign="NotSet" HeaderStyle-VerticalAlign="NotSet"
                    ItemStyle-HorizontalAlign="Center" HeaderStyle-Width="80px" ItemStyle-VerticalAlign="Middle">
                    <ItemTemplate>
                        <asp:Button ID="FileEditButton" runat="server" Text="查看文件" CommandName="FileEditButton" CommandArgument='<%# Eval("SID") %>' />
                        <asp:Button ID="FileDeleteButton" runat="server" Text="刪除文件" CommandName="FileDeleteButton" OnClientClick ="return confirm('確定要結案嗎？')" CommandArgument='<%# Eval("SID")%>' />
                    </ItemTemplate>
                    <HeaderStyle Width="80px"></HeaderStyle>
                    <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle"></ItemStyle>
                </asp:TemplateField>
            </Columns>
            <EmptyDataTemplate>
                <div align="center">
                    沒有資料</div>
            </EmptyDataTemplate>
            <HeaderStyle BackColor="#E0ECFF" Font-Bold="True" />
            <PagerSettings Mode="NumericFirstLast" />
            <PagerStyle BackColor="#0000CC" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="White" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F7F7F7" />
            <SortedAscendingHeaderStyle BackColor="#487575" />
            <SortedDescendingCellStyle BackColor="#E5E5E5" />
            <SortedDescendingHeaderStyle BackColor="#275353" />
        </asp:GridView>

2.控制层

/// <summary>
        /// 附件文件編輯 GirdView內點擊發生事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void AqFile_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            //如果點擊了 查看文件
            if ("FileEditButton".Equals(e.CommandName))
            {
                string sid = e.CommandArgument.ToString();
                QcAqFile qcAqFiles = _MES_DAO.getQcAqFileBySID(sid);
                //1.得到二進制文件
                Byte[] file1 = qcAqFiles.AQ_FILE_BINARY;
                //2.得到文件名稱
                string fileName = qcAqFiles.AQ_FILE_NAME;

                //下載後的文件放置路徑
                string serverPath = Server.MapPath("uploadFile\\downLoad\\");
                //如果不存在就創建
                if (!System.IO.Directory.Exists(serverPath))
                {
                    System.IO.Directory.CreateDirectory(serverPath);
                }

                //System.IO.Stream fs = null;
          BinaryWriter bw = null;
          try
          {

		    //Response的方式,適用於程式是發布到服務器,然後把文件下載到本地
             //Response.ContentType = "Application/plain";
             //Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
             //this.Response.Clear();
             //fs = this.Response.OutputStream;
             //fs.Write(file1, 0, file1.Length);
             //fs.Close();      
             //this.Response.End();
                    bw = new BinaryWriter(File.Open(serverPath + fileName, FileMode.OpenOrCreate));
                    bw.Write(file1);
                }
                catch (Exception ex)
                {
                    Response.Write("<sript>alsert('寫出文件錯誤!')</sript>" + ex.Message);
                    log.Error("寫出文件錯誤!!!===>" + ex.Message);
                    throw;
                }
                finally
                {
                    bw.Close();
                }

		//這種方式是打開指定位置的文件 ==>適用於本地打開(如果將程式發布到服務器就不行了,服務器的方式請使用Response)
                System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
                //info.WorkingDirectory = Application.StartupPath;
                info.FileName = serverPath + fileName;
                info.Arguments = "";
                try
                {
                    System.Diagnostics.Process.Start(info);
                }
                catch (System.ComponentModel.Win32Exception we)
                {
                    Response.Write("<sript>alsert('打開失敗!')</sript>" + we.Message);
                    log.Error("打開失敗!!!===>" + we.Message);
                    return;
                }
                jsSelectTab = "$('#MH').tabs('select', 2);";

            }

            //如果點擊了 刪除文件
            if ("FileDeleteButton".Equals(e.CommandName))
            {
                string sid = e.CommandArgument.ToString();
                string msg = _MES_DAO.deleteAqFileBySID(sid);
                if ("OK".Equals(msg))
                {
                    List<QcAqFile> qcAqFile = _MES_DAO.getQcAqFileByAQ_SID(this.FileAQ_SID.Text);
                    AqFileGridView.DataSource = qcAqFile;
                    AqFileGridView.DataBind();
                    Response.Write("<script>alert('刪除成功!!!')</script>");
                }
                else
                {
                    Response.Write("<script>alert('刪除失敗!!!')</script>");
                }
                //重新查詢資料

                jsSelectTab = "$('#MH').tabs('select', 2);";

            }
        }

3.Dao 层

  /// <summary>
        /// 依照提供的主鍵SID值 ,查詢出QC_AQ_FILE內對應的記錄
        /// </summary>
        /// <param name="SID"></param>
        /// <returns></returns>
        public QcAqFile getQcAqFileBySID(string SID)
        {
            List<SqlParameter> args = new List<SqlParameter>();
            string sql = @"SELECT * FROM [dbo].[QC_AQ_FILE] WHERE [SID] = @SID";
            this.setSqlParameter(args, "@SID", Convert.ToInt32(SID));
            DataTable dt = this.executeQuery(sql, "edison", args);
            
            return new QcAqFile(dt.Rows[0]);
        }

如有问题,欢迎提出交流!!!