Recently I needed to display a "parsed out" datatable that was created from an uploaded text configuration file and make this data editable in an ASP.NET DataGrid prior to the user submitting the changes for either update or insert into a custom table. I was actually forced to reconstruct and improve on some test code that had been created by a developer who was no longer around (and, as you can guess, neither was his code - sound familiar?).
As I remember, the other developer had struggled with the idea of creating a DataGrid where all the rows are editable at the same time (with one master "Update all" button) but he gave up and he finally decided to dynamically create a big HTML Table with all the rows and columns in one big "submit form" on the page.
However, this is a fairly common requirement, and I was sure that it could be done more easily, and a useful infrastructure for "code re-use" built at the same time, so I created this "all rows editable at the same time" DataGrid. Combined with some specialized code to synch - up DataGridItem objects with the DataTable that underlies them as the DataSource, it was a simple matter to then pass this updated DataTable into my custom "DAHandler" class which I've featured on at least one other article here, and all the changes would be persisted to the SQL Server data store in one single "Update All" button click.
For purposes of this example I've used the ubiquitous Northwind Database, which is present on almost everyone's machine. If you don't have it, or you've messed yours up (as I have) with all your "experiments", here is a link to a fresh copy.
I've also included the Anthem.Net library and wired up the DataGrid and the Update buttons with an Anthem Panel and Anthem Button controls just to show you how easy it is to "Anthemify" (AJAX - IFY?) your stuff so there's no visible postback or page reload. Kudos to Jason Diamond for Anthem, I've been peripherally involved in his project even before it got promoted to Sourceforge.net. Anthem.NET is available for both ASP.NET 1.1 and 2.0, and is very easy to learn. You can also write Anthem - enabled custom controls very easily, thanks to Jason's expert and very compact approach that preserves the stateful Page model, unlike some other "AJAX" libraries out there.
My grid example here keeps it simple, and so I only display the FirstName, LastName, HomePhone, and the EmployeeID (which is not editable). The trick to getting all the rows of a DataGrid to display as "edit rows" is to put all the editable columns in as ItemTemplate columns, not as "EditItemTemplates":
<Columns>
<asp:BoundColumn DataField="EmployeeId" HeaderText="EmployeeId"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="LastName">
<ItemTemplate>
<asp:TextBox EnableViewState=True id=LastName runat="server"
Text='<%# DataBinder.Eval(Container, "DataItem.LastName") %>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="FirstName">
<ItemTemplate>
<asp:TextBox ID="FirstName" enableViewState = true runat="server"
Text='<%# DataBinder.Eval(Container, "DataItem.FirstName") %>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="HomePhone">
<ItemTemplate>
<asp:TextBox ID="HomePhone" enableViewState = true runat="server"
Text='<%# DataBinder.Eval(Container, "DataItem.HomePhone") %>'>
</asp:TextBox>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
With that taken care of, it is a simple matter to bind our grid and have it come up in a "100%" editable state:
 |
