10 Popular tips for mastering ado.net

ado| tricks ADO. NET provides a unified programming pattern and a set of common classes for any type of data access, regardless of the language in which you develop your code. Ado. NET is brand new, but as consistent as possible with ADO, it transforms the programming pattern from a client/server, a connection-based pattern to a new pattern that lets the disconnected front-end download records, work offline, and then reconnect to submit changes. Ado. NET is a WinForms application, ASP. NET application and Web services, a common feature. Its functionality can be implemented across LAN and Internet connections, and can be implemented in stateful (stateful) and stateless (stateless) situations.

This means that, as a common technology, ADO. NET objects are not equally powerful in all possible environments. Using Ado.net to build a data tier for a rich client is not the same as building a data tier for a Web application where a client is usually a shared and important entity, such as a Web server.

If you used to be an ADO developer and now you're using ado.net, you might think of data access as a universal object, such as a recordset. It is natural for us to match the old object pattern with the new object pattern and to use the existing methods. NET application. However, some good methods in the ADO environment may not be powerful when converting to a ado.net environment. Also, the complexity of seemingly trivial ado.net object schemas can lead to poor programming, undesirable code, and even functionality. I'm going to talk about 10 things that may be troubling you in ado.net programming, and provide tips and solutions to avoid them.

1. Avoid database-agnostic forms of programming
Ado. NET data access is strongly typed, which means that at any time you must understand what data source you are working on. Instead, in ADO, you can write data access code (they take advantage of the common pattern of OLE DB providers) and view the basic data source as just a parameter. The ADO object pattern provides a unique connection and command object that hides the characteristics of the underlying DBMS. 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. This feature is used by many developers to use an Access database outside the production environment to quickly test or demonstrate the application.

This cannot be done in ado.net, because at least the connected object must be a data source specific in Ado.net. You can't create a connection in an indirect or generic way unless you decide to use ADO's data access technology to--ole DB. In Ado.net, you can use the OleDbConnection class to create a connection to a database that allows you to access a variety of data sources. It is not particularly effective to apply classes in the System.Data.OleDb namespace in a. NET managed environment because they are used to access data using OLE DB. You can only use OLE DB to access data sources that do not have a. NET Data provider.

If your application must access a completely different data source (and you know what data source may be involved-a reasonable assumption), then you can create a centralized factory class that returns a Connection object, This connection object is managed through a generic interface (IDbConnection interface). The factory class uses application parameters internally to determine what. 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've got a connection object, you can create and execute a command in a database-agnostic way, regardless of the data source used. You can use the CreateCommand method and refer to the command through the IDbCommand interface. You can then use the ExecuteReader method or the ExecuteNonQuery method on the IDbCommand interface to execute the command. If you use ExecuteReader, you can get a data reader and you can use the IDataReader interface for general access to it.

You cannot populate a DataSet object with a common database programming pattern. In fact, you can't create a data adapter object in an indirect way, as you would create a command. The reason is that in some cases, the data adapter is different from the command object, and it can implicitly create a connection internally. However, it must work in a strongly typed manner and must know what the basic database server is.

2. Using strings to serialize extended properties
Several Ado.net objects have a collection called extendedproperties. This property, like the collection of Goods (cargo collection), can be used to store any type of user information. Datasets, DataTable, and DataColumn are the classes that can provide that data member. Ado. NET implements this ExtendedProperties property by using a hash table encapsulated by the PropertyCollection class. You can insert data into the collection using the Add method. The Add method uses two parameters to hold the data--key and value. The prototype of the method defines the parameter as a generic object type, and you can store any type of information. However, in special cases, you should pay special attention to the types of objects that are saved as extended attributes.

If you want to serialize a Ado.net object that contains extended properties to XML, it is best to use only strings. If not, you must take action against the Ado.net's inner serializer.

