Tech Off Thread

15 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

ADO.Net feature request

Back to Forum: Tech Off
  • User profile image
    UlsterFry

    Is this a good idea or bad idea?

    With an adapter Fill() method you can specify the dataset and table name, but what about when you execute multiple select queries?

    Would it be an idea to overload the Fill() method to accept a dataset object and a string array?

    here's an example:

    SqlConnection sqlConn = new SqlConnection(connString);
    SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM CompanyCustomers;SELECT * FROM CustomerOrders", sqlConn);

    DataSet ds = new DataSet();
    string [] tableNames = new string[] {"Customers", "Orders"};
    sqlAdapter.Fill(ds,tableNames);



  • User profile image
    cheong

    I think it'll be lovely if there's an overload to return a string[,] (Afterall, most SQL datatypes can be converted to string)

    But for the suggestion of multiple select bugs me... I'm not sure what it is but I don't think I'll be easy with that... I'd rather to run it multiple times than mix it in the same table.

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    UlsterFry

    I don't think you are on my wavelength

    If you run the following code:

    SqlConnection sqlConn = new SqlConnection(connString);
    SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM CompanyCustomers;SELECT * FROM CustomerOrders", sqlConn);

    DataSet ds = new DataSet();
    sqlAdapter.Fill(ds);

    You get a dataset with 2 tables called Table and Table1.
    Now, you can rename these with:

    ds.Tables[0].TableName = "Customers";
    ds.Tables[1].TableName = "Orders";

    But wouldn't it be nice to pass in an array of table names so that when your dataset is created you'd end up with the table names defined in your string array.

  • User profile image
    cpod

    Isn't this what the DataAdapter's TableMappings is for?

  • User profile image
    UlsterFry

    yeah,  there are a few ways of setting/changing the table names including setting up the table-mappings, but I thought that if you can specify a table as a string for a single command to the dataAdapter, why not be able to pass in an array for mutiple commands, it's a cheap and cheerful way of populating the dataset.

    It's just a suggestion, doesn't seem like it would be too hard to extending the dataAdapter to do this.

  • User profile image
    cheong

    UlsterFry wrote:
    I don't think you are on my wavelength...

    Nop. I fully understand what you said but the suggestion to return string[,] is just another idea come in my mind when see this.

    I still don't think I'll want to perform multiple selects at once.

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    jmbledsoe

    UlsterFry wrote:
    Is this a good idea or bad idea?

    With an adapter Fill() method you can specify the dataset and table name, but what about when you execute multiple select queries?

    Would it be an idea to overload the Fill() method to accept a dataset object and a string array?

    here's an example:

    SqlConnection sqlConn = new SqlConnection(connString);
    SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM CompanyCustomers;SELECT * FROM CustomerOrders", sqlConn);

    DataSet ds = new DataSet();
    string [] tableNames = new string[] {"Customers", "Orders"};
    sqlAdapter.Fill(ds,tableNames);

    Why exactly would you want to fill multiple tables at once?  Is it to preserve referential integrity in your DataSet?  Or, is there some other reason.

  • User profile image
    blowdart

    jmbledsoe wrote:
    Why exactly would you want to fill multiple tables at once?  Is it to preserve referential integrity in your DataSet?  Or, is there some other reason.


    I know I do it when I have things like nested repeaters.

  • User profile image
    UlsterFry

    blowdart wrote:
    
    jmbledsoe wrote:Why exactly would you want to fill multiple tables at once?  Is it to preserve referential integrity in your DataSet?  Or, is there some other reason.


    I know I do it when I have things like nested repeaters.


    Ditto, and it's useful to populating a dataset of unrelated data.

  • User profile image
    jmbledsoe

    blowdart wrote:
    
    I know I do it when I have things like nested repeaters.

    UlsterFry wrote:
    
    Ditto, and it's useful to populating a dataset of unrelated data.


    Hmm . . . I've run into the nested repeater case as well, and usually, I want the "outer" repeater to have rows based on some criteria, and I want the "inner" repeater to have child rows of each of the rows in the outer repeater.

    I usually use typed DataSets, and a data access toolkit to fill them.  It's got a class called a MultiTableDataAdapter which will allow you to fill a child table based on arbitrary criteria, and it will also fill necessary rows in parent tables.

    As for the two tables of unrelated data, if I were you I'd just use two data adapters.  Is that so bad?  [A]

  • User profile image
    UlsterFry

    jmbledsoe wrote:
    
    As for the two tables of unrelated data, if I were you I'd just use two data adapters.  Is that so bad? 


    Sure you can use 2 adapters and you can rename the tables using various options but can I stress this is just a simple suggestion!

  • User profile image
    mikep

    Thanks for the post!

    As has been observed, this can be achieved using TableMappings, for example, the following code writes out the table names "Customers" and "Orders":

    SqlConnection sqlConn = new SqlConnection(connString);
    SqlDataAdapter sqlAdapter = 
       new SqlDataAdapter("SELECT * FROM Customers;SELECT * FROM Orders", sqlConn);
    sqlAdapter.TableMappings.Add("Table","Customers");
    sqlAdapter.TableMappings.Add("Table1","Orders");

    DataSet ds = new DataSet();
    sqlAdapter.Fill(ds);
    foreach(DataTable t in ds.Tables)
       Console.WriteLine(t.TableName);

    Alternatively, in ADO.NET 2.0 we added a Load() command that allows you to directly load a datatable or dataset from any datareader, without requiring you to create a DataAdapter.  The Load() command does take an optional string of tablenames, so you could write the code as follows:

    SqlConnection sqlConn = new SqlConnection(connString);
    DataSet ds2 = new DataSet();
    SqlCommand command = 
                new SqlCommand("SELECT * FROM Customers;SELECT * FROM Orders",sqlConn);
    sqlConn.Open();
    SqlDataReader reader = command.ExecuteReader;(CommandBehavior.CloseConnection);
    ds2.Load(reader,LoadOption.OverwriteChanges,"Customers2","Orders2");
    foreach(DataTable t in ds2.Tables)
       Console.WriteLine(t.TableName);

    Given this direct mechanism, and a desire to avoid having too many overloads, we decided not to add another variation of overloads to Fill.

    We could certainly revisit this decision if people feel this scenario is common/important enough to justify additional overloads, but our general feeling was that having too many overloads made the API more confusing.

  • User profile image
    Ang3lFir3

    mikep wrote:
    Alternatively, in ADO.NET 2.0 we added a Load() command that allows you to directly load a datatable or dataset from any datareader, without requiring you to create a DataAdapter.


    just wanted to say this is one of those things that I love about ADO.Net 2.0 .... first time i saw it my initial thought was "duh that makes sense".....

    I have always wondered how this differs performance wise from using the DataAdapter's Fill Method? I prefer it (the .Load method) as its faster for me to write and seems to be lighter weight than newing up a dataAdapter is this true?

    (im of the belief that you should never need a DataAdapter just to collect data that won't be updated)

    p.s. why do i feel like this is a stupid question?

  • User profile image
    UlsterFry

    mikep wrote:
    

    Alternatively, in ADO.NET 2.0 we added a Load() command that allows you to directly load a datatable or dataset from any datareader, without requiring you to create a DataAdapter.  The Load() command does take an optional string of tablenames, so you could write the code as follows:



    Ah,  that's cool..   hadn't spotted that!

  • User profile image
    smcirish

    Regarding copying a 2-dim array into a dataset.

    How do I do this?  Do you have any code samples?  I am coding in VB - VS2003

    -smc

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.