BDP for Microsoft .NET Components

The thought of quick application development with drag-and-drop components in a graphical environment is attractive. When you're using drag-and-drop components, it's very quick and easy to create applications that use BDP components to access data. Graphical editors for the components minimize trips to the code and simplify the process. However, it would be wise to think about the implications of writing an application this way.

First, think about requirements for enterprise applications. There are certainly concerns about application scalability, robustness, and maintainability. A common approach to achieving this goal is by designing a multitiered architecture for the application. By separating user interface, logic, and data access into separate layers, it is possible to achieve the goals of high-performance enterprise class applications.

Now, think about what happens when an application is built by connecting BDP components directly to Windows Forms controls. This action bypasses a business logic layer, makes a data access layer impossible, and mixes all the layers together into a monolithic application reminiscent of decades past. Essentially, the application has no scalability, reuse suffers, and maintenance quickly becomes a nightmare. Frankly, a GUI approach to connecting data components directly to user interface controls flies directly in the face of good software design.

After seeing the problems of the drag-and-drop GUI approach, where scalability, reuse, and maintainability are affected detrimentally, it would be instructive to know in what context the GUI approach could be useful. By its very nature, drag-and-drop programming is fast and easy. Because of this, it works well with RAD development for very small applications, one-time projects, and prototyping. It is a quick-and-dirty way to accomplish a project that won't live beyond a short time period.

Now that the drag-and-drop approach has been put in context, I'll show how to use it to quickly create a small application. Along the way, look for ways to use the techniques presented to quickly generate code that may be cut and pasted for use in a data access layer.

The example in this section will take only a few steps to quickly create a database application. It will be a Windows Forms application with a DataGrid, a panel, and an update button. To get started, create a new Windows Forms application. Perform the following steps to add and configure controls, which should look similar to Figure 15.2:

1. Add a Panel control. Set the Dock property to Bottom and resize it so it is just large enough to hold a Button control.

2. Add a Button control to the Panel. Set its Text property to Update, set its Name property to btnUpdate, and clear all anchors from its Anchor property.

3. Add a DataGrid control. Set the CaptionText property to Customers, the Name property to dgrCustomers, and the Dock property to Fill. Select the Auto Format link and set it to Colorful 2.

Figure 15.2. Windows Forms controls for a database application.

A helpful capability of C#Builder is the Data Explorer window (see Figure 15.3), which allows you to configure and display available databases. This example will use the Northwind database, so we need a connection to it.

Figure 15.3. The Data Explorer window.

To add a new database connection, right-click on the MSSQL branch in the Data Explorer and select Add New Connection. In the Add New Connection dialog that appears, MSSQL will be selected as the Provider Name. Fill in the Connection Name field with a recognizable name, such as Northwind, and click the OK button. The connection name may be anything; I usually give it the same name as the database I'm connecting to.

After the connection has been created, it still isn't usable because there are several other parameters to set. Right-click on the Northwind connection and select Modify Connection to bring up the Connections Editor dialog (see Figure 15.4). At a minimum, fill in the HostName (server), Database, UserName, and Password properties. The HostName would be the INSTANCENAME parameter used when MSDE was set up or the name of the machine MSDE was installed on if no INSTANCENAME parameter was used. Another way to determine an MSDE hostname is to double-click the SQL Server Service Manager icon on the Windows taskbar and view the list of servers. Before closing the dialog, click the Test button to make sure the parameters were filled in correctly. If so, a MessageBox with the words "Connection Successful" will pop up. Otherwise, double-check the properties to make sure they are filled in correctly or check the database to make sure it is installed properly. Click the OK button when done.

Figure 15.4. The Connections Editor dialog.

Most of the options on the connection context menu in the Data Explorer are self-explanatory, but there is one option that I would like to explain more called the SQL window, which is shown in Figure 15.5. Selecting SQL Window from the connection context menu brings up the SQL window, which contains an edit box where SQL queries may be entered and executed. This is an excellent way to test queries before adding them to code or stored procedures.

Figure 15.5. The SQL window.

Opening the connection for the Northwind database reveals three branches for Tables, Views, and Procedures. The Tables branch opens further to show tables with fields and indexes belonging to each table. The Views branch works similar to Tables, but shows View objects instead. The Procedures branch shows stored procedures and parameters. Overall this is a good way to browse a database, test out queries in the SQL window, and prepare for any type of database development.

After the connection has been created, find the Customers table of the Northwind database connection in Data Explorer. Be sure the Windows Form designer surface is open, select the Customers table in the Data Explorer, and drag-and-drop it onto the designer surface. This creates BdpConnection1 and BdpDataAdapter1 objects. Rename BdpConnection1 to conn and BdpDataAdapter1 to customersDA by selecting each component in the Component Tray and modifying the Name property in the Design section of the Object Inspector. The conn and customersDA components are shown in Figure 15.6. There are two other components in Figure 15.6, which are discussed in this section: customersDS and customersCB.

