DLLs and Database Connections

[jpastika]

I am new to Visual Basic and OOP, but not new to programming in general. I am starting to work on a Database application, and I need some advice on how to proceed in two main areas, DLLs and Database connections. Here are my two questions:

1) What are the advantages/disadvantages to using DLLs?
I am considering encapsulating all Database logic, Business logic, and GUI logic into separate DLLs. The reasons I am considering this are that I "think" it would be easier to maintain and enhance the application if I can simply update a DLL and send out the new DLL to the users (this is an internal application). Am I wrong in thinking this? Are there other advantages to using DLLs or are there any disadvantages to using DLLs for the purpose I am thinking about?

2) I know I want to use ADO to connect to the SQL Server Database, but I'm not sure about the best/most efficient way of doing this. I don't want to have each user creating 100 database connections while using the application just because some combo boxes will be populated from tables and each form will be used to add or update data in the Database. So, should I create a Database connection class in which the connection to the database is created. If so, where do I need to create an instance of that class so I can reference the connection throughout the project thereby causing each user to only have 1 connection to the Database? I apologize if I am not being clear. Basically I want each user to only have 1 connection to the Database and I need to know how to do that.

I appreciate any help anyone can give me. You guys are a heck of a lot better at this stuff than I am. Thank you!

-Jeremy

 

[steveblum]

Regarding question 2, use just one connection. Either keep the connection in a class, and then call that class to do anything with the database, or have a connection object defined globally, and then use that object for any database calls. As much as possible use client side, read-only, disconnected recordsets. To disconnect the recordset, define a new recordset, set the connection to the connection object, read the data, then set the connection of the recordset to nothing. This will disconnect the recordset from the database. This is the most efficient use of database resources.

The only time this won't work is if you plan to update the database directly with that recordset later, in which case it will need to be dynamic and stay connected. But this method is generally not advised. It is better to update the database with sql commands or better yet stored procedures, and then just re-read any recordsets that could have changed.

 

[jpastika]

Thank you for the advice! I figured a class or global connection would be the way to go, and your explanation really helped. I hadn't really thought about disconnecting the recordsets. Thank you for that advice.

-Jeremy