Shulou(Shulou.com)06/02 Report--

What is the implementation process of the data access layer DAL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

For simplicity in the demonstration, suppose: backend database (only user table User and department table Department are available for SqlServer), and each table field is correspondingly simplified:

User (user table) Id primary key Name name DeptId department number remaining fields omitted. Department (department table) Id primary key Name name Desc department description remaining fields omitted.

Background database: the situation of testdb

Establish the relevant stored procedures:

Generally speaking, I also like to convert ORM into entity objects (see screenshot)

(note: the DeptTitle attribute has been added here)

Now it's time to access the database SqlServer type, which is encapsulated in SqlserverProvider. If you access the Access database in the future, the corresponding access is encapsulated in AccessProvider.

(Provider stands for data access provider)

SqlUserProvier specially implements the operation of SqlServer table user, and AccessUserProvider specifically implements the operation of Access table user. Obviously, the operation function is the same (addition, deletion, modification and query), so the same part of different subclasses is abstracted to form a parent class (UserProvider).

Start with the concrete subclass implementation: SqlUserProvider:UserProvider

(the error in the database connection string above: the word integrated is correct. Correct the error after debugging.)

We found overloaded GetUsers methods, a lot of code repetition, method refactoring (repeated code refactoring to method GetUsersFromReader)!

Continue to specifically implement the abstract method of the parent class: GetUserById, and find that part of the code of this method duplicates with that of the previous GetUsersFromReader method!

It is found that the red part of the image above is duplicated (the GetUserById method forgot to pass the parameters required by the stored procedure), and then the method is refactored to refine the repeated code to avoid multiple changes in the future.

Then write the subsequent methods of this class (add / delete / modify): (you can open the VS development environment, connect to the corresponding database, and look at the parameters of the stored procedure, so as to avoid coding forgetting and passing parameters.)

Next, let's take a look at the class: AccessUserProvider, see the following figure

The query statement in the GetUsers method above does not have a federated query and will be changed later. (here is just a demonstration, which is similar to Access and can be similar to creating a query table.)

We found that the methods GetUserFromReader and GetUsersFromReader of the two subclasses of UserProvider have duplicate code (only the parameters of the methods are different) [find a way to abstract it out and put it in the parent class]

The parameters of the method are SqlDataReader and OleDbDataReader, but look at the definition and see that they have their own parent class: DbDataReader.

Public class SqlDataReader: DbDataReader, IDataReader, IDisposable, IDataRecord

Public sealed class OleDbDataReader: DbDataReader

Overwrite parent class: UserProvider

The method of the parent class is appended with the modifier protected to ensure that only subclasses can access it.

The subclass can directly call the methods of the parent class (GetUserFromReader and GetUsersFromReader methods), as shown in the screenshot:

Similar code for perfecting the SqlDepartmentProvider class and the AccessDepartmentProvider class

(parent class: DepartmentProvider provides protection methods GetDeparmentFromReader and GetDepartmentsFromReader)

Each specific subclass Provider repeats the attribute: ConnString, so it is decided to create a parent class: DataAccess to hold this attribute (both UserProvider and DepartProvider inherit from it). In fact, DataAccess can also contain other properties and common methods.

