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


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

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

  数据库的结构如下:

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="../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">&nbsp;</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">&nbsp;</td>
66     <td width="13%">&nbsp;</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">&nbsp;</td>
69     <td>&nbsp;</td>
70    </tr>
71    <tr>
72     <td background="../images/ye_r2_c1.gif">&nbsp;</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          &nbsp;<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          &nbsp;<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          &nbsp;<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">&nbsp;</td>
132     <td>&nbsp;</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">&nbsp;</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>&nbsp;</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}
82

本文作者:
« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3