Tutorials to .com

Tutorials to .com » Dotnet » Control-and-components » Real grid similar to Excel Control

Real grid similar to Excel Control

Print View , by: iSee ,Total views: 14 ,Word Count: 5224 ,Date: Thu, 21 May 2009 Time: 2:32 PM

Note with this article to download documents related to Visual Basic_ version of March 4 has been updated. If you are in before the March 4 to download the source code will need to re-download the files for Visual Basic file.

ASP.NET DataGrid control to generate a HTML output, this result does look like a Microsoft Excel worksheet copy of the Web. In addition, the control supports the selection and the type of place editing functions, which further confirms this similarity. In particular, support for local editing features from this point of view, this similarity is most obvious on. When you click the special type of command-line, the grid will use a text box (rather than static text) to redraw its contents. At the same time, the command line will change the layout, edit the link will replace the two other links - one to save the changes, and the other to cancel the changes. Looks almost the entire Excel column name is identical to the command box.

DataGrid also allows programmers to have the opportunity to some extent by the editor from the definition of the layout of the cells. This can be achieved through the following methods: using a template to replace the out of bound out and buttons out, and mark out the template of the body of the definition of the template editor.

To put it simply, DataGrid control only for the provision of infrastructure in-place editing, and save the changes when certain events inspire. In order to be able to edit, the grid should provide the three components you may want the data source for the implementation of the basic steps: "Insert," deleted and "Update." DataGrid infrastructure for editing (basically out EditCommandColumn category) only guarantee that the implementation of update and delete operations. The realization of "delete" function is relatively easy to some, but only requires that you define a command called "delete" ButtonColumn object, and wait for events to stimulate DeleteCommand.

In this column, you will see how to extend the DataGrid control to support the INSERT command. We will create a new class and its inherited from DataGrid to achieve this. We will make as much as possible the realization of such a sample code in order to eliminate some duplication of code. Therefore, we will have a stimulating new events and more specific control events, and we can use this interface to maintain the only database table.

EditableGrid control

Which should be the new control interface?

The idea is to limit as far as possible, programmers must be prepared in the amount of code. Will be responsible for the control of its own interface to add any new line, and then save the changes need to warn users when. This principle applies to most operations, but not be limited to "insert." In-place editing and deletion of the realization, the SQL statement must always take some of the relative standard around and repeat the code. Especially in the realization of place editing feature, you must reset the edit index of the item and re-loading and re-bind the data source updated. All of these samples to the new code will be embedded in the EditableGrid control. Therefore, the control provided by a new Boolean attribute AddNewRow, as well as several custom events - InitRow, UpdateView, "save data", "insert data" and "delete data."

When the user want to add a new line, he (or she) simply AddNewRow property is set to true and re-bind the control. The rest of the operation took place in-house. (I will describe the details of this process.) The new model will be drawn in the editor, InitRow event will inspire, and its purpose is simply to allow you the opportunity to set some fields to their default values.

The role of UpdateView not closely related with the update. DataGrid control does not cache the data source, so, regardless of the page when the back-fat (for paging, sorting, editing, insert or delete), you need to re-bind. To simplify the coding and embedded coding as much as possible, add this new event. When the grid need to set up the DataSource property, the "update view" it will inspire. "Update view" typical of the current process will be allocated to the data source and call DataBind method attributes.

When the SQL statement can not be further delay in the implementation, it will inspire the other three events - the "Save data", "insert data" and "delete data." In dealing with any incidents which may be set up and implementation of "update", "insert" or "delete" statement. You are responsible for retrieval of the updated data and the preparation and execution of the order. In addition to "insert data" (with the DataGrid programming interface no close relationship), the "Save Data" and "delete data" is also different from the standard UpdateCommand and DeleteCommand, this is because they are more specific and only requires you run the SQL code. New event UpdateCommand and DeleteCommand is basically the process of excitation, these processes are loaded when EditableGrid control approach to the definition of silence. These internal processes responsible for the implementation of all other tasks (for example, reset the index) and refresh the view. The latter (ie, refresh the view) is excited to complete the UpdateView events.

Set control

Let us quickly browse categories EditableGrid. The class constructor to initialize the properties of certain public and protected properties, and base class for a few incidents to deal with the procedures set up by default.

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);

EditableGrid class has a public property that have not yet mentioned - AllowFullEditing. The attributes of the grid members to support the full editing features. If you set the attribute false, then the control will not provide the in situ function to edit or insert. Whether the implementation is how to deal with? AllowPaging the control will automatically set to true, and to provide a PageIndexChanged process. This means that EditableGrid or better than the DataGrid control, which automatically gives you free page.

