Drydo's Blog

Strongly Typed Datasets (.NET 2.0)

Course Introduction

Welcome to my course on Strongly Typed Datasets. The intention of this course is to introduce the student to the concept of the Strongly Typed Dataset, its capabilities and advantages in the real world environment. This course is not intended for ADO.NET newbies and *does* require some understanding of the Dataset object, OO Programming, database structure concepts and some SQL skills such as creating a database and connecting to it. But on the plus side, if you're like me and not the kind of person who enjoys poring over technical books/manuals but would prefer delve into the code and experiment then this course should hopefully suit you.

All code provided is VB, but holds true for C#, and has been generating in Visual Studio 2005 Pro using Framework 2.0. I haven't tested any of this code in any of the Express versions but I anticipate that all elements mentioned in this course are supported.

All database elements have been created using SQL Server 2000 - but shouldn't cause any problems for SQL 2005 and Express versions.

About the Lecturer

Mark Dryden is the Lead Developer for a small software house based in South Leicestershire, England. Initially wanting to be a Graphic Artist (as well as a ninja) things took a turn for the worse when introduced to programming in the murky depths of MSWord 6.0 VBA macros. Whilst his journey through the world of development his path has taken him to wonders of MS Access Development, the barren lands of Lotus Notes and fancy free fields of Web Development - his heart has always been with Visual Basic.

His current position entails developing and support for Digital Signage and Kiosk applications in the retail sector with implementations at many high profile organisations across the world. His skill set is not exclusive to VB and has been known for utilising VB6, ASP, ASP.NET, SQL Server, MySQL and a host of other technologies.

Mark can often be found at the VBCity Forums (www.vbcity.com) contributing articles and materials or simply getting down and dirty in the forums.

When not in the guise of an IT Professional, Mark lives with his partner Katie and two children, Reece who is four and Jemima who is one. Mark is a keen footballer, playing centre-half or on the right for his local Sunday Morning football team as well as playing during the week. He is also a keen cyclist and power kiter, albeit, a fair weather one in both cases.

So what are Strongly Typed Datasets?

Strongly Typed Datasets are not objects in the .NET framework. They are, in fact, designer generated wrapper classes based upon a XSD structure (XML document) and inherit from the Dataset and its associated objects like the DataTable and DataAdapter.

For the most part, all of the code generation for Strongly Typed Datasets is abstracted away from us (the developers), however, we can tweak the Dataset as necessary to add new methods to fill the Dataset, new methods for specific access, amending column properties, etc.

So what are the advantages of the Strongly Typed Dataset? Well, here's a couple that come to mind...

1. 1. Provides strongly typed access to our data. If you've used Datasets in the past, you could access the ID field of the first row in a table using something like this...
   Dim ds As New DataSet
   ' Load the dataset with data
   Debug.WriteLine(ds.Tables("tblCustomers").Rows(0).Item("CustomerID").ToString())

   But with a Strongly Typed Dataset you would use something like this...

   Dim ds As New DataSet
   ' Load the dataset with data
   Debug.WriteLine(ds.tblCustomers.Item(0).CustomerID)

   …giving type security with your columns as well as explicitly naming our data entities (e.g. the tables). For example, in the two code samples above the 'CustomerID' field might be an integer. Without strongly typing, you would need to cast the 'CustomerID' to an integer; with the Strongly Typed Dataset this is performed automatically.

2. Automatically generates data access code for simple SELECT, INSERT, UPDATE and DELETE statements and the mapping for the TableDataAdapters.

So what's involved with creating a Strongly Typed Dataset? Well, there are two methods you can employ.

• Manually.
  This is useful for generating Dataset that you may want to serialise over a webservice or simply persisting your data using WriteXML and ReadXML methods
• Generated from an existing source.
  By utilising the Server Explorer, it is possible to connect into actual database and simply drag and drop the tables onto the designer. The import process will configure the tables, columns, datatype, relationships and a host of other things.

Of course, it is possible to generate a Strongly Typed Dataset manually for an existing data source, but whoa that's a lot of unnecessary work…

Practicial 1: Generating a Strongly Typed Dataset manually

The following details the steps required to generate a Strongly Typed Dataset manually, and thus, not based upon a database.

