Subject Best practice for connecting application to FB
Author Tim Gahnström
We are writing an application that uses Firebird and I was looking for some suggestion as to what is the best practice in communicating with the DB layer.

Our application is written in VB.NET with fbembeded so my code samples are from there but the problems should be similar for any environment.

First question: All communication is done via stored procedures, selectable and executable. No other SQL is written in the application other then "SELECT * FROM SP_MYPROECURE" and "EXECUTE PROCEDURE".
The reason for this is that we get the type (and typo) checking done by firebird and we can show all dependencies etc in administration tools.

Is this sensible or is it just awkward and creating unnecessary SPs?

Another thing we do now is that for more or less every SP in firebird we have a coresponding VB function on this form:

Dim DBCon As FbConnection
Dim fc As FbCommand
DBCon = New FbConnection(sConnectionString)
fc = New FbCommand("Select * from SP_GET_ACTIVE_LETTERS(@SSECTION)", DBCon)
fc.Parameters.Add("@SSECTION", sSection).Direction = ParameterDirection.Input
fc.CommandType() = CommandType.Text
Dim fdr As FbDataReader = fc.ExecuteReader()
While fdr.Read
End While
Catch ex As Exception
Log.AddEvent("Error in deleteDBItem: " & ex.Message, 5, True)
killDBCon(DBCon, fc)
End Try

This way we get ordinary functions to work with for the rest of the application and need not to bother with FB related stuff in the business logic.

There is quite a lot of work writing these functions so the question here about best practice is twofold. Is this a good way of interacting with FB? If it is a good way are there code generators for this or do these functions need to be written by hand? Second, is it maybe better to make a one more level where the first only has a few lower level functions that takes the parameter array as input and returns an array of outputs. Then create a level on top of that that encapsulates these functions into comon typed VB functions. This would have the advantage of putting all the DB related info in one place where it can be tightly controlled and logged but it would add one more layer of complexity.

One last question, the DB is serving data directly to a user interface so latency is reasonably important but it still seems quick enough to open and kill connections to the DB for each query. This is to avoid the risk of using the same connection at the same time from different threads and whatever other isses there might be. Is this sensible or is it much better for some reason to a homemade or built in connection pooling?

In short, how do you interact with your database from the business layer? Do you have pointers for good document or ideas of what to think about how to handle the data layer in a three people sized project?

Many thanks