Office Test - Tutorial 9

The 9th page of the OfficeTest General Tests tutorial looks at using Strongly Typed DataSets and ObjectDataSource in place of SqlDataSource. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

ObjectDataSource
ObjectDataSource is a replacement for SqlDataSource. Where SqlDataSource provides a direct link to the database through SQL, ObjectDataSource provides a connection to more abstract data classes. TableAdapter, DataTable and properly defined custom data classes can be linked to an ObjectDataSource and provide method calls that perform Inserts, Updates, Deletes and Selects. This provides a middle tier so that the interface is not so directly linked to the database through SQL calls. The Insert, Update, etc. methods can contain additional C# logic and do not even need to work with a database or possibly could even work with multiple databases.

To configure the ObjectDataSource just drag and drop one on your web page and then right-click and select "Configure Data Source" just as you would with a SqlDataSource. You will get the following dialogs:

Configure ObjectDataSource

Rather than choosing a connection string you will instead pick your custom data class, TableAdpater, DataTable etc..

Rather than enter a SQL statement instead you select the appropriate method from the data object you chose in the previous step.

The end result will generate HTML code that looks something like this:

The TypeName represents the data object and then each of the method names follow. Parameters are automatically added matching the parameters for each of the methods.

Configuring the ObjectDataSource once your data object is created is pretty straight forward. Now we'll spend some time looking at the different types of data objects it can work with. In this tutorial we'll cover Visual Studio's built in Strongly Typed DataSet design. In later tutorials we'll discuss building our own more robust business objects.

9. Strongly Typed DataSets.

To add a new DataSet schema to your application go to the solution explorer and right-click on the App_Code directory if you have one. If you don't then you can do the same from the root solution branch as well. Select "Add New Item" and then from the dialog pick "DataSet". When the page for the schema appears you'll be asked to select a connection string or database connection.

Initial Setup
Once you've supplied a connection Visual Studio will start you through the process of creating your first TableAdapter. You can also right-click on the page background and select "Add" and then "TableAdapter", from the menu, to create a new one at any time.

1. Select SQL

First you need to supply a select statement for the dataset. Optionally you can click the query builder button to help build the SQL.

2. Get/Fill Methods

Then you'll be asked to choose your Fill (retrieves all records into a dataset supplied as a parameter) and GetData (returns a DataTable object filled with all records) methods. The defaults for this dialog are generally what you'll want. Although, if the database table does not get modified from you web site, the last checkbox can be unchecked and no Insert, Update or Delete methods will be generated.

3. Finish

The last dialog just lets you know what was generated.

That's really all there is to it unless you want to add additional selects and filters. The Insert, Update and Delete commands are built automatically using the columns and table name provided in the select SQL. In my case I noticed they kept the " (quotes) around the column and table names when the Insert, Update and Delete commands were generated, which is good.

Adding a single row select
I wanted to add a single row select since that is how I normally keep my GridView and DetailsView synchronized. To do this right-click on the TableAdapter image in the schema and select "Add" and then "Query" from the menu.

SQL/Stored Procedure

Choose the method you want for selecting the data row.

Type of Query/SQL

Choose the query type. In our case we want the first option. We only want 1 row but its still a result set of 1 we want. The 2nd option is only for aggregate SQL statements (like "select count(*) from MyTable").

Add the SQL and Parameters

Enter the new query and remember to use bind parameters so we can filter to a single row.

Method Name

Finally we can add a method name that has some meaning for us. In this case adding "ByID" on the end of the Fill and GetData let us know this query selects only by the primary key. We could use any name we want of course.

Employee/BillingType DataSet Schema
My final schema for this web page, and others we'll be looking at, looked like this:

In some ways this is actually quite handy. It provides a relatively simple and fast way to generate methods we can use to work with our data. In addition we get a nice visual picture of the schema including the names of all the methods each table will be using. It even provides relationship information as foreign keys. Having said that I should also point out that this is not really the best use of ObjectDataSource and I would actually never use Strongly Typed DataSets in any real web site.

A look at the code
First off if you look at the BillingTypeStronglyTypedDataSet.aspx.cs unit you'll notice there is no code at all. No code is really required for our example and even the minimal code we had in the other web pages for the Firebird parameter binding quirks are not required here.

If you go back to the DataSet Schema page and right-click on the page and select "View Code" another editor tab will open with the dataset code. Notice this is just more XML tags and parameters. While this is nice and simple it is also the biggest drawback of using this method. If real C# business objects were created then we would have something to work with for adding more advanced features or real data layer abstraction. The XML really gives us nothing to work.

Why ObjectDataSource?
One of the best reasons to use ObjectDataSource is to provide a middle tier to your application. The interface (HTML) and code directly bound to it through events and callbacks represents your first tier. Normally with SqlDataSource this code also includes your SQL and connection information. The Database would represent your 2nd tier and since the SQL is embedded in the first tier they are directly and inseparably linked.

ObjectDataSource provides a simple mechanism that allows the code to work with 3 or more tiers. Again the interface is your first tier. The interface does not contain SQL or connection information but rather just binds to business object methods. The business objects represent your 2nd or middle tier. Those objects supply data to the interface through parameters, classes and objects that are not directly related to the database. The business objects contain the connection code and SQL (or whatever data storage access code is required) necessary to talk to the database which is now the third tier.

This sort of design adds a layer of abstraction between the interface and the database. At any time the database could be changed from SqlServer to Oracle or Firbird or a flat file and the interface doesn't need to know or change. Only the connection and SQL code on the business objects needs to be modified. Also since we have a lot of control over that code we can add logic allowing it to support multiple types of data storage - such as both Oracle and Firebird at the same time.

Creating datasets (Strongly Typed DataSets) the way we do in this tutorial while fast, simple and visual does gain any of those benefits. The SQL is separated from the interface directly but is still tightly coupled with the DataSet XML and does not provide enough flexibility to handle complex data or multiple data storages. There is also no real way to add object factory style functionality to these datasets which is common in large scale applications. While not obvious when you first look at the DataSet designer it is actually possible to extend Strongly Typed DataSets. The tutorial following this one will discuss briefly how you do this but in the end it doesn't change my stance on this method of development.

See next Tutorial...Employee: Strongly Typed DataSets web page which provides a slightly more complex example of Strongly Typed DataSets with relations and ObjectDataSources.