Creating a Connected Application


  • In the next few pages, you will create a managed C++ application that connects to a Microsoft Access database. You will use an OleDbConnection object to establish this connection.

Once you are connected, you will create an OleDbCommand object to represent a SQL statement. You will then perform the following tasks:

  • Execute a statement that returns a single value. OleDbCommand has an ExecuteScalar method for this purpose.

  • Execute a statement that updates the database. OleDbCommand has an ExecuteNonQuery method to do this.

  • Execute a statement that queries the database. OleDbCommand has an ExecuteReader method to do this. ExecuteReader returns an OleDbDataReader object, which provides fast, forward-only access to the rows in the result set. You will use this OleDbDataReader object to process the result set.

Connecting to a Database

In this exercise, you will create a new application to perform all the operations described in the preceding section. The first step is to connect to the database.

Note

This exercise uses the Microsoft Northwind database. You may already have a copy if you have Microsoft Office installed on your PC, but there is also a copy in the sample files that accompany the book.

  1. Create a Visual C++ Console Application (.NET) project named ConnectedApplication.

  2. In the ConnectedApplication.cpp file, after the using namespace System statement, add the following statements:

    // Generic ADO.NET definitions using namespace System::Data; // Specific definitions for the OleDb data provider using namespace System::Data::OleDb; 
    Note

    If you are using SQL Server 7 or later, you can use the SQL Server data provider instead. To do so, use the System::Data::SqlClient namespace and replace all the class names that begin OleDb with Sql. For example, replace OleDbException with SqlException.

  3. In the _tmain function, create an OleDbConnection object as follows:

    // Create the connection OleDbConnection * cnNwind = new OleDbConnection();
  4. The OleDbConnection object has a ConnectionString property, which enables you to specify the database you want to use. Set the ConnectionString property as follows:

    // Set the connection string cnNwind->ConnectionString = S" Provider=Microsoft.Jet.OLEDB.4.0; " S"Data Source=C:\\temp\\northwind.mdb";

    This connection string defines a connection to the Northwind database, using the Microsoft Jet database engine. You will need to edit the Data Source path to point to your copy of Northwind.

    Note

    If you are using SQL Server, you’ll need to provide a SQL Server connection string. You’ll also need to connect to a SQL Server version of the Northwind database.

  5. Open the database connection as follows:

    try { // Open the database cnNwind->Open(); Console::WriteLine(S"Connected to database successfully!"); } catch (OleDbException * pe) { Console::Write(S"Error occurred: "); Console::WriteLine(pe->Message); }

    Just about everything you do with databases can generate an exception. Therefore, you should always enclose your database code in a try and catch block, as shown in the preceding code.

  6. At the end of the _tmain function, close the database connection as follows:

    // Close the connection if (cnNwind->State != ConnectionState::Closed) { cnNwind->Close(); } Console::WriteLine(S"The database connection is now closed"); 

    The State property indicates the current state of the connection. The allowable values for this property are defined in the ConnectionState enumerated type, which is located in the System::Data namespace.

  7. Build your program, and fix any compiler errors.

  8. Run the program. If all is well, you’ll see the message shown in the following figure displayed on the console.

    click to expand

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.

  1. Continue using the project from the previous exercise.

  2. In the _tmain function, add the following code to the try block, after the statement that opens the database connection:

    // Count the customers OleDbCommand * cmProducts = new OleDbCommand(); cmProducts->CommandText = S"SELECT COUNT(*) FROM Products"; cmProducts->CommandType = CommandType::Text; cmProducts->Connection = cnNwind; 

    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.

    Note

    You could write the previous code sample more concisely, as follows:

    OleDbCommand * cmProducts = new OleDbCommand(S"COUNT(*) FROM Products", cnNwind);

  3. 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(S"Number of products: "); Console::WriteLine(numberOfProducts);
  4. Build your program, and fix any compiler errors.

  5. Run the program. The message shown in the following figure should be displayed on the console.

    click to expand

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.

  1. Continue using the project from the previous exercise.

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

    // Update the prices of products cmProducts->CommandText = S"UPDATE products SET UnitPrice = " S"UnitPrice * 1.05 ";

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

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

    int rowsAffected = cmProducts->ExecuteNonQuery(); Console::Write(S"Number of products increased in price: "); Console::WriteLine(rowsAffected);
  4. Build your program, and fix any compiler errors.

  5. Run the program. The message shown in the following figure should be displayed on the console.

    click to expand

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.

  1. Continue the project from the previous exercise.

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

    // Query the database cmProducts->CommandText = S"SELECT ProductName, UnitPrice FROM Products";

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

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

    OleDbDataReader * reader = cmProducts->ExecuteReader();
  4. 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(S"\n------------------------------------"); while (reader->Read()) { Console::Write(reader->GetString(0)); Console::Write(S", "); Console::WriteLine(reader->GetDecimal(1)); } Console::WriteLine(S"--------------------------------------");

    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.

  5. After the loop, close the OleDbDataReader as follows:

    reader->Close();
  6. 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.)

    click to expand




Microsoft Visual C++  .NET(c) Step by Step
Microsoft Visual C++ .NET(c) Step by Step
ISBN: 735615675
EAN: N/A
Year: 2003
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net