Review Questions

1.	Which of the following is the appropriate connection string for logging onto a Microsoft SQL Server 6.5 database? myConn.ConnectionString = _ "Provider=MSSQL; Data Source=(local); " & _ "Initial Catalog=pubs" & _ "User ID=guest; Password=p5n7u!N" myConn.ConnectionString = _ "Data Source=(local); Initial Catalog=pubs" & _ "User ID=guest; Password=p5n7u!N" myConn.ConnectionString = _ "Provider=MSSQL; Data Source=pubs; " & _ "Initial Catalog=(local) " & _ "User ID=guest; Password=p5n7u!N" myConn.ConnectionString = _ "Provider=MSSQL; Data Source=pubs; " & _ "Initial Catalog=(local) " & _ "User ID=guest; Password=p5n7u!N"
2.	Your Windows forms application uses Windows Integrated Security and allows users of your application to connect to the SQL Server database by using their own Windows username. Users sometimes report that database operations are very slow. What action might improve data access time? Change the SQL Server security mode to mixed mode. Allow your application to log in as the system administrator. Create a single application login so that a single connection pool can serve all users of your application. Rewrite your application’s SQL queries.
3.	Your application will be using ADO.NET Command objects to call stored procedures. Which Command property settings should you use? Set the CommandType property to StoredProcedure and the CommandText property to the name of the procedure. Set the CommandText property to StoredProcedure and the CommandType property to the name of the procedure. Set the CommandType property to Database and the CommandText property to StoredProcedure. Set the CommandType property to StoredProcedure and the CommandText property to the value of the input parameter.
4.	You are using an ADO.NET Command object to run a SQL query that requests a count of rows in a database table. Which command method should you use? ExecuteNonQuery ExecuteReader ExecuteXMLReader ExecuteScalar
5.	You are using an ADO.NET Command object to run a SQL query that will delete a row in the database. Which command method should you use? ExecuteNonQuery ExecuteReader ExecuteXMLReader ExecuteScalar
6.	You have created a DataReader object to read customer information from the database. What instruction should you use to retrieve the customer’s name from the first column in a DataReader’s resultset? myString = myReader.GetChars(0) myString = myReader.GetChars(1) myString = myReader.GetString(0) myString = myReader.GetString(1)
7.	In order to read all the rows from a DataReader, which method should you call? myReader.NextResult() myReader.MoveNext() myReader.Read() myReader.GetValues()
8.	You need to be sure that the database connection is closed immediately when its associated DataReader object is closed by the consumer. How can you most easily accomplish this? With the DataReader.Dispose method. With the DataReader.Close method. When the DataReader is created by the Command.ExecuteReader method, pass a parameter called CommandBehavior.SequentialAccess. When the DataReader is created by the Command.ExecuteReader method, pass a parameter called CommandBehavior.CloseConnection.
9.	When creating an ASP.NET web application, how can you quickly display information from a DataReader, called myReader, in a Web Forms DataGrid control? Set the DataSource property of the DataGrid to reference the myReader and then call the DataGrid.DataBind method. Set the DataReader property of the DataGrid to reference the myReader and then call the DataGrid.DataBind method. Set up a loop to read through the DataReader and assign values to the rows and columns of the DataGrid. Set up special template columns for the DataGrid and then use the GetDataType methods of the DataReader to display each row of data.
10.	You are using an ADO.NET Command object to run a SQL query that will update selected rows in the database, based on the criteria specified in your SQL statement’s WHERE clause. Your call to the ExecuteNonQuery method looks like this: x = myCommand.ExecuteNonQuery() What will the variable x contain after the query is run? -1 True or False, indicating whether or not any errors occurred while processing the data A status code from the data base server The number of rows that were updated
11.	You are using an ADO.NET Command object to run a SQL query that will return a single calculated value. Your call to the ExecuteScalar method looks like this: x = myCommand.ExecuteScalar() What data type should you use when you declare your variable named x? Integer Object Variant Decimal
12.	Your procedure needs to perform two separate database queries. You need to debit an amount in the first database table and credit that amount in another table. You want to make sure that both operations are successful. If one of the instructions fails, no partial changes should be written to the database. Which ADO.NET objects should you use? Use the Connection object’s BeginTrans, CommitTrans, and Rollback methods. Instantiate a new Transaction object and call its methods to commit or roll back the transaction. Use the Connection object to create a new Transaction object and then use methods of the Transaction object to commit or roll back the transaction. Create a new Transaction object and add it to the Connection.Transactions collection.
13.	You are using the ADO.NET Transaction object to coordinate database operations in your code. You would like to make a setting indicating to the database server that you would like the highest level of database locking to be applied while your transaction is running. Which value should you use for the Transaction.IsolationLevel property? ReadCommitted Serializable ReadUncommitted RepeatableRead
14.	You are creating error handling for your ADO.NET application that will use the SqlClient data provider. You are interested in processing only data access errors with this Catch block. How should you specify the Catch block portion of your error handler? Catch ex As Exception Catch ex As SqlException Catch ex As SqlError Catch ex as SqlException.Errors
15.	Code in your error handler does not access the SqlException.Errors collection to read error messages, but rather reads the Message property directly from the SqlException object. What effect does this have on your application? You will not see any error messages. You will the same message as the first SqlError object in the Errors collection. You will see a message warning you to read the Errors collection. You will see a generic message.

