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

What returns "1"? Are you saying `EXISTE` has one row with one column and it's value is "1"? Because `Fill()` will return how many rows were added or refreshed and that would be 1. — Crowcoder, Mar 13 '18 at 12:45
It's only a simple example to explain the problem. I want to have multiple queries dynamically constructed on runtime and exec them on a tableadapter I have from design mode. In the example, the original query will return 1 row with 1 column and value 1, and the code query should return 1 row with 1 column and value 2255 — davidrgh, Mar 13 '18 at 12:54
That should work, I'm trying to determine if you are looking at the right thing to determine success. — Crowcoder, Mar 13 '18 at 12:56
The purpose I'm trying to achieve is to having a query in this style: select column1, column2 from table where (condition1 = value1) and (condition2 = value2) and (condition3 = value3) and ...... and (conditionN = valueN) where "N" is not given untill the program runs — davidrgh, Mar 13 '18 at 12:59

score 0 · Answer 1 · edited May 10 '20 at 10:25

0

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);

edited May 10 '20 at 10:25

David Buck

3,752
35
31
35

answered May 10 '20 at 09:42

Mohammed Abdel kader

1

Caius Jard · Answer 2 · 2020-09-16T05:53:58.510

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)
}

Change SelectCommand on runtime

2 Answers2