Saving Blob Files direct to SQL Server

The common thinking of a developer if asked for file upload and download component is to put it in a shared directory and upload it as part of a file system. But there is a more neater way of doing it and Sharepoint does it. Now, ill show you how I do it with ASP.NET.

First lets setup our page to support vb.net function call from client side using PageMethods.

.....
<form id="frmStrive" runat="server">
<asp:ScriptManager ID="scriptManager" runat="server" EnablePageMethods="true" />
<asp:FileUpload ID="fileUpload" runat="server" Width="100%" onchange="PutDescOnParent(this.value);"/>
...

right after our form tag is we created a scriptmanager and the most important thing is that we set EnablePageMethods=”true” which will do the trick.

Next we set up our client side code.

function onUploadClick(){
            if ($j("#txtUploadDesc").val() == '') {
                alert("Please put a description for this file.");
                return;
            }
    
            var vaild = fileUpload.value.length > 0;
            if(vaild){
                //  disable the upload button
                $get('upload').disabled = 'disabled';
                
                //  update the message
                updateMessage('info', 'Initializing upload ...');
                PageMethods.SaveFileToDb(document.getElementById("txtUploadDesc").value);
                //  submit the form containing the fileupload
                
                form.submit();
....

Now if you’ll notice on the 14th line i called PageMethods.SaveFileToDb where it accepts one parameter and call a server side code. Lets see what it does from behind.

<System.Web.Services.WebMethod()> _
    <System.Web.Script.Services.ScriptMethod()> _
    Public Shared Sub SaveFileToDb()
        Dim files As New System.Collections.Generic.Dictionary(Of String, String)
        files = DirectCast(HttpContext.Current.Session("Files"), System.Collections.Generic.Dictionary(Of String, String))

        For Each kvp As KeyValuePair(Of String, String) In files
            Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection2").ConnectionString)
                Const SQL As String = "INSERT INTO [tblFiles] (filename, filedescription, mainfile,parent) VALUES (@fileName, @fileDescription, @mainFile,@parent)"
                Dim myCommand As New System.Data.SqlClient.SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@fileName", kvp.Key)
                myCommand.Parameters.AddWithValue("@fileDescription", kvp.Value)
                Dim im As New _HR001
                Dim fle As New System.IO.FileInfo(im.GetPath(kvp.Key))
                im.Dispose()
                Dim imageBytes(fle.Length) As Byte
                Dim s As System.IO.Stream
                s = fle.OpenRead
                s.Read(imageBytes, 0, imageBytes.Length)
                myCommand.Parameters.Add("@mainFile", Data.SqlDbType.VarBinary)
                myCommand.Parameters("@mainFile").Value = imageBytes
                myCommand.Parameters.AddWithValue("@parent", New _HR001().GetGuid())
                myConnection.Open()
                myCommand.ExecuteNonQuery()
                myConnection.Close()
            End Using
        Next kvp
    End Sub

Now what this block of code does is that it simply retrieves the path of the file(s) the we temporarily store on a session then inserts those files to a particular table which in my case named tblFiles containing a varbinary(max) field which is the container of binary files. Oh by the way during the time we started our session, we created a temporary directory on the server which is identified via creating a unique id and is achieved by the below line.

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        Dim dir As New System.IO.DirectoryInfo(Server.MapPath("~/uploads/"))
        dir.CreateSubdirectory(Session("TempDir"))
        Session("Files") = New System.Collections.Generic.Dictionary(Of String, String)
End Sub 

NOTE: In sql server 2000, you can upload up to 8mb files only, which is upgraded in sql server 2005 up. In sql server 2005 up? It depends on your disk space. Now let me show you the structure of my table.

You might be asking, after saving to sql server how will i retrieve it? Here’s how.

Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection2").ConnectionString)

                Const SQL As String = "SELECT * from tblFiles where fileId=@fileId"
                Dim myCommand As New System.Data.SqlClient.SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@fileId", HttpContext.Current.Request.QueryString("fileId"))

                myConnection.Open()
                Dim myReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader

                If myReader.Read Then

                    Response.ClearContent()
                    Response.AddHeader("Content-Disposition", "attachment; filename=""" & myReader.Item("fileName") & """")

                    Dim bw As New System.IO.BinaryWriter(Response.OutputStream)
                    bw.Write(DirectCast(myReader("mainFile"), Byte()))
                    bw.Close()

                    Response.End()

                End If

                myReader.Close()
                myConnection.Close()


            End Using

See the picture? Its not quite complex. Have to continue doing my project. My next blog will be about how to convert a lousy windows forms to WPF the silverlight way.

Advertisement