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

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>

<title>FileUpload Large</title>

</head>

<body>

<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>

<li>

<asp:HyperLink

id="lnkFile"

Text='<%#Eval("FileName")%>'

NavigateUrl='<%#Eval("Id", "~/FileHandlerLarge.ashx?id={0}")%>'

Runat="server" />

</li>

</ItemTemplate>

</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