Home > 2010 Winter BTI420 > Introduction to a Data Access Layer for BTI420 students

Introduction to a Data Access Layer for BTI420 students

February 21, 2010 Leave a comment Go to comments

Creating a Data Access Layer – also known as a "DataSet" – enables you to work with a database in an object-oriented manner. You will learn concepts and skills that are foundational to creating high-quality applications in the future.


Last week, you were introduced to data access, by using the SqlDataSource object. That object was a thin wrapper for common database operations. Learning about and using a SqlDataSource was a good first step in learning data access.

The next step is to learn the technology that you will use from now on – the DataSet, which enables you to implement a Data Access Layer.


Software "layers" or "objects"

Last week, you saw diagrams that illustrated the objects that implemented data access in a typical ASP.NET data-driven application. A reminder:

Data display control on a web form < — > Object Data Source < — > Data access layer (DataSet) < — > SQL Server database

The diagram showed you one commonly-used abstraction of the objects. Another commonly-used abstraction is to think of the software objects as "layers". Doing so would result in the following view or abstraction:

  • Data display control on a web form, which interfaces with…
  • ObjectDataSource, which interfaces with…
  • Data access layer (DataSet), which interfaces with…
  • SQL Server database

A Data Access Layer – a DataSet – is an item that you create in your App_Code folder.

It includes class definitions (TableAdapter, DataTable, DataTableRow, etc.). Instances of a class (e.g. an object) can include methods that wrap common operations on data.

A DataSet is available to ALL resources in your web site. Any web form can use the DataSet to perform data access operations. (Compare that with the simple "SqlDataSource" object, which is dedicated to a specific web form.)

Visual Studio has a graphical DataSet Designer that helps us create and modify a DataSet.


Create a DataSet

Typically, you create a DataSet as a data access layer to the data that your application needs. In your situation, we recommend the following:

  • Create a DataSet to work with your copies of the the Northwind tables
  • In the near future, create a DataSet to work with your assignment’s tables
  • In general, create a DataSet for each database that you use (although you can create more if the database is large) 

Create the myNorthwind DataSet

In Solution Explorer, right-click your App_Code folder, and Add New Item. Choose DataSet, and name it "myNorthwind".

A DataSet Designer surface opens, ready for the next step.


What can you add to the DataSet design surface?

We generally create two kinds of objects:

  • Table adapters
  • Query adapters

Here’s how MSDN describes a table adapter:

[A] TableAdapter connects to a database, executes queries or stored procedures, and … returns a new data table populated with the returned data… .TableAdapters are also used to send updated data from your application back to the database.

Users … can think of a TableAdapter as [an object] with a built-in connection object and the ability to contain multiple queries. Each query added to a TableAdapter is exposed as a public method that is called like any other method or function on an object.

[You] can have as many queries as you want on a TableAdapter as long as they return data that conforms to the same schema.

So… a table adapter is an object with:

  • A schema definition (the layout of the data)
  • A main query, and additional queries
  • A built-in database connection

The following diagram shows a Northwind Customers table adapter, with explanatory notes:


A query adapter is similar to a table adapter, but its queries typically perform operations that do not require a table to be returned. For example, counting the rows in a table, or updating all rows of a specific table column, etc.


What table and query adapters should be created?

Follow these guidelines to help you with this question.

Create a table adapter that includes all table columns. Use this table adapter for operations where you need to work with all columns. For example, a GridView or DetailsView enables you to display all columns. This kind of table adapter is also useful when inserting and updating.

Note – A "main query" is created with a new table adapter. It supports the return of a data table object, and the ability to receive a table object for bulk insert/update/delete operations. You will be able to create additional queries to support other data manipulation operations.

Create a table adapter for list web server controls (e.g. ListBox, DropDownList, etc.). This kind of table adapter will typically have about two columns – a column that contains the descriptive text that will appear in a ListItem’s Text property (and therefore be visible in the list), and a column that contains the key; this would become the ListItem’s Value property, and not be visible, but it could be used for further processing.

Create a query adapter when you want to perform non-table operations.


Table adapter naming convention (suggestion)

A typical DataSet can include many table adapters. To help you quickly determine their purpose, we suggest a naming convention:

For all-column table adapters, use a "Table_" prefix.

For table adapters that target list web server controls, use a "List_" prefix.

FYI – Query table adapters are auto-named "QueriesTableAdapter".


A table adapter’s "main query"

A typical table adapter includes a "main query", which gets created with the table adapter. It defines the schema for operations on the table.

The main query typically defines two methods:

  • GetData()
  • Fill()

The GetData method

GetData is designed to return the query results to you as a data table.

The data table is strongly typed. This enables data-bound controls (e.g. GridView, DetailsView, etc.) to render column names, column types, and so on, in a strongly-typed manner.

The Fill method

Fill is designed to accept a data table argument and perform bulk operations (insert/update/delete) on the database.

