SQL: Insert .pdf File to Database and Download form Database

<div id="divSetupDownload" runat="server" align="center">
<div style="font-family: Tahoma; font-size: 16px; color: Maroon; font-weight: bold;"
align="center">
Manage Download</div>
<div>
<asp:Label ID="lblMessageDownload" runat="server" ForeColor="Red"></asp:Label>
</div>
<br />
<fieldset style="border: 1px solid #cccccc">
<div>
<table>
<tr>
<td align="left">
Select File
</td>
<td>
:
</td>
<td>
<asp:FileUpload ID="fuPDFfile" runat="server" />
</td>
</tr>
<tr>
<td valign="top" align="left">
Description
</td>
<td valign="top">
:
</td>
<td valign="top">
<asp:TextBox ID="txtPdfDescription" runat="server" SkinID="TextArea" TextMode="MultiLine"></asp:TextBox>
<ajaxToolkit:HtmlEditorExtender ID="htmlEditorExtender1" EnableSanitization="false"
TargetControlID="txtPdfDescription" runat="server" />
</td>
</tr>
<tr>
<td colspan="3" align="right">
<br />
<asp:Button ID="btnSetupDownload" SkinID="btnMid" runat="server" Text="Submit" OnClick="btnSetupDownload_Click" />
</td>
</tr>
</table>
</div>
<br />
<div style="font-family: Tahoma; font-size: 12px;">
<asp:GridView ID="gvwDownloadList" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderStyle="None" ForeColor="Black" Width="450px">
<Columns>
<asp:TemplateField HeaderText="Id">
<ItemTemplate>
<div>
<asp:Label ID="lblId" Text='<% #Eval("Id") %>' runat="server" />
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<div>
<asp:TextBox ID="txtDescription" runat="server" Text='<% #Eval("Description") %>' />
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FileName">
<ItemStyle Width="150" />
<HeaderStyle Width="150" />
<ItemTemplate>
<div>
<asp:Label ID="lblFileName" Width="150" runat="server" Text='<% #Eval("FileName") %>' />
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Extension">
<ItemTemplate>
<div>
<asp:Label ID="lblExtension" runat="server" Text='<% #Eval("Extension") %>' />
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Download">
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
<asp:ImageButton ID="ibDownload" ImageUrl="~/download.png" runat="server" OnClick="ibDownload_Click" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edit">
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
<asp:Button ID="btnEditDownload" SkinID="btnSmall" runat="server" Text="Edit" OnClick="btnEditDownload_Click" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:Button ID="btnDeleteDownload" SkinID="btnSmall" runat="server" Text="Delete"
OnClick="btnDeleteDownload_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</fieldset>
</div>

