ASP.NET – Upload (Insert) File / Download (Get) File 至 MSSQL Database
MSSQL Database Table : TblUploadDownLoad |
CREATE TABLE [dbo].[TblUploadDownLoad]( [FileId] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY, [FileName] [varchar](200) NULL, [Extension] [char](10) NULL, [FileData] [varbinary](max) NULL ) |
MSSQL Database Procedure : Sp_AddFiles |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Sp_AddFiles') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Sp_AddFiles] GO CREATE PROCEDURE [dbo].[Sp_AddFiles] ( @fname Varchar(200), @ext char(10), @fdata VarBinary(max) ) AS BEGIN INSERT INTO [dbo].[TblUploadDownLoad] ([FileName], [Extension], [FileData]) VALUES (@fname, @ext, @fdata) END |
MSSQL Database Procedure : Sp_GetFile |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Sp_GetFile') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Sp_GetFile] GO CREATE PROCEDURE [dbo].[Sp_GetFile] ( @fid int ) AS BEGIN SELECT [FileData], [Extension] FROM [dbo].[TblUploadDownLoad] WHERE [FileId] = @fid END |
MSSQL Database Procedure : Sp_GetFileNames |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Sp_GetFileNames') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[Sp_GetFileNames] GO CREATE PROCEDURE [dbo].[Sp_GetFileNames] ( @fid int ) AS BEGIN SELECT [FileId], [FileName] FROM [dbo].[TblUploadDownLoad] END |
web.conf |
<?xml version="1.0"?> <configuration> … … <connectionStrings> <add name="sqlcn" connectionString="Data Source=VMSER001;Initial Catalog=Development;User ID=sa;Password=P@ssw0rd" providerName="System.Data.SqlClient" /> </connectionStrings> … … </configuration> |
App_Code\DataHelper.cs ( Compile ) |
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace FileUploadDatabaseSample.App_Code { public class DataHelper { static SqlConnection sqlcn; static SqlCommand sqlcmd; static SqlDataAdapter sqlda; static SqlDataReader sqldr; static int _records; static string _ext; public static string Ext { get { return DataHelper._ext; } set { DataHelper._ext = value; } } static DataHelper() { sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings[“sqlcn"].ConnectionString); } public static int Records { get { return DataHelper._records; } set { DataHelper._records = value; } } public static void ExecuteProcedure(string _procedurename, SqlParameter[] sqlparam) { try { sqlcn.Open(); sqlcmd = new SqlCommand(_procedurename, sqlcn); foreach (SqlParameter p in sqlparam) { sqlcmd.Parameters.Add(p); } sqlcmd.CommandType = CommandType.StoredProcedure; _records = sqlcmd.ExecuteNonQuery(); } catch (Exception) {throw;} finally {sqlcn.Close();} } public static DataSet GetFileNames(string _sql, SqlParameter[] Param) { try { sqlcmd = new SqlCommand(_sql, sqlcn); foreach (SqlParameter p in Param) { sqlcmd.Parameters.Add(p); } sqlcmd.CommandType = CommandType.StoredProcedure; sqlcn.Open(); DataSet ds = new DataSet(); sqlda = new SqlDataAdapter(sqlcmd); sqlda.Fill(ds); return ds; } catch (Exception) {throw;} finally {sqlcn.Close();} } public static byte[] GetFileFromDB(SqlParameter[] Param, string _procname) { try { byte[] file = null; sqlcn.Open(); SqlCommand sqlcmd = new SqlCommand(_procname, sqlcn); sqlcmd.CommandType = CommandType.StoredProcedure; foreach (SqlParameter para in Param) { sqlcmd.Parameters.Add(para); } sqldr = sqlcmd.ExecuteReader(); if (sqldr.Read()) { file = (byte[])sqldr[0]; _ext = sqldr[1].ToString(); } return file; } catch (Exception) {throw;} finally { sqldr.Close(); sqlcn.Close(); } } } } |
App_Code\Upload.cs ( Compile ) |
using System; using System.Data; using System.Data.SqlClient; using FileUploadDatabaseSample.App_Code; namespace FileUploadDatabaseSample.App_Code { public class Upload { public bool UploadFile(string _filename, byte[] _filedata, string _ext) { string _spname = "Sp_AddFiles"; SqlParameter[] Param = new SqlParameter[3]; Param[0] = new SqlParameter("@fname", SqlDbType.VarChar, 200); Param[0].Value = _filename; Param[1] = new SqlParameter("@ext", SqlDbType.Char, 10); Param[1].Value = _ext; Param[2] = new SqlParameter("@fdata", SqlDbType.VarBinary); Param[2].Value = _filedata; DataHelper.ExecuteProcedure(_spname, Param); if (DataHelper.Records > 0) return true; else return false; } public DataSet GetFileNames() { string _sql = "Sp_GetFileNames"; SqlParameter[] Param = new SqlParameter[0]; DataSet ds = DataHelper.GetFileNames(_sql, Param); return ds; } public byte[] GetFile(int _fid) { byte[] _file = null; //String _ext = null; SqlParameter[] Param = new SqlParameter[1]; Param[0] = new SqlParameter("@fid", SqlDbType.Int); Param[0].Value = _fid; _file = DataHelper.GetFileFromDB(Param, "Sp_GetFile"); //_ext = DataHelper.Ext; return _file; } } } |
Upload.aspx |
<asp:FileUpload ID="fileuploadpanel" runat="server" /><br /> <asp:Button ID="submitbtn" runat="server" Text="Button" onclick="submitbtn_click" /> <asp:Label ID="notificationlbl" runat="server" Text="" style="color:#FF0000;" /> |
Upload.aspx.cs |
using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using FileUploadDatabaseSample.App_Code; namespace FileUploadDatabaseSample { public partial class Upload : System.Web.UI.Page { … … protected void submitbtn_click(object sender, EventArgs e) { Upload obj = new Upload(); try { if (fileuploadpanel.HasFile) { Int32 intfileSize = 0; Stream fileStream = null; intfileSize = fileuploadpanel.PostedFile.ContentLength; fileStream = fileuploadpanel.PostedFile.InputStream; byte[] fileContent = new byte[intfileSize + 1]; int intStatus = 0; intStatus = fileStream.Read(fileContent, 0, intfileSize); string ext = System.IO.Path.GetExtension(fileuploadpanel.FileName.ToString()); if (obj.UploadFile(fileuploadpanel.FileName.ToString(), fileContent, ext)) { notificationlbl.Visible = true; notificationlbl.Text = "File Uploaded"; } } } catch (Exception) { throw; } } } } |
Download.aspx |
<asp:DropDownList ID="FileList" runat="server" /><br /> <asp:Button ID="downloadbtn" runat="server" Text="Button" onclick="downloadbtn_click" /> |
Download.aspx.cs |
using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using FileUploadDatabaseSample.App_Code; using System.IO; namespace FileUploadDatabaseSample { public partial class Download : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Upload obj = new Upload(); if (!IsPostBack) { DataSet ds = obj.GetFileNames(); FileList.DataSource = ds.Tables[0]; FileList.DataTextField = "FileName"; FileList.DataValueField = "FileId"; FileList.DataBind(); } } protected void downloadbtn_click(object sender, EventArgs e) { Upload obj = new Upload(); try { byte[] fileData = obj.GetFile(int.Parse(FileList.SelectedValue)); Response.ClearContent(); Response.AddHeader("Content-Disposition", "attachment; filename=" + FileList.SelectedItem.Text.ToString()); BinaryWriter bw = new BinaryWriter(Response.OutputStream); bw.Write(fileData); bw.Close(); String[] item = FileList.SelectedItem.Text.ToString().Split('.'); Response.ContentType = ReturnExtension(item[item.Length – 1]); Response.End(); } catch (Exception) { throw; } } private string ReturnExtension(string fileExtension) { switch (fileExtension) { case ".htm": case ".html": case ".log": return "text/HTML"; case ".txt": return "text/plain"; case ".docx": return "application/ms-word"; case ".tiff": case ".tif": return "image/tiff"; case ".asf": return "video/x-ms-asf"; case ".avi": return "video/avi"; case ".zip": return "application/zip"; case ".xls": case ".csv": return "application/vnd.ms-excel"; case ".gif": return "image/gif"; case ".jpg": case "jpeg": return "image/jpeg"; case ".bmp": return "image/bmp"; case ".wav": return "audio/wav"; case ".mp3": return "audio/mpeg3"; case ".mpg": case "mpeg": return "video/mpeg"; case ".rtf": return "application/rtf"; case ".asp": return "text/asp"; case ".pdf": return "application/pdf"; case ".fdf": return "application/vnd.fdf"; case ".ppt": return "application/mspowerpoint"; case ".dwg": return "image/vnd.dwg"; case ".msg": return "application/msoutlook"; case ".xml": case ".sdxl": return "application/xml"; case ".xdp": return "application/vnd.adobe.xdp+xml"; default: return "application/octet-stream"; } } } } |