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 .

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

01using System.IO;

02using System.Data;

03using System.Data.SqlClient;

04using System.Configuration;

05 

06protected void btnUpload_Click(object sender, EventArgs e)

07    {

08        //Check whether FileUpload control has file

09        if (FileUpload1.HasFile)

10        {

11            string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

12            string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);

13            string documentType = string.Empty;

14 

15            //provide document type based on it's extension

16            switch (fileExtension)

17            {

18                case ".pdf":

19                    documentType = "application/pdf";

20                    break;

21                case ".xls":

22                    documentType = "application/vnd.ms-excel";

23                    break;

24                case ".xlsx":

25                    documentType = "application/vnd.ms-excel";

26                    break;

27                case ".doc":

28                    documentType = "application/vnd.ms-word";

29                    break;

30                case ".docx":

31                    documentType = "application/vnd.ms-word";

32                    break;

33                case ".gif":

34                    documentType = "image/gif";

35                    break;

36                case ".png":

37                    documentType = "image/png";

38                    break;

39                case ".jpg":

40                    documentType = "image/jpg";

41                    break;

42            }

43 

44            //Calculate size of file to be uploaded

45            int fileSize = FileUpload1.PostedFile.ContentLength;

46 

47            //Create array and read the file into it

48            byte[] documentBinary = new byte[fileSize];

49            FileUpload1.PostedFile.InputStream.Read(documentBinary, 0, fileSize);

50 

51            // Create SQL Connection

52            SqlConnection con = new SqlConnection();

53            con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

54 

55            // Create SQL Command and Sql Parameters

56            SqlCommand cmd = new SqlCommand();

57            cmd.CommandText = "INSERT INTO SaveDoc(DocName,Type,DocData)" +

58                              " VALUES (@DocName,@Type,@DocData)";

59            cmd.CommandType = CommandType.Text;

60            cmd.Connection = con;

61 

62            SqlParameter DocName = new SqlParameter("@DocName", SqlDbType.VarChar, 50);

63            DocName.Value = fileName.ToString();

64            cmd.Parameters.Add(DocName);

65 

66            SqlParameter Type = new SqlParameter("@Type", SqlDbType.VarChar, 50);

67            Type.Value = documentType.ToString();

68            cmd.Parameters.Add(Type);

69 

70            SqlParameter uploadedDocument = new SqlParameter("@DocData", SqlDbType.Binary,fileSize);

71            uploadedDocument.Value = documentBinary;

72            cmd.Parameters.Add(uploadedDocument);

73 

74            con.Open();

75            int result = cmd.ExecuteNonQuery();

76            con.Close();

77            if (result > 0)

78                lblMessage.Text = "File saved to database";

79            GridView1.DataBind();

80        }

81    }

 

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

01protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

02    {

03        if (e.CommandName == "Download")

04        {

05            string fileName = string.Empty;

06            int index = Convert.ToInt32(e.CommandArgument);

07            GridViewRow row = GridView1.Rows[index];

08            int documentID = Convert.ToInt32(GridView1.DataKeys[index].Value);

09            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

10            SqlCommand cmd = new SqlCommand("SELECT DocName,DocData FROM SaveDoc WHERE DocID = " + documentID, con);

11            con.Open();

12            SqlDataReader dReader = cmd.ExecuteReader();

13            while (dReader.Read())

14            {

15                fileName = dReader["DocName"].ToString();

16                byte[] documentBinary = (byte[])dReader["DocData"];

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

18                fStream.Write(documentBinary, 0, documentBinary.Length);

19                fStream.Close();

20                fStream.Dispose();

21            }

22            con.Close();

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

24        }

25    }