Solved

ADO.NET, data adapter, datatable, commandbuilder questions

Posted on 2006-06-15
3
803 Views
Last Modified: 2006-11-18
Hi all,

I currently use the following approach to using the ADO.NET framework for accessing my access database:

 sSQL = "SELECT Statement"

dt = new datatable
da = New OleDb.OleDbDataAdapter
cbCommandBuilder = New OleDb.OleDbCommandBuilder
da.Update(dt)

My question is when I want to destroy the dt datatable do I have to also destroy the commandbuilder object and the data adapter object?   I expect that all these object will be destroyed also when they go out of scope.

Also, I was wondering if I destroy object dt (datatable) and then recreate it.  Do I end up with existing data from the previous object?  Will I need to clear it?

Also If I destroy the dt object but not the da or cbCommandBuilder objects....and then I create a new dt object will the existing link to the commandbuilder object work with the new instance of the dt object or would I have to create a new data adapter and command builder object for the newly created datatable?

I hope these questions make sence.

Thanks




0
Comment
Question by:mike_allain
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 125 total points
ID: 16916612
When you destroy your datatable you do not have to destroy the dataadapter and/or the commandbuilder.  Any object, including a dataadapter and a commandbuilder will be destroyed when it goes out of scope.  But, for instance, if you give your dataadpater a form wide scope and then apply the commandbuilder to it within a more limited scope, the dataadapter will while it (not the commandbuilder) remains in scope retain the Insert, Delete and Update commands that the commandbuilder "gave it".

If a datatable is destroyed - either explicitly by dt=Nothing, or implicitly by going out of scope - it will not only lose its data it will also lose its structure: it is not an object at all any longer.  If the datatable is not destroyed, you can make it lose its data, but retain its structure, with .Clear().

There is no permanent link between a dataadapter and a datatable.  So, if you destroy a datatable you can then use the same dataadapter to (create and) fill another datatable.  I say "(create and) fill" because, while there must be an existing datatable for the dataadpater's .Fill method - which you don't mention in your code - to operate on, that .Fill method will, if necessary, give any new datatable the structure that its SelectCommand requires.

I hope, if your question did make the sense I think it did, the above gives sensible answers ;-)

Roger
0
 

Author Comment

by:mike_allain
ID: 16926653
Thanks Roger

Its what I was looking for.... one quick question however I noticed you used dt=nothing.  Is this still the proper way od destroying an object or should I use dt.Dispose()

Mike
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16926742
Mike

Garbage collection is not my strong point ;-)

For general purposes, I don't think it matters which you use.  I say "for general purposes" because there can be occasions when you specifically want an object's "dispose" sub to fire - see this

   http://msdn2.microsoft.com/en-us/library/2z08e49e.aspx

By and large, though, whether you Dispose, or set to Nothing, or simply let something go completely out of scope, my understanding is that the GarbageCollector will eventually tidy it away and reclaim its resources.  It will do this sooner if resources are getting short, or just when it gets round to it if there is no pressure on resources.  I let it get on with it, using .Dispose or Nothing as the mood takes me with no real rhyme or reason, and have never encountered a problem.  But, as I say, I'm not an expert in relation to it and it may be that one way is "better" than the other.

Roger
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question