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()" />
</div>
</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);
}
}