BalCoding | Firebird Embedded access through Microsoft Enterprise Library

Now that I could create a Firebird Embedded database and the structure programmatically, I was looking for a good way to interact with the database.

I thought I would try using the Microsoft Enterprise Library Data Access Block. I found that Andrea Sansottera already worked on creating a Firebird provider.

I'm a bit silly sometimes and I wanted to really understand how it worked, so I made a provider too based on the Microsoft SQL Server provider (which is what is recommended in the documentation on extending the Data Access block by Adding a New Database Provider). Only two classes need to be built to add a Database Provider. I am integrating with the Enterprise Library-June 2005 Data Access Application Block.

There really was not much too it. I just basically replaced the Sql with Fb in most of the code and commented out some timeout stuff.

The Enterprise Library comes with a GUI configuration tool to set up my configuration files. The Data Access Block basically just takes a configuration parameter for creation of a Database object. I chose to call my configuration parameter "DatabaseDefault".

Here's the code for my pretty minimal Data Access Layer.

/*
* Created by SharpDevelop.
* User: Brian Lakstins
* Date: 9/12/2005
* Time: 6:48 AM
*
*/
using System;
using System.Data;
using System.Data.OleDb;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace BALConsultingNet.Data
{
    public class DAL {

        public static Database GetDefaultDatabase(){
           Database loDatabase = DatabaseFactory.CreateDatabase("DatabaseDefault");
           return loDatabase;
        }

        public static DataTable GetTable(string lsTableName)
        {
            Database loDatabase = DAL.GetDefaultDatabase();
            DBCommandWrapper loSelect = loDatabase.GetSqlStringCommandWrapper("select * from \"" + lsTableName + "\"");
            DataSet loData = loDatabase.ExecuteDataSet(loSelect);
            return loData.Tables[0];
        }

        public static int InsertTable(string lsTableName, OleDbParameter[] laParameter)
        {
            Database loDatabase = DAL.GetDefaultDatabase();
            DBCommandWrapper loInsert = loDatabase.GetStoredProcCommandWrapper("SP_" + lsTableName + "INSERT");

            foreach (OleDbParameter loParameter in laParameter){
                loInsert.AddInParameter(loParameter.ParameterName,loParameter.DbType,loParameter.Value);
            }
            loInsert.AddOutParameter("id",DbType.Int32,1);
            loDatabase.ExecuteNonQuery(loInsert);

            return (int)loInsert.GetParameterValue("id");
        }
    }
}

I created a little console application to insert a record using a stored procedure and then get a DataTable of all the rows in a table.

/*
* Created by SharpDevelop.
* User: Brian
* Date: 10/3/2005
* Time: 6:59 AM
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Data;
using System.Data.OleDb;
using BALConsultingNet.Data;

namespace DMLTest
{
    class MainClass
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            OleDbParameter[] laParameter = new OleDbParameter[3];
            laParameter[0] = new OleDbParameter("@namefirst","Brian");
            laParameter[0].DbType=DbType.String;
            laParameter[1] = new OleDbParameter("@namemiddle","Alan");
            laParameter[1].DbType=DbType.String;
            laParameter[2] = new OleDbParameter("@namelast","Lakstins");
            laParameter[2].DbType=DbType.String;

            DAL.InsertTable("Person",laParameter);
            DataTable loTable = DAL.GetTable("Person");
            Console.WriteLine(loTable.Rows.Count);


        }




    }
}