Filling a DataSet and Binding to a Control on a Form

Filling a `DataSet` and Binding to a Control on a Form

Single- tier data access applications are popular sample and starter applications, but they don't fill many niches in software development. This especially applies to examples in which all the database interaction code is right in the event handler for the button click. It's just too hard to maintain that kind of application. In this section, the solution has two projects: one with the user interface and one with the database code. You could build the database code into a separate class library project in its own solution, using the techniques of Chapter 6, "Writing a Class Library in Managed C++," and even share the database code among several applications.

You could move all this database code into a Web Service (as in Chapter 10, "Writing and Consuming a Web Service"), and then change the user interface code to use the Web Service. This design would then become a distributed application built on Web Services. Alternatively, you could use .NET Remoting (as in Chapter 14, "Moving Layers to Different Machines with .NET Remoting") to move the database project onto another computer, creating a distributed application built on .NET Remoting. The more modular your application is, the simpler the job of pulling part of it onto another machine becomes.

Creating the User Interface

Create a Windows Forms application called EmployeeUI . Drag on a text box, and then change the Text property to an empty string and the name to employeeName . Drag a button next to it, and then change the text and the name to Lookup . Drag a data grid underneath them and resize it to take up most of the dialog box. The form should resemble Figure 11.1.

Figure 11.1. Creating a simple user interface.

graphics/11fig01.gif

Creating the Data Layer Project

Before you write the Click handler for the Lookup button, you must write the data layer class that will provide the database access. In Solution Explorer, right-click the entire solution and choose Add, New Project. Select a Class Library (.NET) and name it EmployeeData . Right-click EmployeeData in Solution Explorer, and choose Add, Add Class. Choose a Generic C++ class.

Name the class Employee and click Finish on the Generic Class Wizard. In Employee.h, add a namespace declaration (you can copy it from EmployeeData.h), and change the class definition to make it a public, garbage-collected class:

 namespace EmployeeData {     public __gc class Employee     {     public:         Employee(void);         ~Employee(void);     }; }

This class needs a Lookup() method that takes a string, represents an employee name, and looks that name up in the database, returning a data set holding all the records in the database that matched the name. Add the declaration to the class definition:

 DataSet* Lookup(String* name);

Add these using statements to the top of the file, after the pragma:

 using namespace System; using namespace System::Data;

Add this stub for the Lookup function to Employee.cpp:

 DataSet* Employee::Lookup(String* name) {     DataSet* ds = new DataSet("Employees");     return ds; }

Add a reference to System.Xml.dll (on the .NET tab of the Add Reference dialog box) so that this code will compile (the DataSet class defines some interfaces that are defined in the System.Xml assembly), and then enter this code. At the top of Employee.cpp, add a using statement:

 using namespace EmployeeData;

Finally, remove the EmployeeData.cpp and EmployeeData.h files: They aren't needed in this solution. Right-click each of them in Solution Explorer and choose Remove. Build the solution to make sure there are no typing errors.

Handling the Connection String

The Lookup() method will use a DataAdapter to fill ds before returning it. To create a DataAdapter , you need a connection to the database. To create a connection, you need a connection string. It's a bad idea to hard-code connection strings in many different functions throughout an application. There are two reasons for this:

If the database name, location, or authentication technique ever changes, you will have to find and change the connection string in many places within your code.
The built-in connection pooling in ADO.NET works only if all the connection objects that are created use identical connection strings. If the strings vary a little, even in the order of the parameters inside them, the connections won't be pooled and your application will run more slowly.

It's appealing, then, to have a variable that holds the connection string, and to set that once, perhaps in the constructor, and use it throughout this data layer component. Expand the class definition in Employee.h, adding these lines after the declaration of Lookup :

 private:     String* ConnStr;

Add this line to the body of the Employee constructor:

[View full width]

  [View full width] 
 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Working\Kick Start\deptstore  .mdb" ;

Make sure you change the path to match the location where you copied the Access file earlier. Now you can use this connection string throughout the data layer.

Using a Configuration File

If you want the connection string to be a property of the data layer, keeping it in a member variable that is set in the constructor makes a lot of sense. But what if you ever need to change it? Using a hard-coded string like this would require you to edit the source code and recompile the application, not to mention redistributing the application and redeploying it, whenever the connection string changes. A better approach than hard-coding is to use a configuration file.

Configuration files for ASP.NET applications have received plenty of publicity, but it's a lesser-known feature of regular Windows applications. A configuration file is a collection of XML that can be edited by a user, and read by the application to determine settings such as connection strings. Unfortunately, you can't add a configuration file to your class library, but it can read the application's configuration file.

To add a configuration file to the application, right-click the EmployeeUI project in Solution Explorer and choose Add, Add New Item. Select Configuration File (app.config) and click Open . Edit the file so that it reads like this:

