Save Store Files In Sql Database Download From GridView Asp.Net

Upload Save Or Store Files In Ms Sql Server Database And Download In Asp.Net.

In this post i'm explaining how to save pdf,word,excel,jpeg,gif,png files in MS sqlserver database .

Save or Store files in sqlserver database asp.net

I'll also explain how to download files from sql database.

For this example i have created a sample database with table name SaveDoc to save documents in it. table schema is shown below in image.

Read Display Images In GridView From SqlServer DataBase Asp.Net to know how to save and retrieve images in sql database and display them in gridview using handler.




For uploading and saving files to database we need to use Fileupload control, so drag and place one fileupload control and one button on the aspx page in design mode.

Place one label on the page to display success or failure message, and one gridview to display uploaded documents and provide link to download document files.

I have added one button field in gridview to provide download link to file shown in respective row of gridview and this gridview is populated by sqlDataSource.

HTML SOURCE OF THE PAGE
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server"/>
<asp:Button ID="btnUpload" runat="server"  
            onclick="btnUpload_Click" 
            Text="Upload"/>
</div>
<br/>
<asp:Label ID="lblMessage" runat="server">
</asp:Label><br /><br /><br />
      
<asp:GridView ID="GridView1" runat="server" 
              AutoGenerateColumns="False" 
              DataSourceID="SqlDataSource1" 
              onrowcommand="GridView1_RowCommand" 
              DataKeyNames="DocID">
<Columns>
<asp:BoundField DataField="DocID" HeaderText="DocID" 
                InsertVisible="False" 
                ReadOnly="True" 
                SortExpression="DocID" />

<asp:BoundField DataField="DocName" 
                HeaderText="DocName" 
                SortExpression="DocName" />

<asp:BoundField DataField="Type" HeaderText="Type" 
                SortExpression="Type" />
            
<asp:ButtonField ButtonType="Image"  
                ImageUrl="~/download.png" 
                CommandName="Download" 
                HeaderText="Download" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
SelectCommand="SELECT [DocID], [DocName], [Type] 
               FROM [SaveDoc]">
</asp:SqlDataSource>
</form>

To upload and save files in database write code mentioned below in Click event of upload button we placed on aspx page.

C# CODE
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

protected void btnUpload_Click(object sender, EventArgs e)
    {
        //Check whether FileUpload control has file 
        if (FileUpload1.HasFile)
        {
            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string documentType = string.Empty;

            //provide document type based on it's extension
            switch (fileExtension)
            {
                case ".pdf":
                    documentType = "application/pdf";
                    break;
                case ".xls":
                    documentType = "application/vnd.ms-excel";
                    break;
                case ".xlsx":
                    documentType = "application/vnd.ms-excel";
                    break;
                case ".doc":
                    documentType = "application/vnd.ms-word";
                    break;
                case ".docx":
                    documentType = "application/vnd.ms-word";
                    break;
                case ".gif":
                    documentType = "image/gif";
                    break;
                case ".png":
                    documentType = "image/png";
                    break;
                case ".jpg":
                    documentType = "image/jpg";
                    break;
            }

            //Calculate size of file to be uploaded
            int fileSize = FileUpload1.PostedFile.ContentLength;

            //Create array and read the file into it
            byte[] documentBinary = new byte[fileSize];
            FileUpload1.PostedFile.InputStream.Read(documentBinary, 0, fileSize);

            // Create SQL Connection 
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            // Create SQL Command and Sql Parameters 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO SaveDoc(DocName,Type,DocData)" +
                              " VALUES (@DocName,@Type,@DocData)";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            SqlParameter DocName = new SqlParameter("@DocName", SqlDbType.VarChar, 50);
            DocName.Value = fileName.ToString();
            cmd.Parameters.Add(DocName);

            SqlParameter Type = new SqlParameter("@Type", SqlDbType.VarChar, 50);
            Type.Value = documentType.ToString();
            cmd.Parameters.Add(Type);

            SqlParameter uploadedDocument = new SqlParameter("@DocData", SqlDbType.Binary,fileSize);
            uploadedDocument.Value = documentBinary;
            cmd.Parameters.Add(uploadedDocument);

            con.Open();
            int result = cmd.ExecuteNonQuery();
            con.Close();
            if (result > 0)
                lblMessage.Text = "File saved to database";
            GridView1.DataBind();
        }
    }