#region Download
protected void btnSetupDownload_Click(object sender, EventArgs e)
{
//Check whether FileUpload control has file
if (fuPDFfile.HasFile)
{
string fileName = Path.GetFileName(fuPDFfile.PostedFile.FileName);
string fileExtension = Path.GetExtension(fuPDFfile.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 = fuPDFfile.PostedFile.ContentLength;
//Create array and read the file into it
byte[] documentBinary = new byte[fileSize];
fuPDFfile.PostedFile.InputStream.Read(documentBinary, 0, fileSize);
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SelfAssessmentConnStr"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO T_DownloadFile(FileName,Extension,Content,Description) VALUES(@FileName,@Extension,@Content,@Description)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@FileName", fileName));
cmd.Parameters.Add(new SqlParameter("@Extension", fileExtension));
cmd.Parameters.Add(new SqlParameter("@Content", documentBinary));
cmd.Parameters.Add(new SqlParameter("@Description", txtPdfDescription.Text.ToString()));
int result = cmd.ExecuteNonQuery();
con.Close();
if (result > 0)
lblMessageDownload.Text = "File has been saved to database";
}
}
catch (Exception ex) { }
}
DataTable dt = ClsCommon.GetAdhocResult("SELECT * FROM T_DownloadFile").Tables[0];
gvwDownloadList.DataSource = dt;
gvwDownloadList.DataBind();
}
protected void btnEditDownload_Click(object sender, EventArgs e)
{
GridViewRow row = ((Button)sender).Parent.Parent as GridViewRow;
Int32 index = row.RowIndex;
String DocumentId = ((Label)gvwDownloadList.Rows[index].FindControl("lblId")).Text;
String Description = ((TextBox)gvwDownloadList.Rows[index].FindControl("txtDescription")).Text;
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SelfAssessmentConnStr"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("UPDATE T_DownloadFile SET Description=@Description WHERE Id=@Id", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@Description", Description.ToString()));
cmd.Parameters.Add(new SqlParameter("@Id", DocumentId.Trim().ToString()));
int result = cmd.ExecuteNonQuery();
con.Close();
if (result > 0)
lblMessageDownload.Text = "Description Edited Successfully";
}
}
catch (Exception ex) { }
DataTable dt = ClsCommon.GetAdhocResult("SELECT * FROM T_DownloadFile").Tables[0];
gvwDownloadList.DataSource = dt;
gvwDownloadList.DataBind();
}
protected void btnDeleteDownload_Click(object sender, EventArgs e)
{
GridViewRow row = ((Button)sender).Parent.Parent as GridViewRow;
Int32 index = row.RowIndex;
String DocumentId = ((Label)gvwDownloadList.Rows[index].FindControl("lblId")).Text;
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SelfAssessmentConnStr"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("DELETE FROM T_DownloadFile WHERE Id=@Id", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@Id", DocumentId.Trim().ToString()));
int result = cmd.ExecuteNonQuery();
con.Close();
if (result > 0)
lblMessageDownload.Text = "File has been Deleted Successsfully from database";
}
}
catch (Exception ex) { }
DataTable dt = ClsCommon.GetAdhocResult("SELECT * FROM T_DownloadFile").Tables[0];
gvwDownloadList.DataSource = dt;
gvwDownloadList.DataBind();
}
protected void ibDownload_Click(object sender, ImageClickEventArgs e)
{
string fileName = string.Empty;
GridViewRow row = ((ImageButton)sender).Parent.Parent as GridViewRow;
Int32 index = row.RowIndex;
String DocumentId = ((Label)gvwDownloadList.Rows[index].FindControl("lblId")).Text;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SelfAssessmentConnStr"].ConnectionString);
SqlCommand cmd = new SqlCommand("SELECT FileName,[Content] FROM T_DownloadFile WHERE Id = " + DocumentId, con);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
while (dReader.Read())
{
fileName = dReader["FileName"].ToString();
byte[] documentBinary = (byte[])dReader["Content"];
FileStream fStream = new FileStream(Server.MapPath("pdf") + @"\" + fileName, FileMode.Create);
fStream.Write(documentBinary, 0, documentBinary.Length);
fStream.Close();
fStream.Dispose();
}
con.Close();
Response.Redirect(@"pdf\" + fileName);
}
#endregion

<td style="height: 227px;">
<asp:Panel ID="pnlCtMarksEntry" runat="server" ScrollBars="Vertical" Height="220px">
<div style="font-family: Tahoma; font-size: 12px;">
<asp:GridView ID="gvwDownloadList" runat="server" AutoGenerateColumns="False" BorderStyle="None"
ForeColor="Black" GridLines="None" ShowHeader="False" Width="220px" BackColor="#D0E1DC">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<div>
<asp:Label ID="lblDescription" runat="server" Text='<% #Eval("Description") %>' />
<asp:Label ID="lblId" Text='<% #Eval("Id") %>' Visible="false" runat="server" />
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:ImageButton ID="ibDownload" ImageUrl="~/images/120px-Download.png" Width="30px"
runat="server" OnClick="ibDownload_Click" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</asp:Panel>
</td>


//// Download area
DataTable dt = ClsCommon.GetAdhocResult("SELECT * FROM T_DownloadFile").Tables[0];
gvwDownloadList.DataSource = dt;
gvwDownloadList.DataBind();

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s