[View full width]

  [View full width] 
 <configuration>   <appSettings>     <add key=" connstr"  value=" Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Working  \Kick Start\deptstore.mdb" />   </appSettings> </configuration>

The idea behind a configuration file is that whenever you build the solution, this file will be copied to EmployeeUI.exe.config in the same folder as EmployeeUI.exe. This configuration file controls the behavior of the application at runtime, and changes to this file take effect without your having to rebuild the solution. For a C++ project, you have to arrange this copy step yourself. Here's how:

Bring up the properties page for the EmployeeUI project.
Expand the Build Events folder and select Post-Build Event.
Choose All Configurations from the Configurations drop-down box at the top of the dialog box.
Click next to Command Line and a symbol appears. Click that symbol.
Click the Macros button to display a list of useful shortcuts.
You could enter the entire post-build string as literal text, but it would need to be edited if you changed the name of the application, or even switched between Debug and Release builds. You are aiming for the string copy app.config Debug\EmployeeUI.exe.config or copy app.config Release\EmployeeUI.exe.config but it should be built from placeholders.
Type the words copy app.config into the empty white box in the Command Line dialog box. Enter a space. Double-click ConfigurationName in the list of macros. Enter a backslash. Double-click TargetFileName in the list of macros. Type .config immediately after it in the command line.
Click OK in the Command Line dialog box. The command-line property should now be
```
 
```
```
 copy app.config $(ConfigurationName)$(TargetFileName).config 
```
Click OK on the properties page.

The next step is to use the entry that you added to the configuration file.

Edit the constructor for Employee so that it uses the configuration settings. Replace the line of code that set the value of ConnStr with this line:

 ConnStr =    Configuration::ConfigurationSettings::AppSettings->get_Item("connstr");

The ConfigurationSettings class represents the contents of the configuration file, and the static AppSettings property of the class represents the contents of the <appSettings> element within the file. The get_Item() method takes a key and returns the corresponding value.

Writing the `Lookup()` Method

With the connection string issue settled, all that remains to complete this data layer is to write the Lookup() method. The steps this method will complete are as follows :

Create a new empty data set.
Build the query string.
Create the data adapter.
Use the data adapter to fill the data set.
Return the data set.

When you're trying to get information from a database, there's a lot than can go wrong. Your connection string might not be right, you might not have permission to access the database, your query might contain a syntax error, and so on. It's very important to surround database code in a try/catch block so that you can get as much information as possible if anything goes wrong.

Enter this code for the Lookup() method:

 DataSet* Employee::Lookup(String* name) {     DataSet* ds = new DataSet("Employees");     StringBuilder* query = new StringBuilder();     query->Append("SELECT * FROM Employees WHERE EmployeeName Like '%");     query->Append(name);     query->Append("%'");     OleDbDataAdapter* adapter = new OleDbDataAdapter(query->ToString(),                                                      ConnStr);     try     {         adapter->Fill(ds," Employees");     }     catch (OleDbException* e)     {         Console::WriteLine("OleDbException caught while filling the dataset");         Console::WriteLine(e->Message);     }     return ds; }

There are several points to notice about this code:

It uses a StringBuilder to build the SQL statement from the string that was passed in.
The SQL statement uses the LIKE clause and surrounds the name with % characters to find strings that contain the name string .
There's no need to open or close the connection; the adapter takes care of that. You just pass the connection string to the adapter constructor.
The adapter fills the data set; data sets cannot fill themselves .

Using the Data Layer from the UI

Before the user interface code can use the data layer, you must add a reference to that data layer. Right-click the References node under EmployeeUI in Solution Explorer and choose Add Reference. Select the Projects tab and add a reference to EmployeeData .

In the design view for Form1.h, double-click the Lookup button to edit the handler. Add this code to the method:

 private: System::Void Lookup_Click(System::Object *  sender,                                    System::EventArgs *  e) {     EmployeeData::Employee* emp = new EmployeeData::Employee();     DataSet* ds = emp->Lookup(employeeName->Text);     dataGrid1->DataSource = ds->Tables->get_Item(0); }

All the database work is delegated to the data layer. Here in the user interface, the handler creates an instance of the Employee object from the data layer, calls its Lookup() method, and binds the returned data set to the data grid for display.

Build and run this application. Enter a short string of letters in the text box and click Lookup. You should see some results in the grid. Figure 11.2 shows the results of searching for names that contain the letter G in the sample database provided with this chapter.

Figure 11.2. The application searches for employees by name.

graphics/11fig02.gif

This application demonstrates how simple it is to separate your user interface from a data layer or other class library that provides services to the user interface. It also uses a configuration file to maximize the flexibility of the deployed application.