How to edit, delete, classify, and pagination in the DataGrid Control

This article Article This section describes how to edit, delete, classify, and pagination in the DataGrid Control. To achieve our intention, we use the northwind database that comes with sqlserver2000. Program There are two parts:
1. contains HTML Code . Aspx File
2. Background C # class files containing all logic and Methods
Code:
Aspx file:
Here we have designed a DataGrid object, and I have annotated some attributes and methods. It becomes so simple:
<Asp: DataGrid id = "mydatagrid" style = "Z-INDEX: 101; left: 16px; position: absolute; top: Running PX" runat = "server"
Borderstyle = "ridge"
Gridlines = "NONE"
Borderwidth = "2px"
Bordercolor = "white"
Backcolor = "white"
Cellpadding = "3"
Cellspacing = "1"
When allowpaging = "true" // "true" of the allowpaging attribute, paging operations can be performed
Allowsorting = "true" // This is a category attribute
Pagesize = "15" // set 25 records per page
Pagerstyle-mode = "nextprev" // two modes are available: Next previous and page numberin.
Pagerstyle-nextpagetext = "Next"
Pagerstyle-prevpagetext = "previous"
Pagerstyle-horizontalalign = "center"
Pagerstyle-position = "topandbottom"
Datakeyfield = "productid" // each record of the DataGrid contains a productid Field
Onpageindexchanged = "mydatagrid_pageindexchanged" // The mydatagrid_pageindexchanged function (function) is activated when you perform page flip operations)
Onsortcommand = "sort_grid" // The sort_grid (function) function is activated when you classify a DataGrid.
Ondeletecommand = "mydatagrid_delete" // This event activates the mydatagrid_delete function (function) to delete a record.
Onupdatecommand = "mydatagrid_update" // This event activates the mydatagrid_update function (function) to update a record.
Oncancelcommand = "mydatagrid_cancel // This event activates the mydatagrid_cancel function (function) to cancel the current operation
Oneditcommand = "mydatagrid_edit" // This event activates the mydatagrid_edit function (function) to edit a record.
Autogeneratecolumns = "false" // you can specify "false" to automatically generate a behavior"
Horizontalalign = "Left">
<Footerstyle forecolor = "black" backcolor = "# c6c3c6"> </footerstyle>
<Headerstyle font-bold = "true" forecolor = "# e7e7ff" backcolor = "# 4a3c8c"> <Pagerstyle nextpagetext = "Next" prevpagetext = "previous" horizontalalign = "right" forecolor = "black"
Position = "topandbottom" backcolor = "# c6c3c6"> </pagerstyle>
<Selecteditemstyle font-bold = "true" forecolor = "white" backcolor = "# 9471de"> </selecteditemstyle>
<Itemstyle forecolor = "black" backcolor = "# dedfde"> </itemstyle>

 

