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.
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.