The excel| control note the Visual basic_ version of the download file associated with this article was updated on March 4. If you downloaded the source code before March 4, you will need to download the file again to get the Visual Basic file.
The ASP.net DataGrid control generates an HTML output result that looks exactly like a Web copy of a Microsoft Excel worksheet. In addition, the control supports functionality such as selection and in-place editing, which further confirms this similarity. In particular, this similarity is most obvious from the point of view of supporting in-place editing capabilities. When you click a special type of command column, the grid uses a text box instead of static text to redraw its contents. At the same time, the command column changes the layout, replacing the edit link with two other links-one to save the changes and the other to cancel the changes. The entire look is almost exactly the same as the Excel Name box command bar.
The DataGrid also gives programmers the opportunity to customize the layout of the edited cell to some extent. This can be accomplished by using templated columns instead of bound and button columns, and by defining the editing template in the markup body of the templated column.
Simply put, the DataGrid control simply provides the infrastructure for in-place editing and fires certain events when the changes are saved. For full editing, the grid component should provide three basic actions that you might want to perform for the data source: Insert, delete, and update. The infrastructure that the DataGrid uses for editing (essentially the EditCommandColumn column class) only guarantees that updates and deletions can be performed. It is relatively easy to implement the delete feature, and just ask you to define a ButtonColumn object with the command name "delete" and wait for the DeleteCommand event to fire.
In this column, you'll see how to extend the DataGrid control so that it supports the INSERT command. We will do this by creating a new class and inheriting it from the DataGrid. We will make the class implement as much sample code as possible, eliminating some duplication of coding. Therefore, we will have a control that fires new events and more specific events, and we can use this unique interface to maintain database tables.
Editablegrid Control
What kind of interface should the new control have?
The idea is to limit the number of code that programmers have to write as much as possible. The control will be responsible for adding any new rows to its own interface, and then alerting the user when the changes need to be saved. This principle applies to most operations and should not be limited to "inserts". When you implement in-place editing and deletion, you always have to put some relatively standard and repetitive code around the SQL statement. In particular, when you implement in-place editing, you must reset the index of the edited item and reload and rebind the updated data source. All of these sample code will be embedded in the new Editablegrid control. Therefore, the control provides a new Boolean property named Addnewrow, as well as several custom event-initrow, Updateview, save data, insert data, and delete data.
When a user wants to add a new row, he or she simply sets the Addnewrow property to True and rebind the control. The rest of the operation occurs internally. (I'll describe the details of this process later.) The new row is drawn in edit mode, and the Initrow event is fired to give you the opportunity to set some fields to their default values.
Updateview roles are not closely related to update operations. The DataGrid control does not cache the data source, so you need to rebind whenever the page is sent back (for paging, sorting, editing, inserting, or deleting). This new event is added to simplify coding and embed as many encodings as possible. The update view fires when the grid needs to set its DataSource property. The typical handler for the Update view assigns the current data source to the property and calls the DataBind method.
When the corresponding SQL statement cannot delay execution further, three other events-save data, insert data, and delete data-are fired. When handling any of these events, you can set up and perform an update, insert, or DELETE statement. You are responsible for retrieving the updated data and preparing and executing the command. In addition to "insert data" (not closely related to the DataGrid programming Interface), "Save Data" and "delete data" are also different from standard UpdateCommand and DeleteCommand, because they are more specific and just ask you to execute the SQL code. The new event is essentially fired by UpdateCommand and DeleteCommand handlers, which are defined silently by the Editablegrid control at load time. These internal handlers are responsible for performing all other tasks (for example, resetting the index) and refreshing the view. The latter (that is, refreshing the view) is accomplished by stimulating the Updateview event.
Set control
Let's quickly browse the Editablegrid class. The constructor of the class initializes some public and protected properties and sets the default handler for several events of the base class.
Namespace Bwslib
{
public class Editablegrid:datagrid
{
Public Editablegrid ()
{
Allowfullediting = true;
Addnewrow = false;
AllowPaging = true;
RejectChanges = false; Internal use
Mustinsertrow = false; Internal use
Handlers
Init + = new EventHandler (OnInit);
PageIndexChanged + = new
Datagridpagechangedeventhandler (onpageindexchanged);
ItemCreated + = new Datagriditemeventhandler (onitemcreated);
CancelCommand + = new Datagridcommandeventhandler (Oncancelcommand);
EditCommand + = new Datagridcommandeventhandler (Oneditcommand);
UpdateCommand + = new Datagridcommandeventhandler (Onupdatecommand);
DeleteCommand + = new Datagridcommandeventhandler (Ondeletecommand);
}
:
}
}
The Editablegrid class has a public property-allowfullediting that has not been mentioned. This property member supports full editing of the grid. If you set this property to False, the control will not provide in-place edit or insert functionality. What is the process of implementation? The control automatically sets the AllowPaging to True and provides a handler for pageindexchanged. This means that Editablegrid is still better than the DataGrid control because it gives you an automatic free paging.
When Allowfullediting is set to True (the default value), the Editablegrid control automatically appends two new columns to the grid. The first column is EditCommandColumn, which provides in-place editing capabilities. The second column is ButtonColumn, and its command is DELETE. Both columns are added by handlers that run in response to the Init event.
public void OnInit (Object sender, EventArgs e)
{
if (allowfullediting)
Addworkercolumns ();
}
private void Addworkercolumns ()
{
Edit column
EditCommandColumn editcolumn = new EditCommandColumn ();
Editcolumn.edittext = Editcolumntext;
Editcolumn.updatetext = Editcolumnupdatetext;
Editcolumn.canceltext = Editcolumncanceltext;
Columns.Add (Editcolumn);
Delete column
ButtonColumn deletecolumn = new ButtonColumn ();
Deletecolumn.commandname = "Delete";
Deletecolumn.text = Deletecolumntext;
Columns.Add (DeleteColumn);
}
Editcolumntext, Editcolumnupdatetext, Editcolumncanceltext, and Deletecolumntext determine the text of the button used to trigger the edit and delete operations. They are "edit", "OK", "Cancel", and "delete" by default.
Before you insert the control into any asp.net page, you must register it as follows:
<% @Register tagprefix= "Expo" namespace= "Bwslib" assembly= "Editablegrid"%>
In the above declaration, you can change the contents of the TagPrefix property, but you cannot change any other content except for the namespace and class name of the control that can be modified. The following code shows how to declare the control in an ASPX page:
<expo:editablegrid id= "Grid" runat= "Server"
Autogeneratecolumns= "false"
Font-name= "Verdana" font-size= "Xx-small"
cellspacing= "0" cellpadding= "3"
borderstyle= "SOLID" bordercolor= "Skyblue" borderwidth= "1"
Gridlines= "both"
Pagesize= "4"
Datakeyfield= "EmployeeID"
oninitrow= "Initrow"
onupdateview= "Updateview"
Onsavedata= "SaveData"
Oninsertdata= "InsertData"
Ondeletedata= "DeleteData" >
:
<columns>
:
</columns>
</expo:EditableGrid>
The sample page reads data from a Microsoft Access 2000 table named Employees and stores the resulting dataset (dataset) in the Cache object.
Private DataSet Physicaldataread ()
{
OleDbDataAdapter da = new OleDbDataAdapter (
"SELECT * from Employees",
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\myemployees.mdb;");
DataSet ds = new DataSet ();
Da. Fill (ds, "Employees");
return DS;
}
Figure 1 shows the output of the controls in the page.
Figure 1. Editablegrid Controls in operations
You do not need any additional code to add and delete columns. However, you must specify an Updateview handler. However, as you can see, this is a section of code that must be used in multiple locations with the DataGrid control. However, because there are updateview events, you only need to specify one time.
public void Updateview (Object sender, EventArgs e)
{
Updatedataview ();
}
private void Updatedataview ()
{
DataSet ds = (DataSet) cache["MyData"];
DataView dv = ds. tables["Employees"]. DefaultView;
Grid. DataSource = DV;
Grid. DataBind ();
}
In the above control declaration, you can also see three event handlers for data-related events. Let's take a look at how the control handles them.
Insert operation
The Editablegrid control does not include any user interface elements used to start the insert operation. As with other important features, programmers are responsible for putting hyperlinks or buttons that trigger a given event on a grid onto a page. Because adding new items is not dependent on specific rows, it is recommended that you do not use special command columns (for example, edit or delete). Client code handles page-level events and sets the Addnewrow property to respond to the action. The page then refreshes the grid to reflect the changes. For example, the Insert button in Figure 1 is linked to the following On-click handler:
public void OnInsert (Object sender, EventArgs e)
{
Grid. Addnewrow = true;
Updatedataview ();
}
The update Data View is a page-level procedure that handles grid data binding and sets the data source property.
The idea is to make the user not add new records directly to the data source, but only to declare the goals they want to achieve. Therefore, when you set the data source property of the Editablegrid control, the control checks the value of the Addnewrow. In a derived control, because there are multiple accessors, it is easy to determine when a given property is read or written. After doing so, Editablegrid overrides the data source property as follows:
public override Object DataSource
{
get {return base. DataSource;}
Set
{
Standard behavior
Base. DataSource = value;
Customized behavior
if (allowfullediting)
{
if (Addnewrow)
{
Addnewrow = false;
Insertnewrow ();
}
More code here ...
:
}
}
}
When you set the data source, a new row is appended as long as the Addnewrow property is true. Insertnewrow is the internal process used to set the grid for the insert and row editing features. The important assumption here is that the grid is bound to the Data View object. With this code, you can easily generalize to a more general solution.
private void Insertnewrow ()
{
Get the underlying DataTable object
DataTable dt = ((DataView) DataSource). Table;
If any pending changes, stop this to avoid inserting over
And over again if user refreshes ...
DataTable tableofpendingchanges = dt. GetChanges (datarowstate.added);
if (tableofpendingchanges!= null)
Return
Add the new row
DataRow row = dt. NewRow ();
Dt. Rows.Add (row);
Initialize the row (fire the Initrow event)
Datagridinitroweventargs Dgire = new Datagridinitroweventargs ();
Dgire. row = row;
Oninitrow (Dgire);
Goto to last page and turn edit mode for the last item
int nnewitemindex = setindexestolastpage (DT);
EditItemIndex = Nnewitemindex;
Tracks that a new row has just been added (internal member)
Mustinsertrow = true; means the table has pending changes
}
After you create the underlying datasheet object, use the NewRow method to add a new row and fire the custom Initrow event so that the user has the opportunity to set some fields to their default values. To achieve its objectives, Initrow needs to pass some data down to an event handler and receive any updates. You must schedule a custom event data structure and a custom event handler signature. Datagridinitroweventargs is the name of the event data structure, and Datagridinitroweventhandler is the delegate to use.
public delegate void Datagridinitroweventhandler (
Object sender, Datagridinitroweventargs e);
public event Datagridinitroweventhandler Initrow;
private void Oninitrow (Datagridinitroweventargs e)
{
if (Initrow!= null)
Initrow (this, e);
}
The Datagridinitroweventargs class inherits from EventArgs and expands by simply adding a DataRow member.
public sealed class Datagridinitroweventargs:eventargs
{
Represents the row being added. can use this object
To set fields to default values.
Public DataRow Row;
}
The control fills a Row member with a live instance of the newly created (but still empty) DataRow object before the Initrow event is fired.
Datagridinitroweventargs Dgire = new Datagridinitroweventargs ();
Dgire. row = row;
Oninitrow (Dgire);
If you need to set some default values, you can write the Initrow handler and set the fields for the DataRow object.
public void Initrow (Object sender, Datagridinitroweventargs e)
{
e.row["LastName"] = "Esposito";
}
Here, DataSource has an extra line to simulate the insert operation of the new record. The row has been added to the bottom of the data source. Therefore, you should make the grid point to its last page, and also consider the possibility of new rows entering a new page. For example, if you have eight records in a table and you use a page size of four, you will need to add a new page when adding a new record. To enable users to edit the contents of a new row, simply set the EditItemIndex property of the DataGrid to the page index of the new row. The final step in Insertnewrow is to set up internal data members to track operations that add new uncommitted rows to the table.
In short, the code adds a blank row object to the grid's data source. The row represents the pending changes to the datasheet object, and if the user cancels the operation, the row must be rejected. You must also reject a row if the user moves to another page or decides to click and edit another row on the same page. Rejecting pending changes is one of the things that built-in handlers (for example, PageIndexChanged, EditCommand, and CancelCommand) do through an internal data member RejectChanges. (For more detailed information, see Source code.) ）
By putting the new row in edit mode, you can complete the insert phase and go to the update phase, as shown in the following illustration.
Figure 2. Insert New row
I also need to clarify another aspect for the subject of Insertnewrow. After you get the data source, the method immediately ensures that there are no rows that have been added but have not yet been committed. On design, you can have at most one pending change, and if the code is back in the process of having an added row, it is because the user refreshed the page. To avoid repeatedly adding blank rows and uncommitted rows, the program flow jumps out of the code block.
Update operation
Note that the Delete column that you see in Figure 1 is not visible in Figure 2. To simplify the interface, I decided to hide the Delete column when any row entered edit mode. And because of the previously mentioned built-in event handlers, this behavior is hard-coded in the Editablegrid class.
The update operation is performed by the following three events:
editcommand-Start the operation and present it in edit mode
updatecommand-Save your changes and restore the default user interface
cancelcommand-cancels the changes and restores the previous user interface
Typical code for EditCommand and CancelCommand is the ability to easily embed traditional vanilla code in a class. In general, there is no real content that needs to be addressed at the page level, but sometimes it may be different from your particular instance.
The application-specific logic of the update operation is set in the Update command event. In addition to saving any changes, you should restore the consistent state of the grid (cancel edit mode, restore the Delete column, and reject pending changes), and make sure that the changes are then reflected by the data source used for the display. If you use cached data in the same way as in this example, then it is important to follow.
public void Onupdatecommand (Object sender, DataGridCommandEventArgs e)
{
Clear edit mode
EditItemIndex =-1;
Show/hide DELETE Column
Toggledeletecolumn (TRUE);
Reject changes on the last row
RejectChanges = true; Internal member
Update or Insert Data
if (Mustinsertrow)//internal member
Oninsertdata (e);
Else
Onsavedata (e);
Refresh View
Onupdateview (Eventargs.empty);
}
The UpdateCommand handler cancels the editing mode of the current row, and then opens the visibility flag for the Delete column. At this point, the table may have a pending change. The conditional form makes sense because the update command event fires under two conditions, when you intend to save your changes to an existing row, or when you want to insert a new row. The internal member Mustinsertrow is set by the Insertnewrow and reset by DataSource, which helps determine which case. After the code has finished processing the state of the grid, it fires two consecutive events-an event that allows the page to be saved or inserted into the physical data source, and another event to refresh the data binding. This explains why InsertData and SaveData handlers can only perform physical updates that are primarily done through SQL statements.
The signatures of the InsertData and SaveData event handlers are the same as those of the UpdateCommand.
public event Datagridcommandeventhandler SaveData;
protected virtual void Onsavedata (DataGridCommandEventArgs e)
{
if (savedata!= null)
SaveData (this, e);
}
public event Datagridcommandeventhandler InsertData;
protected virtual void Oninsertdata (DataGridCommandEventArgs e)
{
if (InsertData!= null)
InsertData (this, e);
}
In the example code discussed in this article, there are several hypotheses, one of which is to assume that the data source of the grid is a data View object. This indirectly means that custom paging is not supported, or, more specifically, that the code must be carefully modified to handle such a grid. The same applies to sorting.
The second important assumption is that you are updating with an SQL statement.
The idea is that no matter what changes are made, a single SQL statement is always fired to apply it. The Editablegrid that are designed here do not handle batch updates correctly. Incidentally, in my building Web Solutions with asp.net and ado.net, when introducing in-place editing, the pros and cons of using batch updates for grids are discussed in more detail. However, if you are interested in using a batch of newer technologies, please send me an e-mail and I will introduce this topic in a future column.
Because you are updating through a direct SQL statement (or the content that the data source recognizes as a direct statement), the Update command event can successfully command to reject any changes. This is the main difference in the batch update scenario.
The Save data and insert data events represent a subset of the tasks necessary to perform the update. After the command is executed, these handlers must ensure that the grid can access and display the refreshed data. In this case, you must update the cached copy of the data. Depending on the underlying database schema (any triggers or any AutoNumber fields), you can decide whether to either completely re-read or update the cached data on a per-field basis.
Let's take some time to learn how to retrieve the updated data from the grid's edit template. I consider making Editablegrid controls smart enough to extract values from cells and populate them into DataRow objects to act as event data. This approach makes it trivial to update code in a batch scenario. The decision to make the ASP.net page responsible for extracting the data is because you can also transparently support editing the template. I'll show you the code that is necessary to not use any special templates.
public void SaveData (Object sender, DataGridCommandEventArgs e)
{
StringBuilder sb = new StringBuilder ("");
Sb. Append ("UPDATE Employees SET");
Sb. Append ("Firstname= ' {0} ',");
Sb. Append ("Lastname= ' {1} ',");
Sb. Append ("Title= ' {2} ',");
Sb. Append ("Country= ' {3} ')";
Sb. Append ("WHERE employeeid={4}");
String cmd = sb. ToString ();
SB = null;
TextBox fName = (textbox) e.item.cells[1]. Controls[0];
TextBox lName = (textbox) e.item.cells[2]. Controls[0];
TextBox position = (textbox) e.item.cells[3]. Controls[0];
TextBox country = (textbox) e.item.cells[4]. Controls[0];
cmd = String.Format (cmd,
Fname.text, Lname.text,
Position. Text, country. Text,
Grid. Datakeys[e.item.itemindex]);
Executes the command
OleDbConnection conn = new OleDbConnection (m_connstring);
OleDbCommand C = new OleDbCommand (CMD, conn);
C.connection.open ();
C.executenonquery ();
C.connection.close ();
Re-read from the database and updates the cache
Datafromsourcetomemory ();
}
To retrieve the information that the user entered in the text box, use E.item.cells[n]. CONTROLS[0] expression where n is the index of the column (starting from 0). Keep in mind that the in-place editing feature of the DataGrid allows you to treat columns as read-only by setting the read-only property to true.
Delete operation
The Delete operation works in approximately the same way as the Insert and Update operations. The Automatically created column has a command name Delete that causes the ± event to fire when the button is clicked. The built-in handler fixes the grid interface and then fires DeleteData and Updateview sequentially.
Because the deletion is more intrusive than the insert or update operation, you may want to use a client-script code and ask the user to confirm it before continuing. I discussed this method in the "Dialog Bar" section of last month ' s column. In this month's source code, there is an actual implementation as shown in the following figure.
Figure 3. Confirm before deleting
The final optimization
I mentioned that the Editablegrid control supports editing templates. Let me prove this conclusion. In step 2nd of the example code, I use a slightly different set of columns in the grid.
<columns>
<asp:boundcolumn runat= "Server" headertext= "ID"
Datafield= "EmployeeID" readonly= "true"/>
<asp:templatecolumn runat= "Server" headertext= "Name" >
<itemtemplate>
<%# DataBinder.Eval (Container.DataItem, "LastName") + "," +
DataBinder.Eval (Container.DataItem, "FirstName")%>
</itemtemplate>
<edititemtemplate>
<asp:textbox runat= "Server" id= "LName"
Text= ' <% #DataBinder. Eval (Container.DataItem, "LastName")%> '/>
<asp:textbox runat= "Server" id= "FName"
Text= ' <% #DataBinder. Eval (Container.DataItem, "FirstName")%> '/>
</edititemtemplate>
</asp:templatecolumn>
<asp:boundcolumn runat= "Server" headertext= "Position"
datafield= "title"/>
<asp:boundcolumn runat= "Server" headertext= "Country"
datafield= "Country"/>
</columns>
As you can see, there is a template column that displays the first and last names in a single field. The edit template for this column (you must specify an edit template that can edit the column) is provided by two side-by text boxes. You do not need to make any changes in the class to get the example shown in Figure 4.
Figure 4. Using Edit templates
On the other hand, editing a template requires a little tweaking when you use the way to retrieve updated text from a text box. You can now retrieve the controls in the template by name. It is possible and advisable to do so because you know the ID of the control.
TextBox fName = (textbox) E.item.findcontrol ("FName");
TextBox lName = (textbox) E.item.findcontrol ("LName");
TextBox position = (textbox) e.item.cells[2]. Controls[0];
TextBox country = (textbox) e.item.cells[3]. Controls[0];
In this month's source code, you'll find the C # source for the class, two sample asp.net pages, and the Access database that I used. However, you will not find the compiled assembly. I have provided a (very) simple batch file that you can use to compile the class into executable code for any version of the. NET CLR that is compatible with Beta 2 and its later version.
Note If you are having trouble keeping the download running correctly, check the following steps:
·1. The CS class is compiled into an assembly. This can be done by opening a DOS box and running the C.bat batch file in ZIP, or by creating a new class Library project in Visual Studio and adding the class file to this blank project.
2. You must make the assembly available for the sample ASPX page. The batch file included in the download file copies the DLL to the C:\inetpub\wwwroot\bin folder. If you happen to have a different path, make changes to it. If you create a virtual directory, make sure that you copy the DLL to the Bin subfolder of the virtual folder, not to the bin folder of the Web server root.
3. Depending on the ASP.net security settings, you may encounter difficult updateable Query errors when interacting with a sample Microsoft Access database. In this case, change the example. The security settings for the MDB file. Select the file in Windows Explorer, display the properties, and then click the Security tab. Next, add the \aspnet user and make sure that it has permission to write and modify the file.
4. Refreshes the ASPX page.
Dialog bar: Creating custom templates
In the previous column, you discussed the Summary grid component. Is there a chance to create or load a custom template for the Total row? This allows the design specification to be specified in the ASPX file, not in the code.
It is no doubt possible to use a custom template that summarizes rows. The problem is just how to achieve it? Or, more precisely, which method is the easiest? I'll introduce a few possible methods below.
• Use Pagelet: You can modify the application's code to dynamically load the Pagelet control (also known as the user control)-that is, the ASCX file. An ASCX file looks like a small Web form, and much of it is layout information. You can use Page.LoadControl to load an ASCX file by name and then add it to the Controls collection of a cell in a DataGridItem object.
• Template columns: All columns of a DataGrid are templated columns. Use the asp.net tag and some code everywhere to define each column. What you can do is divide the structure of the block into two distinct and mutually exclusive parts-one for the normal row and the other for the Total row. To ensure that only one row is displayed at a time, you can handle the control's Visible property and bind the property to the criteria that define the total row or normal row.
• Write a new control: derive a new class from the DataGrid and add a new template property. This allows you to use a custom child tag and enter the Total row layout in almost the same way as the input column template.
Back to the top of the page
