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);
}
}
The ASP.NET Wiki was started by Scott Hanselman in February of 2008. The idea is that folks spend a lot of time trolling the blogs, googlinglive-searching for answers to common "How To" questions. There's piles of fantastic community-created and MSFT-created content out there, but if it's not found by a search engine and the right combination of keywords, it's often lost.
The ASP.NET Wiki articles moved to CodeProject in October 2013 and will live on, loved, protected and updated by the community.