VB.NET CODE
Protected Sub btnUpload_Click(sender As Object, e As EventArgs)
 'Check whether FileUpload control has file 
 If FileUpload1.HasFile Then
  Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
  Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
  Dim documentType As String = String.Empty

  'provide document type based on it's extension
  Select Case fileExtension
   Case ".pdf"
    documentType = "application/pdf"
    Exit Select
   Case ".xls"
    documentType = "application/vnd.ms-excel"
    Exit Select
   Case ".xlsx"
    documentType = "application/vnd.ms-excel"
    Exit Select
   Case ".doc"
    documentType = "application/vnd.ms-word"
    Exit Select
   Case ".docx"
    documentType = "application/vnd.ms-word"
    Exit Select
   Case ".gif"
    documentType = "image/gif"
    Exit Select
   Case ".png"
    documentType = "image/png"
    Exit Select
   Case ".jpg"
    documentType = "image/jpg"
    Exit Select
  End Select

  'Calculate size of file to be uploaded
  Dim fileSize As Integer = FileUpload1.PostedFile.ContentLength

  'Create array and read the file into it
  Dim documentBinary As Byte() = New Byte(fileSize - 1) {}
  FileUpload1.PostedFile.InputStream.Read(documentBinary, 0, fileSize)

  ' Create SQL Connection 
  Dim con As New SqlConnection()
  con.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

  ' Create SQL Command and Sql Parameters 
  Dim cmd As New SqlCommand()
  cmd.CommandText = "INSERT INTO SaveDoc(DocName,Type,DocData)" & " VALUES (@DocName,@Type,@DocData)"
  cmd.CommandType = CommandType.Text
  cmd.Connection = con

  Dim DocName As New SqlParameter("@DocName", SqlDbType.VarChar, 50)
  DocName.Value = fileName.ToString()
  cmd.Parameters.Add(DocName)

  Dim Type As New SqlParameter("@Type", SqlDbType.VarChar, 50)
  Type.Value = documentType.ToString()
  cmd.Parameters.Add(Type)

  Dim uploadedDocument As New SqlParameter("@DocData", SqlDbType.Binary, fileSize)
  uploadedDocument.Value = documentBinary
  cmd.Parameters.Add(uploadedDocument)

  con.Open()
  Dim result As Integer = cmd.ExecuteNonQuery()
  con.Close()
  If result > 0 Then
   lblMessage.Text = "File saved to database"
  End If
  GridView1.DataBind()
 End If
End Sub


To retrieve files from database for download in click of download button we put in gridview, we need to write code mentioned below in RowCommand Event of gridview

