Get Started with the Enterprise Library Data Access Application Block
page 1 of 11
Published: 25 Jul 2005
Unedited - Community Contributed
Abstract
If you have downloaded Microsoft's Enterprise Library Data Access Application Block, you may have had difficulty getting started with it. To help you, Bilal Haidar provides a number of simple examples that walk you through the most common uses of this new Application Block.
by Bilal Haidar
Feedback
Average Rating: 
Views (Total / Last 10 Days): 186879/ 296

Introduction

[ Download Code ]

The Enterprise Library Data Access Application Block is the new version of the Microsoft Data Access Application Block (DAAB). Whereas the previous DAAB was a stand-alone application block, the new DAAB is a part of the new Enterprise Library collection of application blocks. You can obtain the most recent version of the library from the Patterns & Practices Enterprise Library Developer Center.

Last week, I spent one whole day trying to figure out how to configure and work with the new Data Access Application Block. This was due to few resources being available on the web. I even visited the Data Access Application Block forum at the ASP.NET Forums, but found few good resources to start with. Even with the few articles I found, I was not immediately successful in learning how to use the different methods available in the new Enterprise Library.

For the above reasons, I decided to write this article; not to explain how to configure the Enterprise Library, but rather to provide sample codes that can be used in a data-driven ASP.NET website.

Outline

In this article, I will start by listing the methods in the Enterprise Library Data Access Application Block (which, from now on, I will refer to as EN-DAAB) that can be used to interact with the database. Then, I will show you how to define the EN-DAAB configuration section in either the App.config or Web.config configuration file. Later, I will present sample code for each method, to show how easy it is to work with those methods. Finally, I will recommend some online websites that you can visit and check the latest news on the EN-DAAB.

Enterprise Library Data Access Methods

The EN-DAAB contains a set of useful methods that can save developers from having to write their own methods for common database functions.

As you may know, the EN-DAAB allows you to interact with different databases including Microsoft SQL Server, Oracle, and IBM DB2. However, when using its methods, you will not notice the difference whether using SQL Server, Oracle, or any other database provider. Everything is encapsulated within those methods, forming a strong abstraction layer that hides from the developer the details of interacting with a particular database. Let us have a look at each method and give a brief explanation of its function.

  1. CreateDatabase(): This method is used to create a new instance of the database being used in the application, called a database object.
  2. ExecuteReader("ReaderName"): This method returns an IDataReader object.
  3. ExecuteDataSet("DataSetName"): This method returns a DataSet object.
  4. LoadDataSet(“DataSetName”): This method fills an existing DataSet object.
  5. ExecuteNonQuery(dbCommandWrapper): This method is used to execute a query that returns a set of fields in a row, and to insert, delete, and update records.
  6. ExecuteScalar(dbCommandWrapper): This method is used only when you want to retrieve a single value from the database.

Define the Configuration

The EN-DAAB requires a configuration file in order to function properly. If you are developing a Windows Forms application, the configuration file is named App.config. On the other hand, if you are developing an ASP.NET web application, the configuration file is named Web.config.

Now the question is how to configure the EN-DAAB? The answer is simple: follow the steps below.

Step1: From the Windows Start menu, navigate to Programs, Microsoft patterns & practices, Enterprise Library, Enterprise Library Configuration. Once you open the Enterprise Library Configuration program, you will see the following window:

Step2: Right-click on the Enterprise Library Configuration node and choose New Application.

Step3: Right-click on the new Application1 node, and select New, Data Access Application block.

You can see in the above figure the Configuration Application Block and the Data Access Application Block. I will first configure the Configuration Block, and then move to the other block.

Step4: Click on the dataConfiguration node and look at the right window. You have the choice of whether to encrypt the configuration section. I will choose not to encrypt the configuration file by setting the value of Encrypt to False.

Step5: Click on the XML File Storage Provider node to specify the filename that will hold the configuration in your application and the XML File Storage Provider. Rename the second field to XmlProvider and leave the first field as it is. Now, click on the Xml Serializer Transformer node, and rename this field to XmlTransformer.

