Master 10 popular techniques of ADO. net

ADO. NET provides a unified programming mode and a set of public classes for any type of data access, regardless of the language in which you develop code. ADO. net is brand new, but it is as consistent as possible with ADO, it enables the programming mode from a client/server, connection-based mode to a new mode, this new mode allows disconnected front-end download records, offline work, and reconnection to submit changes. Ado. Net is a common feature of winforms applications, ASP. NET applications, and Web services. Its functions can be implemented across LAN and Internet connections, and can be implemented in the case of stateful and stateless.

This means that, as a common technology, ADO. Net objects are not equally powerful in all possible environments. Use ADO. net is a rich client (rich client) to build a data layer with the same client is usually shared and important entities (such as Web servers) the Web application build a data layer is not the same.

If you used to be an ADO developer and now use ADO. net, you may regard data access as a 10 thousand-level object, such as recordset. We naturally match the old object pattern with the new object pattern, and use the existing method for. NET applications. However, some good methods in the ADO environment may not be powerful when being converted to the ADO. NET environment. Furthermore, the complexity of the seemingly insignificant ADO. Net object mode may lead to poor programming conditions, unsatisfactory code, or even failure to implement functions. I will talk about 10 things that may bring you trouble in ADO. NET programming, and provide tips and solutions to avoid them.

1. Avoid database-agnostic Programming

Data access in ADO. NET is strongly typed, that is, you must understand what data source you are processing at any time ). On the contrary, in ADO, you can write data access code (which fully utilizes the general mode of the ole db Provider) and regard the basic data source as a parameter only. The ADO object mode provides unique connection and command objects that hide basic DBMS features. Once you set the provider attribute on the connection object, the same code is required to create a command object for SQL Server or Oracle. Many developers use the ACCESS database outside the production environment to quickly test or demonstrate applications.

This cannot be done in ADO. net, because in ADO. net, at least the connection object must be specific to the data source. You cannot create a connection in an indirect or general way, unless you decide to use the ADO Data Access Technology-ole db. In ADO. net, you can use the oledbconnection class to create a connection to a database, which allows you to access various data sources. The use of classes in the system. Data. oledb namespace in the. NET hosting environment is not particularly effective because they use ole db to access data. You can only use ole db to access data sources without. NET data providers.

If your application must access all different data sources (and you know what data sources may be involved-a reasonable assumption), you can create a centralized factory class, it returns a connection object and uses a common interface (idbconnection Interface) to manage the connection object. The factory class uses application parameters internally to determine what to use. NET data provider: 'create the connection

Dim factory as new myappconnectionfactory

Dim conn as idbconnection

Conn = factory. createconnection (connstring)

'Create the command

Dim cmd as idbcommand = conn. createcommand (query)

Once you get a connection object, you can create and execute a command in database-agnostic mode, regardless of the data source used. You can use the createcommand method and use the idbcommand interface to reference the command. Then, you can use the executereader method or executenonquery method on the idbcommand interface to execute commands. If you use executereader, you can get a data reader and use the idatareader interface to perform general access to it.

You cannot use a common database programming mode to fill in a DataSet object. In fact, you cannot create a data adapter object in an indirect way like creating a command. The reason is that, in some cases, data adapter is different from the command object and can implicitly create a connection internally. However, it must work in a strongly typed way and know what the basic database server is.

2. Use strings to serialize extended attributes

Several ADO. Net objects have a set called extendedproperties. Like Cargo Collection, this property can be used to store any type of user information. Dataset, able, and datacolumn are classes that can provide this data member. Ado. Net implements the extendedproperties by using a hash table encapsulated by the propertycollection class. You can use the add method to insert data into the collection. The add method uses two parameters to save data -- Key and value. This method defines parameters as common object types, and you can store any type of information. However, in special cases, you should pay special attention to the types of objects saved as extended attributes.

If you want to serialize ADO. Net objects that contain extended attributes to XML, you 'd better use only strings. If not, you must take countermeasures against the inherent serializer behavior of ADO. net.

When ADO. when a DataSet object is saved to XML, the content of the extendedproperties set is serialized into the memory, but it is probably due to performance reasons, ADO. net uses the tostring method instead of the XML serializer to implement serialization. More importantly, when the ADO. Net object is read back and restored, the extendedproperties set contains the string representation of the object, not the object itself.