c# CODE
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Download")
        {
            string fileName = string.Empty;
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = GridView1.Rows[index];
            int documentID = Convert.ToInt32(GridView1.DataKeys[index].Value);
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT DocName,DocData FROM SaveDoc WHERE DocID = " + documentID, con);
            con.Open();
            SqlDataReader dReader = cmd.ExecuteReader();
            while (dReader.Read())
            {
                fileName = dReader["DocName"].ToString();
                byte[] documentBinary = (byte[])dReader["DocData"];
                FileStream fStream = new FileStream(Server.MapPath("Docs") + @"\" + fileName, FileMode.Create);
                fStream.Write(documentBinary, 0, documentBinary.Length);
                fStream.Close();
                fStream.Dispose();
            }
            con.Close();
            Response.Redirect(@"Docs\" + fileName);
        }
    }

VB.NET CODE
Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs)
 If e.CommandName = "Download" Then
  Dim fileName As String = String.Empty
  Dim index As Integer = Convert.ToInt32(e.CommandArgument)
  Dim row As GridViewRow = GridView1.Rows(index)
  Dim documentID As Integer = Convert.ToInt32(GridView1.DataKeys(index).Value)
  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
  Dim cmd As New SqlCommand("SELECT DocName,DocData FROM SaveDoc WHERE DocID = " & documentID, con)
  con.Open()
  Dim dReader As SqlDataReader = cmd.ExecuteReader()
  While dReader.Read()
   fileName = dReader("DocName").ToString()
   Dim documentBinary As Byte() = DirectCast(dReader("DocData"), Byte())
   Dim fStream As New FileStream(Server.MapPath("Docs") & "\" & fileName, FileMode.Create)
   fStream.Write(documentBinary, 0, documentBinary.Length)
   fStream.Close()
   fStream.Dispose()
  End While
  con.Close()
  Response.Redirect("Docs\" & fileName)
 End If
End Sub

Build and run the application.


Download Sample Code



If you like this post than join us or share

18 comments:

Clarence said...

Thanks, this helped me download an image field into an excel file.


niranjan479 said...

Getting an error:Input string was not in a correct format. for the statement int index = Convert.ToInt32(e.CommandArgument);
please help me!


amiT jaiN said...

@Above: plz check html source of gridview, it should have command property defined for button

asp:ButtonField ButtonType="Image"
ImageUrl="~/download.png"
CommandName="Download"
HeaderText="Download"

btw did you try the source code attached with this article ?


Anonymous said...

How to deny access to Docs folder created in server. Flaw in this code, user can access all files from directory listing.


Anonymous said...

How to save files to computer rather than loading the files in browser...?


amiT jaiN said...

@Above: the code shows Save As dialog, if it's opening files in browser then you can check your browser settings to not open files


Anonymous said...

Hi, I am getting the following error:
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

on:
Dim result As Integer = cmd.ExecuteNonQuery()

Tks!


amiT jaiN said...

@Above: please check datatype of DocData Column of your database table, it should be VarBinary

Dim uploadedDocument As New SqlParameter("@DocData", SqlDbType.Binary, fileSize)
uploadedDocument.Value = documentBinary
cmd.Parameters.Add(uploadedDocument)

I guess it's varchar in your table or you are using wrong column for this data


Anonymous said...

i am getting this type error,i try to change the path "docs" then also it is not help full,or we have to create a new folder of "docs" file in the drive..

ERROR

Could not find a part of the path 'E:\File Download\Docs\images.jpg'.


amiT jaiN said...

@Above : You need to manually create Docs folder in root of your application first


Anonymous said...

i am getting the same problem ,i placed the Docs folder in
E:\File Download\
-App_Data
Database.mdf
-Default.aspx
-Docs

can you provide me the exact location how to place


amiT jaiN said...

@Avove: Create Docs folder in root of your application, which as you mentioned above seems right

There might be problem of path you are using, use

FileStream fStream = new FileStream(Server.MapPath("~\Docs") + @"\" + fileName, FileMode.Create);

And

Response.Redirect(@"~\Docs\" + fileName);

BTW why don't you download the source code attached with this post and try


Asha said...

Hi,
i m getting an error can you please help me

The connection name 'ConnectionString' was not found in the applications configuration or the connection string is empty.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"


amiT jaiN said...

@Asha: please check your connection string name in web.config file and write that instead of ConnectionString


Asha said...

hi Amit,

thank you as you suggested i changed the connection string now its working properly. thanks again:-)


Asha said...

Hi Amit,

Can you suggest me which book is good to learn C# .Net.


amiT jaiN said...

@Asha: Beginning ASP.NET 2.0 with C# (Wrox Publication) is good to start


Anonymous said...

Thanks very much, really appreaciate your help


Find More Articles