|
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 selec |
