About. Net petshop and duwamish ADO. NET database programming

Overview

ADO. NET provides us with powerful database development capabilities. Multiple built-in objects provide different options for our database programming. However, when we allow flexible selection, many beginners are also confused. Should I use datareader or dataadapter? I only want to read a small amount of data. Do I have to fill the entire dataset with fill? Why can't datareader provide a data update method like recordset? What are the advantages of dataset?

In this article, I will make some simple analysis and comparison on the. NET petshop database programming mode and duwamish database programming mode. If you have any of the above questions, I believe that after reading this article, you can develop a database programming mode that best suits your application according to your specific needs.

　　Brief Introduction to. Net petshop and duwamish

I believe you have heard of the famous "Pet Store Competition". Yes, one of the protagonists of this article is the winner. net petshop, Microsoft claims to be 27 times faster and 1/4 of the Code volume is far ahead of the J2EE-based PetStore pet store. Sun has complained about this and accused the war of moisture. However ,. net petshop is definitely a classic. net instance tutorial, at least to provide us with a way to catch up with J2EE, it is: http://www.gotdotnet.com/team/compare

. Net petshop pet Online Store homepage

Duwamish is an online bookstore with simple appearance and extremely complicated interior. net complete application example, as a Microsoft official sample, it provides both C # and VB. net two language versions, and also attached a large number of detailed Chinese information, if printed out, it is really home travel, sleep essential things. What? Have you heard of it? If you have installed Visual Studio. if it is not installed yet, you can find and install it in the enterprise Samples Directory of vs.net. For example: C: \ Program Files \ Microsoft Visual Studio. net \ enterprise samples \ duwamish 7.0 CS.

Duwamish online e-bookstore Homepage

　　Structure Description

Both stores adopt the n-layer application structure (there is no doubt that the n-layer application architecture should be developed by you. net Applications, even if you only want to make a Web page counter), the difference is that petshop uses the most common three-tier application structure, namely the presentation layer, middle layer and data layer. Duwamish adopts a layer-4 Application structure separated by different projects, which are the presentation layer, business appearance layer, business rule layer, and data layer. We will not discuss the advantages and disadvantages of these two structures in detail, and the reasons for such a hierarchy, because the focus of this article is not here. We mainly analyze their database programming models.

　　Duwamish Data Access Analysis

First, let's take a look at the duwamish bookstore, which uses the data storage mode that dataadapter and dataset work with. What's different is that it performs subclass extension on dataset as a data carrier, that is, custom dataset is used for data transmission between layers. The following is a custom dataset example:

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 is called in the constructor. In this way, the customized books table is bundled with the dataset, saving the time for column mapping, this is really a good idea. Why didn't I think of it? :)

After resolving the data structure, let's take a look at the code implementation at the data layer. In duwamish, there are five classes in the data layer: books, categories, MERS MERs, and orders, each class is only responsible for data access. The following is the sample code of 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) { 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 data layer code. We can see that duwamish uses the dataadapter to fill the data in the Custom dataset, and then returns the dataset. I am surprised that the specific data access method such as getbookbyid can be seen in the data access layer. Although there is still an abstract fillbookdata method, there are three layers above, what does the upper layer do if the bottom layer does this? The answer is Data check. The upper layer basically performs some strict Data Validity verification (of course, it also includes some complicated 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 method, only

Dataset = customersdataaccess. loadcustomerbyemail (emailaddress );

In this case, the data layer is called directly. Others are verifying legitimacy. We can realize how important the robustness of the system is to be considered for a real enterprise-level development.

　　Analysis of. Net petshop Data Access

OK, duwamish is finished. Let's take a look at the data access mechanism of petshop.

Petshop has only one project. The hierarchical method is to write the intermediate layer and data layer as CS files in the components directory, where the data layer is a class named database, it encapsulates all underlying operations on the database. 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 another data access method that is completely different from duwamish. It abstracts all data access methods into a runproc method. As for the returned data, it is a bit lazy and returns a datareader to you. Read it by yourself. Do you still remember what is the cross-layer data transmission carrier used by duwamish? By the way, it is dataset, which is filled by the data layer and returned to the middle layer. But here, the data transmission carrier at the data layer and the transmission layer is changed to datareader. In fact, it cannot be called a data carrier because the data has not started to be read. Here, the role of datareader is similar to that of pointer. Maybe we should call it "Data Reference ":)

Next, let's look at how the datareader is "processed:

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 (); // See 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? Previously, we performed data paging here, and only returned the minimum amount of data to meet the needs, rather than just like many of us who like to be lazy, simply bind the entire able to the DataGrid, resulting in a large amount of data redundancy.

Here, the data is actually read and manually filled into a custom object array. 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 a little surprised why I don't use struct? Is the performance gap between struct and class in. Net NEGLIGIBLE?

　　Analysis Summary

By observing the specific implementation of these two stores, we get two different data access modes. duwamish uses dataset as the core, because dataset provides a lot of related methods in this regard, therefore, the data transmission, data format definition, and data verification of the entire application are carried out around dataset. The definition of the entire architecture is very clear and rigorous, but it seems a little huge. Petshop does not use a dataset in the entire program. The program is simple and lightweight, but not as robust as duwamish. These two programs are written by different Microsoft teams, so they have different styles. However, they all represent the. NET standard mode. Here, you should have a better understanding of the questions raised at the beginning of the article.

In addition, please note that after opening the data connection, petshop does not read the data immediately. Instead, it passes the datareader to another object for data read and closes the connection. In this way, the data connection time is extended, and the database connection is a very valuable server resource. In contrast, dawamish fills in immediately after connecting to the database, then, the rapid release of database connections is more conducive to concurrent access by a large number of users.

Another point is that the update operation is not mentioned in the above program. petshop uses the command object to execute a single Stored Procedure for the update operation. It is an online real-time data update mode. Dawamish adopts the dataadapter update method to submit dataset changes to the database at one time, which is an offline data update mode. The advantage of this mode is that you can update a large volume of data at a time to reduce the number of database connections. The disadvantage is that it is not appropriate to track data changes in real time when the database changes frequently. Specific data update methods should be adopted according to specific situations.

In general, if you only need to quickly read and display the data, we recommend that you use datareader. If you need to make a lot of changes to the data, there is a possibility of a large number of concurrent accesses, in addition, you do not need to track database changes in real time. We recommend that you use dataset. Of course, these two situations are a bit extreme, and the actual application environment may have complicated conditions. You need to review the situation on your own and use them comprehensively, however, I personally prefer petshop's lightweight style :)

This article only attempts to make a simple Tracing Analysis on the Data Access Mechanism of the above two typical. NET application routines.