Uploading files to database from ASP.NET pages

ASP.NET Community

3.72/5 (7 votes)

Oct 11, 2013

CPOL

40556

This example will show how is possible to upload files to SQL server database using C# and asp.net. For demostration purposes database will have only

This example will show how is possible to upload files to SQL server database using C# and asp.net. For demostration purposes database will have only one table named File with colums: FileId, Filename, Extension, ContentType, Document. FileId serves as primary key in table.

At the first step we must insert one html file control named txtFile and button btnSave which will submit form.

 <form id="frmMain" runat="server">
<div>
<input type="file" id="txtFile" title=" Browse for file which you want to save " Runat="server"/>
<input id="btnSave" type="button" value="Save" onclick="document.frmMain.submit()" />

</form>

Following code is create statement for File table:

CREATE TABLE [dbo].[File](
[FileId] [int] IDENTITY(1,1) NOT NULL,
[Filename] [varchar](100) NULL,
[Extension] [varchar](8) NULL,
[ContentType] [varchar](50) NULL,
[Document] [varbinary](50) NULL,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

For saving files into table will serve stored procede spFile_Add which saves file into table File:

CREATE PROCEDURE spFile_Add
(
@Filename varchar(100),
@Extension varchar(8),
@ContentType varchar(50),
@Document varbinary(50)
)
AS
BEGIN
Insert Into [File](Filename, Extension, ContentType, Document)
values (@Filename, @Extension, @ContentType, @Document)
Select Scope_Identity()
END

We will use Enterprise library from database transactions, hence we must add same references:

Microsoft.Practices.EnterpriseLibrary.Common
Microsoft.Practices.EnterpriseLibrary.Data
System.Configuration

In Code behind file we must add following lines of code:

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
protected void Page_Load(object sender, EventArgs e)
{
    if (Page.IsPostBack)
    {
        if (txtFile.PostedFile != null && txtFile.PostedFile.ContentLength > 0)
        {
            string filename = Path.GetFileName(txtFile.PostedFile.FileName);
            string extension = Path.GetExtension(filename);
            string contentType = txtFile.PostedFile.ContentType;
            byte[] document = new byte[txtFile.PostedFile.ContentLength];
            int fileId = AddFile(filename, extension, contentType, document);
        }
    }
}
protected int AddFile(string filename, string extension, string contentType, byte[] document)
{
    SqlDatabase sqlDatabase = new SqlDatabase
    (ConfigurationManager.ConnectionStrings["dbConnString"].ConnectionString);
    string sql = "spFile_Add";
    SqlCommand sqlCommand = sqlDatabase.GetStoredProcCommand(sql) as SqlCommand;
    Object obj = null;
    try
    {
        sqlDatabase.AddInParameter(sqlCommand, "@Filename", SqlDbType.VarChar, filename);
        sqlDatabase.AddInParameter(sqlCommand, "@Extension", SqlDbType.VarChar, extension);
        sqlDatabase.AddInParameter(sqlCommand, "@ContentType", SqlDbType.VarChar, contentType);
        sqlDatabase.AddInParameter(sqlCommand, "@Document", SqlDbType.VarBinary, document);
        obj = sqlDatabase.ExecuteScalar(sqlCommand);
        if (obj != null)
            return int.Parse(obj.ToString());
        else
            return 0;
    }
    catch (Exception err)
    {
        throw new ApplicationException(err.Message);
    }
}