AllowFullEditing When set to true (the default) when, EditableGrid control automatically added two new columns to the grid. The first column is EditCommandColumn, which provides editing features in situ. The second column is ButtonColumn, it is the command DELETE. The two out through the Init event in response to the operation of the process to add the.

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 used to determine the trigger DeleteColumnText edit and delete the text of the button operation. They were in default when the "Edit", "OK", "Cancel" and "delete."

Inserted in the control of any ASP.NET page, you must register it as follows:

<% @ Register TagPrefix = "expo" Namespace = "BWSLib" Assembly = "EditableGrid"%>

In the above statement, you can change the TagPrefix attribute, but, in addition to the control of the namespace and class name can be modified, the content can not be changed to any other. The following code shows how the statement in the ASPX page in the control:

<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>
</ expo: EditableGrid>
A sample page from the Employees table in Microsoft access 2000 andgenerated DataSet (data sets) stored 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 page in the control of the output.

Figure 1. EditableGrid operation control

Add and delete rows without any additional code. However, it is necessary to specify UpdateView process. However, as you can see, this is a must in a number of locations used in conjunction with the DataGrid control code. However, there was UpdateView event, so only the specified 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 ();

Control in the above statement, you can see the three data-related event processing. Let us look at how the controls are dealing with them.

Insert operation

Control does not include any EditableGrid used to start the "Insert" operation of user interface elements. As for the other important functions, the programmer is responsible for Grid will be able to touch events on a given hyperlink or button on the page. Add a new item rather than as a result of reliance on a specific line of operation, it is recommended that you do not use a special command line (for example, "edit" or "delete"). Client-side code to deal with page-level events, and set AddNewRow in response to the operation attribute. Then, refresh the page grid, in order to reflect the changes. For example, Figure 1 Insert (insert) button to link to on-click the following procedures:

public void OnInsert (Object sender, EventArgs e)
grid.AddNewRow = true;
UpdateDataView ();

"Update Data View" is a page-level process, which deal with data binding grid and set the "data source" attribute.

The idea of design is to enable the user directly to a new record added to the data source, but only a statement of its desired goals. Therefore, in settings EditableGrid control "data source" attribute, the control checks the value of AddNewRow. In the derivation of control because there are a variety of access devices, so it is easy to determine when to read or write a given attribute. After doing so, EditableGrid be rewritten as follows, "data source" attribute:

public override object DataSource
get (return base.DataSource;)
/ / Standard behavior
base.DataSource = value;

/ / Customized behavior
if (AllowFullEditing)
if (AddNewRow)
AddNewRow = false;
InsertNewRow ();

/ / More code here ...

In the set of "data source" when, as long as AddNewRow attribute true, a new line will be added. InsertNewRow is used to insert and set the line editing features of internal process grid. The important assumption here is that mesh with the "Data View" object binding. Use of the code, you can easily be extended to more general solutions.

private void InsertNewRow ()
/ / Get the underlying DataTable object
DataTable dt = ((DataView) DataSource). Table;

/ / If any pending changes, stop here to avoid inserting over
/ / And over again if user refreshes ...
DataTable tableOfPendingChanges = dt.GetChanges (DataRowState.Added);
if (tableOfPendingChanges! = null)

/ / 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
Basis in the creation of "Data Table" object, the method used to add a new line NewRow and stimulate InitRow custom events, so some users the opportunity to field is set to default values. In order to achieve its objectives, InitRow down some of the data needs to be delivered to the event handler and receive any updates. To arrange for a custom event data structure and a custom event handler signature. DataGridInitRowEventArgs is the name of the structure of event data, and DataGridInitRowEventHandler is commissioned to be used.

public delegate void DataGridInitRowEventHandler (
Object sender, DataGridInitRowEventArgs e);
public event DataGridInitRowEventHandler InitRow;
private void OnInitRow (DataGridInitRowEventArgs e)
if (InitRow! = null)
InitRow (this, e);

Class DataGridInitRowEventArgs inherited from EventArgs and add a simple expansion of the members of a DataRow.

public sealed class DataGridInitRowEventArgs: EventArgs
/ / Represents the row being added. You can use this object
/ / To set fields to default values.
public DataRow Row;

InitRow inspiring incident, the creation of the control with the new (but still empty) of the DataRow objects in real-time example of a member to fill Row.

DataGridInitRowEventArgs dgire = new DataGridInitRowEventArgs ();
dgire.Row = row;
OnInitRow (dgire);