3. Use executexmlreader with BLOB fields

The. NET data provider for SQL Server uses the XML extension provided by the database and provides an additional method (executexmlreader) to execute queries. All Executors (such as executereader and executescaler) on the command object use different methods to obtain the result set. Excecutereader returns data through a managed pointer (managed cursor), while executescaler returns the first value in the result set as a scalar value. Executexmlreader executes the query and returns an XML-based output stream bound to an xmltextreader object. In this way, you do not need to do additional work to process data in XML format. To achieve this, the query string must return XML data. For SQL Server, when a query string contains a for XML clause, it can be implemented. Although this is only a possibility.

An unfamiliar situation is that to make executexmlreader work, it is enough to make the result set contain XML data. The following query method is good, as long as the column contains text in XML format: Select data from table where key = 1

This column is a typical blob or ntext field, and its text is displayed as XML. A brief look at the internal structure of the executexmlreader method will help us understand it. This method uses executereader to execute queries and obtain a data stream object from the data provider. Next, it binds the data stream to a newly created instance of the xmltextreader class, And the instance is returned to the caller. The connection remains busy until the XML reader stops working. The SQL Server Provider is the only provider. It provides a method for us to directly read data from an XML reader. However, this method is more related to the provider, the relationship with database performance is not big. Oracle supports XML queries, But Oracle data providers do not support XML queries. In contrast, it is not difficult to compile an executexmlreader method for the ole db data provider (Click here to download the instance ).

4. Do not try to cache a dataview

Dataset and datatable objects are the only ADO. Net objects that contain data. Dataview is a lightweight class that cannot be serialized. It only represents a view built on a table ). You can filter the view based on an expression or row status. Many applications require you to manage data views and bind them to data controls, such as Windows and web DataGrid controls. A dataview object cannot cache data. It only caches the indexes of rows in the basic table that matches the current filter. The cache index sequence is consistent with the current sorting expression. You cannot cache dataview without caching the basic able.

For example, ASP. NET applications that provide paging (such as using the DataGrid Control) usually end with a dataview object because it supports sorting and filtering. In some cases (mostly for performance reasons), you may decide to cache the data source. The object to be cached cannot be dataview (it is the object you actually bind ). A dataview is only an index. If there is no basic able object, it is useless.

5. Use Find to read a record

You can use the select method of datatable to run a query in memory, or set a filter on the view to filter out all records that do not match the specified standard, you can read a specific row in a able object. You can set a filter by setting the rowfilter attribute of the dataview class. Both methods use the same engine to select records. They can accept an expression, parse it, and evaluate the values of each clause. The select method of datatable returns an array with all matched datarow objects. The rowfilter attribute reconstructs the internal index of dataview to contain all (and only contain) matched records. Then, the application can access the record. These two methods have almost the same performance. The method used depends on the environment and personal preferences. For example, if you use a data-bound control, such as a DataGrid or datalist, rowfilter is ideal. If you have to process a string of records, the Select method is better.

However, you can also use another method (still based on dataview), which is the fastest way to read records in a table. This method uses find: dim view as dataview

View = new dataview (table)

View. Sort = "orderid"

Dim index as integer = view. Find (10248)

Dim row as datarow = view (INDEX). Row

The find method uses the current index of the view and matches the specified value (or multiple values) with the fields that form the current index. In the preceding code, the value 10248 matches the orderid of the column. If the sort attribute is empty and the datatable object has a primary key, the columns in the primary key are used. The find method returns the value of the zero position in the first matched row.

If you want to return multiple records, you can use the findrows evolution form: view. Sort = "orderid, discount"

Dim keys (1) as object

Keys (0) = 10248

Keys (1) = 0

Dim row as datarow = _

View (view. Find (KEYS). Row

The previous Code allows you to use the find overload method (with a set of objects) to match values of multiple columns.

6. Use pre-sorted data as much as possible

The ADO. Net object mode makes it easy for us to sort objects. You can create a dataview object and set its sort attribute. Ado. Net runtime can view the new sort expression and re-compile the index for the view. This step is implemented in the memory, but the speed is not fast. Sorting is costly. More importantly, it is not a linear operation ). Sorting a group of data requires n * log (n) computing costs. That is to say, as the number of entries to be sorted increases, the cost of linear increase is very high. Therefore, you should restrict the sorting in the application and use the pre-sorted data as much as possible. In Web applications, dynamic sorting has a considerable impact on performance. In this case, you should design applications, restrict the demand for dynamic sorting, and rely on algorithms written to the database server. Unless you are using a special feature of the application that can make the complexity lower than the limit of N * log (N), avoid using the manual sorting algorithm, because this algorithm may be worse than the algorithm in the system.

