Creating a Dataset from a Query

Posted on 2006-04-27
Medium Priority
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

dungla earned 600 total points
ID: 16559057
With your query:

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

So the DataTable will have

Author Comment

ID: 16563637
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.

Accepted Solution

nguyenvinhtu earned 900 total points
ID: 16568516
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

807 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