|
Introduction
This is the second part of our series on creating a Data Access Layer (DAL) in .NET. In the first part, we discussed some of the basic principles associated with building a DAL and laid out the groundwork for the DAL - covering the setup of the database, login accounts, and Visual Studio projects. In this article, we will set up the code necessary for retrieving and manipulating data. So, let's not waste any time and jump right into building Data Handler classes!
Creating Data Handlers with the Component Class
The Data Handler classes in our Data Access Layer (DAL) will provide the interface to the data through public methods for retrieving and updating DataSets. These classes will contain private instances of Connection, Command and DataAdapter objects. These objects will do the work behind the scenes with our DataSets and DataReaders.
So, how many Data Handlers do we need? This is where the art comes into the design of your DAL. This issue may be primarily influenced by the context in which the DAL is being used. One approach is to have one large Handler class for all the data access. This may be fine for scenarios where the lifetime of an instance of the class will be extended, however for Web apps, there will probably be a lot of unnecessary overhead since each instance of the Handler object will probably utilize only a few of the DataAdapters at any one time. For Web apps, my preference is to keep the Handler classes small and specific to a limited set of entities. This means less overhead in instantiating the objects and fewer constituent components in the tray. If the Handler class is too small though, you may have difficulty building a transaction scope around all the entities that need to work together.
When we created the DAL project in VS .NET in the last article, it created a default class file called Class1.vb. You can delete this since we won't be using standard classes for our Data Handlers, just yet. Instead, we will use a special class that comes in the .NET arsenal called a Component, which extends from the System.ComponentModel.Component class. A Component Class provides a designer tray similar to the one found in Web forms that allow you to drag non-UI components from the Toolbox or Server Explorer on to the class (like DataSets, DataAdapters, etc). These constituent components can then be manipulated through the Properties panel or in some cases, special wizards.
NOTE: .NET Components should not be confused with COM components.
Let's create our first Handler class. To add a component class to the DAL project, right-click the project node in the Solution Explorer (Fig #1) then select Add, then Add Component...
Fig #1 - Add a Component Class
When the Add New Item dialog (Fig #2) appears, give it the name WidgetHandler.
Fig #2 - Adding a Component class with 'Add a New Item' Dialog
Click the Open button and the WidgetHandler component class will appear with the designer ready to add DataAdapters.
Adding DataAdapters to the Handler Components
DataAdapters are the primary means available for filling or updating a DataSet. As we mentioned in the last article, DataAdapters are built of up to 4 separate Command objects - one for each SELECT, INSERT, UPDATE and DELETE action. You could code these Command objects manually, or you can let VS .NET do it for you.
Building by dragging from tables
If you haven't done this yet, you won't believe how easy it is. Simply open the Tables node from the local database connection we set up in the last article, WidgetStore_Local. Click and drag one of the tables onto the WidgetHandler component's Designer Tray. For the first DataAdapter, I have selected the Widgets table. VS .NET automatically creates a configured DataAdapter with a corresponding Connection for us and displays an icon for each in the tray (Fig #3).
Fig #3 - Viewing the new DataAdapter in the WidgetHandler designer
In addition, you may have noticed that VS .NET is smart enough to know that the data source for the tables is SQL Server, so it has kindly provided the SqlClient versions of the Connection and DataAdapter objects.
I recommend renaming the object references in the designer right after they are created. To do this, select one of the icons and change the (Name) property in the Properties panel. If there is only one connection, I like to rename it simply to "conn". If there is more than one, I would probably name it "widgetStoreConnection". Also, VS .NET kindly renames any references to the connection that DataAdapters are using. For DataAdapter names, I use the entity name followed by "DataAdapter", so for the Widgets DataAdapter, we will have "widgetsDataAdapter". I use camel-case formatting for these variable names since they are private in scope (actually they are Friend by default - close enough :). In fact, you could also prefix the names with and underscore or "m_" to indicate that they are private members of the entire class.
Fig #4 - Renaming the components in the WidgetHandler designer
Here are some guidelines from Microsoft for naming fields or members of a .NET class:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconfieldusageguidelines.asp
You may have noticed that I am purposefully avoiding the use of Hungarian Notation. The growing consensus is that Hungarian Notation has little place in .NET (even Microsoft states this in the above link). This is particularly due to the fact that with property driven tools like the component designer we just saw and the powerful Intellisense in the code window, it is almost difficult to not know what type of object you are dealing with. Here are a few more opinions on the use of Hungarian Notation.
http://www.msdnaa.NET/Resources/Display.aspx?ResID=1805
http://www.aspalliance.com/Ambrose/Articles/Hungarian.aspx
When you have a chance, experiment with the component designer; you might find some interesting things. For example, if you drag a second table onto the component, VS .NET recognizes that the table comes from a connection that you have already defined, so it will continue to use the same object reference when creating the command objects. You will find that you can also drag more than one table onto the designer at a time. In this case, a separate DataAdapter will be created for each table. In addition, you can expand the table node in the Server Explorer and select a subset of fields from the table to drag over to create the DataAdapter. If you need to create joins, you will need to follow the techniques described in the next section.
As constituent components are added to the tray, corresponding code is auto-generated for them in the code portion of the component. (This is almost like the code-behind of ASP.NET Web forms.) If you right-click on the component designer, and select View Code, you can see what VS .NET auto-generated for the DataAdapter. Expand the "Component Designer generated code" region in the code window and scroll down to the InitializeComponent method. Notice that the INSERT, UPDATE and DELETE command objects are pre-configured with a parameter collection. Very nice!
Table Mappings
Table mappings in the DataAdapter provide useful information for filling DataSets. Remember, DataSets can contain more than one table. Table mappings make it easier for the DataAdapter to ensure that the right table is being filled.
Building the Stored Procedures
Now, if you inspected the code generated above, you may have noticed that the four command objects in the previous example were created by embedding the SQL directly in the code. As we mentioned earlier, we want to avoid using SQL DML statements directly in our code for security reasons. Instead, we want to use stored procedures that perform these actions. In the olden days (before VS .NET) we would have to get cracking to write SQL scripts to build 4 separate stored procedures to handle the SELECT, INSERT, UPDATE, and DELETE statements for every entity in our system. Today, VS .NET can automate this script generation for us.
There are two ways to do this. First, we can reconfigure a DataAdapter that was previously built by using the drag table method. Second, we can create a new DataAdapter by dragging a new SqlDataAdapter icon from the Data section of the Toolbox into our Handler component. Both of these techniques will launch the DataAdapter Configuration Wizard. However, I recommend the second method, since the first can disrupt the table mappings.
Configuring the DataAdapter
The DataAdapter Configuration Wizard (Fig #5) starts with a welcome screen explaining what will happen. All you have to do is click Next to start.
Fig #5 - The DataAdapter Configuration Wizard
Choosing the Data Connection
The next step is to choose the data connection to build the DataAdapter from. The dropdown list (Fig #6) will provide a list of all the connections predefined in the Server Explorer's Data Connections. Select our current connection, WidgetStore_Local.
Fig #6 - Choosing a Data Connection for the DataAdapter
Choosing the Query Type
Next, choose the query type you want to use for the DataAdapter. For our purposes, we will select the Create new stored procedures option (Fig #7).
Fig #7 - Select 'Create new stored procedures' as the Query Type for the DataAdapter
Generate Stored Procedures
Next, we are presented with a screen to supply the SELECT statement that will be used to create our stored procedures (Fig #8).
Fig #8 - Type in the SELECT statement for the DataAdapter
If you are reconfiguring a previous generated DataAdapter, the SQL statement will appear in the box. If you are starting from scratch, you can just type in the SELECT statement. Don't be afraid to use SELECT * in your SELECT statement. The wizard will find all the current fields from the referenced table and insert them in place of the star. A nice time saver!
From this SELECT statement, the wizard will automatically create the corresponding SELECT, INSERT, UPDATE, and DELETE stored procedures using the same fields in the SELECT statement.
Query Builder
You can provide a very complex SELECT statement with joins if you need to - practically anything goes. If you need some help, you can click the Query Builder... button to bring up a graphical design tool for building queries, similar to the one in Enterprise Manager.
Fig #9 - Create the SELECT statement using the visual Query Builder tool
Advanced Options
On the SELECT statement wizard page, there is also a button for Advanced Options.... Click this and the Advanced SQL Generation dialog (Fig #10) appears with three advanced options for your consideration:
Fig #10 - Set Advanced Options for the DataAdapter
Generate INSERT, UPDATE and DELETE statements
If you only need the DataAdapter for retrieving data, then be sure to unselect this option to avoid creating unnecessary command objects for INSERT, UPDATE and DELETE. If you unselect this option, the next two options become unselected as well.
Use optimistic concurrency
If selected, VS .NET will add conditions to the SQL WHERE clause of both the UPDATE and DELETE statements to check if the original record has changed on the server since the record was retrieved in our disconnected DataSet. If the original record has changed, then the action will fail, and the DataAdapter will throw a DbConcurrencyException.
Refresh the DataSet
When this option is selected, VS .NET will add a SELECT statement to both the UPDATE and INSERT stored procedures to reselect the record that was just updated or inserted. When the DataAdapter encounters a returned resultset after either of these actions, it will insert the values back into the corresponding rows in the DataSet. This will allow the DataSet to reflect values that are set by the server, such as auto-increment IDs.
Create the Stored Procedures
Once your SELECT statement has been defined, VS .NET is ready to create the stored procedures in the database for you (Fig #11). All you need to do is define what name to use for each. PLEASE, DO NOT USE THE DEFAULT NAMES THAT ARE PROVIDED. I recommend the following pattern: first start with a prefix of "dalsp_" followed by the entity name, another underscore and the name of the command. For example, the SELECT procedure for Widgets would be "dalsp_Widgets_Select".
If you use a prefix, DO NOT USE "sp_". This prefix is used by the SQL Server to identify system procedures (that's why many of the utility stored procedures that come with SQL Server have this prefix). If you give your procedures this prefix, SQL Server will look for them in the master database first, then the context database, thus unnecessarily wasting resources.
Fig #11 - Name the Stored Procedures for the DataAdapter
Finally, the wizard confirms that we are complete (Fig #12).
Fig #12 - View the Wizard Results
Click Finish to End.
Viewing the Auto-Generated Stored Procedures
To view the stored procedures we created, expand the Stored Procedure node under our connection in the Server Explorer (Fig#13):
Fig #13 - Viewing the newly created Stored Procedures in Server Explorer
Double-click any of the stored procedures to view the contents. They will be presented as ALTER PROCEDURE statements so that you can make any editing changes you want, right inside of VS .NET!
Here is what you will find:
The SELECT Procedure
The SELECT procedure is straightforward - no surprises.
ALTER PROCEDURE dbo.dalsp_Widgets_Select
AS
SET NOCOUNT ON;
SELECT WidgetID,
Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT
FROM Widgets
The INSERT Procedure
In the INSERT stored procedure, there are two statements - an INSERT statement followed by a SELECT statement.VS .NET provides the SELECT statement to refresh the DataSet. This will be very important if your DataSet has multiple tables with relationships since we will be using placeholder values for keys until we receive the actual values. Once the new values are in place, the DataSet will perform a cascade update to all the related child records.
ALTER PROCEDURE dbo.dalsp_Widgets_Insert
(
@Name varchar(50),
@Description varchar(200),
@WidgetTypeID int,
@Qty int,
@Price money,
@CreateDT datetime,
@UpdatedDT datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO Widgets(Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT) VALUES (@Name,
@Description, @WidgetTypeID, @Qty, @Price, @CreateDT, @UpdatedDT);
SELECT WidgetID,
Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT FROM Widgets WHERE (WidgetID = @@IDENTITY)
The UPDATE Procedure
In the UPDATE procedure, we see a lengthy WHERE clause with many conditions testing the original values of the DataSet with the values currently in the database. This is how VS .NET tests for concurrency conflicts. If there is no match, then that means someone has changed (or deleted) the record before we had a chance to perform the update. As a result, the updated record count returned will be zero (note the SET NOCOUNT OFF). The DataAdapter will interpret this condition as a concurrency conflict and throw a DBConcurrencyException.
ALTER PROCEDURE dbo.dalsp_Widgets_Update
(
@Name varchar(50),
@Description varchar(200),
@WidgetTypeID int,
@Qty int,
@Price money,
@CreateDT datetime,
@UpdatedDT datetime,
@Original_WidgetID int,
@Original_CreateDT datetime,
@Original_Description varchar(200),
@Original_Name varchar(50),
@Original_Price money,
@Original_Qty int,
@Original_UpdatedDT datetime,
@Original_WidgetTypeID int,
@WidgetID int
)
AS
SET NOCOUNT OFF;
UPDATE Widgets
SET
Name = @Name,
Description =
@Description,
WidgetTypeID =
@WidgetTypeID,
Qty = @Qty,
Price = @Price,
CreateDT =
@CreateDT,
UpdatedDT =
@UpdatedDT
WHERE (WidgetID
= @Original_WidgetID)
AND (CreateDT
= @Original_CreateDT OR @Original_CreateDT IS NULL AND CreateDT IS NULL)
AND (Description
= @Original_Description OR @Original_Description
IS NULL
AND Description IS NULL)
AND (Name = @Original_Name)
AND (Price
= @Original_Price)
AND (Qty
= @Original_Qty)
AND (UpdatedDT
= @Original_UpdatedDT OR @Original_UpdatedDT IS NULL
AND UpdatedDT IS NULL)
AND (WidgetTypeID
= @Original_WidgetTypeID);
SELECT WidgetID,
Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT
FROM Widgets
WHERE (WidgetID = @WidgetID)
The DELETE Procedure
In the final stored procedure, the DELETE statement performs the same kind of concurrency check that was used by the UPDATE statement.
ALTER PROCEDURE dbo.dalsp_Widgets_Delete
(
@Original_WidgetID int,
@Original_CreateDT datetime,
@Original_Description varchar(200),
@Original_Name varchar(50),
@Original_Price money,
@Original_Qty int,
@Original_UpdatedDT datetime,
@Original_WidgetTypeID int
)
AS
SET NOCOUNT OFF;
DELETE FROM Widgets
WHERE (WidgetID
= @Original_WidgetID)
AND (CreateDT
= @Original_CreateDT OR @Original_CreateDT IS NULL AND CreateDT IS NULL)
AND (Description = @Original_Description OR @Original_Description IS
NULL
AND Description IS NULL)
AND (Name = @Original_Name)
AND (Price
= @Original_Price)
AND (Qty
= @Original_Qty)
AND (UpdatedDT
= @Original_UpdatedDT OR @Original_UpdatedDT IS NULL
AND UpdatedDT IS NULL)
AND (WidgetTypeID
= @Original_WidgetTypeID)
GO
Variations on SELECT Statements for the Same Entities
So far, we have only discussed building SELECT statements that will retrieve all the records. Of course, this is not always practical. Say, for instance, we want to be able to get all orders for a specific customer.
SELECT statements usually fall into one of three patterns:
- List - these SELECT statements usually provide the entire contents of a table and will denormalize lookup values.
- Single Entity - usually for selecting a single record from the database for editing.
- Filtered Subset - useful for getting related content such as Orders related to a customer
To create these alternate statements, we would create "read-only" DataAdapters, meaning DataAdapters that only have a SELECT command. When you are building a read-only DataAdapter, remember to go into the Advanced Options (Fig #10) dialog and unselect the Generate Insert, Update and Delete statements option.
Here is an example of a list statement we may use for retrieving widgets. We will use this in a DataAdapter named WidgetsListDataAdapter.
SELECT Widgets.WidgetID, Widgets.Name, Widgets.Description,
Widgets.Qty, Widgets.Price, Widgets.CreateDT, Widgets.UpdatedDT,
WidgetTypes.Name AS Type
FROM Widgets
INNER JOIN WidgetTypes
ON Widgets.WidgetTypeID = WidgetTypes.WidgetTypeID
When specifying SQL statements with joins in the DataAdapter Wizard, you may experience some problems when the stored procedure is created. In this case, the wizard tries to be a little too smart and adds the WidgetTypeID of the WidgetType table as the primary key. If this happens, re-edit the stored procedure before creating a DataSet (which we will explain shortly).
Here is an example of the SELECT statement you might use for filtering Orders by Customer:
SELECT
Orders.*
FROM
Orders
WHERE
(CustomerID = @CustomerID)
When the above SQL statement is run through the DataAdapter wizard, The SELECT command object is built with a parameter for @CustomerID. You can add any parameter you want to your SQL statements, simply by prefixing the value with the @ symbol. You could also create a separate SELECT command for this parameterized query and swap it into the DataAdapter. Just be sure to swap the original back after the process.
Another adjustment you may want to make to the SELECT statement is filtering out deleted items. In transactional systems like our Widget Store, it is rare that you would actually delete records from the database, particularly, when other historical records are dependent on them. Instead, you would change their status to "deleted" (or something else to indicate that they are inactive). In our Orders table, we use a bit field to represent the deleted status. Simply add another WHERE clause of Deleted = 0. If you need to list all the records, whether they are deleted or not, just build an additional "show all" DataAdapter.
Altering the INSERT, UPDATE, and DELETE Stored Procedures
The default INSERT, UPDATE, and DELETE stored procedures are good for most operations; however, you may require special conditions to be handled to maintain your data requirements and auditing rules. For instance, we want to be sure that the Create and Update datetime values in our records are set by SQL Server, not by the business logic of the application. This will prevent any inconsistencies in time that may occur across multiple machines that are using the DAL. In addition, it ensures that the values will be set every time there is an INSERT or UPDATE statement. The following INSERT procedure demonstrates this.
ALTER PROCEDURE dbo.dalsp_Widgets_Insert
(
@Name varchar(50),
@Description varchar(200),
@WidgetTypeID int,
@Qty int,
@Price money,
@CreateDT datetime,
@UpdatedDT datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO Widgets(Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT) VALUES (@Name,
@Description, @WidgetTypeID, @Qty, @Price, getDate(), getDate());
SELECT WidgetID,
Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT FROM Widgets WHERE (WidgetID = @@IDENTITY)
Notice that I have left the @CreateDT and @UpdatedDT parameters in place so that we do not have to make manual changes to the DataAdapter. I just simply ignore the value coming in. I also use the getDate() function to set the current datetime for the two fields.
In the UPDATE statement, I have commented out the update for the CreateDT field, since this value should not change. I have again used the getDate() method for setting the UpdateDT field.
CREATE PROCEDURE dbo.dalsp_Widgets_Update
(
@Name varchar(50),
@Description varchar(200),
@WidgetTypeID int,
@Qty int,
@Price money,
@CreateDT datetime,
@UpdatedDT datetime,
@Original_WidgetID int,
@Original_CreateDT datetime,
@Original_Description varchar(200),
@Original_Name varchar(50),
@Original_Price money,
@Original_Qty int,
@Original_UpdatedDT datetime,
@Original_WidgetTypeID int,
@WidgetID int
)
AS
SET NOCOUNT OFF;
UPDATE Widgets
SET
Name = @Name,
Description =
@Description,
WidgetTypeID =
@WidgetTypeID,
Qty = @Qty,
Price = @Price,
/*
CreateDT = @CreateDT, */
UpdatedDT = getDate()
WHERE (WidgetID
= @Original_WidgetID)
AND (CreateDT = @Original_CreateDT OR @Original_CreateDT IS NULL AND CreateDT IS NULL)
AND (Description
= @Original_Description OR @Original_Description
IS NULL
AND Description IS NULL)
AND (Name = @Original_Name)
AND (Price
= @Original_Price)
AND (Qty
= @Original_Qty)
AND (UpdatedDT
= @Original_UpdatedDT OR @Original_UpdatedDT IS NULL
AND UpdatedDT IS NULL)
AND (WidgetTypeID
= @Original_WidgetTypeID);
SELECT WidgetID,
Name, Description, WidgetTypeID, Qty, Price,
CreateDT, UpdatedDT
FROM Widgets
WHERE (WidgetID = @WidgetID)
As we mentioned earlier, we may want to prevent the business logic of an application from being able to actually delete a record. We can alter our DELETE stored procedure so that the DataAdapter thinks it is actually deleting, but all we are doing is setting the Deleted field of the record equal to 1.
ALTER PROCEDURE dbo.dalsp_Orders_Delete
(
@Original_OrderID int,
@Original_CreateDT datetime,
@Original_CustomerID int,
@Original_Deleted bit,
@Original_OrderDT datetime,
@Original_OrderStatusID int,
@Original_ShippingAddress varchar(200),
@Original_ShippingCity varchar(50),
@Original_ShippingState varchar(50),
@Original_ShippingZip varchar(50),
@Original_SubTotal money,
@Original_Tax money,
@Original_UpdateDT datetime
)
AS
SET NOCOUNT OFF;
UPDATE Orders
SET Deleted = 1,
UpdatedDT
= getDate()
WHERE (OrderID
= @Original_OrderID)
AND (CreateDT
= @Original_CreateDT OR @Original_CreateDT IS NULL AND CreateDT IS NULL)
AND (CustomerID = @Original_CustomerID)
AND (Deleted
= @Original_Deleted)
AND (OrderDT
= @Original_OrderDT)
AND (OrderStatusID
= @Original_OrderStatusID OR @Original_OrderStatusID
IS NULL
AND OrderStatusID IS NULL)
AND (ShippingAddress
= @Original_ShippingAddress OR @Original_ShippingAddress
IS NULL
AND ShippingAddress IS NULL)
AND (ShippingCity
= @Original_ShippingCity OR @Original_ShippingCity
IS NULL
AND ShippingCity IS NULL)
AND (ShippingState
= @Original_ShippingState OR @Original_ShippingState
IS NULL
AND ShippingState IS NULL)
AND (ShippingZip
= @Original_ShippingZip OR @Original_ShippingZip
IS NULL
AND ShippingZip IS NULL)
AND (SubTotal
= @Original_SubTotal OR @Original_SubTotal IS NULL AND SubTotal IS NULL)
AND (Tax = @Original_Tax OR @Original_Tax IS NULL AND
Tax IS NULL)
AND (UpdateDT
= @Original_UpdateDT OR @Original_UpdateDT IS NULL AND UpdateDT IS NULL);
Completing the DataAdapters
Here is a list of the DataAdapters to be added to the Widget Handler:
|
Widgets
|
Provides read and update for single widgets
|
|
WidgetsList
|
Provides denormalized readonly list of widgets
|
|
WidgetType
|
Provides readonly list of WidgetTypes
|
|
Orders
|
Provides read and update for all Orders
|
|
OrderDetails
|
Provides read and update for all Order Details
|
|
Customers
|
Provides read and update for all Customers
|
|
OrdersByCustomers
|
Provides readonly for all Orders for all customers
|
Settings Permissions for the New Stored Procedures
Once you have created all the stored procedures, you will need to grant the WidgetStoreDAL login account permission to use them. If you remember, our account has been restricted from using tables directly, so the stored procedures will be the only means of access to the data.
When you create a new SQL login account, by default, it does not have permissions to access any tables. However, you should probably check just to make sure. To do this, open the Users folder under our database in Enterprise Manager, then double-click the WidgetStoreDAL user entry. This will bring up the Database User Properties dialog (Fig #14).
Fig #14 - Changing Permissions for the WidgetStoreDAL
Select the Permissions... button and ensure that all the tables are unselected. Then select the EXEC checkboxes for each of the DAL stored procedures we have just created (Fig #15).
Fig #15 - Setting Execute permissions for the Stored Procedures
If you don't have Enterprise Manager, you can grant the login account execute permissions to our stored procedures:
GRANT EXECUTE ON [dbo].[dalsp_Orders_Select] TO [WidgetStoreDAL]
GO
Update Your SQL Create Scripts
Once all your stored procedures are created for your DataAdapters, don't forget to update the Create scripts in our database project. To do this, right-click the database connection node in Server Explorer and select Generate Scripts... from the popup menu.
Creating Typed DataSets
Well, now it's time to get back on track with our DAL, and we will do that by creating some Typed DataSets.
The easiest way to create a Typed DataSet is to start with a defined DataAdapter. Just right-click any of the DataAdapter icons in the component designer and select Generate DataSet - or if the icon is selected, you can also go to the Data menu item on the top menu bar and select Generate DataSet. When you have done that, the Generate DataSet dialog (Fig #16) will popup:
Fig #16 - The Generate DataSet dialog
Select New option, then type in a name for your DataSet. I recommend using the EntityDataSet pattern, for instance, CustomersDataSet.
Note that a checklist of all the DataAdapters is presented in the dialog along with the name of the "table" they represent. This means that you can specify more than one table in a DataSet. (You would use each DataAdapter to fill the DataSet consecutively, as we will demonstrate shortly.)
When you click OK, this wizard will create an XML Schema Definition (.XSD) file (Fig #17) with the name you provided. The generated schema documents the names and data types for each of the fields referenced in each selected DataAdapter table.
Fig #17 - Viewing the DataSet Schema in design mode
At the bottom of the XSD design panel, you will see two buttons for toggling the view of the Schema Definition - DataSet and XML. Click XML to see the code behind the design view.
<?xml version="1.0" standalone="yes"?>
<xs:schema id="CustomersDataSet" targetNamespace="http://www.tempuri.org/CustomersDataSet.xsd" xmlns:mstns="http://www.tempuri.org/CustomersDataSet.xsd" xmlns="http://www.tempuri.org/CustomersDataSet.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="CustomersDataSet" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerID" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
<xs:element name="Firstname" type="xs:string"
minOccurs="0" />
<xs:element name="Lastname" type="xs:string" minOccurs="0" />
<xs:element name="Company" type="xs:string" minOccurs="0" />
<xs:element name="Address" type="xs:string" minOccurs="0" />
<xs:element name="City" type="xs:string" minOccurs="0" />
<xs:element name="State" type="xs:string" minOccurs="0" />
<xs:element name="Zip" type="xs:string" minOccurs="0" />
<xs:element name="CreditAvailable"
type="xs:boolean"
minOccurs="0" />
<xs:element name="CreateDT" type="xs:dateTime" />
<xs:element name="UpdateDT" type="xs:dateTime"
minOccurs="0" />
<xs:element name="Deleted" type="xs:boolean" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Customers" />
<xs:field xpath="mstns:CustomerID" />
</xs:unique>
</xs:element>
</xs:schema>
On the Generate DataSet dialog(Fig #16), you may have noticed that you can select an option to Add this DataSet to the designer. When selected, this option will create a member variable in the component class for the DataSet. Because we will be using a stateless model for our DAL component, it will be more efficient to create only the instances required for gathering the specific data we need. There will be less overhead if we don't instantiate all the DataSets every time.
The DataSet Class
The XSD file is just the beginning. The real magic behind the XSD is the Typed DataSet class that is auto-generated behind the scenes. This class will be the basis of the Object to Relational mapping we discussed in the last article. If you remember, Typed DataSets provide a collection of table and row objects that are specific to the entities they represent.
Here is a diagram of the structure of an Untyped DataSet.
DataSet
DataTables - collection, numeric or name index
DataTable
DataColumns
DataRows - collection, numeric index
DataRow
Items - field collection - numeric or name index
Names can be used for indexes with Untyped DataSets as long as the schema is loaded.
For Typed DataSets the structure is a bit more concise. For instance, a Typed DataSet class provides named properties for each of the DataTables contained within. In addition, the class contains another object for DataRows.
CustomersDataSet
Customers (CustomersDataTable) - direct instance
Column Properties
CustomersRows
CustomersRow
Properties (Fields)
Our CustomersDataSet contains one public read-only property, Customers, which returns a CustomersDataTable. The CustomersDataTable is an inner class which inherits from the DataTable class. It carries column definitions and a collection of CustomersRows. Each CustomersRow contains numerous properties that each match fields in the Customers entity.
Changing the XSD
Now that we have a schema, let's look at how we can make a few adjustments to change the way the DataSet operates.
Setting fields to Read Only
Remember earlier we configured our INSERT and UPDATE stored procedures to not allow the CreateDT and UpdatedDT fields to be changed by the business logic of the app - only through the stored procedure.
We can let our DAL user know this by setting these elements in the XSD to ReadOnly. To do this, select the appropriate element in the designer. In the Properties panel, change the ReadOnly property to True.
The primary key for the Widgets schema is automatically set to ReadOnly. (Fig #18)
Fig #18 - Setting schema properties
Setting the Auto Increment Seed and Step values
If you reviewed our database schema from the last article, you would have noticed that all the primary keys for our tables were set to be Identity fields. When our schema was created, the primary keys were discovered; however, the auto-increment settings were not. If you don't change this you will be able to add your first record to the DataSet but not the second -- the unique constraint will prevent it.
Fortunately, the DataSet provides a mechanism for simulating an auto-increment. I say, "simulate" because it is important to remember that DataSets are disconnected as we are using them. This means the Primary Key will not contain the actual auto-increment from the database until the DataSet is updated. Therefore, the auto-increment value is relative to the DataSet only. For this reason, I have found it wise to set the AutoIncrementSeed to 0 and the AutoIncrementStep to -1. These settings can also be set programmatically.
Setting a Caption
Captions can also be applied to the elements in your entities, This is a great way to provide meta-data to the presentation layer so that consistent labels can be used for specific fields. You can access the Caption values programmatically through the corresponding column property as such:
Changing the entity names when singular or plural
You may have noticed that when VS .NET created the parts of our DataSet, it simply used the entity name as a prefix to the parts. Since our entity name, Customers, is plural, it can make singular items awkward sounding, such as "CustomersRow". The XSD file provides the ability to specify different prefixes for the singular and plural parts of the DataSet. You can learn more about this from the following MSDN article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingannotationswithtypeddataset.asp
Creating Relationships
As we mentioned before, DataSets not only can contain more than one table, they can also provide relationships between those tables. These relationships can enforce referential integrity, just like a database. You will find this feature very useful for validating values from lookup tables, like the WidgetsType table. Also, it is useful for controlling relations to child records - like OrderDetails of the Orders table.
To get started, we will need to create a DataSet with two tables. We currently have a DataSet for the Widgets table so lets add the WidgetsType table to the same DataSet. You can do this by right-clicking the WidgetsType DataAdapter icon on the Handler class designer. From the popup menu, select Generate DataSet.... Now, rather than creating a new DataSet, you will select WidgetStore.DAL.WidgetsDataSet from the Existing dropdown list. Click OK and the WidgetTypes table will be added to the Widgets DataSet.
The next step is to build the relationship. Open the WidgetsDataSet.XSD file and select one of the entity boxes. Then from the Schema menu, select Add, then Relation. The Edit Relation dialog (Fig #19) will then appear.
Fig #19 - Editing parameters for a DataSet Relation
Next you must identify which is the Parent element and which is the Child element from the dropdowns provided. For our example, select WidgetTypes as the parent and Widgets as the child. You may have noticed that the dialog will try to figure out which elements in the DataSet belong where. Sometimes this works; however, you should carefully review the choices it makes. If the parent table has a primary key, the dialog will automatically select this key for the Key option. In the Fields section, you should identify the primary Key Fields from the parent element and the Foreign Key Fields in the child element.
Relationship Options and Rules
There are additional DataSet Properties on the Edit Relation dialog you can use to configure your relationship.
Create foreign key constraint only
When you create a relationship in a DataSet, the DataSet code will be auto-generated to handle constraining values in the foreign key fields in addition to providing properties and methods for handling child records from a parent. If you select the Create foreign key constraints only option, the DataSet will exclude the child handling methods, making the DataSet a bit more lightweight. This may be a good scenario for situations were you only want to include a parent table for lookup values, and you don't plan to manipulate thee records in the parent table.
Fig #20 - Viewing the DataSet Relation in design mode
Action Rules
Another consideration for DataSet relationships is what to do to child records when certain actions are performed on the data in parent records, such as updates and deletes. To support this, the Edit Relation dialog lets you set behaviors for these actions.
Update rule
The Update Rule handles situations where an existing primary key of a parent row that has related children rows is changed. The following options can be set:
- None - no change will be made to foreign key values in the child table.
- Cascade - the new parent key value will be applied to all related foreign key values.
- SetNull - the related foreign key values will be set to null.
- SetDefault - return the related foreign key to the default value defined in the DataSet.
All the options but Cascade have the potential of creating orphan records in the child table, so these setting should be used with care. The default setting is Cascade.
It is important to leave this set to default (or Cascade) if you want primary keys based on Identity values to be updated to children once they are returned by the database. Remember the auto-increment values we have when we are disconnected are just temporary placeholders. The database will report back the actual values after the DataAdapter performs the update.
Delete rule
The Delete Rule handles situations where a parent row that has related children records is deleted from the DataSet. The following options can be set:
- None - no change will be made to the related child rows.
- Cascade - delete all the child records that are related to the deleted parent record.
- SetNull - the foreign key values will be set to null.
- SetDefault - return the foreign key to the default value defined in the DataSet.
Again, all the options but Cascade, have the potential of creating orphan records in the child table. The default setting is Cascade.
Accept/Reject rule
The Accept/Reject Rule handles situations where a parent row that has related children records has the AcceptChange or RejectChange methods called on it. There are only two options that can be set for this:
- None - no change will be made to the related child rows.
- Cascade - all the child records that are related to the parent record will be set to the same Accept/Reject status.
Adding Methods to the Handler Class
Once you have set up some Typed DataSets, you are ready to start creating methods for retrieving and updating data.
At the beginning of this article, I discussed how the context for your DAL can influence how you partition your Handler classes. Context can also affect how there are two approaches to designing the methods for the Handler class - 'stateless' and 'stateful'. In the stateless approach, the class will be designed so that each method can operate autonomously. Member variables will not be required to persist across method calls. This is generally the model best suited for use with Web Applications. In stateful designs, classes maintain data in member variables across method calls.
Although our class is designed with member variables (for convenience), we will design the methods as stateless.
Methods for Retrieving DataSets
The primary purpose of these methods is to use the Fill command of DataAdapters to load data into DataSets. Let's take a look at two examples.
Show GetCustomersDataSet - later GetCustomersWithOrders
Getting a List
In our first example, we get a DataSet containing all the widget rows.
Public Function GetWidgetsListDataSet() As WidgetsListDataSet
Dim ds
As New
WidgetsListDataSet()
Try
widgetsListDataAdapter.Fill(ds)
Return
ds
Catch
exc As Exception
Dim
msg As String
msg = "Error reading Widget
List from Database."
Throw
New DALException(msg, exc, ds)
End Try
End Function
At this point, with all the preparation we have done, it only requires a few lines of code to fill the DataSet.
Notice, there was no need to open the connection before using the DataAdapter when filling a single DataSet. The DataAdapter will test the connection that is referenced by the DataAdapter's command objects. If the connection is not open, the DataAdapter will automatically open it at the start of the process and close it when the process is complete.
Getting a Single Entity
Retrieving a single entity is very similar to the previous example, however, our method now has a parameter for the widgetID which it passes into the corresponding parameter of the SelectCommand.
Public Function GetWidgetsDataSet(ByVal
widgetID As Integer)
As WidgetsDataSet
Dim ds
As New
WidgetsDataSet()
Try
'fill
types lookup before widget row
widgetTypesDataAdapter.Fill(ds)
'set the
ID parameter for the widget
widgetsDataAdapter.SelectCommand.Parameters("@WidgetID").Value
= widgetID
widgetsDataAdapter.Fill(ds)
Return
ds
Catch
exc As Exception
Dim
msg As String
msg = "Error reading Widgets
from Database."
Throw
New DALException(msg, exc, ds)
End Try
End Function
Exception Handling
A key objective of our DAL is to prevent unfriendly errors from being propagated to the consumer of the DAL.
In each of the examples above, we've used a Try Catch block around the core working code. In the Catch statement, we catch a single generic exception and pass it into the constructor of the DALException class, a custom class we have created to analyze errors that have occurred and present a consistent and more acceptable exception to the DAL consumer. The DALException class inherits from the ApplicationException class, a .NET Framework class especially designed for creating custom exceptions in applications.
The best part of this technique is that the exception processing code is centralized and encapsulated, meaning that the data retrieval code above can be very streamlined and easy to maintain. We could even move this DALException class into another project and deploy it as a global assembly that can be shared amongst other projects, making it even easier to maintain.
Imports
System.Data.SqlClient
Imports
System.Diagnostics
Imports
System.Configuration
Imports
System.Text
Public
Class DALException
Inherits
ApplicationException
Const
PERMIT_LOGGING As Boolean
= False
Public Enum DALExceptionCode
Concurrency
ConnectionFailure
DatabaseFailure
LoginFailure
Constraint
Transaction
Locks
General
End Enum
Private
m_ExceptionCode As DALExceptionCode =
DALExceptionCode.General
Private
m_DataSet As DataSet
Private
m_ErrorRows As DataRow()
Private
m_Log As New
StringBuilder()
Public Sub New(ByVal msg As String, ByVal exc As Exception, ByVal
ds As DataSet)
'Set the
default message and inner exception
MyBase.New(msg,
exc)
m_DataSet = ds
'determine
what type of exception we have
If TypeOf exc Is
ConstraintException Then 'thrown by the DataSet
m_Log.Append("ConstraintException occured" & vbCrLf)
m_ExceptionCode =
DALExceptionCode.Constraint
ElseIf
TypeOf exc Is
DBConcurrencyException Then
m_Log.Append("DBConcurrencyException occured" & vbCrLf)
m_ExceptionCode =
DALExceptionCode.Concurrency
ElseIf
TypeOf exc Is
SqlClient.SqlException Then
m_Log.Append("SqlClient.SqlException occured" & vbCrLf)
processSQLException(exc)
ElseIf
TypeOf exc Is
DataException Then
'this
should handle all other providers - OLEDB, ODBC
m_Log.Append("DataException
occured" & vbCrLf)
m_ExceptionCode =
DALExceptionCode.Constraint
ElseIf
TypeOf exc Is
Exception Then
'anything
left
m_Log.Append("Exception occured"
& vbCrLf)
m_ExceptionCode =
DALExceptionCode.General
End If
detectRowErrors(ds)
m_Log.Append("Original Exception:
" & vbCrLf)
m_Log.Append(exc.ToString)
logException(m_Log.ToString)
End Sub
Private Sub processSQLException(ByVal
exc As SqlClient.SqlException)
'4060-4 -
could not open database
'18450 -
18461 - login failed
'18482,3,5 -
could not connect to server
'547 -
foriegn key violation
'2627 -
Unique Index/Constraint violation
'2601 -
Unique Index/Constraint violation
'1201 - 1223
locks
'2502 -
could not start transaction
'2520-5 -
could not find database
If
exc.Number >= 4060 And exc.Number <= 4064
Then
m_ExceptionCode =
DALExceptionCode.DatabaseFailure
m_Log.Append("Database
Failure - Could not open" & vbCrLf)
ElseIf
exc.Number >= 18450 And exc.Number <=
18461 Then
m_ExceptionCode =
DALExceptionCode.LoginFailure
m_Log.Append("Login
Failure" & vbCrLf)
ElseIf
exc.Number = 18482 Or exc.Number = 18483 Or exc.Number = 18485 Then
m_ExceptionCode =
DALExceptionCode.ConnectionFailure
m_Log.Append("Connection
Failure" & vbCrLf)
ElseIf
exc.Number = 2627 Or exc.Number = 2601 Or exc.Number = 547 Then
m_ExceptionCode =
DALExceptionCode.Constraint
m_Log.Append("Constraint
Violation" & vbCrLf)
ElseIf
exc.Number >= 1201 And exc.Number <= 1223
Then
m_ExceptionCode =
DALExceptionCode.Locks
m_Log.Append("Lock
Error" & vbCrLf)
ElseIf
exc.Number = 2502 Then
m_ExceptionCode =
DALExceptionCode.Transaction
m_Log.Append("Transaction
Error" & vbCrLf)
ElseIf
exc.Number >= 2520 And exc.Number <= 2525
Then
m_ExceptionCode =
DALExceptionCode.DatabaseFailure
m_Log.Append("Database
Failure - Could not find database" & vbCrLf)
End If
Dim
err As SqlError
m_Log.Append(vbCrLf)
m_Log.Append("*******************" & vbCrLf)
m_Log.Append("SQL ERRORS:"
& vbCrLf)
For Each err In
exc.Errors
'default
m_Log.Append(err.ToString)
m_Log.Append(vbCrLf)
Next
End Sub
Private Sub logException(ByVal
msg As String)
Debug.WriteLine(msg)
If
PERMIT_LOGGING Then
'try to
get the app name from the config settings
Dim
appName As String
Try
appName =
ConfigurationSettings.AppSettings("Application Name")
Catch
exc As Exception
'don't
do anything
Debug.Write(exc.ToString)
End
Try
Try
If
appName <> "" Then
If
(Not EventLog.SourceExists(appName)) Then
EventLog.CreateEventSource(appName, appName)
End
If
EventLog.WriteEntry(appName, msg, EventLogEntryType.Error)
Else
EventLog.WriteEntry("DALException", msg,
EventLogEntryType.Error)
End
If
Dim
LogName As String
= "Application"
If
(Not EventLog.SourceExists(LogName)) Then
EventLog.CreateEventSource(LogName,
LogName)
End
If
'
Insert into event log
Dim
Log As New
EventLog()
Log.Source = LogName
Log.WriteEntry(msg,
EventLogEntryType.Error)
Catch exc As Exception
'don't
do anything
Debug.Write(exc.ToString)
End
Try
End If
End Sub
'cycle thru all
tables and rows in the DataSet to find errors
Private Sub detectRowErrors(ByVal
ds As DataSet)
Dim
row As DataRow
Dim
table As DataTable
Dim
column As DataColumn
If
IsNothing(ds) Then Exit
Sub
If
ds.HasErrors Then
m_Log.Append("****************************" & vbCrLf)
m_Log.Append("Errors Detected
in DataSet: " & vbCrLf)
m_Log.Append(vbCrLf)
For
Each table In
ds.Tables
If
table.HasErrors Then
'set
error rows property
m_ErrorRows = table.GetErrors
m_Log.Append("Entity:
" & table.TableName() & vbCrLf)
For
Each row In
table.Rows
If row.HasErrors Then
m_Log.Append(vbTab
& "Key: " & row(table.PrimaryKey(0)) & vbCrLf)
m_Log.Append(vbTab
& row.RowError & vbCrLf)
m_Log.Append(vbCrLf)
For Each column In row.GetColumnsInError
m_Log.Append(vbTab & vbTab & "ColumnName: " &
column.ColumnName & vbCrLf)
m_Log.Append(vbTab & vbTab & "Error: " &
row.GetColumnError(column) & vbCrLf)
m_Log.Append(vbCrLf)
Next
End If
Next
End
If
Next
End If
End Sub
'********************************************
Public ReadOnly Property
ErrorCode() As DALExceptionCode
Get
Return
m_ExceptionCode
End Get
End Property
Public ReadOnly Property
OriginalDataSet() As DataSet
Get
Return
m_DataSet
End Get
End Property
Public ReadOnly Property
RowsWithErrors() As DataRow()
Get
'this is only from one table
Return
m_ErrorRows
End Get
End Property
End Class
The New constructor provides a filter to determine the exact type of exception that is being processed. Once the type is determined, we call a subordinate method to do the rest of the work. The processSQLException method is where the real analysis takes place. We actually examine the resulting SQL error codes and translate them into our own collection found in the DALExceptionCode Enum.
Logging errors
Unfortunately, logging is one of the things that has become difficult in ASP.NET. Basically you have three choices: writing to the event log, the file system, or a database. Writing database errors to the database is unwise - especially if errors are resulting from an inability to get to the database. For the event log or the file system, your ASP.NET app must be running under an account that has sufficient permissions to access these resources. If the Web application is permitting anonymous users, then the app is impersonating the ASPNET account. This account does not have sufficient permissions to write to the file system or make the necessary registry changes to setup the source for event logs.
The following knowledge base articles explains how to set these permissions or provide alternate impersonations on the Web site.
http://support.microsoft.com/default.aspx?scid=KB;en-us;q317012
http://support.microsoft.com/default.aspx?scid=KB;en-us;q306158
The downside of these work-arounds is they require that you have administrative access to the server. If your production site is hosted by a third party, you may be out of luck. Another approach may be to send an e-mail reporting the error. I explain how to do this in my recent article, Tracking Visitors in ASP.NET.
Methods for Updating
To handle all INSERT, UPDATE, and DELETE actions on a DataSet, we only need a single update method for each DataAdapter. That is because the DataAdapter's Update method can handle all these functions.
Public Sub UpdateWidgetsDataSet(ByVal
ds As WidgetsDataSet)
Try
widgetsDataAdapter.Update(ds)
ds.AcceptChanges()
Catch
exc As Exception
Dim
msg As String
msg = "Error update
Widgets."
Throw
New DALException(msg, exc, ds)
End Try
End Sub
In the case of the previous example, we are probably only updating a single record at a time since the DataSet was retrieved as a single entity. There may be situations where you have a large number of records in a DataSet and only a few have changed. The following example demonstrates how to extract the specific types of changed records and update them separately through the GetChanges method of the DataSet.
Public Sub UpdateCustomersDataSet(ByVal
ds As CustomersDataSet)
Try
conn.Open()
Dim
insertedRows As System.Data.DataSet
InsertedRows =
ds.GetChanges(System.Data.DataRowState.Added)
If
Not IsNothing(InsertedRows) Then
customersDataAdapter.Update(insertedRows)
ds.Merge(insertedRows)
End
If
Dim UpdatedRows As
System.Data.DataSet
UpdatedRows =
ds.GetChanges(System.Data.DataRowState.Modified)
If
Not IsNothing(UpdatedRows) Then
customersDataAdapter.Update(UpdatedRows)
ds.Merge(UpdatedRows)
End
If
Dim
DeletedRows As System.Data.DataSet
DeletedRows =
ds.GetChanges(System.Data.DataRowState.Deleted)
If
Not IsNothing(DeletedRows) Then
customersDataAdapter.Update(DeletedRows)
ds.Merge(DeletedRows)
End
If
ds.AcceptChanges()
Catch
exc As Exception
Dim
msg As String
msg = "Error while updating
Customers."
Throw
New DALException(msg, exc, ds)
Finally
conn.Close()
End Try
'TODO: save
changes only
End Sub
Another option to consider for Update methods is to set the ContinueUpdateOnError property of the DataAdapter to True before calling the DataAdapter's Update method. When this is set, the DataAdapter will continue to process data rows and simply record the error with the row. It will not throw an exception. The offending rows can then be retrieved using the same process we use in the detectRowErrors method of the DALException class.
Methods for Opening DataReaders
If you already have SELECT commands built for DataAdapters, then you can just use those for executing a DataReader.
Public Function GetWidgetsListDataReader() As IDataReader
Dim dr
As IDataReader
Try
widgetsListDataAdapter.SelectCommand.Connection.Open()
dr =
widgetsListDataAdapter.SelectCommand.ExecuteReader( _
CommandBehavior.CloseConnection)
Return
dr
Catch exc As Exception
Dim
msg As String
msg = "Error reading Widget
List from Database."
Throw
New DALException(msg, exc, Nothing)
End Try
Note that we have used the CloseConnection CommandBehavior on the ExecuteReader method. Also, keep in mind that you can't use a DataReader through a Web service.
Note that I have used the generic interface IdataReader as the return type for this method. This way you can switch to or from SQLClient, OLEDB, or ODBC and your DAL consumer won't know the difference.
Handling Transactions
In most DALs, you are bound to run into situations where multiple records in multiple related tables need to be added or updated together. In these situations, the entire transaction should be completed together or not at all. To accommodate this, ADO.NET provides easy access to SQL Server transaction context through the Connection Object.
In the example below, we take a look at updating both the Orders and OrderDetails tables from the OrdersDataSet.
The process is started by acquiring a reference to a Transaction object after the Connection object is explicitly opened. That Transaction reference is then added to the INSERT, UPDATE, and DELETE commands of each of the DataAdapters.
Public Sub UpdateOrdersDataSet(ByVal
ds As OrdersDataSet)
Dim
trans As SqlTransaction
Try
conn.Open()
trans = conn.BeginTransaction
ordersDataAdapter.UpdateCommand.Transaction = trans
ordersDataAdapter.InsertCommand.Transaction
= trans
ordersDataAdapter.DeleteCommand.Transaction = trans
orderDetailsDataAdapter.UpdateCommand.Transaction = trans
orderDetailsDataAdapter.InsertCommand.Transaction = trans
orderDetailsDataAdapter.DeleteCommand.Transaction
= trans
ordersDataAdapter.Update(ds)
orderDetailsDataAdapter.Update(ds)
trans.Commit()
ds.AcceptChanges()
Catch
exc As Exception
trans.Rollback()
Dim
msg As String
msg = "Error while updating
Orders."
Throw
New DALException(msg, exc, ds)
Finally
conn.Close()
End Try
End Sub
If the code manages to get to the end of the Try block without error, we call the Commit method on the transaction; otherwise, in the Catch block we call Rollback. Also, notice that we close the Connection in the final clause when done.
If your DAL requires transactions to span across multiple databases, you may need to consider developing your DAL with Serviced Components that can be deployed under COM+ using Component Services.
Reinitializing the Connection
Keep in mind that the connection object we created in the designer is pointing to our development database. We don't want the app to have a hard coded connection, so we will reset the connection string to be whatever is set by our application. In this case, we will set the connection string value in the Web.config file. Using the System.Configuration.ConfigurationSettings.AppSettings method, we can retrieve this value at runtime.
Public Sub New()
MyBase.New()
'This call
is required by the Component Designer.
InitializeComponent()
'Add any
initialization after the InitializeComponent() call
Dim
connStr As String
= _
System.Configuration.ConfigurationSettings.AppSettings("connectionString")
If
connStr <> "" Then
conn.ConnectionString = connStr
End If
End Sub
Web.config file
In the appSettings block of the Web.config, create the following entry if you are using the WidgetSoreDAL account:
<appSettings>
<add key="connectionString" value="data source=(local)\NetSDK;
initial catalog=WidgetStore;persist security info=True;
user id=WidgetStoreDAL;password=password;"/>
</appSettings>
or, if you are using integrated security, create this entry:
<appSettings>
<add key="connectionString" value="data source=(local)\NetSDK;
initial catalog=WidgetStore;persist security info=False;
Integrated Security=SSPI;"/>
</appSettings>
Calling from Web Page
Now, we can start utilizing our DAL in the Widget Store Web application. We will just look at a few examples for managing widgets. (Fig #21)
Fig #21 - The Widget Store Web App
Private Sub Page_Load(ByVal
sender As System.Object, ByVal e As
System.EventArgs)
Handles
MyBase.Load
'Put user
code to initialize the page here
Dim wh
As New
WidgetHandler()
Try
WidgetsListDataSet1 =
wh.GetWidgetsListDataSet
DataGrid1.DataBind()
Catch
exc As DALException
'hide
grid
DataGrid1.Visible = False
'show
error
ErrorLabel.Visible = True
ErrorLabel.Text = exc.Message
ErrorLabel.Text &=
"<br>Contact system administrator."
Catch
exc As Exception
'show
error
ErrorLabel.Visible = True
ErrorLabel.Text = "An error
occured while retrieving Widget data."
ErrorLabel.Text &=
"<br>Contact system administrator."
Finally
End Try
Session("SelectedWidgetID")
= Nothing
End Sub
On the first line of the Page_Load event we instantiate
When a user selects a widget from the grid, we put the Primary Key for that widget in the session then redirect to EditWidget.aspx Web form
Private Sub DataGrid1_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal
e As System.EventArgs) Handles
DataGrid1.SelectedIndexChanged
Session("SelectedWidgetID")
= DataGrid1.DataKeys(DataGrid1.SelectedIndex)
Response.Redirect("EditWidget.aspx")
End Sub
The EditWidget.aspx appears as follows (Fig #22):
Fig #22 - The Widget Store Web App
The Page_Load event of our detail page has a similar structure to
Private Sub Page_Load(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim
widgetID As Integer
If Not (Session("SelectedWidgetID")) Then
widgetID =
Session("SelectedWidgetID")
Else
Response.Redirect("WebForm1.aspx")
End If
If Not IsPostBack Then
Try
WidgetsDataSet1 =
handler.GetWidgetsDataSet(widgetID)
Catch
exc As DALException
'show
error
ErrorLabel.Visible = True
ErrorLabel.Text = exc.Message
ErrorLabel.Text &=
"<br>Contact system administrator."
Catch
exc As Exception
'show
error
ErrorLabel.Visible = True
ErrorLabel.Text = "An
error occured while retrieving Widget data."
ErrorLabel.Text &=
"<br>Contact system administrator."
Finally
End
Try
'check
row count
bind()
'get
current row
Dim
row As WidgetsDataSet.WidgetsRow
row =
WidgetsDataSet1.Widgets.Rows(0)
'select
dropdown entry
ddType.Items.FindByValue(row.WidgetTypeID).Selected = True
'put the
DataSet into the session
Session("widgetsDataSet") = WidgetsDataSet1
Else
WidgetsDataSet1 =
Session("widgetsDataSet")
End If
End Sub
Private Sub bind()
DataBind()
Dim
row As WidgetsDataSet.WidgetsRow
row = WidgetsDataSet1.Widgets.Rows(0)
End Sub
Forcing an Error
If you want to see how errors are propogated to the presentation layer, try changing the connection string to something that will fail.
Summary
Once again, we have covered a lot of territory. Hope I have been able to provide you with the basic framework for building your own Data Access Layer with ADO.NET and Visual Studio .NET. Please send me your comments and suggestions. I will be happy to assemble the ideas you are willing to share for a followup. I am definitely interested in different approaches to this problem.
In our next article, we will take a more in-depth look at utilizing the DAL in Web apps, Web Services and Client-Server apps with .NET Remoting. We will also explore integration with a business rules layer and what to do when concurrency errors occur.
Until then ...
About the Author
Wayne Plourde is a consulting Software Architect who began his career as a building architect twenty years ago. In 1995, he succumbed to the call of the World Wide Web, and since then has been designing sophisticated Web-based and client-server applications for corporations around the country. Wayne holds both MCSD and SCJP certifications and has just completed his .NET MCAD certification. You can contact Wayne at wayne@plourdenet.com or visit his Web site at http://www.plourdenet.com.
|
