Post
Ask Question

Export GridView to Access Using ASP.Net C#

Background

There is often a need in a project's reporting module to export a GridView to Access. So by considering that requirement I decided to write this article especially focusing on beginners and those who want to learn how to export a GridView to Access Using ASP.NET C# and using the itextsharp DLL.

 Prerequisites
  • To export the Grid view, we need to use a reference of itextsharp.dll.
  • Download the itextsharp.dll from the internet.

The itextsharp.dll is the free DLL available for download that provides some methods to export a Grid view into an Access file. After adding the reference, use the following reference of itextsharp.dll:

  1. using iTextSharp.text;  
  2. using iTextSharp.text.pdf;  
  3. using iTextSharp.text.html.simpleparser; 
Now before creating the application, let us create a table named employee in a database from where we show the records in a Grid view, the table has the following fields (shown in the following image):
 
 
I hope you have created the same type of table.
Now create the project as:
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New Project" - "C#" - "Empty Project" (to avoid adding a master page).
  3. Provide the Project name such as ExportGridToAccess or another as you wish and specify the location.
  4. Then right-click on Solution Explorer - "Add New Item" - Default.aspx page.
  5. One Button, one label, and a gridview.

Now let us create a function to bind the records to the Grid view from the database. If you are a beginner and don't know in detail how to bind a Grid view from a database then refer to my following article.

Now, for this article create the following function in the default.aspx.cs page to bind the Grid view:
  1. private void Bindgrid()  
  2. {  
  3.     connection();  
  4.     query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security  
  5.     com = new SqlCommand(query, con);  
  6.     SqlDataReader dr = com.ExecuteReader();  
  7.     GridView1.DataSource = dr;  
  8.     GridView1.DataBind();  
  9.     con.Close();  
  10.   
  11. } 
Now, call the above function on page load as:
  1. protected void Page_Load(object sender, EventArgs e)  
  2. {  
  3.     if (!IsPostBack)  
  4.     {  
  5.         Bindgrid();  
  6.   
  7.     }  
  8. } 
Now run the application and then we can see the following records in the Grid view:
 
 
 
We now have records to export into Access. Let us start coding for our actual requirements. Add the VerifyRenderingInServerForm event after the page load that is required when exporting a Grid view to Excel, Word and PDF to avoid the run time error "GridView' must be placed inside a form tag with runat=server."
  1. public override void VerifyRenderingInServerForm(Control control)  
  2. {  
  3.     //required to avoid the runtime error "  
  4.     //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."  
  5. } 
Now before creating the function to Export the grid view to Access add the reference of itextsharp.dll by right-clicking the Solution Explorer. After adding the reference the Solution Explorer will look as in the following:
 
 
Now create the following function to export the Grid view to Access:
  1. private void ExportGridToAccess()  
  2. {  
  3.   
  4.     Response.ContentType = "application/ms-access";  
  5.     Response.AddHeader("content-disposition", "attachment; filename=Vithal_Wadje.mdb");   
  6.     Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  7.     StringWriter sw = new StringWriter();  
  8.     HtmlTextWriter hw = new HtmlTextWriter(sw);  
  9.     GridView1.RenderControl(hw);  
  10.     StringReader sr = new StringReader(sw.ToString());  
  11.     Document AccessDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);  
  12.     HTMLWorker htmlparser = new HTMLWorker(AccessDoc);  
  13.     PdfWriter.GetInstance(AccessDoc, Response.OutputStream);  
  14.     AccessDoc.Open();  
  15.     htmlparser.Parse(sr);  
  16.     AccessDoc.Close();  
  17.     Response.Write(AccessDoc);  
  18.     Response.End();  
  19.     GridView1.AllowPaging = true;  
  20.     GridView1.DataBind();  
  21. } 
Now double-click on the Export button and call the preceding function on "onclick" as in the following:
  1. protected void Button1_Click(object sender, EventArgs e)  
  2. {  
  3.     ExportGridToAccess();  
  4. } 
Now the entire code of the Default.aspx.cs page will be as follows:
  1. using System;  
  2. using System.Web.UI;  
  3. using System.Configuration;  
  4. using System.Data.SqlClient;  
  5. using System.IO;  
  6. using System.Web;  
  7. using iTextSharp.text;  
  8. using iTextSharp.text.pdf;  
  9. using iTextSharp.text.html.simpleparser;  
  10.   
  11. public partial class _Default : System.Web.UI.Page  
  12. {  
  13.     private SqlConnection con;  
  14.     private SqlCommand com;  
  15.     private string constr, query;  
  16.     private void connection()  
  17.     {  
  18.         constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();  
  19.         con = new SqlConnection(constr);  
  20.         con.Open();  
  21.   
  22.     }  
  23.     protected void Page_Load(object sender, EventArgs e)  
  24.     {  
  25.         if (!IsPostBack)  
  26.         {  
  27.             Bindgrid();  
  28.   
  29.         }  
  30.     }  
  31.   
  32.     public override void VerifyRenderingInServerForm(Control control)  
  33.     {  
  34.         //required to avoid the runtime error "  
  35.         //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."  
  36.     }  
  37.   
  38.     private void Bindgrid()  
  39.     {  
  40.         connection();  
  41.         query = "select *from Employee";//not recommended this i have written just for example,write stored procedure for security  
  42.         com = new SqlCommand(query, con);  
  43.         SqlDataReader dr = com.ExecuteReader();  
  44.         GridView1.DataSource = dr;  
  45.         GridView1.DataBind();  
  46.         con.Close();  
  47.   
  48.     }  
  49.     protected void Button1_Click(object sender, EventArgs e)  
  50.     {  
  51.         ExportGridToAccess();  
  52.     }  
  53.     private void ExportGridToAccess()  
  54.     {  
  55.   
  56.         Response.ContentType = "application/ms-access";  
  57.         Response.AddHeader("content-disposition", "attachment; filename=Vithal_Wadje.mdb");   
  58.         Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  59.         StringWriter sw = new StringWriter();  
  60.         HtmlTextWriter hw = new HtmlTextWriter(sw);  
  61.         GridView1.RenderControl(hw);  
  62.         StringReader sr = new StringReader(sw.ToString());  
  63.         Document AccessDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);  
  64.         HTMLWorker htmlparser = new HTMLWorker(AccessDoc);  
  65.         PdfWriter.GetInstance(AccessDoc, Response.OutputStream);  
  66.         AccessDoc.Open();  
  67.         htmlparser.Parse(sr);  
  68.         AccessDoc.Close();  
  69.         Response.Write(AccessDoc);  
  70.         Response.End();  
  71.         GridView1.AllowPaging = true;  
  72.         GridView1.DataBind();  
  73.     }  
  74. } 
Now run the application and click on the Export Button. The following popup is shown:
 
 
 
Now click and save the preceding file and open it with Excel by establishing the connection or you can open this file with the tool mdb file viewer. 
 
Notes
  • Download the Zip file from the attachment for the full source code of the application.
  • Change the connection string in the web.config file to specify your server location.

Summary

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.


Similar Articles