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

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

找了一些资料之后运用到项目当中。

数据库的结构如下：

USE [EOffice]

/**//****** 对象: Table [dbo].[Group_Files] 脚本日期: 07/20/2006 23:57:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

然后我写了一个存储过程，代码如下：

USE [EOffice]

/**//****** 对象: StoredProcedure [dbo].[SendTo_Group_Email] 脚本日期: 07/20/2006 23:59:21 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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)

)

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" %>

3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

5<html xmlns="http://www.w3.org/1999/xhtml" >

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

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

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;

12using System.Data;

13using System.Data.SqlClient;

14using EOffice.Model;

15using EOffice.SqlServer;

16using System.IO;

17using System.Configuration;

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;");

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();

39 String strUnitName = ConfigurationSettings.AppSettings["UnitName"];

40 String strServerFileIndex = ""; //服务器端文件附件索引

41 String strClientFileIndex = ""; //客户端文件附件索引

43 String strRemote = HiddenField2.Value;

44 String[] strRemoteUser = strRemote.Split(',');

45 String strTmpUserName;

46 String strTmpServer;

47 Boolean bDone = false;

49 /**/////////上传多附件代码/////////////

50 //得到File表单元素

51 HttpFileCollection files = HttpContext.Current.Request.Files;

52 HttpPostedFile postedFile;

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;

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();

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();

92 connClient = new SqlConnection(strConnectString);

94 for (int intCount = 0; intCount < files.Count; intCount++)

95 {

96 postedFile = files[intCount];

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;

12using System.Data;

13using System.Data.SqlClient;

14using EOffice.Model;

15using EOffice.SqlServer;

16using System.IO;

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"];

29 if (strFileID == null)

30 {

31 Response.Redirect("../ErrorMsg.aspx");

32 }

34 DbLink db = new DbLink();

35 conn = db.Connect();

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"]);

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();

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}

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