In this article, I will explain you how to how to use Microsoft Enterprise Library to connect to SQL-Server database and perform some DML operations like select, insert, update and delete with simple examples and also using stored procedure.

The Microsoft Enterprise Library is a collection of reusable software components (application blocks) designed to assist software developers with common enterprise development cross-cutting concerns (such as logging, validation, data access, exception handling, and many others). There are various versions available.

1. Enterprise Library 6.0 (April 2013) - Current
2. Enterprise Library 5.0 Windows Azure Integration Pack (Dec 2011) - Current
3. Enterprise Library 5.0 Optional Update 1 (May 2011) - Current
4. Enterprise Library 5.0 Silverlight Integration Pack (May 2011) - Active
5. Enterprise Library 5.0 (April 2010) - Active
6. Enterprise Library 4.1 (October 2008) - Deprecated
7. Enterprise Library 4.0 (May 2008) - Deprecated
8. Enterprise Library 3.1 (May 2007) - Deprecated
9. Enterprise Library 3.0 (April 2007) - Deprecated
10. Enterprise Library 2.0 (January 2006) - Active/Retired
11. Enterprise Library 1.1 (June 2005) - Deprecated
12. Enterprise Library 1.0 (January 2005) - Deprecated

Related Articles

1. Calling stored procedure with OUTPUT parameter using C# (C-Sharp) in ASP.Net
2. How to pass table-valued parameter to stored procedure using C# with ado.net

Creating application

In this article, I will explain you with single-tier application. Later on I will explain you how to use Microsoft Employee Library's all the blocks with data access layer. First of all, create a ASP.NET Empty Web Application.

Installing Enterprise Library Using Package Manager Console

Next step is to install Microsoft Enterprise Library. You can download Microsoft Enterprise Library's application blocks from Microsoft's website or can install using Package Manager Console. To install using Package Manager Console, go to Tools-> NuGet Package Manager-> Package Manager Console, after that type "Install-Package EnterpriseLibrary.Data -Version 5.0.505" and press enter as shown below.

After successful installation, you can check in references folder there all the necessary dll files will be added.

Creating Table

We need to create a table. Below is the script to create sql table.

create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10)
)
insert into tblEmployee values('Rahul','Male')
insert into tblEmployee values('John','Male')
insert into tblEmployee values('Mery','Female')
insert into tblEmployee values('Manish','Male')

Include Namespace

Add below namespaces.

using System.Configuration;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

Execute Select Query

Below is the code to execute select query with ExecuteDataSet command.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
DbCommand dbCommand = db.GetSqlStringCommand("select * from tblEmployee");
dbCommand.CommandType = CommandType.Text;
DataSet ds = db.ExecuteDataSet(dbCommand);

Execute Insert Query

Below is the code to insert record with ExecuteNonQuery command.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
DbCommand dbCommand = db.GetStoredProcCommand("insert into tblEmployee values(@Name,@Gender)");
dbCommand.CommandType = CommandType.Text;
 
db.AddInParameter(dbCommand, "@Name", DbType.String, "Mathew");
db.AddInParameter(dbCommand, "@Gender", DbType.String, "Male");
 
int Result = db.ExecuteNonQuery(dbCommand);

Execute Stored Procedure with OUTPUT Parameter

Next is to execute stored procedure, which will return number of Male Employee or Female Employee based on Gender supplied from INPUT parameter. Below is the script to create stored procedure.

create procedure usp_EmployeeGender
@Gender varchar(10)=null,
@GenderCount int output
as
begin
select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender
end

Below is the code to call stored procedure with output parameter.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
 
DbCommand dbCommand = db.GetSqlStringCommand("usp_EmployeeGender");
dbCommand.CommandType = CommandType.StoredProcedure;
 
db.AddInParameter(dbCommand, "@Gender", DbType.String, "Male");
db.AddOutParameter(dbCommand, "@GenderCount", DbType.Int32, 4);
 
db.ExecuteNonQuery(dbCommand);
 
int GenterCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@GenderCount"));