Publish date: 2008-04-01
Updated: 2017-11-12

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

Keywords: Visual Studio 2008, VS 2005, Firebird ADO .Net provider, C#, access to InterBase

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.

Server Explorer - add firebird connection

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:

Select data source type

Further the dialog will appear:

Add connection to InterBase Firebird

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

IBProvider - Firebird Data Link

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:

Server Explorer tables list

Create application form

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

add combobox

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

Configuration wizzard. access to Firebird

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

Configuration wizzard. connect to Firebird 2

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

Configuration wizzard. Firebird connection string

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:

Configuration wizzard. Settings

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:

Combobox - Firebird data source

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:

TableAdapter. Add new query to Firebird

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

TableAdapter. Query text
NOTE
While recording the selection clause, use the positioned parameters (symbol «? «). Method code will be generated automatically so that will not bring any inconvenience.

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.

Advice
Visual Studio .Net 2008 (2005) separates the code used by a designer and the user code on the account of partial classes. In the normal application we’ll surely need to extend the TableAdapter logic. It is possible to do it, describing the partial class matching to the exact TableAdapter class generated by a designer.

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:

TableAdapter. Edit query

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

DataSet designer

In the properties list the necessary OleDbCommand objects will appear:

Table Adapter properties

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:

Data source binding

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:

Project TextBox

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.

Author: Andrew A. Merkulov  

Publish date: 2008-04-01. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link http://www.ibprovider.com/eng required.