[RESOLVED] [2.0] Filling a typed dataset with multiple tables sql query

[steve_rm]

Hello,

I am using MySql as my database. I can fill individual tables, however when I query to select from 2 different tables and do an add watch on the dataset the count for those 2 tables is 0.

Everything runs without errors. However, when I check count property for the table in the add watch it gives an zero.

However, if I write the query like this: "SELECT * FROM Beverage"
and fill using this da.Fill(DS.Beverage); Then the count display the correct number of rows.

Can anyone explain this?

Many thanks,

My code for filling

 try
            {
                this.OpenConnection();

                RestaurantDataSet ds = new RestaurantDataSet();
                MySqlDataAdapter da = new MySqlDataAdapter();
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = cnn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT maincourse.MainCourseID, beverage.BeverageID FROM maincourse, beverage";
                                    
                da.SelectCommand = cmd;
                da.Fill(ds);

                return ds;      
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }

[jmcilhinney]

What tables could you possibly be watching. First of all, that is only one query so it's only going to populate one DataTable, not two. What's more, it's going to create a completely new DataTable and add it to your DataSet. If you want to populate two existing DataTables then you have to execute two queries and you have to either specify EXACTLY which table you want populated, or else your DataSet has to already contain a DataTable with a name that matches the name of the one and only table involved in the query. If your query involves more than one table and you don't specify the name of the DataTable to populate then you are ALWAYS going to get a new DataTable created.

Apart from that, how can you perform a join on a MainCourse table and a Beverage table? How could they possibly have a direct relationship?

[steve_rm]

Hello,

Ok, thanks. I understand now.

So I will have to create queries for each table and fill the dataset.

cmd.CommandText = "SELECT MainCourseID FROM maincourse";
da.Fill(ds.maincourse);
 
cmd.CommandText = "SELECT beverageID FROM beverage";
da.Fill(ds.beverage);

Something like the above?

Steve

[jmcilhinney]

That should work, assuming that the schema of the result set of each query matches the schema of the DataTable you're populating with it. I'm guessing that your DataTables contain more columns than just the ID. I have to admit, I'm not quite sure what will happen if your DataTable has more columns than your query's result set. I may well be that the other columns are populated with null values, although it's also possible an error will occur. An error will definitely occur if any of those columns don't allow nulls.

Another point to note is that some ADO.NET providers support multiple SQL statements per command. I'm not sure about MySQL but if it was SQL Server this would be valid:

cmd.CommandText = "SELECT MainCourseID FROM maincourse; SELECT beverageID FROM beverage";
da.Fill(ds);

In this case you don't have to specify the name of the DataTable because each query relates to one and only one table and that name maps to an existing Datatable in the DataSet. It's always best to specify a table name even if you don't have to, but in this case it wouldn't be possible because you can't specify two names.