Since the advent of the Enterprise Library, the Patterns & Practices group at Microsoft has made the job of a .NET developer simpler in myriad ways. One of the most common jobs of someone building Line of Business applications is invariably data access, and the Data Access Application Block (DAAB) is absolutely one of these time-savers.
Although we could spend quite a while discussing the various features, my favorite feature has to be the ability to pass an object array as the parameters in the stored procedure or SQL statement, as shown in this example:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace DoyleITS.Samples.Data
{
public class AdventureWorksDatabase
{
public IDataReader GetEmployeeManager(int EmployeeId)
{
Database db = DatabaseFactory.CreateDatabase("AdventureWorks");
object[] parameters = { EmployeeId };
DbCommand command = db.GetStoredProcCommand("dbo.uspGetEmployeeManagers", parameters);
return db.ExecuteReader(command);
}
}
}
This block of codes relies on the following information in the application configuration file:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections />
<connectionStrings>
<add name="AdventureWorks"
connectionString="Server=DOYLE002\SQL2K5;Database=AdventureWorks;Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<appSettings />
</configuration>
As you can see, it takes very little code to execute the stored procedure. The CreateDatabase() method of the DatabaseFactory determines the appropriate data access provider from the connection string (“AdventureWorks”), and investigates the stored procedure parameters and data types.
Using the data access class I’ve created is simple:
AdventureWorksDatabase db = new AdventureWorksDatabase();
IDataReader reader = db.GetEmployeeManager(1);
while (reader.Read())
{
// do stuff
}
Overall assumptions
The overall design of the DAAB seems to be that database connectivity information will always be provided in the application configuration file. This approach works great in websites, web services, and Windows services, where the process can run under a service account using integrated security. It also works well in a trusted environment using SQL authentication (knowing that credentials are stored in the configuration file). The underlying assumption, however, is that the connection information is known at build or deploy time.
Even in a non-trusted environment, or when you are deploying applications (and configuration files) to your user PCs, you can handle secure credentials. One method is to use another the Configuration Application Block, also in the Enterprise Library, to encrypt configuration information. Again, this must be done at build or deploy time.
What if you don’t know your database connection (server or database name, or even credentials) until runtime?
Imagine you have an environment in which you host multiple instances of an identically-structured database, one for each customer. Then imagine you have created an application which must manage data within any or all of those databases. You certainly wouldn’t want to maintain all connections within the application configuration file. Why not? If you get a new customer, you will have to update the configuration file and redeploy the application. If you are using deployment methods such as ClickOnce, you will be pushing new versions simply because you gained another data source.
Regardless of how you store and retrieve the customer database information (that’s an architectural design outside the scope of this discussion), once you know where you need to go, how do you plug that into the DAAB?
The first approach could be the GenericDatabase class, which inherits the DAAB’s abstract Database class. This object allows you to specify the connection string and the provider, as shown below:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.Common;
using DoyleITS.Samples.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace DoyleITS.Samples.Data
{
public class MultiCustomerDatabase
{
private string serverName;
private string databaseName;
public MultiCustomerDatabase(string ServerName, string DatabaseName)
{
this.serverName = ServerName;
this.databaseName = DatabaseName;
}
public Database GetDatabase()
{
DbConnectionStringBuilder builder = new DbConnectionStringBuilder();
builder.ConnectionString = ConfigurationManager.ConnectionStrings["MultiCustomerDB"].ConnectionString;
if (builder.ContainsKey("Server"))
builder["Server"] = this.serverName;
if (builder.ContainsKey("Database"))
builder["Database"] = this.databaseName;
string providerName = ConfigurationManager.ConnectionStrings["MultiCustomerDB"].ProviderName;
Database db = new GenericDatabase(builder.ConnectionString, DbProviderFactories.GetFactory(providerName));
return db;
}
}
}
The concept here is that you can dynamically generate and use the GenericDatabase to make runtime decisions on database connectivity. To cover things you may have noticed, you would definitely want validation in the MultiCustomerDatabase constructor. Also, in the GetDatabase() method, you would want to implement a more robust process of identifying, updating, or creating the necessary connection string elements (e.g., “Database” versus “Initial Catalog”).
The example above relies on a connection string, as shown below, but depending on your implementation, it is not completely necessary – again, another architectural design consideration.
<add name="MultiCustomerDB"
connectionString="Server=SPECIFY;Database=SPECIFY;Integrated Security=SSPI;"
providerName="System.Data.SqlClient"/>
The GenericDatabase accepts in the constructor the database provider information, stored in your connection string. This basically informs the DAAB of the provider (e.g., SQL Client, OLEDB) you wish to use.
Any caveats?
Unfortunately, the GenericDatabase does not support parameter discovery, which means the following code will not work:
public IDataReader GetEmployeeManager(int EmployeeId)
{
Database db = this.GetDatabase();
object[] parameters = { EmployeeId };
DbCommand command = db.GetStoredProcCommand("dbo.uspGetEmployeeManagers", parameters);
return db.ExecuteReader(command);
}
The above example uses my favorite DAAB feature, the parameter object array. A NotSupportedException will be thrown with the message, “Parameter discovery is not supported for connections using GenericDatabase. You must specify the parameters explicitly, or configure the connection to use a type deriving from Database that supports parameter discovery.” Yes, you would have to code every parameter and add it to the command object, before executing the stored procedure.
We have some choices to make: We can go through all of our data access code, and code the parameters to the command object manually, or we can alter our GetDatabase() logic.
Instantiating the correct Database
If I have only a few data access methods, it may be easiest to code the parameters. This would allow you to retain the database-agnostic approach. In many cases, you’ll have dozens of methods you would have to update, or worse yet, hundreds (if you were to use a base database class to handle database connectivity). The code sample below shows a modification to the GetDatabase() method to determine the appropriate Database implementation based on the provider name. My logic is that, in all reality, you will know which database technology or technologies you will support. If you decide to support more, like MySQL, you can update the switch statement.
First, I will add any using statements, to include technology-specific namespaces.
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
Now, I will use the switch statement to instantiate the Database object.
public Database GetDatabase()
{
DbConnectionStringBuilder builder = new DbConnectionStringBuilder();
builder.ConnectionString = ConfigurationManager.ConnectionStrings["MultiCustomerDB"].ConnectionString;
if (builder.ContainsKey("Server"))
builder["Server"] = this.serverName;
if (builder.ContainsKey("Database"))
builder["Database"] = this.databaseName;
string providerName = ConfigurationManager.ConnectionStrings["MultiCustomerDB"].ProviderName;
Database db = null;
switch (providerName)
{
case "System.Data.SqlClient":
db = new SqlDatabase(builder.ConnectionString);
break;
default:
db = new GenericDatabase(builder.ConnectionString, DbProviderFactories.GetFactory(providerName));
break;
}
return db;
}
Finally, here’s how I use it:
string server = "";
string database = "";
// ... logic to determine server/database ...
MultiCustomerDatabase db = new MultiCustomerDatabase(server, database);
IDataReader reader = db.GetEmployeeManager(1);
while (reader.Read())
{
// do stuff
}
Closing thoughts
Sometimes, simplicity is best. I could spend time implementing a more “robust” solution, that would handle any database technology, but again, I should know or assume what technologies I should support.
In a real-world implementation, I would also configure the connection string elements according to the provider name, eliminate case-sensitivity, check for the existence of all required elements, and add any missing elements.
No comments:
Post a Comment