最近有个涉及到邮件的活，需要把文件上传到数据库，然后再把文件从数据库里面读出来，进行下载。
找了一些资料之后运用到项目当中。
数据库的结构如下：
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="http://www.knowsky.com/images/spacer.gif" width="11" height="1" border="0" alt="" /></td>
49 <td colspan="2"><img src="http://www.knowsky.com/images/spacer.gif" width="209" height="1" border="0" alt="" /></td>
50 <td width="1%"><img src="http://www.knowsky.com/images/spacer.gif" width="12" height="1" border="0" alt="" /></td>
51 <td width="2%"><img src="http://www.knowsky.com/images/spacer.gif" width="1" height="1" border="0" alt="" /></td>
52 </tr>
53 <tr>
54 <td style="height: 35px"><img src="http://www.knowsky.com/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="http://www.knowsky.com/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="http://www.knowsky.com/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="http://www.knowsky.com/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="http://www.knowsky.com/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;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}
