none
Amending TableAdapter Fill CommandText at runtime RRS feed

  • Question

  • I want to be able to amend the SQL for my TableAdapter Fill() at runtime (so that I can filter based on related tables).  [I am using an Access database.]

    I know that this is not straightforward since the properties are not normally exposed at runtime.

    I found the following (VB) Code that is supposed to do the trick:

    Namespace BioProcDBDataSetTableAdapters
    Partial Public Class bioTableAdapter


    Public Sub MyCode()


          Me.CommandCollection(2).CommandText = "Select * FROM Bio WHERE lname = 'Smith' "


    End Sub


    End Class
    End Namespace

    I have tried doing this in C# using the following:

        public partial class PeopleTableAdapter
        {
            public void SetSQLx(string theSQL)
            {
                this.CommandCollection[1].CommandText = theSQL;
            }
        }
    

    However, this generates the error:
    Error 1 'TestTableRelations.PeopleTableAdapter' does not contain a definition for 'CommandLine' and no extension method 'CommandLine' accepting a first argument of type 'TestTableRelations.PeopleTableAdapter' could be found (are you missing a using directive or an assembly reference?)
    I currently have the following using directives:
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Text;
    using System.Collections;
    using System.Collections.ObjectModel;
    using System.Collections.Specialized;
    using System.ComponentModel.Design;
    using System.Data.OleDb;
    using System.Data.Common;

     

    In desparation, I have even tried changing the CommandCollection property to public in xxxDataSet.Designer.cs, but this has not helped:

     

    public global::System.Data.OleDb.OleDbCommand[] _commandCollection;
    ...
    public global::System.Data.OleDb.OleDbCommand[] CommandCollection {...

     

     

     



    I have not had much luck finding documentation on TableAdapters or CommandLine.

    What can I do to get this to work?

    I hope I have posted this in the right forum - if not, please tell me.

    Tuesday, October 13, 2009 10:15 AM

Answers

  • TableAdapters are a major PITA ... sorry, I just do not like them (and do not use them).

    Anyway, here's where your problem is ... the TableAdapters are in a different namespace than your DataSet. You probably are putting your partial class in the wrong namespace, that's all (using statements won't help you here).

    So, for example, say that you have a DataSet called CustomerDataSet and it is created under the namespace MyApp.DataSets. The namespace for your TableAdapters would NOT be MyApp.DataSets, it would be MyApp.DataSets.CustomerDataSetTableAdapters. Use that namespace for your partial class and everything should be fine.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Phil Gameson Friday, October 16, 2009 4:24 PM
    Friday, October 16, 2009 3:41 PM

All replies

  • I have done a bit more research since my original query, and have found that adding the following directly into xxxDataSet.Designer.cs (within "public partial class PeopleTableAdapter") not only compiles but does what I want it to:

     

    public void SetSQL(string theSQL)

    {

     

    this.CommandCollection[0].CommandText = theSQL;

    }
    Unfortunately the code gets removed everytime the DataSet gets regenerated, which is all too often.

    CommandCollection is declared as protected, so surely it should be visible within the class, even if it is not physically in the same file???

    Tuesday, October 13, 2009 12:08 PM
  • Simply add a new class/file to your project and create another partial class called PeopleTableAdapter and put your SetSQL method there instead. It will not get regenerated when your DataSet gets regenerated because it's in a totally separate file.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, October 15, 2009 6:14 PM
  • Bonnie,

    Thanks for replying.

    I am not sure what I am doing wrong, but I thought that I had already tried what you are suggesting.

    Whilst the code compiles and works perfectly well when included directly within the generated file (xxxDataSet.Designer.cs), as soon as I put it in a separate file it fails to compile as it does not recognise the "CommandCollection".
    As previously, the error is
    Error 1 'TestTableRelations.PeopleTableAdapter' does not contain a definition for 'CommandLine' and no extension method 'CommandLine' accepting a first argument of type 'TestTableRelations.PeopleTableAdapter' could be found (are you missing a using directive or an assembly reference?)


    The (entire) new file that I have added is:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Text;
    using System.Collections;
    using System.Collections.ObjectModel;
    using System.Collections.Specialized;
    using System.ComponentModel.Design;
    using System.Data.OleDb;
    using System.Data.Common; 
    
    namespace TestTableRelations2
    {
        public partial class PeopleTableAdapter : global::System.ComponentModel.Component
        {
            public void SetSQL(string theSQL)
            {
                this.CommandCollection[0].CommandText = theSQL;
            }
        }
    
    
    }
    

    I have added all the "using" statements in the forelorn hope of resolving the problem.

    The namespace is the same as that of the xxxDataSet.Desinger.cs
    The line starting "public partial class" is the copied from xxx.DataSet.Designer.cs.

    After I type "this." intellisense offers me a number of options, but none of them are from the declarations within public partial class PeopleTableAdapter within xxx.DataSet.Designer.cs.

    I have tried repeating this in a new project (but with the same table), and had exactly the same problem.

    Am I doing something wrong, and if so what?


    Phil Gameson

     

    Friday, October 16, 2009 8:36 AM
  • TableAdapters are a major PITA ... sorry, I just do not like them (and do not use them).

    Anyway, here's where your problem is ... the TableAdapters are in a different namespace than your DataSet. You probably are putting your partial class in the wrong namespace, that's all (using statements won't help you here).

    So, for example, say that you have a DataSet called CustomerDataSet and it is created under the namespace MyApp.DataSets. The namespace for your TableAdapters would NOT be MyApp.DataSets, it would be MyApp.DataSets.CustomerDataSetTableAdapters. Use that namespace for your partial class and everything should be fine.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Phil Gameson Friday, October 16, 2009 4:24 PM
    Friday, October 16, 2009 3:41 PM
  • Bonnie,

    I am becoming a little bit disillusioned with TableAdapters as well!

    Anyway, you have correctly identified my problem, which was indeed the namespace.
    Whilst the top of the xxxDataSet.Designer.cs had the namespace set to that of the [Project Name], the section to do with my TableAdapter was actually in namespace "[Project Name].xxxDataSetTableAdapters".

    Having sorted all this, the solution to my problem amounts to no more than 4 (real) lines of code, but getting there has taken some time:

    namespace TestTableRelations.Support_LogDataSetTableAdapters
    {
        public partial class PeopleTableAdapter : System.ComponentModel.Component
        {
            public void SetSQL(string theSQL)
            {
                this.CommandCollection[0].CommandText = theSQL;
            }
        }
    }



    Many Thanks

    Friday, October 16, 2009 4:33 PM
  • You're quite welcome Phil.

    If you want to see an alternative to TableAdapters (DataAdapters, TableAdapters are basically just a wrapper around the original DataAdapters), see two of my blog entries:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Friday, October 16, 2009 5:59 PM
  • I was reading this purely by accident, lookong for anything on DB independence. Please excuse my ignorance, but I do not understand the problem. This code compiles and executes perfectly at runtime in both an SQL server and an MSAccess runtime environment:

    TableAdapter.Adapter.SelectCommand =

    new OleDbCommand("Select * from Users order by " +

     

    "domainname, computer, userid", OleConn);

    and the TableAdapter.Fill command uses the TableAdapter.Adapter.SelectCommand  to fill the table.

    Thursday, January 7, 2010 9:11 AM
  • PGMariotti, the solution you proposed half worked for me. It didn't produce an error, but it didn't execute my select statement either because, as I saw stepping in the Fill command, it uses the CommandCollection[0].CommandText and the one I provided is CommandCollection[1]. Is there anything more you do before calling the Fill?
    Thursday, January 7, 2010 12:33 PM
  • @PGMariotti -- while your code compiles fine, using the OleDb* set of commands for SQL Server is not recommended because it will perform better and more reliably if you use the Sql* set of commands instead (found in System.Data.SqlClient I think ... off the top of my head). I still recommend writing your own DataAccess classes instead of using TableAdapters.

    @Dm.A -- it sounds like you're trying to work around the TableAdapter limitations with some of your own code? Maybe if you post a few snippets we can see what you're attempting to do and give you some suggestions ...
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, January 7, 2010 6:14 PM
  • You're quite welcome Phil.

    If you want to see an alternative to TableAdapters (DataAdapters, TableAdapters are basically just a wrapper around the original DataAdapters), see two of my blog entries:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Is the link invalid? Something error occurs when opening the link.
    Saturday, February 26, 2011 11:45 PM
  • Yes, both links are good ... I just double-checked. Try it again ... what error are you getting?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 27, 2011 12:07 AM
  • Thank you for a very enlighteing post. How do I figure out the variable name that references the object through which I can access SetSQL().

    I've added the method, but as a Visual Studio newbie, I can't figure out how to call it.

     


    Jim Thompson
    Saturday, November 19, 2011 6:53 PM
  • Hi Jim -- could you post the relevant parts of your code?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, November 20, 2011 1:24 AM