Filling Dataset by executing query on multiple tables

Question

Dear Friends
I have a problem. I want to fill dataset by executing query on multiple tables and then capture data in comboboxes.
and also combox1.datasource=ds.tables["which table name or what should i write here?"]

Suresh Paldia · Answer

Why don't you execute a JOIN on multiple tables returning a single dataset. Example: String query = "SELECT A.firstname FROM EMPLOYEETABLE AS A INNER/LEFT/RIGHT/FULL JOIN ADDRESSTABLE AS B ON somecondition"; SqlDataAdapter a = new SqlDataAdapter(query,con); DataSet ds = new DataSet(); a.Fill(ds,"resulttable"); ComboBox1.DataSource = ds;

Suresh Paldia · Answer

Your Welcome Mayur..

Suresh Paldia · Answer

Your welcome Mayur

Mayur Gujrathi · Answer

Thank You Very Much Sir

Suresh Paldia · Answer

In above case when using join, and if the result returned is only one datatable. This datatable your dataset is known by name "resulttable" if i use a.Fill(ds,"resulttable"); Now, in future you can refer/map to that table by using ComboBox1.DataSource = ds.Table["resulttable"] But in case my query itself is returning more than 1 tables, for example string stmt = "SELECT name FROM Customer SELECT ordernumber FROM Order SELECT recieptnumber FROM RecieveDetails"; Or you might use a stored procedure that returns multiple tables. Code above will result in 3 DataTables called "Table", "Table1", "Table2" by default if i use - a.Fill(ds); ComboBox1.DataSource = ds.Tables["Table"]; ComboBox2.DataSource = ds.Tables["Table1"]; ComboBox3.DataSource = ds.Tables["Table2"]; And it will return 3 datatables called "resulttable", "resulttable1" and "resulttable2" if i use - a.Fill(ds,"resulttable"); ComboBox1.DataSource = ds.Tables["resulttable"]; ComboBox2.DataSource = ds.Tables["resulttable1"]; ComboBox3.DataSource = ds.Tables["resulttable2"]; Accordingly, you will have to refer these tables by these names only. But, i recommend you to add a TableMapping for each table returned by your statement. You can do this as, SqlDataAdapter da= new SqlDataAdapter(stmt, sqlConnection); da.TableMappings.Add("Table", "Customer"); da.TableMappings.Add("Table1", "Order"); da.TableMappings.Add("Table2", "RecieveDetails"); da.Fill(dataset); Then you can use; ComboBox1.DataSource = ds.Tables["Customer"]; ComboBox2.DataSource = ds.Tables["Order"]; ComboBox3.DataSource = ds.Tables["RecieveDetails"]; More meaningful... isn't it! Hope you got this concept... For more about Table Mapping, you can visit http://msdn.microsoft.com/en-us/library/ms810286.aspx Happy Learning...

Mayur Gujrathi · Answer

Sir Whats This "resulttable". Please give me some brief exposure,Its Urgent. I didnt understood Thank You String query = "SELECT A.firstname FROM EMPLOYEETABLE AS A INNER/LEFT/RIGHT/FULL JOIN ADDRESSTABLE AS B ON somecondition"; SqlDataAdapter a = new SqlDataAdapter(query,con); DataSet ds = new DataSet(); a.Fill(ds,"resulttable"); ComboBox1.DataSource = ds;

Manikavelu Velayutham · Answer

You can achieve your goal using LINQ. There you can join multiple table and fetch the needed values. Finally you can fill it in a dataset.

Mahesh Chand · Answer

Are these tables are related? If these tables are related, data should automatically come in multiple DataTable objects within a singe DataSet. To access a table, you can specify the table name. For example, if you have two tables - Customer and Products. comboBox1.DataSource = ds.Tables["Customers]; So what you need to do is, either create a relationship in your database and use the JOIN SQL query OR you can add a relationship in the DataRelation (you probably don't need this).

Filling Dataset by executing query on multiple tables

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (8)