1. Open a new windows form project
2. Add a new item to the project (Right-Click the Project name in the 'Solution Explorer') and select the 'Dataset' option. Set an appropriate name, e.g. Dataset1.
3. From the Toolbox, drag and drop a 'DataTable' object onto designer screen.
4. Set an appropriate name for the table and add two columns by right-clicking the table name and selecting 'Add Column', e.g. 'ID' and 'Value'.

   
   Custom DataTable with 2 columns

5. Save the Dataset (this invokes the Dataset generation tool).
6. Go back to your form and drop a DataGridView onto the form. From the 'DataGridView' Tasks dialog, select the 'Choose Datasource' dialog and select the 'Other Datasources' and drill down until you reach the table you generated. Keep all other options selected, e.g. add, delete, edit, etc.
7. Drop a button onto the form and name 'butSave' with the text 'Save'.
8. Enter the codeview of the form (double-click the form) and in the Form_Load method enter the following code…
   ' Generate a FileInfo object to the persisted data
   f = New IO.FileInfo(Application.StartupPath & IO.Path.DirectorySeparatorChar & "MyData.xml")
   ' Does the file exist?
   If f.Exists Then

   ' Load the data
   Me.DataSet1.ReadXml(f.FullName)

   End If

9. In the 'butSave' click event add…
   Me.DataSet1.WriteXml(f.FullName)

10. Finally, add a private variable in the Form / Class…
    Private f As IO.FileInfo

This form will allow you to…

• Create new rows for the Strongly Typed Dataset and edit / delete existing rows.
• Persist the changes to an XML data file in the startup directory of the assembly.
• If the XML data file exists, load the existing data.

So with hardly any code we successfully generated a Strongly Typed Dataset, bound it to a DataGrid and added routines to persist and read the data.

Practicial 1: Generating a Strongly Typed Dataset manually

The following details the steps required to generate a Strongly Typed Dataset manually, and thus, not based upon a database.

1. Open a new windows form project
2. Add a new item to the project (Right-Click the Project name in the 'Solution Explorer') and select the 'Dataset' option. Set an appropriate name, e.g. Dataset1.
3. From the Toolbox, drag and drop a 'DataTable' object onto designer screen.
4. Set an appropriate name for the table and add two columns by right-clicking the table name and selecting 'Add Column', e.g. 'ID' and 'Value'.

   
   Custom DataTable with 2 columns

5. Save the Dataset (this invokes the Dataset generation tool).
6. Go back to your form and drop a DataGridView onto the form. From the 'DataGridView' Tasks dialog, select the 'Choose Datasource' dialog and select the 'Other Datasources' and drill down until you reach the table you generated. Keep all other options selected, e.g. add, delete, edit, etc.
7. Drop a button onto the form and name 'butSave' with the text 'Save'.
8. Enter the codeview of the form (double-click the form) and in the Form_Load method enter the following code…
   ' Generate a FileInfo object to the persisted data
   f = New IO.FileInfo(Application.StartupPath & IO.Path.DirectorySeparatorChar & "MyData.xml")
   ' Does the file exist?
   If f.Exists Then

   ' Load the data
   Me.DataSet1.ReadXml(f.FullName)

   End If

9. In the 'butSave' click event add…
   Me.DataSet1.WriteXml(f.FullName)

10. Finally, add a private variable in the Form / Class…
    Private f As IO.FileInfo

This form will allow you to…

• Create new rows for the Strongly Typed Dataset and edit / delete existing rows.
• Persist the changes to an XML data file in the startup directory of the assembly.
• If the XML data file exists, load the existing data.

So with hardly any code we successfully generated a Strongly Typed Dataset, bound it to a DataGrid and added routines to persist and read the data.

Practical 2: Generating a Strongly Typed Dataset manually on a SQL Database

Firstly, we need an actual database to base our Dataset upon. You can either use an existing database of your own or create a new one named 'StronglyTypedSample1' and execute this SQL file to generate our first test table.

1. Ensure that your Dataset is created then follow steps 1 and 2 from Practical 1 calling the Dataset 'Dataset2'.
2. Goto your 'Server Explorer' ('View'->'Server Explorer'), right-click 'Data Connections' then select 'Add Connection'.
3. Configure the connection to your database and click onto the 'Test Connection' button to ensure that your connection is valid and click 'OK'
4. Expand the new connection that has been added to the list and then expand the 'Tables' node.

   
   Custom Dataset with 1 table

