< 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?
-
Creating and Executing a Command
-
Executing a Command That Modifies Data
-
Executing Queries and Processing the Results
-
Creating a Disconnected Application
Creating and Executing a Command
In this exercise, you will create an OleDbCommand object that represents the following SQL statement:
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:
|
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);
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.
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);
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.
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();
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"--------------------------------------");
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();
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.)
| |
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.
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.
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