Can someone please explain how to create a professional looking app that utilizes the full "power" of ADO.NET (1.x)?
I see all the beginner articles that show you how to drag a connection, and dataadapter onto a form, set some properties and bind to a datagrid. How does that help you create a real application??
If I wanted to have the users looking at a datagrid of data and allow them to change whatever field they wanted I would have given them Sql Server Enterprise Manager!
Writing your own code to create dataadapters, connections, commands, and parameters, and then all the stored procedure code is a lot of work. And putting that into something that looks halfway object oriented, and is DB independent (except for the sprocs) takes more than a bit of thinking the first time around.
Let's ask one of our data layer objects to give us some data. After all that work, what have we got? A dataset with one table of data. Where are the business layer objects everyone talks about? Anyone?
Forget business objects then. Let's just create an application layer that can pull the data we need for a specific task and merge that all into a single dataset then we'll start databinding like crazy. Textboxes, Labels, Lists, whatever you want. Now the user has made a change, how do we update that data? Send the dataset to the data layer!
But wait...All those dataadapters we created, they only work for a single table. It doesn't matter if you setup mutliple table mappings for queries that return mutliple result sets, the adapter is too stupid to use those mappings as parameters for a single stored procedure you write that just happens to update multiple tables.
Did I lose anybody there? How about an example. You have a many-to-many relationship. Happens quite often where I come from, perhaps because I prefer normalized databases? Suppose we model people and the activities they do throughout the day. We have a person table, an activities table, and a person_activities table to bridge the gap. I have to write a query that returns 2 (TWO!) resultsets in order for the activities and person_activities tables to be loaded with the activities performed by any given person. To be fair, table mappings on the adapter make it easy to fill the data set. However, if I write a stored procedure that inserts a brand new activity and ties it to a person's activity list and that takes a person's ID from the person_activities table in the dataset, and all the columns from the activities table in the dataset the dataadapter is clueless as for how to proceed. Even though I've given it all the table mappings it needs, and source column names in the parameter objects, it doesn't understand. I know this example is a bit contrived but there are real world scenerios where you need to update tables like those at the same time.
Another gripe I have is with the DataView. It supports the Parent() function for filtering but only Child() if it is part of an aggregate function. Why? This goes back to the many-to-many relationship. Suppose I want a DataView of all the activities a person has done, because I have multiple people and their associated activities all loaded in one dataset. I would like to tell the DataView to select activities where the activity ID and the person's ID are in the person_activities table. Something like:
activityView.RowFilter = "Child(rel_activity_person_activities).PersonID = " + ID.ToString();
I'm done ranting for now. If I've mispoken about any of ADO.NETs features, or lack thereof, please let me know.
I see all the beginner articles that show you how to drag a connection, and dataadapter onto a form, set some properties and bind to a datagrid. How does that help you create a real application??
If I wanted to have the users looking at a datagrid of data and allow them to change whatever field they wanted I would have given them Sql Server Enterprise Manager!
Writing your own code to create dataadapters, connections, commands, and parameters, and then all the stored procedure code is a lot of work. And putting that into something that looks halfway object oriented, and is DB independent (except for the sprocs) takes more than a bit of thinking the first time around.
Let's ask one of our data layer objects to give us some data. After all that work, what have we got? A dataset with one table of data. Where are the business layer objects everyone talks about? Anyone?
Forget business objects then. Let's just create an application layer that can pull the data we need for a specific task and merge that all into a single dataset then we'll start databinding like crazy. Textboxes, Labels, Lists, whatever you want. Now the user has made a change, how do we update that data? Send the dataset to the data layer!
But wait...All those dataadapters we created, they only work for a single table. It doesn't matter if you setup mutliple table mappings for queries that return mutliple result sets, the adapter is too stupid to use those mappings as parameters for a single stored procedure you write that just happens to update multiple tables.
Did I lose anybody there? How about an example. You have a many-to-many relationship. Happens quite often where I come from, perhaps because I prefer normalized databases? Suppose we model people and the activities they do throughout the day. We have a person table, an activities table, and a person_activities table to bridge the gap. I have to write a query that returns 2 (TWO!) resultsets in order for the activities and person_activities tables to be loaded with the activities performed by any given person. To be fair, table mappings on the adapter make it easy to fill the data set. However, if I write a stored procedure that inserts a brand new activity and ties it to a person's activity list and that takes a person's ID from the person_activities table in the dataset, and all the columns from the activities table in the dataset the dataadapter is clueless as for how to proceed. Even though I've given it all the table mappings it needs, and source column names in the parameter objects, it doesn't understand. I know this example is a bit contrived but there are real world scenerios where you need to update tables like those at the same time.
Another gripe I have is with the DataView. It supports the Parent() function for filtering but only Child() if it is part of an aggregate function. Why? This goes back to the many-to-many relationship. Suppose I want a DataView of all the activities a person has done, because I have multiple people and their associated activities all loaded in one dataset. I would like to tell the DataView to select activities where the activity ID and the person's ID are in the person_activities table. Something like:
activityView.RowFilter = "Child(rel_activity_person_activities).PersonID = " + ID.ToString();
I'm done ranting for now. If I've mispoken about any of ADO.NETs features, or lack thereof, please let me know.