5. Select the new table (tblRegister) and drag onto the xsd workspace (Blue and white striped bit) and save the Dataset.
6. Create a new Form (or clear the existing Form of elements from Practical 1) and drop a DataGridView onto the form and a button (named 'butSave' with the text 'Save').

Note: here I'm going to open and bind the table through code rather than through the graphical designer. It's a personal thing where I like to see the code myself, but feel free to utilise the GUI components to perform the above.

7. Generate a private variable (in the Form) for our Dataset…
   Private mDs As DataSet2

8. Within the Form_Load event, copy the following code…
   ' Load the data into the dataset
   
   ' Initialise the dataset
   Me.mDs = New DataSet2
   
   ' Generate the table adapter to load in the data
   Dim daRegister As New DataSet2TableAdapters.tblRegisterTableAdapter
   daRegister.Fill(Me.mDs.tblRegister)
   
   ' Set the datasource of the grid view
   Me.DataGridView1.DataSource = Me.mDs.tblRegister
   
   ' Cleanup
   daRegister.Dispose()
   daRegister = Nothing
   

9. Finally, in ButSave_Click event copy the following…
   'Save the data back up the database
   
   ' Generate the table adapter to save data back to the database
   Dim daRegister As New DataSet2TableAdapters.tblRegisterTableAdapter
   ' Pass the appropriate table to update
   daRegister.Update(Me.mDs.tblRegister)
   
   ' Cleanup
   daRegister.Dispose()
   daRegister = Nothing

So now we have a Strongly Typed Dataset based upon a 'real' database table with all of the basic SELECT, INSERT, UPDATE and DELETE SQL statements automatically generated within our Strongly Typed Table Adapter and available through a custom namespace (in this case 'Dataset2TableAdapters')

In addition, the database connection (you may have noticed we do not define one in our code) has been derived from the database connection we generated when we first connected to our database.

Admittedly, this particularly example isn't enough for a real-world scenario but it does demonstrate the speed and ease of creating a Strongly Typed Dataset and performing some of the more arduous tasks especially in a RAD (Rapid Application Development) environment.

One table, one Dataset?

It has always been a surprise for me when browsing around the forums and so forth that developers seem to treat Datasets as individual data entities, e.g. one Dataset = one table. In reality, a Dataset is merely a container for one or more DataTables that can reflect not only your data entities but mirror those relationships, cascade updates and deletes and provide methods to easily navigate through child records or up to a parent record.

Of course, there are a couple of gotcha's that are involved when dealing with multiple tables but there are techniques to easily get around this. But to start with, we will create the new database structure and then configure the actual Strongly Typed Dataset.

Practical 3: Utilising three tables in one Dataset

Create a new blank database called 'StronglyTypedSample2' and run this SQL File that will create our test database structure…

1. Follow Steps 1 to 4 from Practical 2, calling the Dataset 'Dataset3'
2. Select all three tables (tblCustomers, tblContacts and tblContactTypes) and drag them on the workspace.

This should now have created appropriate DataTable and DataAdapter objects for all three tables as well as any constraints / relationships that have been defined and it should look like the following (after a bit of moving) …

Custom Dataset with 3 Tables

Now at this point it is worth talking about the actual process that is involved when you drag and drop a table from the server explorer to the workspace. The first element of this process is the generation of the 'DataTable' as shown below in section A. This is the strongly typed representation of our underlying table and will include all columns, their respective datatypes and other restrictions (e.g. field sizes). The second element is the related DataAdapter for the DataTable (shown in section B) and will automatically generate the SelectCommand, InsertCommand, UpdateCommand and DeleteCommand, including all SQL, for that data entity as well as Strongly Typed Fill and Update methods.

Note: to view SQL code used for each command object, click onto the 'Tableadapter' header and view its properties and associated 'command' objects.

Design Note: all command objects are created using the appropriate parameter objects, e.g. SQLParameter for SQL databases.

From here, we can now customise our Dataset to be more intuitive for our Dataset needs, for example, adding specialised loading methods and handling referential integrity.

Adding custom fill methods.

