Adding commands at runtime to generated adapters

[Guest]

So I’ve got this dataset that was built with the designer. All is well except
that the Select command needs to be generated at runtime for app-specific
reasons.

I thought I would do something clever, which was add a version of Fill() to
the dataset class that takes the SQL string as a parameter. But much to my
dismay, my designer-built dataset class is frequently rebuilt, presumably to
keep it in synch with changes to the table structure.

That’s actually quite a neat little feature but now I’m in a bind. The first
line in the generated TableAdapter’s Fill() does this:

this->Adapter->SelectCommand = this->CommandCollection[0];

Both CommandCollection and the underlying array _commandCollection are
private, as is the underlying SqlAdapter of my TableAdapter. Since I can’t
say CommandCollection[0]=this or Adapter->SelectCommand=that I’m at a bit of
a loss as to how I’m supposed to provide a Select statement at runtime.
Deriving yet another class from the generated TableAdapter whose sole purpose
is to override Fill() seems a little silly.

What am I missing here?

 

[Otis Mukinfus]

So I’ve got this dataset that was built with the designer. All is well except
that the Select command needs to be generated at runtime for app-specific
reasons.

I thought I would do something clever, which was add a version of Fill() to
the dataset class that takes the SQL string as a parameter. But much to my
dismay, my designer-built dataset class is frequently rebuilt, presumably to
keep it in synch with changes to the table structure.

That’s actually quite a neat little feature but now I’m in a bind. The first
line in the generated TableAdapter’s Fill() does this:

this->Adapter->SelectCommand = this->CommandCollection[0];

Both CommandCollection and the underlying array _commandCollection are
private, as is the underlying SqlAdapter of my TableAdapter. Since I can’t
say CommandCollection[0]=this or Adapter->SelectCommand=that I’m at a bit of
a loss as to how I’m supposed to provide a Select statement at runtime.
Deriving yet another class from the generated TableAdapter whose sole purpose
is to override Fill() seems a little silly.

What am I missing here?

I have not tested this, but I believe if you want to stick with the
Wizard built code you will probably have to do something like this...

Pseudo code:

// create a new command and add the appropriate parameters
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.Text = "Your SQL Text"
cmd.Parameters.Add("Your Parm(s)", SqlType.whatever);
//populate the parameters
..
..
..
..
//substitute the new command and it's parameters...
Adapter.SelectCommand = cmd;

End Pseudo code:


Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[Guest]

Unfortunately that's not possible as the (re)generated code has made access
to both the adapter and the array of commands it uses private. And while
Property-style access was also set up in the generated code, it is read only.
This is the crux of the problem, not the syntax of how one would go about it


At present there doesn't appear to be a way modify a generated adapter in
this way at runtime short of deriving a class from the base and, out of
necessity, overriding much of how it was created.

This seems a little clunky so I was wondering if this was in fact how people
were meant to go about it, and I'm guessing it is since the generated Fill()
function is declared virtual. It seems to me that it would be a lot simpler
to have the wizard generate read/write access to the SQL command array and
adapter.