Interbase and Visual C# .NET

• Download source (ado2) - 1.69 MB
• Download source (spClass) - 1.35 KB

Introduction

In a university project I had to manage an interbase, Oracle and SQL Server database with C#. Oracle and SQL Server was easy but when I was searching for some information about connecting C# with Interbase, it was like hell. I had to read a lot of articles because I had never done it. But a few days later, bingo!

Background

The first problem faced was to find the correct database Driver. I was searching a lot and found IBProvider (free version doesn't work), ADO 1.0 (IB Driver). Then I found the ADO 2.0 Driver (only works with Windows XP and IB2007). ADO 2.0 from IB let me take a breath because I thought that I'll never find it.

The zip file includes ADO.NET 2.0 Driver for CodeGear Interbase, information about how it works. But the second problem was "how does it really work?" First install the driver, then Edit the machine.config file from the C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG directory.

Add the following line in the section labelled <DbProviderFactories>:

<add name="InterBase ADO.NET 2.0 Data Provider" invariant="Borland.Data.AdoDbxClient" 
	description=".Net Framework Data Provider for InterBase" 
	type="Borland.Data.TAdoDbxProviderFactory, Borland.Data.AdoDbxClient, 
	Version=11.0.5000.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b"/>

Start Visual Studio 2005.
File new C# Windows application.
Project- Add Reference and add the AdoDbxCleint.dll to your project.

Using the Code

The file spClass includes 2 classes:

• spClass contains two methods (1 overloaded)
• axUsu contains eight methods (created to understand how this driver works)

spClass methods are the ones that get the TAdoDbxCommand (get the connection and send a simple query). getDataIB gets the result of a query (like SELECT * FROM...)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Borland.Data;
using Borland.Vcl;
using Borland.Data.Units;
using System.Data.SqlClient;
using System.Data.Common;
class spClass
    {
        public static TAdoDbxCommand getComdIB()
        { 
           //The string connection can change
            TAdoDbxConnection c = new TAdoDbxConnection(
                "DriverName=Interbase;Database=192.168.1.101:C:\\" +
                "Farmacia\\Farmacia.gdb;RoleName=RoleName;User_Name=sysdba;" +
                "Password=masterkey;SQLDialect=3;" +
                "MetaDataAssemblyLoader=Borland.Data.
			TDBXInterbaseMetaDataCommandFactory," +
                "Borland.Data.DbxReadOnlyMetaData,Version=11.0.5000.0,Culture=neutral," +
                "PublicKeyToken=91d62ebb5b0d1b1b;GetDriverFunc=getSQLDriverINTERBASE;" +
                "LibraryName=dbxint30.dll;VendorLib=GDS32.DLL");
            TAdoDbxCommand cmd = new TAdoDbxCommand();
            cmd.Connection = c;
            return cmd;
        }
        public static TAdoDbxCommand getComdIB(string query)
        {
            TAdoDbxConnection c = new TAdoDbxConnection(
                "DriverName=Interbase;Database=192.168.1.101:C:\\Farmacia\\" +
                "Farmacia.gdb;RoleName=RoleName;User_Name=sysdba;Password=masterkey;" +
                "SQLDialect=3;MetaDataAssemblyLoader=" +
                "Borland.Data.TDBXInterbaseMetaDataCommandFactory," +
                "Borland.Data.DbxReadOnlyMetaData,Version=11.0.5000.0," +
                "Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;" +
                "GetDriverFunc=getSQLDriverINTERBASE;LibraryName=dbxint30.dll;" +
                "VendorLib=GDS32.DLL");
            TAdoDbxCommand cmd = new TAdoDbxCommand();
            cmd.Connection = c;
            cmd.CommandText = query;

            return cmd;
        }
        public static DataTable getDataIB(DbCommand cmd)
        {
            cmd.Connection.Open();
            DbDataReader myreader = cmd.ExecuteReader();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            ds.Tables.Add(dt);
            ds.Load(myreader, LoadOption.PreserveChanges, ds.Tables[0]);
            myreader.Close();
            return ds.Tables[0];
        }
    }        

Now we have done the essential method to get a IBCommand, and a Datatable.

axUsu class includes methods to insert, delete, update, get Users, from a database named Farmacia.dgb in a remote server but we can focus on one method: Insert method(addUsr):

public void addUsr(string usr, string pwd, string nombre, string nivel)
        {
            string query = "INSERT INTO USUARIOS(USR, PWD, NOMBRE, NIVEL)"
                + "VALUES(" + usr + "," + pwd + "," + nombre + "," + nivel + ")";
            TAdoDbxCommand cmd = spClass.getComdIB(query);
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
        }

In this method, we can identify an TAdoDbxCommand object named cmd that has an initial value from spClass.getComdIB(query). It means calling the method and getting the cmd object with connection and commandText properties initialized. Then open connection, execute the query and close connection, we have made a successful query.

If we want to get all the users in the table named users in a Windows Form, we can get them by writing this code:

axUsu users = new axUsu();

DataTable UsAll= users.getUsrAll();
dataGridView1.DataSource = UsAll;

Insert method:

axUsu addUsr = new axUsu();
addUsr.addUsr("User1",axUsu.cifrarSHA1("Password"), "Name", 1);

It's easy when we have completed the methods. I hope it will be useful.

History

• 11^th June, 2008: Initial post