As you can imagine filling a Dataset with every record from our data source sounds wasteful at best, especially in the circumstance where you application only needs a small snapshot of data.

Firstly, let's review the 'StronglyTypedSample2' created in Practical 3. Here we have three tables, 'tblContactTypes' that is a simply reference table, 'tblCustomers' that stores our top-level customer data and 'tblContacts' that holds details about individual contacts for a specific customer.

SQL Diagram of StronglyTypedSample2

Here I would always assume that as 'tblContactTypes' is a reference table and it is unlikely I'll ever need to load any specific records from that table. With 'tblCustomers' it is quite feasible I might want a method that loads a customer record from the database using the CustomerID. And with the 'tblContacts' table it would be beneficial to load a contact record either based on a ContactID or those contact records that relate to a top-level customer record.

Practical 4: Adding a custom fill method.

1. Select the 'tblCustomersTableAdapter', 'Add Query' then Click 'Next'.
2. Select the option 'SELECT which returns rows' and click 'Next'.
3. Generate a SELECT query to obtain Customer records, e.g.
   SELECT
   custID, custCompanyName, custTelephoneNo, custFaxNo, custURL
   FROM
   tblCustomers
   WHERE
   custID = @CustomerID

   Note: Parameters are defined by the designer by inserting a '@' symbol before each parameter.

4. Enter appropriate method names for this new fill method, e.g. FillByCustomerID and GetDataByCustomerID and click 'Finish'.

From this point we can easily add new fill methods based upon a variety of criteria.

Handling Referential Integrity

Now with our three tables configured in our Strongly Typed Dataset and with our custom Fill methods, we now need to concentrate on the relationships between our tables.

When dealing with Parent/Child relationships it is extremely important to ensure that Primary keys and foreign keys are updated as new records are inserted into the database. To aid this process we must action the following three things…

1. Ensure that new records inserted into the Dataset do not conflict with existing primary keys.

   Open the Dataset designer and select the 'customerID' column from the 'tblCustomer' table. In the properties for that column, ensure that the 'AutoIncrement' is set to true, the 'AutoIncrementSeed' is set to '-1' and the 'AutoIncrementStep' is set to '-1'. This will ensure that no new record Ids will conflict with any existing records (assuming you increment your Identity within the database).

2. Ensure that Inserted records return their newly created ID.

   Nothing actually needs to occur here as each InsertCommand includes the SQL INSERT statement and a SELECT statement retrieving the new data using 'SCOPE_IDENTITY' to retrieve the last created ID on that connection.

   Note: this works well in the SQL environment where multiple statements can be executed. This usage is Access is addressed later in this course.

3. Ensure that Inserted records update their child records foreign key.

   To do this, simply click onto each relationship and select 'Edit Relation. In the 'Relation' dialog, ensure that 'Both Relation and Foreign Key Constraint' option button is selected AND that both the 'Update Rule' and 'Delete Rule' are set to 'Cascade'. This will ensure that any parent updates through that relationship (i.e. the primary key value) are automatically updated and if a deletion is made, any related records are also deleted.

The Manager

So we've successfully generated our Strongly Typed Dataset and configured it. The next thing is to take all that disparate code and create a wrapper to allow users to interface with it easily. Why? Well…

• As you may have noticed in Practical 2, when we tested our Dataset there was no need to generate an actual database connection object. This is because when we used the Server Explorer to connect into our data source, it generated the connection string in the 'Settings' section of our Project Properties. Of course, this is fine in testing but what about in a live environment? Wouldn't it be useful to change the connection object as required?
• As we're using multiple tables and cascading changes between them - it is still necessary to ensure that each table and its appropriate rows are saved in a logical order, e.g. Insertions first, then the actions of all child records, Updates and finally Deletions.
• Custom methods to Fill the Dataset using appropriate parameters, e.g. retrieve a snapshot of the dataset by loading all data relative to a specific Customer ID.

To do this I create a new class and name it (e.g. 'Sample2DatasetManager') and then generate appropriate methods to…

• Initialise the Dataset and TableAdapters
• Expose the Dataset through a readonly property
• Expose the connection as a property and ensure that any new connections are distributed to all Tableadapter
• A method to call all the Tableadapter default 'Fill' methods.
• A method to save the DataTables in a specific order to prevent any breaks of referential integrity.
• A method to fill the Dataset with a customer record and its dependant records.