Figure 15.6. The Component Tray.

The BdpConnection object has a Connection Editor that can be found on its context menu or via editor link in the Object Inspector. The options are the same as those in the Connection Editor that is accessible from the Data Explorer.

The BdpDataAdapter has dialogs both for configuring it and for generating a typed data set, which are accessible from its context menu or via verbs on the Object Inspector. Opening the Configure Data Adapter editor shows a dialog with three tabs: Command, Preview Data, and DataSet. The Command tab (see Figure 15.7) allows configuration of the select, insert, update, and delete queries. There is a tab group for each of these queries and selecting each tab shows that the queries were already generated when the component was dropped onto the designer surface. These may be adjusted as necessary for more complex scenarios. To adjust the statements automatically, select the desired table, select multiple columns using Control-click or Shift-click, and then click the Generate SQL button. Note that assignment of this DataAdapter to a CommandBuilder component, discussed in the next paragraph, could change the delete, insert, and update commands.

Figure 15.7. The Data Adapter Configuration Command tab.

After the DataAdapter has been created, drag-and-drop a BdpCommandBuilder onto the Windows Form designer surface. This action will add a new CommandBuilder component named BdpCommandBuilder1 to the Component Tray. Select BdpCommandBuilder1 and change its Name property under the Design section of the Object Inspector to customersCB. Then, change the DataAdapter property under the Misc section of the Object Inspector to customersDA. Assigning the DataAdapter, customersDA, to the CommandBuilder, customersCB, causes the CommandBuilder to generate delete, insert, and update commands for the DataAdapter. It accomplishes this by analyzing the DataAdapter's select command and figuring out what the other commands should be.

The Preview Data tab (see Figure 15.8) is for testing the select query to make sure it is correct. Check the Limit Rows box and enter a number in the Rows to Fetch field to control how many records are returned when the Refresh button is clicked.

Figure 15.8. The Data Adapter Configuration Preview Data tab.

The DataSet tab (see Figure 15.9) is for associating a DataSet with this BdpDataAdapter. If there is an existing DataSet to use, choose the Existing DataSet option and select the appropriate DataSet from the drop-down list. Choosing the New option will generate a new DataSet, which will have the name that is entered into the adjacent field. The Configure DataSet editor will accomplish the same thing, but it does provide two more options. For the purpose of this example, choose the New option and name the DataSet, customersDS.

Figure 15.9. The Data Adapter Configuration DataSet tab.

After the data components have been created and configured, the DataGrid must be set to use them. To accomplish this, select the DataGrid in the Windows Form on the designer surface. There is a Data section in the Object Inspector. Select the DataSource property and change it to customersDS, which makes the tree view appear in the DataGrid. Then change the DataMember property to Table1, which makes the data appear in the DataGrid. Table1 is the logical name of the table being held in the DataSet that was generated during BdpDataAdapter configuration. If live data from the Northwind Customers table does not show up in the DataGrid in the designer, select the BdpDataAdapter component in the Component Tray and set the Active property in the Object Inspector to true.

The final task is to add the update functionality to the application so that changes in the DataGrid may be persisted to the database. This will require adding code to the Click event handler of the Update button. To accomplish this, double-click the Update button, which will open the code editor to an empty event handler shell for the Click event. This code uses the Update method of the BdpDataAdapter, which accepts parameters that include a reference to the DataSet that was bound to the DataGrid and the logical table name within the DataSet to persist. Listing 15.3 shows how this works.

Listing 15.3 An Event Handler for Persisting DataSet Changes (GuiDataComponents.cs)

 private void btnUpdate_Click(    object sender, System.EventArgs e) {    customersDA.Update(customersDS, "Table1"); } 

As shown in Listing 15.3, the only action to take is simply calling the Update method of the BdpDataAdapter with the DataSet and the logical name of the table, within the DataSet. The BdpDataAdapter has the list of commands to perform insert, update, and delete operations on the database. It also has a reference to the connection object, which it opens and closes by itself as it persists changes to the database. In practice, you should wrap your manually entered database code in try/catch blocks. Figure 15.10 shows what the application looks like when running.

Figure 15.10. A running database application.

That was the way to do this by drag-and-drop, but you may want to add components individually for working with other data providers or connecting to data sources not available in the Data Explorer. In that case, drag each data component from the Tool Palette to the designer surface individually. It may be done in the order that the code would be written. For example, start with a BdpConnection component and configure it. Next, add a BdpDataAdapter component and configure it.

SHOP TALK
LET THE GUI HELP

Even if you manually code the data layer, the GUI components can still help out. Reasons for doing so may be for more control over a multitier architecture. The BDP components and their GUI editors generate code automatically. Therefore, it could save a lot of time by dropping the components onto a temporary design surface and configuring them. Then copy and paste the generated code into a custom component.

This is what I did for part of the demos, especially the connection string. I dragged a BdpConnection over to the design surface, selected a string from the ConnectionString property in the Object Inspector, and then copied the generated string from the code.