Exam Essentials

Know how to select a .NET data provider.  The SqlClient data provider is used with Microsoft SQL Server 7 and 2000. Older versions of Microsoft SQL Server must use the OleDb data provider. The OleDb data provider is also used with other types of databases, such as Oracle and Access. Use the ODBC data provider for legacy systems.

Know how to use the ADO.NET Connection object.  Understand how connection pooling works and how to create a connection string. Understand the differences between appropriate values for the SqlConnection.ConnectionString and the OleDbConnection.ConnectionString.

Understand the different methods of the Command object.  Use the ExecuteReader method to create a DataReader to access rows of data returned from the database. Use ExecuteNonQuery to run a SQL UPDATE, INSERT, or DELETE statement, or other type of query that does not return rows of data. ExecuteNonQuery returns the number of records affected by the operation. Use the ExecuteScalar method when your query will return a single value, such as the result of a sum, count, or average calculation. Use the ExecuteXmlReader method with SQL Server 2000 when writing queries that use the FOR XML clause.

Know how to access data with a DataReader.  Understand the Read and Close methods and how to retrieve column data by using the GetDataType methods. Remember that the DataReader provides only forward-only, read-only access to data. The DataReader maintains an open connection to the database while you are accessing its data. Understand the CommandBehavior parameters and how they can be used to optimize your application. Know how to access multiple resultsets with a single DataReader. Remember to close the DataReader as soon as possible after you have used the data and to close the DataReader after each ExecuteReader method if you are using it for multiple operations.

Know how to work with stored procedures.  Use the Visual Studio .NET Server Explorer window to create, edit, and test SQL Server stored procedures. Use the Command object to call stored procedures from your code. Create a Parameters collection that passes input parameters to the stored procedure and can retrieve output parameters and return values after the stored procedure has completed.

Know how to use ADO.NET transactions.  Use the BeginTransaction method to create a Transaction object. Set the Transaction property of the Command object that will participate in the same transaction. Use the Commit and Rollback methods to control transaction outcome. Understand the differences between the IsolationLevel property values.

Know how to use Exception and Error objects.  The Exception object is fired by the .NET data provider if an error occurs at the database. The Exception object has an Errors collection that contains one or more Error objects. Exception and Error objects have properties that enable you to retrieve error information such as error number and messages.