Now on to the "cool stuff". When we edit an item in a row, we need a way to synchronize the edited items in the underlying DataSource, which in this case is a DataTable. Here is The code that I use to do this:
private void UpdateDatasetFromGrid()
{
foreach(DataGridItem itm in DataGrid1.Items )
{
foreach (DatasetColumnNameToGridColumnIndex ColName in System.Enum.GetValues(typeof(DatasetColumnNameToGridColumnIndex)))
{
UpdateDataTableFromGrid(dt, ColName.ToString(), itm, (int)ColName);
}
}
DataGrid1.DataSource=dt;
Session["dt"]=dt;
DataGrid1.DataBind();
}
private enum DatasetColumnNameToGridColumnIndex: int
{
LastName = 1,
FirstName = 2,
HomePhone = 3
}
private void UpdateDataTableFromGrid(DataTable DataTable, string DataTableColumnName, DataGridItem DataGridItem, int DataGridCellIndex)
{
if(dt==null) dt= ((DataSet)Session["ds"]).Tables[0];
foreach (Control control in DataGridItem.Cells[DataGridCellIndex].Controls)
{
if (control is CheckBox)
{
this.dt.Rows[DataGridItem.DataSetIndex][DataTableColumnName] = ((CheckBox)control).Checked;
System.Diagnostics.Debug.WriteLine( "SET "+control.ID + " TO: "+ ((CheckBox)control).Checked.ToString());
break;
}
else if (control is TextBox)
{
try
{
this.dt.Rows[DataGridItem.DataSetIndex][DataTableColumnName] = ((TextBox)control).Text;
System.Diagnostics.Debug.WriteLine( "SET "+control.ID + " TO: "+((TextBox)control).Text);
}
catch(Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message +ex.StackTrace);
}
break;
}
}
}
What this does is to iterate all the DataGridItems in the Grid, and for each editable column in the enum (FirstName, LastName, and HomePhone) to get the value of the underlying editable control (TextBox or Checkbox) in the grid, and assign it's value to the corresponding Row[columnName] of that row in the DataTable. Now our Datatable is synched - up with any changes we've made, and we can simply pass it into my DAHandler static method that automatically wires up the DataAdapter commands and performs the update for us.
To add a new "row" to the grid, I use code like this:
private void Button2_Click(object sender, System.EventArgs e)
{
DataTable dt = (DataTable)Session["dt"];
DataRow row;
row = dt.NewRow();
row.ItemArray = dt.Rows[0].ItemArray;
row["EmployeeID"]=DBNull.Value ;
row["firstname"]="";
row["LastName"]="";
row["HomePhone"]="";
row["Title"]=System.DBNull.Value ;
row["TitleOfCourtesy"]=System.DBNull.Value ;
row["BirthDate"]=System.DBNull.Value ;
row["HireDate"]=System.DBNull.Value ;
row["Address"]=System.DBNull.Value ;
row["City"]=System.DBNull.Value ;
row["Region"]=System.DBNull.Value ;
row["PostalCode"]=System.DBNull.Value ;
row["Country"]=System.DBNull.Value ;
row["Extension"]=System.DBNull.Value ;
row["Photo"]=System.DBNull.Value ;
row["Notes"]=System.DBNull.Value ;
row["ReportsTo"]=System.DBNull.Value ;
row["PhotoPath"]=System.DBNull.Value ;
dt.Rows.Add(row);
dt.AcceptChanges();
DataGrid1.DataSource=dt;
DataGrid1.DataBind();
Session["dt"]=dt;
}
This creates a new "blank" row that we can fill in. This has been added to the DataTable, and since its RowState is "Modified" after we have "filled it in", it will be inserted when we do the database update; in my database I have a single UpdateEmployee stored procedure that actually performs both updates and inserts, like so:
CREATE PROCEDURE dbo.UpdateEmployee @EmployeeId int , @LastName nvarchar(40), @FirstName nvarchar(25), @Title nvarchar(25), @TitleOfCourtesy nvarchar(25), @BirthDate datetime, @HireDate datetime, @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @HomePhone varchar(25), @Extension nvarchar(4), @Photo image, @Notes ntext, @ReportsTo int, @PhotoPath nvarchar(255) AS if NOT EXISTS( Select * from employees where lastname = @lastname and Firstname = @firstname) BEGIN insert into Employees ( LastName , FirstName , Title , TitleOfCourtesy, BirthDate , HireDate , Address , City , Region , PostalCode , Country , HomePhone , Extension , Photo , Notes , ReportsTo , PhotoPath ) VALUES ( @LastName , @FirstName , @Title , @TitleOfCourtesy, @BirthDate , @HireDate , @Address , @City , @Region , @PostalCode , @Country , @HomePhone , @Extension , @Photo , @Notes , @ReportsTo , @PhotoPath ) END ELSE BEGIN UPDATE EMPLOYEES SET LastName=@lastname , FirstName =@FirstName, Title =@Title, TitleOfCourtesy=@titleofCourtesy, BirthDate=@birthdate , HireDate=@hiredate , Address =@address, City =@City, Region=@region , PostalCode =@PostalCode, Country =@country, HomePhone=@homephone , Extension =@extension, Photo =@photo, Notes =@notes, ReportsTo =@reportsto, PhotoPath =@photopath WHERE EMPLOYEEID =@EMPLOYEEID END
The syntax for the static method in the DAHandler ("DataAdapter handler") class is as follows:
public static bool SubmitChanges( DataTable tbl, string ConnectionString, string spUpDateName, string spInsertName, string spDeleteName)
What this method does is to pass the connection String and the stored procedure name to the SqlCommandBuilder Class's DeriveParameters method, and assuming that each parameter name is the same as it's related table column, it simply chops off the leading "@" sign in the parameter name to get the source column, and returns the Update, Insert, and Delete commands for the entire DataTable to be passed into a SQLDataAdapter. The DataTable, which is a reference type, can then be inspected for any Row Errors, and your update is done. There is no need to create a connection, or a DataAdapter, or commands, or any parameters. Just pass in the above items and you did it. If one of the spNames is a null string, the DaHandler class simply ignores it and doesn't create that particular command.
And that wraps up the "All rows editable" DataGrid. The Grid itself in the example download is enclosed inside an Anthem Panel control, which takes care of all the fancy Remote Scripting ("AJAX" if you must) calls behind the scenes. If your boss says she wants you to use "AJAX" this is probably the easiest way to impress her, without having to write any code -- unless of course you are using ASP.NET 2.0 in which case you could choose to use the ATLAS Framework ( or just the 2.0 version of Anthem.net).
Of course the inevitable question will arise, "How do you delete a row"? The answer is, the way I have it set up, you don't. I simply didn't need this functionality. I leave this enhancement to the reader. One relatively easy way would be to add an extra hyperlink column "Delete" that would allow you to get the DataGridItem that generated the event, find out which row in the underlying DataTable it represents (using code that is essentially already in this project) and then delete it from the DataTable. If you have a delete stored proc for the DAHandler class, then your deletes will go through just fine.
The sproc, Anthem.NET library, my "DAHandler" utility class, and all the code are in the downloadable Visual Studio Solution here. All you may need to do is change the connection string in your appSettings web.config section. I have also created an ASP.NET 2.0 version you can download here. This version uses the 2.0 Anthem library and it is set up as a Web Application Project, which you'll need to download if you haven't gotten the add-in yet. And by the way, Mr. ScottGu, if you happen to be reading this, I cannot describe how pleased I am to see this new Web Application Projects add-in finished and code-complete!
No comments:
Post a Comment