Overview:
This article has been organized on the basis of Microsoft Site Resources, which is intended to introduce the best solutions to perform and complete performance optimization, stability, and functionality in your ADO.NET application; it is also included in ADO. The best practice for developing existing data objects in NET, and helps you design ADO.NET applications to provide advice.
This article contains the following:
1. Data providers in the .NET framework;
2. Introduce the best uses in comparison DataSet and DataReader;
3. How to use Dataset, Commands, and Connections;
4. Combined XML;
5. If you are an ADO programmer, you may wish to see the differences and contacts of ADO.NET and ADO;
6. In combination with some FAQ, discuss ADO.NET views and skills.
Introduction:
A. Data providers in the .NET framework
Data Providers play a bridge role between the application and the database, which allows you to return query results from a database, execute commands, and update to the dataset.
B. Introduction to several Data Provider
Data in the table below indicates that various Data Provider and the best applicable database objects
provider
description
SQL Server.Net Data Provider
Use the system.data.sqlclient namespace in the .NET framework;
It is recommended to use SQL Server7.0 or later in the intermediate layer application;
It is recommended to use MSDE or SQL Server7.0 or higher in separate applications;
SQL Server 6.5 or earlier, you must use Ole DB Provider for SQL Server in Ole DB.NET Data Provider.
OLE DB.NET DATA Provider
Use the system.data.oledb namespace in the .NET framework;
It is recommended to use SQL Server6.5 or previous versions in the intermediate layer application, or any OLE DB Provider that supports OLE DB interface list in the .NET Framework SDK, the OLE DB interface list will be listed later;
It is recommended to use Access in a separate application, and the intermediate layer application is not recommended;
No longer support OLE DB Provider for ODBC, to access ODBC, using ODBC.NET DATA Provider.
ODBC.NET DATA Provider
Use the system.data.odbc namespace in the .NET framework;
Provide access to a database that uses an ODBC driver connection;
.NET DATA Provider for Oracle
Use the system.data.OracleClient namespace in the .NET framework;
Provide access to the Oracle database.
Custom.Net Data Provider
Provide a set of interfaces that allow you to customize a Data provider;
SQLXML Managed Classes
The latest version of SQLXML3.0 containing SQLXML Managed Classes, allows you to access SQL Server2000 or later version of XML functionality, such as performing an XML template file, perform XPath query and use UpdateGrams or DiffGrams update data; store procedures in SQLXML 3.0 And the XML template will be used as a web service through SOAP.
The OLE DB interface list mentioned in the table lists it here.
OLE DB object
interface
OLE DB ServicesidataInitilize
DataSource
IDbinitializeidbcreateSessionIDbpropertiesiPersistidbinfo *
Session
ISessionPropertiesiopenrowsetIDBSChemarowset * iTransactionLocal * idbcreateCommand *
Command
ICommandtextCommandpropertiesiCommandwithParameters * Iaccessor (ONLY Required if iCommandwithParameters is supported) iCommandPrepare *
MultipleResults
IMultIpleResults
Rowset
IrowsetiaccessoricolumnsInfoicolumnsrowset * irowsetInfo (Only Required if DBTYPE_HCHAPTER is supported)
Row
Irow *
Error
IrrorInfoierRorRecordsiSQLERRORINFO *
C. Connect SQL Server7.0 or higher
Using SQL Server.Net Data Provider Connection SQL Server7.0 or later is the best way to establish a direct connection to SQL Server and there is no need for any technical layer. The following figure shows the technical comparison of various access to SQL Server7.0 or higher:
Figure 1 (Connection to access SQL Server7.0 or higher variety of various technologies)
The following example demonstrates how to create and open a connection to SQL Server7.0 or higher database:
'Visual Basic
Dim nwindconn as sqlconnection = new sqlconnection ("data source = localhost; integrated security = sspi;" & _ "initial catalog = northwind")
nwindconn.open ()
'C #
SqlConnection nwindconn = new SqlConnection ("Data Source = localhost; integrated security = sspi;"
"InTIAL CATALOG = Northwind");
nwindconn.open ();
D. Connect ODBC data source
ODBC.NET DATA Provider, using System.Data.odbc namespace, with the same structure as SQL Server and OLE DB .Net Data Porvider, use ODBC prefix (such as ODBCCONNETION), and standard ODBC connection characters. The following example demonstrates how to create and open a connection to the ODBC data source:
'Visual Basic
DIM NWINDCONN AS ODBCCONNECONNECTION = New OdbcConnection ("driver = {sql server}; server = localhost;" & _ "trusted_connection = yes; database = northwind")
nwindconn.open ()
'C # OdbcConnection nwindconn = new odbcconnection ("driver = {sql server}; server = localhost;"
"Trusted_Connection = YES; Database = Northwind");
nwindconn.open ();
E. Use DataReaders, DataSets, DataAdapters and DataViews
ADO.NET reads data and stores with DataSet and DataReader objects. DataSet is like the direct relatives of the database, with all tables, order, and database constraints (such as intervals). DataReader reads fast, only entering and read-only data streams from the database. With DataSet, you will often use DataAdapter (or CommandBuilder) to deal with your database, and you may use DataView to order and filter data, DataSet also allows you to create a child object inherited to DataSet to express data Table, rows and columns. The following shows the DataSet object model:
Figure 2 (DataSet Object Model)
The following will be introduced for when using DataSet or DataReader, and will also explain how to use DataAdAPter and DataView to optimize access to data.
F. Comparison of DataSet and DataReader
When designing your app, you decide whether to use DataSet or use DataReader, mainly look at the functional level you want to implement in your application.
Use DataSet to do the following in your application:
I. Navigate between multiple discrete results table;
A DataSet can contain multiple result tables, which are discontinuous. You can separate these tables separately, or you can process these tables as a parent child relationship.
II. Operate multiple data sources (such as mixed data obtained from not only one database such as XML files and electronic data sheets);
The following example demonstrates a list of Customers tables from the SQL Server2000's NorthWind database and reads a list of ORDERS tables from Access2000's NorthWind database, and then use DataRelation to establish a correspondence between two tables:
'Visual Basic
DIM CustConn as SqlConnection = New SqlConnection ("Data Source = localhost; integrated security = sspi;" & _
"InTIAL CATALOG = Northwind;")
Dim Custda as SqldataAdapter = New SqldataAdapter ("Select * from Customers", CustConn)
Dim ORDERCONN AS OLEDBCONNECTION = New OLEDBCONNECTION ("provider = microsoft.jet.Oledb.4.0;" & _ "data source = c: / program files / microsoft office /" & _ "office / samples / northwind.mdb;") DIM ORDERDA AS OLEDBDATAADAPTER = New OLEDBDataAdapter ("Select * from Orders", ORDERCONN)
CustConn.open ()
ORDERCONN.Open ()
DIM Custds as dataset = new dataset ()
Custda.Fill (Custds, "Customers")
OrderDa.Fill (Custds, "Orders")
CustConn.close ()
ORDERCONN.CLOSE ()
DIM CustOrderrel As DataRelation = Custds.ReLations.Add ("CustOrDers", _ CustMers "). Column (" Customerid "), _ Custds.Tables (" Orders "). Column (" Customerid)))
Dim Prow, Crow as DataRow
For Each ProW in Custds.Tables ("Customers"). Rows
Console.writeLine ("Customerid"). Tostring ())
For Each Crow in Prow.getChildrows (CustOrderrel)
Console.WriteLine (VBTAB & CROW ("ORDERID"). TOSTRING ())
NEXT
NEXT
'C #
SqlConnection CustConn = New SqlConnection ("Data Source = localhost; integrated security = sspi; initial catalog = northwind;");
Sqldataadapter Custda = New Sqldataadapter ("Select * from Customers", CustConn;
OLEDBConnection OrderConn = New OLEDBCONNECTION ("provider = microsoft.jet.Oledb.4.0;" "Data Source = C: // Program Files // Microsoft Office // Office // Samples // Northwind.mdb;");
OLEDBDATADAPTER ORDERDA = New OLEDBDATAADAPTER ("Select * from Orders", OrderConn; CustConn ();
OrderConn.open ();
DataSet Custds = New DataSet ();
Custda.Fill (Custds, "Customers");
OrderDa.Fill (Custds, "Orders");
CustConn.close ();
ORDERCONN.CLOSE ();
Dataralation CustOrderrel = Custds.ReLases.Add ("CustOrDers", Custds.tables ["Customers"]. Column ["Customerid"], Custds.Tables ["Orders"]. Columns ["CustomerID"]);
Foreach (DataRow Prow In Custds.tables ["Customers"]. ROWS)
{
Console.writeLine (Prow ["Customerid"]);
Foreach (DataRow Crow in Prow.getChildrows (Custorderrel))
Console.writeline ("/ t" crow ["OrderID"]);
}
III. The data is exchanged in the layer or use an XML web service, which is different from DataReader that DataSet can be passed to a remote client;
The following example demonstrates how to create an XML web service, where using getCustomers to take the Customers table data in the database, update the data in the database using the UpdateCustomers:
1. 'Visual Basic
2. <% @ Webservice Language = "VB" class = "Sample"%>
3. Imports System
4. Imports system.data
5. Imports system.data.sqlclient
6. Imports System.Web.Services
7.
8. Public Class Sample
9. Public nwindconn as sqlconnection = new SqlConnection ("data source = localhost; integrated security = sspi; initial catalog = northwind")
10.
11. Public Function getCustomers () AS Dataset
12. Dim Custda as SqldataAdapter = New SqldataAdapter ("Select Customerid, CompanyName from Customers", NWINDCONN) 13. DIM CUSTDS AS DATASET = New Dataset ()
14. Custda.MissingSchemaAction = missingschemaAction.addwithKey
15. Custda.Fill (Custds, "Customers")
16. getCustomers = Custds
17. End Function
18.
19. Public Function UpdateCustomers (Custds As Dataset) AS Dataset
20. DIM Custda as SqldataAdapter = New SqlDataAdapter ()
21. Custda.insertCommand = New SqlCommand ("Insert Into CustomMand (" Customerid, CompanyName "& _" Values (@customerid, @companyname ", nwindconn)
22. Custda.insertCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid")
23. Custda.insertCommand.Parameters.Add ("@ companiesName", sqldbtype.nchar, 15, "companyname")
24. Custda.UpdateCommand = New SqlCommand ("Update Customers Set Customerid = @customerid," & _
25. "CompanyName = @companyname where customerid = @OLDCUSTOMERID", NWINDCONN)
26. Custda.UpdateCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid")
27. Custda.UpdateCommand.Parameters.Add ("@ companiesName", SqldbType.Nchar, 15, "CompanyName")
28. Dim myparm as sqlparameter = Custda.UpdateCommand.Parameters.Add ("@ OldCustomerid", SqldbType.Nchar, 5, "Customerid")
29. MyParm.SourceVersion = DataRowVersion.original
30. custDA.DeleteCommand = New SqlCommand ( "DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn) 31. MyParm = custDA.DeleteCommand.Parameters.Add ( "@ CustomerID", SqlDbType.NChar, 5, "CustomerID")
32. MyParm.SourceVersion = DataRowVersion.original
33. Custda.Update (Custds, "Customers")
34. UpdateCustomers = Custds
35. End Function
36. End Class
37.
38. 'C #
39. <% @ Webservice Language = "C #" class = "Sample"%>
40. USING SYSTEM;
41. USING SYSTEM.DATA;
42. USING SYSTEM.DATA.SQLCLIENT;
43. USING System.Web.Services;
44. [WebSpace (Namespace = "http://microsoft.com/webservices/")]
45. Public Class Sample
46. {
47. Public SqlConnection Nwindconn = New SqlConnection ("Data Source = localhost; integrated security = sspi; initial catalog = northwind");
48. [WebMethod (Description = "Returns Northwind Customers", enablesession = false]
49. Public Dataset getCustomers ()
50. {
51. SqlDataAdapter Custda = New SqldataAdapter ("Select Customerid, CompanyName from Customers", NWINDCONN
52. DataSet Custds = New DataSet ();
53. Custda.MissingSchemaAction = missingschemaAction.addwithkey;
54. Custda.Fill (Custds, "Customers");
55. Return Custds;
56.}
57. [WebMethod (Description = "Updates Northwind Customers", Enablesession = false]
58. Public DataSet UpdateCustomers (DataSet Custds)
59. {
60. SqlDataAdapter Custda = New SqldataAdapter ();
61. custDA.InsertCommand = new SqlCommand ( "INSERT INTO Customers (CustomerID, CompanyName)" "Values (@CustomerID, @CompanyName)", nwindConn);. 62 custDA.InsertCommand.Parameters.Add ( "@ CustomerID", SqlDbType .Nchar, 5, "customerid");
63. Custda.insertCommand.Parameters.Add ("@ companyName", SqldbType.Nchar, 15, "CompanyName");
64. Custda.UpdateCommand = New SqlCommand ("Update Customers Set Customerid = @customerid," "CompanyName = @companyname where customerid = @OLDCUSTOMERID", NWINDCONN);
65. Custda.UpdateCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
66. Custda.UpdateCommand.Parameters.Add ("@ companies", sqldbtype.nchar, 15, "companyname");
67. SQLParameter myparm = Custda.UpdateCommand.Parameters.Add ("@ OldCustomerid", SqldbType.Nchar, 5, "Customerid");
68. myparm.sourceversion = DATAROWVERSION.ORIGINAL;
69. Custda.deleteCommand = New SqlCommand ("Delete from Customers Where Customerid = @customerid", NWINDCONN);
70. myparm = custda.deleteCommand.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
71. myparm.sourceversion = DATAROWVERSION.ORIGINAL;
72. Custda.Update (Custds, "Customers");
73. Return Custds;
74.}
}
IV. Reuse of data (such as sorting, search, or filtering data);
V. Execute the large capacity data processing of each line, process the DataReader hangs no longer need, affect the performance of each line;
VI. Using multiple data such as XML operations such as XSLT conversion or XPath query.
The following example shows how to use XMLDATADOCUMENT Synchronization DataSet data and how to use the XSLT style file in the HTML file in the HTML file, first is xslt style file:
