 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / January 2006Populate Strongly Typed Dataset - without "Configure Data Adapter"
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 > Hello > [quoted text clipped - 21 lines] > > Ian What we do, is create the stored procedures by hand :P 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 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 > interesting solution. > [quoted text clipped - 3 lines] > 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 Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.