Programming with Microsoft Visual C++.NET 6ed [Electronic resources] نسخه متنی

Working with the Providers

Let's take a look at what it takes to connect to a database and execute some code using ADO.NET and managed C++. The general operating mode for ADO.NET is to connect to a database, issue a command, and then examine the results. We'll examine connecting to a database first.

Connecting to the Database

The SqlConnection class and the OleDbConnection class implement IDbConnection, which provides methods for opening a database connection and for starting local transactions programmatically.

You saw in Chapter 31 that garbage collection is nondeterministic, which means you don't know when (or even if) a memory allocation will be collected. For that reason, you should close ADO.NET connections explicitly when they're no longer needed (rather then closing them in a destructor somewhere). IDbConnection::Close is the method for accomplishing this task. You can also call Dispose on the connection object, which will also close the connection. Usually the Open and Close (or Dispose) calls live within a try/catch block.

The managed providers support modern database features such as connection pooling. The OLEDB data provider uses OLEDB's built-in connection pooling, which works through the classic COM+ dispenser manager. The SQL Server data provider uses an internal pooling architecture that behaves similarly to COM+ services' object-pooling feature.

The following listing shows a simple console program that connects to a database and issues a simple selection command. This example assumes that SQL Server is installed on the machine and that a database named CompanyDB is available. The example also assumes that there's a table named Employees containing the Name and DeptID columns within the database.

// This is the main project file for VC++ application project 
// generated using an Application Wizard.
#include "stdafx.h"
#using <mscorlib.dll>
#using <system.dll>
#using <system.data.dll>
#include <tchar.h>
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::ComponentModel;
// This is the entry point for this application
int _tmain(void)
{
// Create a connection object
SqlConnection* conn;
conn = new SqlConnection
(S"server=localhost;uid=sa;pwd=;database=CompanyDB");
SqlCommand* command;
command = new SqlCommand("select * from Employees", conn);
IDataReader* rdr;
try 
{
conn->Open();
rdr = command->ExecuteReader();
while(rdr->Read())
{
Console::Write("Name: ");
Console::Write(rdr->get_Item("Name"));
Console::Write("Dept: ");
Console::WriteLine(rdr->get_Item("DeptID"));
}
}
catch (Exception* e) 
{
System::Console::WriteLine(e->ToString());
}
__finally
{
conn->Dispose();
}
return 0;
}

Notice that the code creates an instance of the SqlConnection class and an instance of the SqlCommand class. Also notice that the code initializes the instance of the SqlCommand class with the connection object and a simple selection statement.

As long as the connection object and the command object work correctly, you can create a data reader by calling SqlCommand::ExecuteReader. The data reader reads through each row. You can use the data reader's get_Item method to find a value within a specific column for that row. (We'll discuss data readers in detail later in this chapter.)

The connection is opened and the command is executed within a try block to catch any errors. Errors that can occur include:

Invalid connection string (wrong account or password or a bad database name)





Wrong table name





Wrong column names

If any errors occur during the process of connecting to the database and executing the query, ADO.NET will throw an exception. In the previous listing, the exception information is simply printed to the screen.

Issuing Commands

Connecting to a database and issuing a simple query is pretty straightforward. However, in real-world situations, collecting data is often much more involved. The command classes (SqlCommand and OleDbCommand) included with the managed providers are fairly sophisticated. You can use them to submit any valid SQL action statement or query to the database (or you can use another command language supported by an OLEDB provider).

SqlCommand and OleDbCommand both implement the IDbCommand interface. You might specify the command when you construct the command object, or you might set the command by using the CommandText property. The command object is associated with a specific connection object (as shown in the previous listing). The following listing shows how to initialize an SqlCommand from an existing connection. Notice that this approach emphasizes programming to the interfaces rather than to the actual classes.

IDbConnection* conn = dynamic_cast<IDbConnection*>(new SqlConnection(
"server=localhost;uid=sa;pwd=;database=CompanyDB"));    
IDbCommand* cmd2 = new SqlCommand(
"select * from Depts", 
dynamic_cast<SqlConnection*>(conn));

You saw earlier that IDbCommand::ExecuteReader submits the command and retrieves results through a DataReader class. IDbCommand also includes a method named ExecuteNonQuery that returns only the number of rows affected.

When applying commands to a command object, an application can set the CommandText property to a SQL command (or perhaps some other command language supported by an OLEDB database) or a stored procedure name. The CommandType property indicates the meaning of CommandText— whether it's a plain-text command or a stored procedure.

Using Stored Procedures with a Command