If you need to set some default values, you can prepare and set up InitRow processing field DataRow object.

public void InitRow (Object sender, DataGridInitRowEventArgs e)
e.Row [ "LastName"] = "Esposito";

Here, DataSource additional simulation of a line can insert new records. The line has been added to the bottom of the data source. Therefore, you should make the grid point to the last page of it, but also taking into account the new line into the possibility of a new page. For example, if a table of eight records, but you are using a page size of four, then add a new record also need to add a new page. In order to enable users to edit the contents of the new line, simply set the DataGrid's properties EditItemIndex line for the new page index. InsertNewRow completed the final step is to set up internal data members, to track to the table to add a new line of operation did not submit.

In short, the code object will be an empty line added to the grid data source. The bank on behalf of the "data table" to change the object to be determined, and if the user cancels the operation, it must reject it. If the user decided to move to another page or click and edit the same page on another line, it must reject it. Refusal to change is to be built-handling procedures (for example, PageIndexChanged, EditCommand and CancelCommand) through the completion of the internal data members of one of the things RejectChanges. (For more details, please refer to the source code.)

New line will be placed through the edit mode to complete the insertion phase, to enter the update phase, as shown below.

Figure 2. Insert a new line

For the main body of InsertNewRow, I also need to clarify one aspect of the other. Access to the data source, the method has been added to immediately ensure that there is no row but has not been introduced. In the design, can be determined there is a change, and if re-entering the code has been added a line of process, it is because the user refresh the page. In order to avoid repeated blank lines and add the line did not submit, the program flow will jump out of the code block.

Update operation

Please note that I do not see in Figure 2 can be seen in Figure 1 in the Delete column. To simplify the interface, I have decided to visit at any time to enter the edit mode to hide out Delete. And because of the previously mentioned built-in event handler, so this type of behavior is EditableGrid of hard-coded.

update operation is the implementation of the following three events:

• EditCommand - the beginning of the operation and a line edit mode

• UpdateCommand - save the changes and restore the default user interface

• CancelCommand - the abolition of the changes and restore the former user interface

For a typical EditCommand and CancelCommand code is embedded in the ability to easily type in the code of traditional vanilla. In general, here there is no real need to solve the page-level content, but sometimes also may be related to your specific examples are different.

"Update" operation of the application-specific logic on the "update order" incident. Apart from the preservation of any changes, you should restore the consistent state grid (cancel edit mode, Delete out and refused to restore the changes to be determined), and to ensure that subsequent changes made by the data source used to display reflected. If you like this case as the use of cache data, then the back is crucial.

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);
OnSaveData (e);

/ / Refresh view
OnUpdateView (EventArgs.Empty);
Cancel UpdateCommand procedures of the current row in edit mode, and then open the Delete symbol visibility out. At this point, the table may be determined to have a change. Because the "update order" events in both cases (intention to save the changes to the existing time line; to insert a new line) under the excitation, so the conditions of meaningful form. Internal members InsertNewRow set by MustInsertRow reset by the DataSource, which help to determine what kind of situation. When the code dealing with the state of the grid, it inspired two consecutive events - an event so that the page be saved or inserted into the physical data source, and the other data binding refresh event. This explains why the process InsertData and only SaveData mainly through the implementation of SQL statement to update the physical.

InsertData and SaveData signature event handler with the same 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);

Discussed in this paper in the sample code, set up a number of assumptions, one of which is the assumption that the data source the grid is the "Data View" object. This has indirectly indicated that he did not support custom paging, or, more precisely speaking, we must be careful to modify this code to deal with such a grid. This applies equally to sort.

By the second key assumption is that the use of SQL update statements.

Design thinking is that no matter what kind of changes, a single SQL statement is always excited to apply it. EditableGrid here can not correctly designed to handle batch update. Incidentally, in my Building Web Solutions with ASP.NET and ADO.NET a book, in-place editing, when introduced, discussed in more detail in the grid for the advantages and disadvantages of the use of approved updates. However, if you are interested in using the grant to upgrade their technologies, please send an e-mail to me, I will in future columns are described in this topic.

As a result of your SQL statement through the direct (or data sources for direct statements to identify the contents) to update, so, "update order" event can be successful in order to refuse any changes. This is the grant program to update the main difference.

"Save data" and "insert data" event on behalf of the Executive to update the tasks necessary for a subset. In the implementation of the order, these procedures must ensure that the grid can refresh the data access and display. In this case, the need to update the cached copy of data. According to the basis of the database structure (or any automatic trigger any number of fields), may decide to completely re-read or update the cache based on the data in each field.

