export gridview data to send mail as pdf with attachment in asp.net

export gridview data to send mail as pdf with attachment in asp.net
In this article I will explain export gridview to send mail as pdf with attachment in asp.net

I created a table in Sql server as shown
USE [chinnu]
GO

/****** Object: Table [dbo].[EmployeeInfo] Script Date: 10/29/2013 02:35:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EmployeeInfo](
[EmpId] [nvarchar](50) NOT NULL,
[EmpName] [nvarchar](50) NOT NULL,
[EmpAddress] [nvarchar](50) NOT NULL,
[EmpDesignation] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

I Created a Empty solution with name GridViewMail and For that solution I am creating a webform MailData.aspx

I am designing the UI in MailData.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MailData.aspx.cs" Inherits="GridViewMail.MailData" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gridview" runat="server" AutoGenerateColumns="False" >
<Columns>
<asp:BoundField DataField="EmpId" HeaderText="EmployeeId" InsertVisible="False"
/>
<asp:BoundField DataField="EmpName" HeaderText="EmployeeName" InsertVisible="False"
/>
<asp:BoundField DataField="EmpAddress" HeaderText="EmployeeAddress" />
<asp:BoundField DataField="EmpDesignation" HeaderText="EmployeeDesignation" />
</Columns>
</asp:GridView>
<asp:ImageButton runat = "server" ImageUrl ="~/pdfmail.gif" ToolTip = "Click Here to Send Mail with PDF Format" Width = "70" Height = "55"
onclick="Emailbtn" />
</div>
</form>
</body>
</html>

Now I am writing the logic for sending mail with pdf format with gridview data.
In References we have to add itextsharp DLL
In MailData.aspx.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Net.Mail;
using System.IO;
using System.Text;
using System.Web.UI.HtmlControls;
using iTextSharp.text;
using iTextSharp.text.html.simpleparser;
using iTextSharp.text.pdf;

namespace GridViewMail
{
public partial class MailData : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridData();

}
}
private void BindGridData()
{
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString))
{
SqlCommand command = new SqlCommand("SELECT * from EmployeeInfo", con);
SqlDataAdapter da = new SqlDataAdapter(command);
DataTable dt = new DataTable();
da.Fill(dt);
gridview.DataSource = dt;
gridview.DataBind();
}
}
catch (Exception ex)
{
}

}

protected void Emailbtn(object sender, ImageClickEventArgs e)
{

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gridview.RenderControl(htw);
StringReader sr = new StringReader(sw.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
MemoryStream ms = new MemoryStream();
PdfWriter.GetInstance(pdfDoc, ms);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = ms.ToArray();
MailMessage mailmess = new MailMessage();
mailmess.From = new MailAddress("jlakshmitulasi@gmail.com");
mailmess.To.Add(new MailAddress("jlakshmitulasi@gmail.com"));
mailmess.Subject = "Employee Information";
mailmess.Body = "Please find the attached pdf document ";
mailmess.Attachments.Add(new Attachment(new MemoryStream(bytes),"EmployeeInformation.PDF"));
mailmess.IsBodyHtml = true;
SmtpClient sc = new SmtpClient();
sc.Host = "smtp.gmail.com";
sc.Port = 587;
sc.Credentials = new System.Net.NetworkCredential("username", "password");
sc.EnableSsl = true;
sc.Send(mailmess);



}

In web.config I am adding the ConnectionString

<connectionStrings>
<add name="Connstring" connectionString="Data Source=DotNetSharePoint;Database=Chinnu;User Id=sa;Password=password123;" providerName="System.Data.SqlClient" />

</connectionStrings>

public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}

}
}


Now Press F5,We can see Out Put:


how to upload and download file in database in asp.net

In this article I will explain how to upload and download file in database in asp.net

I Created a table in SQL server as shown

USE [Test]
GO

/****** Object: Table [dbo].[tbl_fileuploads] Script Date: 10/29/2013 04:38:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_fileuploads](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](max) NOT NULL,
[FileData] [varbinary](max) NOT NULL,
CONSTRAINT [PK_tbl_fileuploads] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Now I am creating an empty solution with name DBFiles for that I am adding a webform with name
Files.aspx.
I am designing the UI in Files.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Files.aspx.cs" Inherits="DBFiles.Files" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div >
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="UploadFile" />
<asp:Label ID="lblerr" runat="server" Style="color: Red;"></asp:Label>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" >
<Columns>
<asp:TemplateField HeaderText="S.No">
<ItemTemplate>
<asp:Label ID="lblSRNO" runat="server" Text='<%#Container.DataItemIndex+1 %>'> </asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="FileName" HeaderText="Name"
InsertVisible="False"
ReadOnly="True"
SortExpression="FileName" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" runat="server" Text="Download" OnClick="DownloadFile"
CommandArgument='<%# Eval("ID") %>'></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" Text="Delete" OnClientClick="return confirm('Are you sure to Delete this Document')"
CommandArgument='<%# Eval("ID") %>' runat="server" OnClick="DeleteFile" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>

Now I am writing the logic for uploading,downloading and deleting files from database in Files.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;

namespace DBFiles
{
public partial class Files : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridData();
}
}

private void BindGridData()
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString);
SqlCommand command = new SqlCommand("SELECT ID,FileName,FileData from [tbl_fileuploads]", connection);
SqlDataAdapter daimages = new SqlDataAdapter(command);
DataTable dt = new DataTable();
daimages.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();

}


protected void UploadFile(object sender, EventArgs e)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string documentType = string.Empty;
int fileSize = FileUpload1.PostedFile.ContentLength;
byte[] documentBinary = new byte[fileSize];
FileUpload1.PostedFile.InputStream.Read(documentBinary, 0, fileSize);
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO tbl_fileuploads(FileName,FileData)" + " VALUES (@FileName,@FileData)";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
SqlParameter FileName = new SqlParameter("@FileName", SqlDbType.VarChar, 50);
FileName.Value = fileName.ToString();
cmd.Parameters.Add(FileName);
SqlParameter uploadedDocument = new SqlParameter("@FileData", SqlDbType.Binary, fileSize);
uploadedDocument.Value = documentBinary;
cmd.Parameters.Add(uploadedDocument);
con.Open();
int result = cmd.ExecuteNonQuery();
con.Close();
BindGridData();
}



protected void DownloadFile(object sender, EventArgs e)
{



int id = int.Parse((sender as LinkButton).CommandArgument);
byte[] bytes;
string fileName;

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select FileName,FileData from tbl_fileuploads where ID=@id";
cmd.Parameters.AddWithValue("@ID", id);
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
bytes = (byte[])sdr["FileData"];

fileName = sdr["FileName"].ToString();
}
con.Close();
}
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}

protected void DeleteFile(object sender, EventArgs e)
{



SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Connstring"].ConnectionString);
int id = int.Parse((sender as LinkButton).CommandArgument);
SqlCommand cmd = new SqlCommand("delete from tbl_fileuploads where id = @Id", con);
cmd.Parameters.AddWithValue("@Id", id);
cmd.CommandType = CommandType.Text;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindGridData();


}

}
}


In Web.Config I am adding the connectionstring
<connectionStrings>
<add name="Connstring" connectionString="Data Source=DotNetSharePoint;Database=Test;User Id=sa;Password=password123;" providerName="System.Data.SqlClient" />
</connectionStrings>


Press F5,Now we can see the OutPut as .