You can use a command object to call stored procedures to perform database manipulation. SqlCommand and OleDbComnand implement IDbCommand and support parameterized statements through the Parameters property. The SqlParameter and the OleDbParameter classes encapsulate the parameter functionality necessary for stored procedures. Both the OleDbCommand and the SqlCommand include collections of parameters—OleDbParameterCollection and SqlParameterCollection, respectively. The following listing shows how to open an SqlConnection and associate it with an SqlCommand that executes a parameterized stored procedure. This listing expects a database named CompanyDB to be available, which includes a stored procedure named getByDeptID that takes a single string parameter (designating the department ID).

void RunStoredProc() 
{
// Execute a stored procedure
SqlConnection* conn = new SqlConnection(
"server=localhost;uid=sa;pwd=;database=CompanyDB");
SqlCommand* cmd = new SqlCommand("getByDeptID", conn);    
IDataReader* reader;
try 
{
conn->Open();
cmd->Parameters->Add(
new SqlParameter("@dept_id", SqlDbType::VarChar, 11));
cmd->Parameters->get_Item("@dept_id")->Value = S"Engineering";
cmd->CommandType = CommandType::StoredProcedure;
reader = cmd->ExecuteReader();
// Use the reader to examine result set
}
catch(Exception* e)
{
Console::WriteLine(e->ToString());
}
__finally
{
conn->Dispose();
}
}

As you set up parameters, you can designate the direction of each parameter to be input, output, inout, or return. Of course, the parameters you set up programmatically will need to match the format of the parameters in the actual stored procedure (although strict type checking for the parameters isn't enforced).

Using Data Readers to Retrieve Data

You can use data readers to retrieve read-only, forward-only data streams. As mentioned, after you create a command object, you can call IDbCommand::ExecuteReader to create a data reader for retrieving rows from a data source. Most SQL commands and stored procedures produce rectangular results. For example, when you issue Select * from Employees, you're asking to see everything within the Employees table. The result set you get back is a collection of homogenous rows that you can examine with an implementation of IDataReader. Columns within rows can contain only primitive data types, in accordance with the relational model.

Earlier, you saw an example of using a data reader to parse through a result set. The example got only a single result set back. The IDataReader implementations in ADO.NET (SqlDataReader and OleDbDataReader) provide forward-reading access to the result set. As you read through the result set using the reader, the reader always looks at the current row. Each time you call Read on a reader, you get a new row. Read returns false when there are no more rows to be read. The data readers in ADO.NET support multiple result sets. IDataReader::NextResult advances the data reader to the next result set. Here's an example of getting multiple result sets back from a single query:

void MultipleResultSets() 
{
SqlConnection* conn = new SqlConnection(
"server=localhost;uid=sa;pwd=;database=CompanyDB");
SqlCommand* cmd = new SqlCommand(
"select * from Employees;select * from Depts", conn);
IDataReader* rdr;
try
{
conn->Open();
rdr = cmd->ExecuteReader();
bool more = true;    
while (more)
{
while (rdr->Read())
{
Console::Write("Column 0 = ");
// Get the first column
Console::WriteLine(rdr->get_Item(0));
// Get the second column
Console::Write("Column 1 = ");
Console::WriteLine(rdr->get_Item(1));
}
Console::WriteLine("End of result set");
more = rdr->NextResult();
} 
}
catch(Exception* e)
{
e->ToString();
}
__finally
{
conn->Dispose();
}
}

Error Handling

The data access code you've seen so far has been wrapped in try/catch blocks. For simple error handling, this is often sufficient. General errors will be caught by the generic exception class. However, sometimes databases return additional error information or even a collection of errors. To accommodate multiple errors, both SQL Server and OLEDB managed data providers subclass their own exception class, which can return a collection of errors.

The OLEDB data provider's OleDbException exposes an Errors collection that is similar to the ADO's Errors collection (which can be accessed through the OLEDB's IErrorRecord interface). Each error contains an error message, the provider's native error, and an optional SQLState. The OleDbException is derived from ExternalException.

The SQL Server data provider exposes an SqlException derived from SystemException. It encapsulates a SqlErrorCollection collection that exposes a superset of ADO's error information. This includes SQL Server–specific error information. SqlError is created by the SQL Server provider when an error occurs. It contains the SQL Server instance, the error severity, and an optional stored procedure name and line number.

One problem with classic ADO is that severe errors (showstoppers) and SQL warning messages (not showstoppers) are combined in the Errors collection. For example, one of these messages might indicate that a database language has changed. The combination might affect your result set, but it won't stop the query from running. The ADO.NET architecture exposes warnings as events, not as showstopping errors. You can watch the warnings or ignore them.