Quickly build a data access layer within 15 minutes

From: http://www.cnblogs.com/caviare

 

Download link

• Download codesmith 30-day free trial Professional Edition
• Download the latest. nettiers Template
• Viewing bookArticleRelated video demonstration

 

Introduction

Through this article, you can learn how to use the Microsoft Enterprise Library, codesmith, And. nettiers templates to quickly build a data access layer within less than 15 minutes.

From now on, we have abbreviated the data access layer as Dal, which isProgramThe layer that interacts with the database. Handwritten Dal LayerCodeIt is a tedious and time-consuming repetitive activity, and many vulnerabilities may occur during program compilation.

Make sure that the SQL server's northwind database is installed, but this example can also run on any other database. Of course, after reading the article, you will not waste any time to write the Dal layer code for your application, but it will be faster and simpler.

After reading this article, you will use best practices within one minute to create a data access layer (the other 14 minutes are used to download the required software for the first time ).

Code Generation Overview

Code Generation, or code generation using tools and software, is not a new concept. In fact, code generation is quite common. In this article, we use codesmith to generate code and T-SQL scripts for the Dal layer.

Codesmith is a common tool for developers. It can use templates to output the desired format code. You can use a template to generate any code you want. This article uses a. nettiers template.

 

Step 1 install software

First, download codesmith and. nettiers, the former is a 30-day trial version (of course, there are also cracking tools on the Internet, the latest version is 3.2, is. NET 2.0, we can download version 3.1 or 3.0)

Download and install codesmith

Http://www.codesmithtools.com/

Codesmith has many built-in templates, and we will include them in the. nettiers template we need later. We have installed codesmith and downloaded the. nettiers template library.

Download the. nettiers template library for codesmith

Http://cstemplates.sourceforge.net/

Download the latest. nettiers template library installation file from the address above.

The last step is to ensure that you have installed the SQL Server database and are ready to connect to the database.

Step 2-codesmith

Open codesmith. Let's get familiar with its usage quickly.

As mentioned above, codesmith is a template-driven tool. There is a template browser window on the right side of codesmith, which allows you to quickly use the template you have installed. See.

Let's take a look at the template and double-click the hashtable. CST template.

Hashtable. CST is a hashtable template used to generate a strongly typed set. Let's take a quick look at another codesmith window, the attribute window.

The Properties window allows you to set the attributes of the template. If you use the hashtable. CST template, we need to set the attributes of classname, itemtype, and keytype. For example, we want to create a set of person objects and use the integer key to access the personcollection set. You need to set classname to personcollection In the attribute window, itemtype to person, and keytype to int. Click the run button in the toolbar to generate the source code of the strongly typed set you need.

Take a look at hashtable. CST, you will find it a bit like ASP. net syntax, in fact, codesmith template is similar to ASP. net form to generate the code you want, just ASP. net is used to generate HTML code, while codesmith is used to generate what you needSource code.

Remember that codesmith does not automatically generate the code you need, but you can define a template to generate all the code you want.

Step 3: generate a data access layer

We have basically understood codesmith's usage. Now we will demonstrate how to use the. nettiers template.

Remember, codesmith allows you to quickly generate code and rarely produce errors caused by handwritten code .. The code generated by the nettiers template is also the best practice for data access recommended by Microsoft.

First we need to add the. nettiers templates to the codesmith template Explorer:

First, add the. nettiers template to the template browser window.

1. In the template browser, click the Open Directory icon and browse the directory containing the template.
2. Select the. nettiers installation directory. The installation directory is c: \ Program Files \ serialcoder \ nettiers. 0.9.2 -Caribert \ Templates \
3. After selection, a folder will be added to the template browser window, expand it, and select the nettiers. CST Template
4. Now let's configure the data connection string, open the nettiers. CST template, and click the dialog box after the sourcedatabase attribute in the Properties window.
   
5. In this window, you can create a new data source, such.
   
