This chapter defines basic data objects as runtime data-access types that have ADODB counterparts. ADO.NET 2.0 provides the following basic data objects for data retrieval, updates, or both:
- Connection objects define the data provider, database manager instance, database, security credentials, and other connection-related properties. The VB 2005 code to create a .NET Connection is quite similar to the VB6 code to create an ADODB.Connection object. You also can create a new, persistent (design-time) Connection object by right-clicking Server Explorer's Data Connections node and choosing Add Connection to open the Connection Properties dialog. Alternatively, choose Tools➪Connect to Database to open the dialog.
- Command objects execute SQL batch statements or stored procedures over an open Connection. Command objects can return one or more resultsets, subsets of a resultset, a single row, a single scalar value, an XmlDataReader object, or the RowsAffected value for table updates. Unlike opening ADODB.Recordset objects from an ADODB.Connection, the ADO.NET Command object isn't optional. Command objects support an optional collection of Parameter objects to execute parameterized queries or stored procedures. The relationship of ADODB and ADO.NET parameters to commands is identical.
- DataReader objects retrieve one or more forward-only, read-only resultsets by executing SQL batch statements or stored procedures. VB .NET code for creating and executing a DataReader from a Command object on a Connection object is similar to that for creating the default, cursorless ADODB Recordset object from an ADODB.Command object. Unlike the default forward-only ADODB.Recordset, you can't save a DataReader's resultset to a local file and reopen it with a client-side cursor by Save and Open methods.
- XmlReader objects consume streams that contain well-formed XML documents, such as those produced by SQL Server FOR XML AUTO queries or stored procedures, or native xml columns of SQL Server 2005. XmlReaders are the equivalent of a read-only, forward-only cursor over the XML document. An XmlReader object corresponds to the ADODB.Stream object returned by the SQLXML 3.0 and later SQLXMLOLEDB provider.
SqlClient doesn't support bidirectional (navigable) cursors. Microsoft added an SqlResultset object, which emulated an updatable server-side cursor, to an early VS 2005 beta version. The VS 2005 team quickly removed the SqlResultset object after concluding that it encouraged "bad programming habits," such as holding a connection open during data editing operations. An ExecutePageReader method, which relied on the SqlResultset object, was removed at the same time and for the same reason.
Figure 1-2 illustrates the relationships between ADO.NET Connection, Command, Parameter, DataReader, and XmlReader objects. Parameters are optional for ADODB and basic ADO.NET commands. The SqlClient types can be replaced by OleDb or Odbc types. Using the OleDb provider to return an XmlDataReader object from SQL Server 2000 requires installing SQLXML 3.0 SP-2 or later; the Odbc provider doesn't support XMLReaders. SQL Server 2005's setup program installs SQLXML 4.0.
The above tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.
This was first published in May 2006