Really similar to Excel's grid controls (in-depth research data access)
Release Date: 4/1/2004 | Update Date: 4/1/2004
Dino esposito
Wintellect
February 14, 2002
Download EditableGrid.exe.
Note The Visual Basic_ version of the download file related to this article has been updated on March 4. If you download the source code before March 4, you will need to re-download the file to get the Visual Basic file.
The ASP.NET DataGrid control generates an HTML output result, which looks like a Web copy of the Microsoft Excel worksheet. In addition, the control supports functions such as selecting and on-site editing, which further confirms this similarity. In particular, this similarity is the most obvious from the point of view of supporting the local editing function. When you click on a special type of command, the grid uses the text box (not static text) to redraw its content. At the same time, the command profile will change the layout and replace the edit chain to two other links - a change to save your changes and the other to cancel the changes. The whole looks almost identical to the Excel Name box command column.
DataGrid also allows programmers to have the opportunity to customize the layout of the edited cells to some extent. This can be implemented by using the template column instead of binding columns and button columns, and defining editing templates in the template column.
Simply put, the DataGrid control only provides an infrastructure for local editing and inspiring certain events while saving the changes. In order to be able to fully edit, the grid component should provide three basic operations you might want to perform on the data source: "Insert," Delete and "Update". DataGrid is used to edit the infrastructure (essentially the EditCommandColumn column class) only ensures that the update and delete operation can be performed. Realizing the "Delete" function is relatively easy, and only requires you to define the ButtonColumn object named "Delete" and wait for the DeleteCommand event to be excited.
In this column, you will see how to extend the DataGrid control, allowing it to support the insert command. We will implement this by creating a new category and makes it from DataGrid. We will enable this class as much sample code as possible to save some repeat coding. Therefore, we will have an control of the new event and more specific events, and we can use this unique interface to maintain database tables.
EditableGRID control
Which interface should the new control?
The idea is to limit the number of code that the programmer must write as much as possible. The control will be responsible for adding any new row to its own interface, and then warning users when you need to save. This principle applies to most of the operations, and should not be limited to "insert". When implementing on-site editing and deletion, always put some relative standards and repetitive code around the SQL statement. Especially when implementing a local editing function, the index of the edited project must be reset, and reload and re-bound the updated data source. All these samples will be embedded in the new EditableGrid control. Therefore, the control provides a new Pul property called AddNewRow, as well as several custom events - initrow, updateview, "saving data", "inserted data", and "delete data".
When the user wants to add a new row, he (or her) simply sets the AddNewRow property to True and resin the control. The rest of this operation occurs inside. (I will describe the details of this process later.) New row will draw in edit mode, the initrow event will be excited, and its purpose is just to set some fields to default.
The role of the UpdateView is not closely related to the update operation. The DataGrid control does not caches the data source, so you need to rebound regardless of the page (for paging, sort, editing, plugging, or deleting), you need to rebound. In order to simplify the encoding and embedding the encoding as much as possible, add this new event. When the grid needs to set its DataSource properties, "update view" will be excited. A typical handler of "Update View" assigns the current data source to this attribute and calls the DATABIND method. When the corresponding SQL statement cannot be further delayed, the other three events, "Save Data", "Insert Data", and "Delete Data". When dealing with any events, you can set up and execute "Update", "Insert" or "Delete" statements. You are responsible for retrieving updated data and preparing and execute the command. In addition to "Insert Data", "Save Data" and "Delete Data" are different from standard UpdateCommand and DeleteCommand because they are more specific and asked you to perform SQL code. The new event is basically excited by the handler of UpdateCommand and DeleteCommand, which is defined by the EditableGrid control when loading. These internal processing programs are responsible for performing all other tasks (eg, reset indexes) and refresh the view. The latter (ie, refresh view) is done by exciting UpdateView events.
Set control
Let's quickly browse EditableGrid classes. The constructor of this class initializes certain public properties and protected properties, and sets the default handler for several events in 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 (onpageIndexchange);
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 that has not been mentioned - Allowfullediting. The property member supports the full editing function of the grid. If you set this property to False, the control will not provide an on-site editing or insert function. What is the process of executing? This control automatically sets allowpaging to true and provides a handler for pageIndexchanged. This means that EditableGrid is better than the DataGrid control because it provides you with an automatic idle paging. The EditableGrid control automatically adds two new columns to the grid when the allowfullediting is set to TRUE (default). The first column is EditCommandColumn, which provides on-site editing. The second column is ButtonColumn, and its command is delete. These two columns are added by a handler running responding to an init event.
Public void Oninit (Object Sender, Eventargs E)
{
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;
Column.Add (deletecolumn);
}
EditColumnText, EditColumnUpdatetext, EditColumnCancelText, and DeleteColumnText determine the text used to trigger the button for editing and deleting a button. They are "editing", "OK", "Cancel", and "Delete" at the default.
Before inserting 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, however, any other content cannot be changed in addition to the namespace and class name of the control. The following code shows how to declare the control in the ASPX page:
AutogenerateColumn = "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"> : : The Sample page reads the data from a Microsoft Access 2000 table named Employees and stores the generated 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 result of the control in this page. Figure 1. EditableGrid control in operation No additional code is required when adding and deleting columns. However, you must specify an UPDATEVIEW handler. However, as you can see, this is a code that must be used with the DataGrid control together in multiple locations. However, because there is an UPDATEVIEW event, just 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 event handler for events related to data. 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 initiate the "insert" operation. As with other important functions, the programmer is responsible for the superlink or button that is able to trigger a given event on the grid to the page. Since adding new projects do not depend on a particular row, you recommend that you do not use a special command column (for example, "Edit" or "Delete"). The client code handles page-level events and sets the AddNewRow property to respond to operation. Next, the page refreshes the grid to reflect the changes made. 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 (); } "Update Data View" is a page-level process that handles grid data binding and sets the "Data Source" attribute. The design idea is to make the user not directly add new records to the data source, but only declare the goals they want to implement. Therefore, when setting the "Data Source" property of the EditableGrid control, the control checks the value of AddNewrow. In derived controls, because there are a variety of accessors, it is convenient to determine when read or write given attributes. After doing so, EditableGrid will overwrite the Data Source attribute as follows: Public Override Object DataSource { Get {returnrate.dataser set { // Standard Behavior Base.DataSource = value; // Customized Behavior allowfullediting { IF (addNewrow) { AddNewRow = false; INSERTNEWROW (); } // More code Here ... : } } } When setting the "Data Source", you will add a new line as long as the AddNewrow property is TRUE. INSERTNEWROW is used to set the internal process of the grid for the insertion and row editing function. Important assumptions herein are grids and "Data View" object bindings. With this code, you can easily extend to a more common solution. Private void insertnewrow () { // Get the underlying datatable object DataTable DT = ((DataView) DataSource .table; // if any points, stop here 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 change } After creating the base "Dataset" object, use the new line to add a new row and inspire the custom initrow event so that the user has the opportunity to set some fields to the default. In order to achieve its goals, initrow needs to pass some data down to the event handler and receive any updates. A custom event data structure must be arranged and a custom event handler signature. DataGridInitRowEventArgs is the name of the event data structure, and DataGridInitRowEventHandler is the delegation to use. Public Delegate Void DataGridinitRoweventhandler Object sender, DataGridinitRowEventArgs E); Public Event DataGridinitRowEventhandler InitRow; Private Void OnInitrow (DataGridinitrowEventRgs E) { IF (Initrow! = NULL) INITROW (this, e); } The DataGridinitRowEventargs class inherits to Eventargs and extension by simply adding a DATAROW member. Public Sealed Class DataGridinitRowEventArgs: Eventargs { // REPRESENTS The Row Being Added. You can Use this Object // TO SET FIELDS to Default VALUES. Public Datarow Row; } Before exciting the initrow event, the control fills the ROW member with the real-time instance of the newly created (but still empty) DataRow object. 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 field of the DataRow object. Public void Initrow (Object Sender, DataGridinitrowEventArgs E) { E.Row ["Lastname"] = "esposito"; } Here, DataSource has an additional line to simulate new recorded insertions. This line has been added to the bottom of the data source. Therefore, you should make your grid to its last page, and also considering the possibility of new rows into the new page. For example, if there are eight records in the table, and the page size you are using is four, you need to add a new page when adding a new record. In order to enable users to edit the content of the new row, simply set the DataGrid's EditIndEx property to the page index of the new row. The final step completed by INSERTNEWROW is to set internal data members to track the operations of adding new uncommitted banks to the table. Briefly, the code adds an idlip-like object to the grid's data source. The row represents the "Data Table" object to be changed, and if the user cancels the operation, the line must be rejected. If the user moves to another page or decides to click and edit the other line on the same page, it must also reject the line. Refuse to be determined is one of the things that the built-in handler (for example, pageIndexchanged, editcommand, and carative through internal data member rejectchanges. (For more details, see the source code.) By placing the new line in edit mode, the insert phase can be completed, so that the update phase is entered, as shown below. Figure 2. Insert a new line For the main body of INSERTNEWROW, I also need to clarify another aspect. After obtaining the data source, the method immediately ensures that the row has not been added but has not been submitted. In design, up to one process change, and if the code re-enters the process of adding rows, it is because the user refreshed the page. In order to avoid repeated addition of blank rows and uncommitted rows, the program stream will jump out of the code block. UPDATE operation Please note that the delete column that can be seen in Figure 1 can be seen in Figure 2. To simplify the interface, I decided to hide the Delete column when you enter edit mode. And because of the previously mentioned built-in event handler, this behavior is hardcoded in the EditableGrid class. The UPDATE operation is performed by the following three events: • EditCommand - Start this action and edit mode rendering • UpdateCommand - Save the changes made and restore the default user interface • CancelCommand - Cancel the changes made and restore previous user interfaces Typical code for EditCommand and CancelCommand is a traditional Vanilla code that is conveniently embedded in classes. In general, there is no real need to be solved on the page level here, but sometimes the situation may differ from your specific instance. The "Update" application-specific logic is set in the "Update Command" event. In addition to saving any changes, you should restore the consistent state of the grid (cancel the edit mode, restore the Delete column, and reject the to-pending changes), and ensure that the changes made are then reflected by the data source used to display. If you use cache data as in this example, the later point is critical. Public void OnupdateCommand (Object Sender, DataGridCommandeventArgs E) { // Clear Edit Mode EditIndex = -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 edit mode of the current line and then opens the visibility flag of the Delete column. At this point, the table may have a to be determined. Because the "Update Command" event is in both cases (intended to save the changes to the existing line; if you want to insert a new row), the conditional form is very meaningful. The internal member MustInSertrow is set by INSERTNEWROW and reset by DataSource, which helps to determine which situation. After the code is processed, it excited two consecutive events - an event allows the page to save or insert into the physical data source, and another event refreshes the data binding. This explains why INSERTDATA and SAVEDATA handler can only perform physical updates primarily through SQL statements. The signature of the INSERTDATA and SaveData event handler is the same as UpdateCommand. Public Event DataGridCommandeventHandler Savedata; Protected Virtual Void OnSavedata (DataGridCommandeventAndargs 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 sample code discussed herein, several assumptions are set, one is assumed that the data source of the grid is the "data view" object. This indirect means that it does not support custom paging, or more specifically, this code must be carefully modified to handle such a grid. This is equally applicable to sort. The second important assumption made is to update using the SQL statement. Design ideas is that no matter what changes have been made, always inspire a single SQL statement to apply it. The EditableGrid designed here does not handle the appointment correctly. By the way, in my BUILDING Web Solutions With ASNET and ADO.NET book, the advantages and disadvantages of the grid are discussed in more detail in more detail. However, if you are interested in using batch update technology, please send me an email, I will introduce this topic in the later column. Since you update by direct SQL statement (or data source to direct statements), "Update Command" event can successfully command to reject any changes. This is the main difference in batch update scenarios. "Save Data" and "Insert Data" event represent the subset of the tasks required to perform updates. After executing this command, these handles must ensure that the grid can access and display refresh data. In this case, the cache copy of the data must be updated. Depending on the underlying database architecture (any trigger or any automatic field), it can be decided to be completely reread or update the cache data for each field. Let us spend some time to learn how to retrieve the updated data from the editing template of the grid. I consider that the EditableGrid control has sufficient intelligence to extract values from cells and populate them into the DataRow object to serve as event data. This method makes it difficult to update the code in a batch. The reason why the ASP.NET page is responsible for extracting the data because you can also support editing templates in transparency. Below I will show you the code necessary when you don't use any special template. 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 entered in the text box, use the E.Item.cells [n] .controls [0] expression, where n is the index of the column (starting from 0). Keep in mind that DataGrid's local editing feature allows you to deepen the columns as read-only by setting the "read-only" property to True. DELETE operation The work mode of the delete operation is substantially the same as INSERT and UPDATE operations. Automatically created columns have a command name Delete, which will cause the excitation ± event when clicking this button. The built-in handler repairs the interface of the grid, and then inspire Deletedata and UpdateView in turn. Since the intrusion of the delete operation is stronger than inserted or updated, you may want to use a client script code and ask the user to confirm before proceeding. I discussed this method in the "Dialog" section of the Last Month's Column. In this month's source code, there is a practical implementation as shown below. Figure 3. Confirm before deletion Ultimate optimization I mentioned the EditableGrid control support editing template. Let me prove this conclusion. In the second step of the sample code, I use a group of different columns in the grid. Datafield = "Employeeid" readonly = "true" /> <% # DataBinder.eval (Container.DataItem, "Lastname") "," DataBinder.eval (Container.DataItem, "firstname")%> Text = '<% # DataBinder.eval (Container.DataItem, "Lastname")%>' /> Text = '<% # DataBinder.eval (Container.DataItem, "firstname")%>' /> Datafield = "title" /> Datafield = "country" /> As you can see, there is a template column, which shows name and last name in a single field. The editing template of this column (you must specify the editing template that can edit the column) is provided by two columns of text boxes. No need to make any changes in the class, you can obtain the example shown in Figure 4. Figure 4. Using editing templates On the other hand, when using the text of the text from the text box, the editing template needs to be adjusted. Now you can retrieve the controls in the template by name. The reason why you can do this 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 will find C # source code, two sample ASP.NET pages, and I have used the Access database. However, you will not find the compiled assembly. I provide a (very) simple batch file, you can use it, compile this class into an executable code for any version of the .NET CLR compatible with Beta 2 and its higher version. Note If you encounter problems when you have a correct runtime, check the following steps: • 1. Compile CS class into an assembly. This can be achieved 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 use the assembly to be available for the sample ASPX page. Download the batch file included in the file to copy the DLL to the C: / INETPUB / WWWROOT / BIN folder. If you just have a different path, modify it. If you create a virtual directory, make sure that the DLL is copied into the BIN subfolder of the virtual folder instead of copying to the Bin folder of the web server root. • 3. According to the ASP.NET security settings, when interacting with the sample Microsoft Access database, it may encounter an UPDATEABLE query error. In this case, please change the security settings for the example .mdb file. Select this file in the Windows Explorer, display the property, and then click the Security tab. Then, add / ASPNET users and make sure it has the right to write and modify the file. • 4. Refresh the ASPX page. Dialog: Creating a custom template In the previous column, you discuss the Summary Grid component. Have you had the opportunity to create or load a custom template for the summary bank? This will allow specifications for design in the ASPX file (not code). The custom template using the summary bank will undoubtedly it will be possible. The problem is just how to implement it? Or, more accurately, which method is the simplest? I will introduce several possible methods below. • Using Pagelet: You can modify the application's code to dynamically load the Pagelet control (also known as user control) - the ASCX file. The ASCX file looks like a small web form, and most of them are layout information. You can use Page.LoadControl to load the ASCX file by name, and then add it to the Controls collection of a cell of the DataGridItem object. • Template Column: All columns of DataGrid are template columns. Use the ASP.NET tag and some of the code to define each column. The operation you can perform is to divide the structure of the block into two distracted and mutually exclusive parts - part of the common line, and the other is used for the summary line. To ensure that only one line is displayed at a time, you can handle the "visible" property of the control and bind the property with the critical bank command or the normal row. • Write a new control: a new class is derived from DataGrid and add a new template property. This allows you to use custom submarkers, enter the summary line layout in almost the same way as the input column template. Back to top Dino Esposito is Wintelle's ADO.NET expert and training faculty and consultant, and the work location is located in Rome, Italy. DINO is the special editor of MSDN Magazine, which is the writer of the Cutting Edge column. He also regularly submits developer Network Journal and MSDN NEWS. Dino is the founder of the building web solution for Microsoft Press, and is one of the founders of http://www.vb2themax.com/. If you want to contact Dino, you can send an email to dinoe@wintellect.com
