We will explore each individual Data Access block feature and along the way we will understand the concepts behind the individual elements. This will help us to get up to speed with the basics. To get started, we will do the following:
- Reference the Data Access block assemblies
- Configure Data Access settings
- Add the required Namespace
- Create an instance of
Database - Perform actions using the
Databaseinstance
To complement the concepts and sample code of this book and allow you to gain quick hands-on experience of different features of the Data Access block, we have created a sample demonstration application. The following is a screenshot of the sample application:
For the purposes of this demonstration we will be referencing non-strong-named assemblies but based on individual requirements, Microsoft strong-named assemblies or a modified set of custom assemblies can be referenced as well.
The following table lists the required assemblies:
|
Assembly |
Required/Optional |
|---|---|
|
|
Required |
|
|
Required |
|
|
Required |
|
|
Required |
|
|
Required |
Before we can leverage the features of the Data Access block we have to add the initial Data Access Settings to the configuration. The following steps will add the settings to the configuration file.
- Open the Enterprise Library configuration editor either using the shortcut available in Start | All Programs | Microsoft patterns & practices | Enterprise Library 5.0 | Enterprise Library Configuration or by just right-click the configuration file in the Solution Explorer window of Visual Studio IDE.
- Next click on Edit Enterprise Library V5 Configuration. Initially, we will have a blank configuration file with default Application Settings and Database Settings.
The following screenshot shows the default configuration settings:
Database Settings configuration is already loaded and consists of three sections: Database Instances, Oracle Connections, and Custom Databases. Let us go ahead and configure the connection string in the Database Instances section.
- Click on the plus symbol provided on the top right corner of the Database Instances section and click on the Add Database Connection String menu item.
The following screenshot shows the menu option Add Database Connection String:
- Once we click on the Add Database Connection String, the configuration editor will add a new connection string as shown in the following screenshot:
- The configuration editor has added a connection string section with Name as Database Connection String and an empty value and database provider.
- Change the Name property to the name of your choice. The Name property can be used to create an instance of
Database. - Next click on the ellipsis button provided against the Connection String property. This action will pop up a small Edit Text Value dialog as shown in the following screenshot. Enter the database connection string you wish to connect to while leveraging the Data Access Application Block.
- Next select the appropriate Database Provider from the drop-down list of providers. For the purposes of this demonstration, we will be using SQL Server database and so we will select
System.Data.SqlClientdatabase provider.
- The following screenshot shows the selected Database Provider:
Although this step is optional, it helps in creating an instance of Database without providing the database instance name for the object construction. This basically means creating a default Database instance.
- Click on the arrow (representing expand/collapse) provided on the right side of the Database Settings; this will allow us to configure the Default Database Instance property.
- Next, select the Default Database Instance you wish to configure from the drop-down list.
The following screenshot shows the selection of the Default Database Instance configuration option:
The following screenshot shows the selected Default Database Instance:
The configuration editor generates the corresponding xml in the configuration file. The following is the output of the configuration; certain values are truncated for readability.
[XML Configuration] <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" /> </configSections> <dataConfiguration defaultDatabase="EntLibBook-DataAccess" /> <connectionStrings> <add name="EntLibBook-DataAccess" connectionString="server=.SQLEXPRESS;database=EntLibBook-DataAccess;Integrated Security=true;" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>
We definitely don't want to get bored by fully qualifying the type on every instance of its usage, so to make our life easy we can add the namespace given below to the source code file to use the Data Access block elements without fully qualifying the reference. Although we will be using EnterpriseLibraryContainer to instantiate objects (so we will also add Microsoft.Practices.EnterpriseLibrary.Common.Configuration namespace to the source file), the Unity Namespace section is listed to make you aware of the availability of the alternate approach of instantiating objects.
Microsoft.Practices.EnterpriseLibrary.Data
Common Data Related Namespace:
System.DataSystem.Data.Common
Configuration Namespace (Optional): Required while using the EnterpriseLibraryContainer to instantiate objects.
Microsoft.Practices.EnterpriseLibrary.Common.Configuration
Unity Namespace (Optional): Required while instantiating objects using the Unity container.
System.ConfigurationMicrosoft.Practices.UnityMicrosoft.Practices.Unity.Configuration
Database is an abstract class part of Microsoft.Practices.EnterpriseLibrary.Data namespace, this class is in the heart of the action. When we generate an instance of this class based on the configuration, we get the respective concrete implementation. This class provides several virtual (Overridable in Visual Basic) properties and methods, default behavior/logic is implemented, and this provides flexibility to derived classes to override the behavior/logic based on the individual requirements. It exposes several utility methods such as CreateConnection, CreateParameter, GetDataAdapter, GetParameterValue, GetSqlStringCommand, GetStoredProcCommand, and so on; these methods have several helpful overloads as well. Apart from these, it also provides methods such as ExecuteReader, ExecuteNonQuery, ExecuteDataSet, ExecuteScalar, LoadDataSet, UpdateDataSet, and so on to perform CRUD (Create, Read, Update, Delete) operations.
The following diagram shows the Database class and the derived classes:
The Data Access block provides parameter caching services for stored procedures; while executing the command more than once, parameter caching eliminates the round trip to the database to get the parameters and types. The ParameterCache class is internally used by the abstract Database class to cache parameters. CachingMechanism is an internal class, which provides the actual caching functionality to the ParameterCache class.
The following class diagram shows the methods exposed by the ParameterCache class:
The SqlDatabase class inherits from the Database class and is part of the Microsoft.Practices.EnterpriseLibrary.Data.Sql namespace. This class represents an SQL Server database and uses SQL Server .NET managed provider System.Data.SqlClient to connect and perform operations on an SQL Server database. This class overrides several properties and methods and provides implementation specific to SQL Server database. Properties such as SupportsAsync (returns true), ParameterToken (returns @), SupportsParemeterDiscovery (returns true), and so on are overridden. Also it overrides methods such as BeginExecuteNonQuery, BeginExecuteReader, BeginExecuteScalar, and corresponding "End" methods to leverage asynchronous execution. Additionally, it adds methods such as ExecuteXmlReader, BeginExecuteXmlReader, and EndExecuteXmlReader to expose functionality specific to SQL Server database.
The following class diagram shows the properties and methods exposed by the SqlDatabase class:
The SqlCeDatabase class inherits from the Database class and is part of the Microsoft.Practices.EnterpriseLibrary.Data.SqlCe namespace. This class provides implementation to work with SQL Server Compact Edition database. SQL Server CE doesn't provide any connection pooling and so the cost of opening the initial connection is high; this class provides a simple connection pooling implementation to improve the performance. The implementation overrides several methods and provides its own logic specific to SQL Server CE database. It is to be noted that since SQL Server CE doesn't support stored procedures, all the methods related to stored procedures will throw an exception of type NotImplementedException. Instead, use the methods ending with Sql such as ExecuteDataSetSql, ExecuteNonQuerySql, ExecuteReaderSql, ExecuteScalarSql, and so on. This class also adds additional utility methods such as CreateFile, CloseSharedConnection, and so on.
The following class diagram shows the properties and methods exposed by the SqlCeDatabase class:
The OracleDatabase class inherits from the Database class and is part of the Microsoft.Practices.EnterpriseLibrary.Data.Oracle namespace. This class provides implementation to access and perform CRUD operations on an Oracle database. It internally leverages Oracle .NET Managed Provider System.Data.OracleClient to connect and perform operations on an Oracle 9i database.
The following class diagram shows the properties and methods exposed by the OracleDatabase class:
The GenericDatabase class also inherits from the Database class and is part of the Microsoft.Practices.EnterpriseLibrary.Data namespace. This class doesn't provide any specific behavior and is used when none of the other concrete implementations of Database maps to the specific data provider. It overrides only one method; the protected DeriveParameters method is overridden and it throws an exception of type NotSupportedException. Being a generic implementation, there is obviously no generic way and support for parameter discovery.
The following class diagram shows the methods exposed by GenericDatabase class:
We have several options at hand while creating a Database object such as using the static DatabaseFactory class, using Unity Service Locator, and using Unity container directly. A few approaches such as configuring the container through configuration file or code are not listed here but the recommended approach is either to use the Unity Service Locator for applications with few dependencies or create objects using Unity container directly to leverage the benefits of this approach. Use of the static factory class is not recommended.
Note
More information on Unity Container and Service Locator is available at http://msdn.microsoft.com/en-us/library/ff664535(PandP.50).aspx.
DatabaseFactory is a static class and is part of the Microsoft.Practices.EnterpriseLibrary.Data namespace. This class contains factory methods for creating Database objects; it exposes a method called CreateDatabase with an overload accepting the connection string name. Internally, it leverages EnterpriseLibraryContainer, which is part of the Microsoft.Practices.EnterpriseLibrary.Common.Configuration namespace; this class is an entry point for the container infrastructure for Enterprise Library.
The following class diagram shows the methods exposed by the DatabaseFactory class:
Static factory classes were the default approach to creating objects with versions prior to 5.0. This approach is no longer recommended and is still available for backward compatibility.
The following is the syntax to create a deafult instance of Database using the DatabaseFactory class:
Database db = DatabaseFactory.CreateDatabase();
The following is the syntax to create a named instance of Database using the DatabaseFactory class:
This approach is recommended for applications with few dependencies. The EnterpriseLibraryContainer class exposes a static property called Current of type IServiceLocator, which resolves and gets an instance of the specified type.
The following is the syntax to create a deafult instance of Database using Unity service locator:
Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
The following is the syntax to create a named instance of Database using Unity service locator:
Larger complex applications demand looser coupling. This approach leverages the dependency injection mechanism to create objects instead of explicitly creating instances of concrete implementations. Unity container resolves objects using type registrations and mappings; these can be configured programmatically or through a configuration file and based on the configuration it resolves the appropriate type whenever requested. The following example instantiates a new Unity container object and adds the Enterprise Library Core Extension. This loads the configuration and makes registrations and mappings of Enterprise Library available.
The following is the syntax to create a default Database instance directly using Unity container:
var container = new UnityContainer(); container.AddNewExtension<EnterpriseLibraryCoreExtension>(); Database database = container.Resolve<Database>();
The following is the syntax to create a named Database instance directly using Unity container:
var container = new UnityContainer();
container.AddNewExtension<EnterpriseLibraryCoreExtension>();
Database database = container.Resolve<Database>("EntLibBook-DataAccess");