Save files to SQL Server 2005 using Asp.net

In this post “Save files to SQL Server 2005 using Asp.net”, we will see how to save Document, Excel and PDF files into SQL Server 2005 database from an Asp.Net form.  

Uploading files is a common activity in Asp.net application, in general we upload the files and save them to the file system and store the path of the saved file in the database, but there are specific scenarios which demand us to store the entire file in the database in binary format, we shall see how this can be achieved using Asp.Net

First create a table to store the Documents uploaded, add a column with type varbinary (MAX), this column will be used to store the content of the uploaded file in binary format.

Also create a Stored Procedure which will be used to insert the document content.

CREATE PROCEDURE [dbo].[InsertDocument]

      @FileName varchar(50),

      @Extension varchar(5),

      @FileContent varbinary(max)

AS

BEGIN

      INSERT INTO Profiles (FileName, Extension, FileContent)

      Values (@FileName,@Extension,@FileContent);

END

Next create an Asp.net application and add a new .aspx file to the application.
In the .aspx file add a FileUpload control as follows

<asp:FileUpload ID="fileUploadDocument" runat="server" /> 

<asp:Button ID="cmdUpload" runat="server" Text="Upload"

    onclick="cmdUpload_Click" /><br /><br />

<asp:Label ID="lblMsg" runat="server" ForeColor="Red"></asp:Label>

Once the controls are added move to the code-behind file to write the upload logic as follows.

protected void cmdUpload_Click(object sender, EventArgs e)

{

    try

    {

        if (fileUploadDocument.PostedFile.ContentLength > 0)

        {

            // Get the File name and Extension

            strFileName = Path.GetFileName(fileUploadDocument.PostedFile.FileName);

            strFileExtension = Path.GetExtension(fileUploadDocument.PostedFile.FileName);

            //

            // Extract the content of the Document into a Byte array

            int intlength = fileUploadDocument.PostedFile.ContentLength;

            Byte[] byteData = new Byte[intlength];

            fileUploadDocument.PostedFile.InputStream.Read(byteData, 0, intlength);

            //

            // Save the file to the DB

            string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

            objConn = new SqlConnection(strConn);

            objCmd = new SqlCommand("InsertDocument", objConn);

            //

            objCmd.CommandType = CommandType.StoredProcedure;

            //

            SqlParameter pFileName = new SqlParameter("FileName", SqlDbType.VarChar);

            pFileName.Size = 50;

            pFileName.Direction = ParameterDirection.Input;

            pFileName.Value = strFileName;

            //

            SqlParameter pExtension = new SqlParameter("Extension", SqlDbType.VarChar);

            pExtension.Size = 5;

            pExtension.Direction = ParameterDirection.Input;

            pExtension.Value = strFileExtension;

            //

            SqlParameter pFileContent = new SqlParameter("FileContent", SqlDbType.VarBinary);

            pFileContent.Size = byteData.Length;

            pFileContent.Direction = ParameterDirection.Input;

            pFileContent.Value = byteData;

            //

            objCmd.Parameters.Add(pFileName);

            objCmd.Parameters.Add(pExtension);

            objCmd.Parameters.Add(pFileContent);

            //

            objConn.Open();

            objCmd.ExecuteNonQuery();

            objConn.Close();

            //

            lblMsg.Text = "Document Uploaded Succesfully" ;

        }

    }

    catch (Exception ex)

    {

        lblMsg.Text = " Error uploading Document: " + ex.Message.ToString();

    }

}

Run the application, browse the required file and click on the Upload button, the file will be conterted into BYTEs and will be stored in the SQL Server 2005 database.

That’s it we have Stored a file into SQL Server 2005 database using Asp.net

Refer the post Retrieve files from SQL Server 2005 using Asp.net, to see on how to retrieve the saved files from the database.

Related Post
Retrieve files from SQL Server 2005 using Asp.net