ASP.NET – Upload (Insert) File / Download (Get) File 至 MSSQL Database

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