locked
TableAdapter Query RRS feed

  • Question

  • Is there a way to change the command of a query in a TableAdapter programatically?

     

    i need to change the WHERE statement base on a "Statement Builder" for "smart" querys that i have in my application

    Wednesday, March 12, 2008 7:22 PM

Answers


  • Check this link for a similar question: http://forums.microsoft.com/MSDN/ShowPost.aspx?siteid=1&PostID=640116

    Also check in your DataSetName.Designer.cs file, look for "private void InitCommandCollection()"

    You can create a public method in the TableAdapter class to add your own queries to the:  this._commandCollection[]

    eg.

    private void AddQueryToCommandCollection(string myQuery)
    {
      int idx = this._commandCollection.Length;
    this._commandCollection[idx] = new System.Data.Odbc.OdbcCommand();
    this._commandCollection[idx].Connection = this.Connection;
    this._commandCollection[idx].CommandText = myQuery;
    this._commandCollection[idx].CommandType = System.Data.CommandType.Text;
    }

    the myQuery string should contain your whole SQL query with the WHERE clause and values.
    eg.  "SELECT Index,Item,Price,Seller FROM Products WHERE (Seller LIKE '%free%')"

    Of course you should be careful not to mess up the TableAdapter class, I haven't actually tried this myself
    but I have the same problem and just thought of this possible solution a couple of minutes ago.

    Check the rest of the class to make sure you know how to call the right query from the: this._commandCollection[] array.

    Let me know if it works for you.


    Thursday, March 13, 2008 11:04 AM
  • First of all, thanks for the reply Twizted . . .

     

    I followed your advise and after about 15 mins reviewing the [ DataSetName ].Designer.cs file, figured out a easier way to achive it.

     

    I added a query without any filter called CustomFill the "normal way" (in the dataset designer). Then created the following method in the adapter class i wanted to customize:

     

    public void CustomizeFill(string strCommand)

    {

    if (strCommand.Length > 0)

    {

    this.InitCommandCollection();

    this._commandCollection[1].CommandText = strCommand;

    }

    }

     

    As you can figure out, _commandCollection[1] its the query i added in the dataset designer.

     

    Then, every time i want to use this on a Form i do the following:

     

    this.tableAdapter.CustomizeFill(strSelect + " " + strFrom + " " + strWhere);

    this.tableAdapter.CustomFill(this.dataSet.dataTable);

     

    Basically it changes the select query of the TableAdapter in run time.

    Thursday, March 13, 2008 7:37 PM

All replies


  • Check this link for a similar question: http://forums.microsoft.com/MSDN/ShowPost.aspx?siteid=1&PostID=640116

    Also check in your DataSetName.Designer.cs file, look for "private void InitCommandCollection()"

    You can create a public method in the TableAdapter class to add your own queries to the:  this._commandCollection[]

    eg.

    private void AddQueryToCommandCollection(string myQuery)
    {
      int idx = this._commandCollection.Length;
    this._commandCollection[idx] = new System.Data.Odbc.OdbcCommand();
    this._commandCollection[idx].Connection = this.Connection;
    this._commandCollection[idx].CommandText = myQuery;
    this._commandCollection[idx].CommandType = System.Data.CommandType.Text;
    }

    the myQuery string should contain your whole SQL query with the WHERE clause and values.
    eg.  "SELECT Index,Item,Price,Seller FROM Products WHERE (Seller LIKE '%free%')"

    Of course you should be careful not to mess up the TableAdapter class, I haven't actually tried this myself
    but I have the same problem and just thought of this possible solution a couple of minutes ago.

    Check the rest of the class to make sure you know how to call the right query from the: this._commandCollection[] array.

    Let me know if it works for you.


    Thursday, March 13, 2008 11:04 AM
  • First of all, thanks for the reply Twizted . . .

     

    I followed your advise and after about 15 mins reviewing the [ DataSetName ].Designer.cs file, figured out a easier way to achive it.

     

    I added a query without any filter called CustomFill the "normal way" (in the dataset designer). Then created the following method in the adapter class i wanted to customize:

     

    public void CustomizeFill(string strCommand)

    {

    if (strCommand.Length > 0)

    {

    this.InitCommandCollection();

    this._commandCollection[1].CommandText = strCommand;

    }

    }

     

    As you can figure out, _commandCollection[1] its the query i added in the dataset designer.

     

    Then, every time i want to use this on a Form i do the following:

     

    this.tableAdapter.CustomizeFill(strSelect + " " + strFrom + " " + strWhere);

    this.tableAdapter.CustomFill(this.dataSet.dataTable);

     

    Basically it changes the select query of the TableAdapter in run time.

    Thursday, March 13, 2008 7:37 PM