Web Development

General discussion


Query between tables In Dataset .NET

By pramodsahu_4nov ·
I have a Query related to Dataset & DataTable in ASP.NET/VB.NET

1) MyDataset is a dataset populated from Two diffrent XML file.
2) Dataset having two tables table(0) is ResourceAllocation, And table(1) is Projects
3) Table(?Project?) is a master table containing all the projects of company some fields are ProjectID,ProjectName,Dureation,Status
4) Table(?ResourceAllocation?) is a table in which we are maitaning the employess working on the project some fields are ProjectID,Resource Name,Startdate etc

Now I want to show the output in Datagrid from both table like in RDBMS

Select A.ProjectName, B.ResourceName, B.StartDate
From Project A, ResourceAllocation B
WHERE A.ProjectID = B.ProjecrtID
AND A.Status = ?Opened?

ProjectName ResourceName StartDate

I made a relation between Both Table on ProjectID, Select statement is applicable on a single table only.
I don?t want to display record by GetChildRows method, it?s a limited flexibility.

So how can I access record from both table & Apply WHERE clause on fields of both tables in a single Select statement.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by MacQuarrie In reply to Query between tables In D ...

You don't have a join in place. Try this:

Select A.ProjectName, B.ResourceName, B.StartDate
From Project A

INNER JOIN ResourceAllocation B ON A.ProjectID = B.ProjectID

WHERE A.Status = ?Opened?

Collapse -

by RRV In reply to Query between tables In D ...

Good requirement. This feature was a longtime requirement. Having a Join SQL statement can bring data direct from different tables from DB server. But if you already have data in 2 tables in your dataset...I'm not so sure in VS2003. But sure in VS2005. VS2005 sports with new thing called "SELECT". You can query your array, user defined data which is currently in memory. For example create a 2 dimension array with your data. You can run a standard SQL query or T-SQL on this runtime array and get result in a variable/array/structure.
That means at runtime you have a dataset with 2 tables. You can query these two tables at runtime to get one single output directed to a datagrid or so.
Good luck

Collapse -

by kailashdhondiyal In reply to Query between tables In D ...

You have to create a new data table for containing joining records.

//Data Relation
DataTable dt = new DataTable();
DataColumn[] TransactionColumns;
DataColumn[] DetailColumns;
TransactionColumns = new DataColumn[] { ds.Tables["csrvendorsall"].Columns["vnd_cd"] };
DetailColumns = new DataColumn[] { ds.Tables["foobar"].Columns["vnd_cd"] };
DataRelation Tran_Detail = new DataRelation("VendorsCode", TransactionColumns, DetailColumns, false);
DataTable tbl = new DataTable("Join");
tbl.Columns.Add("vnd_cd", typeof(string));
tbl.Columns.Add("vendorcode", typeof(string));
tbl.Columns.Add("repname", typeof(string));
tbl.Columns.Add("fac_cd", typeof(string));
tbl.Columns.Add("fee", typeof(float));
foreach (DataRow parent in ds.Tables["csrvendorsall"].Rows)
foreach (DataRow child in parent.GetChildRows("VendorsCode"))
DataRow joinRow = ds.Tables["Join"].NewRow();
joinRow["vnd_cd"] = parent["vnd_cd"];
joinRow["vendorcode"] = parent["vendorcode"];
joinRow["repname"] = parent["repname"];
joinRow["fac_cd"] = child["fac_cd"];
joinRow["fee"] = child["fee"];

//bind the values to our GridView
GridView1.Caption = "<h4>Loaded the data using Sample4</h4>";
GridView1.DataSource = ds;
GridView1.DataMember = "Join";

Related Discussions

Related Forums