Release Date: 4/1/2004
| Update Date: 4/1/2004
Summary: Writing the best practices for Microsoft ADO.NET code, as well as recommendations for developers using ADO.NET. (Page 21 Print Page)
Note If you are not familiar with the ADO.NET and .NET Framework, see the Accessing Data With Ado.net section in the .NET Framework SDK. If you are an ADO programmer, please see ADO.NET for the ADO Programmer for more information.
Best practices using ADO.NET
Dennis Lu
Doug Rothaus
Microsoft Corporation
July 2002
Applicable to:
With experience in developing Microsoft ADO.NET and Microsoft® .NET Framework
This page
Introduction .NET Framework Data Provider Use DataReader, DataSet, DataAdapter, and DataView Use Command Use Connection and XML Integration More Useful Tips
Introduction
This article provides you with the best solution for implementing and achieving optimal performance, scalability, and features in Microsoft ADO.NET applications; while also telling best practices in using ADO.NET; Helps optimize ado.net application design.
This article contains:
• Information about the .NET Framework Data Provider. • Comparison between DataSet and DataReader, and interpretation of the best usage of each object in these objects. • Explain how to use DataSet, CommANDs, and Connections. • Information about integration with XML. • General skills and problems.
For additional information about ADO.NET Best Practices, see Part of the .NET Data Access Architecture Guide section in MSDN Library. Note, ". Net Data Access Architecture Guide mainly focuses on the structure of Microsoft SQL Server 7.0 or higher.
The following list provides additional information about ADO.NET:
• Newsgroup: By the NNTP news reading program in the news: //msnews.microsoft.com/microsoft.public.dotNet.framework.adonet or by the web located at http://msdn.microsoft.com/newsgroups/loadframes.asp Browser, you can access BDA newsgroups. • Discussion list: http://www.asplists.com/asplists/aspngdata.asp http://discuss.develop.com/dot.com.html
Back to top
.NET frame data provider
The data provider in the .NET framework functions between the application and the data source. The .NET Framework Data Provider can return query results from the data source, execute the command to the data source to the data source. This article includes which .NET framework data provider is some of the skills that best suits you.
Which .NET Framework Data Provider?
To make your application get the best performance, use the .NET Framework Data Provider that best suits your data source. There are a lot of data providers to choose your application. The following table provides information about the available data provider and which data source is best for each data provider.
Provider Details SQL Server .NET Data Provo can be found in system.data.sqlclient namespace. It is recommended to use the MICROSoft SQL Server version 7.0 or a higher intermediate application. Single-layer applications that use Microsoft Data Engine (MSDE) or Microsoft SQL Server version 7.0 or later. For Microsoft SQL Server 6.5 and earlier versions, you must use the OLE DB provider for SQL Server with the OLE DB .NET data provider. OLE DB .NET Data Provo can be found in system.Data.oledb namespace. It is recommended to use Microsoft SQL Server 6.5 or earlier, or support the OLE DB interface of any OLE DB interface listed in the OLE DB interface listed in the OLE DB interface listed in the OLE DB interface used by the OLE DB interface used by the OLE DB interface listed in the OLE DB interface listed in the OLE DB interface. use. (OLE DB 2.5 interface is not required.) For Microsoft SQL Server 7.0 or later, it is recommended to use SQL Server's .NET Framework Data Provider. It is recommended to use single-layer applications using Microsoft Access databases. No suggestion intercess databases use the ACCESS database. Disable ODBC (MSDasql) OLE DB Provider Support. To access an open database connection (ODBC) data source, you can download the ODBC .NET data provider, and .NET Framework SDK 1.1 will contain it. The ODBC .NET data provider can download the ODBC .NET data provider. Can be found in the Microsoft.Data.odbc namespace. Provides access to data sources connected to the ODBC driver. Note The ODBC Data Provider will be included in the .NET framework version that is about to be released in 1.1. The namespace containing the ODBC .NET data provider is System.Data.odbc. The .NET data provider for Oracle can download Microsoft .NET data provider for Oracle. Can be found in System.Data.OracleClient namespace. Provides access to Oracle Data Sources (Version 8.1.7 and Higher Versions). Note The .NET data provider for Oracle will be included in the .NET framework version that is about to be published in 1.1. Custom.NET Data Provider ADO.NET provides minimal set of interfaces that enable you to implement your own .NET framework data provider. For more information on creating a custom data provider, see the importing a .NET DATA Provider in the .NET Framework SDK. SQLXML hosted class For Microsoft SQL Server 2000 XML release (SQLXML 3.0) contains SQLXML hosted classes, which can access Microsoft SQL Server 2000 and its higher version of XML feature from the .NET framework. For example, these classes allow you to perform an XML template, perform an XML path language query on the server, or perform data updates with UpdateGrams or DiffGrams. SQLXML 3.0 introduces Web services for SQL Server 2000 based on SQLXML 1.0 and 2.0 features. For SQLXML 3.0, the stored procedure and XML template can be disclosed via SOAP. You can download SQLXML 3.0. Connect to SQL Server 7.0 or higher
To get optimal performance when connecting to Microsoft SQL Server 7.0 or later, use the SQL Server .NET data provider. The design purpose of the SQL Server .NET data provider is that you can access SQL Server directly without any additional techniques. Figure 1 illustrates the difference between different technologies available to access SQL Server 7.0 or higher. Figure 1. Accessing SQL Server 7.0 or higher connection method
Connect to ODBC data source
The ODBC .NET data provider can be found in the Microsoft.Data.odbc namespace, its structure is the same as the .NET data provider for SQL Server and OLE DB. ODBC .NET Data Provider (Download) Follow Name Conference - "ODBC" is prefix (for example, odbcconnection), and uses a standard ODBC connection string.
Note The ODBC .NET data provider will be included in the .NET framework version starting at 1.1. The namespace containing the ODBC .NET data provider is System.Data.odbc.
Back to top
Use DataReader, DataSet, DataAdapter and DataView
ADO.NET provides two objects to retrieve relational data and store it in memory: Dataset and DataReader. DataSet provides a relationship representation of data in memory, and a complete set includes some tables (these tables contain data, sorting and constrained data), and the relationship between tables. DataReader provides a fast, enter, read-only data stream from the database.
When using DataSet, DataAdapter often uses DataAdApter (may also be a CommandBuilder) to interact with the data source. When using DataSet, you can also use DataView to sort and filter data in DataSet. You can also inherit from DataSet, create a strong type DataSet, which is used to disclose the table, row, and listeners as strong type object properties.
The following topics include information involving: How to optimize access to data using DataSet or DataReader, and how to optimize how DataAdapter (including CommandBuilder) and DataView skills.
DataSet and DataReader
When designing an application, consider the level you need to function in your application to determine the use of DataSet or DataReader.
To do the following by the application, use DataSet:
• Navigate between multiple discrete sheets of the results. • A data from multiple data sources (eg, mixed data from multiple databases, an XML file, and a spreadsheet). • Exchange data between layers or use XML web services. Unlike DataReader, DataSet can be passed to the remote client. • Reuse the same row to improve performance (such as sort, search, or filtering data) by cache. • Perform a lot of processing per line. The extension processing of each line returned to DataReader will extend the necessary time to serve the DataReader, which affects performance. • Operation of data using XML operations, such as scalable style sheet language conversion (XSLT conversion) or XPath query.
For the following cases, you must use DataReader in your application:
• No cache data is required. • The result of the handle is too large, and the memory can't be put. • Once you need to enter, you will quickly access the data.
DataAdapter uses DataReader when you fill DataSet. Therefore, using DataAdapter replaces DataSet promotion performance as saving DataSet occupies memory and populating cycles required for DataSet. In general, this performance improvement is just symbolic, so design decisions should be based on the functionality required. Benefits using strong type DataSet
Another benefit of DataSet is to be inherited to create a strong type of DataSet. Strong Type Dataset is the benefits of design-time type checks, as well as the benefits of Microsoft Visual Studio .NET to the end of the strong class DataSet statement. After modifying the DataSet architecture or relational structure, you can create a strong type of DataSet, disclose the lines and columns as objects, not as item in the collection. For example, the name column of the client table is not disclosed, and the NAME property of the Customer object is disclosed. Type DataSet derived from the DataSet class, so there is no feature of DataSet. That is, the type of DataSet can still be remotely accessed and is provided as a data binding control (eg, DataGrid). If the architecture is unknown in advance, it can still benefit from the functionality of universal DataSet, but it is not available to the additional features of strong type DataSet.
Handling empty reference in strong type DataSet
When using strong type DataSet, you can discuss the DataSet XML Architecture Definition Language (XSD) schema to ensure that strong type Dataset processes empty references. NULLVALUE annotation allows you to use a specified value String.empty instead of dbnull, retaining an empty reference or an exception. Which option to choose depends on the context of the application. By default, if you encounter an empty reference, an exception will be triggered.
For more information, see Working With a typed dataset.
Refresh the data in DataSet
Use DataAdapter.Fill if you want to refresh the value in the DataSet with the update value on the server. If there is a primary key defined on a DataTable, DataAdapter.Fill matches the primary key and makes the value on the application server when changing to an existing line. Even if they modified them before refreshing, the RowState that refreshed the row is still set to unchanged. Note that if the primary key is not defined for the DataTable, DataAPter.Fill adds a new line with the possible host key value.
If you want to refresh the table with the current value from the server, keep any changes made to the rows in the table, you must first use the DataAdapter.Fill to populate the table and populate a new DataTable, then use the preserveChanges value True to put DataTableMerge to DataSet in.
Search data in DataSet
When queries in the DataSet matching lines match specific conditions, you can use an index-based lookup to improve search performance. When the primaryKey value is assured to DataTable, an index is created. An index is created when you create DataView for DataTable. Here are some techniques that use index-based findings.
• If you query the columns of the PrimaryKey that make up the DataTable, use DataTable.Rows.Find instead of DataTable.select. • For queries involved in non-master key columns, you can use DataView to improve performance. When the sort order is applied to the DataView, the index used when searching is created. DataView discloses the Find and Findrows methods to query the data in the underlying DataTable. • If you do not need to be sorted views, you can still use the index-based lookup by creating DataView for DataTable. Note that this will only bring benefits only when multiple query operations are performed on the data. If you only perform a single query, the process you need to create an index will reduce the performance boost brought by the index. DataView construction
If you create DataView and modify the sort, rowfilter or rowstatefilter properties, DataView will establish an index for the data in the base DATATABLE. When you create a DataView object, use the DataView constructor, which uses sort, rowfilter, and rowstatefilter values as constructor parameters (together with the base DataTable). The result is an index created. Create a "empty" DataView and then set the sort, rowfilter, or rowstatefilter property, which will result in at least two times.
Pagination
ADO.NET can explicitly control what data returned from the data source and how much data is available locally in the DataSet. Subject to the results of the query does not have a unique answer, but there are some techniques that should be considered when designing applications.
• Avoid overloading DataAPter.Fill with StartRecord and maxRecords values. When filling DataSet in this way, only the maxRecords parameter (starting from the StartRecord parameter ID) is used to populate the DataSet, but always returns a complete query. This will cause unnecessary processing to read "unwanted" records; and to return additional records, unnecessary server resources will be exhausted. • It is used to create a SQL statement, combining the WHERE clause and the Order By clause and TOP predicate. This technique depends on the existence of a way to identify each row. When you browse the next page record, modify the WHERE clause to include all unique identifiers greater than the record of the last unique identifier of the current page. When you browse the previous page record, modify the WHERE clause to return all unique identifiers less than the record of the first unique identifier of the current page. Both queries returned to the recorded TOP page. When browsing the previous page, you need to sort in sequences. This will effectively return the last page of the query (if needed, you may be reordered the result). For an example of this technology, see Paging Through a Query Result. • Another technology that only returns only one page record is to create a SQL statement, combine the use of TOP predicates and embedded SELECT statements. This technology does not rely on the existence of a way to uniquely identify each row. The first step in using this technology is to multiply the number of pages of the page with the page size. The result is then passed to the TOP predicate of SQL Query, which is arranged in ascending order. Then embed this query into another query, and the latter selects the TOP page size from the descended embedded query results. In essence, returning is the last page of embedded query. For example, to return the third page of the query result (page size is 10), you should write the command as follows: SELECT TOP 10 * from Customers Order By ID ASC) As table1
ORDER BY ID DESC Note that the result page returned from the query is displayed as sequence. You should be reordered if needed. • If the data does not change often, you can maintain a recording cache locally in the DataSet to improve performance. For example, you can store 10 pages in the local DataSet, and you only query new data from the data source only when the user browsing exceeds the cache first page and the last page.
For more information, see .NET Data Access Architecture Guide.
Fill DataSet with architecture
When populating DataSet with data, the DataAdapter.Fill method uses the DataSet's existing architecture and populates it with the data returned from SelectCommand. If there is no table name in the DataSet matches the table name to be filled, the Fill method creates a table. By default, Fill only defines columns and column types.
You can rewrite the default behavior of the Fill by setting the MISSINGSChemaAction property of DataAdapter. For example, let Fill creates a table architecture and also includes primary key information, unique constraint, column properties, whether it is allowed to be empty, maximum column length, only column, and automatic increment, you must specify DataAdapter.MissingsChemaAction as missingschemaAction. .Addwithkey. Or, before calling DataAdapter.fill, you can call DataAdapter.FillSchema to ensure that the schema has been in place when populating DataSet. The call to FillSchema generates an additional stroke to the server for retrieving additional architectural information. To get the best performance, you need to specify a DataSet schema before calling Fill, or set the MissingsChemaAction of DataAPter.
Best practices using CommandBuilder
Suppose SELECTCOMMAND performs a single table Select, CommandBuilder automatically generates DataAdapter's InsertCommand, UpdateCommand, and DeleteCommand properties based on DataAdapter's selectcommand properties. Here is some of the techniques that use CommandBuilder for optimal performance.
• The use of CommandBuilder should be limited to or in the designs. The processing necessary to generate the DataAdapter command property will affect performance. If you know the contents of the INSERT / UPDATE / DELETE statement, they are explicitly set. A better design skill is to create a stored procedure for the Insert / Update / Delete command and explicitly configure the DataAdapter command properties to use them. • CommandBuilder uses DataAdapter's SelectCommand property to determine the value of other command properties. If DataAdapter's SELECTCommand itself has changed, make sure the refreshschema is called to update the command properties. • If the DataAdapter command property is empty (the command property is empty), CommandBuilder generates a command for it. If the command properties are explicitly set, CommandBuilder will not rewrite it. If you want CommandBuilder to generate a command for the previously set command properties, set the command property to empty.
Batch SQL statement
Many database supports multiple command merge or batch to execute it. For example, SQL Server allows you to separate commands with a semicolon (;). Multiple commands merge into a single command, reduce the number of servers, and improve the performance of the application. For example, all predetermined deletes can be stored locally in the application, and then one batch command call is issued, and they are removed from the data source.
Although this does improve performance, it is possible to increase the complexity of the application when managing data updates in the DataSet. To remain simple, you may have to create a DataAdapter for each DataTable in DataSet.
Fill DataSet with multiple tables
If you use a batch SQL statement to retrieve multiple tables and populate the DataSet, the first mesh is named named by the table name specified to the Fill method. The subsequent mesh is specified to the table name of the Fill method plus a number naming from 1 and the increment is 1. For example, if you run the following code:
'Visual Basic
Dim da as sqldataadapter = new sqldataadapter ("Select * from order) DIM DS AS DATASET = New DataSet ()
Da.fill (DS, "Customers")
// C #
SqldataAdapter Da = New SqldataAdapter ("Select * from Orders;", MyConnection;
DataSet DS = New Dataset ();
Da.fill (DS, "Customers");
Data from the Customers table is placed in a DataTable called "Customers". Data from the Orders table is placed in the DATATABLE named "CUSTOMERS1".
After filling DataSet, it is easy to change the TABLENAME attribute of the "Customers1" table to "Orders". However, the following fill can cause the "Customers" table to be re-filled, and the "Orders" table is ignored and another "Customers1" table. In order to make remedy this situation, create a DataTableMapping, map "CUSTOMERS1" to "Orders" and create additional table mappings for other later tables. E.g:
'Visual Basic
Dim da as sqldataadapter = new sqldataadapter ("Select * from customer;", myconnection)
Da.TableMappings.Add ("Customers1", "Orders")
DIM DS AS DATASET = New Dataset ()
Da.fill (DS, "Customers")
// C #
SqldataAdapter Da = New SqldataAdapter ("Select * from Orders;", MyConnection;
Da.TableMappings.Add ("Customers1", "Orders");
DataSet DS = New Dataset ();
Da.fill (DS, "Customers");
Use DataReader
Here are some techniques that use DataReader to get the best performance, and also answered some common problems with DataReader.
• DataReader must be turned off before accessing any output parameters of related Command. • Turn off DataReader after completing reading data. If you use Connection just to return DataReader, turn off it immediately after closing DataReader. Another way to explicitly shut down Connection is to pass the CommandBehavior.CloseConnection to the ExecuteReader method to ensure that the relevant connection is turned off when DataReader is turned off. This is especially useful if you return DataReader from a method and you can't control the shutdown of DataReader or related connections. • DataReader cannot be accessed remotely between layers. DataReader is designed for connected data access. • When accessing column data, use Type Accessors, for example, getString, GetInt32, etc. This allows you to do not have to convert the Object returned by getValue into a specific type of procedure. • A single connection can only open a DataReader at a time. In ADO, if you open a single connection, and request two records that use only, read-only cursors, the ADO will implicitly open the second, unchecked to the data storage area within the cursor survival. , Then implicitly close the connection. For ADO.NET, "Secret" is very different. If you want to open two DataReaders on the same data store, you must explicitly create two connections, each DataReader. This is a method of providing more control for the use of a pool-based connection. • By default, DataRead is loaded into memory each time read. This allows columns to be randomly accessed within the current row. If this random access is not required, in order to improve performance, pass the commandbehavior.sequentialAlaccess to the ExecuteReader call. This will change the DataReader's default behavior to load data to memory only when the request is requested. Note that commandbehavior.sequentialAndbehavior.sequentialAlaccess requires sequential access to the column. That is, once the returned column is read, it cannot read it again. • If you have already completed data from DataReader, there is still a lot of unread results that hang, just call Command's Cancel before calling DataReader's Close. CLOSE calling DataReader will result in the retrieval of the pending result and clear the stream before shutting down the cursor. Calling Command's Cancel discards the results on the server so that DataReader does not have to read these results when it is off. If you want to return to the output parameters from Command, you have to call Cancel to give up. If you need to read any output parameters, don't call the Cancel of Command, just call the DataReader's Close. Binary large object (blob)
When you retrieve the binary large object (blob) with DataReader, you should pass your commandbehavior.sequentialAracse to the ExecuteReader method call. Because DataReader's default behavior is loaded into memory every time read, because the BLOB value may be very large, the result may be used in a large amount of memory due to a single BLOB. SequentialAccess sets the behavior of DataReader to only load the requested data. You can then use GetBytes or getChars to control how much data each time.
Remember, when using sequentialAccess, you cannot access different fields returned by DataReader in order. That is, if the query returns three columns, the third column is blob, and wants to access the data in the first two columns, it is necessary to access the first column value before accessing the BLOB data, and then accesses the value of the second column. This is because the data is now returned, and the data is no longer available once DataReader reads the data. For a detailed description of how to access blob in ADO.NET, see Obtaining Blob Values from A Database.
Back to top
Use command
ADO.NET provides several different methods for execution of commands and different options for optimizing commands. The following includes some techniques that are the performance of choosing the best command and how to improve the performance of the command.
Best practices using OLEDBCommand
Different. The command execution between the NET frame data provider is as standardized as possible. However, there is still a difference between the data provider. Some techniques are given below, which can be tailored to the command execution of the .NET frame data provider of OLE DB.
• Call the stored procedure using CommandType.Text in accordance with ODBC Call syntax. Use CommandType.StoredProcedure to generate an ODBC Call syntax secretly. • Be sure to set the type of OLEDBParameter, size (if applicable), and precision and range (if the parameter type is Numeric or Decimal). Note that if the parameter information is not explicitly supplied, OLEDBCommand recreates the OLE DB parameter accessor for each execution command.
Best practices using SQLCommand
Using SQLCommand to perform a quick prompt of the stored procedure: If the stored procedure is called, specify the commandType property of SQLCommand as StoredProcedure's CommandType. This does not need to analyze commands before execution by explicitly identifying the command as a stored procedure.
Use prepare method
For repeated parameterization commands, the Command.Prepare method improves performance. Prepare indicates that the data source is a command to be modified multiple times. To effectively use prepare, you need to completely understand how the data source responds to Prepare calls. For some data sources (such as SQL Server 2000), the command is implicitly optimized without having to call preted. For other (such as SQL Server 7.0) data sources, Prepare will be more effective.
Explicit specified architecture and metadata
As long as the user does not specify metadata information, many objects of ADO.NET will infer metadata information. Here are some examples:
• DataAdapter.Fill method, if there are no tables and columns in the DataSet, the DataAdapter.Fill method creates a table and column in the DataSet. • CommandBuilder, which generates a DataAdapter command property for a single table select command. • CommandBuilder.deriveParameters, which populates the Parameters collection of the Command object.
However, each time you use these features, there will be performance losses. These features are recommended to be designed to be designed and inum. In a possible case, the architecture and metadata are explicitly specified. These include definition tables and columns in DataSet, define the Command attribute of DataAdapter, and define Parameter information for Command.
ExecuteScalar and ExecutenonQuery
If you want to return a single value like count (*), sum (price) or AVG (Quantity), you can use Command.executeScalar. ExecuteScalar returns the value of the first column of the first line and returns the result set as a scalar value. Because it can be done in a single step, ExecuteScalar not only simplifies the code, but also improves performance; if you need to use DataReader, you need two steps to complete (ie, ExecuteReader value).
Use ExecutenonQuery using the SQL statements that do not return row, such as modifying data (such as INSERT, UPDATE, or DELETE) or only returns the output parameter or return value. This avoids any unnecessary processing used to create empty DataReaders.
For more information, see Executing a Command.
Test NULL
If the column in the table (in the database) is allowed to be empty, it cannot test whether the parameter value "is equal" empty. Instead, you need to write a WHERE clause, whether the test column and parameters are empty. The following SQL statement returns some rows, and their LastName columns are equal to the value assigned to the @LastName parameter, or the lastname column and @lastname parameters are empty.
Select * from customer
WHERE (Lastname = @lastname) or (lastname is null and @lastname is null))
Pass NULL as a parameter value
In the command of the database, when null values are sent as the parameter value, NULL cannot be used (Nothing in Visual Basic .NET). Need to use dbnull.value. E.g:
'Visual Basic
Dim param as sqlparameter = new sqlparameter ("@ name", sqldbtype.nvarchar, 20)
Param.Value = dbnull.value
// C #
Sqlparameter param = new Sqlparameter ("@ name", sqldbtype.nvarchar, 20);
Param.Value = dbnull.value;
Execute a transaction
ADO.NET transaction model has changed. In ADO, when the StartTransaction is called, any update operation after calling is considered part of a transaction. However, in ADO.NET, when the Connection.Begintransaction is called, a Transaction object is returned, and it needs to be linked to the Transaction property of Command. This design can perform multiple root transactions on a single connection. If the Command.Transaction property is not set to a transaction that is started against the associated Connection, then Command will fail and lead exceptions.
The upcoming .NET framework will make you register in an existing distributed transaction. This is ideal for the object pool scheme; in this scheme, a pool object is connected once, but the object is involved in multiple independent transactions. This feature is not available in the .NET Framework 1.0 release.
For more information on transactions, see Performing Transactions and .NET Data Access Architecture Guide.
Back to top Use connection
High-performance applications maintain the shortest time connection with data sources in use, and utilize performance enhancements, such as connecting pools. The following topics provide some techniques to help achieve better performance when connecting to the data source using ADO.NET.
connection pool
SQL Server, OLE DB, and .NET Frame Data Provider for ODBC implicit buffer connections. You can control the behavior of the connection pool by specifying a different attribute value in the connection string. For more information on how to control the behavior of the connection pool, see Connection Pooling for The SQL Server .NET Data Provider and Connection Pooling for the OLE DB .NET DATA Provider.
Optimize connection with DataAdapter
The DataAdapter's Fill and Update methods are automatically opened to the connection specified by the related command properties without closing. If the Fill or Update method opens the connection, Fill or Update will close it when the operation is complete. To achieve optimal performance, keep the connection to the database to open only when needed. At the same time, reduce the number of times that opens and closes the multi-operation connection.
If you only perform a single Fill or Update method call, it is recommended to allow Fill or Update methods to implicitly open and close connections. If there are many calls for Fill and / or Update, it is recommended to explicitly open the connection, call fill and / or update, and explicitly close the connection.
In addition, when transaction is performed, the connection is explicitly opened before starting the transaction, and the connection is turned off after commit. E.g:
'Visual Basic
Public Sub RunsqlTractions (Da As Sqldataadapter, MyConnection As SqlConnection, DS AS Dataset)
MyConnection.Open ()
Dim myTrans as sqltransaction = myconnection.begintransaction ()
mycommand.transaction = myTrans
Try
Da.UPDATE (DS)
MyTrans.commit ()
Console.writeline ("Update Successful.")
Catch e as exception
Try
MyTrans.rollback ()
Catch ex as sqlexception
IF not myTrans.connection is nothing then
Console.writeline ("an Exception of Type" & ex.gettype (). Tostring () &_
"Was Encountered While Attempting to Roll Back The Transaction.")
END IF
END TRY
Console.writeline ("An Exception of Type" & E.GETTYPE (). Tostring () & "WAS Encountered.")
Console.writeLine ("Update Failed.")
END TRY
MyConnection.Close ()
End Sub
// C #
Public void Runsqltransaction (SqlDataAdapter Da, SQLCONNECTION MyConnection, DataSet DS)
{
MyConnection.open ();
Sqltransaction myTrans = myconnection.begintransaction (); mycommand.transaction = myTrans;
Try
{
Da.UPDATE (DS);
Mycommand.transaction.commit ();
Console.writeline ("Update Successful.");
}
Catch (Exception E)
{
Try
{
MyTrans.rollback ();
}
Catch (SQLException EX)
{
IF (MyTrans.Connection! = NULL)
{
Console.writeLine ("An Exception of Type" EX.GETTYPE ()
"Was Encountered While Attempting to Roll Back the Transaction.");
}
}
Console.writeline (E.TOString ());
Console.writeline ("Update Failed.");
}
MyConnection.Close ();
}
Always close Connection and DataReader
After completing the use of the Connection or DataReader object, they always turn off them. Although garbage recycling will eventually remove objects and thus release connection and other managed resources, garbage collection is only required when needed. Therefore, ensuring that any valuable resources are explicitly released remaining your responsibility. Also, Connections without explicitly closing may not return into the pool. For example, an overwhelming range is not explicitly closed, and it will be returned to the connection pool only when the pool size is maximized and the connection is still valid.
Note Do not call Close or Dispose to call CONNECTION, DATAREADER, or any other managed object in the finalize method of the class. Finally, only the non-hosting resources they own directly. If the class does not have any non-managed resources, do not include the Finalize method in the class definition.
Use "using" statement in C #
For C # programmers, make sure that a convenient way to always turn off the Connection and DataReader objects is to use the USING statement. When you leave your own scope, you will automatically call the Dispose of the object being "used". E.g:
// C #
String connString = "Data Source = localhost; integrated security = sspi; initial catalog = northwind;"
Using (SqlConnection Conn = New SqlConnection (ConnString))
{
Sqlcommand cmd = conn.createCommand ();
cmd.comMandtext = "Select Customerid, CompanyName from Customers";
Cn.open ();
Using (SqlDataReader DR = cmd.executeReader ())
{
While (Dr.Read ())
Console.writeline ("{0} / t {1}", Dr.getstring (0), Dr.getstring (1));
}
}
The USING statement cannot be used in Microsoft Visual Basic 庐 .NET.
Avoid accessing the OLEDBConnection.State property If the connection has been opened, the OLEDBConnection.State property will perform the local OLE DB call IDBProperties.getProperties to the DataSourceInfo attribute of the DBProp_ConnectionStatus property, which may result in a round trip to the data source. That is to say, check the consideration of the State property may be high. So check the State property only when you need it. If you need to check this property, monitor the StateChange event of OLEDBConnection may make the application's performance better. For more information on the STATECHANGE event, see Working With Connection Events.
Back to top
Integrated with XML
ADO.NET provides a wide range of XML integration in DataSet and discloses SQL Server 2000 and its higher version of the XML feature. You can also use SQLXML 3.0 to access XML features in SQL Server 2000 and its higher versions. Here is the skills and information using XML and ADO.NET.
Dataset and XML
DataSet is closely integrated with XML and provides the following features:
• Load a DataSet architecture or relational structure from the XSD architecture. • Load the contents of DataSet from XML. • If the architecture is not provided, the DataSet architecture can be inferred from the contents of the XML document. • Write the DataSet architecture into an XSD architecture. • Write the contents of the DataSet into XML. • Synchronous access to the relationship representation of data using DataSet, as well as hierarchical representation using XMLDATADOCUMENT.
Note You can use this synchronization to apply an XML function (for example, XPath query and xslt conversion) to data in the DataSet, or provide relationships for all or one of the data in the XML document under the premise of retaining the original XML fidelity. view.
For more information on the XML functionality provided by DataSet, see XML and the dataset.
Architecture
When loading a DataSet from an XML file, you can load the DataSet architecture from the XSD schema or pre-defined tables and columns before loading data. If there is no available XSD architecture, and do not know which tables and columns defined for the contents of the XML file, the architecture can be inferred on the basis of the XML document structure.
Architecture Inference is useful as a migration tool, but it should be limited to design phase applications, which is due to the following restrictions.
• Inferences for architectures, introduce additional processing that affects application performance. • The type of all inference columns is a string. • Inference processing does not have certain determinism. That is, it is based on the XML file content, not a predetermined architecture. Therefore, for the same XML files as two predetermined architectures, since their content is different, the results are obtained from two completely different inference architectures.
For more information, see Inferring Dataset Relational Structure from XML.
SQL Server for XML query
If you are back from SQL Server 2000 for XML, you can create an XMLReader using the SQL Server .NET Framework Data Provider using the SQLCommand.executexmlReader method.
SQLXML hosted class
There are some classes in the .NET framework, which is publicly used for XML of SQL Server 2000. These classes can be found in the Microsoft.Data.sqlxml namespace that adds the XPath query and the XML template file and the ability to convert XSLT to the data. The SQLXML hosted class is included in the XML (SQLXML 2.0) release for Microsoft SQL Server 2000, can be from XML for Microsoft SQL Server 2000 Web Release 2 (SQLXML 2.0) ?? μ?
Back to top
More useful skills
Here is a common technique for writing ADO.NET code.
Avoid automatic incremental conflicts
Just like most data sources, DataSet allows you to identify columns that automatically increase their values when adding new rows. When using the auto-incrementum column in DataSet, if the column of the automatic increment is from the data source, it is possible to avoid the row of DataSet and the rows added to the data source between the local number conflict.
For example, consider a table, its primary key column CustomerID is automatic increment. Two new customer information rows are added to the table and receive automatic incremental Customerid values 1 and 2. Then, only the second customer line is passed to the method of DataAdapter, the newly added row receives an automated incremental Customerid value 1 in the data source, and does not match the value 2 in the DataSet. When DataAdapter fills the second line in the table with the return value, a constraint conflict occurs because the first customer row has already used the CustomerID value 1.
To avoid this, it is recommended to create the columns in the DataSet on the list of automatic increment on the DataSet, and the autocrementseed value is equal to -1 and the autocrementseed value is equal to 0, and the autoincrementseed value is equal to 0, in addition, to ensure The automatic incremental identifier value generated by the data source starts from 1 and increments in a positive order. Therefore, DataSet is a negative number of automatic incremental values, and the positive automatic incremental value generated by the data source does not conflict. Another option is a column using the GUID type, not a column of an automatic increment. The algorithm that generates the GUID value should never cause the GUID value generated in the data source to be the same as the GUID value generated in the DataSet.
If the column of the automatic increment is just used as a unique value, and there is no meaning, consider using the GUID instead of the automatic increment. They are unique and avoid the additional work necessary to use automatic incremental columns.
For examples of column values for automatic increment from the data source, see RetrieVing Identity or Autonumber Values.
Check open concurrent conflict
According to the design, since DataSet is open to the data source, it is necessary to ensure that applications avoid conflicts when multiple clients update data in the data source.
There are several techniques when testing an open concurrent conflict. A technique involves containing timestamp columns in the table. Another technique is to verify that the original value of all columns in a row is still matched to the value found in the database when tested by using the WHERE clause in the SQL statement.
For a detailed discussion on this topic that contains code examples, see Optimistic Concurrency.
Multi-threaded programming
ADO.NET is optimized for performance, throughput and scalability. Therefore, the ADO.NET object does not lock the resource and must only be used only for single threads. One exception is DataSet, which is a thread for multiple readers. However, you need to lock the DataSet when you write.
Just use COM Interop to access ADO only when you need it.
ADO.NET design is to be the best solution for many applications. However, some applications need only features that use ADO objects, for example, ADOMD. In these cases, the application can access ADO with COM Interop. Note that the use of the COM INTEROP to access data with ADO will result in performance reduction. When designing an application, first determine whether ADO.NET meets design requirements before implementing the design of the COM Interop Access ADO. The information contained in this document represents the current view of the discussion problem on the issuance of Microsoft Corporation on the issuance of Microsoft Corporation. Since Microsoft must respond to changing market conditions, this document should not be considered as a commitment to Microsoft, and Microsoft does not guarantee the accuracy of any information provided later after the date will be issued.
This white paper is only used for information purposes. Microsoft provides information provided by this document does not make any express, implied or decrees.
Compliance with all applicable copyright law is the user's responsibility. In the case where the rights specified in the copyright law, no part of this document is copied, stored, or introduced into the retrieval system without the written writing of Microsoft Corporation, or in any form or means (electronic, machinery, Capacin, recording or other), or for any purpose, disseminate any part of this article.
Microsoft has patent rights, patent application rights, trademark rights, copyright or other intellectual property rights to this document. Unless Microsoft is clearly provided in any written license agreement, this document does not give you any license for you to use these patents, trademarks, copyrights, or other intellectual property rights.
Leak 2002 Microsoft Corporation. All rights reserved.
Microsoft, Visual Basic, Visual Studio is a registered trademark or trademark of Microsoft Corporation in the US and / or other countries.
The names of the real companies and products mentioned herein may be trademarks belonging to their respective owners.
Transfer to the original English page
