Jump to content
  • Advertisement
Sign in to follow this  
Holland

[.net] SQL-like query on a DataTable

This topic is 3178 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm working on a c# app and I decided to use a datatable due to the fact that you can easily pull data from a Microsoft SQL Server, which is what I'm using. You can also easily add the data to a DataGrid container, which is very nice. So my questions is, how can I get a new DataTable, or atleast DataRow[], of a subset of the COLUMNS. I understand fully how to get subsets of rows by using DataTable.Select(), but I'd like to mimic a SQL statement like: SELECT id, firstName, phone FROM employeeDB; such that I only get the id, firstName, and phone columns. So far all I've figured out how to do is conditions on rows, but no matter what I still get every column. Thanks in advance!

Share this post


Link to post
Share on other sites
Advertisement
Unless I misunderstood you, sounds like you just need to use LINQ.

http://www.google.com/search?hl=en&source=hp&q=linq+datagrid&aq=f&oq=&aqi=g10

LINQ can be used for just about anything that is enumerable, which includes a collection of columns.

I have never actually used the verbose LINQ style (I always use the method overloads), but I believe it would be something like:


var results = FROM emp IN employeeDB SELECT emp.id, emp.firstName, emp.phone;



Share this post


Link to post
Share on other sites
Quote:
Original post by Spodi
Unless I misunderstood you, sounds like you just need to use LINQ.

http://www.google.com/search?hl=en&source=hp&q=linq+datagrid&aq=f&oq=&aqi=g10

LINQ can be used for just about anything that is enumerable, which includes a collection of columns.

I have never actually used the verbose LINQ style (I always use the method overloads), but I believe it would be something like:

*** Source Snippet Removed ***



this is sort of what i'm looking for. but right now, when my application starts up I just get a "snapshot" of what the DB looks like and store it into a DataTable. I get the entire table...ALL of it (SELECT * FROM database). So what I would like to do it perform basic queries on the DataTable that I pull in from this database. I know I can use the DataTable.Select() method to get a DataRow[]. And I can use that Select() method to make calls like "id == 0" or "startTime >= 10:00" which give me less rows. But I would like some sort of a Select() method that will have a method signature like this:

DataTable SelectColumns(params string[] columns);


And this would allow me to say:

// forward declare a function
DataTable GetEmployeeDataTable();

// a large DataTable with 22 columns and 33 rows.
DataTable largerTable = GetEmployeeDataTable();

// getting a smaller DataTable with only 3 columns (id, firstName, dateofBirth)
// but still has all 33 rows.
DataTable smallerTable = largerTable.SelectColumns("id", "firstName", "dateOfBirth");


...and this would give me a DataTable that has only 3 columns instead of all 22 the database currently has.

I would like to find a way of doing this without including or linking in anything other than what .NET has to offer please. That is my stipulation on this.

Thanks again!

Share this post


Link to post
Share on other sites
Quote:
Original post by Holland
I would like to find a way of doing this without including or linking in anything other than what .NET has to offer please. That is my stipulation on this.
The only way I can imagine doing that would be to create a whole new DataTable with only the columns you're interest in. But that seems horribly inefficient to me (working with DataTables in general is not exactly efficient, either, though).

Why exactly do you only need a subset of the columns? The benefit of DataTable.Select is that when it selects the subset of rows, it doesn't have to allocate any extra memory to hold them - they just reference the rows in the original DataTable. But I can't imagine how a "SelectColumns" implementation could work without having to make lots of copies of things.

As mentioned by Spodi, I think LINQ is the way to go for you. It's got all kinds of methods for querying in-memory data sets.

Share this post


Link to post
Share on other sites
I think I'll just make a clone of the DataTable using DataTable.Clone(). then I'll go through the cloned table and do DataTable.Column.Remove("columnName") against all of the columns i don't want.

It might be inefficient, but I can't imagine it's all that bad really. The benefits is because right now I want to just display certain columns in a DataGrid, but I don't want to lose all of the information in my main table. And on the same token, setting a DataGrid to just display an entire DataTable is a simple assignment operator to a property of the DataGrid. So I don't want to have to go through and insert columns and rows into the DataGrid. I just want a function that will give me a new DataTable with only the columns I want to display to the user.

Share this post


Link to post
Share on other sites
Quote:
Original post by Holland
The benefits is because right now I want to just display certain columns in a DataGrid, but I don't want to lose all of the information in my main table. And on the same token, setting a DataGrid to just display an entire DataTable is a simple assignment operator to a property of the DataGrid. So I don't want to have to go through and insert columns and rows into the DataGrid.
If that's all you want to do, then you just have to set the AutoGenerateColumns to false and populate the Columns yourself, then bind your DataTable as normal. In fact, if you're adding your DataGrid in the aspx/ascx file, you can use markup to define the column with ease. That's gotta be much simpler than cloning the whole DataTable and removing columns.

Share this post


Link to post
Share on other sites
Quote:
Original post by Codeka
Quote:
Original post by Holland
The benefits is because right now I want to just display certain columns in a DataGrid, but I don't want to lose all of the information in my main table. And on the same token, setting a DataGrid to just display an entire DataTable is a simple assignment operator to a property of the DataGrid. So I don't want to have to go through and insert columns and rows into the DataGrid.
If that's all you want to do, then you just have to set the AutoGenerateColumns to false and populate the Columns yourself, then bind your DataTable as normal. In fact, if you're adding your DataGrid in the aspx/ascx file, you can use markup to define the column with ease. That's gotta be much simpler than cloning the whole DataTable and removing columns.


Hmm...so I can just do:

DataGrid.AutoGenerateColumns = false;
DataGrid.Columns.Add(new DataColumn("id", typeof(Int32)));
DataGrid.Columns.Add(new DataColumn("firstName", typeof(string)));
DataGrid.Columns.Add(new DataColumn("lastName", typeof(string)));

DataGrid.DataSource = myDataTable;



and even though myDataTable might have 22 columns, it will only display the columns with the heading "id", "firstName", "lastName"?

If that is the case then that is EXACTLY what i need. :)

Share this post


Link to post
Share on other sites
Quote:
Original post by Holland
and even though myDataTable might have 22 columns, it will only display the columns with the heading "id", "firstName", "lastName"?

If that is the case then that is EXACTLY what i need. :)
Yes, except you add BoundColumn objects to the Columns property, like so:

DataGrid.AutoGenerateColumns = false;
DataGrid.Columns.Add(new BoundColumn("id", "Identifier"));
DataGrid.Columns.Add(new BoundColumn("firstName", "First Name"));
DataGrid.Columns.Add(new BoundColumn("lastName", "Last Name"));

DataGrid.DataSource = myDataTable;

The first parameter to the constructor is the name of the column in the DataTable, and the second parameter is the actual column heading to use.

Share this post


Link to post
Share on other sites
Quote:
Original post by Codeka
Quote:
Original post by Holland
and even though myDataTable might have 22 columns, it will only display the columns with the heading "id", "firstName", "lastName"?

If that is the case then that is EXACTLY what i need. :)
Yes, except you add BoundColumn objects to the Columns property, like so:

*** Source Snippet Removed ***
The first parameter to the constructor is the name of the column in the DataTable, and the second parameter is the actual column heading to use.


Are you sure about the BoundColumn? I added the reference to System.Web and an using System.Web.UI.WebControls. I have the same line of code like you show:

DataGrid.Columns.Add(new BoundColumn("id", "Ident"));



but it says BoundColumn does not have a constructor that takes 2 params.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!