Find a book on .NET design patterns. For general software design the bible is Design Patterns by Gamma et al. You'd be looking for Data Access Object patterns.

Specifically, never use '*', only request the columns you need. It saves bandwidth and more importantly errors when columns are added.

Try using Views instead of selectable stored procedures where stored procedures aren't required.

Connections are light enough in Firebird that you don't need a connection pool unless your load is very large. A connection pool can also be used to throttle total concurrent connections, so if that is a requirement you'll need one.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tim Gahnström
Sent: Wednesday, October 05, 2005 8:16 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Best practice for connecting application to FB

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
Try
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
cLetters.Add(fdr.GetString(0))
End While
fdr.Close()
Catch ex As Exception
Log.AddEvent("Error in deleteDBItem: " & ex.Message, 5, True)
Finally
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

Tim




------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income households are not online. Help bridge the digital divide today!
http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links