<Columns>
<Asp: editcommandcolumn buttontype = "linkbutton" updatetext = " </ASP: editcommandcolumn>
<Asp: buttoncolumn text = " </ASP: buttoncolumn>
<Asp: boundcolumn datafield = "productid" sortexpression = "productid" readonly = "true" headertext = "productid"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "productname" sortexpression = "productname" headertext = "productname"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "quantityperunit" sortexpression = "quantityperunit" headertext = "quantity perunit"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "unitprice" sortexpression = "unitprice" headertext = "unit price" dataformatstring = "{0: c}"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "unitsinstock" sortexpression = "unitsinstock" headertext = "units instock"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "unitsonorder" sortexpression = "unitsonorder" headertext = "units onorder"> </ASP: boundcolumn>
<Asp: boundcolumn datafield = "reorderlevel" sortexpression = "reorderlevel" headertext = "reorderlevel"> </ASP: boundcolumn>
<Asp: templatecolumn headertext = "discontinued" sortexpression = "discontinued">
<Itemtemplate>
<Asp: checkbox id = "discontinued" runat = "server" Checked = '<% # databinder. eval (container. dataitem, "discontinued") %>'/>
</Itemtemplate>
</ASP: templatecolumn>
</Columns>
</ASP: DataGrid>
You see, isn't it difficult? The key lies in our manual brain. Reading more information is also crucial!
C # background program:
Let's take a look at a program:
Private void page_load (Object sender, system. eventargs E)
{
If (! Ispostback)
{
Bindgrid ();
}
}
The above shows a very good technique. Data is bound when the page is not in the PostBack status. This means that the requested data on the page will be bound.
Continue viewing the program:
/// <Summary>
/// This function returns the dataset about the product details
/// </Summary>
/// <Returns> </returns>
Private dataset getproductdata ()
{
/// Sqlstatement is an SQL statement (string type)
String sqlstatement = "select products. productid, products. productname, products. quantityperunit, products. unitprice," +
"Products. unitsinstock, products. unitsonorder, products. reorderlevel, products. discontinued" +
"From products ";:
/// Declare the sqlconnection object: myconnection
Sqlconnection myconnection = new sqlconnection (@ "Server = (local) \ netsdk;" +
"Database = northwind; uid = northwind; Pwd = northwind ;");
/// Declare the command object: mycommand
Sqldataadapter mycommand = new sqldataadapter (sqlstatement, myconnection );
/// Set the command type to text.
Mycommand. selectcommand. commandtype = commandtype. text;
/// Create a DataSet object instance
Mydataset = new dataset ();
/// Fill in the data returned from the table products with mydata
Mycommand. Fill (mydataset, "Products ");
/// The final returned mydataset object
Return mydataset;
}
This code executes the given SQL statement to access the database. The Private function getproductdata returns a dataset containing data records. Next, let's see how to edit the record:
/// <Summary>
/// This function is activated only when the user clicks the edit button.
/// </Summary>
/// <Paramname = "sender"> </param>
/// <Paramname = "E"> </param>
Protected void mydatagrid_edit (Object sender, datagridcommandeventargs E)
{
/// Locate the index (itemindex) of the selected project and bind the data to it.
Mydatagrid. edititemindex = (INT) E. Item. itemindex;
Bindgrid ();
}
The annotations attached to the code above can also help you understand the functions of the mydatagrid_edit function: When you click the edit button, the mydatagrid_edit function is activated and the program finds the index of the record to be edited, allocate the index number to the edititemindex attribute of the DataGrid.
If you click the cancel button, we will call the above. the myd_d_cancel function mentioned in the aspx file. If the value assigned by the program to the edititemindex attribute of the DataGrid is-1, the user does not select Edit. The program is as follows:
/// <Summary>
/// The mydatagrid function is activated when you click the cancel button.
/// </Summary>
/// <Paramname = "sender"> </param>
/// <Paramname = "E"> </param>
Protected void mydatagrid_cancel (Object sender, datagridcommandeventargs E)
{
Mydatagrid. edititemindex =-1;
Bindgrid ();
}
The following code shows how to delete a selected record from the DataGrid. We know that the Web Control DataGrid has the datakeyfield attribute. In fact, it contains the productid field value of each record. You will certainly ask how to get the productid value of the selected record in the DataGrid through the datakeyfield attribute? The following code will let you go:
-----
Int productid = (INT) mydatagrid. datakeys [(INT) E. Item. itemindex];
-----
The code for the mydatagrid_delete function is as follows:
/// <Summary>
/// Delete a record from Dataset
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void mydatagrid_delete (Object sender, datagridcommandeventargs E)
{
Int productid = (INT) mydatagrid. datakeys [(INT) E. Item. itemindex];
String sqlstatement = "delete products where productid =" + productid;
String myconnectionstring = "Server = localhost; uid = sa; Pwd =; database = northwind ";

Sqlconnection myconnection = new sqlconnection (myconnectionstring );
Sqlcommand mycommand = new sqlcommand (sqlstatement, myconnection );

Mycommand. commandtimeout = 15;
Mycommand. commandtype = commandtype. text;

Try
{
Myconnection. open ();
Mycommand. executenonquery ();
Myconnection. Close ();
}
Catch (exception ee)
{
Throw EE;
}
Mydatagrid. edititemindex =-1;
Bindgrid ();
}
The following code updates the product information of the northwind database,
We can use the following technology to retrieve values:
-------------------
Bool discon = (checkbox) E. Item. findcontrol ("discontinued"). checked;
-------------------
In this case, we can use the fincontrol () method to obtain the value of discontinued checkbox.
/// <Summary>
/// Update record
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void mydatagrid_update (Object sender, datagridcommandeventargs E)
{
Int productid = (INT) mydatagrid. datakeys [(INT) E. Item. itemindex];
String productname = (textbox) E. Item. cells [3]. controls [0]). text;
String quantityperunit = (textbox) E. Item. cells [4]. controls [0]). text;
String unitprice = (textbox) E. Item. cells [5]. controls [0]). text;
Int16 unitsinstock = int16.parse (textbox) E. Item. cells [6]. controls [0]). Text );
Int16 unitsonorder = int16.parse (textbox) E. Item. cells [7]. controls [0]). Text );
Int16 reorderlevel = int16.parse (textbox) E. Item. cells [8]. controls [0]). Text );
Bool discon = (checkbox) E. Item. findcontrol ("discontinued"). checked;
Int result;

