最近有個涉及到郵件的活，需要把文件上傳到數據庫，然後再把文件從數據庫裡面讀出來，進行下載。
找了一些資料之後運用到項目當中。
數據庫的結構如下：
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,歡迎光臨學網,收藏本篇文章 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14]
$False$
@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> <br> 44 <form id="form1" method="post" enctype="multipart/form-data" runat="server">
45 <div >
46 <table border="0" cellpadding="0" cellspacing="0" width="100%">
47 <tr>
48 <td width="1%"><img src="/School/UploadFiles_7810/201105/20110501075007694.gif" width="11" height="1" border="0" alt="" /></td>
49 <td colspan="2"><img src="/School/UploadFiles_7810/201105/20110501075007694.gif" width="209" height="1" border="0" alt="" /></td>
50 <td width="1%"><img src="/School/UploadFiles_7810/201105/20110501075007694.gif" width="12" height="1" border="0" alt="" /></td>
51 <td width="2%"><img src="/School/UploadFiles_7810/201105/20110501075007694.gif" width="1" height="1" border="0" alt="" /></td>
52 </tr>
53 <tr>
54 <td ><img src="/School/UploadFiles_7810/201105/20110501075007393.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" ><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 ><img src="/School/UploadFiles_7810/201105/20110501075007982.gif" alt="" name="ye_r1_c3" width="12" height="30" border="0" id="ye_r1_c3" /></td>
62 <td ></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="/School/UploadFiles_7810/201105/20110501075007214.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" >
75 <tr bgcolor="#b9d5f4">
76 <td >
77 <div align="center" class="style2"> 文件標題</div></td>
78 <td colspan="2" align="left" >
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 >
85 本地用戶</td>
86 <td align="left" colspan="2" >
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 >
94 異地用戶</td>
95 <td align="left" colspan="2" >
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" > 文件上傳</td>
104 <td colspan="2" rowspan="2" align="left" valign="top" bgcolor="#F1F5FC" >
105 <input id="File1" runat="server" type="file" /><br />
106 <input id="File2" runat="server" type="file" />
107 <input id="File3" runat="server" type="file" />
108 <input id="File4" runat="server" type="file" />
109 <input id="File5" runat="server" type="file" /></td>
110 </tr>
111 <tr>
112 </tr>
113 <tr bgcolor="#B9D5F4">
114 <td >
115 <div align="center" class="style2"> 郵件內容</div></td>
116 <td colspan="2" align="left" >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 >
123 </td>
124 <td colspan="2" align="left" >
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="/School/UploadFiles_7810/201105/20110501075007837.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="/School/UploadFiles_7810/201105/20110501075007565.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}
