Experiment three: Separating the read data function from the repository

first understand the following paragraph and then start experimenting.

After the transformation of experiment two, our code has a little structure concept:

Listing.aspx: To the repository to products, and then the resulting products are displayed in the page.

Repository: Responsible for reading data from the database and converting the data into a collection of objects.

The path to the transformation of the code structure is not over yet, see below.

If there is one more table in the Database: Order form (orders). All orders in the order form, of course, also need to be read out on the page. Referring to the practice of reading products, we need to add a property similar to the product in repository: Orders. The code for the Repository class is as follows:

1 usingSystem.Collections.Generic;2 usingSystem.Data.SqlClient;3 4 namespaceSportstoreex5 {6      Public classRepository7     {8          PublicIenumerable<product> Products9         {Ten             Get One             { Ailist<product> products =NewList<product>(); -  -                 varsql ="Select Productid,name,description,category,price from Products"; the                 varCon =NewSqlConnection ("Data Source=.;i Nitial catalog=sportsstore;integrated security=true"); -                 varcmd =NewSqlCommand (sql, con); - SqlDataReader Dr; -  + con. Open (); -Dr =cmd. ExecuteReader (); +                  while(Dr. Read ()) A                 { at                     varProd =NewProduct -                     { -ProductID = Dr. GetInt32 (0), -Name = Dr. GetString (1), -Description = Dr. GetString (2), -Category = Dr. GetString (3), inPrice = Dr. Getdecimal (4) -                     }; to Products . ADD (prod); +                 } -                 returnProducts ; the             } *         } $ Panax Notoginseng          PublicIenumerable<order>Orders -         { the             Get +             { AIlist<order> Orders =NewList<order>(); the  +                 varsql ="Select Orderid,productid,count from Orders"; -                 varCon =NewSqlConnection ("Data Source=.;i Nitial catalog=sportsstore;integrated security=true"); $                 varcmd =NewSqlCommand (sql, con); $ SqlDataReader Dr; -  - con. Open (); theDr =cmd. ExecuteReader (); -                  while(Dr. Read ())Wuyi                 { the                     varOrd =NewOrder -                     { Wu                         //OrderID = Dr. GetInt32 (0), -                         //ProductID = Dr. GetInt32 (1), About                         //Count = Dr. GetInt32 (2) $                     }; - Orders.add (ORD); -                 } -                 returnOrders; A             } +         } the     } -}

Compare the 第8-35 line and 第37-62 line code, in addition to the 第23-31 row and the 第52-57 line is not the same, the other parts are not all the same, are connected to the database, execute queries and other statements? Whenever we read a data table, we need to repeat the code, and from that point of view, our code has to be further modified. (There are a lot of other more complicated reasons, there is no way to show this one by one, for beginners, as long as the current program structure only to do some small, not complex applications, enterprise-class applications can not use this structure)

Well, explain the reasons for the further transformation, below to see how the transformation.

The code of the get accessor of the products property of the repository class is analyzed, and logically, it consists of two parts, one part is responsible for accessing the database, and the other is responsible for converting the records read from the database into the Product object, that is, completing the "record- > object "Conversion.

For each data table, the process of reading is the same (both establishing a connection SqlConnection object, establishing an instruction operation SqlCommand object, opening the database, starting to read the data), but converting it to an entity object is different (because the properties of each entity object are different).

So, the goal of our transformation is to extract the process of reading the database and make a common function, leaving the "record-and-object" conversion process in Repository.

Specific steps

1. Add a new class to the Sportstoreex project: SqlHelper,

The SqlHelper class code is as follows:

usingSystem.Data;usingSystem.Data.SqlClient;namespacesportstoreex{ Public classSqlHelper { Public StaticSqlDataReader Executedatareader (stringConnectionString,stringSQL) {SqlConnection con=NewSqlConnection (connectionString); SqlCommand Command=NewSqlCommand (sql, con); Con.            Open (); //when you call the ExecuteReader () method, if you pass a commandbehavior.closeconnection parameter, the system automatically shuts down the connection when the user closes reader.             returncommand.                    ExecuteReader (commandbehavior.closeconnection); }    }}

2. Modify the Repository Class code

The Repository.cs file code is as follows:

1 usingSystem.Collections.Generic;2 usingSystem.Data.SqlClient;3 4 namespaceSportstoreex5 {6      Public classRepository7     {8         stringConstring ="Data Source=.;i Nitial catalog=sportsstore;integrated security=true";9          PublicIenumerable<product> ProductsTen         { One             Get A             { -ilist<product> products =NewList<product>(); -  the                 varsql ="Select Productid,name,description,category,price from Products";  -  -                 using(SqlDataReader dr =sqlhelper.executedatareader (constring, SQL)) -                 { +                      while(Dr. Read ()) -                     { +                         varProd =NewProduct A                         { atProductID = (int) dr["ProductID"], -Name = (string) dr["Name"], -Description = (string) dr["Description"], -Category = (string) dr["Category"], -Price = (decimal) dr[" Price"] -                         }; in Products . ADD (prod); -                     } to                 } +                 returnProducts ; -             } the         } *     } $}

Explain:

(1) Line 8th: Because the Repository class, whether the products property or the Orders property to be added, they use the same database connection word, so the connection word contxt from the products property inside Out, In the Repository class, as a member of Class one, so that the common contxt can be used in all properties or methods in the Repository class, and there is no need to redefine the connection word within each property.

(2) Line 17th: This calls the Executedatareader () method of the SqlHelper class created in the 1th step, let it read the database, the Executedatareader () method returns the database result, and gives it to the DR variable. See, the process of reading the database is not easy for repository, only need to call a method of a class has the data has the wood?

Our code uses a using statement, the syntax of this statement is not the focus of the content of this article, in order to make this focus, the content is compact, here does not do a detailed explanation, please refer to: C # using the role

(3) 21-28th: Responsible for converting a row of records in Dr into a product class object.

See, through the above 2-step transformation, we have the following benefits:

(1) The task of reading data from a database is the responsibility of the Executedatareader method of the SqlHelper class, whether it is to read which database, which data table, you can let it dry, just tell him the database connection Word and SQL statement on the line.

(2) The products that have been obtained from the products data sheet into the Product object are responsible for the products property of repository.

The program structure is clearer, is there wood? Each class has its own wood. Think about the fact that we live in the same way? Xi is responsible for the national general policy, strong brother responsible for economic development, because they each boss has responsibility for their own things, so the state machine can be normal operation. Unlike a husband and wife shop, what is the husband and wife two dry, dry exhausted also can not store dry big, finally really too tired had to close closed.

Note: The above code is just to show beginners how to module programming, designed to help beginners understand the "proficient in asp.net4.5 Programming" book Sportstore site code, the code demonstrated in this experiment is not used as a practical development method. The actual development of ADO is much more complex than demonstrated here.

Experiment three: Separating the read data function from the repository