Enterprise Library-Data Access Block

Enterprise librar- Data AccessProgramBlock Preface

When writing a program, we will inevitably access the database. when accessing the database, we will certainly encounter the following problems:

1)RepeatedCode--Writing (cutting and pasting) the same data access code throughout your data access layer

2)Hard coding --Matching Stored Procedure Parameter definitions with the calling application code

3)Concerns about the connection pool --Wondering if your code is properly closing connections

4)Write your own component to simplify calling the stored procedure --Writing a component to make it simpler to call stored procedures

5)How to store connection strings --Wrestling with where/how to store connection string Information

Of course, we can solve these problems ourselves, but the data access block provides us with these features. Why don't we use them?

I, Our goal

1)A simple and effective method --A simple and efficient way of working with commonly used databases

2)Transparency between multiple databases --Transparency when developing for multiple types of databases

3)Loose coupling between logic and physical databases "--A way to place an indirection between a logical database instance and a physical database instance

4)Simple Verification Method --An easy way to adjust and validate the Database Configuration Settings

II, Data Access Application Block What can be done

1)Provides best practices-Provides access to the most often used features of ADO. Net with applied best practices

2)Improve consistency-Write code that works against multiple database brands (caveats apply !)

3)Improved security-Leverages the configuration application block to securely store connection strings

4)Improved usability-Easily call a stored procedure with one line of code

III, Design Data Access Application Block

Goals of the data access block:

1)Integrated Execution of database access logic

2)Eliminate Common Code errors, such as connection failure

3)Reduce developers' need to write the same code for common data access

4)Reduces the writing of General Code

5)Image. NetSimilar to the data access architecture wizard, it is the best practice for data access integration.

6)Make the data access Block Function applicable to different databases as much as possible

The relationships between classes in the Data Access block are as follows:

 

IV, Start practice

Finally, we know what data blocks are for data access.

1) CreateWindowsOrWebApplicationMicrosoft. Practices. enterpriselibrary. Common. dllAndMicrosoft. Practices. enterpriselibrary. Data. dllReference to the project.

2) Add the following namespace to the file header.Using Microsoft. Practices. enterpriselibrary. Data

3) See the following code:

 

Database DB =   Null ;

DB = Databasefactory. createdatabase ( " Quickstarts instance " );

 

Int Count = ( Int ) DB. executescalar (

Commandtype. Text,

" Select count (*) from MERs " );

 

String Message =   String . Format (

" There are {0} customers in the database " ,

Count. tostring ());

 

MessageBox. Show (Message );

 

(Note:DB. executescalarThis command has polymorphism, IT andSqlcommand. executescalarThe returned results are the same.DB. executescalarCall method dependency and open and close the database defined in the configuration file .)

4) Read another piece of code.

 

Database DB =   Null ;

DB = Databasefactory. createdatabase ();

 

Dataset DS = DB. executedataset (

Commandtype. Text,

" Select * from MERs " );

 

Datagrid1.datasource = DS. Tables [ 0 ];

(Note:

DB. executedatasetMethod dependency and enable or disable the connection, and returnSQLThe Assembly filled in by the query result that may contain multiple tables. In this Code, we did not specifyCreatedatabaseMethod To create a database instance. In fact, there is a default database in the configuration file, which uses this default database .)

5) Then let's look at the application of the stored procedure.

A. Defines a member variable, which will be used in multiple processes

Private Database _ DB = databasefactory. createdatabase ("quickstarts instance ");

(Note: Let's look at the definition of this member variable. It is not just a database instance, but A description of the database)

B. Execute the Stored Procedure

 

Using (Idatareader datareader = _ DB. executereader ( " Getcategories " ))

{

// Processing code

While (Datareader. Read ())

{

CATEGORY item =   New Category (

Datareader. getint32 ( 0 ),

Datareader. getstring ( 1 ),

Datareader. getstring ( 2 ));

This . Cmbcategory. Items. Add (item );

}

}

(Note: here, you have not managed any database connection, but release

Data ReaderThe return value is very important, which is completed in the above Code. WhenData ReaderReleased,DbconnectionIs also disabled. InDatabaseThere are two methods to return the dataset,ExecutedatasetAndLoaddataset.ExecutedatasetReturns a newly created dataset, whileLoaddatasetIs to assemble an existing one .)

6) Update database

 

// Todo: Use the dataset to update the database

System. Data. Common. dbcommand insertcommand =   Null ;

Insertcommand = _ DB. getstoredproccommand ( " Holaddproduct " );

_ DB. addinparameter (insertcommand, " Productname " ,

Dbtype. string, " Productname " , Datarowversion. Current );

_ DB. addinparameter (insertcommand, " Categoryid " ,

Dbtype. int32, " Categoryid " , Datarowversion. Current );

_ DB. addinparameter (insertcommand, " Unitprice " ,

Dbtype. Currency, " Unitprice " , Datarowversion. Current );

 

System. Data. Common. dbcommand deletecommand =   Null ;

Deletecommand = _ DB. getstoredproccommand ( " Holdeleteproduct " );

_ DB. addinparameter (deletecommand, " Productid " ,

Dbtype. int32, " Productid " , Datarowversion. Current );

_ DB. addinparameter (deletecommand, " Lastupdate " ,

Dbtype. datetime, " Lastupdate " , Datarowversion. Original );

 

System. Data. Common. dbcommand updatecommand =   Null ;

Updatecommand = _ DB. getstoredproccommand ( " Holupdateproduct " );

_ DB. addinparameter (updatecommand, " Productid " ,

Dbtype. int32, " Productid " , Datarowversion. Current );

_ DB. addinparameter (updatecommand, " Productname " ,

Dbtype. string, " Productname " , Datarowversion. Current );

_ DB. addinparameter (updatecommand, " Categoryid " ,

Dbtype. int32, " Categoryid " , Datarowversion. Current );

_ DB. addinparameter (updatecommand, " Unitprice " ,

Dbtype. Currency, " Unitprice " , Datarowversion. Current );

_ DB. addinparameter (updatecommand, " Lastupdate " ,

Dbtype. datetime, " Lastupdate " , Datarowversion. Current );

 

Int Rowsaffected = _ DB. updatedataset (

This . Dsproducts,

" Products " ,

Insertcommand,

Updatecommand,

Deletecommand,

Updatebehavior. Standard );

(Note: when updating the database, we need to manually create the parameters of the stored procedure.

DatatableThe ing between columns and stored procedure parameters. HereUpdatedatasetMethod. You can obtain the data stream to execute all update transactions .)

7) ExploitationEnterprise Library ConfigurationConfiguration file for configuration items

A. Open the configuration file

If you have a definition inMachine. configFile connection section, you will findEnterprise Library ConfigurationThe tool is automatically created.Data Access Application Block.

 

If not, you can create one by yourself, as shown below:

 

B. SelectThe Data Access Application Block | connection strings | connection string node. Change the name property to quickstarts instance.

 

C.Select the database node for this connection string. Change the value property on the right hand side to entlibquickstarts.

 

D. SelectThe server node, and set its value to "(local) \ sqlexpress ".

 

E.Select the Data Access Application Block node. Set the defaultdatabase property is to the quickstarts instance. Then, save it.

 

References

Download Enterprise Library and related resources from:

Http://msdn.microsoft.com/practices

Http://msdn.microsoft.com/library? Url =/library/en-US/dnpag2/html/entlib2.asp

Join Enterprise Library community:

Http://practices.gotdotnet.com/projects/entlib

Read blogs from the Enterprise Library team:

Http://msdn.microsoft.com/practices/Comm/EntLibBlogs/