If (! Discon)
{
Result = 0;
}
Else
{
Result = 1;
}
String sqlstatement = "update products" +
"Set productname = '" + productname + "'," +
"Quantityperunit = '" + quantityperunit + "'," +
"Unitprice =" + unitprice. substring (unitprice. indexof ("￥") + 1) + "," +
"Unitsinstock =" + unitsinstock + "," +
"Unitsonorder =" + unitsonorder + "," +
"Reorderlevel =" + reorderlevel + "," +
"Discontinued =" + Result +
"Where productid =" + productid;

String myconnectionstring = "Server = localhost; uid = xjb; Pwd = xjb; database = northwind ";
Sqlconnection myconnection = new sqlconnection (myconnectionstring );
Sqlcommand mycommand = new sqlcommand (sqlstatement, myconnection );

Mycommand. commandtimeout = 15;
Mycommand. commandtype = commandtype. text;

Try
{
Myconnection. open ();
Mycommand. executenonquery ();
Myconnection. Close ();
}
Catch (exception ee)
{
Throw EE;
}

Mydatagrid. edititemindex =-1;
Bindgrid ();
}

The next bindgrid () calls the private function getproductdata to obtain the DataSet object and bind it to the DataGrid Control.
/// <Summary>
/// Accept the database data and bind it again
/// </Summary>
Protected void bindgrid ()
{
Mydatagrid. datasource = getproductdata (). Tables ["Products"]. defaultview;
Mydatagrid. databind ();
}
When the user moves forward or backward in the DataGrid, The mydatagrid_pageindexchanged event is activated. Because the DataGrid cannot automatically obtain the index number of the new page, we can only obtain the index number manually.
/// <Summary>
/// Paging operation
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void mydatagrid_pageindexchanged (Object source, datagridpagechangedeventargs E)
{
Mydatagrid. currentpageindex = E. newpageindex;
Bindgrid ();
}
When you want to classify data, activate the following sort_grid event. For example, if you click field headers, the event will be activated and the data will be divided into the desired category. We need the dataview object to be classified by the E. sortexpression. tostring () method. The returned result is the category of the clicked domain title.
/// <Summary>
/// Category
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Protected void sort_grid (Object sender, datagridsortcommandeventargs E)
{

Dataview DV = new dataview (getproductdata (). Tables ["Products"]);
DV. Sort = E. sortexpression. tostring ();
Mydatagrid. datasource = DV;
Mydatagrid. databind ();
}
Execution result:
The program is successfully tested in the Win2000 + sqlserver2000 + vs. netbeta2 environment. The program execution result is as follows: