asp.net關於文件在資料庫的存入與讀取

作者：Eve Cole 更新時間：2009-06-30 17:01: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
3http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
4
5http://www.w3.org/1999/xhtml " >
6
7
8 發送郵件
9
23
43
44

46
47
48
49
50
51
52
53
54
55
61
62
63
64
65
66
67
68
69
70
71
72
73
131
132
133
134
135
137
139
140

56
57
58
59
60

收文登記

74
75
76
78
82
83
84
86
91
92
93
95
101
102
103
104
110
111
112
113
114
116
120
121
122
124
128
129

77 檔案標題	79 80 81 Display="Dynamic" ErrorMessage="檔案標題不能為空。">
85 本地用戶	87 88 id="SelectLocal" class="Input_Button" name="SelectLocal" onClick="vbscript:Select_Local_User('..SelectMulti.aspx')" type="button" 89 value="選擇" /> 90
94 異地用戶	96 97 98 type="button" value="選擇" /> 99 100
檔案上傳	105 106 107 108 109

115 郵件內容	117 118 119 Display="Dynamic" ErrorMessage="郵件內容不能為空。">
123	125 126 127

130

< /td>
136

< /td>
138

141

142

143
144
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

http://topwin.cnblogs.com/archive/2006/07/21/456087.html