Guide: This article mainly introduces the basic characteristics of ADO.NET, and some of the code used to display how to establish a database connection in ADO.NET, send query commands and quickly browse the DataReader object.
Translation finishing: .NET technology network (www.51dotnet.com) 飞 small lion
English version:
http://www.itpeople.com.cn/tech/tech-news.asp?fatherid=137&newsid=31148&clasStype=152
ADO.NET is Microsoft's next-generation products for Microsoft ActiveX Data Objects (ADO), which creates a Distributed and Data Shared Application Application Development Interface (API) in Microsoft .NET.
ADO.NET can be used in any user's application, requires data source connection and communication with OLE DB-Compliant, such as Microsoft SQL Server.
At the same time, ADO.NET maintains some main concepts related to the previous ADO model, which has been greatly improved, and provides a channel from different information sources to obtain structured data - one platform text file, from The database management system is obtained, or the classified XML data - however, all are performed in accordance with a compatible, standardized design model.
This article is intended to introduce key features of ADO.NET, focusing on accessing data in relational database management system (RDBMS).
Quick View
SQL Server 7.0 (and updated) and any data source that can be accessed via the OLE DB provider. These are also called managed providers. The data access API of the .NET Framework provides two ways to identify and process two types of data sources: SQL Server 7.0 (and updated) and any data source that can be accessed through the OLE DB provider. SQL (System.Data.sql) library can be directly coupled to SQL Server data, while ADO (System.Data.ado) libraries can be used for any data sources accessible through the OLE DB provider.
SQL Server managed providers use a private protocol called "TabularData Stream" in MS SQL Server 7.0 or later, without using OLE DB, ADO, or ODBC.
ADO.NET managed providers work in these OLE DB providers.
Driver Driver Provider Provider SQLOLEDB SQL OLE DB Provider MSDAORA Oracle OLE DB Provider JOLT Jet OLE DB Provider MSDASQL / SQLServer ODBC SQL Server ODBC Driver via OLE DB for ODBC Provider MSDASQL / Jet ODBC Jet ODBC Driver via OLE DB Provider for ODBC Provider
ADO.NET now does not support MSDasql / Oracle ODBC Driver (Oracle Ole DB Driver for ODBC).
The following sections will introduce the core components of the ADO.NET available to each managed provider.
Connections - Connect and manage database transactions. Commands - commands sent to the database. DataReaders - read flow data directly. DateSets and DateSetCommands - stores and operates data in the memory.
The core ADO.NET feature can basically be summarized as follows:
The Connection object establishes a connection between the web page and between databases. The Commands object issues a command to the database provider, and the result returned in these connections in a stream. The result set can be quickly read with DataReaders, or stored in the DateSets object resident memory, and then allows users to access and operate records in dataset through the DateSetCommands object. Developers can use the Dateset built-in method to process the data set on the basis of the data source. In order to use the managed provider in the .NET frame, you need to include the following namespaces to the .aspx page.
SQL managed provider:
<% @ Import namespace = "system.data.sql"%>
ADO managed provider:
<% @ Import namespace = "system.data.ado"%>
Connections
Microsoft provides two Connection objects in the .NET framework to establish a connection to a specific database: SQLConnection and Adoconnection. The Connection object can be explicitly opened by calling the Open's method on the connection that has been created. The following code snippet demonstrates to create and open the connection with any provider.
SqlConnection
[C #] String connectionString = "server = localhost; uid = sa; pwd =; database = northwind"; SQLConnection myConn = new SQLConnection (connectionString); myConn.Open (); [VB] Dim connectionString As String = _ m connectionString As String = _ "server = localhost; uid = sa; pwd =; database = northwind" DIM myconn as sqlconnection = new sqlConnection (connectionString) MyConn.open
Adoconnection
[C #] String connectionString = "Provider = SQLOLEDB.1; Data Source = localhost; uid = sa; pwd =; Initial Catalog = Northwind;" ADOConnection myConn = new ADOConnection (connectionString); myConn.Open (); [VB] Dim connectionString As String = _ ost; uid = sa; pwd =; Initial Catalog = Northwind; "ADOConnection myConn = new ADOConnection (connectionString); myConn.Open (); [VB] Dim connectionString As String = _" Provider = SQLOLEDB.1 Data Source = localhost; "& _" uid = sa; pwd =; initial catalog = nohwind "DIM myconn as adoconnection = new adoconnection (connectionString) MyConn.Open ()
Commands
After the connection is established, the next step is to do the SQL statement running in the database. The easiest and straightforward approach is to implement through the ADO and SQL command objects. The Command object can give some instructions that the provider how to operate the database information.
A command (Command) can be expressed in a typical SQL statement, including executing a selection query (select Query) to return record sets, execute the action query to update (add, edit, or delete) database record, or create and Modify the table structure of the database. Of course, commands (Command) can also pass parameters and return values.
Commands can be explicitly defined, or call the stored procedure in the database. The next small section code proves how to issue a select command after establishing a connection.
SQLCommand
[C #] String SQLStmt = "SELECT * FROM Customers"; SQLCommand myCommand = new SQLCommand (SQLStmt, myConn); [VB] Dim SQlStmt As String = "SELECT * FROM Customers" Dim myCommand As SQLCommand = New SQLCommand (SQLStmt, myConn)
Adocommand
[C #] String SQLStmt = "SELECT * FROM Customers"; ADOCommand myCommand = new ADOCommand (SQLStmt, myConn); [VB] Dim SQlStmt As String = "SELECT * FROM Customers" Dim myCommand As ADOCommand = New ADOCommand (SQLStmt, myConn)
DataReaders
When you handle large amounts of data, a large amount of memory will lead to performance problems. For example, a connection (Connection) uses a traditional ADO RecordSet object to read the 1000 row database record, you must assign memory to this connection to this connection until the life cycle of this connection is ended. If a 1000 user performs the same operation on the same computer at the same time, the memory is excessive use will become a key issue.
In order to solve these problems, the .NET framework includes a DataReaders object, and this object returns only a read only from the database, only forward data flow. And there is only one record in the current memory.
The DataReader interface supports a variety of data sources, such as relational data and hierarchical data. DataReader can apply to a simple read-only recordset that only needs to return a command running.
The following code snippet explains how to declare the variable to a DataReader object, and also the result of the COMMAND object when the code is executed. When the COMMAND object is called, the Command object must have been created and used as a parameter. Continue the above example:
SqlDataReader
[C #] sqldatarader myreader = null; mycommand.execute (out myreader); [VB] Dim MyReader as SqldataReader = NothingMyCommand.execute (MyReader)
AdodaReader
[C #] adodatareader myreader = null; mycommand.execute (out myreader); [VB] Dim MyReader as adodatareader = NothingMyCommand.execute (MyRead) Next step is a simple format using DataReader
[C #] while (myReader.read ()) {[c #] while (myreader.read ()) {// do your thing with the current row here} [vb] while myreader.read 'do your excreting with the current rot Herend While
The following example shows the content we have discussed so far: establish a connection to the SQL data source, to save the select command for the connection, save the return result, and then obtain data by loop DataReader.
Here is a complete code written with C #.
<% @ Import namespace = "system.data"%> <% @ import namespace = "system.data.sql"%>
