ASP.NET GridView control demo
Introduction
This CSASPNETGridView project describes how to populate ASP.NET GridView control and how to implement Insert, Edit,Update, Delete, Paging and Sorting functions in ASP.NET GridView control. We have received many posts in forums about this popular web control, so this sample provides a complete sample for showing how to implement these basic functions of this control. The sample demonstrates data source from both database and memory.
Building the Sample
For this sample to work, you must install the SqlServer 2008 R2 Express. This sample contains a SqlServer database file, if you do not install SqlServer, The DataInMemory.aspx page can also works fine. More information about SqlServer 2008 R2 Express and download links can be found here:
Running the Sample
Please follow these demonstration steps below.
Step 1: Open the CSASPNETGridView.sln. Expand the CSASPNETGridView web application and press Ctrl + F5 to show the DataFromDatabase.aspx.
Step 2: We will see a GirdView control on the page, you can add, edit, delete the columns of the GridView control, the data is come from App_Data/GridView.mdf file, and the GridView's status is stored in ViewState for persisting data across postbacks.
Step 3: The GridView the page size is 15, you need insert 16 Persons in this GridView to see the next page. Please click the title of the GridView to sort the result by PersonID, LastName or FirstName properties.
Step 4: Please press Ctrl+F5 to show DataInMemory.aspx page, the test steps just like DataFromDataBase.aspx.
Step 5: Validation finished.
Using the Code
Code Logical:
Step 1. Create a C# "ASP.NET Empty Web Application" in Visual Studio 2010 or Visual Web Developer 2010. Name it as "CSASPNETGridView ". The project includes two web form pages for demonstrating two ways to bind data source with the GridView, name them as "DataFromDataBase.aspx", "DataInMemory.aspx".
Step 2. Before we start to write code, we need install SqlServer 2008 R2 Express and create a database file as the data source of GridView control. Add an Asp.net folder "App_Data" and create a Sql Server Database,"GridView.mdf". Add "Person" table with three fields "PersonID","FirstName","LastName", PersonID is the primary key of the table, and you can insert some default values in Person table.
Step 3. Drag and drop a GridView control, two LinkButton controls, two TextBox controls and a Panel control into DataFromDataBase.aspx page. The GridView is used to display, edit and delete the data of database file, the TextBox and LinkButton are used to insert new items to the data table. In the first step, check your controls and rename them and set some basic properties of the GridView, such as GridView's templates and events.
The following Html code is showing the GridView's necessary events (onpageindexchanging, onrowcancelingedit, onrowdatabound, etc), GridView's TemplateField and other controls:
HTML
<asp:GridView ID="gvPerson" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" onpageindexchanging="gvPerson_PageIndexChanging" onrowcancelingedit="gvPerson_RowCancelingEdit" onrowdatabound="gvPerson_RowDataBound" onrowdeleting="gvPerson_RowDeleting" onrowediting="gvPerson_RowEditing" onrowupdating="gvPerson_RowUpdating" onsorting="gvPerson_Sorting"> <RowStyle BackColor="White" ForeColor="#003399" /> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:CommandField ShowDeleteButton="True" /> <asp:BoundField DataField="PersonID" HeaderText="PersonID" ReadOnly="True" SortExpression="PersonID" /> <asp:TemplateField HeaderText="LastName" SortExpression="LastName"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("LastName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FirstName" SortExpression="FirstName"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <FooterStyle BackColor="#99CCCC" ForeColor="#003399" /> <PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" /> <SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" /> <HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" /> </asp:GridView> <asp:LinkButton ID="lbtnAdd" runat="server" onclick="lbtnAdd_Click">AddNew</asp:LinkButton> <asp:Panel ID="pnlAdd" runat="server" Visible="False"> Last name: <asp:TextBox ID="tbLastName" runat="server"></asp:TextBox> First name: <asp:TextBox ID="tbFirstName" runat="server"></asp:TextBox> <asp:LinkButton ID="lbtnSubmit" runat="server" onclick="lbtnSubmit_Click">Submit</asp:LinkButton> <asp:LinkButton ID="lbtnCancel" runat="server" onclick="lbtnCancel_Click">Cancel</asp:LinkButton> </asp:Panel>
Step 4. Copy the Page_Load and BindGridView methods of the sample and paste them to your DataFromDataBase.aspx.cs file, and navigator to the Property panel and switch to Event. Double click on the following event and generate the Event Handlers, after that, fill the generated methods with the sample code.
The following code is used to implement the basic functions of the GridView control.
C#
protected void Page_Load(object sender, EventArgs e) { // The Page is accessed for the first time. if (!IsPostBack) { // Enable the GridView paging option and // specify the page size. gvPerson.AllowPaging = true; gvPerson.PageSize = 15; // Enable the GridView sorting option. gvPerson.AllowSorting = true; // Initialize the sorting expression. ViewState["SortExpression"] = "PersonID ASC"; // Populate the GridView. BindGridView(); } } private void BindGridView() { // Get the connection string from Web.config. // When we use Using statement, // we don't need to explicitly dispose the object in the code, // the using statement takes care of it. using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString())) { // Create a DataSet object. DataSet dsPerson = new DataSet(); // Create a SELECT query. string strSelectCmd = "SELECT PersonID,LastName,FirstName FROM Person"; // Create a SqlDataAdapter object // SqlDataAdapter represents a set of data commands and a // database connection that are used to fill the DataSet and // update a SQL Server database. SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn); // Open the connection conn.Open(); // Fill the DataTable named "Person" in DataSet with the rows // returned by the query.new n da.Fill(dsPerson, "Person"); // Get the DataView from Person DataTable. DataView dvPerson = dsPerson.Tables["Person"].DefaultView; // Set the sort column and sort order. dvPerson.Sort = ViewState["SortExpression"].ToString(); // Bind the GridView control. gvPerson.DataSource = dvPerson; gvPerson.DataBind(); } } // GridView.RowDataBound Event protected void gvPerson_RowDataBound(object sender, GridViewRowEventArgs e) { // Make sure the current GridViewRow is a data row. if (e.Row.RowType == DataControlRowType.DataRow) { // Make sure the current GridViewRow is either // in the normal state or an alternate row. if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate) { // Add client-side confirmation when deleting. ((LinkButton)e.Row.Cells[1].Controls[0]).Attributes["onclick"] = "if(!confirm('Are you certain you want to delete this person ?')) return false;"; } } } // GridView.PageIndexChanging Event protected void gvPerson_PageIndexChanging(object sender, GridViewPageEventArgs e) { // Set the index of the new display page. gvPerson.PageIndex = e.NewPageIndex; // Rebind the GridView control to // show data in the new page. BindGridView(); } // GridView.RowEditing Event protected void gvPerson_RowEditing(object sender, GridViewEditEventArgs e) { // Make the GridView control into edit mode // for the selected row. gvPerson.EditIndex = e.NewEditIndex; // Rebind the GridView control to show data in edit mode. BindGridView(); // Hide the Add button. lbtnAdd.Visible = false; } // GridView.RowCancelingEdit Event protected void gvPerson_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { // Exit edit mode. gvPerson.EditIndex = -1; // Rebind the GridView control to show data in view mode. BindGridView(); // Show the Add button. lbtnAdd.Visible = true; } // GridView.RowUpdating Event protected void gvPerson_RowUpdating(object sender, GridViewUpdateEventArgs e) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString())) { // Create a command object. SqlCommand cmd = new SqlCommand(); // Assign the connection to the command. cmd.Connection = conn; // Set the command text // SQL statement or the name of the stored procedure cmd.CommandText = "UPDATE Person SET LastName = @LastName, FirstName = @FirstName WHERE PersonID = @PersonID"; // Set the command type // CommandType.Text for ordinary SQL statements; // CommandType.StoredProcedure for stored procedures. cmd.CommandType = CommandType.Text; // Get the PersonID of the selected row. string strPersonID = gvPerson.Rows[e.RowIndex].Cells[2].Text; string strLastName = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox1")).Text; string strFirstName = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox2")).Text; // Append the parameters. cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = strPersonID; cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = strLastName; cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = strFirstName; // Open the connection. conn.Open(); // Execute the command. cmd.ExecuteNonQuery(); } // Exit edit mode. gvPerson.EditIndex = -1; // Rebind the GridView control to show data after updating. BindGridView(); // Show the Add button. lbtnAdd.Visible = true; } // GridView.RowDeleting Event protected void gvPerson_RowDeleting(object sender, GridViewDeleteEventArgs e) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString())) { // Create a command object. SqlCommand cmd = new SqlCommand(); // Assign the connection to the command. cmd.Connection = conn; // Set the command text // SQL statement or the name of the stored procedure cmd.CommandText = "DELETE FROM Person WHERE PersonID = @PersonID"; // Set the command type // CommandType.Text for ordinary SQL statements; // CommandType.StoredProcedure for stored procedures. cmd.CommandType = CommandType.Text; // Get the PersonID of the selected row. string strPersonID = gvPerson.Rows[e.RowIndex].Cells[2].Text; // Append the parameter. cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value = strPersonID; // Open the connection. conn.Open(); // Execute the command. cmd.ExecuteNonQuery(); } // Rebind the GridView control to show data after deleting. BindGridView(); } // GridView.Sorting Event protected void gvPerson_Sorting(object sender, GridViewSortEventArgs e) { string[] strSortExpression = ViewState["SortExpression"].ToString().Split(' '); // If the sorting column is the same as the previous one, // then change the sort order. if (strSortExpression[0] == e.SortExpression) { if (strSortExpression[1] == "ASC") { ViewState["SortExpression"] = e.SortExpression + " " + "DESC"; } else { ViewState["SortExpression"] = e.SortExpression + " " + "ASC"; } } // If sorting column is another column, // then specify the sort order to "Ascending". else { ViewState["SortExpression"] = e.SortExpression + " " + "ASC"; } // Rebind the GridView control to show sorted data. BindGridView(); }
Step 5. Double click on the Click event of LinkButton control to generate the event handler and fill the generated methods with the sample, these two button are used to add new items to the database file and cancel the insert operate.
The following code shows how to insert new items to the database file.
C#
protected void lbtnAdd_Click(object sender, EventArgs e) { // Hide the Add button and showing Add panel. lbtnAdd.Visible = false; pnlAdd.Visible = true; } protected void lbtnSubmit_Click(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ToString())) { // Create a command object. SqlCommand cmd = new SqlCommand(); // Assign the connection to the command. cmd.Connection = conn; // Set the command text // SQL statement or the name of the stored procedure cmd.CommandText = "INSERT INTO Person ( LastName, FirstName ) VALUES ( @LastName, @FirstName )"; // Set the command type // CommandType.Text for ordinary SQL statements; // CommandType.StoredProcedure for stored procedures. cmd.CommandType = CommandType.Text; // Append the parameters. cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = tbLastName.Text; cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = tbFirstName.Text; // Open the connection. conn.Open(); // Execute the command. cmd.ExecuteNonQuery(); } // Rebind the GridView control to show inserted data. BindGridView(); // Empty the TextBox controls. tbLastName.Text = ""; tbFirstName.Text = ""; // Show the Add button and hiding the Add panel. lbtnAdd.Visible = true; pnlAdd.Visible = false; } protected void lbtnCancel_Click(object sender, EventArgs e) { // Empty the TextBox controls. tbLastName.Text = ""; tbFirstName.Text = ""; // Show the Add button and hiding the Add panel. lbtnAdd.Visible = true; pnlAdd.Visible = false; }
Step 6. The DataInMemory.aspx page is pretty much the same with DataFromDataBase.aspx page, this web page get data from memory, instead of database file. So we only need to add a new method "InitializeDataSource" for generating the DataTable variable, then we need to modify the BindGridView method to bind new the DataTable with GridView.
The following code is use initialize the DataTable and stores it in ViewState.
C#
// Initialize the DataTable. private void InitializeDataSource() { // Create a DataTable object named dtPerson. DataTable dtPerson = new DataTable(); // Add four columns to the DataTable. dtPerson.Columns.Add("PersonID"); dtPerson.Columns.Add("LastName"); dtPerson.Columns.Add("FirstName"); // Specify PersonID column as an auto increment column // and set the starting value and increment. dtPerson.Columns["PersonID"].AutoIncrement = true; dtPerson.Columns["PersonID"].AutoIncrementSeed = 1; dtPerson.Columns["PersonID"].AutoIncrementStep = 1; // Set PersonID column as the primary key. DataColumn[] dcKeys = new DataColumn[1]; dcKeys[0] = dtPerson.Columns["PersonID"]; dtPerson.PrimaryKey = dcKeys; // Add new rows into the DataTable. dtPerson.Rows.Add(null, "Davolio", "Nancy"); dtPerson.Rows.Add(null, "Fuller", "Andrew"); dtPerson.Rows.Add(null, "Leverling", "Janet"); dtPerson.Rows.Add(null, "Dodsworth", "Anne"); dtPerson.Rows.Add(null, "Buchanan", "Steven"); dtPerson.Rows.Add(null, "Suyama", "Michael"); dtPerson.Rows.Add(null, "Callahan", "Laura"); // Store the DataTable in ViewState. ViewState["dtPerson"] = dtPerson; } private void BindGridView() { if (ViewState["dtPerson"] != null) { // Get the DataTable from ViewState. DataTable dtPerson = (DataTable)ViewState["dtPerson"]; // Convert the DataTable to DataView. DataView dvPerson = new DataView(dtPerson); // Set the sort column and sort order. dvPerson.Sort = ViewState["SortExpression"].ToString(); // Bind the GridView control. gvPerson.DataSource = dvPerson; gvPerson.DataBind(); } }
Step 7. Build the application and you can debug it.
No comments:
Post a Comment