Simplify Your Code with Custom Fill Methods on DataSets
Entity Framework gets all the attention, but there's still something to like about ADO.NET DataSets. For instance, one reason that typed DataSets remain popular is that you can dramatically simplify your code by extending the DataSet with your own custom TableAdapter methods. All you have to be able to do is create an SQL statement in the Visual Studio query designer: Visual Studio will generate the code for you.
Ordinarily, to fill a table in a DataSet you need a half-dozen lines to open a connection, create an adapter (with its commands and parameters), set values in the adapter's parameters, call the adapter's Fill method, and then retrieve the resulting table (without, of course, forgetting to close the connection).
Update, insert, and delete statements aren't any simpler. What's especially irritating is that the code is virtually identical from one retrieval/update to another: really, only the SQL statement changes. With a custom TableAdapter method, though, you just call a method, passing the values required by your SQL statement.
When you create a typed DataSet, you get a TableAdapter with two methods on it for each table you drag into the DataSet: You get a Fill method that loads data into a DataTable but doesn't return anything, and a GetData method that fills the DataTable and then returns the DataTable to the calling code. As an example, here's the GetData method in action, retrieving all the rows in Northwind database's Orders table into a DataTable in a DataSet and then returning the DataTable:
Dim nwTB As New Northwind.OrdersDataTable
Dim nwtba As New NorthwindTableAdapters.OrdersTableAdapter
nwTB = nwtba.GetData
To create your own GetData, Fill, or update method, first right-mouse click on your table in the TableAdapters area at the bottom of the table and pick Add Query. That will bring up the query wizard where you can choose between using an SQL statement to retrieve your data, select from existing stored procedures, or create your own stored procedure.
Assuming you select Use SQL Statements, you'll be taken to a page that allows you to select the kind of SQL statement you want (everything from Select to Insert statements). After that choice, you'll be shown a default SQL statement you can modify. If you're not confident about your SQL writing skills, click the Query Builder button at the bottom of the dialog to be taken to Visual Studio's form-based query builder.
Once you've finished writing your SQL statement, the wizard will take care of writing all the ADO.NET code for you—all you have to do is name the method that will be added to your TableAdapter. For SQL statements, the wizard even generates a Fill and a GetData method. So to call a SQL statement that fills a DataTable with orders for a particular customer and hands the DataTable back to you, you just need this code:
Dim nwTB As New Northwind.OrdersDataTable
Dim nwtba As New NorthwindTableAdapters.OrdersTableAdapter
nwTB = nwtba.GetDataByCustomerID("ALFKI")
If you're paid by the hour, feel free to write the ADO.NET code. But this is the best kind of code-generation solution, because it allows you to concentrate on the one part of the code that matters -- the SQL statement -- while the boilerplate code is taken care of for you.
Posted by Peter Vogel on 10/09/2012