Datamanipulation in Datatables/sets (C#)

Question

5.00/5 (1 vote)

See more:

Hello Community.

I have a specific Problem with Datamanipulation in DataTabel/Set.
First I start with the framework.

Data Sources: A Big MS EXCEL (2003) worksheet with. (Call it Source.xls)
My Program, how should manipulate and analyses some Data.
Output should be another Excel ...

The Problem:
After reading in all the Data from the Source.xls into my DataTabel (called myTestTable). I would like to filter it with SQL or something.

Input:

myExcelConnecter_YieldetValue = new MyExcelConnector();
DataTable myTestTable = new DataTable();
myTestTable = (myExcelConnecter_YieldetValue.getExcelData("PathToSource.xls", "SELECT * FROM [ABC$A2:FL3000]'"));

into myTestTable

I would like to filter some Columns and then query some Cells (with SQL).

1)So first i tryed change the Import SQL

SQL

"SELECT * FROM [ABC$A2:FL3000]'"

to

SQL

"SELECT * FROM [ABC$A2:FL3000] WHERE A$ ='name'"

But the Syntax didn't work (and googeling for "Get Data from Excel with SQL/ADB/OleDB/..." did not help me.

2) Query the DataTable:
The Same Here I only can filter for Rows with .Select but i need to filter the Columns and then search (with SQL) for an Value in a specific Cell.
The SQL would look like this.

SQL

Select name, staat, city from myTestTable where name Like 'Testera';

But i have no idea how to do this in a DataTable Object.

3)The i Tried to change the DataTable in an foreach-loop.
But you can't change the Table while he is in the loop.
First i Want to copy only the tables i need to a seperate DataTable.

foreach (DataColumn myCol in myTestTable.Columns) 
{
   if (myCol.ColumnName.ToString() == "name")
   {
      myCOpy.Columns.Add(myCol);
   }
}

But i get the Error (Column 'name' already belongs to another DataTable)

Then i want to delete all columns i don't use (i won't want to have)

C#

myExcelConnecter_YieldetValue = new MyExcelConnector();
DataTable myTestTable = new DataTable();
myTestTable = (myExcelConnecter_YieldetValue.getExcelData("PathToSource.xls", "SELECT * FROM [ABC$A2:FL3000]'"));
List<String> myKillerCol = new List<string>();
foreach (DataColumn myCol in myTestTable.Columns) 
{
   if (myCol.ColumnName.ToString() != "name")
   {
      myKillerCol.Add(myCol.ColumnName.ToString());
   }
}
foreach (String myStringrunner in myKillerCol) 
{
   myTestTable.Columns.Remove(myStringrunner);
}

That worked BUT the performance was evil. I have to do this like 200 times and one time takes 30-40sec.

4) I write all the Data to a Database where i know how to select an manipulate some Stuff. But this is only an Option if you can't help me.
Or if you say the performance of Option 4) is the Best.

Thx for reading all this and i am open for any help/tips/advise/...
Greetings
Ben

Posted 14-Jan-13 23:17pm

BenKoe

Updated 14-Jan-13 23:41pm

v2

Add a Solution

Comments

Herman<T>.Instance 15-Jan-13 6:17am

DataView object is what you need. You add the DataTable to it, you can FILTER, SORT, any thing you want.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

BenKoe · Answer 1 · 2013-01-15T23:44:00

THX digimanus for that nice tip.
But i used a LINQ query to get over my problem.

the trick is to set the DataTableObject.AsEnumerable().
Then you can work with LINQ trow it.

Example:

C#

DataTable myTestTable = new DataTable();
myTestTable = myMsSqlDB.GetBEX_ACT_TestCases(specificationDataTab); //This is the function,how i fill up my table
var query = from r in myTestTable.AsEnumerable()
            where r.Field<string>("Vorname") == "Peter"
            select new
            {
               Name = r.Field<string>("Vorname"), // (" ") is the ColoumnName in the DataTable
               BirthDate = r.Field<string>("BirthDate"),
            };

            foreach (var item in query) 
            {
               Console.WriteLine(item.Name" ->> " + item.BirthDate);
            }</string></string></string>

Greetings Ben