RAD Studio for Microsoft .NET |  |

Passing Parameters in a Database Application
See Also
Collapse All

The following procedures describe a simple application that allows you to pass a parameter value at runtime to a DataSet. Parameters allow you to create applications at design time without knowing specifically what data the user will enter at runtime. This example process assumes that you already have your sample Interbase Employee database set up and connected. For purposes of illustration, this example uses the default connector IBConn1, which is set to a standard location. Your database location may differ.

To pass a parameter

1. Create a data adapter and connection to the Interbase employee.gdb database.
2. Add a text box control, a button control, and a data grid control to your form.
3. Configure the data adapter.
4. To add a parameter to the data adapter.
5. Configure the data grid.
6. Add code to the button Click event..
7. Compile and run the application.

To create a data adapter and connection

1. Choose FileNewWindows Forms Application for either Delphi for .NET or C#. The Windows Forms designer appears.
2. Click on the Data Explorer tab and drill down to find the IBConn1 connection under the Interbase node.
3. Drag and drop the EMPLOYEE table onto the Windows Form. This creates a BdpDataAdapter and BdpConnection and displays their icons in the Component Tray.
4. Select the data adapter icon, then click the Configure Data Adapter designer verb in the Designer Verb area at the bottom of the Object Inspector. This displays the Data Adapter Configuration dialog.
5. Rewrite the SQL statement that is displayed in the Select tab of the dialog to:

Copy Code

    SELECT EMP_NO, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEE WHERE FIRST_NAME = ?;

As you can see, this statement is limiting the number of fields. It also contains a ? character as part of the Where clause. The ? character is a wildcard that represents the parameter value that your application passes in at runtime. There are at least two reasons for using a parameter in this way. The first reason is to make the application capable of retrieving numerous instances of the data in the selected columns, while using a different value to satisfy the condition. The second reason is that you may not know the actual values at design time. You can imagine how limited the application might be if we retrieved only data where FIRST_NAME = 'Bob'.

1. Click the DataSet tab.
2. Click New DataSet.
3. Click OK. This creates the DataSet that represents your query.

To add a parameter to the data adapter

1. Select the data adapter icon, then expand the properties under SelectCommand in the Fill area of the Object Inspector. You should be able to see your Select statement in the SelectCommand property drop down list box.
2. Change the ParameterCount property to 1.
3. Click the (Collection) entry next to the Parameters property. This displays the BdpParameter Collection Editor.
4. Click Add to add a new parameter.
5. Rename the parameter to emp.
6. Set BdpType to String, DbType to Object, Direction to Input, Source Column to FIRST_NAME, and ParameterName to emp.
7. Click OK.
8. In the Object Inspector, set the Active property under Live Data to True.

To add controls to the form

1. Drag and drop a TextBox control onto the form.
2. Drag and drop a Button onto the form.
3. Change the Text property of the button to Get Info.
4. Drag and drop a DataGrid data control onto the form.
5. Arrange the controls how you want them to appear, making sure that the DataGrid is long enough to display four fields of data.

To configure the data grid

1. Select the data grid.
2. Set the DataSource property to the name of the DataSet (dataSet1 by default).
3. Set the DataMember property to Table1. This should display the column names of the columns specified in the SQL statement that you entered into the data adapter.

To add code to the button Click event

1. Double-click the button to open the Code Editor.
2. In the button1_Click event code block, add the following code:

Copy Code

    bdpSelectCommand1.Close();
    /* This closes the command to make sure that we will pass the parameter to */
    /* the most current bdpSelectCommand.                                                                                                                                               */

                bdpDataAdapter1.Active = false;
    /* This clears the data adapter so that we don't maintain old data                              */

                bdpSelectCommand1.Parameters["emp"].Value = textBox1.Text;
    /* This sets the parameter value to whatever value is in the text field.   */

                bdpDataAdapter1.Active = true;
    /* This re-activates the data adapter so the refreshed data appears in the data grid. */

Copy Code

    Self.bdpSelectCommand1.Close();
    /* This closes the command to make sure that we will pass the parameter to */
    /* the most current bdpSelectCommand.                                                                                                                                               */

                Self.BdpDataAdapter1.Active := false;
    /* This clears the data adapter so that we don't maintain old data                              */

                Self.bdpSelectCommand1.Parameters['emp'].Value := textBox1.Text;
    /* This sets the parameter value to whatever value is in the text field.   */

                Self.BdpDataAdapter1.Active := true;
    /* This re-activates the data adapter so the refreshed data appears in the data grid. */

If you have changed the names of any of these items, you need to update these commands to reflect the new names.

1. Save your application.

To compile and run the application

1. Press Shift + F9 to compile the application.
2. Press F9 to run the application.
3. Type one of the names John, Robert, Roger, Kim, Terri, Katherine, or Ann into the text box.
4. Click the button. This displays the employee number, first name, last name, and salary of the employee with that name in the data grid. If there is more than one person with the same first name, the grid displays all occurrences of employees with that name.

See Also

ADO.NET Overview

Data Providers for Microsoft .NET

Building a Windows Forms Database Application

Copyright(C) 2008 CodeGear(TM). All Rights Reserved.
What do you think about this topic? Send feedback!