Let us take the time to learn how to edit from the grid template to retrieve the updated data. I consider making the control EditableGrid sufficient intelligence value to the Royal Philharmonic from the unit, filling them to the DataRow object to serve as event data. This method makes the grant program to update the code become insignificant. The reason why decided to let the ASP.NET page is responsible for extracting data is transparent so you can also edit the template support. I will show you the following in the template do not use any special code when necessary.

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 user input in the text box of information, use e.Item.Cells [n]. Controls [0] the expression, in which n is the index of the column (from 0). Please keep in mind, DataGrid in-place editing feature allows you to "read only" attribute set to true to be listed as a read-only.

Delete operation

Delete mode of operation of the Insert and Update the same operation. Automatically created order out of a Delete, click the button in the lead to stimulate ± events. Built-in procedures to repair the grid interface, and then followed one after another inspired DeleteData and UpdateView.

As a result of the invasion of deletion than insertion or update operation, and so you may want to use a client-side script code, and require users to confirm before continuing. My last month's column in the "dialogue field" part of the methods discussed. The source code of this month, one as in the figure below the actual realization.

Figure 3. To confirm the deletion of

Optimization of the final

I mentioned to support the edit template EditableGrid control. Let me prove this conclusion. The sample code in Step 2, I use the grid in a slightly different list.

<asp: boundcolumn runat = "server" headertext = "ID"
datafield = "employeeid" readonly = "true" />
<asp:templatecolumn runat="server" headertext="Name">
<% # DataBinder.Eval (Container.DataItem, "lastname") + "," +
DataBinder.Eval (Container.DataItem, "firstname")%>
</ itemtemplate>
<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, it shows in a single field name and surname. The editor of this column template (you must specify the column to the editor to edit the template) from the two side-by-side to provide the text box. The need to make any changes to class can be obtained as shown in Figure 4 example.

Figure 4. The use of edit template

On the other hand, in the use of retrieval from the text box to update the way the text, edit the template needs a little adjustment. Now, you can search by name of the control template. And suggested the reason could be to do so, because you know the control ID.

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 this kind of C++ # source code, ASP.NET pages as well as the two examples I used the Access database. However, you will not find compiled assembly. I provided a (very) simple batch file, you can use it, for with the beta 2 and later versions compatible with any version of the. NET CLR, the category has become executable code compiler.

Note If you download the correct run-time so that problems, please check the following steps:

• 1. Will become a CS-type assembly compiler. This can open a DOS box and run a batch c.bat in the ZIP file to complete, or in Visual Studio to create a new Class Library project and add files to the blank project to achieve.

• 2. Must be set so that procedures can be used for the sample ASPX page. Download documents, including the batch file to copy the DLL to c: \ inetpub \ wwwroot \ bin folder. If you happen to have a different path, please modify it. If you create a virtual directory, make sure to copy the DLL to the virtual sub-folder of the BIN folder, and not copied to the Web server root folder of the BIN.

• 3. According to ASP.NET security settings in Microsoft Access database with sample interaction may encounter difficult Updateable Query error. In this case, change the sample. MDB file security settings. In "Windows Explorer", select the documents to show the property, and then click the Security tab. Then, add \ ASPNET user and to ensure that it is entitled to write and modify the file.

• 4. Refresh ASPX page.

Dialogue column: create a custom template

In the last column, you discussed the Summary grid components. Are there opportunities to create or load custom summary template trip? This will allow in the ASPX file (not code) provides the design specifications.

Summary of trip to the use of custom template will undoubtedly be possible. The only question is how to achieve? Or, more precisely, the simplest method? I will introduce in the following several possible ways.

• Use pagelet: the application can modify the code to dynamically loaded controls pagelet (also known as user controls) - that is, ASCX file. ASCX file looks like a small Web form, and most of the contents of which are the layout of information. Page.LoadControl can be used to load the ASCX files by name, and then add it to the object DataGridItem a cell's Controls collection.

• Templates included: DataGrid template are all out of out. The use of ASP.NET tags and some of the code around the definition of each column. You can do is to block the structure is divided into two distinct and mutually exclusive part of - part of the general line for the other part for the summary line. In order to ensure that only show the first line, the control can handle the "visible" property and the property line with the definition of a summary of the conditions or general binding line.

• the preparation of the new controls: DataGrid derived from a new class and add a new template attributes. This allows you to use a custom sub-markings, in accordance with the importation of out almost the same manner as the template input line summary layout.

Back to top

.Net Control and components Articles

Can't Find What You're Looking For?

Rating: Not yet rated


No comments posted.