DataGrids - dynamic datasource attachment

[Mongr1l]

I have several questions concerning the following setup:

I have A subroutine that dynamically attaches different tables to the same datagrid.

Assume all the tables selected have a key field.
My question concerns tables that have an
autoincrement Key field

Dim ds as new DataSet, cmdSelect as New OleDBCommand 
Dim da New OleDbDataAdapter
Dim cn as New OleDBConnection(CONNECTION_STR)

  da.SelectCommand = CmdSelect


Private Sub TableAttach(byval tblName as String)
  Dim cb as New OleDBCommandBuilder

  ds.reset

  With cmdSelect
      .CommandText = "Select * from " & tblName
      .Connection = cn
  end With

  cb.DataAdapter = da  

  da.Fill(ds, tblName)

  dataGrid1.DataSource = ds.Table(0)

Question 1: Does dataset reset empties the existing table schema? My fear is that I don't want the last table undisposed

Question 2: Do I have keep creating new commandbuilder object every time I switch tables? Or can I just re-use the old one by repointing it to the same dataAdapter again?

Question 3: As I said before, all tables have a key field. The ones with an autoincrement key field, where is that one populated? In the app or only when it gets to the back end? The reason I'm asking is because I dont' want the user to enter data into that field for obvious reasons.

I could write code that would indentify such fields and disable them... but how would the could indentify those fields?

Perhaps by use of the dataset.FillSchema method?


Thoughts?


- mongril

 

[Mongr1l]

Ok, I think I answered Question 3. However, I would like everybody's opinion on the other two.

My solution to the AutoIncrement part was as follows (basically, add fillschema):

Dim ds as new DataSet, cmdSelect as New OleDBCommand
Dim da as new OleDBDataAdapter
Dim cn as New OleDBConnection(CONNECTION_STR)

  da.SelectCommand = cmdSelect

Private Sub TableAttach(byval TblName as String)
  Dim cb as New OleDBCommandBuilder

  ds.Reset()
  
  With cmdSelect
     .CommandText = "Select * from " & TblName
     .Connection = cn
  End With

  cb.DataAdapter = da
  da.FillSchema(ds, SchemaType.Source, TblName)

  da.Fill(ds, TblName)

  dataGrid1.DataSource = ds.Tables(0)

  
End Sub

However, I would still like some good opinions on effective instantiation, such the command builder re-use and dataset table management.

My concern (as I'm sure it is every programmer's) is that I don't want a bunch of disconnected instantiated objects floating around in memory.

Basically, I'd like to keep things as tidy as possible with respect to memory.

Thoughts?

- mongril

 

[Mongr1l]

Ok, I tried a commandBuilder.dispose() at the very end of the subroutine, but it seems like that disposed the auto generated Update, Delete, Append objects for the DataAdapter.

I also tried moving the command builder to the general declarations for the class, but that also gave me errors when I tried to attach another table.

So I'm thinking I need the subroutine to create new instances of the command builder every time a table is selected for attachment to the grid.

Won't this fill the memory with a bunch of unused command objects? Or am I missing something?


Ah! I think I have it. Prior to the creation of new command builder, I can write in a check to see if a previous one already exists. If it does, then I dispose of it before I create a new one.

The other ADO objects seem to be fine with re-use.

This seems like a sound strategy. It isn't, please let me know.

- mongril

 

[Mongr1l]

Ok, I found the solution.

I declared a command builder variable (not instanced) in the general declarations area.

In the TableAttach subroutine, at the very beginning, I put the following code:

  If Not (cb is nothing) then cb.dispose()
  cb = New CommandBuilder

This worked well.


Cheers!!

- mongril