Change SelectCommand on runtime

Question

I've created on my dataset a tableadapter with the next select command

select count(*) as cuenta from table1.

But, on the runtime, I need to compose different selects with variable number of clauses. I'd like to use the same tableadapter but I'm not able to change the select command.

This is a little example of my problem:

Original select command for the dataadapter "EXISTE" on DataSource"ds":

select 1 as cuenta(*)

On designer I drop the dataadapter to create an instance on my form: "EXISTE1"

Code:

EXISTE1.Adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select 2255 as cuenta");
EXISTE1.Fill(ds.EXISTE);

But it always returns me "1" as result, not 2255.

How can I run the other command without creating a new Fill function?

Best Regards

Answer 1

The solution for this problem is to fill the dataset from Adapter instead of TableAdapter. This is working well for me.

EXISTE1.Adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("select 2255 as cuenta");
EXISTE1.Adapter.Fill(ds.EXISTE);

Answer 2

You're supposed to add more queries to the tableadapter by right clicking it in the designer and choosing "add query" though from the posted query it would be simpler to just insert the value 2255 into your datatable rather than run a select query that selects a single constant.

myDataset.EXISTE.AddEXISTERow(2255);

If you look at the designer.cs you'll see that the multiple queries a TA knows about are kept in a collection and the relevant one is loaded into the select command before every fill, which explains why your efforts have no effect; your new select command is overwritten when you fill

If your query is truly dynamic in a way that cannot be scripted ahead of time it might be neatest to just use a regular DataAdapter with your dynamically built command and custom parameter set to fill the table. You could add this as code into the tableadapter by double clicking on the tableadapter in the designer, to open the code behind (the designer creates a partial class so it's easy to extend) and add something like:

void FillByVariable(EXISTEDataTable dt, string sql, Dictionary<string,object> p){
  using(var da = new XxxDataAdapter(sql, this.Connection.ConnectionString) //new da that uses the same conn as the TA
  {
    foreach(var kvp in p)
      da.SelectCommand.Parameters.AddWithValue(kvp.Key, kvp.Value); //demo code, read Joel's "can we stop using AddWithValue" blog and make something better if you use sql server
  }
  da.Fill(dt)
}