Create the Region Helper Class

Purpose:

To create a reusable class to support operations on the Region table.

This class and the process used to create it will be the model for each command helper class that we create.

Starting Point:

You have VisualStudio.NET open.  You have created a Web forms project and a Data Access project.  You have just run the Data Adapter Wizard in the Web forms project to create the standard stored procedures for the Region table.  The Database class exists in the Data Access project and contains a valid connection string.  The Data Access project is selected.

Steps:

  1. Right-click the Data Access project, and select ‘Add Class’ from the context menu.
  2. Name the new class ‘RegionHelper” and save it.
  3. This class will use several members of the System.Data library.  Import the following namespaces: System.Data, System.Data.SqlClient, System.Data.Common.  This means a ‘using’ statement (‘imports’ in VB.NET) for each of the desired objects must be created just before or just after the declaration of the class itself.  (Where they are declared only makes a difference you have more than one class in each file.)
  4. Create a routine to return a DataTableMapping object using the code generated by the Data Adapter wizard. 
  5. Create a routine to return a SqlCommand object for selecting a single record using the code generated by the Data Adapter wizard.
  6. Create a routine to return a SqlCommand object for selecting all of the records in the Region table.  You will have to write this code yourself, but this will be easy. 
  7. Create a routine to return a SqlCommand object for updating a single record using the code generated by the Data Adapter wizard.  (Note the associated procedure which describes that process.)
  8. Create a routine to return a SqlCommand object for inserting a single record using the code generated by the Data Adapter wizard.  (Note the associated procedure which describes that process.)
  9. Create a routine to return a SqlCommand object for deleting a single record using the code generated by the Data Adapter wizard.  (Note the associated procedure which describes that process.)
  10. Compile and save your new class.

Rationale:

Visual Studio will create the data table mappings and command objects for you at the same time that it creates your stored procedures, but the data adapter wizard creates them in the "wrong" tier and does not create them in a reusable form.  (By wrong, we mean in a tier that violates Microsoft's own guidelines.)  This is the fastest method we have found for cleaning those

Discussion:

Note that in each case we have taken an object created locally in the Web forms class and transformed it into an object that can be used anywhere.  In this way, the same code can support any number of forms or services in this or any other project.  As a general rule, in-project reusability is enhanced by placing the reusable items in as low a tier as possible.

This is the process you need to go through to create the first command helper class.  You will find that simple cut and paste saves a lot of work in creating similar classes (custom code generation saves even more).  Although your command helper classes may contain other methods, the six methods created here will exist in every command helper class for an updatable data source.

As you have experienced, this is a fair amount of work.  However, it is straight-forward so that most of it can be performed quickly with practice (ten to fifteen minutes to complete the stored procedures and helper class) or automated.  This is a very small amount of work to create a reusable class that will handle all of your database access for a table.

Previous Step: Create the Data Access Project

Next Step: