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

joe · 发表于 2007-2-10 19:58

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

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

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\'>$#@&!\"))>=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\'>$#@&!\"))>=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\"></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\"></td>
66       <td width=\"13%\"></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\"></td>
69       <td></td>
70       </tr>
71       <tr>
72       <td background=\"../images/ye_r2_c1.gif\"></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                   <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                   <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                   <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\"></td>
132       <td></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\"></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></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

WD=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

帐号		自动登录	找回密码
密码			成为会员