How to query a c# data table in a OdbcCommand

Question

I have two query's. One from one database, and one from another.

The second query uses the first query's data as a table. (This is what I need to know how to do)

The query's more complicated then the example but i tried to make it simple. Example below:

DataTable dt = new DataTable();
OdbcConnection myConnection = salesConnection();
myConnection.Open();

OdbcCommand selectCMD = new OdbcCommand("SELECT CUSTOMER.CUSTOMER_NBR " +
"FROM CUSTOMER", myConnection);

OdbcDataAdapter cmd = new OdbcDataAdapter();
cmd.SelectCommand = selectCMD;

cmd.Fill(dt);
myConnection.Close();

DataTable dtM = new DataTable();
myConnection = mConnection();
myConnection.Open();

selectCMD = new OdbcCommand("SELECT '**DATA TABLE ABOVE**'.CUSTOMER_NBR " + 
                            "FROM '**HOW TO I REFERENCE THE DATA TABLE ABOVE**'",
                            myConnection);

cmd = new OdbcDataAdapter();
cmd.SelectCommand = selectCMD;

cmd.Fill(dtM);
myConnection.Close();

Does the first query return a single CUSTOMER_NBR or multiple? In general, use parameters in your second command which are set from the first table's row(s) (you could use a lop if it mcontains multiple rows). — Tim Schmelter
– Tim Schmelter, Commented Sep 3, 2013 at 13:45
If you need to use the data from first qurey, why don't you combine it to your second one? — huMpty duMpty
– huMpty duMpty, Commented Sep 3, 2013 at 13:47
It returns a big list of customer names and numbers (for this example a big list of numbers). Use a lop? Never heard of it. — Luke
– Luke, Commented Sep 3, 2013 at 13:50
huMpty duMpty, because their not from the same database, they require two different connections strings to get data — Luke
– Luke, Commented Sep 3, 2013 at 13:51
I thought I deleted my answer. lol. I dont think you can do what your asking because sql doesn't understand a .net datatable. You'd have to build your sql using the data in your datatable. If the answer to huMpty duMpty question is correct the solution be easier than you think and he can help — Rob White
– Rob White, Commented Sep 3, 2013 at 14:05

huMpty duMpty · Accepted Answer · 2013-09-03 14:14:43Z

1

I don't think you can do what you are trying to do at the moment

As far as I know, you will need to combine both the queries in to one in the second stage.

You can have a detail look at sp_addlinkedserver (T-SQL)

For example

Select Columns
From   TableName TB1
       Inner Join [OtherServer].[DatabaseName].[dbo].[TabeleName] Tb 
                                        ON Tb.Column=TB1.Column

Update

If this is Oracle , please refer Accessing and Modifying Information in Multiple Databases (Oracle)

edited Sep 3, 2013 at 14:14

answered Sep 3, 2013 at 14:09

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Conrad Frix Over a year ago

Are we sure this is a SQL server we're dealing with?

Luke Over a year ago

I'll test out your suggestions. Thanks

Conrad Frix Over a year ago

@Luke As an aside it is possible to send a DataTable to an Oracle procedure and then join to it. See stackoverflow.com/questions/5557318 but you can't use a Odbc Command, you'd have to use ODP.NET.

Luke Over a year ago

I marked this as the answer because it pushed me into the right direction. I changed the whole sql so it doesn't depend on the first query.

Collectives™ on Stack Overflow

How to query a c# data table in a OdbcCommand

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related