Ramble on. Net PetShop and Duwamish ADO. NET Database programming

ado| Programming | data | database Overview

Ado. NET provides us with a strong database development capabilities, its built-in multiple objects for our database programming provides a different choice. But when we are allowed to choose flexibly, many beginners are puzzled, should I use DataReader or should I use DataAdapter? I just want to read a small amount of data, do I have to fill the entire dataset? Why can't DataReader provide a way to update data like the recordset? What is the benefit of the dataset?

In this article, I'll do some simple analysis and comparison of the. NET PetShop database programming pattern and the Duwamish Database programming pattern. If you have the above questions, I believe that after reading this article, you can develop a database programming model that is best suited to your application, depending on your specific needs.

　　 . Net PetShop and Duwamish Brief introduction

I believe you must have heard of the famous "Pet Shop war", yes, one of the main characters of this article is the winner. Net PetShop, Microsoft boasts a 27 times-fold-speed and 1/4-point code that is far ahead of the Petstore pet store based on Java EE. Although Sun has complained about this, accusing the "war" of water, but anyway,. NET PetShop is definitely a classic. NET instance tutorial, at least provides us with a "shortcut =" To catch up with Java EE:), its download address is: http:// Www.gotdotnet.com/team/compare

. Net PetShop Pet Online Store Home

Duwamish is an online bookstore with a simple exterior and an extremely complex interior. NET application example, as a Microsoft Official sample, it also provides C # and vb.net two languages, and also attached a large number of detailed Chinese information, if printed out, is really home travel, sleep into the toilet necessary things. What the? You've never heard of it? Oh, if you installed visual Studio. NET, it is lying quietly on your hard drive, but has not been installed, you can find and install it in your vs.net's enterprise Samples directory, for example: C:\Program Files\Microsoft Visual Studio . NET\Enterprise Samples\Duwamish 7.0 CS.

Duwamish Online Electronic Bookstore Home

　　Structure Brief

All two stores employ an n-tier application structure (there is no doubt that the N-tier architecture should definitely be developed for you. NET application, even if you just want to do a web counter, the difference is that PetShop uses the most common three-tier application structure, namely, the presentation layer, the middle tier and the data layer. Duwamish, however, uses a four-tier application structure, which is separated by different items, namely the presentation layer, the business Facade layer, the business rule layer and the data layer. As for the pros and cons of these two structures, and why they are so layered, we do not discuss them in detail, because this is not the focus of this article. Our main analysis is the pattern of their database programming.

Duwamish Data Access Analysis

First of all, let's look at the Duwamish Bookstore, which uses the data storage pattern of the DataAdapter and DataSet, which, in contrast, is a subclass extension of the dataset as a data carrier, that is, a custom dataset for layer data transmission, The following is an example of a custom dataset:

public class Bookdata:dataset
{
Public BookData ()
{
//
Create the tables in the dataset
//
Builddatatables ();
}
private void Builddatatables ()
{
//
Create The Books table
//
DataTable table = new DataTable (books_table);
datacolumncollection columns = table. Columns;

Columns. Add (Pkid_field, typeof (System.Int32));
Columns. Add (Type_id_field, typeof (System.Int32));
Columns. Add (Publisher_id_field, typeof (System.Int32));
Columns. Add (Publication_year_field, typeof (System.Int16));
Columns. Add (Isbn_field, typeof (System.String));
Columns. Add (Image_file_spec_field, typeof (System.String));
Columns. Add (Title_field, typeof (System.String));
Columns. Add (Description_field, typeof (System.String));
Columns. Add (Unit_price_field, typeof (System.Decimal));
Columns. Add (Unit_cost_field, typeof (System.Decimal));
Columns. Add (Item_type_field, typeof (System.String));
Columns. Add (Publisher_name_field, typeof (System.String));

This. Tables.add (table);
}
.........
}

We can see that it has a builddatatables method, and it's called in the constructor, so that the custom books table is bundled with the dataset to save the column Mapping in the future, which is a good idea, why didn't I think of it? ：）

Solved the data structure, and then look at the code implementation of the layer, in Duwamish, the data layer has 5 classes, respectively, books,categories,customers and orders, each class is responsible for the data access. The following is a sample code for one of the classes:

Private SqlDataAdapter Dscommand;
Public bookdata Getbookbyid (int bookid)
{
     return fillbookdata ("Getbookbyid", "@ BookID ", bookid.tostring ());
}
Private BookData fillbookdata (string commandtext, String paramname, String paramvalue)
{
  & nbsp if (Dscommand = null)
    {
        throw new System.objectdisposedexception (GetType (). FullName);
   }
    bookdata   data    = new BookData ();
    SqlCommand command = Dscommand.selectcommand;

Command.commandtext = CommandText;
Command.commandtype = CommandType.StoredProcedure; Use stored proc for perf
SqlParameter param = new SqlParameter (paramname, SqlDbType.NVarChar, 255);
Param. Value = paramvalue;
Command. Parameters.Add (param);

Dscommand.fill (data);
return data;
}

Here is the code for the data layer, and we can see here that Duwamish uses DataAdapter to populate the dataset with the data and then return it. I feel very strange is in the data access layer can actually see Getbookbyid such a specific data access method, although finally there is an abstract FillBookData method, but there are three layers above, ah, the bottom is to do this, the upper floors are doing? The answer is the data check, the upper level is basically doing some very strict data validation (of course, will also include some of the more complex transaction logic, but not many), the sample code is as follows:

Public CustomerData GetCustomerByEmail (string emailaddress, string password)
{
//
Check Preconditions
//
Applicationassert.checkcondition (EmailAddress!= String.Empty, "Email address is required",
Applicationassert.linenumber);
Applicationassert.checkcondition (password!= string.empty, "Password is required",
Applicationassert.linenumber);
//
Get the customer DataSet
//
CustomerData DataSet;
using (dataaccess.customers customersdataaccess = new Dataaccess.customers ())
{
DataSet = Customersdataaccess.loadcustomerbyemail (EmailAddress);
}
//
Verify the customer ' s password
//
DataRowCollection rows = dataset.tables[customerdata.customers_table]. Rows;

if (rows. Count = = 1) && Rows[0][customerdata.password_field]. Equals (password))
{
return dataSet;
}
Else
{
return null;
}
}

