Upload Large Files in SQL in an ASP.NET web application:
In this article we learn how to upload large files in sql
sever database by using asp.net file upload control
FileUploadLarge.aspx
<%@ Page Language="C#"
%>
<%@ Import Namespace="System.IO"
%>
<%@ Import Namespace="System.Data"
%>
<%@ Import Namespace="System.Data.SqlClient"
%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
const string conString = @"Server=SAI;Integrated
Security=True; initial catalog=master";
void
btnAdd_Click(Object s, EventArgs
e)
{
if
(upFile.HasFile)
{
if
(CheckFileType(upFile.FileName))
{
AddFile(upFile.FileName,
upFile.FileContent);
rptFiles.DataBind();
}
}
}
bool
CheckFileType(string fileName)
{
return Path.GetExtension(fileName).ToLower() == ".doc";
}
void AddFile(string fileName, Stream
upload)
{
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand("INSERT Files (FileName) Values (@FileName);"
+ "SELECT @Identity = SCOPE_IDENTITY()",
con);
cmd.Parameters.AddWithValue("@FileName", fileName);
SqlParameter idParm =
cmd.Parameters.Add("@Identity", SqlDbType.Int);
idParm.Direction = ParameterDirection.Output;
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
int newFileId
= (int)idParm.Value;
StoreFile(newFileId, upload,
con);
}
}
void StoreFile(int fileId, Stream
upload, SqlConnection connection)
{
int bufferLen
= 8040;
BinaryReader br = new BinaryReader(upload);
byte[] chunk =
br.ReadBytes(bufferLen);
SqlCommand cmd = new SqlCommand("UPDATE Files SET FileBytes=@Buffer WHERE
Id=@FileId", connection);
cmd.Parameters.AddWithValue("@FileId", fileId);
cmd.Parameters.Add("@Buffer", SqlDbType.VarBinary,
bufferLen).Value = chunk;
cmd.ExecuteNonQuery();
SqlCommand cmdAppend
= new SqlCommand("UPDATE Files SET FileBytes .WRITE(@Buffer, NULL, 0)
WHERE Id=@FileId", connection);
cmdAppend.Parameters.AddWithValue("@FileId", fileId);
cmdAppend.Parameters.Add("@Buffer", SqlDbType.VarBinary,
bufferLen);
chunk = br.ReadBytes(bufferLen);
while
(chunk.Length > 0)
{
cmdAppend.Parameters["@Buffer"].Value = chunk;
cmdAppend.ExecuteNonQuery();
chunk =
br.ReadBytes(bufferLen);
}
br.Close();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml"
>
<head id="Head1"
runat="server">
<title>FileUpload Large</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<asp:Label
id="lblFile"
Text="Word
Document:"
AssociatedControlID="upFile"
Runat="server" />
<asp:FileUpload
id="upFile"
Runat="server" />
<asp:Button
id="btnAdd"
Text="Add
Document"
OnClick="btnAdd_Click"
Runat="server" />
<hr />
<asp:Repeater
id="rptFiles"
DataSourceID="srcFiles"
Runat="server">
<HeaderTemplate>
<ul class="fileList">
</HeaderTemplate>
<ItemTemplate>
<li>
<asp:HyperLink
id="lnkFile"
Text='<%#Eval("FileName")%>'
NavigateUrl='<%#Eval("Id",
"~/FileHandlerLarge.ashx?id={0}")%>'
Runat="server" />
</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
<asp:SqlDataSource id="srcFiles"
ConnectionString="Server=SAI;Integrated
Security=True; Initial catalog=master;" SelectCommand="SELECT Id,FileName FROM Files"
runat="server" />
</div>
</form>
</body>
</html>
Now add a .ashx file and use this
code in it
<%@ WebHandler Language="C#"
Class="FileHandlerLarge"
%>
using System;
using
System.Web;
using System.Data;
using
System.Data.SqlClient;
public class FileHandlerLarge
: IHttpHandler {
const string conString = @"Server=SAI;Integrated
Security=True;Initial catalog= master";
public void ProcessRequest (HttpContext
context)
{context.Response.Buffer = false;
context.Response.ContentType
= "application/msword";
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand("SELECT FileBytes FROM Files WHERE Id=@Id",
con);
cmd.Parameters.AddWithValue("@Id", context.Request["Id"]);
using (con)
{
con.Open();
SqlDataReader reader =
cmd.ExecuteReader
(CommandBehavior.SequentialAccess);
if
(reader.Read())
{
int bufferSize
= 8040;
byte[] chunk = new byte[bufferSize];
long retCount;
long startIndex
= 0;
retCount =
reader.GetBytes(0, startIndex, chunk, 0, bufferSize);
while (retCount
== bufferSize)
{
context.Response.BinaryWrite(chunk);
startIndex += bufferSize;
retCount =
reader.GetBytes(0, startIndex, chunk, 0, bufferSize);
}
byte[]
actualChunk = new Byte[retCount
- 1];
Buffer.BlockCopy(chunk,
0, actualChunk, 0, (int)retCount - 1);
context.Response.BinaryWrite(actualChunk);
}
}
}
public bool IsReusable {
get {
return false;
}
}
}
In web.config file under System.web
tag add this line
<httpRuntime maxRequestLength="10240"
requestLengthDiskThreshold="100" />
Conclusion:
NOTE : This article is excerpted for the BOOK
“ASP.NET 3.5 Unleashed” by Stephen Walther
Here we learned how to use fileupload control
to save large files
You have to browse .doc files here
Thanks !!!
For further help feel free to contact me B)
[email protected]
Interface:
![FileUpload Control..JPG]()