When Ado.net saves a DataSet object to XML, the contents of the ExtendedProperties collection are serialized into memory, but presumably for performance reasons, Ado.net uses the ToString method rather than the XML Serializer to achieve serialization. More importantly, when the Ado.net object is read back and restored, the ExtendedProperties collection contains the string representation of the object, not the object itself.

3. Using a ExecuteXmlReader with a BLOB field
The. NET data provider used for SQL Server (data Provider) uses the XML extension provided by the database and provides an additional method (ExecuteXmlReader) to execute the query. All the actors on the command object, such as ExecuteReader and Executescaler, use different methods to get 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 that has been bound to a XmlTextReader object. In this way, you don't have to do extra work to process the data in XML. To achieve this, the query string must return the XML data. For SQL Server, it can be implemented when the query string contains a FOR XML clause. Although this is only one possibility.

A less-known scenario is that for ExecuteXmlReader to work, it's enough to have the result set contain XML data. The following query method is fine, 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 whose text is displayed as XML. A brief look at the internal structure of the ExecuteXmlReader method will help us understand. The method executes the query using ExecuteReader and obtains 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 is always busy until the XML reader stops working. The SQL Server provider is the only provider that provides a way for us to read data directly from an XML reader, but this approach is more about provider-related, and less relevant to database performance. Oracle supports XML queries, but Oracle's data providers do not support XML queries. By contrast, writing a ExecuteXmlReader method for an OLE DB data provider is not difficult (click this download instance).

4. Do not try to cache a DataView
The DataSet and DataTable objects are unique Ado.net objects that contain data. DataView is a lightweight, serializable class that represents only the views that are built on a single table. You can filter the view according to the state of an expression or row. 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 simply caches the index of the rows in the base table that matches the current filter. The order of cached indexes is consistent with the current sort expression. Caching a DataView without caching a basic DataTable is not possible.

For example, a asp.net application that provides paging, such as by using the DataGrid control, usually ends 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 just an index, and if there is no basic DataTable object, it is useless.

5. Use Find to read a record
You can read a specific row in a DataTable object by using the Select method of the DataTable to run an in-memory query, or by setting a filter on the view to filter out all records that do not match the specified criteria. You can set a filter by setting the RowFilter property on the DataView class. Both of these methods use the same engine to select records. They can accept an expression, parse it, and ask for the values of each clause. The Select method of the DataTable returns an array with all the matching DataRow objects. The RowFilter property rebuilds the internal index of the DataView to contain all (and only) matching records. The application can then access the record. The two methods are almost the same in performance, and which method depends on the environment and personal preferences. For example, if you are using a data-bound control, such as a DataGrid or DataList, then RowFilter is ideal. If you have to process a bunch of records, the Select method is better.

However, you can also use another method (still based on DataView), which is the quickest way to read a record in a table. The method is to use 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) to the field that forms the current index. In the preceding code, the value 10248 matches the column OrderID. If the Sort property is empty and the DataTable object has a primary key, the columns in the primary key are applied. The Find method returns a value based on the 0 position of the first row that matches.

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 preceding code allows you to match the values of multiple columns by using the overloaded method of find (with a set of objects).

6. As far as possible with the predefined data
Ado. NET object pattern allows us to easily implement sorting. You can create a DataView object and set its Sort property; Ado.net Runtime View the new sort expression and reorder the view. This step is implemented in memory, but it is not fast. The cost of sorting is high, and more importantly, it is not a linear operation (linear operation). Sorting a set of data requires the computational cost of N*log (n), which means that as the number of items to be sorted increases, the cost of the line increase is significant. Therefore, you should limit the ordering in your application and use the sorted data as much as possible. In Web applications, dynamic sorting has a significant impact on performance. In this case, you should design the application, limit the need for dynamic sorting, and rely on algorithms that are written dead in the database server. Unless you are using an application that can make complexity less complex than the N*log (n) limit, avoid using a manual sorting algorithm, which 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 complete object pattern for obtaining and managing schema information. You should use ActiveX Data Objects Extensions for data Definition Language and Security (ADOX) or local functionality provided by each database to get and change schema information. ADOX is an extension of an ADO object that includes the objects used to create and modify schemas. You can write code that works for a variety of data sources (regardless of the local syntax), because ADOX is an object-based approach to managing schemas.

You can use a data reader object to read (not set) simple schema information. All data reader classes (OleDbDataReader, SqlDataReader, OracleDataReader) provide a getschematable method that can read the metadata information of the queried columns. GetSchemaTable Returns a DataTable object (format is one row per column) and a fixed set of columns containing information. The returned metadata can be grouped into three categories: column metadata data, database features, and column properties. The columns returned can be AllowDBNull, isautoincrement, ColumnName, Isexpression, IsReadOnly, and NumericPrecision. There is a complete list in the MSDN material (see Additional resources).

When you invoke ExecuteReader, if you execute the KeyInfo command, the GetSchemaTable method can return more accurate data. You can combine KeyInfo behavior with default behavior, execute a separate command and get schema and data: reader = cmd. ExecuteReader (_
Commandbehavior.keyinfo Or _
CommandBehavior.CloseConnection)



Only values that perform the Keyinfo,iskey, BaseTableName, isaliased, Isexpression, and Ishidden fields can be returned correctly. If you perform KeyInfo, critical columns (if any) are usually added at the bottom of the result set, but do not return data to them.

8. Saving space with a derived class and custom serialization
Only two Ado.net objects are--datatable and datasets that are marked as serializable. Serialization in the. NET framework is accomplished by formatter objects. They can save an object instance to a binary or a soap stream (stream).. NET formatter uses reflection to extract any necessary information. However, if the class implements the ISerializable interface, then the. NET formatter gives the interface a way to make copies of all the information that needs to be serialized into a single memory buffer. Both the DataTable and DataSet classes support serialization through the ISerializable interface.

If you serialize a DataTable or a dataset to a binary (binary stream), you should be able to get very compact output results. Although the resulting file you get is minimal, unfortunately, it's actually not small. Paradoxically, you save a binary dataset much larger than the same dataset you save to XML with the WriteXml method.

To explain this, we need to see how the Ado.net object is serialized. When a DataSet object is serialized, it saves the xml-based DiffGram notation in the formatter buffer. In a serial DataTable, it first creates a temporary dataset object, defines it as its parent, and then serially acts as a DiffGram.

A DiffGram is an XML stream that provides a stateful representation of the tables and rows in a dataset. A DiffGram file is very detailed and somewhat verbose. DiffGram contains the current data, as well as the original values of the modified rows and unresolved errors. When we save a dataset or a DataTable, all this information is passed to serializer. The serialized object always contains XML data, so even when the output stream is binary, the final output will still be large.

You can create a new serializable class that inherits the DataTable or dataset to solve the problem and save the Ado.net object more efficiently. You must mark the new class with the <serizlizable () > attribute, even if the parent class is serializable. In fact, serial (serizlizability) is not a class property that can be automatically inherited. A new class that you build from a DataTable or DataSet can also implement the ISerializable interface. Of course, you can choose a different serialization scheme for the new class. A simple and effective way is to map all the members of a DataTable class to arrays and value members.

The use of a derived class and a custom serialization scheme can save up to 80% of disk space for a DataSet object. The ratio of space saved depends on the data type in the dataset. The more you base your data on text, the more space you will save. However, using binary BLOB fields can only save about 25% of the space (download a complete example).

9. Select a paging mechanism that fits your data
The DataGrid server control makes it easier for you to display data in a Web application with a variable length page. The control has binding and formatting capabilities that can accept a ado.net data object and generate HTML code for the browser. For performance reasons, the DataGrid does not cache the contents of the data source in the view state of the page. Therefore, when you return to the page, you must populate the grid. There are two ways to do this: cache the data source as a whole or part on the Web server, and then read back, or load the required records from the physical database for each request. If you choose the first method, the data is read only once from the store, stored in a cache, and read back for subsequent postback events. We usually save this data in an in-memory global object (such as session or cache). We use a dataset to gather all the data we need and save it in memory. Saving a DataSet object in session is not the same as the thread meaning in ADO, but it can still affect scalability by reducing the amount of memory available to the Web server.

