Save Store Files In SqlServer Database AspNet

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

show source

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 }

VB.NET CODE

show source

01Protected Sub btnUpload_Click(sender As Object, e As EventArgs)

02 'Check whether FileUpload control has file

03 If FileUpload1.HasFile Then

04 Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

05 Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

06 Dim documentType As String = String.Empty

07

08 'provide document type based on it's extension

09 Select Case fileExtension

10 Case ".pdf"

11 documentType = "application/pdf"

12 Exit Select

13 Case ".xls"

14 documentType = "application/vnd.ms-excel"

15 Exit Select

16 Case ".xlsx"

17 documentType = "application/vnd.ms-excel"

18 Exit Select

19 Case ".doc"

20 documentType = "application/vnd.ms-word"

21 Exit Select

22 Case ".docx"

23 documentType = "application/vnd.ms-word"

24 Exit Select

25 Case ".gif"

26 documentType = "image/gif"

27 Exit Select

28 Case ".png"

29 documentType = "image/png"

30 Exit Select

31 Case ".jpg"

32 documentType = "image/jpg"

33 Exit Select

34 End Select

35

36 'Calculate size of file to be uploaded

37 Dim fileSize As Integer = FileUpload1.PostedFile.ContentLength

38

39 'Create array and read the file into it

40 Dim documentBinary As Byte() = New Byte(fileSize - 1) {}

41 FileUpload1.PostedFile.InputStream.Read(documentBinary, 0, fileSize)

42

43 ' Create SQL Connection

44 Dim con As New SqlConnection()

45 con.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

46

47 ' Create SQL Command and Sql Parameters

48 Dim cmd As New SqlCommand()

49 cmd.CommandText = "INSERT INTO SaveDoc(DocName,Type,DocData)" & " VALUES (@DocName,@Type,@DocData)"

50 cmd.CommandType = CommandType.Text

51 cmd.Connection = con

52

53 Dim DocName As New SqlParameter("@DocName", SqlDbType.VarChar, 50)

54 DocName.Value = fileName.ToString()

55 cmd.Parameters.Add(DocName)

56

57 Dim Type As New SqlParameter("@Type", SqlDbType.VarChar, 50)

58 Type.Value = documentType.ToString()

59 cmd.Parameters.Add(Type)

60

61 Dim uploadedDocument As New SqlParameter("@DocData", SqlDbType.Binary, fileSize)

62 uploadedDocument.Value = documentBinary

63 cmd.Parameters.Add(uploadedDocument)

64

65 con.Open()

66 Dim result As Integer = cmd.ExecuteNonQuery()

67 con.Close()

68 If result > 0 Then

69 lblMessage.Text = "File saved to database"

70 End If

71 GridView1.DataBind()

72 End If

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

show source

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 }

VB.NET CODE

show source

01Protected Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs)

02 If e.CommandName = "Download" Then

03 Dim fileName As String = String.Empty

04 Dim index As Integer = Convert.ToInt32(e.CommandArgument)

05 Dim row As GridViewRow = GridView1.Rows(index)

06 Dim documentID As Integer = Convert.ToInt32(GridView1.DataKeys(index).Value)

07 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

08 Dim cmd As New SqlCommand("SELECT DocName,DocData FROM SaveDoc WHERE DocID = " & documentID, con)

09 con.Open()

10 Dim dReader As SqlDataReader = cmd.ExecuteReader()

11 While dReader.Read()

12 fileName = dReader("DocName").ToString()

13 Dim documentBinary As Byte() = DirectCast(dReader("DocData"), Byte())

14 Dim fStream As New FileStream(Server.MapPath("Docs") & "\" & fileName, FileMode.Create)

15 fStream.Write(documentBinary, 0, documentBinary.Length)

16 fStream.Close()

17 fStream.Dispose()

18 End While

19 con.Close()

20 Response.Redirect("Docs\" & fileName)

21 End If

22End Sub