In this, the final part of her two part series on ADO.NET, Vaijayantee talks about the purpose of ADO.NET, scalability, XML, the ADOCommand and SQLCommand objects and more. If you're just new to .NET then Vaijayantee's ADO.NET tutorial series will have you up and running with databases in a short time.
All You Need To Know About ADO.NET: Part 2/2 (Page 1 of 6 )
A command object is used for executing a query against the database. ADO command objects are equivalent to DataAdapters that hold the actual query. It needs an established connection, it's command type and commandtext properties to be set before calling its Execute method.
The SQLCommand object is analogous to the command object in ADO. We also need a connection for execution. The DataAdapter supports read, add, update and delete operations and for these it supports following properties:
Actually, these properties are objects. To be mores specific, these are instances of the SQLCommand object. Unless otherwise specified, the appropriate objects are generated at runtime automatically. The SelectCommand can be edited at design time while configuring the DataAdapter. The command objects can be executed independently by using their execute methods. Important properties of the object are CommandType and CommandText. If the CommandType property is set to StoredProcedure, then CommandText property should be set to the name of the stored procedure, otherwise the CommandText property contains the actual query.
To see a command working, let's use following piece of code:
Dim sqlSttg As String = "SELECT * FROM Publishers" Dim cmd As New OleDBCommand (cmd.CommandText = sqlSttg cmd.ActiveConnection = Conn
[Note] This code assumes that you have a connection to data (Conn open as discussed in the previous example). [End Note]
ADO.NET's Connected Architecture ADO.NET has been designed fundamentally to be a disconnected and distributed data access technology based on XML, so ADO is still a better approach if you need a continuous connection to the underlying database, although it does requires COM Interop. The DataReader object of ADO.NET comes closest to the RecordSet object of ADO in that it also depends upon a connection to read the data.
The DataReader Object Sometimes an application is only required to read data and not to update or write the data. Especially if such applications need large amount of data, then it is wiser to do away with the DataSet object because of its memory overheads. The DataReader requires very little memory because it just reads the data as its name suggests. It reads only one record at a time, and for applications using huge read only data, the DataReader is an excellent alternative because it is a read only and forward only stream that cuts down significantly on memory requirements.
After creating an instance of the Command object, a DataReader can be created by calling Command.ExecuteReader to retrieve rows from a data source. Assuming a connection as in the previous example is set up, the following code illustrates how to loop through a DataReader:
Dim dReader As OleDbDataReader Set dReader = Nothing dReader = cmd.ExecuteReader( Do While dReader.Read MsgBox(myReader.GetString(1)) End While
The code above loops through the publishers table in the pubs database and displays the pub_id value for all of the publishers.
The Read method of the DataReader object is used to obtain a row from the results of the query. Columns can be accessed by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on).
Performance considerations The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory. There's only row row of data in memory at any time, which thereby reduces the memory requirements. It is obvious that if the application requires sequential, read only data, then the DataReader object will be the best choice from a performance point of view.
We should keep in mind that while the DataReader is open, the connection is used exclusively by it. This may hamper the performance of your applications. To avoid this, the DataReader object should be closed explicitly by using its close method as soon as its job is done.
Comparison to the Fire-Hose Cursor A cursor is a database element used by ADO to control the record navigation, ability to update data and visibility of changes made to the data by other users. ADO.NET does not have an inherent cursor object, but has data classes that include traditional functionality of the cursors. A forward only and read only cursor is commonly known as a Fire-hose cursor, and the DataReader object provides the functionality of the forward only and read only cursors.