Microsoft.NET Enterprise Library
Introduction:
Every day is a new day not only in our life but also in Technologies life. Just a few weeks ago I was using Microsoft.NET Data Access Application Block. I found Microsoft.NET Application Block very useful and enjoyed using it. In January Microsoft introduced Enterprise Library which consists of all the application blocks under one hood. In this article I will be talking about the "Data Access using Enterprise Library".
Advantages of using Enterprise Library:
Sometimes people ask me that what are the advantages of using Enterprise Library and why can't we be just happy using the common techniques to accessing data. The biggest and perhaps the most important advantage is that if one company sends his code to another company for validation or some other purpose and if the code is written using Enterprise Library it will be easier for the other company to understand since they know all the methods and functions of the Library. Another big advantage is that it has many blocks already made for you which you can use in your application.
Adding the References:
The first and the most important thing is to add a reference to the required dll files to work with the Enterprise Library. Add references to the following dll files:
Microsoft.Practices.EnterpriseLibrary.Data;
Microsoft.Practices.EnterpriseLibrary.Common;
Microsoft.Practices.EnterpriseLibrary.Configuration;
Creating a Database object:
In Enterprise Library you can actually make a Database object which maps to the database which you have specified in the Configuration File.
This line will create a default database object:
Database db = DatabaseFactory.CreateDatabase();
You can also create an instance of the database:
Database db = DatabaseFactory.CreateDatabase("MyDatabase");
The advantage of this is that you can refer and change its configuration information any where without having to compile the code again.
Making command Wrappers:
Command wrappers are used to execute commands against the database. There are two types of command wrappers one can execute a simple text based Ad-hoc query and others can execute stored procedures.
Let's see how to make both of them:
string myCommand = "Select * FROM Categories";
DBCommandWrapper myCommandWrapper = dbSQL.GetSqlStringCommandWrapper(myCommand);
In this example we just set the string myCommad to the textual query that we want to execute which in this case is a simple query which selects all the items from the Categories database. Keep in mind that I will be using Northwind database in this article which is installed with SQL SERVER 2000 as the default database.
Later we have just attached the wrapper with our database object. Let's see how we can attach a simple stored procedure instead of the Textual query also it is always better to use the stored procedure since its better in performance and have higher security than the simple text based queries.
string sqlCommand = "GetProductsByCategory";
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
I think its really simple to understand that the string sqlCommand above is the name of the stored procedure. Later we have just attached that stored procedure to our database object.
Now lets see some common operations that you can perform using the objects provided in the Enterprise Library. Keep this in mind that Enterprise Library also differs from the Application Block in a way that it does not have static methods and you can make your objects of connections, commands etc.
Executing DataReader:
Executing datareader is very simple. Let's say we want to display all the CategoryName from the Northwind Categories table using DataReader.
First of all we need to make the database so it will know that what database we are using.
// Creates the database depending on the configuration information in the web.config file.
// This is the default of creating database object.
Database db = DatabaseFactory.CreateDatabase();
Next step as you have already guessed is creating the command object or the datawrapper object.
// Here I have created the Ad-Hoc Query which selects CategoryName from Categories
string sqlCommand = "Select CategoryName FROM Categories";
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
Next is to assign this query to the datareader so it will know that what to read.
// The use of the C# using keyword indicates that the reader will be closed automatically when its finished
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
// We will write this in the next step
}
// The use of the C# using keyword indicates that the reader will be closed automatically when its finished
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
StringBuilder readerData = new StringBuilder();
while (dataReader.Read())
{
// Get the value of the 'Name' column in the DataReader
readerData.Append(dataReader["CategoryName"]);
readerData.Append(Environment.NewLine);
}
}
Do you see this line
readerData.Append(dataReader["CategoryName"]);
It means that we are pulling the data out of the CategoryName column which we originally wanted right !
Finally ! and never forget this to close the reader:
if (dataReader != null)
dataReader.Close();
This will not only close the reader but also closes the connection.
Lets see one more type of Data Access which is very common. This is selecting data from the database using DataSet. In this example we will have a small textbox and we will enter the CategoryID. And the data is retrieved depending on the CategoryID sounds simple right let's see how this can be done.
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "GetProductsByCategory";
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);
// Retrieve products from the specified category.
dbCommandWrapper.AddInParameter("@CategoryID", DbType.Int32, Convert.ToInt32(txtCategory.Text));
// DataSet that will hold the returned results
DataSet productDataSet = null;
productDataSet = db.ExecuteDataSet(dbCommandWrapper);
As you see above most of the lines are common which we already had when we were using the SqlDataReader. We introduced parameters in this example which as we can see is very simple to add. txtCateogry is the textbox and we convert the value to int using the Convert method provided in the .NET library.
After this you have the dataset in the Dataset object called productDataSet. You can bind to the datagrid if you want or perform any other operation.
So you see that Microsoft is making things easier for you every day. With the arrival of Enterprise Library all the common operations that are performed in an Enterprise Library is placed under one hood. I hope you enjoyed this article and we will see more Enterprise Library blocks in the future articles.