< C++ .NET and Using ADO.NET 2 | Main | C++ .NET and Using ADO.NET 4 >


 

 

C++ .NET And Using ADO.NET 3

 

 

What we have in this page?

  1. Creating and Executing a Command

  2. Executing a Command That Modifies Data

  3. Executing Queries and Processing the Results

  4. Creating a Disconnected Application

 

Creating and Executing a Command

 

In this exercise, you will create an OleDbCommand object that represents the following SQL statement:

SELECT COUNT(*) FROM Products

This statement returns an integer indicating how many rows are in the Products table. You will execute this statement by using the ExecuteScalar method on the OleDbCommand object.

 

8.      Continue using the project from the previous exercise.

9.      In the main() function, add the following code to the try block, after the statement that opens the database connection:

// Count the customers

OleDbCommand ^ cmProducts = gcnew OleDbCommand();

cmProducts->CommandText = L"SELECT COUNT(*) FROM Products";

cmProducts->CommandType = CommandType::Text;

cmProducts->Connection = cnNwind;

 

Adding code to the try block, after the statement that opens the database connection

 

This code creates and configures an OleDbCommand object to encapsulate a SQL statement. The CommandText property defines the SQL to be executed, and CommandType says that this is the SQL text. If you were using SQL Server, you could use CommandText to specify the name of a stored procedure, giving CommandType::StoredProcedure as the command type. The Connection property specifies which database connection to use when executing the command. You could write the previous code sample more concisely, as follows:

OleDbCommand ^ cmProducts = gcnew OleDbCommand(L"COUNT(*) FROM Products", cnNwind);

10.  Add the following code to execute the SQL statement and display the results on the console:

// Print the result

Object ^ numberOfProducts = cmProducts->ExecuteScalar();

Console::Write(L"Number of products: ");

Console::WriteLine(numberOfProducts);

 

Adding code to execute the SQL statement and display the results on the console

 

11.  Build your program, and fix any compiler errors. Run the program. The message shown in the following figure should be displayed on the console.

 

Connecting and closing database C++ .NET program output example

Executing a Command That Modifies Data

 

In this exercise, you will execute a command that increases the price of all products by 5 percent. You will use the following SQL statement:

UPDATE Products SET UnitPrice = UnitPrice * 1.05

You will use the ExecuteNonQuery method to execute this statement. ExecuteNonQuery returns an integer to indicate how many rows the statement affected.

 

12.  Continue using the project from the previous exercise.

13.  Find the code you wrote in the previous exercise, and add the following line of code:

// Update the prices of products

cmProducts->CommandText = L"UPDATE products SET UnitPrice = L"UnitPrice * 1.05 ";

This code reuses the OleDbCommand object from the previous exercise but specifies a different SQL statement.

 

14.  Next, add the following code to execute the SQL statement and display the results on the console:

int rowsAffected = cmProducts->ExecuteNonQuery();

Console::Write(L"Number of products increased in price: ");

Console::WriteLine(rowsAffected);

 

Adding code to execute the SQL statement and display the results on the console

 

15.  Build your program, and fix any compiler errors. Run the program. The message shown in the following figure should be displayed on the console.

 

C++ .NET - displaying data query result through the console

 

 

 

 

Executing Queries and Processing the Results

 

In this exercise, you will execute a command that queries information from the database. You will use the following SQL statement:

SELECT ProductName, UnitPrice FROM Products

You will use the ExecuteReader method to execute this statement. ExecuteReader returns an OleDbDataReader object, which is a fast, forward-only reader that reads through the rows in the result set.

 

16.  Continue the project from the previous exercise. Find the code you wrote in the previous exercise, and add the following line of code:

// Query the database

cmProducts->CommandText = L"SELECT ProductName, UnitPrice FROM Products";

 

 

This code reuses the OleDbCommand object from the previous exercise but specifies a different SQL statement.

 

17.  Add the following code to execute the SQL statement and retrieve the results into an OleDbDataReader object:

OleDbDataReader ^ reader = cmProducts->ExecuteReader();

 

Adding code to execute the SQL statement and retrieve the results into an OleDbDataReader object

 

18.  Add the following code to loop through the results one row at a time. For each row, output column 0 (the ProductName) as a String value, and output column 1 (the UnitPrice) as a Decimal value.

Console::WriteLine(L"\n------------------------------------");

while (reader->Read())

{

  Console::Write(reader->GetString(0));

  Console::Write(L", ");

  Console::WriteLine(reader->GetDecimal(1));

}

Console::WriteLine(L"--------------------------------------");

 

Adding code to loop through the results one row at a time

 

The Read method steps through the record set one row at a time. For each row, we use the strongly typed methods GetString and GetDecimal to get the values of columns 0 and 1, respectively.

 

19.  After the loop, add code to close the OleDbDataReader as follows:

reader->Close();

 

Adding code to close the OleDbDataReader

 

20.  Run the program. The message shown in the following figure should be displayed on the console. (You might get different values than we’ve shown here.)

 

C++ .NET OledbDataReader - reading and displaying data in the Console

 

Creating a Disconnected Application

 

For the rest of the module, we’ll turn our attention to disconnected applications. A disconnected application is one that does not have a permanently available connection to the data source. For example, a salesperson might require an application that can access data in the central database, even when he or she is out of the office. ADO.NET provides the DataSet class to enable you to achieve data access in disconnected applications. The following figure shows the DataSet object model.

 

The DataSet object model 1

 

A DataSet is an in-memory collection of DataTable objects and relationships between these DataTables. You can create many DataTable objects in a DataSet to hold the results of numerous SQL queries. Each DataTable has a collection of DataColumn objects. These DataColumn objects contain metadata about the columns, such as the column name, data type, default value, and so on. Each DataTable also has a collection of DataRow objects. These DataRow objects contain the data for the DataSet. To fill a DataSet with data, you must use a data adapter object. If you are using the SQL Server .NET data provider, you will use SqlDataAdapter. If you are using the OLE DB .NET data provider, you will use OleDbDataAdapter. The following figure shows how data adapters work with DataSets.

 

The DataSet object model - DataTable, DataAdapter and Data Source

 

 

 

 

Each data adapter works with a single DataTable in a DataSet. You call the Fill method on a data adapter to fill the DataSet with data from the database. You call the Update method on a data adapter to save any changes in the DataSet back to the database. Internally, the data adapter has four command objects to achieve data access on behalf of the DataSet. The data adapter uses these command objects to retrieve data from the database and to update the database with any modifications. If the data adapter is using the SQL Server data provider, these commands will be SqlCommand objects. If the data adapter is using the OLE DB data provider, these commands will be OleDbCommand objects. The following table describes these command objects.

 

Command Object in a Data Adapter

Description

SelectCommand

Contains a SQL SELECT statement to retrieve data from the database into the DataSet table.

InsertCommand

Contains a SQL INSERT statement to insert new rows from the DataSet table into the database.

UpdateCommand

Contains a SQL UPDATE statement to modify existing rows in the database.

DeleteCommand

Contains a SQL DELETE statement to delete rows from the database.

 

Table 8

 

 

Part 1 | Part 2 | Part 3 | Part 4 | Part 5

 

 


< C++ .NET and Using ADO.NET 2 | Main | C++ .NET and Using ADO.NET 4 >