Question Where should I instantiate my SQL connections?

einsteinsdog

New member
Joined
Oct 29, 2012
Messages
1
Programming Experience
1-3
I have a general question about where to instantiate my SQL connections/Datasets in my application and wondered if I might be able to pick the brains of more experienced developers. Apart from a bit of reading as I go along, I have no formal education in software development though have spent the last 3 years developing a reasonably complex VBA application with probably 25k+ lines of code. Due to this I feel quite comfortable writing code but understand that I am probably woeful in terms of my architecture (for instance I have public variables everywhere). Currently I'm using VBA and have decided to try and move to VB.net going forwards. Whilst I am relatively happy with the general syntax etc of VB.net so far, I am now trying to pay more attention to the overall architecture of my applications to make them more secure and professional in their design. Anyway context over.

I connect to SQL Server extensively (examples are in ADO though will be using ADO.net soon) and in my current application basically have 2 or 3 public connections and public recordsets (conn1,record1..conn3,record3 etc) that are defined in a module (no idea why as it was hashed together in the beginning). All connections in the application are based on these 3 initial connections. As these are already dimensionalised and instantiated in the module, whenever I need to use them I just open and close as required. So I might have a form and in this I use 'record.Open sql, conn' and then 'record.close' when I've finished. As the program will crash if I don't close the connections as I go it forces me to be careful when I use them, and not leave loads of open connections, however I wondered what the general opinion should be on this.

Question1: At what level should my connections be created and how many would a typical application have? Should I create 1 public connection for each datasource I have at the highest level and use these as I go like I am at the moment? Should a public connection be created for each class? Should a new connection be created /closed in each method? Do my connections need to be encapsulated in to the method for security so that they are accessible outside of the method/class or are they OK being left as public?

Question2: If I need to create/kill connections all the time as I go through my application (lets say 100 methods in total for instance) then it seems stupid to write the whole 'dim X as new sqlconnection, set x = new sqlconnection..' 100 times in each method, so is there a way of creating a basic function/class where I can write the code once, pass a variable to it, and end up with the function instantialising a whole set of sqlconnection/sqlcommand/adapter and datasets for me?

Thanks for reading and I apologise if my terminology is incorrect or ridiculous as I am trying t to grips with all of this without much formal background.
 
It really depends how you have architected your application. Ideally you would have separate presentation, business logic and data access layers. In that case, all your data access code goes in your data access layer. You would have a repository class for each entity type and any common code would go in repository base class.

If you're just putting all your data access code in your forms though, there's absolutely no reason not to create a Data Source and then add your DataSet and TableAdapters to the form in the designer. You don't necessarily have to drag tables from the Data Sources window onto the form to set up all the bindings but, again, if you're not creating a separate data access layer then you're not following "proper" architecture rules anyway so there's no reason not to take full advantage of the designer.

If you do want to write all your ADO.NET code yourself in your forms then they should follow the same rules as everything else, i.e. give everything the narrowest scope possible. That means creating your data access objects locally unless you specifically need to use them in multiple methods. Here are some common ADO.NET scenarios and the most appropriate way to create an destroy the data access objects:

Retrieving and Saving Data in Databases
 
If I were you, I would use the awesome sauce that are Entity Framework and Linq. It makes database access completely trivial, and for large amounts of data it's a lot easier to read, write and maintain code. Plus you benefit from Intellisense everywhere in your queries.
 
Back
Top