Step6: In this step, I will configure the Data Access Application Block node. This node contains three child nodes:

  1.  Connection Strings
  2. Database Instances
  3.  Database Types

The Connection Strings node is the place where you add the connection string to your database. In this article I will choose to connect to a SQL database. Once you expand the Connection Strings node you will see a default connection string. Click on the Sql Connection String node, and in the right window you have the option of renaming the connection string. To configure the connection string, expand the Sql Connection String node, and you will see that there are three parameters:

  1. Database
  2. Integrated Security
  3. Server

Click on Database parameter, and in the right window write Northwind in the Value field. Then, click on the Server parameter, and in the right window put localhost in the Value field.  Leave all other parameters as they are, although you could have given them names according to your preferences. Note that you can add additional parameters to the connection string by right clicking on Sql Connection String node and adding other parameters.

The other two nodes, Database Instances and Database Types, can be left with their default values.

After you finish all the steps, open File menu item and select Save Application. When you are prompted to choose a file name, choose either the Web.config or the App.config file in your application.

Now that you have seen how to configure the EN-DAAB, let us put the application block into action.

Code Samples

In this section we are going to present a code sample for each of the methods listed above. With these samples you should get a better understanding of how to use these methods while developing your data access methods.

Be sure to add references to the following assemblies:

   Microsoft.Practices.EnterpriseLibrary.Configuration.dll
   Microsoft.Practices.EnterpriseLibrary.Data.dll

You can find these assemblies in the following path:

   [Drive Name]:\Program Files\Microsoft Enterprise Library\bin

In each code file you will need to include the following lines:

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

The database used for the following code samples is the Northwind database that ships with Microsoft SQL Server, and the data table used is the Customers table.

 


View Entire Article

User Comments

Title: rt   
Name: rt
Date: 10/23/2012 2:40:03 AM
Comment:
tt
Title: net_beginner   
Name: rgent
Date: 11/5/2011 8:38:21 AM
Comment:
Very helpful and clear article, Thanks!!!
Title: Sr. .NET Developer   
Name: Loften Pierce
Date: 4/26/2011 3:34:21 PM
Comment:
Great article! Very clear and concise!
Title: MELibrary   
Name: shiva
Date: 4/16/2011 4:38:37 AM
Comment:
Excelent Work Brother
Title: source code   
Name: phani
Date: 2/14/2011 12:06:50 AM
Comment:
Hi
Your article is good , But it is not working properly in enterprise library 5.0 , can you prepare an article on enterprise library 5.0 for us like beginners in Enterprise Library 5.0

Thank you,
Phani
Title: source code   
Name: Ahmed
Date: 1/12/2011 12:57:10 PM
Comment:
Hi Bilal,

I tried using your code for testing but I can't open it in VS2008, can you please help?

