Summary
This article describes an important object DataReader in ADO.NET, and a simple example illustrates the use of DataReader.
table of Contents
ADO.NET overview
2. How to use DataReader
3. Small knot
ADO.NET overview
Briefly introduce ADO.NET before explaining topics. ADO.NET has two core components: DataSet and .NET DATA Provider, .NET Data Provider is used to connect to the database, execute the SQL command, and retrieve datasets.
.NET Framework includes SQL Server .NET Data Provider (facing Microsoft SQL Server 7.0 and later) and OLE DB .NET DATA Provider, SQL Server .NET Data Provider is the most efficient when connecting and handling SQL Server database, because it uses Special for SQL Server's protocol and optimization processing, all SQL Server .Net Data Provider classes are included in the System.Data.sqlClient namespace. OLE DB .NET DATA Provider is used to support OLE DB interfaces. ADO.NET can provide the following OLE DB Providers: Sqloledb - Microsoft Ole DB Provider for SQL Server; MSDara - Microsoft Ole DB Provider for Oracle; Microsoft.jet.Oledb.4.0 - Ole DB Provider for Microsoft Jet. The System.Data.OleDblient namespace contains all OLE DB .NET Data Provider classes. Therefore, in the program, you want to include the namespace of the Data Provider you want.
The code introduced when using SQL Server .NET Data Provider:
[Visual Basic]
Imports system.data.sqlclient
[C #]
Using system.data.sqlclient;
The code introduced when using OLE DB .NET DATA Provider:
[Visual Basic]
Imports system.data.oledb
[C #]
Using system.data.oledb;
.NET DATA Provider contains 4 main objects: Connection, Command, DataReader, DataAdapter.
The CONNECTION class is used to establish a database connection.
If the provider is SQL Server, you can use the following code.
[Visual Basic]
DIM adoconn as sqlconnection = new sqlconnection ("data source = dbserver; initial catalog = northwind; persist security info = true; user id = sa")
[C #]
SqlConnection Adoconn = New SqlConnection ("Data Source = DBServer; Initial Catalog = Northwind; Persist Security Info = True; User ID = SA");
Otherwise, the provider is OLE DB, you can use the following code.
[Visual Basic]
Dim adoconn as oledbconnection = new oledbconnection ("provider = sqloledb; data source = localhost; integrated security = sspi; initial catalog = northwind") [C #]
OLEDBConnection Adoconn = New OLEDBCONNECTION ("provider = SQLOLEDB; DATA SOURCE = localhost; integrated security = sspi; initial catalog = northwind");
Command class is used to execute the SQL command or stored procedure.
DataReader class is used to retrieve a data stream that is only forward-moving from the database, depending on the .NET Data Provider, DataReader is divided into SqlDataReader Class and OLEDBDataReader Class.
SqlDataReader
[Visual Basic]
DIM SQLMYREADER AS SQLDATAREADER = cmdmycommand.executeReader ()
[C #]
SqlDataReader SqlmyReader = cmdmyCommand.executeReader ();
OLEDBDataReader
[Visual Basic]
DIM OlemyReader as OledbDataReader = cmdmycommand.executeReader ()
[C #]
OLEDBDATAREADER OLEMYREADER = cmdmycommand.executeReader ();
DataAdapter class is used to populate DataSet and update the data source.
2. How to use DataReader
DataReader is part I want to focus on, because using DataReader can improve the performance of the application, which is an important means to indicate data to a representation. Data access relationship based on DataReader can be represented by the following figure
After creating a Command object, create a DataReader object by calling the Command.executeReader method, you can use the read method to retrieve the recordset from the data source. Finally, it is necessary to focus on the DataReader object that should explicit after using DataReader. The following code is a simple data query from the Russen database using SqlDataReader.
Imports system
Imports system.data
Imports system.data.sqlclient
Imports Microsoft.visualBasic
Public Class DataReadersample
Public Shared Sub Main ()
DIM SQLCONN AS SQLCONNECTION = New SqlConnection ("Data Source = dbserver; initial catalog = northwind; persist security info = true; user id = sa")
DIM SQLCMD as Sqlcommand = Sqlconn.createCommand ()
Sqlcmd.commandtext = "SELECT EMPLOYEID, Lastname, Firstname from EMPLOYEES"
Sqlconn.open ()
DIM SQLReader As SqldataReader = Sqlcmd.executeReader () DIM INTFOR AS INT32
For intfor = 0 to Sqlreader.fieldcount - 1
Console.write (String.Format ("{0,-20: g}", SQLReader.getname (intFor)))))
NEXT
Console.writeLine ()
Do while sqlreader.read ()
Console.Writeline (String.format ("{0, -20: g} {1, -20: g} {2, -20: g}", SQLReader.GetInt32 (0), SQLReader.getstring (1), SQLReader .Getstring (2))))))))))))
Loop
SQLReader.close ()
Sqlconn.close ()
Console.read ()
End Sub
END CLASS
Chart 1. SQLReader execution search results
The same can also make the Command object execute the SQL batch to retrieve multiple record sets. At this point, the NEXTRESULT method to call DataReader reads the data generated by the next SQL batch.
Imports system
Imports system.data
Imports system.data.sqlclient
Imports Microsoft.visualBasic
Public Class DataReadersample
Public Shared Sub Main ()
DIM SQLCONN AS SQLCONNECTION = New SqlConnection ("Data Source = dbserver; initial catalog = northwind; persist security info = true; user id = sa")
DIM SQLCMD as Sqlcommand = Sqlconn.createCommand ()
Sqlcmd.commandtext = "SELECT EMPLOYEID, LastName, Firstname from Employees; Select CategoryId, CategoryName, Description from Categories"
Sqlconn.open ()
DIM SQLReader As SqldataReader = Sqlcmd.executeReader ()
DIM INTFOR AS INT32
DIM BLNNEXTRESULT As Boolean = FALSE
Do While Not BlnnextResult
For intfor = 0 to Sqlreader.fieldcount - 1
Console.write (String.Format ("{0,-20: g}", SQLReader.getname (intFor)))))
NEXT
Console.writeLine ()
Do while sqlreader.read ()
Console.Writeline (String.format ("{0, -20: g} {1, -20: g} {2, -20: g}", SQLReader.GetInt32 (0), SQLReader.getstring (1), SQLReader .Getstring (2))))))))))))
Loop
Console.writeLine ()
BLNNEXTRESULT = NOT SQLReader.NextResult ()
Loop
SQLReader.close ()
Sqlconn.close ()
Console.read ()
End Sub
END CLASS
Figure 2. SQLReader's SQL batch execution search results
3. Small knot
This text is an initiator, I want to make love .Net's entry people will have a general understanding of ADO.NET and DataReader, and successfully prepare .NET code.
