Creating table from existing dataset tables

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

In ADO.NET, if I've got a dataset which contains two tables, t1 and t2, how
can I create a new table containing the results of a select statement like
the following:


select t1.firstName, t1.lastName, t2.institution
where t1.personID = t2.personID

Thanks,

Dan
 
Hi Dan,

You should create new DataTable instance, add three string columns to it and
copy the data.
DataTable table = new DataTable("Join");
table.Columns.Add("firstName", typeof(string));
table.Columns.Add("lastName", typeof(string));
table.Columns.Add("institution", typeof(string));

then you do foreach loop through one of the source tables and copy the
rows..
 
Dan,
In addition to Miha's comments.

There is a JoinView sample custom DataView class for VB.NET that may do what
you want.

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325682

Basically you create a new JoinView object, set the properties for your
join, then use this JoinView to add rows to the DataTable that Miha showed
how to create.

Of course if you just need the data & not an actual DataTable, you could
just use the JoinView directly.

Hope this helps
Jay
 
Thanks Jay.

JoinView may help me out, though, when I look at the code, I'm not sure how
exactly it populates each row in its row list with the columns from the
different tables. Perhaps I need to understand better what
DataSet.Relations do (do you know a good article which describes that?).

Since DataSets are billed as a sort of "in memory database", I was hoping
there might be some way to simply set a DataSet as the Data Source for a
select statement (similar to the way you set a physical database as the Data
Source), and then do selects on the tables in the DataSet. I'd like to do
something like the following:

// myDataSet contains tables t1 and t2
SqlConnection myConnection = new SqlConnection(DataSource=myDataSet);
string mySqlStatement = "select t1.firstName, t1.lastName, t2.institution
from t1, t2 where t1.personID = t2.personID"
SqlDataAdapter myAdapter = new SqlDataAdapter(mySqlStatement, myConnection);
myAdapter.fill(myDataSet, "NewTable");

Obviously the precise code above won't work, but is there any way to do
something like this?

Thanks very much,

Dan
 
Back
Top