6. Test whether the connection is successful. Click OK to close the window and select the name of the data source you just created.
7. Finally, the entiredatabase attribute is true, the namespace attribute is demo, and the outputdirectory attribute is the directory you want to output. Of course, you must create a directory to generate code in advance.

So far, you have completed all the attribute settings of the nettiers. CST template, which should look like this.

Of course we didn't tell you all about it. nettiers attribute settings, but it is enough for the Dal-Layer Code to generate the flat. Click the run button (Small arrow) in the toolbar to generate the code.

Note: You must set the primary key for the data table you created. If no primary key is created, no response code will be generated for this table. After you click the run button, a report will pop up, you can see the table code generated.

Step 4-view the generated code

Use Visual Studio. NET to open the demo. sln solution in the code output directory you just set (I set C: \ nettiers \ northwind_demo \ directory.

Let's take a quick look at the projects and files included in the solution.

You shoshould find 3 projects within the solution:

The solution contains three projects.

• Demo-This project contains the main classes you want to interact with the database.
• Demo. dataaccesslayer-these libraries contain actual database operation Execution Code
• Demo. dataaccesslayer. sqlclient-this type of library contains the code for Microsoft recommended database access design patterns and best practices (Microsoft patterns & Practices classes.

Step 5-compile and complete

Next, we use Visual Studio to compile the project.

Congratulations, you now have a database access layer that uses the best practices for accessing databases recommended by Microsoft.

Now you can use your dal layer in other projects. For example, you need to obtain, update, and delete an employee's record.

 
Using demo. dataaccesslayer. sqlclient;
 

 
// Select by primary key
 

 
Employee Employee = sqldatarepository. employeeprovider. getbyemployeeid (13 );
 

 
Employee. firstname = "John ";
 

 
Employee. lastname = "doe ";
 

 
// Save changes
 

 
Sqldatarepository. employeeprovider. Save (employee );
 

 
// Delete record
 

 
Sqldatarepository. employeeprovider. Delete (employee );
 

For more information about generating the Dal layer, refer to the. nettiers link below.

Http://cstemplates.sourceforge.net/nettiers-manual-v1.html

Remember, your data access layer code is generated through templates. If you modify the database structure, you just need to generate the Dal Layer Code through the template. (Of course, If you modify the generated code before re-generating the code and re-generate the new achievements to overwrite it for you, what should you do, you can write a subclass that inherits the automatically generated class, where you can override the related method, or in.. NET 2.0 uses multiple file classes.) In addition, you can expand your template based on your ideas, including annotations, naming conventions, and patterns.

Section

 

Codesmith can generate any code you want based on your template. This article shows you how to use it in just a few minutes. the nettiers template is used to create a database access layer that uses the best practices for accessing databases in your application.

You can view the video demonstration here:

Http://community.codesmithtools.com/r.ashx? Id = 1

A new feature of codesmith 3.1 is that code lines can be computed. It can calculate the code generated by codesmith conservatively.

 

Here we can see that. nettiers generates 91,559 lines of code. Suppose a very powerful programmer can write 60 lines of code in an hour, and it takes 1,800 hours to write the code. To join a powerful programmer, you have to pay it nearly $60 an hour. codesmith and. nettiers help your company save 1,800 hours and $109,000. (It seems a little exaggerated .)

Get a codesmith and exert its power.

Codesmith and. nettiers related resources

If you have some problems with codesmith, you can access itsCommunityTo get the solution: http://community.codesmithtools.com/. codesmith community has a lot of people writing templates and share them with everyone to download, you can go and see if there is anything you need.

If you think about buying codesmith, you can access its website http://www.codesmithtools.com/. You can get an authorization to exempt from 30-day trial restrictions. If you still have questions, you can send e-mail sales@codesmithtools.com to their after-sales support mailbox.

If you have any questions about the. nettiers template, visit the following Forum:

Http://community.codesmithtools.com/forums/16/ShowForum.aspx

English link:

Build a data access layer in less than 15 minutes

Http://community.codesmithtools.com/blogs/tutorials/archive/ 2006/02/13 /Nettiers. aspx

In addition, I would like to take this post to investigate what your persistent layer uses, including nhib.pdf, ibatis.net, daab, SPL, sqlhelper, and CMP, I think daab in the Enterprise Library will be used in the project in the future. I don't know if it is suitable for enterprise development. What are the disadvantages of daab.

Nettier template usage

1.1. Overview:
The. NET code generated using the nettier template, including the complete data layer, uses Microsoft's technology
Enterpriselibrary1.1, which generates corresponding query functions and stored procedures for each table.
Supports multi-field query and paging. The data layer is a factory model. You only need to call the datarepository class to obtain the corresponding table connection.
Port instance, and then you can modify the table. For multi-table join operations, you can obtain query records of subsets in the parent table.
Directory, which is saved in the ilist container. For sub-tables, an object instance of the parent table is provided. Provides transaction functions for database operations
Yes. For more complex operations, you can directly provide the database operation instance of enterpriselibrary for direct operations.
Features:
1. Generate the vs.net project and solution;
2. Integrated with enterpriselibrary1.1, you can directly use the enterpriselibrary configuration file for database links.
Parts;
3. The ing between data tables and entities is. Each table has an entity generated, which corresponds to each
Fields. The object class is serialized and has a trigger event. Fields of Enumeration type are supported;
4. operations on the entities generated by tables and views include: basic CRUD operations: Update, delete, insert,
Select All, paged select, find; supports primary key, foreign key, multi-Table Association, sorting, paging, SQL
Statement and View query;
5. Obtain strong datasets stored in ilist or vlist and can be bound to DataGrid, gridview, or
In other page controls, table sorting is supported;
6. Generate the WebService Service for data distribution;
7. Create a stored procedure script and automatically install it on the database server;
8. Generate a complete Nant build. xml file, automatically compile and test the code, and generate APIs in chm format.
Document;
9. Generate complete data verification rules based on the database, including a framework for managing rules;
10. Each table has an entity class. You can inherit from your customer code and add your own handler.
Method;
11. Generating a data source eliminates the need to process the data source;
12. Create a full set of web admin user controls. You can perform Web management on databases;
13. Complete nunit testing;
14. Complete annotations, which comply with Microsoft naming rules;
15. The nettiers template is free and open-source.
1.2. Framework Structure:
The data logic includes the customer business logic components and data interface logic components (persistence layer logic ).
Above.
The following uses the single-Table employee of the northwind database as an example.
Structure of the employee data table:
Generated oo structure:
The interface calls datarepository to obtain the provider of each function block, and then adds, deletes, modifies, and queries a single table.
If you need more complex business logic, such as using transactions to process several tables at the same time, you need to write additional business
Logic code. The logic layer code can be placed in the BL layer. It is worth mentioning that the generation generated in the BL Layer
Code, only the most basic and general business logic, only the entity class VO of single table data is generated, and the processing of each field of single table data is
And sorting and retrieval of data sets.
Employeescollection
Employeescollectionbase
Employees
Employeesbase employeesvo
In datarepository, the method used to obtain the provider of each function module is static.
Sqlprovider instances, which are provided to the outside world for their corresponding abstract class definition methods.
The instance call sequence is:
:
Datarepository
:
Sqldataprovider
:
Sqlemployeesproviderbase
1: employeesprovider
2: getall
3: getbyemployeeid
Analysis of datarepository. employeesprovider. getall () Operations: the order of Instantiation is
The datarespository class maintains a global variable current, which is actually
Sqldataprovider instance, while current is of the dataproviderbase type, the relationship is:
Dataproviderbase
Sqldataprovider
(From sqlclient)
When obtaining the employeesprovider in datarepository
The employeesprovider () method in sqldataprovider, and a new sqlemployeesprovider
For example, the implementation of all methods in this instance is implemented in the sqlemployeesproviderbase class, because
Sqlemployeesprovider inherits the sqlemployeesproviderbase class, which is equivalent to the DaO layer.
Database Operations.
Sqlemployeesproviderbase
Employeesproviderbase
(From Base)
Sqlemployeesprovider
When generating each sqlprovider instance, you must first instantiate sqldataprovider.
The activator class is generated based on the name of the class provided by the configuration file. To support different databases, nettier
Yes. A configuration file nettiersconfigdata. config and the DLL used to read the configuration file are provided:
Nettiers. configuration. dll.
The activator class can dynamically construct objects, a bit like
Class. forname (classname). newinstance () statement. Because the activator class is used to construct an object, its class name is
It can be uncertain during compilation, so it can be used as the plug-in interface to make a program that can be added with the DLL plug-in Winamp.
.
1.3. Method of calling the interface:
1.3.1. Get all data
Obtain all the employee data, sorted by the lastname field:
Using northwind. dataaccesslayer;
// Get all the employee, sort it on the lastname and print them out
Tlist <employees> employees = datarepository. employeeprovider. getall ();
Employees. Sort (employeecolumns. lastname, listsortdirection. ascending );
Foreach (employee in employees)
{
Console. writeline ("{1} {0}", employee. firstname, employee. lastname );
}
1.3.2. add data
Create a new employee and save it.
Using northwind. dataaccesslayer;
// Create a new record and save
Employee Employee = new employee ();
Employee. firstname = "John"; employee. lastname = "doe ";
Employee. Birthdate = datetime. Now; employee. Address = "10, Fake Street ";
Employee. City = "metroplolis"; employee. Country = "USA ";
Employee. hiredate = datetime. now;
Employee. homephone = "0123456789 ";
Employee. Notes = "this is a fake employee ";
Employee. Title = "M ";
Employee. titleofcourtesy = "dear ";
Employee. postalcode = "5556 ";
Employee. region = "New-York ";
Datarepository. employeeprovider. insert (employee );
// Look, new ID already populated
Console. writeline ("new employee ID" + employee. employee ID );
1.3.3. modify data
Obtain and modify data according to the subscript;
Using northwind. dataaccesslayer;
// Select by index and update
Tlist <employees> employees =
Datarepository. employeeprovider. getbylastname ("doe ");
If (employees. Count = 1)
{
Employees [0]. Notes = "this is a modified fake employee ";
Datarepository. employeeprovider. Save (employees [0]);
Console. Write (employees [0]);
}
1.3.4. delete data
Query and delete data using the primary key
Using northwind. dataaccesslayer;
// Select by primary key and delete
// (Demonstrate that insert, update, delete methods can also take collection
As parameter)
Employee Employee = sqldatarepository. employeeprovider. getbyemployeeid (13 );
Datarepository. employeeprovider. Delete (employees );
1.3.5. Transaction Control
Transaction Control instances can use transactions to control insert, modify, and delete operations to ensure that all operations are successful or fail to be rolled back;
Using northwind. dataaccesslayer;
// The sqlclient can work with transactions.
// Also show the Save method, wich encapsulate the use of insert, update and
Delete methods.
Transactionmanager transaction = datarepository. createtransaction ();
Transaction. begintransaction (/* isolationlevel. readuncommited */);
Try
{
// Insert
Employee Employee = new employee ();
Employee. firstname = "John ";
Employee. lastname = "doe ";
Employee. Birthdate = datetime. now;
Employee. Address = "10, Fake Street ";
Employee. City = "metroplolis ";
Employee. Country = "USA ";
Employee. hiredate = datetime. now;
Employee. homephone = "0123456789 ";
Mployee. Notes = "this is a fake employee ";
Employee. Title = "M ";
Employee. titleofcourtesy = "doctor ";
Employee. postalcode = "5556 ";
Employee. region = "New-York ";
Datarepository. employeeprovider. Save (transaction, employee );
// Modify the employee instance
Employee. Notes = "this is a modified fake employee ";
// Update
Datarepository. employeeprovider. Save (transaction, employee );
Transaction. Commit ();
Console. writeline ("OK ");
}
Catch (exception ex)
{
Try {transaction. rollback ();} catch (){}
Console. writeline ("NOK: {0}", ex );
}
1.3.6. Save Association
Deep storage: stores parent objects and subsets at the same time.
/*
Deepsave helper method can help you to save an object and its children in
One call.
*/
Using northwind. dataaccesslayer;
Order order = order. createorder ("alfki", 1, datetime. Now, datetime. Now,
Datetime. Now, 1, 0.1 m, "ship name", "ship address", "Paris", "IDF", "75000 ",
"France ");
Order. orderdetailcollection. Add (order. orderid, 1, 15.6 M, 10, 0.02f );
Order. orderdetailcollection. Add (order. orderid, 2,122.6 M, 43, 0.03f );
Datarepository. orderprovider. deepsave (order );
Console. writeline ("New Order saved: orderid is:" + order. orderid. tostring ());
1.3.7. Multi-Database Support
Supports operations on multiple different databases at the same time. Configure different database connections in the El configuration file or
Dynamically generate different database connections in the code to use different types of databases at the same time.
/*
You can configure multiple data provider in the configuration console, and
Write code to acces a specific one, instead of the default.
*/
Using northwind. dataaccesslayer;
Sqldataprovider myrepository = datarepository. Providers ["my second data
Provider "] As northwind. dataaccesslayer. sqlclient. sqldataprovider;
This. listbox1.datasource = myrepository. productprovider. getall ();
This. listbox1.displaymember = "productname ";
This. listbox1.valuemember = "productid ";
// Or if you can't have it pre-configured, you can change the connection
String at runtime.
Using northwind. dataaccesslayer;
// New syntax using a declared connection string:
Tlist <Products> List =
Datarepository. Connections ["northwindconnectionstring2"]. provider. customersprovide
R. getall ();
// New syntax using a dynamic connection string:
Datarepository. addconnection ("dynamicconnectionstring", "Data
Source = (local); initial catalog = northwind; Integrated Security = true ;");
Tlist <products <list =
Datarepository. Connections ["dynamicconnectionstring"]. provider. productsprovider. Ge
Tall ();
This. listbox1.datasource = List;
This. listbox1.displaymember = "productname ";
This. listbox1.valuemember = "productid ";
1.4. Configure the nettiers Template
1.4.1. datasource directory:
The Database Name and connection parameters of sourcedatabase are displayed on the configuration page in codesmith connector E.
The tables whose sourcetables need to be materialized in the data table
Entiredatabase specifies the tables to be materialized in the database. This setting replaces sourcetables settings.
1.4.2. General Directory
Ouputdirectory output directory. The configuration page is displayed in codesmith certificate e.
Businesslogiclayerfoldername: logical layer sub-directory name. It is recommended to leave it empty.
Dataaccesslayerfoldername: Sub-directory name of the data layer. Recommended Value: dataaccesslayer
Sqlfoldername: Directory Name of the stored procedure script. Recommended: SQL
Namespace project namespace, with the same name and directory.
Generateunittest declare to generate nunit test project name
Vsnetintegration declares whether to generate an entire project or generate a project separately by layer.
Vsnetversion vs.net version
1.4.3. WebService Parameters
Generatewebservice declares whether to generate WebService
Webserviceoutputpath: Path of the file generated by WebService
Webserviceurl data layer subdirectory name; Recommended Value: dataaccesslayer
Sqlfoldername indicates the URL of webserviceoutputpath
After the configuration is complete, you can save it as a configuration file. You only need to load it next time.
Added a web layer template called weblayer to add, delete, modify, and query a single table;
For an example of configuring the SQL Server database northwind, see the netier directory.
The property. xml file can be directly loaded and then the corresponding code is generated. The complete project is generated and can be compiled directly.
Run.