Thanks
Ahmed
Title: Query!!!   
Name: Dipti
Date: 4/28/2010 3:14:56 AM
Comment:
Hi i am new to Microsoft Enterprise Library, i am using it for My DAL in my application. i am not aware any library of microsoft etrprise is containing DbCommand, DbConnection etc..Can you tell me please which name space do i have to import or i am suppose to use System.Data.Common library..
Thanks!!
Please Answer
(04cp271dipti@gmail.com)
Title: Syntax error.   
Name: Dipal Bhavsar
Date: 12/16/2009 11:50:02 PM
Comment:
Syntax wrong.
Use Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
instead of
using Microsoft.Practices.EnterpriseLibrary.Configuration;
Title: EntLib   
Name: Michael Doug
Date: 7/26/2009 2:30:21 AM
Comment:
Hey is a great article for learning and developing Data Access Application Block.
Title: good   
Name: Abhishek Jha
Date: 7/9/2009 7:49:04 AM
Comment:
Thanks a lot u have solved a great deal of problem i have getting in learning about enterprise library,good going
Title: Enterprise Library Wrapper Class to Implement Run-time Dynamic Connection Strings Without Using Config File   
Name: Raja Lakshman
Date: 6/11/2009 8:25:36 PM
Comment:
Have a look at the above URL (http://www.codeproject.com/KB/dotnet/EntLibWrapper.aspx) to use the DAAB in Enterprise Library with run-time dynamic connection strings.

Hope this helps.

Cheers
Title: SQL Server authentication   
Name: gulfraz
Date: 2/25/2009 8:34:13 AM
Comment:
Hi Bilal!
In my desktop application, I read an xml config file and build the connection string at runtime by using an encrypted password. The connection uses SQL Server authentication. How can I set the connection string at runtime to use sql server authentication with Enterprise Library.


Thank You
Title: Get Started with the Enterprise Library Data Access Application Block   
Name: Mrugesh
Date: 1/6/2009 4:57:54 AM
Comment:
This is very helpful article for dotnet users new with Enterprise Library.
Title: entlib with oracle parameter   
Name: Mahir
Date: 11/12/2008 2:43:58 AM
Comment:
I am facing problem with parameter in oracle. I don't know the exact reasons. The value is not passing to the procedure.
Title: Well done   
Name: Tanweer Hussain
Date: 10/28/2008 6:24:36 PM
Comment:
Even the aricel is based on old Enterprise Library. I felt his article is making life easier,
Title: Re: Raji   
Name: Bilal Haidar
Date: 9/18/2008 3:30:56 PM
Comment:
Well, this article was on an old version of the library.

Check the documentation on the Ent. Lib 4.0, they have good resources!

Regards
Title: No Title   
Name: Raji
Date: 9/18/2008 3:22:26 PM
Comment:
This is very old. For example, DBCommandWrapper no longer works. It is very frustrating to find limited resources about the Enterprise Library, and if you happen to find it, it turns out to be too old and no longer applies to the current version.
Title: Little Candle in The Dark   
Name: Gatot 'Gatz' Wahyudi
Date: 9/2/2008 5:01:23 AM
Comment:
Dear Haidar,

Thank you so much for your helpful article. Like you said before, I didn't find any article that explained this material in more easy way before. Then i found your article. It likes a little candle in the dark. It's enlightening. Thank you.

Best regards,

Gatot Wahyudi
Title: Nice Article   
Name: Anil
Date: 7/7/2008 5:16:46 AM
Comment:
Hi ...


Thanq very much for giving this good article. it helps me a lot...

Regards,
Anil Kumar
Title: ENTLIB   
Name: Vivek Vashisht
Date: 7/1/2008 1:17:06 AM
Comment:
Thanks Bilal.
You have done a nice job over here...
Thanks once again
Title: samples for enterprise library january 2006 version   
Name: Jayanthy
Date: 3/27/2008 6:56:39 AM
Comment:
could you please post some samples for enterprise library january 2006 version , it would be most helpful.The configuration wizard is not the same and so it is difficult to understand.Thank you in advance.
-Jayanthy
Title: Great Article   
Name: Ahmad Shadi
Date: 2/18/2008 11:56:27 AM
Comment:
Thank u very much for this powerfull article
Title: Help please   
Name: Vi
Date: 1/24/2008 9:23:03 AM
Comment:
I like this article. It`s easy to understand.
Thanks ^^

But can u help me..
I`m already install Enterprise Library January 2006, but
why my Enterprise Library Configuration won`t open?

There is an error when I build my Enterprise Library.
Maybe there is something I missed?

thanks a lot
Title: Application Blocks   
Name: Joe
Date: 1/15/2008 10:38:56 AM
Comment:
Thanks
Title: Bad article   
Name: Matt Cameron
Date: 12/10/2007 9:09:04 AM
Comment:
Pointless and Senseless man, I am sorry I wasted my 15 mins on this article because on of my friend sent it to me. I guess you need to update this article or take it off man.

Sorry again, I wouldnt had posted the comment if I would not had wasted time on this.
Title: Application Blocks   
Name: mrudula
Date: 11/5/2007 6:28:54 AM
Comment:
Hi this article is very useful .but i have small doubt.

i am using Enterprise Library 2.0 and 3.0 but i didnt find

Configuration application Block.

please help where i can find this block to encrypt my configuration settings plzzzzzzzzzzz help me

Thanks in advance
Title: Very Good   
Name: RichB
Date: 10/31/2007 3:04:11 PM
Comment:
One of the first articles I've read that actually makes me think that I might actually use this stuff.
Title: You are the MAN!!!!   
Name: Tim
Date: 7/25/2007 3:00:44 PM
Comment:
Thanks for great work!
Title: Great !   
Name: Prakash
Date: 2/5/2007 5:07:13 AM
Comment:
I am a newbie. I found this article excellent but can anyone refer me using Enterprise Library 2.0 using Visual Studio 2005 as concise and comprehensive this is !
Title: Data access block - January 2006   
Name: RVM
Date: 12/8/2006 1:25:33 PM
Comment:
I am using the last version of enterprise library(jan 06).
i need to configure my web file for dataaccess app block but... following these steps, i just see the "data access application block" node.

how can i add the other nodes, i mean, the "configuration application block", "database instances" and "database type" node?

or.. in the last version (jan 06), is this not posible?

thanks a lot
Title: Configuring Oracle Connection   
Name: Faisal
Date: 12/6/2006 2:16:43 AM
Comment:
I need to configure Oracle database connection string using Enterprise Configuration Library tool. Can you give some tips about this. Thanks :)
Title: Need for DAAB uisng Enterprise Library 2.0   
Name: venkatesh
Date: 11/22/2006 2:14:39 AM
Comment:
Its a great Article. But it would be helpful if you could provide the same using the Enterprise library 2.0.
Title: Nice one   
Name: krishna kumar
Date: 8/8/2006 2:02:41 PM
Comment:
hi so useful, simple beauty in presntation, easy for beginers
Title: EL DAAB Command Timeout   
Name: Techno_Dex
Date: 7/27/2006 5:16:28 PM
Comment:
DbCommand dcCommand;
DataSet dsDataSet
string[] saTableNames;

In the new EL DAAB, I have discovered another way to set the Commmand Timeout without modifying the EL code, but I would see the P&P people include Kirk's overloaded methods.

Use this
dcCommand = dDatabase.GetSqlStringCommand(sSQLCommand);
dcCommand.CommandTimeout = 60;
dDatabase.LoadDataSet(dcCommand, dsDataSet , saTableNames);

Instead of this
dDatabase.LoadDataSet(CommandType.Text, sSQLCommand, dsDataSet , saTableNames);
Title: Excellent !!   
Name: Dalton Cernach de Andradde
Date: 7/14/2006 4:12:43 PM
Comment:
Thats great, Its the most compelte introduction to DABB that I've seen.
Title: ELAB   
Name: Harsha
Date: 7/11/2006 1:10:15 PM
Comment:
Good One... Thanks
Title: Re:   
Name: Bilal Hadiar [MVP]
Date: 6/1/2006 2:29:16 AM
Comment:
Hi to all:
Thanks a lot for you all for the great comments you honored me with!

Hope I will always be able to deliver good content to all of you!

Regards
Title: Get Started with the Enterprise Library   
Name: Nikhil
Date: 6/1/2006 2:21:16 AM
Comment:
Very helpful article indeed.Good job.
Title: Using Data Access Block with Oracle   
Name: Hrishi
Date: 5/23/2006 12:54:09 AM
Comment:
Really nice article..

I want to know how to use Ent.Library(DAAB) for Oracle coz lot of online material is for Sql server..If any steps or links are provided for oracle use it would be really nice.
Title: Good one   
Name: Subarno
Date: 4/17/2006 5:32:53 AM
Comment:
Nice. Both concise and comprehensive.
Title: Great Title   
Name: Evangelos Hadjichristodoulou
Date: 4/15/2006 4:59:50 PM
Comment:
Great Job there Bilal
Title: Connection Timeout!!   
Name: Kirk Rose
Date: 4/4/2006 1:00:53 PM
Comment:
I had the same issue and had to write code to extend SQLHelper ExecuteDataset methods. I basically copied one of the overloaded ExecuteDataSet methods, added a timeout parm and then just added a single statement to set the command's timeout...

Hope this helps...

Public Overloads Shared Function ExecuteDataset(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal commandTimeout As Integer, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dataAdatpter As SqlDataAdapter
Dim mustCloseConnection As Boolean = False
cmd.CommandTimeout = commandTimeout
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
Title: Connection Timeout!!   
Name: dan
Date: 3/6/2006 12:12:47 AM
Comment:
Hi...
DAAB is awesome. But i have one problem using it. Im using DAAB in my application. Im using SQLHelper and its methods(ExecuteDataset to be specific) in Data Access. But my query takes almost 15 minutes to execute. But while executing this query my browser times out after 30 seconds. So after searching i found out that 30 sec is default for any SQL connection and can be changed using command objects timeout property. Since im not using command object can you please tell me how to set timeout using SQLHelper?.
Title: Oracle Connection   
Name: Bilal Haidar
Date: 2/8/2006 7:13:37 AM
Comment:
Hello
You should create a new Profile provider to make it work with Oracle.
This is out of the topic in this article but you can find many such links if you try to do a simple search on google.com

Thanks.
Title: Getting error when connect to oracle   
Name: Toral Gohil
Date: 2/8/2006 7:12:01 AM
Comment:
Hello Bilal Haidar,

I am getting error when i tried to connect to the oracle. I have done all the things that you have stated above. But still it says service not found. Please if you can help me for solving the same. My Id is gohil_toral@yahoo.co.in
Title: Excellent Job!   
Name: Patrick
Date: 12/9/2005 10:40:46 PM
Comment:
Awesome job on this article!
Title: Mr.   
Name: Bilal Haidar [MVP]
Date: 11/4/2005 11:11:39 AM
Comment:
Thank you all for your great comments. I pray to God to be able to present the best for you all the times.

Regarding the "LIKE", I beleive it is better to use "Like" when comparing string or varchars in SQL Server.

Regards
Title: LIKE?   
Name: yourq1
Date: 11/3/2005 8:18:49 PM
Comment:
Haidar,
Why is LIKE used and not equals (=) ?
I understand it's an nchar(5). Ex.:

DELETE
[Customers]
WHERE
[CustomerID] LIKE @CustomerID

That seems to me like an inappropriate generalization?
Please explain! Thanks.
Title: OUTSTANDING   
Name: Sgt13Echo
Date: 11/2/2005 12:28:11 PM
Comment:
This was BY FAR the best tutorial I have seen on application blocks!

I wish more were like this ONE

I GIVE IT A 10

Out!
Title: Ms.   
Name: Preeti
Date: 10/18/2005 10:18:20 AM
Comment:
Hi Bilal,

Excellent code. Very Clear.
I did the intial configuration the way its said here. I keep getting the error like "Parser Error Message: Invalid section name.
The section 'dataConfiguration' does not
exist in the requested configuration file ...."
Could you please help me resolve it?

Actually I am trying to have data access as a seprate class library and then include that dll into my web application.
Title: How to call datareader from another Class   
Name: Tanweer
Date: 9/27/2005 6:12:44 PM
Comment:
I want to access datareader from another class how I can do that.

using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
while (dataReader.Read())
Title: Strongly Typed DataSet (to Rosie)   
Name: sloan
Date: 9/7/2005 6:33:54 PM
Comment:
Public Function TitlesDSGet() as MyStronglyTypedDS

dim returnDS as MyStronglyTypedDS = new MyStronglyTypedDS

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim dbCommandWrapper As dbCommandWrapper = db.GetStoredProcCommandWrapper("dbo.uspTitlesGetAll")


db.LoadDataSet(dbCommandWrapper, returnDS, New String() {"Titles", "Publishers"})


Return returnDS

naturally, this example has the strongly typed dataset having 2 tables "Titles" and "Publishers", and the usp will return 2 result sets.

I have only tried that code on Sql Server 2000, fyi.
Title: Re: Richard   
Name: Bilal Haidar [MVP]
Date: 8/25/2005 2:28:10 AM
Comment:
Hi Richard:
I am glad this article was of help to you.
Thanks for the comments on my blog, I did some changes, check them when you have time.

Regards
Title: Very Helpful   
Name: Richard Dudley
Date: 8/24/2005 2:10:02 PM
Comment:
Great job, Bilal! I struggled with this thing for a while, and eventually gave it up for the old DAAB. After I read your article, I figured I'd try the EL one more time, and it's working in my application now. Thanks!
Title: Very good   
Name: Tushar
Date: 8/22/2005 2:05:08 PM
Comment:
Do you have more such examples using other Application Blocks in ASP.NET applications...or do you know of any other links or resources that contains examples using other application blocks...???

Thanks...
Title: strongly type dataset   
Name: Rosie
Date: 8/4/2005 4:51:56 PM
Comment:
Can you use DBCommandWrapper to load a strongly typed dataset. LoadDataSet and ExecuteDataSet does not seem to work
Title: Re: how to use daab in asp.net   
Name: Bilal Haidar [MVP]
Date: 8/4/2005 1:50:47 AM
Comment:
Download the accompanying files from this article, there is a Web application in ASP.NET that tests all the mentioned methods.

Regards
Title: how to use daab in asp.net   
Name: jay
Date: 8/3/2005 10:54:57 PM
Comment:
how can i use daab in asp.net?
Title: Re:   
Name: Bilal Haidar [MVP]
Date: 8/3/2005 9:04:14 AM
Comment:
You can do something like that:

// Build the SQL Query Here
string sqlCmd = "SELET CustomerName FROM Customers WHERE CustomerID=1";
DbCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCmd, CustomerID);
string CustomerName = (string)db.ExecuteScalar(dbCommandWrapper);

You can use the GetSqlStringCommandWrapper() too, if you don't have any parameters.

Hope that helps,
Title: Re: Re: UpdateDataSet()   
Name: Dave
Date: 8/3/2005 8:50:04 AM
Comment:
Maybe I'm missing something here. I'm using the latest release of Enterprise Library with Visual Studio 2005 Beta2. I only have two overloads for GetStoredProcCommandWrapper - with and without parameters. There aren't any overloads for GetSqlStringCommandWrapper. I even made sure to open up the source projects and check there.

dave
Title: Re: UpdateDataSet()   
Name: Bilal Haidar MVP
Date: 8/2/2005 4:58:40 PM
Comment:
Hello, check all the overloads of the method GetStoredProcCommandWrapper, it has several overloads one of them you can pass inline query.
If still couldn't find it, ping me again please.

Regards
Title: Oracle SQL statements   
Name: Dave
Date: 8/2/2005 4:40:45 PM
Comment:
I'm working with both MS Sql and Oracle on this project. Most MS DBA's seem to favor stored procedures and most Oracle DBA's seem to favor using inline SQL. I am having a real hard time trying to get the UpdateDataSet method to work with SQL statements instead of stored procedures. Can you show an example of this?

Thanks, dave
Title: Good Job   
Name: Gregg
Date: 7/30/2005 12:40:16 PM
Comment:
Nice. Both concise and comprehensive.
Thank you for taking the time to put this together.
I now have a much clearer understanding than I did before.
Title: Eng.   
Name: Bilal Haidar [MVP]
Date: 7/25/2005 5:05:50 PM
Comment:
I am so happy I was able to make your life easy by providing such an article.
I am glad you liked it,
Thank you for the time you took to read it.

Best of luck,
Regards
Title: Superb   
Name: Rajeev Gopal
Date: 7/25/2005 4:24:42 PM
Comment:
This one is really helpful for the beginners and saves lot of time for them.

Kudos Bilal!
Title: Very Well placed   
Name: Farhan Shaikh
Date: 7/25/2005 12:08:30 PM
Comment:
smooth article and very well written by working with Microsoft Enterprise DAAB Lib. can you put your thought for Exception Handling and Cypto...
Title: Get Started with the Enterprise Library   
Name: Vishal
Date: 7/25/2005 3:19:38 AM
Comment:
It's really helpful article with examples, so that easy to understand as well to implement

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 7/29/2014 10:45:19 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search