AbstractIf 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.

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.

CreateDatabase(): This method is used to create a new instance of the database being used in the application, called a database object.

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.

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.

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 EncrypttoFalse.

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 XmlProviderand 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 DataAccess Application Block node. This node contains three child nodes:

Connection Strings

Database Instances

Database Types

The Connection Strings nodeis 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:

Database

Integrated Security

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 InstancesandDatabase 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.

Title:
source code
Name:
phani
Date:
2011-02-14 12:06:50 AM
Comment: HiYour 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

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

ThanksAhmed

Title:
Query!!!
Name:
Dipti
Date:
2010-04-28 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:
SQL Server authentication
Name:
gulfraz
Date:
2009-02-25 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:
2009-01-06 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:
2008-11-12 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:
2008-10-28 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:
2008-09-18 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:
2008-09-18 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.

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.

Title:
samples for enterprise library january 2006 version
Name:
Jayanthy
Date:
2008-03-27 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:
Bad article
Name:
Matt Cameron
Date:
2007-12-10 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.

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:
2007-10-31 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:
2007-07-25 3:00:44 PM
Comment: Thanks for great work!

Title:
Great !
Name:
Prakash
Date:
2007-02-05 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:
2006-12-08 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?

Title:
Need for DAAB uisng Enterprise Library 2.0
Name:
venkatesh
Date:
2006-11-22 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:
Connection Timeout!!
Name:
Kirk Rose
Date:
2006-04-04 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...

Title:
Connection Timeout!!
Name:
dan
Date:
2006-03-06 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:
2006-02-08 7:13:37 AM
Comment: HelloYou 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

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

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:
2005-09-27 6:12:44 PM
Comment: I want to access datareader from another class how I can do that.

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:
2005-08-25 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:
2005-08-24 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:
2005-08-22 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:
2005-08-04 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:
2005-08-04 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:
2005-08-03 10:54:57 PM
Comment: how can i use daab in asp.net?

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

Hope that helps,

Title:
Re: Re: UpdateDataSet()
Name:
Dave
Date:
2005-08-03 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:
2005-08-02 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:
2005-08-02 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:
2005-07-30 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:
2005-07-25 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:
2005-07-25 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:
2005-07-25 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:
2005-07-25 3:19:38 AM
Comment: It's really helpful article with examples, so that easy to understand as well to implement