SqlceDataAdapter - best practise?

irishdude11

I have an application that interacts with an sqlce database using classes like SqlCeDataAdapter, SqlCeDataTable and SqlCeConnection.

Should I A) declare all these as members in my 'DatabaseManager' class and reusing them or just declare them in the scope of functions where I work with the database?

All the examples I can find seem to just declare them in the scope of the database manipulation functions...in fact they just declare them in the scope of 'using' statements in those functions, like this -

 using (SqlCeConnection c = new SqlCeConnection( 
 Properties.Settings.Default.DataConnectionString))        
 {  
       c.Open();
         
       // Create new DataAdapter        
       using (SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT * FROM Animals", c))        
       {             
       // Use DataAdapter to fill DataTable                 
       DataTable t = new DataTable();             
       a.Fill(t);

Oh yeah by the way, Im using the DataTable as the DataContext for a DataGridView if that affects things.

Malice

First off don't use "SELECT *" in a query, just retrieve what you need and no more. Sorry but that is something that bugs the shit out of me when I see in applications I work on.

Now, for your specific questions

Should I A) declare all these as members in my 'DatabaseManager' class and reusing them or just declare them in the scope of functions where I work with the database?

What exactly are you looking to define in your DatabaseManager class? Instances of SqlCeDataAdapter, SqlCeDataTable and SqlCeConnection?
If you do that then you're going to have to look after initialisation of each object and making sure that they are closed after use.

With the using statement as provided in your code example, the instances of SqlCeDataAdapter and SqlCeConnection are closed automatically once execution hits the closing '}' and they go out of scope. Apologies if I'm stating the obvious and you already know this!

m1nder

I used to do it as follows...

I'd create a class for data access. In there I had a method to open a db connection, and a method to safely close the db connection. You could pass in a connection object as a parameter to the methods, or populate a global connection object variable (not preferable). Basically you're creating a class to do all your routine db stuff. It's code re-use, loosely coupled and easy to maintain / fix / improve on.

Declaring stuff like dataadapters and tables is fine at method level. Again perhaps create a method in your data access class for safely disposing of them that you can then call at the end of each of your functions. Its probably overkill as the GC will handle them anyway and as the previous person wrote, you can always use the "using" syntax which guarantees garbage collection. In fact you can only use this syntax on objects that support the IDisposable interface.