In this approach, the real data access is actually only

DataSet = Customersdataaccess.loadcustomerbyemail (EmailAddress);

So, it's a direct call to the data layer. Others are verifying legitimacy, and we can see how important it is to have the robustness of a real enterprise-level development to consider.

. NET PetShop Data access profiling

Ok,duwamish, let's look at the PetShop data access mechanism.

PetShop has only one project, the layered approach is to put the middle tier and the data layer into a CS file in the Components directory, where the data layer is a class named database, which encapsulates all the underlying operations of the databases. The following is a sample code snippet:

public void Runproc (String procname, out SqlDataReader dataReader) {
SqlCommand cmd = CreateCommand (procname, NULL);
DataReader = cmd. ExecuteReader (System.Data.CommandBehavior.CloseConnection);
}

We see a different way with Duwamish data access, it will be all the data access methods abstracted into a Runproc method, as for the return of data, hehe, it is a bit lazy, direct return to a DataReader to you, you read it yourself. Remember Duwamish use of the data transfer carrier is what? Yes, the dataset, which is populated by the data layer and returned to the middle tier. But here, the data layer and the transport layer of the transfer vector into the DataReader, in fact, it can not be called as a data carrier, because the data has not begun to read, here, the role of DataReader and pointers somewhat similar, perhaps we should call it "data reference":

And then looking down, how DataReader was "handled":

Public productresults[] GetList (string catid, int currentpage, int pageSize, ref int numresults)
{
numresults = 0;
int index=0;
SqlDataReader reader = getlist (CATID);
Productresults[] results = new Productresults[pagesize];

Now loop through the ' list and pull out items of the specified page
int start = (int) ((currentPage-1) * pageSize);
If (start <= 0) start = 1;

Skip
for (int i = 0; i < start-1; i++) {
if (reader. Read ()) numresults++;
}
if (Start > 1) reader. Read ();

Read the data we are interested in
while (reader. Read ()) {
if (Index < pageSize) {
Results[index] = new Productresults ();
Results[index].productid = reader. GetString (0);
Results[index].name = reader. GetString (1);
index++;
}
numresults++;
}

Reader. Close ();

If need to ReDim array
if (index = = pageSize)
return results;
else {
Not a full page, ReDim array
productresults[] results2 = new Productresults[index];
Array.copy (results, results2, index);
return results2;
}
}

Have you noticed CurrentPage and pagesize? In this case, the data paging, only to return to meet the minimum amount of data required, rather than many of us like lazy people, simply the entire DataTable a brain binding to the DataGrid, resulting in a large number of data redundancy.

Here, the data is actually read out and manually populated into a custom object array, so let's take a look at the definition of this array:

public class Productresults
{
private string M_productid;
private string M_name;

Product Props
public string ProductID {
get {return m_productid;}
set {M_productid = value;}
}

public string Name {
get {return m_name;}
set {m_name = value;}
}
}

It's very simple, but I'm kind of wondering why not use struct? Isn't it. NET the performance gap between struct and class is negligible?

Analysis and summary

By looking at the specific implementations of these two stores, we got two different data access patterns, Duwamish, which uses a dataset as the core, because the dataset provides a lot of relevant methods in this area, so the entire application of data transfer, the format definition, The data checksum is all around the dataset, and the whole framework definition is very clear and rigorous, but it seems a little large. PetShop in the entire program does not use a dataset, the program is very concise, brisk, but not Duwamish so strong robustness. The two programs are code written by different groups of Microsoft, so they have different styles. But they should all be able to represent. NET's standard mode. See here, you should have a more vivid understanding of the questions raised at the beginning of the article.

Again, note that PetShop does not immediately read data after opening the data connection, but instead passes the DataReader to another object to perform the data read operation before closing the connection. In this way, the time of the data connection is lengthened, and the database connection is a very valuable server resource, in contrast, the dawamish to fill the database immediately after the connection, and then quickly release the database connection is more conducive to a large number of users concurrent access.

Again, the update operation is not mentioned in the above procedure, and PetShop is an online real-time data update mode using the way the Command object executes a single stored procedure for the update operation. Instead, Dawamish uses the DataAdapter Update method, which submits the dataset to the database in one go, and belongs to the off-line data update mode. The advantage of this pattern is that you can update bulk data at once and reduce the number of connections to the database. The disadvantage is that if the database changes very frequently, it is not appropriate to change the data in real time. Specific data-updating methods need to be used in the light of specific circumstances.

In general, if you only need to read the data quickly and display it, it is recommended that you use DataReader, if you need to make a large number of changes to the data, there is a large number of concurrent access, and do not need real-time tracking database changes, it is recommended that the dataset. Of course, these two situations are a bit extreme, the actual application environment may have very complex conditions, the specific needs of your own situation, comprehensive use, but I personally still prefer the PetShop style:

This article only tries on the above two typical. NET application routine data access mechanism has done a simple tracing analysis.