In this article, we will discuss,
- Create Table, Insert multiple records with single insert statement, Create Procedure.
- How to Bind GridView using stored procedure.
- How to resolve RegisterforEventValidation can only be called during Render().
- How to Export Gridview data to excel inside Ajax UpdatePanel.
Deasin your DataBase :
Use the Following script to create Mas_Employee table.
- CreateTable Mas_Employee(
- ID IntPrimaryKeyIdentity,
- Name Varchar(50),
- Gender Varchar(50),
- DeptId Int
- )
Insert multiple records with single insert statement,
- InsertInto Mas_Employee(Name,Gender,DeptId)Values
- ('Kishore','Male',1),
- ('Ashwin','Male',1),
- ('Sravan','Male',2),
- ('SumanGupta','Male',2),
- ('Ragavendra','Male',3),
- ('Anil','Male',3),
- ('Suman','Male',4),
- ('Jaipal','Male',4),
- ('Sudha','Male',4),
- ('Chary','Male',4),
- ('Muni Kumar','Male',5),
- ('Karthik','Male',5),
- ('Bhasker','Male',6),
- ('Karthik','Male',6),
- ('Shanmuk','Male',6)
Create a simple stored procedure to get all employee records from Mas_Employee table.
- CreateProc USP_GetAllEmployees
- As
- Begin
- Select Id,Name,Gender,DeptId From Mas_Employee
- End
Application Chamber:
To create the project: - Go to Start, then All Programs and click Microsoft Visual Studio 2010.
- Go to File, New, Project..., Visual C# , Web. Then select ASP.NET Empty Web Application.
- Provide the project a name and specify the location.
Web.Config:
Create the connection string in the Web.Config file as in the following code snippet:
- <connectionStrings>
- <addname="conStr" connectionString="Data Source=.; Database=DB_Jai; Password=1234; User ID=sa;" providerName="System.Data.SqlClient" />
- </connectionStrings>
Next Right-click on Solution Explorer and add a web form to your project.
Webform Design: Write the following code .aspx page.
- <body>
- <formid="form1" runat="server">
- <asp:ScriptManagerID="ScriptManager1" runat="server"> </asp:ScriptManager>
- <asp:UpdatePanelID="up1" runat="server">
- <ContentTemplate>
- <fieldsetstyle="border: 1px dashed #ccc; margin-left: 10px; margin-top: 0px; text-align: right;
- padding-bottom: 10px; width: 20%; text-align: center;">
- <legendstyle="margin-right: 10px;">
- <asp:LabelID="lblLegend" runat="server" Text="Export Gridview Data to Excel"></asp:Label>
- </legend>
- <tablewidth="80%" align="center">
- <tr>
- <td>
- <asp:GridViewID="gvExport" runat="server"> </asp:GridView>
- </td>
- </tr>
- <tr>
- <td>
- <asp:ButtonID="btnExcel" runat="server" Text="Excel" OnClick="btnExcel_Click" </td>
- </tr>
- </table>
- </fieldset>
- </ContentTemplate>
- </asp:UpdatePanel>
- </form>
- </body>
In CodeBehind:
Invoke the ConnectionString from Web.Config as in the following.
- SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
User Defined Functions:
- #region User Defined Methods
-
- privatevoid bindGridview()
- {
- DataSet ds = newDataSet();
- SqlDataAdapter da = newSqlDataAdapter("USP_GetAllEmployees", con);
- da.SelectCommand.CommandType = CommandType.StoredProcedure;
- da.Fill(ds);
- gvExport.DataSource = ds;
- gvExport.DataBind();
- }
-
- protectedvoid ExportToExcel()
- {
- Response.Clear();
- Response.Buffer = true;
- Response.AddHeader("content-disposition", "attachment;filename=Jaipal_GridDataexportDemo.xls");
- Response.Charset = "";
- Response.ContentType = "application/vnd.ms-excel";
- using(StringWriter sw = newStringWriter())
- {
- HtmlTextWriter hw = newHtmlTextWriter(sw);
- gvExport.AllowPaging = false;
- gvExport.RenderControl(hw);
- string style = @ "<style> .textmode { } </style>";
- Response.Write(style);
- Response.Output.Write(sw.ToString());
- Response.Flush();
- Response.End();
- }
- }
- #endregion
Page Event Handlers:
- #region Page Event Handlers
- protectedvoid Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack) bindGridview();
- }
- protectedvoid btnExcel_Click(object sender, EventArgs e)
- {
- ExportToExcel();
- }#endregion
Run the Application by pressing Ctrl+F5, at this point you will get the following error RegisterforEventValidation,
To resolve the above Issue RegisterforEventValidation can only be called during Render() :
Set EnableEventValidation flag to FALSE in the Web.Config in the following way,
- <pagesenableEventValidation="false"></pages>
(Or) you can also set it in the @Page Directive of the page,
- <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="GridDataExport.aspx.cs"
- Inherits="ExportGridviewData.Application.GridDataExport"EnableEventValidation="false"%>
And also override VerifyRenderingInServerForm in code behind,
- publicoverridevoid VerifyRenderingInServerForm(Control control)
- {
- //
- }
Ajax UpdatePanel Issue:
At this point If you run the application by pressing on Ctrl+F5 it doesn't work as expected. It means if you are clicking on the excel button, it doesn’t export the gridview data into any excel file.
To avoid this, add a PostBackTrigger and give it’s ControlID as the excel export button’s ID or you can move your excel export button out side of the update panel as in the following.
- <Triggers>
- <asp:PostBackTriggerControlID="btnEXCEL" />
- </Triggers>
Run the Application:
Just click on Excel button, then we can see the expected result of gridview data in Excel file.
I hope you enjoyed it.