Bulk Inserting Data with the ListView Control
I am working on a project that needs a new screen for bulk entry tasks. We have a few business scenarios coming up where we have people inputting 5 to 8 records of data at a time. I met with a few of our analysts last week and hashed out a rough outline of what they were looking for. About 10 times or so during the 30 minute meeting some form of the phrase 'kind of like excel' was mentioned. Our data entry personnel are familiar with excel and our analysts thought an excel styled grid would make a lot of sense. 'An excel grid with a Submit button' was the phrase I had underlined twice in my notes.
I wasn't too worried about the grid's styling, but I wasn't quite sure what the best way to handle the 'bulk insert' requirement. Most of our other grids require an explicit mouse click to insert an item - and often times this click includes navigating the user to a separate page that is made to handle inserts. Of course for mass data entry tasks this can get a little old - tabbing through the cells is much nicer. So I took my notes back to my desk and started building a small prototype. You can check out the live demo to watch the grid in action. And a few of my implementation notes can be found under the screen shots below.
Live Demo (IE6, IE7, FF, Opera) | Download
Configuring the ListView
My excel styled grid only has one mode - bulk insert. It would also support mass updating as well, but for our use cases this is a one way data stream. I want to use the ListView because I like how the templates are structured, but the ListView doesn't natively support bulk inserting data (the ListView supports defining an InsertItemTemplate, but the ListView will only render this template once - so this template isn't useful for my scenario). I decided I would try using the regular ItemTemplate, but render elements that support editing instead of the usual read-only controls (i.e. TextBox's instead of Labels). I still use the Bind syntax because I want 2 way databinding, but because I am using the ItemTemplate for an insert scenario I will be responsible for telling the ListView when my data needs to be moved out of the controls and back to my data source. Below is the markup for my ListView.
Setting Up My DataSource
Next, I setup my data source. For this example I am using the ObjectDataSource control. When my data source is first bound to the ListView, I want to render placeholder objects for 8 records (i.e. I want my grid to show 8 empty rows by default). So when my Select method fires, I return a collection that contains 8 Customer objects that have their property values set to their default values (null strings for my example).
Next, I handle the Submit button's click event handler and loop through all of the ListViewDataItems and invoke the ListView's UpdateItem function which will cause the Update method on my ObjectDataSource to fire - moving the data back out from the controls and back into memory. Finally, after the data is moved back to memory, I persist the batch to the database (for the demo I am persisting it to memory, but when this page goes live it will obviously be put in the database).
To make sure I don't persist records with all empty values, I added a validation check to my Customer business object that makes sure the object has at least one data value before the row is submitted. If validation passes, I move the customer data into the database.
Toggling the DataTable's Skin
For a bit of flair I included a couple of radio buttons for changing the grids theme from the default 2003 style excel shown below (found a nice article here on creating this skin) to the 2007 version which is a bit softer (see screen shot at the beginning of the article).
To change the grids skin I attached event handler's to the radio button's click events and use the Sys.UI.DomElement.addCssClass and removeCssClass to toggle the CSS class that is applied to the grid. Below is the JavaScript that does this bit of work for me.
That's it. Enjoy!
Comments
Another excellent post! Its nice to see you "express yourself" and not just settle for the bare minimum amount of flair ;)
Now excuse me while I go watch that movie!
Great Article...I learn a lot Thanks :)
Very nice... Thanks Matt.
Great article.
Very clean and straight forward.
Pete
Once again, your posts are incredibly useful; well thought out and applicable to real-life issues.
Keep it up.
Hi Matt,
Yet again, bang on target.
Really, a very good article.
Thanks.
Matt,
This is a great example, and something I was working on myself. Im a bit confused with your use of delegates however in the Javascript code. It was my understanding that the main reason for wrapping delegates in Javascript was to allow for disposing and to allow instance methods to serve as delegates. Since you arent using the returned delegate at all from the Function.createDelegate() call, Im not sure what the point of using it is. Seems like the following would be equivalent (with less code):
$addHandler($get(rdoOffice2007), click, onSkinChanged());
$addHandler($get(rdoOffice2003), click, onSkinChanged());
Great stuff. If you could get it to accept multiple cells pasted from Excel, itd be outstanding!
Nice
is there a VB version
Thanks again for another neat example Matt. The excel skinning functionality takes this yet another notch in being user-friendly. I was thinking of how to modify this for export to Word/RTF format?
In a Borat Voice:
Very Nice...I Like
Whats the purpose of the delegate in the Save method?
Awesome!
You are legend!!!
Excellent Post. Clean and to the point.
Great article! So at your meeting did you guys get to enjoy some extreme fajitas or pepper poppers?
Hi Matt,
Awesome post! I just have few questions.
1) Lets say that the user has already inserted the customers data and then they pull up the ListView control and it is populated with the data from the database. Everything is good! But now the user makes changes to only 1 row in the ListView control. Are you going to go and update all the rows even though only a single row has been changed? How do you deal with this issue?
Thanks,
Awesome! Terrific job Matt!
Chris
I wrote a post on how to get only the updated rows from the GridView control:
http://geekswithblogs.net/AzamSharp/archive/2008/05/22/122322.aspx
Matt,
Just a heads up: readers should know that Container.DataItemIndex returns the index position in the entire dataset, which is not what you want if you are using paging and you have not used a sproc that pulls only one page of value at a time. In this case you need to use Container.DisplayIndex to get the row position on the page being displayed / updated. If your PageSize is 10 and youve edited the first record on the second page, DataItemIndex returns 10 while DisplayIndex returns 0, and since each page only has index values of 0-9, you get an index out of bounds error if you use the DataItemIndex to find a DataKey value to use in your update code. I learned this the hard way.
Very cool work.
how can i incorporate ajax functionality ?
Thanks
Munna
www.munna.shatkotha.com
www.munna.shatkotha.com/blog
www.shatkotha.com
@Michael McGuire - I use Function.createDelegate out of habbit. You are right - this is not needed.
@Josh - Nothing like slipping in an Office Space reference ;)
@Mohammad Azam - Nice link. Thanks.
@DLT - Good tip. I have been misusing this. Thanks for not making me learn the hard way too.
Hi,
I am usig ListView control for paging using LINQ. But I am unable to get it. I am getting the below error. I am not using any dynamic controls in my screen.
If I am double clicking the paging button then i am able to see the second page records but if i click for the third time its showing the below error.
Microsoft Jscript runtime error:
Sys.WedForms.PageRequestManagerServerErrorException: Failed to load viewstate. The control tree into which viewstate is being loaded must match the control tree that was used to save viewstate during the previous request. For example, when adding controls dynamically, the controls added during a post-back must match the type and position of the controls added during the initial request.
Any one please help me solve this problem. Thanks in advance.
Thanks and Regards
Bharath Reddy VasiReddy
can u provide this example in VS2005
Anyone has the code for CustomerDAO in VB.NET. I having a time converting. thanks.
hi matt,
u r doing really great ..hats off... right now i am working on .net 2.0 where the requirement is very similary like this.. but one problem is the excel sheet which the business people had referred has 62 columns in one row and if suppose 10 rows are there then the grid view is taking hell lot of time to load... is the list view will help me anywhere...
This is an amazing job.
Hey Matt-
Check out my blog for a slightly modified version of your ListView concept. With mine the rows are added/deleted dynamically - and state is saved via an overriden OnItemCommand in an extended ListView class.
I included plenty of links back to your original awesome work - hope you don't mind.
Keep up the great work-
I am working on a project that needs a new screen for bulk entry tasks.
Hi I have got same scenario but with independent of Datasoucre i mean it may be CSV file or Active Directory or XML etc..
I have impmented very nicely using LINQ and just check the code which i written extension method to my DataContext for Bulk insert using Linq To SQL. For that i have created two helper classes namely SqlBulkCopy and LinqBulkCopyReader.
public static class TMSDataContextExtension
{
public static void BulkCopy(this Table table, IEnumerable entities ) where TEntity:class
{
SqlBulkCopy bulk = new SqlBulkCopy(global::DataLayer.Properties.Settings.Default.TMSConnectionString);
LinqBulkCopyReader reader = new LinqBulkCopyReader(entities);
foreach (var column in reader.ColumnMappingList)
{
bulk.ColumnMappings.Add(column.Key, column.Value);
}
bulk.DestinationTableName = reader.TableName;
bulk.WriteToServer(reader);
}
}
I am using the above method i such way like
ICollection data = reader.Parse();
TMSDataContext tms = new TMSDataContext();
tms.Users.BulkCopy(data);
tms.SubmitChanges();
Hey is there any way to block the Google Autocomplete with this form? It makes a cool spreadsheet look all dodgy because the yellow bg doesnt go all to the edges (in FF at least).
Excelent Job.
My question is, how can I put a figure on the 2nd column after losing the focus of the 1st. column.
Hi,
I still can't the VB.NET version to work. Anyone have it?
Thanks