Namespace abstract factory pattern .Dal {publicabstractclassDataAccess {privatestring_connString = ""; publicstringConnString {get {return_connString;}

Public abstract class UserProvider:DataAccess

Public abstract class DepartmentProvider:DataAccess

Usually: the contents of the database connection string are stored in the corresponding configuration file, not hard-coded.

Desktop applications-[app.config], web applications-- [web.config], here is an example of app.config, where the database connection string is first accessed according to the SqlServer database.

Be sure to manually reference: System.configuration, and then access the connection string through the ConfigurationManager class.

As you can imagine, depending on the type of database, the actual underlying data provider is Sql__Provider or Access__Provider.

But for the user caller (business logic layer), you only need to manipulate the Provider.

Suppose my city has two administrative districts (East District 1 and West District 2), and there is a "really nice" main store [fast food series] with chain stores in both districts, external unified telephone number: 1111777.

It is of course convenient to set up an switchboard number. It will not be possible to open 10 branches and announce 10 phone numbers in the future. Who can remember them?

For example: I am hungry now, want to eat this "economy fast food" (one vegetarian one soup), I just need to call 111177, there just need to know my address. (you can imagine: knowing my address), for customers, I don't care which branch is assigned to serve, and how economical fast food is made. All I care about is: be delicious, and then hurry up (after all, you can't stand being hungry for too long.)

Back to our program:

UserProvider is like an item vegetable, DepartmentProvider is like soup vegetable. Access folder [economy], SqlServer folder [business] (you will ask an off-topic question: is there any meat food? My answer is: try not to eat, now it's all hormone feeding. If you eat too much, you are easy to get sick.

There is only one question: since BLL (the customer caller as opposed to DAL) only recognizes (UserProvider/DepartmentProvider), how do you call a subclass that actually works?

This needs to be used in the design pattern (specifically choose which subclass is actually done with the parent class)

Of course, the configuration file here: the database connection string and providerType need to match.

Parent class: UserProvider We provide a static Instance to determine the actual subclass (SqlUserProvider or AccessUserProvider, depending on the value of the ProviderType of the configuration file)

If OracleUserProvider/DB2UserProvider/MySqlUserProvider/XmlUserProvider appears in the future, this blue box still needs to add case branches. This is not good. It needs to be re-coded (modified). A good design should be open to extension and closed to modification. And all the specific subclasses Provider are listed here, in fact, only one subclass Provider is needed, but other subclasses Provider are also forced to appear together (there is a coupling between the real subclasses), so this approach is not desirable and needs to be solved.

This problem is solved by reflection here.

First of all, the constraint: the assignment of ProviderType needs a specification, so you can only select one from (Sql/Access/DB2/MySql/Xml). You can find that the actual subclass name: the value of ProviderType + "UserProvider".

StaticpublicUserProvider Instance {get {if (_ instance = = null) {stringproviderTypeName=ConfigurationManager.AppSettings ["ProviderType"] + "UserProvider"; _ instance = Activator.CreateInstance (Type.GetType (providerTypeName)) asUserProvider;} return_instance;}}

If your DAL is a project (class library) built separately by assembly, please use Assembly.Load and other methods, because it is organized as a folder (DAL folder)

In the future, when the client (BLL) calls, such as deleting the record of the user table, you can call as follows:

UserProvider.Instance.DeleteUser (id). / / BLL doesn't know which subclass (such as SqlUserProvider) actually works here.

Run a test to see if it works properly!

Error found: 1: the database connection string needs to be modified:

Second: change the folder SqlServer to Sql. Under the corresponding changes to the previous namespace:

Namespace Abstract Factory pattern .DAL.Provider.SQL

{

PublicclassSqlDepartmentProvider:DepartmentProvider

... .

Namespace Abstract Factory pattern .DAL.Provider.SQL

{

PublicclassSqlUserProvider:UserProvider

. .

Three: Type.GetType (fully qualified name required)

Test passed: but found no DeptTitle data, look for errors found

PublicUser (intid, stringname, intdeptId, stringdeptTitle) {this.Id = id; this.Name = name; this.DeptId = deptId; this.DeptTitle = deptTitle; / / DeptTitle;}

Attached: the code of AccessUserProvider is as follows:

AccessUserProvider code

UsingSystem; usingSystem.Collections.Generic; usingSystem.Text; usingSystem.Data; usingSystem.Data.OleDb; using Abstract Factory pattern .Dal; using Abstract Factory pattern .DAL.Entity; namespace Abstract Factory pattern .DAL.Provider.access {publicclassAccessUserProvider:UserProvider {publicoverrideList GetUsers () {using (OleDbConnection conn = newOleDbConnection (ConnString)) {vardbcmd = conn.CreateCommand (); dbcmd.CommandText = "GetUsers"; dbcmd.CommandType = CommandType.StoredProcedure; conn.Open () ReturnGetUsersFromReader (dbcmd.ExecuteReader ());}} publicoverrideList GetUsers (intdeptId) {using (OleDbConnection conn = newOleDbConnection (ConnString)) {vardbcmd = conn.CreateCommand (); dbcmd.CommandText = "GetUsersByDepartmentId"; dbcmd.CommandType = CommandType.StoredProcedure; dbcmd.Parameters.Add ("@ DeptId", OleDbType.Integer). Value = deptId; conn.Open (); returnGetUsersFromReader (dbcmd.ExecuteReader ()) } publicoverrideUser GetUserById (intid) {using (OleDbConnection conn = newOleDbConnection (ConnString)) {vardbcmd = conn.CreateCommand (); dbcmd.CommandText = "GetUserById"; dbcmd.CommandType = CommandType.StoredProcedure; dbcmd.Parameters.Add ("@ Id", OleDbType.Integer). Value = id; conn.Open (); returnGetUserFromReader (dbcmd.ExecuteReader ()) }} publicoverrideboolDeleteUser (intid) {using (OleDbConnection conn = newOleDbConnection (ConnString)) {OleDbCommand cmd = newOleDbCommand ("delete from [user] where Id=" + id, conn); conn.Open (); returncmd.ExecuteNonQuery () = = 1;}} publicoverrideintInsertUser (User user) {using (OleDbConnection conn = newOleDbConnection (ConnString)) {OleDbCommand cmd = newOleDbCommand (@ "insert into [user] (name,deptId) values (@ id,@name); 68select max (id) from [user] as newid", conn) Cmd.Parameters.Add ("@ id", OleDbType.Integer). Value = user.Id; cmd.Parameters.Add ("@ name", OleDbType.VarChar). Value = user.Name; conn.Open (); return (int) cmd.ExecuteScalar ();} publicoverrideboolUpdateUser (User user) {using (OleDbConnection conn = newOleDbConnection (ConnString)) {OleDbCommand cmd = newOleDbCommand ("update [user] set name=@name,deptId=@deptid where Id=@id", conn) Cmd.Parameters.Add ("@ name", OleDbType.Integer). Value = user.Name; cmd.Parameters.Add ("@ deptid", OleDbType.Integer). Value = user.DeptId; cmd.Parameters.Add ("@ id", OleDbType.Integer). Value = user.Id; conn.Open (); returncmd.ExecuteNonQuery () = = 1;}

Screenshot of testdb.mdb in the background:

The answer to the question about the implementation process of the data access layer DAL is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.