Connected ADO.NET | Managed C++ and .NET Development: Visual Studio .NET 2003 Edition

As I stated previously, you have two distinct ways of accessing a database using ADO.NET. I cover the one that's easier to visualize and code (at least for me) first: connected access.

With connected access, you are continually connected to the database during the entire time you work with it. Like file access, you open the database, work with it for a while, and then you close it. Also like file I/O, you have the option of buffering data written to the database. This buffered access to the database is better known as transactional database access. I discuss this access method after I cover nontransactional database access.

Using Simple Connected ADO.NET

You'll start with the easiest way of working with database, where the commands you execute happen immediately to the database.

Figure 12-7 shows the basic flow of nontransactional database access.

click to expand
Figure 12-7: Nontransactional database access

Create a link to the database with a SqlConnection.
Open the database with the Open() method.
Create a database command with SqlCommand.

Execute the command by using one of the three methods within SqlCommand (see Table 12-6). The database is immediately updated.

Table 12-6: The Main SqlCommand SQL Statement Execution Methods
METHOD	DESCRIPTION
ExecuteNonQuery	Executes a statement that updates the database.
ExecuteReader	Executes a query to the database that could potentially return multiple rows from a database. This method returns a SqlDataReader object that provides forward-only read access to the retrieved data or result set.
ExecuteScalar	Executes a statement that returns a single value.

Repeat steps 3 and 4 until completed.
Close the database with the Close() method.

Note

If you are using the SQL Server managed provider, use classes prefixed with Sql. On the other hand, when you are using the OLE DB managed provider, use classes starting with OleDb; when you are using the ODBC managed provider, use classes starting with Odbc; and when you are using the Oracle managed provider, use classes starting with Oracle.

Connecting to, Opening, and Closing a Database

With connected nontransactional access to a database, you will always be connecting to, opening, and closing your database. To handle this, you need to work with one of the Connection classes: SqlConnection, OleDbConnection, OdbcConnection, or OracleConnection. Which one of these you use depends on the managed provider you use.

This book uses Microsoft SQL Server, so you'll use the SQL Server managed provider. If you are using the OLE DB, ODBC, or Oracle managed provider, just remember to replace the prefix of every class starting with Sql with OleDb, Odbc, or Oracle and, of course, you will have to change the connection string, but I'll get to that shortly.

Listing 12-5 shows how to connect, open, and close a database in a nontransactional method.

