none
Change Strongly Typed Dataset Adapter Table SQL At Run Time RRS feed

  • Question

  • I am trying to change the SQL in strongly typed dataset adapter at run time and fill the data table with these statments:

    1. WaterUnitInventoryTableAdapters.WATER_UNITTableAdapter water_UNITTableAdapter = new WATER_UNITTableAdapter();
    2. water_UNITTableAdapter.Adapter.SelectCommand.CommandText = "select * from core_wu.water_unit Where WID = '07010103-724'";
    3. water_UNITTableAdapter.Fill(WATER_UNIT);

    However, when running the application, I got an exception error at statement #2 above.  Does anyone know how to fix this problem?

    Thanks in advance for your help in solving this problem.

    Regards,

    Wen.


    • Edited by wen937 Friday, March 30, 2012 12:29 AM
    • Moved by Leo Liu - MSFT Friday, March 30, 2012 8:25 AM Moved for better support. (From:Visual C# General)
    Friday, March 30, 2012 12:25 AM

Answers

  • Thanks Bonnie. But I have found a solution for this problem.

    Below are the original codes that have the problem. I believe this is  a bug in the .Net Framework for statement 2.

    1. WaterUnitInventoryTableAdapters.WATER_UNITTableAdapter water_UNITTableAdapter = new WATER_UNITTableAdapter();
    2. water_UNITTableAdapter.Adapter.SelectCommand.CommandText = "select * from core_wu.water_unit Where WID = '07010103-724'";
    3. water_UNITTableAdapter.Fill(WATER_UNIT);

    These codes allow me to change the SQL of strongly typed data table at run time:

    SqlDataAdapter adapter = new SqlDataAdapter("select * from core_wu.water_unit Where WID = '07010103-724'", dbConnection);
    adapter.Fill(WATER_UNIT);


    • Marked as answer by wen937 Saturday, March 31, 2012 5:08 PM
    • Edited by wen937 Saturday, March 31, 2012 5:09 PM spelling error
    Saturday, March 31, 2012 5:08 PM

All replies

  • Probably this is what you are looking

    How to create XmlDocument object based on XSD at runtime in c#

    http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/thread/794a6d78-5745-44e7-b16a-05e1d33730d7/

    chanmm


    chanmm

    Friday, March 30, 2012 1:53 AM
  • Thanks for the reply. But was trying to change the SQL statement in the strongly typed dataset table adapter.

    Friday, March 30, 2012 2:12 AM
  • Hi wen937,

    I am moving your thread into the ADO.NET DataSet Forum for dedicated support.
    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, March 30, 2012 8:26 AM
  • Thanks Leo.
    Friday, March 30, 2012 3:04 PM
  • What's the exception message?

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, March 31, 2012 4:06 PM
  • Thanks Bonnie. But I have found a solution for this problem.

    Below are the original codes that have the problem. I believe this is  a bug in the .Net Framework for statement 2.

    1. WaterUnitInventoryTableAdapters.WATER_UNITTableAdapter water_UNITTableAdapter = new WATER_UNITTableAdapter();
    2. water_UNITTableAdapter.Adapter.SelectCommand.CommandText = "select * from core_wu.water_unit Where WID = '07010103-724'";
    3. water_UNITTableAdapter.Fill(WATER_UNIT);

    These codes allow me to change the SQL of strongly typed data table at run time:

    SqlDataAdapter adapter = new SqlDataAdapter("select * from core_wu.water_unit Where WID = '07010103-724'", dbConnection);
    adapter.Fill(WATER_UNIT);


    • Marked as answer by wen937 Saturday, March 31, 2012 5:08 PM
    • Edited by wen937 Saturday, March 31, 2012 5:09 PM spelling error
    Saturday, March 31, 2012 5:08 PM
  • It isn't a bug in the .NET Framework ... generated code, as the Typed DataSets and the TableAdapters are, is just code and has nothing to do with the .NET Framework per se.

    It's just that the TableAdapters are not designed to work that way. When the TableAdpaters are generated, the various commands needed to fill each DataTable are already defined in a collection of SqlCommands and each Fill method sets the Adapter.SelectCommand from the appropriate command in the collection. When you first instantiate the TableAdapter class, the Adapter.SelectCommand is null.

    Take a look at the generated TableAdapter class and you'll see what I mean.

    Now, that said, if you don't want to work within the confines of the TableAdapter, then taking charge of it yourself with DataAdapters, as you did, is definitely the way to go. In fact, I have never used TableAdapters, preferring to roll-my-own DataAccess with DataAdapters. I have several blog posts discussing DataAccess and also a blog post on how to generate Typed DataSets and avoid having the TableAdapters generated (I don't like TableAdapters, you may have guessed that). They are old posts, but they are still pretty relevant today. If you are interested, take a look:

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexiblity. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post gets into using anonymous delegates. In the first post is a link to my rant against TableAdapters, which in turn has a link to a post that describes how to create Typed DataSet .xsd's without TableAdapters.

    One more thing if you still want so use TableAdapters: In the DataSet Designer, right-click on the TableAdapter portion of a DataTable to get options for creating new queries and such. See the MSDN docs for a TableAdapter Overview at http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, March 31, 2012 10:29 PM