none
subclassing a tableAdapter to view CommandCollection RRS feed

  • Question

  • A recent discussion on this forum asked whether you could access the SQL command in various FillBy statements associated with a tableadapter. The conclusion was 'No'. However, I have the same need and it seems like it should be possible through subclassing.

    For example, assume Visual Studio has created a memberTableAdapter. I added a class with two 'protoype' ways to access the CommandCollection. This is really rough code - I am new to Visual C# and tableadapters. Will either of these methods not expose the CommandCollection? If the answer is yes perhaps someone could tweak the code to make it actually work - neither one shows up in intellisense.

    My ulitmate goal is to modify the SQL select statement such that I can use an "IN" clause (with an arbitrary numbers of parameters) as part of the 'where' statement.

    By the way: you can access the SQL Select command text as a normal property of the tableadapter but only after the tableadapter has executed a FillBy, which is clearly after the fact. I want to change the command before it executes. (I suppose as an ugly work-around you could issue a 'fake' FillBy, get the command text, change it, then issue a FillBy with your actual parameters??)

    -----------------------------------------------------------------------------------
    namespace  InfoMaster.DataSets
    {
        public partial class memberTableAdapter : global ::System.ComponentModel.Component
        {
            private global ::System.Data.OleDb.OleDbCommand [] _commandCollection;

    //Option 1
            public string GetCommand(int num)

            {
                 return _commandCollection[num].ToString();
            }
    //Option 2
            public override global ::System.Data.OleDb.OleDbCommand [] CommandCollection {
            get
            {
                   return this ._commandCollection;

             }
         }
    }

    }

    ----------------------------------------------------------------------------------

    Thursday, March 18, 2010 3:14 PM

Answers

  • Got Option #1 to work. Main issue with intellisense was I forgot to specify which Dataset in the namespace argument. The only unfortunate aspect of this is you have to know the index number of your FillBy command but the order matches that in DataDesigner.

    Here is the working partial class code:

    namespace  InfoMaster.DataSets.chelaDataSetTableAdapters
    {
        public partial class memberTableAdapter : global ::System.ComponentModel.Component
        {
              public OleDbCommand GetCommand(int num)
             {
                 return this.CommandCollection[num];
             }
             public void SetCommand(int num, OleDbCommand SQL)
             {
                 this._commandCollection[num] = SQL;
             }
        }
    }

    The calling program can now manipulate SQL.CommandText and then reSET it. The only issue I had with modifying the IN clause is that you can't create the FillBy clause using IN (?). If you search for and replace the (?) of-course Visual Studio will complain about a parameter mismatch since it wants to replace the ?. So, I just used another arbitrary placeholder like IN (0).
    • Marked as answer by DreamU Thursday, March 18, 2010 5:36 PM
    Thursday, March 18, 2010 4:56 PM

All replies

  • Got Option #1 to work. Main issue with intellisense was I forgot to specify which Dataset in the namespace argument. The only unfortunate aspect of this is you have to know the index number of your FillBy command but the order matches that in DataDesigner.

    Here is the working partial class code:

    namespace  InfoMaster.DataSets.chelaDataSetTableAdapters
    {
        public partial class memberTableAdapter : global ::System.ComponentModel.Component
        {
              public OleDbCommand GetCommand(int num)
             {
                 return this.CommandCollection[num];
             }
             public void SetCommand(int num, OleDbCommand SQL)
             {
                 this._commandCollection[num] = SQL;
             }
        }
    }

    The calling program can now manipulate SQL.CommandText and then reSET it. The only issue I had with modifying the IN clause is that you can't create the FillBy clause using IN (?). If you search for and replace the (?) of-course Visual Studio will complain about a parameter mismatch since it wants to replace the ?. So, I just used another arbitrary placeholder like IN (0).
    • Marked as answer by DreamU Thursday, March 18, 2010 5:36 PM
    Thursday, March 18, 2010 4:56 PM
  • Awsome workaround!  I'm using it... Thank you!
    Friday, March 26, 2010 3:26 PM
  • Usage example:

    List<string> selection = new List<string>(new string[] { 1, 2, 3 });
    OleDbCommand command = this.SomeTableAdapter.GetCommand(0);
    command.CommandText = String.Format("SELECT Id, Something FROM SomeTable WHERE Id IN ({0})", String.Join(",", selection.ToArray()));
    this.SomeTableAdapter.SetCommand(0,command);
    this.SomeTableAdapter.Fill(this.SomeDataSet.SomeTable);


    • Edited by rootik Monday, April 15, 2013 3:00 PM
    Monday, April 15, 2013 2:46 PM