Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pattikay
Starting Member
36 Posts |
 Posted - 2003-07-09 : 08:16:50
|
| I am working for a firm that has developed an application using VB 6.0It has over 80 different components.Most of this components create and maintain their own connections to the server (SQL SERVER 2000).This is ofcourse not good for performance.I would like to create a DAL (Data Access Layer) using VB or whateverelse which all components can use to connect to DB's.Is that possible?Where can I get information concerning this?Thank you in advance. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-07-09 : 12:04:41
|
| Thought I had a VB6 data access layer on my web site but guess I never got round to posting it.It's pretty simple to build and stops a lot of problems due to people doing funny things with the connection.Do you want client-server where the application maintains a dedicated connection per user or n-tier where the connectiopn is dropped after every command? I usually adopt the latter even for client-server apps.Decide what your access is going to be. Make sure that you only allow calls to SPs - no executing of sql. Easiest is to accept a command object and SP name and return an ado resultset.Most flexible is to accept a collection of parameters and return a collection but that might be a bit slow. I usually implement the parameter collection but return an ado recordset.Create a class module dbaccess - private method connect - called when someone requests a db access and there is no connection. It will have to get the connection string from somewhere - could let the app set it or get it from a standard place.Have a couple of methods GetResultSet, ExecSP which do just that but disconnect the recordset and close the connection before return and return an output paraameter collection if you don't pass via command object - parameters SP name, parameters.For the ado parameters have a method AddParameter which takes a command object and the parameter attributes - it calls a createparameter method which returns the parameter - this is where you can restrict the datatypes and also include your own datatypes.Do you want to allow multiple resultsets?I'll try to post an example if I get time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 07/09/2003 12:07:05 |
 |
|
|
|
|
|
Data Access Layer