7. ADOX can help you get and change schema information

Ado. Net does not provide a full object mode for obtaining and managing schema information. You should use ActiveX Data Objects extensions for Data Definition Language and Security (ADOX) or use the local functions provided by each database to obtain and change schema information. ADOX is an extension of the ADO object. It includes the object used to create and modify the schema. You can write code for various data sources (regardless of the local syntax), because ADOX is an object-based method for managing schema.

You can use a data reader object to read (not set) Simple schema information. All data reader classes (oledbdatareader, sqldatareader, and oracledatareader) provide the getschematable method, which can read the metadata information of the queried columns. Getschematable returns a datatable object (in the format of one row per column) and a fixed set of columns containing information. The returned metadata can be divided into three types: column metadata, database features, and column attributes. The returned columns can be allowdbnull, isautoincrement, columnname, isexpression, isreadonly, and numericprecision. A complete list is provided in the msdn document (see additional resources ).

When executereader is called, If you execute the keyinfo command, the getschematable method can return more precise data. You can combine the keyinfo behavior with the default behavior, execute a separate command, and obtain the schema and data: reader = cmd. executereader (_

Commandbehavior. keyinfo or _

Commandbehavior. closeconnection)

Only the values of the keyinfo, iskey, basetablename, isaliased, isexpression, and ishidden fields can be correctly returned. If keyinfo is executed, key columns (if any) are usually added at the bottom of the result set, but data is not returned to them.

8. Use a derived class and custom serialization to save space

Only two ADO. Net objects are marked serializable -- able and dataset .. The serialization in the. NET Framework is completed through formatter objects. They can save an object instance to a binary or a soap stream .. Net formatter uses reflection to extract any necessary information. However, if this class implements the iserializable interface, the. NET formatter will give way to the interface method, so that they are responsible for copying all the information that needs to be serialized into a memory buffer. Datatable and dataset classes support serialization through the iserializable interface.

If you Serial A able or dataset to a binary stream, you should be able to get very compact output results. Although the result file you get is the smallest, unfortunately it is actually not small. It is ridiculous that the dataset you save to a binary value is much larger than the same dataset you save to XML using the writexml method.

To explain this situation, we need to see how the ADO. Net object is serialized. When serializing A DataSet object, it saves the XML-based DiffGram representation in the formatter buffer. When serializing a able, it first creates a temporary DataSet object, defines it as its parent, and then serializes it as a DiffGram.

A DiffGram is an XML stream that provides a stateful representation of tables and rows in dataset. A DiffGram file is very detailed and lengthy. DiffGram contains the current data and the initial values of modified rows and unsolved errors. When we save a dataset or a able, all this information will be passed to serializer. Serialized objects always contain XML data. Therefore, even if the output stream is binary, the final output result is still large.

You can create a new serializable class that inherits datatable or dataset to solve this problem and save ADO. Net objects more effectively. You must use the <serizlizable ()> attribute to mark the new class, even if the parent class can be serialized. In fact, serizlizability is not a class attribute that can be automatically inherited. You can also implement the iserializable interface for the new class built from datatable or dataset. Of course, you can choose a different serialization solution for the new class. A simple and effective method is to map all the members of the able class to the array and value members.

Using a derived class and a custom serialization solution can save up to 80% disk space for a DataSet object. The ratio of Space saved depends on the Data Type in dataset. The more text your data is based on, the more space you save. However, using the binary blob field can only save about 25% of the space (download a complete example ).

9. Select a paging mechanism suitable for your data

