Print this page.  If your browser doesn't allow JavaScript, right-click this page and choose Print from the popup-menu.        
Using Mimer SQL from Delphi 8 and C# Builder
Categories: ADO.NET, ODBC, Programming Examples, Tools and Interfaces
Introduction

The Mimer Data Provider, Borland Edition for Delphi 8, give you access to Mimer SQL in Borland Delphi and Borland C# Builder. We will focus on Delphi in this paper, but the integration looks basically the same in all supported Borland tools. If you work with older versions than Borland Delphi 8 you can still access Mimer SQL with our newly released dbExpress driver. You can read more about dbExpress and Mimer SQL in the article How to use the Mimer dbExpress Driver.

Mimer Data Provider gives you both a standard Mimer Data Provider and a provider for Borland Data Provider (BDP), Borland’s own interface. The standard Mimer Data Provider works in Borland’s tools as well as in Microsoft Visual Studio and other IDEs.

If you are new to Mimer SQL, it can be a good idea to start reading the guide called Mimer SQL, Getting Started on Windows.
Description

BDP is Borland’s interface for rapid database development in .NET. In short it can be seen as a unifying interface for different Data Providers, managed or not managed.

BDP acts as a wrapper around different Data Providers and gives a single interface to work with. In standard ADO.NET you work with the different Data Providers that the database vendors ships, or with the ones that are included in the .NET framework. For example, to connect to Microsoft SQL Server you use SqlConnection and to connect to Mimer SQL you use MimerConnection. This makes applications hard to migrate between databases. With BDP you only work with the BDP interface. To connect to a database you use the BdpConnection regardless of the underlying database. Which database type to be used is defined in the connection string. If you are familiar with ADO.NET you can work as you are used to with BDP. Simply use BdpConnection, BdpCommand, BdpDataAdapter, and so on just like you would do with any other Data Provider.

The BDP interface can be implemented by database vendors to achieve full integration with Delphi 8 and other Borland tools, and this is what Mimer Information Technology have done for Mimer SQL. This means that Mimer SQL is available in all database tools based on BDP.

BDP does not only unify different Data Providers, it also adds functionality. You can for example use the ISQLMetadata interface to get information about different database objects like tables, views, and procedures. With the interface ISQLDataSources you can get information about available providers and defined datasources.

BDP is actually quite similar to Mimer Provider Manager (Mpm), another unifying interface developed by Mimer Information Technology and released as open source. Both Mpm and BDP solve the problem that arises when working with different Data Providers for different databases.

Database Explorer
Database Explorer is a program you can use to manage BDP data sources and to browse the databases. When installing Mimer Data Provider, Borland Edition for Delphi 8, Mimer SQL is listed and integrated in the same way as other supported databases.

Note! If Mimer SQL is not shown in the Database Explorer, read the section Mimer Data Provider/Mimer Borland Data Provider in the documentation installed toghether with Mimer Data Provider, Borland Edition.

You are not limited to just browse the database, you can also modify the data and commit or rollback the changes.



You can also issue arbitrary SQL commands to execute whatever statement you want.



When you define or modify a Mimer SQL data source you can use the Connection Editor to set the required properties like database to connect to, userid, and password.



The Database Explorer is integrated in Delphi 8 and it is also available as a standalone application. When integrated in Delphi 8 you can, as you will se later, use it to simplify development.


Integration with the IDE

As we said earlier Mimer Data Provider, Borland Edition for Delphi 8, is integrated in Borland’s different IDEs, both as a BDP Provider and as a standard ADO.NET Data Provider. If you or some other developer on your project is using some other IDE like Microsoft Visual Studio .NET or CSharpDevelop you should use the standard Mimer Data Provider. If not, use BDP to take advantage of the full feature set in the Borland IDEs.

The standard Mimer Data Provider works as in most IDEs - Simply drag a component onto your Form and configure it. If you, for example, drag a new MimerDataAdapter onto your form, a wizard will appear and help you configure it.

The components for BDP are available in the Borland Data Provider section of the Tool Palette in Delphi 8. The most powerful component is the BdpDataAdapter. If you are not familiar with Data Adapters you can think of them as a bridge between a DataSet (or a DataTable) and the database. A DataSet can be seen as an in memory database with tables and relations that you can read and modify. The tables in the DataSet are in the form of DataTables. The DataAdapter is used to populate the DataSet and also to make changes permanent in the database. Most GUI components in Windows Forms and Web Forms are database aware and can display DataSets and DataTables. It is the combination of DataAdapters, DataSets and the database aware GUI components that make it so easy to develop database driven applications with .NET.

