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

2009-12-13 12:52| 发布者: admin| 查看: 34| 评论: 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
4
5
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
136
137
138
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

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

收藏分享邀请

		自动登录	找回密码
密码			注册

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

最新评论

相关分类