The DataGrid server control makes it easier for us to display data on pages with variable lengths in Web applications. The control can be bound and formatted. It can accept an ADO. NET data object and generate HTML code for the browser. For performance reasons, in the view status of the page, the DataGrid does not cache the content of the data source. Therefore, when the page is returned, you must fill in the grid. To achieve this, You Can cache the data source as a whole or a part on the Web server, and then read it back; or load the required records for each request from the physical database. If you choose the first method, the data is read only once from the storage, stored in a cache, and read back for future PostBack events. We usually use a Global Object (such as session or cache) in the memory to save the data. We use dataset to collect all the required data and store it in the memory. Saving a DataSet object in a session does not have the same thread meaning as that in ADO. However, reducing the memory available on the Web server can still affect scalability.

If the data to be displayed is session-specific, loading the record page every time a page is returned is better than caching data with a dataset and ASP. NET global object. The well-written SQL code can divide the result set into many pages, coupled with the built-in custom paging MECHANISM OF THE DataGrid Control, we can get the best solution to maintain ASP.. NET application scalability and good performance.

For Windows applications, I suggest the opposite. Desktop applications are suitable for program modes with applications disconnected (dataset and other ADO. Net Objects make this mode easier ). Of course, this does not mean that you can download thousands of records from the client without any worries. Although you can use ADO. Net objects for any type of. NET applications, how to use them varies with the actual situation.

10. Access Multiple result sets

Based on the query syntax, you can return multiple result sets. By default, data reader is located in the first result set. You can use the read method to scroll through the current result set to view records. When the last record is found, the read method returns false to stop reading. You should use the nextresult method to transfer to the next result set. If there are no more result sets to be read, this method returns false. The following code illustrates how to access all records in all returned results: dim reader as sqldatareader

Cmd. Connection. open ()

Reader = cmd. executereader ()

Do

'Move through the first resultset

While reader. Read ()

'Access the row

End while

Loop while reader. nextresult ()

Reader. Close ()

Cmd. Connection. Close ()

When you read the content of a row, you can identify the column by index or name. Using indexes can be faster, because the provider can directly access the buffer. If you specify a column name, the provider will use the getordinal method to convert the name to the corresponding index, and then perform index-based access. Note: For SQL server data reader, all getxxx methods actually call the corresponding getsqlxxx method. For ORACLE data reader, the situation is similar. Local data is always written into the. NET Framework type. The oracledatareader class provides a set of private getxxx methods for its internal types. These methods include getoraclebfile, getoraclebinary, and getoracledatetime. On the contrary, ole db and ODBC readers only have a separate set of get methods.

. NET Framework 1.1 extends the data readers programming interface by adding the hasrows method. This method returns a Boolean value to indicate whether many rows need to be read. (This is a weakness of ASP. NET 1.0 .) However, this method does not tell us the number of valid rows. Similarly, there are no methods or tricks for us to know in advance how many result sets have been returned.

In Oracle Database Programming, multiple result sets returned by a query or a stored procedure are processed by multiple ref cursor objects. The number of output parameters must be associated with the command so that the nextresult method can be used in the Oracle database. In the command text, An ADO. net result set is consistent with an oracle ref cursor. The output parameter names must match the pointer names, and their types must be oracletype. cursor. For example, if the stored procedure (or command text) to be run references two pointers (employees and orders), the following code illustrates how to set to return two result sets: dim P1 as oracleparameter

P1 = cmd. Parameters. Add ("employees", oracletype. cursor)

P1.direction = parameterdirection. Output

Dim P2 as oracleparameter

P2 = cmd. Parameters. Add ("orders", oracletype. cursor)

P2.direction = parameterdirection. Output

In the above Code, CMD is an oraclecommand object pointing to a command or a stored procedure. It executes the code and creates two ref cursor called employees and orders. The ref cursor name must match the name of the ADO. net output parameter.

The ADO. Net object mode consists of two main parts: the hosted provider and the database-agnostic container class, such as dataset. Managed providers are new types of data source connectors; they replace the com-based ole db providers. By the time I wrote this article, there were only a few managed providers to connect to commercial DBMS .. Net Framework 1.1 only contains several local providers-providers and ODBC drivers for SQL Server, Oracle, and all Ole databases. Third-party vendors also support MySQL and provide available providers for Oracle.

Ado. Net looks similar to ADO, And the managed provider structure is comparable to that of the ole db Provider. In addition to these similarities, effective programming in ADO. Net also requires a new set of skills and methods. In most cases, you can get a lot of skills by writing code and accumulate experience in object mode. When you are further studying ADO. NET programming, remember the 10 ADO. Net skills I 've mentioned in this article.