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();
  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();
  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. {
  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;
  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();
  22. }
  23. protected void Page_Load(object sender, EventArgs e)
  24. {
  25. if (!IsPostBack)
  26. {
  27. Bindgrid();
  29. }
  30. }
  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. }
  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();
  48. }
  49. protected void Button1_Click(object sender, EventArgs e)
  50. {
  51. ExportGridToAccess();
  52. }
  53. private void ExportGridToAccess()
  54. {
  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.