Introduction
This is a very simple Enterprise Library 5.0 example.
Getting started with reading data from SQL Server, via Enterprise Library 5.0, requires that the framework be installed on the developing machine and added to the Project References, and ‘using’ references, of the development environment, e.g. Visual Studio 2012.
The software is available from MSDN [1]: Enterprise Library 5.0 — May 2011 (Build 5.0.414.0), but it is recommended that you use NuGet [5] to put the Enterprise Library onto the solution in Visual Studio. This way, the latest version of the Enterprise Library section is downloaded and installed, e.g. 5.0.505.0. I am not sure why the MSDN link points to Enterprise Library (for a stand-alone Msi download) an older version(5.0.414.0) and NuGet has the latest, i.e. 5.0.505.0.
NOTE: Using NuGet to get the Enterprise Library in the: Tools >> Library Package Manager >> Manage NuGet Packages for Solution >> Online >> All, then type ‘entlib’ into the Search Online (Ctrl+E) box, and hit enter button.
Install the following packages from NuGet:
- Microsoft.Practices.EnterpirseLibrary.Common
- Microsoft.Practices.EnterpirseLibrary.Data
- Microsoft.Practices.EnterpirseLibrary.ServiceLocation
- Microsoft.Practices.EnterpirseLibrary.Unity
- Microsoft.Practices.EnterpirseLibrary.Unity.Configuration
- Microsoft.Practices.EnterpirseLibrary.Unity.Inception
- Microsoft.Practices.EnterpirseLibrary.Unity.Inception.Configuration
In Visual Studio, using the menu path: Tools >> Library Package Manager >> Package Visualizer, you can get a listing of all the packages installed (see image below) for this solution.
Package Manager Visualiser
Using Enterprise Library leverages functionality from a collection of reusable application blocks to assist developers with common development objects. Amongst many, the common block are Data Access, Exception Handling, Logging, Security and Validation. To get to the NuGet packages (online), as well as see which ones have been installed, in addition to the above method, use the Visual Studio menu path: Tools >> Manage NuGet Packages for Solution… The image (below) lists all packages (you can type entlib into the search Online field) with a green-tick indication of those you have already installed.
NuGet Package Manager for Solution
This article focuses on the Data Access Application Block.
Problem
Rewriting common code, for every specialised implementation of application code, is not very optimal because it is error prone, because it does not lend itself to reproducing exactly the same implementation. This creates a maintenance dependency, as errors may vary in the different code blocks that essentially do the same thing. A standardised layer of optimised code, substituting common boiler-plate code plumbing, should be used. This layer is the Microsoft Enterprise Library.
This article looks at standardising database access through the enterprise library.
Solution
Enterprise Library Installation
Download and install Microsoft Enterprise Library, or use NuGet (recommended). Create a Visual Studio 2012 console application project, to test a data retrieval. Add references of the Enterprise Library to the project, or if you have used NuGet, these are added automatically to the solution.
NOTE: NuGet adds a new config file, i.e. packages.config, to track installed packages and versions, but, this file is not part of the project and should not go into source control.
Configuration & Code
Once a reference has been added to the project, have a look at the Codeplex [2] examples for download. From this example: [EntLib Dev Guide RC – Code Samples.zip], look in the DataAccess > DataAcess folder for the App.config file.
Two sections are important, viz. configSections, and connectionString. These have to be added, with the former being configured to point at the installed dataConfiguration, i.e. Enterprise Library. The latter is the connection string to the actual database. See the next section on how to use the Enterprise Library Config. tool, to create/ change the app.config file. This is a whole lot less error prone than rolling your own.
Code (from the Microsoft example):
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
</configSections>
<dataConfiguration defaultDatabase="TradeInformation" />
<connectionStrings>
<add name="TradeInformation" connectionString="Database=TradeInformation;Server=TradeDatabasseServer;Integrated Security=SSPI"
providerName="System.Data.SqlClient" />
</connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>
NOTE: Instead of editing the configuration file manually, it can also be done via a tool that provides a user-screen. Simpy right-click on the configuration file in the project (the one referring to Enterprise Library) and select Edit Configuration File, from the menu list. For more detail, see the MSDN example [3]: Using the Configuration Tools. If this option does not show up (right-click) make sure you have the version that targets Visual Studio 2012, in stalled: http://visualstudiogallery.msdn.microsoft.com/029292f0-6e66-424f-8381-3454c8222f9a [4], restart Visual Studio.
EnterpriseLibrary.Config Right-Click
When using the Enterprise Library.Config (right-click the app.config) application to configure the data access configuration. The user-interface requires that a Block (Add Data Settings) be added. In the screen-grab (below) the app.config file can be seen, with the EnterpriseLibrary.Config user-interface, used to create it with below. Doing it this way eliminates human configuration errors.
Enterprise Library.config
The defaultDatabase setting is a default connection, chosen by the GetInstance() method, of the connection listed first, below, when no database key is specified for the method. The second example illustrates a connection, made by specifying the database connection key/ name.
_db = EnterpriseLibraryContainer.Current.GetInstance();
_db = EnterpriseLibraryContainer.Current.GetInstance("TradeInformation");
NOTE: If you get the error “The OutputPath property is not set for this project” when building the solution, make sure that the Configuration Manager (Debug, Release), as well as the Properties of every project in the solution are targeting the same CPU, e.g. x64. Also refer to [http://gabrielmagana.com/2010/04/solution-to-the-outputpath-property-is-not-set-for-this-project/] and [http://connect.microsoft.com/VisualStudio/feedback/details/518181/msbuild-gets-confused-with-build-configuration-and-starts-generating-output-path-errors], if this is not the problem.
Code Example
Using the Codeplex example, I changed it a bit to make a simple Enterprise Library database call to defaultDB over which an ExecuteReader loops to print each record (in this case: trade) out to the console window.
I had a few issues with the .dll file references of Enterprise Library files. The short of it is that when these come into the project via NuGet, they are copied (the packages) into the c:users<your profile>DocumentsVisual Studio 2012Projects<project name>packagesEnterpriseLibrary.Common.5.0.505.0libNET35<.dll name>. A copy also resides in the bin folder:
- Microsoft.Practices.EnterpriseLibrary.Common.dll
- Microsoft.Practices.EnterpriseLibrary.Data.dll
- Microsoft.Practices.Unity.Configuration.dll
- Microsoft.Practices.Unity.dll
- Microsoft.Practices.Unity.Interception.Configuration.dll
- Microsoft.Practices.Unity.Interception.dll
The project references point to the “packages” folder (build), but the application references the bin folder, at run-time. Make sure that you have these references pointing to the correct .dll files for the Enterprise Library. You could find yourself with endless problems when the defaultDB = EnterpriseLibraryContainer.Current.GetInstance(); attempts to get the database instance, using the Enterprise Library. It will either complain about not being able to load the correct .dll files, or that the database key (referring to the database name of either your named or default database in the app.config file) could not be loaded.
Code (Program.cs):
Code:
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.ComponentModel;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace za.co.acme.DataAccess
{
class Program
{
static Database defaultDB = null;
static void Main(string[] args)
{
defaultDB = EnterpriseLibraryContainer.Current.GetInstance<Database>();
ReadTradesToConsole();
}
private static void ReadTradesToConsole()
{
String tSQL = "SELECT TradeID, ReceivedSequenceNumber, TradeDateTime, TradePrice, TradeSize FROM [dbo].[TradeInformation]";
using (IDataReader reader = defaultDB.ExecuteReader(CommandType.Text, tSQL))
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine("{0} = {1}", reader.GetName(i), reader[i].ToString());
}
Console.ReadLine();
}
}
}
}
}
The Codeplex exmaple provides a list of different usage patterns for the data, e.g. reading data from a stored procedure (with named/ unamed parameters), reading data as objects/ XML/ Scalar value, even doing all of this asynchronously. The asynchronous methods check the Enterprise Library’s public virtual bool SupportsAsync { get; } method, to see if the operation is possible, before attempting it.
Citations
Last Updated: 19 March 2013 09h19
-26.054097
28.064559
