Creating a Dataset from a Query

I'm trying to create a dataset with query that uses a dynamic field. Here is my sql:

=============================================================
SELECT a.cad_account_number, a.date_sold, a.county_name, a.sale_price,
           (a.sale_price - b.txroll_NoticedLandValue)/b.txroll_GBA AS imp,
            b.txroll_NeighborhoodCode, b.txroll_year, b.txroll_GBA, b.txroll_NoticedLandValue
FROM MLS a
INNER JOIN tbl_TaxRoll b
ON a.cad_account_number = b.txroll_CadAccountNumber
WHERE b.txroll_NeighborhoodCode IS NOT NULL AND b.txroll_GBA > 1 AND a.sale_price > 1
ORDER BY a.date_sold DESC, b.txroll_NeighborhoodCode
==============================================================

The second line creates a new field "imp" from calculations of other existing columns.

This sql works fine and I'm getting the needed results but I need to create a dataset that has all of these columns.

I'm using Visual Studio 2003 and MS SQL Server 2000. Is there any way of running this query and generating
a dataset from it? I just can't figure out how to use the visual studio IDE to generate a strongly typed dataset with multiple
tables that also has a dynamic column.

Any help is greatly appreciated.  
FastEddie___Asked:
Who is Participating?
 
nguyenvinhtuConnect With a Mentor Commented:
Hi FastEddie,
I heard that Strong typed Dataset cannot be dynamic chaning columns or datatype inside after created. If you still want to have a strongtyped Dataset for easy tracking and modifying. You can create one as you have presented above. Named it and Saved it into Solution.
When querying the result, before you fill in DataSet, write code to read query-result Table to over-ridding the structure of DataSet but still keep the same name and saved place. ^_^.
0
 
dunglaConnect With a Mentor Commented:
With your query:

string strSql = YourQueryString;
DataSet dstData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, ConnectionString);
adapter.Fill(dstData);

So the DataTable will have
cad_account_number
date_sold
county_name
sale_price
imp
txroll_NeighborhoodCode
txroll_year
txroll_GBA
txroll_NoticedLandValue
0
 
FastEddie___Author Commented:
Hi Dungla,

Thanks for the code but I was wondering how to do that in the visual studio IDE.
I need to actually create the .xsd file so I can keep track of and modify the datasets in visual studio.
I create a dataset in the IDE by right clicking on the solution explorer and selecting Add New Item and
then selecting "Dataset". This works great if you are getting existing database fields but
I don't know how to add the dynamic field to the dataset through the IDE.

Got any ideas?

I appreciate your input.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.