As above, the data table is strongly typed. This also enables a data-bound control that supports editing (e.g. GridView, DetailsView, etc.) to render content in a strongly-typed manner, and accept user input in the same way.

Additional methods

When you want to perform additional operations with the table adapter, beyond the main query’s task, you will create additional methods.

For example, what about returning a specific row, or rowset, that matches a "WHERE" clause?

To address this need, you can create an additional method.


Replaceable query parameter syntax

The paragraph above asked about returning results that match a "WHERE" clause. How do you express that in the table adapter?

All replaceable query parameters use the same syntax: An "at" sign – @ – is used as the prefix for the column name in the "WHERE" clause. For example, assume that you want your "WHERE" clause to to focus on a specific "SupplierID" (which is a column name). The SQL Server syntax will be:

SELECT * FROM nwProducts WHERE SupplierID = @SupplierID

Replaceable parameters become method arguments. Cool, huh?


Method naming convention in table adapters (suggestion)

If you’re adding a method to a table adapter, the wizard will ask you for a method name. Typically, you’re adding methods to support selection, as described in the previous section.

The pattern is to construct the name as follows:


The "Criteria" part is terminology supplied by you that matches the "WHERE" and "ORDER BY" criteria. For example, using the previous section’s language, the method name could be:



Pathway through the TableAdapter Query Configuration Wizard  (main query)

We will do this during the lecture a few times. You have to master this for this week’s test.

Right-click the DataSet Designer surface, and choose Add > Table Adapter. The following appears:


Choose your data connection, or create a new connection if you have to. Click Next. You now get to choose your "Command Type".


Our initial examples will "Use SQL statements". Click Next. You now get to "Enter a SQL Statement". Before we do, click the "Advanced Options…" button in the lower left. It shows the following:


For the main query, it’s OK to leave the first box checked. Click OK to return to the wizard.


You can enter SQL, or press the "Query Builder…" button in the lower-right. We’ll do that during the lecture. Here’s the initial Query Builder dialog:


Here’s the Query Builder dialog after selecting all the nwCustomers columns, sorting by CustomerName, and clicking the "Execute Query" button (to test the query):


When you click OK, it returns to the wizard, and shows you this dialog:


Click Next. Choose the defaults, as indicated below:


A "Wizard Results" dialog appears:


You will now see the table adapter on the DataSet Designer surface.

Rename both headings in the table adapter to conform to the "Table adapter naming convention" section above. The image below, on the left, shows the wizard-provided name. The image below, on the right, shows the suggested name, with the "Table_" prefix, and the "TableAdapter" text removed from the queries header.



Pathway through the TableAdapter Query Configuration Wizard  (additional query)

We will do this during the lecture a few times. You have to master this for this week’s test.

The important ideas are to know what your additional query will do, and make sure the naming convention you use is compatible with the intended use.


Using table adapter queries in the web form designer

We will do this during the lecture a few times. You have to master this for this week’s test.

For this example, we will create a web form that has a GridView data display control. We will use the task panel pop-up to define and configure a data source (our table adapter).

Begin by creating a new web form. At the location where you want the GridView to appear, go to the Data section of the Toolbox, and place a GridView on your form. A GridView placeholder appears, with the task panel open:


In the Choose Data Source drop-down list, select <New Data Source…>. The Data Source Configuration Wizard starts. It asks you where the app will get its data from:


Select "Object". If you plan to have more than one data source on the web form, then change the generic "ID" (name) to something that makes sense (e.g. "dsForGridView"). Click OK, and you get to choose the object:

In the drop-down button, select the table adapter you created above. Its name on the list appears as a two-part name:



Click Next, and you get to define the data-handling methods. The wizard looks at the table adapter’s methods, and attempts to match them up to common data operations:


Click Finish, and you’re done – the GridView is now configured with its data source. Notice that the GridView placeholder now has the actual column names, and renderings of their data types. At this point, the web form will run, and display your data.


Using table adapter queries in C# code-behind

We briefly saw how to do this during the lecture. After study week, we’ll do this more.


What about related tables?

A DataSet supports operations on related tables. We cover this in more detail after study week.


Master-detail example during the lecture

You may remember the exception (error) that happened when we constructed a master-detail page. The scenario was that the rendering of a "detail" control (like a DetailsView) caused an exception if the "master" control (like a drop-down list) did not have an item selected.

This scenario works fine when using a SqlDataSource control (as we learned last week). The behaviour of the ObjectDataSource is a bit different than the SqlDataSource.

However, we can fix this, with a simple property change. When you are configuring the ObjectDataSource using the wizard, you get to the "Define Parameters" wizard page. That’s where you specify that the parameter’s source is a control, and so on.

On the "Define Parameters" wizard page, click the "Show advanced properties" link:


The advanced properties appear. To see them better, resize the vertical height of the dialog:


The default setting of "ConvertEmptyStringToNull" is "True". Change it to "False".

Doing this will enable master-detail scenarios to work without exceptions.


Categories: 2010 Winter BTI420
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: