Strongly Typed DataSets and GridViews

If you are anything like me you don't enjoy writing 5 or 6 stored procedures for each business object in your solution.

If you do, then click here to read more about T-SQL!

For the rest of you I will add some notes about my recent experience with DataSets.

Within VS2005 (or the express versions) you can drag a database table onto a DataSet object (an .xsd file generate using the 'Add Item' menu).

This generates a DataTable and DataTableAdapter with the four standard SQL statements (Select, Insert, Update, Delete).

You can then easily link a GridView to this TableAdapter via an ObjectDataSource.

You then run the page it all looks fine, you click the Edit button and make some changes and then click Update.

ObjectDataSource 'ObjectDataSource3' could not find a non-generic method 'Update' that has parameters: TestString, original_TestId, Original_TestString.

Then you become less impressed, you have also clicked the Delete button first and noticed that it just didn't work.

At this point you might look at the SQL that is automatically generated....

UPDATE Test SET TestString = @TestString 
WHERE (TestId = @Original_TestId) AND (@IsNull_TestString = 1) 
AND (TestString IS NULLOR (TestId = @Original_TestId) AND
(TestString = @Original_TestString)

Hmm, I would have been happy with:

UPDATE Test SET TestString = @TestString WHERE TestId = @TestID

If you right-click and configure the tableadapter (from within the xsd file) accepting defaults all the way through then some SQL rather closer to my expectations is generated.

UPDATE Test SET TestString = @TestString WHERE (TestId = @Original_TestId)

You save the DataSet file and run again but still get the same error.

However, if you search for this error on google (thanks Barry) you will find that setting the Gridview column for the primary key to be readOnly=false this resolves the error.

At this point we have a TableAdapter working with a GridView. Its a shame that I couldn't leave out the steps above - but then I guess there would be no point to this article

To summarise, the TableAdapters work well but are let down by the default SQL generation. For all the discussion about SQL above I didn't have to hand craft any SQL statements to get this to work. But I did have to run through the configure wizard to generate useful SQL.

The ReadOnly parameter is a default of the GridView, but still you would expect the two to work together better than they do.

Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.


Michael Freidgeim said:

Please rate/validate the bug "Generated default Update method in DataTableAdapter in a typed DataSet shouuld not update primary key"

01/Mar/2007 11:30 AM

Add Comment


Your comment has been received and will be shown once it passes moderation.