InterBase and Firebird Developer’s manual for ADO.NET. Part 2. Using code generators and tools of Visual Studio 2008 (2005)

Create connection in Server Explorer

Visual Studio built-in tools may provide the important help while writing database applications. Let’s follow the steps of the example of JobManager simple application.

Choose the «Add Connection» command in Server Explorer. The dialog with the data source list will be opened. There we choose Data Source = <other> and Data provider = .Net Framework Data Provider for OLE DB. Press Ok:

Further the dialog will appear:

Choose IBProvider v.3 from the OLE DB providers list. Press the Data Links button:

Here there are two important moments: it’s necessary to turn on the options «Allow automatic transactions» and «Allow saving passwords». Get assured that everything is setup correctly, pressing the button «Test connection».

If we did everything correctly, in the Server Explorer connections list a new one with the list of database objects:

Create application form

Create a new JobForm. Add ComboBox on it and go to ComboBox Tasks editor:

Then you should choose «Add Project Data Source» action in Data Source property. The data sources creation wizard will be opened:

Choose source type Database and go next. In connection list there will be a created before in Server Explorer connection:

Do everything as it is shown on the picture and start the next step:

Wizard offers us to save the data source parameters in application configuration file. A connection string will be saved in «Application Settings» area by default and will not be available for editing inside the application. The behaviour may be changed by setting the Scope property = User. But now we only create section in app.config file and move further:

As a final step we are offered to create DataSet. Let’s choose all the available database tables for it and press «Finish» button.

Now we choose the EMPLOYEE table as a data source for our ComboBox. We set FULL_NAME value to DisplayMember property:

After finishing the operation, three new components will be added to the form. They are DataSet, TableAdapter and BindingSource. We already know DataSet, two other objects appeared only in Net 2.0 and will be viewed below.

TableAdapter class

It is a main class in the binding between database tables and user controls. If to make a parallel with Martin Fawler terms, TableAdapter is a data table gateway for DataTable. It encapsulates load, update and search logic and refers to Data Access Layer. As for DataSet and DataTable they can be referred to Business Layer.

Visual Studio .Net 2005 can generate code for the specified TableAdatapter class. Let’s see what it offers to us:

• Fill() and GetData() methods — the only one difference is that Fill takes the existing DataTable as an argument and GetData() creates the new one, fills it with the data and returns to the consumer.
• ClearBeforeFill property which is used by mentioned before methods for defining if it is needed to clear the table before its filling.
• Standard set of CRUD operations: insert, update, delete among which the overloaded methods with type safe arguments matching database table exist.
• Common property — Connection

We also have the possibility to create the additional queries to database. For this we need Search Criteria Builder tool. To run it, it is necessary to choose «Add Query» in menu at our table adapter:

Enter the query name (actually, new method name in your TableAdapter) and its text either manually stating the selection clause or using the tool:

After all the operations the appropriate method will be generated. Also VS .Net 2005 designer will add ToolStrip component to your form with the button of this method running and the field for filter setting. I think it is excessive but maybe somebody will like it.

The transfer of changes to the database via TableAdapter

Separately it is necessary to pay attention to changes transfer to the database methods. Visual Studio .Net can generate the queries code for insert, update, delete methods. In some cases it can be enough, but due to the experience, SQL expressions received try to pretend for the universality and thus they are not optimal and even not workable.

Show the example: in a delivery with Firebird there is an employee.fdb database. It has a SALES table. I pay attention to AGED field which is available for reading only, as it is calculated with the help of the expression.

COMPUTED BY (ship_date — order_date)

If we are set Select Command text as:

SELECT * FROM SALES

The AGED column will be added to the all commands. While trying to transfer the changes to database, the exception will be generated. It is necessary to edit the queries text for insert, update, delete manually and to delete this column from the update.

To edit SQL expressions it is possible to call the «Edit Queries in DataSet designer» command:

The DataSet designer will be opened, it is needed to choose the appropriate TableAdapter in it (in our case it is SALESTableAdapter):

In the properties list the necessary OleDbCommand objects will appear:

The reader may think that this case is an exception, but it is not so. If you choose this way of Data Access Layer creating, get ready to constant interference to automatic process of SQL queries generation.

BindingSource

With the appearance of Net 2.0 Data Binding technology got its further development. A new class — BindingSource appeared. It is a proxy-object between the data providers and user control elements. Now the user control elements do not refer directly to data storages (DataTable, DataSet, DataView). They refer to the BindingSource object.

This method allows lazy loading implementation and synchronization between many user controls which used same BindingSource.

Let’s give the real example which demonstrates all advantages of the BindingSource object:

We’ll add the new control element — DataGridView and display the list of projects in which the current employee chosen as the chief. PROJECT and EMPLOYEE tables are connected via TEAM_LEADER field upon the INTEG_36 foreign key. Let’s bind user control and data source:

Find BindingSource for the EMPLOYEE table in the data sources list and request the designer to create the new BindingSource for the foreign key relation between projects and employees upon the external INTEG_36 key:

We’ll add TextBox element for binding the project description which is stored in BLOB field PROJECT_DESCR of PROJECT table. To connect it with selected project in the DataGridView list, it is necessary to set Binding object for Text property:

Adding the Data control logic

We learnt how to display, bind and edit data. Now let’s finish our example and learn the application to transfer changes back to the employee database.

The only one edited field that we have is the current project description. To record the changes made in it we’ll use the TableAdapter.Update() method for PROJECT table. Add Button control to the main form and write down the following code in Click event handler:

private void btnSaveChanges_Click(object sender, EventArgs e)
{
        try
        {
            this.pROJECTTableAdapter.Update(this.jobDataSet.PROJECT);
            MessageBox.Show(«Save was successful»);
        }
        catch (Exception exception)
        {
            MessageBox.Show(exception.Message);
        }
}

Also let’s implement rollback changes method. Add one more button and in Click event handler place the code which will rolback all changes in DataSet made since the last accept changes:

private void btnUndoChanges_Click(object sender, EventArgs e)
{
       this.jobDataSet.RejectChanges();
       //refresh TextBox data
       this.iNTEG36BindingSource.CurrencyManager.Refresh();
}

While coding this handler I encountered the following problem: after editing data via TextBox the changes do not transferred to DataSet and DataSet.HasChanges() method always returns false. To solve this problem it is necessary to add the following code to TextBox.Validate event handler:

private void textBox1_Validated(object sender, EventArgs e)
{
        this.iNTEG36BindingSource.EndEdit();
}

Complied application JobManager is available in archive with the examples to the article.