P.Dragon的开发历程

//Code for fun!Code for yourself!
  博客园  :: 首页  :: 新随笔  :: 订阅 订阅  :: 管理

asp.net关于文件在数据库的存入和读取...

Posted on 2006-07-21 00:05  P.Dragon  阅读(2456)  评论(0)    收藏  举报

最近有个涉及到邮件的活，需要把文件上传到数据库，然后再把文件从数据库里面读出来，进行下载。

找了一些资料之后运用到项目当中。
数据库的结构如下：

USE [EOffice]
GO
/****** 对象:  Table [dbo].[Group_Files]    脚本日期: 07/20/2006 23:57:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Group_Files](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Group_Files_FileName]  DEFAULT (''),
    [FileBody] [image] NULL,
    [FileType] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Group_Files_FileType]  DEFAULT (''),
 CONSTRAINT [PK_Group_Files] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


然后我写了一个存储过程，代码如下：

USE [EOffice]
GO
/****** 对象:  StoredProcedure [dbo].[SendTo_Group_Email]    脚本日期: 07/20/2006 23:59:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






Create PROCEDURE [dbo].[SendTo_Group_Email]
(
@Title varchar(200),
@Content varchar(MAX),
@Area varchar(max),
@Sender varchar(50),
@Dept varchar(50),
@SendToUser varchar(50),
@FileName varchar(200),
@FileBody image,
@FileType varchar(4)
)
AS

INSERT Group_Email (Title,[Content],Area,SendToUser,Dept,Sender,FileName,FileBody,FileType) VALUES (@Title,@Content,@Area,@SendToUser,@Dept,@Sender,@FileName,@FileBody,@FileType)




程序代码如下：
SendEmail.aspx

  1<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SendEmail.aspx.cs" Inherits="GroupWork_SendEmail" %>
  2
  3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  4
  5<html xmlns="http://www.w3.org/1999/xhtml" >
  6
  7<head runat="server">
  8    <title>发送邮件</title>
  9<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css">
 10body,td,th {
 11    font-size: 9pt;
 12}
 13body {
 14    background-image: url();
 15    background-color: #F2F7FB;
 16    margin-left: 10px;
 17    margin-top: 5px;
 18    margin-right: 10px;
 19    margin-bottom: 5px;
 20}
 21-->
 22</style></head>
 23<script language="vbscript">
 24function Select_Local_User(url)
 25     dim k
 26    k=showModalDialog(url,"","dialogWidth:485px;status:no;dialogHeight:280px")
 27    if ubound(split(k,"$#@&!"))>=0 then
 28        document.form1.HiddenField1.value = split(k,"$#@&!")(0)
 29        document.form1.txtLocalUser.value = split(k,"$#@&!")(1)
 30    end if
 31end function
 32
 33function Select_Remote_User(url)
 34     dim k
 35    k=showModalDialog(url,"","dialogWidth:485px;status:no;dialogHeight:310px")
 36    if ubound(split(k,"$#@&!"))>=0 then
 37        document.form1.HiddenField2.value = split(k,"$#@&!")(0)
 38        document.form1.txtRemoteUser.value=split(k,"$#@&!")(1)
 39    end if
 40end function
 41
 42</script>
 43<body>
 44    <form id="form1" method="post" enctype="multipart/form-data" runat="server">
 45    <div style="text-align: center">      
 46      <table border="0" cellpadding="0" cellspacing="0" width="100%">
 47        <tr>
 48          <td width="1%"><img src="../Images/spacer.gif" width="11" height="1" border="0" alt="" /></td>
 49          <td colspan="2"><img src="../Images/spacer.gif" width="209" height="1" border="0" alt="" /></td>
 50          <td width="1%"><img src="../Images/spacer.gif" width="12" height="1" border="0" alt="" /></td>
 51          <td width="2%"><img src="../Images/spacer.gif" width="1" height="1" border="0" alt="" /></td>
 52        </tr>
 53        <tr>
 54          <td style="height: 35px"><img src="../Images/ye_r1_c1.gif" alt="" name="ye_r1_c1" width="11" height="30" border="0" id="ye_r1_c1" /></td>
 55          <td colspan="2" align="left" background="../images/ye_r1_c2.gif" style="height: 35px"><table width="119" border="0" cellspacing="0" cellpadding="0">
 56            <tr>
 57              <td width="40" height="20">&nbsp;</td>
 58              <td width="79" valign="top"><strong>收 文 登 记</strong></td>
 59            </tr>
 60          </table></td>
 61          <td style="height: 35px"><img src="../Images/ye_r1_c3.gif" alt="" name="ye_r1_c3" width="12" height="30" border="0" id="ye_r1_c3" /></td>
 62          <td style="height: 35px"></td>
 63        </tr>
 64        <tr>
 65          <td background="../images/ye_r2_c1.gif">&nbsp;</td>
 66          <td width="13%">&nbsp;</td>
 67          <td width="83%" align="left"><img src="../Images/dj.gif" width="300" height="30" /></td>
 68          <td background="../images/ye_r2_c3.gif">&nbsp;</td>
 69          <td>&nbsp;</td>
 70        </tr>
 71        <tr>
 72          <td background="../images/ye_r2_c1.gif">&nbsp;</td>
 73          <td colspan="2"><div align="center">
 74            <table border="0" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC" style="width: 566px">
 75              <tr bgcolor="#b9d5f4">
 76                <td style="width: 186px; height: 20px">
 77                  <div align="center" class="style2"> 文件标题</div></td>
 78                <td colspan="2" align="left" style="height: 20px; width: 433px;">
 79                    &nbsp;<asp:TextBox ID="txtTitle" runat="server" class="Input_TextBox" Width="277px"></asp:TextBox>
 80                  <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtTitle"
 81                        Display="Dynamic" ErrorMessage="文件标题不能为空。"></asp:RequiredFieldValidator></td>
 82              </tr>
 83                <tr bgcolor="#F1F5FC">
 84                    <td style="width: 186px; height: 20px">
 85                        本地用户</td>
 86                    <td align="left" colspan="2" style="width: 433px; height: 20px">
 87                        <asp:TextBox ID="txtLocalUser" runat="server" Width="279px"></asp:TextBox><input
 88                            id="SelectLocal" class="Input_Button" name="SelectLocal" onClick="vbscript:Select_Local_User('..\SelectMulti.aspx')" type="button"
 89                            value="选择" /><asp:HiddenField ID="HiddenField1" runat="server" />
 90                    </td>
 91                </tr>
 92                <tr bgcolor="#b9d5f4">
 93                    <td style="width: 186px; height: 20px">
 94                        异地用户</td>
 95                    <td align="left" colspan="2" style="width: 433px; height: 20px">
 96                        <asp:TextBox ID="txtRemoteUser" runat="server" Width="279px"></asp:TextBox>
 97                        <input id="SelectRemote" class="Input_Button" name="SelectRemote" onClick="vbscript:Select_Remote_User('..\SelectRemoteUser.aspx')" 
 98                            type="button" value="选择" />
 99                        <asp:HiddenField ID="HiddenField2" runat="server" />
100                    </td>
101                </tr>
102              <tr bgcolor="#F1F5FC">
103                <td rowspan="2" align="center" style="width: 186px; height: 11px;"> 文件上传</td>
104                <td colspan="2" rowspan="2" align="left" valign="top" bgcolor="#F1F5FC" style="width: 433px; height: 11px;"> 
105                    <input id="File1" runat="server" style="width: 381px" type="file" /><br />
106                    <input id="File2" runat="server" style="width: 379px" type="file" />
107                    <input id="File3" runat="server" style="width: 379px" type="file" />
108                    <input id="File4" runat="server" style="width: 379px" type="file" />
109                    <input id="File5" runat="server" style="width: 377px" type="file" /></td>
110              </tr>
111              <tr>
112              </tr>
113              <tr bgcolor="#B9D5F4">
114                <td style="width: 186px; height: 22px;">
115                  <div align="center" class="style2"> 邮件内容</div></td>
116                <td colspan="2" align="left" style="width: 433px; height: 22px;">
117                    &nbsp;<asp:TextBox ID="txtContent" runat="server" Height="57px" TextMode="MultiLine" Width="296px" class="Input_TextBox"></asp:TextBox>
118                  <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="txtContent"
119                        Display="Dynamic" ErrorMessage="邮件内容不能为空。"></asp:RequiredFieldValidator></td>
120              </tr>
121              <tr bgcolor="#F1F5FC">
122                <td style="width: 186px; height: 6px">
123                  </td>
124                <td colspan="2" align="left" style="height: 6px; width: 433px;">
125                    &nbsp;<asp:CheckBox ID="chkSms" runat="server" Text="短信通知" />            
126                  <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="提交"  class="Input_Button" />            
127                  <input type="reset" name="Submit" value="重置"  class="Input_Button" /></td>
128              </tr>
129            </table>
130          </div></td>
131          <td background="../images/ye_r2_c3.gif">&nbsp;</td>
132          <td>&nbsp;</td>
133        </tr>
134        <tr>
135          <td><img src="../Images/ye_r3_c1.gif" alt="" name="ye_r3_c1" width="11" height="22" border="0" id="ye_r3_c1" /></td>
136          <td colspan="2" background="../images/ye_r3_c2.gif">&nbsp;</td>
137          <td><img src="../Images/ye_r3_c3.gif" alt="" name="ye_r3_c3" width="12" height="22" border="0" id="ye_r3_c3" /></td>
138          <td>&nbsp;</td>
139        </tr>
140      </table>
141    </div>
142    </form>
143</body>
144</html>
145


SendEmail.aspx.cs

  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Collections;
  5using System.Web;
  6using System.Web.Security;
  7using System.Web.UI;
  8using System.Web.UI.WebControls;
  9using System.Web.UI.WebControls.WebParts;
 10using System.Web.UI.HtmlControls;
 11
 12using System.Data;
 13using System.Data.SqlClient;
 14using EOffice.Model;
 15using EOffice.SqlServer;
 16using System.IO;
 17using System.Configuration;
 18
 19//using iWebSMS2000;
 20[Serializable]
 21public partial class GroupWork_SendEmail : System.Web.UI.Page
 22{
 23    public SqlConnection conn;
 24    //public iSMS2000 SMS;
 25    //public DBstep.SMSClient2000 ObjiSMSClient2000;
 26    protected void Page_Load(object sender, EventArgs e)
 27    {
 28        conn = new SqlConnection("SERVER=SERVER;UID=sa;PWD=8860;database=EOFFICE_SERVER;");
 29       
 30    }
 31    protected void btnSubmit_Click(object sender, EventArgs e)
 32    {
 33        String strTitle = txtTitle.Text;
 34        String strContent = txtContent.Text;
 35        UserInfo info = (UserInfo)Session["EOfficeUserInfo"];
 36        String strUserName = info.UserName;
 37        String strTmpDept = info.DeptId.ToString();
 38
 39        String strUnitName = ConfigurationSettings.AppSettings["UnitName"];
 40        String strServerFileIndex = "";   //服务器端文件附件索引
 41        String strClientFileIndex = "";    //客户端文件附件索引
 42
 43        String strRemote = HiddenField2.Value;
 44        String[] strRemoteUser = strRemote.Split(',');
 45        String strTmpUserName;
 46        String strTmpServer;
 47        Boolean bDone = false;
 48
 49        ////////上传多附件代码/////////////
 50        //得到File表单元素
 51        HttpFileCollection files = HttpContext.Current.Request.Files;
 52        HttpPostedFile postedFile;
 53
 54        foreach (string strUser in strRemoteUser)
 55        {
 56            String[] strSplit = strUser.Split('/');
 57            strTmpUserName = strSplit[0];
 58            strTmpServer = strSplit[1];
 59            String strConnectString = "";
 60            String strAreaName = "";
 61            int nAreaID = 0;
 62            String strDept = "";
 63            strClientFileIndex = "";
 64            SqlConnection connClient;
 65
 66            String strSQL = "Select * From Area Where AreaCode='" + strTmpServer + "'";
 67            SqlCommand cmd = new SqlCommand(strSQL, conn);
 68            cmd.Connection.Open();
 69            using (SqlDataReader sdr = cmd.ExecuteReader())
 70            {
 71                if (sdr.Read())
 72                {
 73                    strConnectString = sdr["ConnectString"].ToString();
 74                    strAreaName = sdr["AreaName"].ToString();
 75                    nAreaID = Convert.ToInt16(sdr["ID"]);
 76                }
 77            }
 78            cmd.Connection.Close();
 79
 80            strSQL = "Select * From MemberList Where UserName='" + strTmpUserName + "' And AreaID=" + nAreaID;
 81            cmd = new SqlCommand(strSQL, conn);
 82            cmd.Connection.Open();
 83            using (SqlDataReader sdr = cmd.ExecuteReader())
 84            {
 85                if (sdr.Read())
 86                {
 87                    strDept = sdr["Dept"].ToString();
 88                }
 89            }
 90            cmd.Connection.Close();
 91
 92            connClient = new SqlConnection(strConnectString);
 93
 94            for (int intCount = 0; intCount < files.Count; intCount++)
 95            {
 96                postedFile = files[intCount];
 97
 98                if (postedFile.ContentLength > 0)
 99                {
100                    String strOldFilePath = postedFile.FileName;
101                    String strFileName = strOldFilePath.Substring(strOldFilePath.LastIndexOf("\\") + 1);
102
103                    //上传文件到服务器
104                    //File1.PostedFile.SaveAs("c:\\Test\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + strExtension);
105
106                    //用于保存文件大小 
107                    int intDocLen;
108                    //Stream用于读取上传数据 
109                    Stream objStream;
110                    String strDocExt;
111                    //上传文件具体内容 
112                    intDocLen = postedFile.ContentLength;
113                    strDocExt = strOldFilePath.Substring(strOldFilePath.LastIndexOf(".") + 1);
114
115                   byte[] Docbuffer = new byte[intDocLen];
116                    objStream = postedFile.InputStream;
117
118
119                    //文件保存到缓存 
120
121                    //缓存将保存到数据库 
122                    objStream.Read(Docbuffer, 0, intDocLen);
123
124                    string fileType = postedFile.ContentType;
125
126
127                    //执行服务器端存储过程Send_Group_Email
128                    if (!bDone)
129                    {
130                        cmd = new SqlCommand("Send_Group_Email", conn);
131                        cmd.CommandType = CommandType.StoredProcedure;
132                        cmd.Parameters.Add("@FileName ", SqlDbType.VarChar, 200);
133                        cmd.Parameters.Add("@FileBody", SqlDbType.Image);
134                        cmd.Parameters.Add("@FileType", SqlDbType.VarChar, 4);
135                        cmd.Parameters.Add("@File_Index", SqlDbType.Int);
136
137                        cmd.Parameters[0].Value = strFileName;
138                        cmd.Parameters[1].Value = Docbuffer;
139                        //cmd.Parameters[2].Value = strDocExt;]
140                        cmd.Parameters[2].Value = fileType;
141
142                        //cmd.Parameters.Add(new SqlParameter("@File_Index", SqlDbType.Int));
143                        cmd.Parameters[3].Direction = ParameterDirection.ReturnValue;
144                        cmd.Connection.Open();
145                        cmd.ExecuteNonQuery();
146                        strServerFileIndex += cmd.Parameters[3].Value.ToString() + ",";
147                        cmd.Connection.Close();
148                    }
149
150                    //执行客户端存储过程Get_Upload_File
151                    cmd = new SqlCommand("Get_Upload_File", connClient);
152                    cmd.CommandType = CommandType.StoredProcedure;
153                    cmd.Parameters.Add("@FileName ", SqlDbType.VarChar, 200);
154                    cmd.Parameters.Add("@FileBody", SqlDbType.Image);
155                    cmd.Parameters.Add("@FileType", SqlDbType.VarChar, 4);
156                    cmd.Parameters.Add("@File_Index", SqlDbType.Int);
157
158                    cmd.Parameters[0].Value = strFileName;
159                    cmd.Parameters[1].Value = Docbuffer;
160                    //cmd.Parameters[2].Value = strDocExt;
161                    cmd.Parameters[2].Value = fileType;
162
163                    //cmd.Parameters.Add(new SqlParameter("@File_Index", SqlDbType.Int));
164                    cmd.Parameters[3].Direction = ParameterDirection.ReturnValue;
165                    cmd.Connection.Open();
166                    cmd.ExecuteNonQuery();
167                    strClientFileIndex += cmd.Parameters[3].Value.ToString() + ",";
168                    cmd.Connection.Close();
169                }
170
171                if (intCount.Equals(files.Count - 1))
172                {
173                    bDone = true;
174                }
175            }
176
177            strClientFileIndex = strClientFileIndex.Remove(strClientFileIndex.Length - 1);
178
179            strSQL = "Insert Into Group_Email (Title,Content,Area,SendToUser,Dept,Sender,Files_Index)";
180            strSQL += " values (";
181            strSQL += "'" + strTitle + "',";
182            strSQL += "'" + strContent + "',";
183            strSQL += "'" + strUnitName + "',";
184            strSQL += "'" + strTmpUserName + "',";
185            strSQL += "'" + strDept + "',";
186            strSQL += "'" + strUserName + "',";
187            strSQL += "'" + strClientFileIndex + "')";
188            cmd = new SqlCommand(strSQL, connClient);
189            cmd.Connection.Open();
190            cmd.ExecuteNonQuery();
191            cmd.Connection.Close();
192
193            String strTmpServerFileIndex = strServerFileIndex.Remove(strServerFileIndex.Length - 1);
194            
195            strSQL = "Insert Into Group_Email_Sever (Title,Content,Area,SendToUser,Dept,Sender,Files_Index)";
196            strSQL += " values (";
197            strSQL += "'" + strTitle + "',";
198            strSQL += "'" + strContent + "',";
199            strSQL += "'" + strAreaName + "',";
200            strSQL += "'" + strTmpUserName + "',";
201            strSQL += "'" + strTmpDept + "',";
202            strSQL += "'" + strUserName + "',";
203            strSQL += "'" + strTmpServerFileIndex + "')";
204            cmd = new SqlCommand(strSQL, conn);
205            cmd.Connection.Open();
206            cmd.ExecuteNonQuery();
207            cmd.Connection.Close();
208        }
209        ////////结束上传多附件/////////////
210        Response.Redirect("../SuccessMsg.aspx");
211    }
212}
213


下载文件的代码：
DownFile.aspx

DownFile.aspx.cs

 1using System;
 2using System.Data;
 3using System.Configuration;
 4using System.Collections;
 5using System.Web;
 6using System.Web.Security;
 7using System.Web.UI;
 8using System.Web.UI.WebControls;
 9using System.Web.UI.WebControls.WebParts;
10using System.Web.UI.HtmlControls;
11
12using System.Data;
13using System.Data.SqlClient;
14using EOffice.Model;
15using EOffice.SqlServer;
16using System.IO;
17
18[Serializable]
19public partial class GroupWork_DownFile : System.Web.UI.Page
20{
21    public SqlConnection conn;
22    public String strFileID;
23    public UserInfo info;
24    protected void Page_Load(object sender, EventArgs e)
25    {
26        strFileID = Request.QueryString["ID"];
27        info = (UserInfo)Session["EOfficeUserInfo"];
28
29        if (strFileID == null)
30        {
31            Response.Redirect("../ErrorMsg.aspx");
32        }
33
34        DbLink db = new DbLink();
35        conn = db.Connect();
36
37        String strSQL = "Select * From Group_Files Where ID=" + strFileID; //+ " And SendToUser = '" + info.UserName + "'";
38        SqlCommand cmd = new SqlCommand(strSQL, conn);
39        cmd.Connection.Open();
40        using (SqlDataReader sdr = cmd.ExecuteReader())
41        {
42            if (!sdr.Read())
43            {
44                Response.Redirect("../ErrorMsg.aspx");
45            }
46            else
47            {
48                Response.Clear();
49                String strFileName = sdr["FileName"].ToString();
50                Response.ContentType   = "APPLICATION/OCTET-STREAM";
51                Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
52                Response.Buffer = true;
53                Response.BinaryWrite((byte[])sdr["FileBody"]);
54
55                //Response.Clear();
56                //Response.ContentType = "application/octet-stream";
57                ////Response.AddHeader("Content-Type", sdr["FileType"].ToString());
58                //Response.BinaryWrite((byte[])sdr["FileBody"]);  
59            }
60        }
61        //conn.Close();
62
63        /*
64        SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
65        SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
66        DataSet ds = new DataSet("MyImages");
67        byte[] MyData = new byte[0];
68        da.Fill(ds, "MyImages");
69        DataRow myRow = ds.Tables["MyImages"].Rows[0];
70        String strFileName = Convert.ToString(myRow["FileName"]);
71        MyData = (byte[])myRow["FileBody"];
72        int ArraySize = new int();
73        ArraySize = MyData.GetUpperBound(0);
74        FileStream fs = new FileStream(@"C:\\Download\" + strFileName,
75        FileMode.OpenOrCreate, FileAccess.Write);
76        fs.Write(MyData, 0, ArraySize);
77        fs.Close();
78         */
79        //Response.Write("下载文件" + strFileName + "成功!");
80    }
81}
82