Selecting data from datatable stored in datatable object

Question

3.00/5 (1 vote)

See more:

I have data in dtmain datatable.I want to use this dtmain data in a select query as below.Is there any method to fire query on datatable stored in datatable object.

Below is my code.

C#

DataSet ds = ReadDataFile("E:\\ImportData\\" + strName + ".xlsx");
 DataTable dtmain = ds.Tables[0];
Sql = "Select Name,Main.* from (SELECT EmpId, FName from  " + dtmain + ")as Main Left Join Table1 T1 ON T1.EmpId =Main.EmpId";

and fire Sql in sql server and get records.

Posted 16-Jul-14 0:50am

pwavell

Updated 16-Jul-14 1:21am

Herman<T>.Instance

v4

Add a Solution

Comments

swapnil_jadhav 16-Jul-14 7:09am

Try using linQ

ZurdoDev 16-Jul-14 7:17am

I'm confused. Do you want to find data that is in your DataTable or do you want to query Sql Server?

3 solutions

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)

ArunRajendra · Answer 1 · 2014-07-16T01:46:00

Solution 1

if I am not wrong you cannot mix data table with sql query.

Posted 16-Jul-14 1:46am

ArunRajendra

CHill60 · Answer 2 · 2014-07-16T01:54:00

Solution 2

Try using LINQ to DataSet[^]
or
DataTable.Select[^]

Posted 16-Jul-14 1:54am

CHill60

PrakashCs.net · Answer 3 · 2014-07-16T03:13:00

use this solution i.e. use linq to query on datatable

C#

public DataSet ds;
public DataTable dt, dt1;
ds = new DataSet();

dt = new DataTable();
dt.TableName = "Employee";
dt.Columns.Add("EmployeeID", typeof(Int32));
dt.Columns.Add("Name");

 ds.Tables.Add(dt);

dt1 = new DataTable();
dt1.TableName = "EmployeeAddress";
dt1.Columns.Add("EmployeeID", typeof(Int32));
dt1.Columns.Add("EmployeeAddressID", typeof(Int32));
dt1.Columns.Add("Address");
dt1.Columns.Add("AddressType");
 ds.Tables.Add(dt);
 ds.Tables.Add(dt1);

 //Inner join
 var result = from emp in ds.Tables[0].AsEnumerable()
             join empAdd in ds.Tables[1].AsEnumerable()
                 on (int)emp["EmployeeID"] equals (int)empAdd["EmployeeID"]
             select new
             {
                 EmployeeID = (int)emp["EmployeeID"],
                 Name = (string)emp["Name"],
                 Address = (string)empAdd["Address"],
                 AddressType = (string)empAdd["AddressType"]
             };

foreach (var emp in result)
{
    Console.WriteLine(String.Format("EmployeeID : {0} , Name : {1} , Address : {2} , AddressType : {3}", emp.EmployeeID,emp.Name,emp.Address,emp.AddressType));
}

//Left join
var result = from emp in ds.Tables[0].AsEnumerable()
             join empAdd in ds.Tables[1].AsEnumerable()
                 on (int)emp["EmployeeID"] equals (int)empAdd["EmployeeID"] into AddGrp
             from record in AddGrp.DefaultIfEmpty()
             select new
             {
                 EmployeeID = (int)emp["EmployeeID"],
                 Name = (string)emp["Name"],
                 Address = record == null ? string.Empty : (string)record["Address"],
                 AddressType = record == null ? string.Empty : (string)record["AddressType"]
             };

foreach (var emp in result)
{
    Console.WriteLine(String.Format("EmployeeID : {0} , Name : {1} , Address : {2} , AddressType : {3}", emp.EmployeeID, emp.Name, emp.Address, emp.AddressType));
}