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

image 

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.

image

 

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

image

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

image

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.

image

 

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

image

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. 

image

That's it.  Enjoy!


TrackBack

TrackBack URL for this entry:
http://mattberseth.com/blog-mt/mt-tb.fcgi/119

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

Posted by: Dan on May 5, 2008 12:00 AM

Very nice... Thanks Matt.

Posted by: Pete on May 5, 2008 12:00 AM

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.

Posted by: Vish on May 5, 2008 12:00 AM

Hi Matt,
Yet again, bang on target.

Really, a very good article.

Thanks.

Posted by: Michael McGuire on May 5, 2008 12:00 AM

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

Posted by: Anthony on May 5, 2008 12:00 AM

Great stuff. If you could get it to accept multiple cells pasted from Excel, itd be outstanding!

Posted by: AsX on May 5, 2008 12:00 AM

Nice
is there a VB version

Posted by: Yubi on May 6, 2008 12:00 AM

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?

Posted by: JoeBo on May 6, 2008 12:00 AM

In a Borat Voice:

Very Nice...I Like

Posted by: Tim on May 6, 2008 12:00 AM

Whats the purpose of the delegate in the Save method?

Posted by: Agha on May 12, 2008 12:00 AM

Awesome!

Posted by: deep.snow on May 21, 2008 12:00 AM

You are legend!!!

Excellent Post. Clean and to the point.

Posted by: Zack Jones on May 22, 2008 12:00 AM

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,

Posted by: Chris on May 22, 2008 12:00 AM

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

Posted by: DLT on May 24, 2008 12:00 AM

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

Posted by: Arvind on July 28, 2008 12:52 AM

can u provide this example in VS2005

Posted by: Hutty on August 6, 2008 03:15 PM

Anyone has the code for CustomerDAO in VB.NET. I having a time converting. thanks.

Posted by: satish on August 12, 2008 11:51 AM

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...

Posted by: Hai Nguyen on August 18, 2008 01:50 PM

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.

Posted by: Yoganand on September 19, 2008 12:24 AM

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

Posted by: Hilarion Mamani on October 6, 2008 11:56 AM

Excelent Job.

My question is, how can I put a figure on the 2nd column after losing the focus of the 1st. column.

Posted by: hutty on October 6, 2008 09:51 PM

Hi,
I still can't the VB.NET version to work. Anyone have it?

Thanks

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Consulting Services

Yep - I also offer consulting services. And heck, I'll do just about anything. If you enjoy my blog just drop me an email describing the work you need done.

Recent Comments

  • hutty wrote: Hi, I still can't the VB.NET version to work. Anyone have it? Thanks ...
  • Hilarion Mamani wrote: Excelent Job. My question is, how can I put a figure on the 2nd column after losing the focus of th...
  • rtpHarry wrote: Hey is there any way to block the Google Autocomplete with this form? It makes a cool spreadsheet lo...
  • Yoganand wrote: Hi I have got same scenario but with independent of Datasoucre i mean it may be CSV file or Active D...
  • Martina wrote: I am working on a project that needs a new screen for bulk entry tasks....
  • Kenneth Scott wrote: Hey Matt- Check out my blog for a slightly modified version of your ListView concept. With mine th...
  • Hai Nguyen wrote: This is an amazing job....
  • satish wrote: hi matt, u r doing really great ..hats off... right now i am working on .net 2.0 where the req...