Answers

1.	A Version 6.5 and older of Microsoft SQL Server must use the OleDb data providers; therefore, they need to specify a provider name in the connection string. The Data Source should be set to the computer name of the database server (or local machine), and the Initial Catalog is the name of the database.
2.	C When users log into the database with unique login names, connection pooling cannot work efficiently because each user will get their own connection pool. Changing to a single application login will enable users to get existing connections from the pool, which is quicker than creating new connections for each user. Changing SQL Server to mixed mode will not improve performance and will introduce new security considerations, as will allowing your application to log in as an administrator. Rewriting your SQL queries might or might not have any effect on application performance.
3.	A The CommandType property indicates what kind of operation the command will be performing. There are three valid values: Text (a SQL statement provided in your code), StoredProcedure, or TableDirect. The CommandText property is a string value that is a SQL statement provided in your code, the name of a stored procedure, or the name of a table. Parameter values are handled by the Command.Parameters collection.
4.	D Use the ExecuteScalar method when running a query that returns a single value. Use the ExecuteReader method when running a query that returns rows of data. Use the ExecuteNonQuery method when running a query such as a SQL INSERT, UPDATE, or DELETE statement. The ExecuteXMLReader returns an XML document object and is for use only with the SqlClient data provider and SQL Server 2000 FOR XML queries.
5.	A Use the ExecuteNonQuery method when running a query such as a SQL INSERT, UPDATE, or DELETE statement. Use the ExecuteScalar method when running a query that returns a single value. Use the ExecuteReader method when running a query that returns rows of data. The ExecuteXMLReader returns an XML document object and is for use only with the SqlClient data provider and SQL Server 2000 FOR XML queries.
6.	C The GetString method should be used because you know that the field that contains the customer name is defined as a string or character data type. The GetChars method is used to read database columns that hold large binary data objects (BLOB). The first column the in the DataReader’s resultset is at ordinal position zero (0), not 1.
7.	C The Read method is used to advance the DataReader to the next row of data. The NextResult method is used when several SQL queries were run as a batch and there are multiple resultsets in a single DataReader. The MoveNext method was used with older versions of the ADO recordset and is not used in ADO.NET. The GetValues method is for retrieving column data.
8.	D Although you can write code to create this behavior, it is most easily accomplished by simply setting the CommandBehavior.CloseConnection parameter when creating the DataReader.
9.	A ASP.NET Web Forms controls are able to use automatic data binding to access data through a DataReader. Windows Forms controls cannot do this. Just set the DataSource property of the DataGrid to reference the DataReader instance and call the DataGrid.DataBind method. You do not need to loop through the rows in the DataReader or write code to work with individual column values.
10.	D The ExecuteNonQuery method returns an integer value showing the number of records that were affected by the query. When working with a DataReader, the RecordsAffected property always returns -1 for SQL SELECT statements. Error information and status codes are accessed through the Exception and Error objects.
11.	B Because the ExecuteScalar method can return different types of data, it returns an Object data type. You can then write code to convert to a more specific data type. The data type of Variant was used in Visual Basic 6 and is not one of the .NET Framework data types.
12.	C The ADO.NET Transaction object cannot be instantiated with the New keyword. It is created by the Connection.BeginTransaction method. After the object is created, you can call methods of the Transaction object to commit or roll back the transaction. In older versions of ADO, the Connection object was used to control transactions and did have BeginTrans, CommitTrans, and Rollback methods. There is no Connection.Transactions collection.
13.	B Serializable provides the highest level of isolation and ensures that no other operations can change or even read the data until your transaction is committed. The other settings provide lower levels of protection.
14.	B Specify a SqlException object in the Catch block. Inside the Catch block, you can then access the SqlError objects that make up the SqlException.Errors collection. If you specify System.Exception in the Catch block, you will receive all types of runtime errors.
15.	B The SqlException object’s property values will be the same as the first SqlError object in the Errors collection.