Listing 12-5: Connecting, Opening, and Closing a Database

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; Int32 main(void) {     SqlConnection *connection = new SqlConnection(); #ifdef SQLAuth     //  SQL Server authentication     connection->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     //  Windows Integrated Security     connection->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif     try     {         connection->Open();         Console::WriteLine(S"We got a connection!");     }     catch (SqlException *e)     {         Console::WriteLine(S"No connection the following error occurred: {0}",             e->Message);     }     __finally     {         connection->Close();         Console::WriteLine(S"The connection to the database has been closed");     }     return 0; }

The first thing you do (as with any other .NET application) is import the namespaces needed to access the ADO.NET basic functionality:

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient;

For those of you using a database different than Microsoft SQL Server, use one of the following namespaces instead of System::Data::SqlClient:System::Data::OleDb, System::Data::Odbc, or System::Data::Oracle.

There is nothing special about creating an SqlConnection class. It is just a default constructor:

 SqlConnection *connection = new SqlConnection();

The hardest part of this piece of coding is figuring out what the connection string is. For the SQL Server managed provider, this is fairly easy because it is usually made up of a combination of four out of six clauses:

The location of the server: Data Source=(local);
The name of the database: Initial Catalog=DCV_DB;
Whether security-sensitive information is returned as part of the connection: Persist Security Info=False;
Whether Windows account credentials are used for authentication (this will be False, the default, for SQL Server authentication): Integrated Security=SSPI;
The user ID (not recommended with Windows Integrated Security): User ID=sa;
The user password (not recommended with Windows Integrated Security): Password=; (notice that in this example the password is blank)

It will look like this in the code:

 connection->ConnectionString =     S"User ID=sa; Password=; Data Source=(local); "     S"Initial Catalog=DCV_DB;";

 connection->ConnectionString =     S"Persist Security Info=False;Integrated Security=SSPI;"     S"Data Source=(local); Initial Catalog=DCV_DB;";

The connection string for the Oracle managed provider is very similar to the SQL Server managed provider, whereas the OLE DB and ODBC managed providers always add an additional clause: for OLE DB, the Provider clause, and for ODBC, the Driver clause. For example:

 connection->ConnectionString =     S"Provider=SQLOLEDB;  Data Source=(local); Initial Catalog=DCV_DB; "     S"User ID=sa; Password=;";

and

 connection->ConnectionString =     S"Driver={SQL Server}; Data Source=(local); Initial Catalog=DCV_DB; "     S"User ID=sa; Password=;";

Note

In the preceding code I define two of the more common connection strings I use and use the compile-time directive #ifdef to allow me to choose the one I want. I do this to simplify things. In most cases, it would be better not to hard-code the connection string at all and instead retrieve it from a configuration file.

You open and close the database in virtually the same way as you do a file, except the Open() method doesn't need any parameters:

 connection->Open(); connection->Close();

You need to pay attention to the try statement. ADO.NET commands can abort almost anywhere, so it is always a good thing to enclose your ADO.NET logic within a try clause and capture any exceptions by catching SQLException (OleDbException, OdbcException, or OracleException).

It is also possible for ADO.NET to abort with the database still open. (Probably not in this example, but I felt having the correct code right from the beginning would make things clearer.) Therefore, it is a good idea to place your Close() method within a __finally clause so that it will always be executed.

Figure 12-8 shows the results of the preceding example program. Impressive, no?

click to expand
Figure 12-8: The database is successfully opened and closed.

Querying a Database

All queries made to a connected database are done using the SqlCommand, OleDbCommand, OdbcCommand, or OracleCommand class. As noted previously, the SqlCommand class provides three methods to send SQL commands to the database, with each depending on the type of command. To query the database, you need to use the ExecuteReader() method.

Before you run the ExecuteReader() method, you need to configure SqlCommand by placing the SQL command into it. There are two common ways of doing this. You can either place the SQL command, in text form, into the CommandText property or place the name of stored procedure containing the SQL command into the same property. The default method is the command in text form. If you plan to use a stored procedure, you need to change the CommandType property to CommandType::StoredProcedure.

Listing 12-6 shows both methods. The first command uses a text-formatted command and retrieves the contents of the Authors database for authors with a specified LastName, in this case hard-coded to "Doors". The second command, using a stored procedure, retrieves all Stories view records where LastName equals the value passed to the stored procedure, in this case also "Doors".

Listing 12-6: The "Doors" Stories

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; Int32 main(void) {     String *Name = S"Doors";     SqlConnection *connection = new SqlConnection(); #ifdef SQLAuth     //  SQL Server authentication     connection->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     //  Windows Integrated Security     connection->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif     try     {         SqlCommand *cmd = new SqlCommand();         cmd->Connection = connection;         cmd->CommandType = CommandType::Text;         cmd->CommandText =             String::Format(S"SELECT FirstName, LastName FROM Authors "                             S"WHERE LastName = '{0}'",                             Name);         connection->Open();         SqlDataReader *reader = cmd->ExecuteReader();         while(reader->Read())         {             Console::WriteLine(S"{0} {1}",                 reader->Item[S"FirstName"], reader->Item[S"LastName"]);         }         reader->Close();         // CREATE PROCEDURE dbo.StoriesWhereLastName         //   (         //      @LastName NVARCHAR(32) = NULL         //   )         // AS         //   /* SET NOCOUNT ON */         //   SELECT StoryID, Headline, Story FROM Stories         //   WHERE LastName = @LastName         //         //   RETURN         cmd->CommandType = CommandType::StoredProcedure;         cmd->CommandText = S"StoriesWhereLastName";         cmd->Parameters->Add(new SqlParameter(S"@LastName",SqlDbType::VarChar));         cmd->Parameters->Item[S"@LastName"]->Value = Name;         reader = cmd->ExecuteReader();         Console::WriteLine (S"------------------------");         while(reader->Read())         {             Console::WriteLine(reader->Item[S"StoryID"]);             Console::WriteLine(reader->Item[S"Headline"]);             Console::WriteLine(reader->Item[S"Story"]);             Console::WriteLine();         }         reader->Close();     }     catch (SqlException *e)     {         Console::WriteLine(S"No connection the following error occurred: {0}",             e->Message);     }     __finally     {         connection->Close();     }     return 0; }

Both calls to the ExecuteReader() method after being configured return an instance of SqlDataReader, which is then iterated through to display the retrieved content.

The code to query a database with a CommandType of Text is pretty easy (if you know SQL, that is). First, you set the SqlCommand class's CommandType property to Text:

 cmd->CommandType = CommandType::Text;

Next, you place the SQL command you want to execute in the CommandText property. What makes this process easy is that you can use standard String formatting to build the command, as you see here:

 cmd->CommandText =     String::Format(S"SELECT * FROM Authors WHERE LastName='{0}'",  Name);

Finally, you run the SqlCommand class's ExecuteReader() method. This method returns a SqlDataReader class from which you process the result set produced from the query:

 SqlDataReader *reader = cmd->ExecuteReader();

The code to query a database with a CommandType of StoredProcedure is a little more difficult if passing parameters is required. (It is a little easier if no parameters are passed, as no SQL code has to be written by the application developer.) First, you set the SqlCommand class's CommandType property to StoredProcedure:

 cmd->CommandType = CommandType::StoredProcedure;

Next, you place the name of the stored procedure you want to execute in the CommandText property:

 cmd->CommandText = S"StoriesWhereLastName";

Now comes the tricky part. You need to build a collection of SqlParameters, within which you will place all the parameters that you want sent to the stored procedure. The SqlCommand class provides a property called Parameters to place your collection of SqlParameters.

The first step is to use the Add() method off of the Parameters property collection to add all the SqlParameters making up all the parameters that will be passed to the stored procedure. The constructor for the SqlParameters class takes two or three parameters depending on the data type of the parameter that will be passed to the stored procedure. If the data type has a predefined length like Int or a variable length like VarChar, then only two parameters are needed.

 cmd->Parameters->Add(new SqlParameter(S"@LastName", SqlDbType::VarChar));

On the other hand, if the data type needs its length specified like Char, then the third parameter is used to specify the length.

 cmd->Parameters->Add(new SqlParameter(S"@FixedSizeString",SqlDbType::Char,32));

When all the parameters are specified, you need to assign values to them so that the stored procedure can use them. You do this by assigning a value to the Value property of the indexed property Item, off of the Parameters property collection of the SqlCommand class. Clear as mud? The example should help:

 cmd->Parameters->Item["@LastName"]->Value = Name;

Finally, when all the parameters are assigned values, you call the SqlCommand class's ExecuteReader() method just like you did for a CommandType of Text:

 reader = cmd->ExecuteReader();

The processing of the result set within the SqlDataReader object is handled in a forward-only manner. The basic process is to advance to the next record of the result set using the Read() method. If the return value is false, you have reached the end of the result set and you should the call the Close() method to close the SqlDataReader. If the value is true, then you continue and process the next result set record.

 while(reader->Read()) {     Console::WriteLine(reader->Item[S"StoryID"]);     Console::WriteLine(reader->Item[S"Headline"]);     Console::WriteLine(reader->Item[S"Story"]);     Console::WriteLine(S""); } reader->Close();

There are two different methods of processing the record set. You can, as I did, use the indexed Item property to get the value based on the column header. You can also process the columns using an assortment of type-specific Getxxx() methods. The following code generates the same output as the preceding code:

 while(reader->Read()) {     Console::WriteLine(reader->GetInt32(0));     Console::WriteLine(reader->GetString(1));     Console::WriteLine(reader->GetString(2));     Console::WriteLine(S""); } reader->Close();

Note the parameter passed in the position of the column starting at zero.

I personally find using column names easier, but the style you choose to use is up to you. Figure 12-9 shows the results of the preceding example program.

click to expand
Figure 12-9: Retrieving Bill Doors's stories

Insert, Update, and Delete Commands

The code to modify the database (i.e., insert, update, and delete rows of the database) isn't much different from the code to query the database. Obviously, the SQL is different. The only other difference is that you call the SqlCommand class's ExecuteNonQuery() method instead of the ExecuteReader() method.

You can still use both CommandTypes and you still need to set up the SQLParameters the same way for stored procedures.

In Listing 12-7 you insert a new record into the database, you change the LastName on the record, and then you delete the record. (A lot of work for nothing, don't you think?)

Listing 12-7: Modifying the Database

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; Int32 main(void) {     String *Name = S"Doors";     SqlConnection *connection = new SqlConnection(); #ifdef SQLAuth     //  SQL Server authentication     connection->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     //  Windows Integrated Security     connection->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif     try     {         SqlCommand *cmd = new SqlCommand();         cmd->Connection = connection;         connection->Open();         cmd->CommandType = CommandType::StoredProcedure;         cmd->CommandText = S"InsertAuthor";         cmd->Parameters->Add(new SqlParameter(S"@LastName", SqlDbType::VarChar));         cmd->Parameters->Add(new SqlParameter(S"@FirstName",SqlDbType::VarChar));         cmd->Parameters->Item[S"@LastName"]->Value = S"Dope";         cmd->Parameters->Item[S"@FirstName"]->Value = S"John";         Int32 affected = cmd->ExecuteNonQuery();         Console::WriteLine(S"Insert - {0} rows are affected", __box(affected));         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"UPDATE Authors SET LastName = 'Doe'"                              S"WHERE LastName = 'Dope'";         affected = cmd->ExecuteNonQuery();         Console::WriteLine(S"Update - {0} rows are affected", __box(affected));         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"DELETE FROM Authors WHERE LastName = 'Doe'";         affected = cmd->ExecuteNonQuery();         Console::WriteLine(S"Delete - {0} rows are affected", __box(affected));     }     catch (SqlException *e)     {         Console::WriteLine(S"No connection the following error occurred: {0}",             e->Message);     }     __finally     {         connection->Close();     }     return 0; }

As you can see, there is not much new going on here in the Managed C++ code, other than the call to ExecuteNonQuery(). This method returns the number of rows affected by the SQL command.

 Int32 affected = cmd->ExecuteNonQuery();

Figure 12-10 shows the results of the preceding example program.

click to expand
Figure 12-10: A lot of modifications to the database for no gain

Returning a Single Value From a Query

The final command executing method of the SqlCommand class is ExecuteScalar(). This method is designed to return an Object pointer as the result of the query. The returned Object points to a value like that produced by an aggregated SQL function such as COUNT or SUM. Again, like the database modifying command, there is not much changed between the source code needed to execute this type of method and that of a standard query.

Listing 12-8 shows how to count all the records in a database and also how to sum a column. (The database does not have a column that you would want to sum—I had to improvise.)

Listing 12-8: Counting and Summing

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; Int32 main(void) {     SqlConnection *connection = new SqlConnection(); #ifdef SQLAuth     //  SQL Server authentication     connection->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     //  Windows Integrated Security     connection->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif     try     {         SqlCommand *cmd = new SqlCommand();         cmd->Connection = connection;         connection->Open();         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"SELECT COUNT(*) FROM Authors";         Object *NumAuthors = cmd->ExecuteScalar();         Console::WriteLine(S"The number of Authors are {0}", NumAuthors);         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"SELECT SUM(AuthorID) FROM Authors";         Object *UselessNum = cmd->ExecuteScalar();         Console::WriteLine(S"The Sum of AuthorIDs for fun is {0}", UselessNum);     }     catch (SqlException *e)     {          Console::WriteLine(S"No connection the following error occurred: {0}",              e->Message);     }     __finally     {         connection->Close();     }     return 0; }

As you can see, other than the SQL code and the calling of the ExecuteScalar() method, there is not much new. The ExecuteScalar() method returns a pointer to an Object, which you can type cast to the type of the return value. In both cases, you could have type cast the return Object pointer to Int32, but the WriteLine() method can do it for you.

Figure 12-11 shows the results of the preceding example program.

click to expand
Figure 12-11: Counting rows and summing a column

Using Connected ADO.NET with Transactions

Think about this scenario. You buy a computer on your debit card, but while the purchase is being processed, the connection to the debit card company is lost. The response from the debit card reader is failure message. You try again and the debit card reader now responds that there is not enough money. You go home empty-handed, angry, and confused. Then a month later your bank statement says you bought a computer with your debit card.

It can't happen, right? Wrong. If you use the preceding immediate updating method, it's very possible, as each update to the database is stand-alone. One command can complete, for example, the withdrawal, while a second command may fail, for example, the sale.

This is where transactions come in handy. They make sure all database commands needed to complete a process are completed successfully before allowing the database to commit (or write) these commands. If one or more of the commands fail, the database can reject all of the commands and return to its original state before any of the commands where completed. This is known as rolling back.

Figure 12-12 shows the basic flow of transactional database access.

click to expand
Figure 12-12: Transactional database access

Create a link to the database with a SqlConnection.
Open the database with the Open() method.
Configure for transactions.
Create a database transaction with the SqlCommand class.
Execute the transaction by using the ExecuteNonQuery() method of the SqlCommand class. The temporary copy of the database is updated.
Repeat steps 4 and 5 until completed.
When all transactions are complete, either commit the transactions to the database or roll them back.
Close the database with the Close() method.

Listing 12-9 shows how to convert the nontransactional example from Listing 12-7 into a transactional example.

Listing 12-9: Transactional Database Updates

 using namespace System; using namespace System::Data; using namespace System::Data::SqlClient; Int32 main(void) {     String *Name = S"Doors";     SqlConnection *connection = new SqlConnection();     SqlTransaction *transaction; #ifdef SQLAuth     //  SQL Server authentication     connection->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     //  Windows Integrated Security     connection->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif     try     {         connection->Open();         SqlCommand *cmd = new SqlCommand();         transaction = connection->BeginTransaction(             IsolationLevel::Serializable, S"AuthorTransaction");         cmd->Connection = connection;         cmd->Transaction = transaction;         cmd->CommandType = CommandType::StoredProcedure;         cmd->CommandText = S"InsertAuthor";         cmd->Parameters->Add(new SqlParameter(S"@LastName", SqlDbType::VarChar));         cmd->Parameters->Add(new SqlParameter(S"@FirstName",SqlDbType::VarChar));         cmd->Parameters->Item[S"@LastName"]->Value = S"Dope";         cmd->Parameters->Item[S"@FirstName"]->Value = S"John";         Int32 affected = cmd->ExecuteNonQuery();         if (affected <= 0)             throw new Exception(S"Insert Failed");         Console::WriteLine(S"Insert - {0} rows are affected", __box(affected));         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"UPDATE Authors SET LastName = 'Doe'"                              S"WHERE LastName = 'Dope'";         affected = cmd->ExecuteNonQuery();         if (affected <= 0)             throw new Exception(S"Insert Failed");         Console::WriteLine(S"Update - {0} rows are affected", __box(affected));         // This transaction will return 0 affected rows         // because "Does" does not exist.         // Thus, the if condition throws an exception which causes all         // Transactions to be rolled back.         cmd->CommandType = CommandType::Text;         cmd->CommandText = S"DELETE FROM Authors WHERE LastName = 'Does'";         affected = cmd->ExecuteNonQuery();         if (affected <= 0)             throw new Exception(S"Insert Failed");         Console::WriteLine(S"Delete - {0} rows are affected", __box(affected));         transaction->Commit();     }     catch (Exception *e)     {         transaction->Rollback(S"AuthorTransaction");         Console::WriteLine(S"Transaction Not completed");         Console::WriteLine(S"SQL error occurred: {0}", e->Message);     }     __finally     {         connection->Close();     }     return 0; }

As you can see there have not been many changes. First, you need to declare a SqlTransaction (OleDbTransaction, OdbcTransaction, or OracleTransaction) class:

 SqlTransaction *transaction;

Next, you need to create a transaction set using the SqlConnection class's BeginTransaction() method. The BeginTransaction() method takes two parameters. The first parameter specifies the locking behavior of the transaction (see Table 12-7) and the second is the name of the transaction set:

 transaction = connection->BeginTransaction(IsolationLevel::RepeatableRead,                                                S"AuthorTransaction");

Table 12-7: Common Transaction IsolationLevels
LEVEL	DESCRIPTION
ReadCommitted	Specifies that locks are held while the data is read, but changes to the data can occur before the transaction is committed
ReadUncommitted	Specifies that changes can occur even while the data is being read
RepeatableRead	Specifies that locks are held on the data until the transaction is committed, but additional rows can be added or deleted
Serializable	Specifies that locks are held on the entire database until the transaction is committed

Now that you have a transaction set, you need to assign it to the SqlCommand class's property Transaction:

 cmd->Transaction = transaction;

The last set of transactional database updates is to execute all the transactions. If everything completes successfully, then execute the SqlTransaction class's Commit() method:

 transaction->Commit();

If, on the other hand, an error occurs, you would then execute the SqlTransaction class's Rollback() method:

 transaction->Rollback(S"AuthorTransaction");

Figure 12-13 shows the results of the preceding example program failing because the name of the author was not found in the database.

click to expand
Figure 12-13: Transactional database update rollback