Data Binding in Windows Forms in a Visual Basic 2005 Application
After the data is retrieved from the SQL Server database, it has to be bound to a control on a Windows Form before it is displayed in a customized format. Such a binding is termed as data binding.
When we bind the properties of a control to a data source such as the contents of a SQL Server database table, we will be able to traverse the database records.
Databinding is used to display data in a form which is a part of the Presentation layer of a 3 tier application. The book 'Database programming..' listed on this website gives you all the steps of databinding to display data in a form with controls such as Textbox control, Listbox and DataGrid control. Let us see how we can bind controls to the underlying Dataset.
We can bind controls in two ways.
- Simple data binding
- Complex data binding
How to implement simple data binding in Visual Basic.net.
The second type of databinding involves more than one value from a data source. This type of binding is called complex data binding because it works with lists of data rather than single values. By doing this, we will be able to display a list of customer names in a list box or in a Data Grid.
The following controls that are generally used to bind data from a database to a Windows Form:
| Control | Description |
|---|---|
| TextBox | This control is used for simple binding since it can display data from a single column. The Text property under the DataBindings category in the properties window is set to a specific column name of the dataset to display the data from that column. |
| ListBox | This control is used to display data for a column from multiple records of a dataset. The DataSource property of the ListBox control is used to set the dataset, and the DisplayMember property of the ListBox control is used to bind the control to a specific column of a table in the dataset. |
| ComboBox | This control has two parts, a text box for entering data and a drop-down list for displaying the data. The DataSource property of the ComboBox control is used to set the dataset, and the DisplayMember property of the ComboBox control is used to bind the control to a specific column of a table in the dataset. |
| DataGrid | This control can display data from multiple records as well as multiple columns. The DataSource property of the DataGrid control is used to set the dataset, and the DataMember property of the DataGrid control is used to bind the control to a specific table in the dataset. |
Implementing Complex Data Binding
Design a Windows Form to display the dataTo design a Windows Form for displaying the data, perform the following steps:
Create a new Visual Basic .Net (Visual Basic .Net) Windows Application project by clicking the New Project. Form1 is added to the project by default. Name the form as FrmCustomer.
Select View from the Menu bar, and then select Toolbox from the View menu. The ToolBox is displayed in the left pane of the window.
From the Windows Forms tab of the Toolbox, drag the DataGrid control to the form and name it GDCust.
Resize the inserted DataGrid control so as to display all the columns of the dataset at run time as shown below.
Fig:- 1.1. A resized Datagrid Control in the Form.
Connect to the database
Connecting to the NorthWind database involves creating a connection, a data adapter, and a dataset.Creating a data adapter
There are three methods to create a data adapter- Manually
- Through a Wizard
- Using the Server Explorer Window
Creating a Data Adapter through a Wizard
The Data Adapter Configuration Wizard helps you to set the properties of a new or existing data adapter. A data adapter contains SQL commands that your application can use to read data into a dataset from a database and write it back again. The wizard can optionally create a data connection that allows the adapter to communicate with a database. Configuring a dataadapter is one of several steps involved in using a dataset in your Visual Basic .Netapplication.To run the wizard
Drag the SqlDataAdapter object from the Toolbox onto a form. The Data Adapter Configuration Wizard Welcome screen is displayed. Click the Next button. On the next screen, click the new connection button. The Data Link Properties dialog box as shown in Fig:-1.2 is displayed, which allows you to specify a provider, server name, database name, user name, and password for the connection.
Fig:- 1.2. The Data Link properties Dialog Box.
In the Select or Enter a server name combo box under the Connection tab, enter the server name or select the server name to which the application will connect. To connect to a server named SYS1, select it from the Select or enter a server name combo box.
Next, provide the information to log on to the server. If logging on to the server requires a password, specify the password. Otherwise, log on to the server by using Windows NT integrated security.
Next, enter the name of the database from which you want to access the data or select the database on the server combo box. In this case, select the database NorthWind from the combo box. You can test if the connection has been created successfully by clicking the Test Connection button. If the connection has been created successfully, the message "Test Connection succeeded " is displayed.
Fig:- 1.3. The second screen of Data Adapter Configuration Wizard.
Click the Next button
On the next screen, specify the method through which the data adapter will access data from the database.
Fig:- 1.4. The Third screen of Data Adapter Configuration Wizard.
The Data Adapter Configuration Wizard provides three options:
Use SQL Statements - Choose this option if you want to access data through SQL statements. This option is selected by default.
Create New Stored procedures - Choose this option if you want to create a new stored procedure for accessing data.
Use Existing Stored Procedures - Choose this option if you want to access data through an existing stored procedure.
For this example, do not change the default option and click the Next button.
On the next screen, you can write the SQL statement to retrieve data or build a query by clicking the Query Builder button. Click the Query Builder button.
Fig:-1.5. The Fourth screen of Data Adapter Configuration Wizard.
When Query Builder opens, the Add Table dialog box is displayed. Select the table whose records you want to display from the list and click the Add button to add the table to Query Builder. In this case, select the Customers table from the the list and click the Add button. The table is added to Query Builder.
Fig:-1.6. Adding Table to Query Builder.
Click Close button to close the Add Table dialog box.
Click OK after selecting the fields. The designed query appears on the screen.
Fig:- 1.7. The Query designed through Query builder.
Click OK to build the query.
The last screen of the wizard displays the message that the data adapter has been configured successfully. It also displays the statements generated by the data adapter to select, insert, update, and delete the table records, as shown below. Click Finish to close Data Adapter Configuration Wizard.
Fig:- 1.8. The Last Screen of Data Adapter Configuration Wizard
A data adapter SqlDataAdapter1 and the connection object SqlConnection1 are created and then added to the component tray, as shown in the following figure. Set the Name property of the data adapter to SqlDbCustomer.
Component tray
The component tray is a separate section in the Windows Forms Designer window. This area displays the invisible controls at design time, there by allowing you to change the properties of such controls. Invisible controls include the controls that are used for establishing a database connection.Create a Dataset
To create a dataset for storing the records, perform the following steps:Select the data adapter from which you want to create the dataset and click Data from the menu bar. Select Generate DataSet from the displayed list. The Generate DataSet dialog box is displayed. Another way to display the Generate DataSet dialog box is to right-click the data adapter and select the Generate DataSet option from the shortcut menu.
The New radio button is selected by default since there is no existing dataset available. Select the tables to be included in the dataset by selecting the check box against the table name. The Customers table check box is also selected by default. Enter DSCustomer as the dataset name. Accept the default selection and click OK. The dataset is added to the component tray.
Bind the data to a Windows Form control
The data has to be bound to the DataGrid control by performing the following steps:- Select the DataGrid control and right-click it. Select the Properties option from the shortcut menu. The properties window for the DataGrid control is displayed.
- Click the DataSource property. When the down arrow is clicked, all the generated datasets are displayed in the drop-down list. Select DSCustomer1 from the drop-down list. This will bind the DataGrid control to the dataset DSCustomer1.
Fig:-1.12. The Properties Window of the DataGrid Control.
Click the DataMember property. When the down arrow is clicked, all the tables in the dataset selected will be displayed in a drop-down list, as shown in the Fig:-1.12. Select the Customers table from the drop-down list.
When you click the DataSource property of the Datagrid control, the drop-down list displays the <dataset.table.name> option as well as the <dataset name> option. If you select the <dataset.table name> option, no value is displayed for the DataMember property since the data member, that is the table name, has already been selected. However, if the <dataset name> option is selected from the DataSource property row, the DataMember property displays the tables present in the selected dataset. Although, both the methods give the same output, in the given problem statement, <dataset name> option has been selected for the DataSource property in order to explain the two options.
To fill the dataset with records from the database, write the following statement in the Load event of the form.
SqlDbcustomer.Fill(DsCustomer1)
The given statement populates the dataset with the records from the Customer table.
To view the bound data, select Debug from the menu bar. Then, select Start from the Debug menu. You can also run the application by clicking the Start icon on the Standard toolbar and view the records.
Fig:-1.13. The Output of the application.