If the data to be displayed is session-specific, it is better to load the record page each time the page is returned than to cache the data with a dataset and ASP.net global objects. Well-written SQL code can divide the result set into many pages, plus the custom paging mechanism built into the DataGrid control, we can get the best solution to keep the ASP.net application scalable and good performance.

For Windows applications, my advice is the opposite. Desktop applications are ideal for applying disconnected programming patterns (datasets and other Ado.net objects to make this pattern simpler). Of course, this does not mean that you can download thousands of records in the client's carefree way. Although you can use ado.net objects for any kind of. NET application, how they are used varies depending on the situation.

10. Accessing multiple result sets
Depending on the syntax of the query, you can return multiple result sets. By default, data reader is located on the first result set. You can scroll through the record in the current result set using the Read method. When the last record is found, the Read method returns false and does not continue reading. You should use the NextResult method to transfer to the next result set. If there are no more result sets to read, the method returns false. The following code shows how to access all records in all returned result sets: Dim reader as SqlDataReader
Cmd. Connection.Open ()
reader = cmd. ExecuteReader ()
Todo
' Move through the ' resultset
While reader. Read ()
' Access the row
End While
Loop while reader. NextResult ()
Reader. Close ()
Cmd. Connection.close ()




When you read the contents of a row, you can identify the column by index or by name. Indexing can be faster because the provider can enter directly into the buffer. If you specify a column name, the provider uses the GetOrdinal method to convert the name to the appropriate index and then perform an indexed access. Note that for SQL Server data reader, all getxxx methods actually invoke the corresponding getsqlxxx method. For Oracle data Reader, the situation is similar and local data is always written into the. NET Framework type. The OracleDataReader class provides a set of private getxxx methods for its own internal type. These methods include Getoraclebfile, Getoraclebinary and Getoracledatetime. Conversely, OLE DB and ODBC readers have only a separate set of Get methods.

The. NET Framework version 1.1 by adding a method HasRows expands the programming interface of the data readers, which returns a Boolean value to indicate whether there are many rows that need to be read. (This is one of the drawbacks of ASP.net 1.0.) However, the method does not tell us the number of valid rows. Similarly, there is no method or technique that allows 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 through multiple REF CURSOR objects. How many result sets do you have to associate with the commands so that the NextResult method can be used for Oracle databases. In the command text, a ado.net result set is consistent with an Oracle REF cursor. The output parameter names must match the pointer name, and their type must be oracletype.cursor. For example, if the stored procedure (or command text) to run refers to two pointers (employees and orders), the following code shows how to set it 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 a OracleCommand object that points to a command or a stored procedure. It executes the code, creating two ref CURSOR, called Employees and orders. The name of the REF cursor and the names of the Ado.net output parameters must match.

Ado. NET object pattern contains two main parts--the hosting provider and the Database-agnostic container class, such as a dataset. A managed provider is a new type of data source connector that replaces the COM-based OLE DB provider. By the time I wrote this article, only a handful of hosting providers were connected to the commercial DBMS. The. NET Framework version 1.1 contains only a few local providers-providers and ODBC drivers for SQL Server, Oracle, and all OLE DB. Third-party vendors also support MySQL and provide Oracle with alternative providers.

Ado. NET looks like ADO, and the managed provider is structurally comparable to OLE DB providers. In addition to these similarities, effective programming in ado.net requires a new set of techniques and good methods. In most cases, you can get a lot of skills by writing code and accumulate experience with object patterns. When you are studying Ado.net programming further, remember the 10 ado.net techniques I have mentioned in this article.