Unveil the mystery of ADO. NET-a summary of the Structure of ADO. NET

ADO. NET provides two main components to access and operate data, they are. NET Framework data provider and DataSet, in order to better design the database program, we need to understand ADO. NET working mechanism .. NET Framework data provider. NET Framework data provider is a component designed specifically for data operations and fast, inbound-only, read-only access to data. The Connection object provides a Connection to the data source. The Command object can be used to access database commands used to return data, modify data, run stored procedures, and send or retrieve parameter information. DataReader provides high-performance data streams from data sources. Finally, DataAdapter serves as a bridge between the DataSet object and the data source. DataAdapter uses the Command object to execute SQL commands in the data source to load data to the DataSet, and coordinates the changes to the data in the DataSet back to the data source. The following code list demonstrates how to use the ADO. NET Technology of SQLServer, ole db, and ODBC to retrieve data from the database SqlClient www.2cto.com. Assume that you can connect to the Northwind Sample Database of Microsoft SQL Server. The Code creates a SqlCommand to select rows from the Products table and add SqlParameter to limit the result to rows whose UnitPrice is greater than the specified parameter value. SqlConnection is enabled in the using block, which ensures that resources are closed and released when the code exits. The sample code uses SqlDataReader to execute the command and displays the result in the console window. [Plain] <span style = "font-family: SimSun; font-size: 18px;"> Option Explicit On Option Strict On Imports System. data Imports System. data. sqlClient Public Class Program Public Shared Sub Main () 'defines the connection String and assigns Dim connectionString As String = "Data Source = (local); Initial Catalog = Northwind; "&" Integrated Security = true "'provides the plug-in String Dim queryString As String =" SELECT ProductID, UnitPrice, Prod UctName from dbo. products "&" WHERE UnitPrice> @ pricePoint "&" order by UnitPrice DESC; "Dim paramValue As Integer = 5' create and open connections in the Using block, make sure that after all resources are used, you can close and release Using connection As New SqlConnection (connectionString) 'to create a connection and property object Dim command As New SqlCommand (queryString, connection) command. parameters. addWithValue ("@ pricePoint", paramValue) 'open a connection in the try/catch Block, create and execute Data Reading, and display the structure to Try connection in the console window. Open () Dim dataReader As SqlDataReader = command. executeReader () Do While dataReader. read () Console. writeLine (vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", dataReader (0), dataReader (1 ), dataReader (2) Loop dataReader. close () Catch ex As Exception Console. writeLine (ex. message) End Try Console. readLine () End Using End Sub End Class </span> OleDb creates an OleDbCommand to select rows from the Products table and add an OleDbParamete R to limit the result to rows whose UnitPrice is greater than the specified parameter value. OleDbConnection is enabled in the using block, which ensures that resources are closed and released when the code exits. The sample code uses OleDbDataReader to execute the command and displays the result in the console window. [Plain] <span style = "font-family: SimSun; font-size: 18px;"> Option Explicit On Option Strict On Imports System. data Imports System. data. oleDb Public Class Program Public Shared Sub Main () Dim connectionString As String = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "&" c: \ Data \ Northwind. mdb; User Id = admin; Password =; "'provides the Dim queryString As String =" SELECT ProductID, UnitPrice, P RoductName from Products "&" WHERE UnitPrice>? "&" Order by UnitPrice DESC; "'specify the parameter value. dim paramValue As Integer = 5' create and open connections in the Using block to ensure that all resources can be closed and released after use As New OleDbConnection (connectionString) dim command As New OleDbCommand (queryString, connection) command. parameters. addWithValue ("@ pricePoint", paramValue) Try connection. open () Dim dataReader As OleDbDataReader = _ command. executeReader () Do While dat AReader. read () Console. writeLine (_ vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", _ dataReader (0), dataReader (1 ), dataReader (2) Loop dataReader. close () Catch ex As Exception Console. writeLine (ex. message) End Try Console. readLine () End Using End Sub End Class </span> Odbc creates an OdbcCommand to select rows from the Products table, add an OdbcParameter to limit the result to rows whose UnitPrice is greater than the specified parameter value. OdbcConnection is enabled in the using block, which ensures that resources are closed and released when the code exits. The sample code uses OdbcDataReader to execute the command and displays the result in the console window. [Plain] <span style = "font-family: SimSun; font-size: 18px;"> Option Explicit On Option Strict On Imports System. data Imports System. data. odbc Public Class Program Public Shared Sub Main () Dim connectionString As String = _ "Driver = {Microsoft Access Driver (*. mdb)}; "_ &" Dbq = c: \ Data \ Northwind. mdb; Uid = Admin; Pwd =; "Dim queryString As String = _" SELECT ProductID, UnitPrice, ProductName fr Om Products "_ &" WHERE UnitPrice>? "_ &" Order by UnitPrice DESC; "Dim paramValue As Integer = 5 Using connection As New OdbcConnection (connectionString) Dim command As New OdbcCommand (queryString, connection) command. parameters. addWithValue ("@ pricePoint", paramValue) Try connection. open () Dim dataReader As OdbcDataReader = _ command. executeReader () Do While dataReader. read () Console. writeLine (_ vbTab & "{0}" & vbTab & "{1}" & vb Tab & "{2}", _ dataReader (0), dataReader (1), dataReader (2) Loop dataReader. close () Catch ex As Exception Console. writeLine (ex. message) End Try Console. readLine () End Using End Sub End Class </span> DataSet is designed for data access independent of any data source. Therefore, it can be used for a variety of different data sources, for XML data, or for managing local data of applications. DataSet contains a set of one or more able objects, which are composed of data rows and data columns and primary keys, foreign keys, constraints, and link information related to data in the DataTable object. Describes the relationship between. NET Framework data providers and DataSet. The following example consists of several methods. These methods are combined to create and fill DataSet from the Northwind database. [Plain] Option Explicit On Option Strict On Imports System. data Imports system. data. sqlClient Public Class NorthwindDataSet Public Shared Sub Main () Dim connectionString As String = _ GetConnectionString () ConnectToData (connectionString) End Sub Private Shared Sub ConnectToData (ByVal connectionString As String) using connection As SqlConnection = New SqlConnection (connectionString) <span style = "fon T-size: 18px; "> <span style =" font-family: SimSun; "> </span> Dim suppliersAdapter As SqlDataAdapter = New SqlDataAdapter () suppliersAdapter. tableMappings. add ("Table", "Suppliers") connection. open () Console. writeLine ("The SqlConnection is open. ") <span style =" font-size: 18px; "> <span style =" font-family: SimSun; "> </span> Dim suppliersCommand As SqlCommand = New SqlCommand (" SELECT SupplierID, CompanyName FROM dbo. suppliers; ", connection) suppliersCommand. commandType = CommandType. text suppliersAdapter. selectCommand = suppliersCommand Dim dataSet As DataSet = New DataSet ("Suppliers") suppliersAdapter. fill (dataSet) Dim productsAdapter As SqlDataAdapter = New SqlDataAdapter () productsAdapter. tableMappings. add ("Table", "Products") Dim productsCommand As SqlCommand = New SqlCommand ("SELECT P RoductID, SupplierID FROM dbo. products; "connection) productsAdapter. selectCommand = productsCommand productsAdapter. fill (dataSet) connection. close () Console. writeLine ("The SqlConnection is closed. ") Dim parentColumn As DataColumn = dataSet. tables ("Suppliers "). columns ("SupplierID") Dim childColumn As DataColumn = dataSet. tables ("Products "). columns ("SupplierID") Dim relation As DataRelation = New <sp An style = "font-size: 18px;"> <span style = "font-family: SimSun;"> </span> System. data. dataRelation ("SuppliersProducts", parentColumn, childColumn) dataSet. relations. add (relation) Console. writeLine ("The {0} DataRelation has been created. ", relation. relationName) End Using End Sub Private Shared Function GetConnectionString () As String Return "Data Source = (local); Initial Catalog = Northwind;" & "Inte Grated Security = SSPI; "<span style =" font-size: 18px; "> <span style =" font-family: SimSun; "> </span> End Function End Class: Select DataReader or DataSet. When deciding whether the application should use DataReader or DataSet, consider the Function types required by the application. You can use DataSet to cache data locally in an application to process the data. If you only need to read the query results, DataReader is a better choice. Remote Data Processing at or from XML Web services. Dynamic Interaction with data, such as binding to a Windows form control or combining and associating data from multiple sources. A large amount of data is processed without the need to maintain an open connection with the data source, so that the connection is released to other clients. If you do not need the functions provided by DataSet, you can use DataReader to return data only in read-only mode, thus improving the application performance. Although DataAdapter uses DataReader to fill in DataSet content, DataReader can improve performance, because it can save the memory used by DataSet and save the processing required to create a DataSet and fill its content.