Let’s dive a bit into coding this time…
Any average line-of-business application needs to retrieve data from a database via stored procs, as a dataset or a scalar (single value). I thought it might be worthwhile to provide a quick reference by writing down the process and sample code for doing this with .NET using the Microsoft Enterprise Library.
For those of you who don’t know, the Microsoft EntLib is a (code and binary) library consisting of a set of blocks that provide standard set of functions for commonly required tasks like data access, error logging, etc. Using it is highly recommended since it is a standard and well-designed piece that is already there for you…and the price is right; it’s FREE! You can download and read about it on the Patterns and Practices site on MSDN: http://msdn2.microsoft.com/en-us/practices/default.aspx
Here are the steps for implementing such a scenario:
- Design the solution
Figure out the best design to meet business needs, and how your classes would be laid out to be more isolated and flexible to accomodate future design and business changes. For e.g. Can you go from a Smart Client to a Web Application without really changing the Business and Data layers? But, I digress.
My designs typically have a DALC (Data Access Logic Component) layer in a separate project with classes that provide static methods for data retrieval.
- Project references
From your project, reference the EntLib 3.0 Data and Common modules.
Connection strings for EntLib are defined in the config file.
<section name=“dataConfiguration“ type=“Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=220.127.116.11, Culture=neutral, PublicKeyToken=null“/>
<add name=“appDB“ connectionString=“Data Source=SQLServer1;Initial Catalog=DB1;User ID=appUser;Password=appPW“ providerName=“System.Data.SqlClient“/>
<add name=“logDB“ connectionString=“Data Source=SQLServer2;Initial Catalog=logs;User ID=logUser;Password=logPW“ providerName=“System.Data.SqlClient“/>
The above example defines 2 connection strings. EntLib uses the defaultDatabase setting if no database is specified. This kind of configuration provides a lot of flexibility, Databases in different environments can be used in a code-agnostic way, by just changing the default database setting.
For e.g. you could have a QA setting in there for the Test environment, which could be set to be the default connection for testing purposes.
<add name=“appDBQA“ connectionString=“Data Source=SQLServerQA1;Initial Catalog=DB1;User ID=appUserQA;Password=appPW“ providerName=“System.Data.SqlClient“/>
Now, in reality I would have a different config file for QA, which would be maintained by Configuration Management, but that is another discussion.
In the DALC class, add the required “uses” clause at the top, to specify which namespaces to look in. The System.Data.Common is for using Datasets.
Typically you would have a method that retrieves data from a stored procedure by passing in parameter values. Something like this:
public static DataSet GetUsers(string name)
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand(“GetUsersForName”);
db.AddInParameter(cmd, “@name”, DbType.String, name);
DataSet dataset = db.ExecuteDataSet(cmd);
The above code essentially executes the stored procedure on the default database as defined the config file.
That’s all for now. Hope that helped.