Creating a Dataset from a Query

Posted on 2006-04-27
Last Modified: 2010-04-16
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
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.  
Question by:FastEddie___
    LVL 13

    Assisted Solution

    With your query:

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

    So the DataTable will have

    Author Comment

    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.
    LVL 4

    Accepted Solution

    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. ^_^.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now