Practical 5: Implementing manager wrapper

OK - I've got to admit this element is going to be pretty boring so like those Dungeons and Dragons book of yesteryear, you can either have a crack at the coding the manager class based upon the previous points (just ensure that the class name is 'Sample2DatasetManager') or you can simply check out my code and the comments.

Manager Class

So you succumbed to temptation ;-)

When completed, we can now utilise the manager to load our Dataset and display…

1. Create a new form, add a DataGridView and button named 'butSave' with the text 'Save'.
2. Generate a private variable (in the Form) for our manager class…
   Private manager As Sample2DatasetManager

3. In the load event of the form add the following code to load the Dataset.
   ' Initialise the manager object
   manager = New Sample2DatasetManager
   ' Load all the data in (for testing)
   manager.LoadAll()
   ' Display just the customers table (for this demo)
   Me.DataGridView1.DataSource = manager.Dataset.tblCustomers

4. In the click event of the button, add the following code.
   ' Call save routine to save changes to the DB
   manager.Save()

However, this doesn't demonstrate how foreign keys are updated - to do this, create a new button on the form and execute the following…

…as you can see from the comments, it adds a new customer record and then adds two contact records for that customer, it then saves the rows and outputs the new IDs. Pretty neat huh?

Strongly Typed Datasets in an n-tier environment?

This is something I've kinda racked over in my head and personally, I think that Strongly Typed Datasets can actually be utilised in an n-tier scenario and not necessarily as just the data access tier but also where the business and data access tiers do not need to be abstracted from each other.

To do this we can capture events of the Dataset, DataTable and TableAdapter classes, by using partial classes, and implement our business logic as appropriate.

In a live environment, complex logic and workflow can be tailored to ensure that data inserted, modified and deleted conforms to specific rules as well as simple and complex validation…

Practical 6: Adding simple validation

In this practical we're going to add validation for a specific column, namely executing a simple regular expression to ensure that the email field for each contact row is a valid address.

1. Open the Strongly Typed Dataset and switch to code view. This will generate a partial class for the Dataset.
2. Within the Dataset Partial Class, create a Partial Class for the 'tblContactsDataTable', e.g.
   Partial Class DataSet3
   

   Partial Class tblContactsDataTable

   End Class

   
   End Class

3. Copy the following into the partial class.
   Private mEmailRegEx As New System.Text.RegularExpressions.Regex( _

   "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")

   
   Private Sub tblContactsDataTable_ColumnChanging(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanging
   

   ' Determine the column
   Select Case e.Column.ColumnName

   Case "conEmail"

   ' Validate the email address using a regular expression
   If Not mEmailRegEx.IsMatch(e.ProposedValue) Then

   e.Row.SetColumnError(e.Column, "Enter a valid email address")

   End If

   End Select

   
   End Sub

4. To test, go back to Practical 5 but set the DataSource of the DataGridView to the contacts table. Now attempt to enter an incorrectly formatted email address in the grid view...

Assorted Questions

Can we use this with other dataproviders, e.g. Access?

Yes. Strongly Typed Dataset can be used with any data provider. One slight concern of using MS Access would be that you cannot run multiple statements.

So with the sample database used in this course, we could run the 'InsertCommand' that performs an SQL Insert and then a Select. With MS Access we can capture the 'RowUpdated' event, run another command to perform a 'SELECT @@IDENTITY' on the same connection.

To do this, we can use the Partial Class of the Dataset by defining the actual namespace then generating the Partial Class for the appropriate tableadapter and finally capturing the 'RowUpdated' event and running the query.

Using our database structure used earlier and importing it into an MS Access database, the following demonstrates retrieving the ID for inserted Customer Rows…

In addition, the OleDb Provider doesn't support named parameters, so in SQL we would use…

…would become…

…for MS Access.

What about stored procedures?

Yes. Stored procedures can be used as an alternative to plain text SQL that is used and if anything, provides another layer of abstraction. To implement this, simply select the appropriate tableadapter and navigate to the 'command' object you want to change, set the 'CommandType' to 'StoredProcedure' and ensure that the 'Parameters' match those of your actual Stored Procedure.