ADO.Net feature request | Tech Off

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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);

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.

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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.

cpod

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

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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.

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.

jmbledsoe
whole different kettle of fish

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.

blowdart
Peek-a-boo

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.

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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.

jmbledsoe
whole different kettle of fish

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]

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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!

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.

Ang3lFir3
Codito Ergo Sum

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?

UlsterFry
http://en.wikipedia.org/wiki/Ulster_fry

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!

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