Basically all you have to do to work with a DataAdapter is to specify a SELECT command and then let a Command Builder generate SQL for INSERT, UPDATE, and DELETE operations. When you use the DataAdapter you call its Fill method with a DataSet or DataTable as parameter. As soon as the data has been fetched you can work with it locally. When you have made changes to your local data, you can call the Update method on the DataAdapter. The corresponding INSERT, UPDATE, or DELETE statements are then used to save the changes in the database. To refresh the DataSet with changes made in the underlying database, simply call the Fill method again. The connection is opened and closed automatically by the DataAdapter.

When you select a BdpDataAdapter in your solution you can configure it from the Object Inspector with the Configure Data Adapter command. A dialog will be shown where you can browse among the available tables and generate or edit the SQL for the SELECT, UPDATE, INSERT, and DELETE commands. You can also preview the data and generate a DataSet. It does not matter if you work with Mimer SQL or any other database, the dialog looks the same. To use a BdpDataAdapter you need a BdpConnection. If you already have one in your solution you can select it in the dropdown list in the BdpDataAdapter Configuration dialog. If not, you create a new one in the same dropdown list. This will show you the Connections Editor where you can select among the configured BDP data sources or create a new data source using the different BDP Providers.



When you are working with BDP the Data Explorer, the integrated Database Explorer, is of great assistance. You can, for example, create a BdpConnection and a configured BdpDataAdapter by simply dragging and dropping a table onto your Form.
Example

To illustrate how easy it can be to develop a database application using BDP with Delphi for .NET in Delphi 8 we have developed a simple application. The example application shows the content of one table in a grid. The grid is updatable and you can commit the changes to the database by clicking a button. This is achieved with only a few lines of code.



The Mimer SQL database is prepared for the example by using the SQL statements given in the file database.sql.

To get started with the program we need to create a Delphi for .NET application. The following is a step by step instruction on how to create the example:
  1. In Delphi 8, create a new Windows Forms application for Delphi for .NET. This will show you a blank Form to start with.

  2. The next step is to create the needed BdpConnection and BdpDataAdapter. If you have not defined a data source for Mimer SQL, do so first. In the Data Explorer, browse to the table that you want to work with. Drag the table and drop it on your form. Now we have everything we need to communicate with the database.

  3. To make it easy to display the data in the table we will use a DataSet and a DataGrid. Right click on the BdpDataAdapter and select Generate Typed DataSet.

  4. When this is done you can drag a DataGrid and a button onto your Form.

  5. Select the DataGrid and in its properties editor change the DataSource to your new DataSet. Set the DataMember to the single table in your DataSet. As soon as you have done this you will see columns in the DataGrid and you can adjust its size to fit your table.

  6. All that is left now is to write a few line of code. Simply double click on the Form. This will create a new method and register it to handle the event that occurs when the Form is loaded. This is the method we use in our example:

    procedure TWinForm.TWinForm_Load(sender: System.Object;
    e: System.EventArgs);
    begin
    Self.DataSet11.EnforceConstraints := false;
    Self.BdpDataAdapter1.Fill(Self.DataSet11.BOOKS);
    end;

    The method above loads the data into the DataSet using the BdpDataAdapter we have configured. To avoid constraint violation errors we start by turning off the checks for constraint errors.

  7. Next we need to implement the logic for updating the database. Simply double click the button on the form and write the method. Our method looks something like this:

    procedure TWinForm.Button1_Click(sender: System.Object;
    e: System.EventArgs);
    begin
    if Self.DataSet11.HasChanges then
    begin
    Self.BdpDataAdapter1.Update(Self.DataSet11.BOOKS);
    Self.DataSet11.AcceptChanges();
    end;
    end;

    The method above first checks if there are any changes to be saved to the database. If there is, we call Update on the BdpDataAdapter with the DataSet as parameter. This will call the INSERT, UPDATE, or DELETE commands that are configured in the BdpDataAdapter to save the changes to the database. When we have saved the changes we call AcceptChanges() on the DataSet to tell that we have saved all the changes.


This is all we have to do to create a Windows application that displays and updates a table in the database using BDP, Mimer SQL, and Delphi for .NET.



Summary

With Mimer Data Provider, Borland Editon for Delphi 8, you will get full integration of Borlands different IDEs and Mimer SQL. This makes it really easy to develop robust and powerful database applications using Delphi for .NET or C#. As you have seen in the article you can simply drag a table from the Database Explorer on your Form and display it with a DataSet and a DataGrid.

Can it be easier? (if you think so, please let us know ;-))
Links

Here are the complete sample application and the statements for loading the database (right-click and 'Save Link Target As...').

You find the Mimer SQL and Mimer Data Provider products at our Download Page.

More information on Borland Products can be found at www.borland.com.


Last updated: 2004-10-12

 

Powered by Mimer SQL

Powered by Mimer SQL