Data Access using TableAdapter

Post By: Hanan Mannan
Contact Number: Pak (+92)-321-59-95-634
-------------------------------------------------------

Data Access using TableAdapter

This could also have been called “Data Access without having to write the SQL yourself”.  When I start coding a web application, I usually start with the database.  Creating tables, defining columns, creating indexes.  That’s the first step.
The third step is where some of the fun starts – you get to start creating entities and storing them in the database, searching for them, populating DataGrid’s with them, and so on…


But there’s that second step.  The one where you need to write some code to bridge between the rest of your app and the objects in the database.  That step is no fun.  It usually involves writing a bunch of classes, one for each table you have in the database.  There’s nothing really interesting or innovative about it; it’s just plumbing you’ve got to create.
Visual Studio 2005 can help here.
A new addition to Visual Studio is the TableAdapter object.
Let’s say you have a database with a table called Recipients, which looks like this:

Now you want to work with this data in your code.  You could start creating an OdbcConnection or SqlConnection and coding up SqlCommand’s, but lets see how the IDE can help here.  Right-click on the Project, and pick Add New Item… and add a DataSet named Recipients.xsd:

It’ll ask you if you want to put the code for it in the App_code folder; sounds like a good idea.
Next dialog lets you choose a database connection, or create a new one.  Database connections are defined using a Connection String, which identifies the database to connect to, the drivers to use, authentication, and other options.  Defining a connection string, storing it in a config file, and reading it and using it to initialize a connection is work you don’t have to do anymore, thanks to the new Data Connection dialog:

Use this dialog to locate your database and define a connection string for it.  This will create an entry in the web.config file (or app.config file):
<connectionStrings>
<remove name=”LocalSqlServer” />
<add name=”GiftsConnectionString” connectionString=”Data Source=Titan;Initial Catalog=Gifts;Persist Security Info=True;User ID=Rainbow;Password=secret;Pooling=False”
providerName=”System.Data.SqlClient” />
</connectionStrings>
The next question it asks is do you want to use existing stored procedures, create new stored procedures, or use SQL statements to define the DataSet.  For now we’ll use SQL statements:

Now the TableAdapter Configuration Wizard needs an SQL statement, so that it can execute it and use the results to generate the corresponding SQL to update, insert, and delete data.  But the point of this article is to use an SQL database without writing any SQL.. so click on the Query Builder.

Once you choose the table (Recipients) it is placed in the area at the top, where you can choose which fields you want the DataSet to contain.  Click the checkboxes in the Recipients box at the top to add them to the query.  Notice that the Filter is ‘= @Param 1′.  A nice shortcut here is that if you enter a ? in the Filter field, then the statement is adjusted so that the associated row is a paramter.  This makes sense in this case because Recipient_ID is the key that we’re going to use to identify Recipient rows.
Hit OK; Now we’re back in the TableAdapter Configuration Wizard, with the SQL filled in:

Click on Advanced Options.  Because we have an ID field that’s an Identify field (meaning it’s value is assigned by the database), the value we put there isn’t going to be stored in the database.  When we add a new Recipient, it’s ID will be the next available value.  To keep our DataSet in sync with the database, the generated class can query for it’s ID after inserting it if you turn Refresh Data Table:

You can click Finish at this point, to generate the DataSet.
If you look at the generated Recipients.xsd file, you’ll see it’s written the SQL for INSERT based on the SELECT statement that you built.
Now we can start writing code!
I added a “using RecipientsTableAdapters;” to the top of C# file for this example code to make it a bit less verbose.

        RecipientsTableAdapter adapt = new RecipientsTableAdapter();
        Recipients.RecipientsDataTable table = adapt.GetData(1);

That’s it – we’ve just retrieved the Recipients records which have a Recipient_ID of 1 (the paramter to GetData is the Recipient_UserID parameter that we defined). We can iterate through them using the typed RecipientsDataRow object:

        foreach (Recipients.RecipientsRow row in table)
        {
            Trace.Write(row.Recipient_Name);
        }
To add a new row:
        table.AddRecipientsRow(“Bob”, 100, 1);
        adapt.Update(table);

To remove a row:
        table.RemoveRecipientsRow(rowToRemove);
To update a row, modify the row object, and then call Update on the adapter.
The tedious part is done:  you now have a C# object that wraps the data access, and you didn’t have to write it.
You’re not quite done yet though – it’s not a good idea to use these objects throughout your code – they’re really just wrapping up the database access into a nice package for you.  You should still create an object that represents a Recipient that you can use throughout your code, and the Recipient object will take care of talking to the database.  This level of indirection will make the software much easier to maintain, especially if anything related to how the Recipient is stored in the database changes.