Populate Strongly Typed Dataset - without "Configure Data Adapter"

[itinsley]

Hello

I have begrudgingly decided to use Strongly Typed Datasets but hate the
code that is generated by dragging and dropping ADO.Net controls and
using Configure Data Adapter and letting the 'Wizard' build my SQL, for
the following reasons:
* I don't want to drag/drop a Connection object on every
page/component, i want to handle connections my own way
* The Stored Procs it creates are practically unreadable
* The table mappings it creates ARE unreadable
* It is difficult to amend this code without using the Wizards - this
messes with your Source code and makes diff comparisons very difficult.

Can anyone recommend a way of populating Strongly Typed Datasets using
straightforward SQL and readable code without 6 levels of nested
mapping code about 400 characters wide. I can always create my own code
generators to create the SQL once i have a model to use.

The main requirements (which the 'wizard' handles for me) are:
* recognising update collisions from multiple users
* handling Identity columns and relationships

Any pointers greatly appreciated.

Ian

 

[Mythran]

Hello

I have begrudgingly decided to use Strongly Typed Datasets but hate the
code that is generated by dragging and dropping ADO.Net controls and
using Configure Data Adapter and letting the 'Wizard' build my SQL, for
the following reasons:
* I don't want to drag/drop a Connection object on every
page/component, i want to handle connections my own way
* The Stored Procs it creates are practically unreadable
* The table mappings it creates ARE unreadable
* It is difficult to amend this code without using the Wizards - this
messes with your Source code and makes diff comparisons very difficult.

Can anyone recommend a way of populating Strongly Typed Datasets using
straightforward SQL and readable code without 6 levels of nested
mapping code about 400 characters wide. I can always create my own code
generators to create the SQL once i have a model to use.

The main requirements (which the 'wizard' handles for me) are:
* recognising update collisions from multiple users
* handling Identity columns and relationships

Any pointers greatly appreciated.

Ian

What we do, is create the stored procedures by hand I created a stored
procedure generator that takes a table and generates the 5 primary stored
procedures (Fetch all, fetch by id, insert, update, and delete). The SQL
then, would be the name of the stored procedure. As for the SqlDataAdapter
and such...we don't use them. Well, we sorta do, we use the Patterns and
Practices Enterprise Library Data Access Library from Microsoft. We
modified the code for it to allow passing a typed data row as a
parameter...which would then have it's columns mapped to the parameters of
the stored procedure by column/parameter name mapping. This makes it a lot
easier to execute the stored procedures to perform all of our stored
procedure needs, with very little code needed in our Data Access layer.

HTH,
Mythran

 

[itinsley]

interesting solution.

What i get from this is that you gain simplicity by doing away with the
mapping code (you assume the parameters map to the columns) and
ignoring data collisions (handled in the ADO.Net code generated
solution by using the Original_ columns) or handling them differently
- i.e. through use of timestamp column.

Passing a typed data row as a parameter is in effect your Data Adapter,
though it would require multiple calls for each row as opposed to using
SQLDataAdapter which would manage this for you.

Do you match the columns and parameters dynamically at runtime or do
you include in your compiled code?

cheers

Ian

 

[Mythran]

interesting solution.

What i get from this is that you gain simplicity by doing away with the
mapping code (you assume the parameters map to the columns) and
ignoring data collisions (handled in the ADO.Net code generated
solution by using the Original_ columns) or handling them differently
- i.e. through use of timestamp column.

Using a column named "RowVersion" of type "Timestamp" In our update and
delete stored procedures, there are two parameters, TableId and RowVersion.
If no rows were updated/deleted, that means there was a concurrency
error....

Passing a typed data row as a parameter is in effect your Data Adapter,
though it would require multiple calls for each row as opposed to using
SQLDataAdapter which would manage this for you.

Yes. But we do not do much in the way of updating thousands of lines of
code using .Net. If we needed to do such a thing, we use DTS packages on
SQL Server If it's a one-time update of a million records, a quick
console app using SQLDataAdapter does the trick..

Do you match the columns and parameters dynamically at runtime or do
you include in your compiled code?

This is handled by the Enterprise Library Data Access Application Block. I
modified the application block to accept a "TypedParams" parameter for most
of their stored procedure methods (ExecuteScalar, ExecuteNonQuery, et
cetera). These overloads then take the columns from the TypedParams row and
maps them to the stored procedure parameters (by name, not ordinal). In
this case, consistancy is key. If we change a parameter name, and the name
of the table column is left alone, this will break. Therefore, shop
standards state that the parameter names must match table column names.

cheers
u2

Ian

Mythran