Baru-baru ini, ada pekerjaan yang melibatkan email, yang mengharuskan mengunggah file ke database, lalu membaca file dari database dan mendownloadnya.
Saya menemukan beberapa informasi dan menerapkannya pada proyek.
Struktur databasenya adalah sebagai berikut:
GUNAKAN[EOffice]
PERGI
/**//****** Objek: Tabel [dbo].[Group_Files] Tanggal skrip: 20/07/2006 23:57:34 ******/
SETEL ANSI_NULLS AKTIF
PERGI
SETEL QUOTED_IDENTIFIER AKTIF
PERGI
SETEL ANSI_PADDING AKTIF
PERGI
BUAT TABEL [dbo].[Group_Files](
[ID] [int] IDENTITAS(1,1) BUKAN NULL,
[NamaFile] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Group_Files_FileName] DEFAULT(''),
[Badan File] [gambar] NULL,
[Jenis File] [varchar](50) Susun Chinese_PRC_CI_AS NULL CONSTRAINT [DF_Group_Files_FileType] DEFAULT (''),
CONSTRAINT [PK_Group_Files] KUNCI UTAMA TERKUMPULAN
(
[ID]ASC
)DENGAN (IGNORE_DUP_KEY = OFF) PADA [UTAMA]
) DI [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
MATIKAN ANSI_PADDING
Lalu saya menulis prosedur tersimpan dengan kode berikut:
GUNAKAN[EOffice]
PERGI
/**//****** Objek: StoredProcedure [dbo].[SendTo_Group_Email] Tanggal skrip: 20/07/2006 23:59:21 ******/
SETEL ANSI_NULLS AKTIF
PERGI
SETEL QUOTED_IDENTIFIER AKTIF
PERGI
Buat PROSEDUR [dbo].[SendTo_Group_Email]
(
@Judul varchar(200),
@Konten varchar(MAX),
@Area varchar(maks),
@Pengirim varchar(50),
@Departemen varchar(50),
@SendToUser varchar(50),
@NamaFile varchar(200),
@FileBody gambar,
@FileType varchar(4)
)
SEBAGAI
INSERT Group_Email (Judul,[Konten],Area,SendToUser,Dept,Sender,FileName,FileBody,FileType) NILAI (@Title,@Content,@Area,@SendToUser,@Dept,@Sender,@FileName,@FileBody, @Jenis File)
Kode programnya adalah sebagai berikut:
KirimEmail.aspx
1<%@ Halaman 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<kepala runat="server">
8 <title>Kirim email</title>
9<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css">
10tubuh,td,th {
11 ukuran font: 9pt;
12}
13tubuh {
14 gambar latar belakang: url();
15 warna latar belakang: #F2F7FB;
16 margin-kiri: 10px;
17 margin atas: 5 piksel;
18 margin-kanan: 10 piksel;
19 margin-bawah: 5 piksel;
20}
21-->
22</gaya></head>
23<bahasa skrip="vbscript">
24fungsi Select_Local_User(url)
25 redup k
26 k=showModalDialog(url,"","dialogWidth:485px;status:no;dialogHeight:280px")
27 jika ubound(split(k," $#@&!"))>=0 maka
28 dokumen.form1.HiddenField1.value = split(k," $#@&!")(0 )
29 document.form1.txtLocalUser.value = split(k," $#@&!")(1 )
30 berakhir jika
fungsi akhir
32
33fungsi Select_Remote_User(url)
34 redup k
35 k=showModalDialog(url,"","dialogWidth:485px;status:no;dialogHeight:310px")
36 jika ubound(split(k," $#@&!"))>=0 maka
37 dokumen.form1.HiddenField2.value = split(k," $#@&!")(0 )
38 document.form1.txtRemoteUser.value=split(k," $#@&!")(1 )
39 berakhir jika
fungsi 40 ujung
41
42</skrip>
43<tubuh>
44 <form id="form1" method="post" enctype="multipart/form-data" runat="server">
45 <div style="text-align: tengah">
46 <tabel 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" bantalan sel="0">
56 <tr>
57 <td lebar="40" tinggi="20"> </td>
58 <td width="79" valign="top"><strong>Daftar untuk menerima tanda terima</strong></td>
59</tr>
60 </tabel></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="tinggi: 35px"></td>
63</tr>
64 <tr>
65 <td background="../images/ye_r2_c1.gif"> </td>
66 <td lebar="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 <tabel border="0" cellpadding="0" Cellspacing="1" bgcolor="#CCCCCC" style="width: 566px">
75 <tr bgcolor="#b9d5f4">
76 <td style="lebar: 186 piksel; tinggi: 20 piksel">
77 <div align="center" class="style2"> Judul file</div></td>
78 <td colspan="2" align="left" style="tinggi: 20 piksel; lebar: 433 piksel;">
79<asp:TextBox ID="txtTitle" runat="server" class="Input_TextBox" Lebar="277px"></asp:TextBox>
80 <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtTitle"
81 Display="Dynamic" ErrorMessage="Judul file tidak boleh kosong."></asp:RequiredFieldValidator></td>
82</tr>
83 <tr bgcolor="#F1F5FC">
84 <td style="lebar: 186 piksel; tinggi: 20 piksel">
85 pengguna lokal</td>
86 <td align="left" colspan="2" style="width: 433px; tinggi: 20px">
87 <asp:TextBox ID="txtLocalUser" runat="server" Lebar="279px"></asp:TextBox><input
88 id="SelectLocal" class="Input_Button" name="SelectLocal" onClick="vbscript:Select_Local_User('..SelectMulti.aspx')" type="button"
89 value="pilih" /><asp:HiddenField ID="HiddenField1" runat="server" />
90 </td>
91</tr>
92 <tr bgcolor="#b9d5f4">
93 <td style="lebar: 186 piksel; tinggi: 20 piksel">
94 Pengguna jarak jauh</td>
95 <td align="left" colspan="2" style="width: 433px; tinggi: 20px">
96 <asp:TextBox ID="txtRemoteUser" runat="server" Lebar="279px"></asp:TextBox>
97 <input id="SelectRemote" class="Input_Button" name="SelectRemote" onClick="vbscript:Select_Remote_User('..SelectRemoteUser.aspx')"
98 ketik = "tombol" nilai = "pilih" />
99 <asp:HiddenField ID="HiddenField2" runat="server" />
100</td>
101 </tr>
102 <tr bgcolor="#F1F5FC">
103 <td rowpan="2" align="center" style="width: 186px; height: 11px;"> Unggah file</td>
104 <td colspan="2" rowpan="2" align="left" valign="top" bgcolor="#F1F5FC" style="width: 433px; height: 11px;">
105 <input id="File1" runat="server" style="lebar: 381px" type="file" /><br />
106 <input id="File2" runat="server" style="lebar: 379px" type="file" />
107 <input id="File3" runat="server" style="lebar: 379px" type="file" />
108 <input id="File4" runat="server" style="lebar: 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="lebar: 186 piksel; tinggi: 22 piksel;">
115 <div align="center" class="style2"> Konten email</div></td>
116 <td colspan="2" align="kiri" style="lebar: 433 piksel; tinggi: 22 piksel;">
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="Isi email tidak boleh kosong."></asp:RequiredFieldValidator></td>
120</tr>
121 <tr bgcolor="#F1F5FC">
122 <td style="lebar: 186 piksel; tinggi: 6 piksel">
123 </td>
124 <td colspan="2" align="left" style="tinggi: 6 piksel; lebar: 433 piksel;">
125 <asp:CheckBox ID="chkSms" runat="server" Text="Pemberitahuan SMS" />
126 <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Kirim" class="Input_Button" />
127 <input type="reset" name="Kirim" value="Reset" class="Input_Button" /></td>
128</tr>
129 </tabel>
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 </tabel>
141 </div>
142 </bentuk>
143</tubuh>
144</html>
145
KirimEmail.aspx.cs
1menggunakan Sistem;
2menggunakan Sistem.Data;
3menggunakan Sistem.Konfigurasi;
4menggunakan System.Collections;
5menggunakan Sistem.Web;
6menggunakan Sistem.Web.Keamanan;
7menggunakan Sistem.Web.UI;
8menggunakan System.Web.UI.WebControls;
9menggunakan System.Web.UI.WebControls.WebParts;
10menggunakan System.Web.UI.HtmlControls;
11
12menggunakan Sistem.Data;
13menggunakan System.Data.SqlClient;
14menggunakan EOffice.Model;
15menggunakan EOffice.SqlServer;
16menggunakan System.IO;
17menggunakan Sistem.Konfigurasi;
18
19//menggunakan iWebSMS2000;
20[Dapat diserialkan]
21kelas parsial publik GroupWork_SendEmail : System.Web.UI.Page
dua puluh dua{
23 koneksi SqlConnection publik;
24 //SMS iSMS2000 publik;
25 //DBstep publik.SMSClient2000 ObjiSMSClient2000;
26 dilindungi kekosongan Page_Load(pengirim objek, EventArgs e)
27 {
28 samb = new SqlConnection("SERVER=SERVER;UID=sa;PWD=8860;database=EOFFICE_SERVER;");
29
30}
31 void yang dilindungi btnSubmit_Click(pengirim objek, EventArgs e)
32 {
33 String strTitle = txtTitle.Teks;
34 String strKonten = txtKonten.Teks;
35 UserInfo info = (UserInfo)Sesi["EOfficeUserInfo"];
36 String strUserName = info.UserName;
37 String strTmpDept = info.DeptId.ToString();
38
39 String strUnitName = ConfigurationSettings.AppSettings["UnitName"];
40 String strServerFileIndex = ""; //Indeks lampiran file sisi server
41 String strClientFileIndex = ""; //Indeks lampiran file klien
42
43 String strRemote = HiddenField2.Nilai;
44 String[] strRemoteUser = strRemote.Split(',');
45 String strTmpNamaPengguna;
46 String strTmpServer;
47 Boolean bSelesai = salah;
48
49 /**/////////Unggah beberapa kode lampiran///////////////
50 //Dapatkan elemen formulir File
51 File HttpFileCollection = HttpContext.Current.Request.Files;
52 HttpPostedFile dipostingFile;
53
54 foreach (string strUser di strRemoteUser)
55 {
56 String[] strSplit = strUser.Split('/');
57 strTmpUserName = strSplit[0];
58 strTmpServer = strSplit[1];
59 String strConnectString = "";
60 StringstrNamaArea = "";
61 int nAreaID = 0;
62 String strDept = "";
63 strClientFileIndex = "";
64 Klien SqlConnection;
65
66 String strSQL = "Pilih * Dari Area Dimana AreaCode='" + strTmpServer + "'";
67 SqlCommand cmd = SqlCommand baru(strSQL, samb);
68 cmd.Koneksi.Buka();
69 menggunakan (SqlDataReader sdr = cmd.ExecuteReader())
70 {
71 jika (sdr.Baca())
72 {
73 strConnectString = sdr["ConnectString"].ToString();
74 strNama Area = sdr["Nama Area"].ToString();
75 nAreaID = Konversi.ToInt16(sdr["ID"]);
76 }
77 }
78 cmd.Koneksi.Tutup();
79
80 strSQL = "Pilih * Dari MemberList Dimana UserName='" + strTmpUserName + "' Dan AreaID=" + nAreaID;
81 cmd = SqlCommand baru(strSQL, samb);
82 cmd.Koneksi.Buka();
83 menggunakan (SqlDataReader sdr = cmd.ExecuteReader())
84 {
85 jika (sdr.Baca())
86 {
87 strDept = sdr["Departemen"].ToString();
88 }
89 }
90 cmd.Koneksi.Tutup();
91
92 connClient = SqlConnection baru(strConnectString);
93
94 untuk (int intCount = 0; intCount < files.Count; intCount++)
95 {
96 dipostingFile = file[intCount];
97
98 jika (dipostingFile.ContentLength > 0)
99 {
100 String strOldFilePath = dipostingFile.NamaFile;
101 String strFileName = strOldFilePath.Substring(strOldFilePath.LastIndexOf("\") + 1);
102
103 //Unggah file ke server
104 //File1.PostedFile.SaveAs("c:\Test\" + DateTime.Now.ToString("yyyyMMddhhmmss") + strExtension);
105
106 //Digunakan untuk menyimpan ukuran file
107 int intDocLen;
108 //Stream digunakan untuk membaca data yang diunggah
109 Aliran objekStream;
110 String strDocExt;
111 //Unggah konten spesifik file
112 intDocLen = dipostingFile.ContentLength;
113 strDocExt = strOldFilePath.Substring(strOldFilePath.LastIndexOf(".") + 1);
114
115 byte[] Docbuffer = byte baru[intDocLen];
116 objStream = dipostingFile.InputStream;
117
118
119 //Simpan file ke cache
120
121 //Cache akan disimpan ke database
122 objStream.Baca(Docbuffer, 0, intDocLen);
123
124 string fileType = dipostingFile.ContentType;
125
126
127 //Jalankan prosedur tersimpan di sisi server Send_Group_Email
128 jika (!bSelesai)
129 {
130 cmd = new SqlCommand("Send_Group_Email", samb);
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.Parameter.Tambahkan("@File_Index", SqlDbType.Int);
136
137 cmd.Parameter[0].Nilai = strNamaFile;
138 cmd.Parameter[1].Nilai = Docbuffer;
139 //cmd.Parameter[2].Nilai = strDocExt;]
140 cmd.Parameter[2].Nilai = Tipe file;
141
142 //cmd.Parameters.Add(new SqlParameter("@File_Index", SqlDbType.Int));
143 cmd.Parameter[3].Direction = ParameterDirection.ReturnValue;
144 cmd.Koneksi.Buka();
145 cmd.ExecuteNonQuery();
146 strServerFileIndex += cmd.Parameter[3].Value.ToString() + ",";
147 cmd.Koneksi.Tutup();
148 }
149
150 //Jalankan prosedur tersimpan klien 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.Parameter.Tambahkan("@File_Index", SqlDbType.Int);
157
158 cmd.Parameter[0].Nilai = strNamaFile;
159 cmd.Parameter[1].Nilai = Docbuffer;
160 //cmd.Parameter[2].Nilai = strDocExt;
161 cmd.Parameter[2].Nilai = Tipe file;
162
163 //cmd.Parameters.Add(new SqlParameter("@File_Index", SqlDbType.Int));
164 cmd.Parameter[3].Direction = ParameterDirection.ReturnValue;
165 cmd.Koneksi.Buka();
166 cmd.ExecuteNonQuery();
167 strClientFileIndex += cmd.Parameter[3].Value.ToString() + ",";
168 cmd.Koneksi.Tutup();
169 }
170
171 if (intCount.Equals(files.Count - 1))
172 {
173 bSelesai = benar;
174 }
175 }
176
177 strClientFileIndex = strClientFileIndex.Hapus(strClientFileIndex.Panjang - 1);
178
179 strSQL = "Masukkan Ke Group_Email (Judul,Isi,Area,SendToUser,Dept,Sender,Files_Index)";
180 strSQL += " nilai(";
181 strSQL += "'" + strJudul + "',";
182 strSQL += "'" + strKonten + "',";
183 strSQL += "'" + strUnitNama + "',";
184 strSQL += "'" + strTmpUserName + "',";
185 strSQL += "'" + strDept + "',";
186 strSQL += "'" + strUserName + "',";
187 strSQL += "'" + strClientFileIndex + "')";
188 cmd = SqlCommand baru(strSQL, connClient);
189 cmd.Koneksi.Buka();
190 cmd.ExecuteNonQuery();
191 cmd.Koneksi.Tutup();
192
193 String strTmpServerFileIndex = strServerFileIndex.Remove(strServerFileIndex.Length - 1);
194
195 strSQL = "Masukkan Ke Group_Email_Sever (Judul,Isi,Area,SendToUser,Dept,Sender,Files_Index)";
196 strSQL += " nilai(";
197 strSQL += "'" + strJudul + "',";
198 strSQL += "'" + strKonten + "',";
199 strSQL += "'" + strNamaArea + "',";
200 strSQL += "'" + strTmpUserName + "',";
201 strSQL += "'" + strTmpDept + "',";
202 strSQL += "'" + strNamaPengguna + "',";
203 strSQL += "'" + strTmpServerFileIndex + "')";
204 cmd = SqlCommand baru(strSQL, samb);
205 cmd.Koneksi.Buka();
206 cmd.ExecuteNonQuery();
207 cmd.Koneksi.Tutup();
208 }
209 /**//////////Akhir mengunggah beberapa lampiran///////////////
210 Respon.Redirect("../SuccessMsg.aspx");
211 }
212}
213
Kode untuk mendownload file:
DownFile.aspx
DownFile.aspx.cs
1menggunakan Sistem;
2menggunakan Sistem.Data;
3menggunakan Sistem.Konfigurasi;
4menggunakan System.Collections;
5menggunakan Sistem.Web;
6menggunakan Sistem.Web.Keamanan;
7menggunakan Sistem.Web.UI;
8menggunakan System.Web.UI.WebControls;
9menggunakan System.Web.UI.WebControls.WebParts;
10menggunakan System.Web.UI.HtmlControls;
11
12menggunakan Sistem.Data;
13menggunakan System.Data.SqlClient;
14menggunakan EOffice.Model;
15menggunakan EOffice.SqlServer;
16menggunakan System.IO;
17
18[Dapat diserialkan]
19kelas parsial publik GroupWork_DownFile : System.Web.UI.Page
20{
21 koneksi SqlConnection publik;
22 strFileID String publik;
23 info Info Pengguna publik;
24 dilindungi kekosongan Page_Load (pengirim objek, EventArgs e)
25 {
26 strFileID = Permintaan.QueryString["ID"];
27 info = (UserInfo)Sesi["EOfficeUserInfo"];
28
29 jika (strFileID == nol)
30 {
31 Respon.Redirect("../ErrorMsg.aspx");
32}
33
34 DbLink db = DbLink baru();
35 samb = db.Connect();
36
37 String strSQL = "Pilih * Dari Group_Files Where ID=" + strFileID; //+ " Dan SendToUser = '" + info.UserName + "'";
38 SqlCommand cmd = SqlCommand baru(strSQL, samb);
39 cmd.Koneksi.Buka();
40 menggunakan (SqlDataReader sdr = cmd.ExecuteReader())
41 {
42 jika (!sdr.Baca())
43 {
44 Respon.Redirect("../ErrorMsg.aspx");
45 }
46 lainnya
47 {
48 Respon.Hapus();
49 String strFileName = sdr["NamaFile"].ToString();
50 Response.ContentType = "APLIKASI/STREAM OKTET";
51 Response.AddHeader("disposisi konten", "lampiran;namafile=" + HttpUtility.UrlEncode(strFileName));
52 Respon.Buffer = benar;
53 Respon.BinaryWrite((byte[])sdr["FileBody"]);
54
55 //Respon.Hapus();
56 //Response.ContentType = "application/octet-stream";
57 /**/////Response.AddHeader("Tipe Konten", sdr["Jenis File"].ToString());
58 //Respon.BinaryWrite((byte[])sdr["FileBody"]);
59 }
60 }
61 //sambungan.Tutup();
62
63 /**//*
64 SqlDataAdapter da = new SqlDataAdapter(strSQL, samb);
65 SqlCommandBuilder MyCB = SqlCommandBuilder baru(da);
66 Kumpulan Data ds = Kumpulan Data baru("Gambar Saya");
67 byte[] Data Saya = byte baru[0];
68 da.Isi(ds, "Gambar Saya");
69 DataRow myRow = ds.Tables["MyImages"].Rows[0];
70 String strFileName = Konversi.ToString(myRow["NamaFile"]);
71 Data Saya = (byte[])Rowsaya["Badan File"];
72 int Ukuran Array = int baru();
73 Ukuran Array = Data Saya.GetUpperBound(0);
74 FileStream fs = FileStream baru(@"C:\Unduh" + strNamaFile,
75 FileMode.OpenOrCreate, FileAccess.Write);
76 fs.Write(Data Saya, 0, Ukuran Array);
77 fs.Tutup();
78*/
79 //Response.Write("Unduh file" + strNamaFile + "Sukses!");
80}
81}
82