C# - Select Data from two tables in SQL to Dataset

Question

I have two tables in my database, (Users and Cities) and I want to select all the data in this tables where the column UserID=1 in Users table.

But the Dataset does not find my tables (Users and Cities)

This is my SQL Query:

SELECT * FROM Users INNER JOIN Cities ON Cities.CityID=Users.CityID WHERE Users.UserID=1

And this is the Mathod:

public static DataSet GetData(string SqlQuery)
{
    OleDbConnection con = new OleDbConnection(conString);
    OleDbCommand cmd = new OleDbCommand(SqlQuery, con);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
}

Code:

    DataSet ds = GetData(myQuery);

    string fname = ds.Tables["Users"].Rows[0]["UserFisrtName"].ToString();
    string lname = ds.Tables["Users"].Rows[0]["UserLastName"].ToString();
    string city = ds.Tables["Cities"].Rows[0]["CityName"].ToString();

    string output = "Name: " + fname + " " + lname + " City: " + city;

check [this](http://stackoverflow.com/questions/11345761/how-to-fill-dataset-with-multiple-tables) — Badiparmagi, Nov 01 '16 at 14:44
Why are you using oleDB instead of SQLClient for a SQL database? If your connection string doesn't specify the Database name then you need to add USE XXXdb in the query. — jdweng, Nov 01 '16 at 14:44
There is no need to reference the table name in your data set. The Join will return data in a single table. All the columns will be in table zero. — Theo, Nov 01 '16 at 15:11

Paul Tsai · Answer 1 · 2016-11-02T13:42:06.380

If you want 2 datatables in the data set, change the sql query to this.

SELECT * FROM Users WHERE Users.UserID=1; select * from City where CityID in (Select cityid from users where userID = 1);

The user table will then be on ds.Tables[0] and the city table on ds.Tables[1].

Please remember to use the using clause to ensure connections, etc are properly disposed.

public static DataSet GetData(string SqlQuery)
{
    using(var con = new OleDbConnection(conString))
    using(var cmd = new OleDbCommand(SqlQuery, con))
    using(var da = new OleDbDataAdapter(cmd))
    {
        var ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

Code:

var ds = GetData(myQuery);
var fname = ds.Tables[0].Rows[0]["UserFisrtName"].ToString();
var lname = ds.Tables[0].Rows[0]["UserLastName"].ToString();
var city = ds.Tables[1].Rows[0]["CityName"].ToString();
var output = "Name: " + fname + " " + lname + " City: " + city;

score 0 · Answer 2 · answered Nov 01 '16 at 15:05

0

I found the answer, in the sql query

SELECT * FROM Users, Cities WHERE Users.CityID=Cities.CityID AND Users.UserID=1

answered Nov 01 '16 at 15:05

Mr Lord

150
1
4
18

So you're saying that using old-style joins adds _two_ tables to the `DataSet`? – D Stanley Nov 01 '16 at 15:08
EEP! That's horrible, I havent see that syntax in over a decade. PLEASE Use "JOIN" syntax when joining tables ... – Theo Nov 01 '16 at 15:09

Theo · Answer 3 · 2016-11-01T16:22:32.070

0

In a Dataset your tables will not automatically have the names of the tables from which they are selected. Use the ordinals instead:

DataSet ds = GetData(myQuery);
string fname = ds.Tables[0].Rows[0]["UserFisrtName"].ToString();
string lname = ds.Tables[0].Rows[0]["UserLastName"].ToString();
string city = ds.Tables[0].Rows[0]["CityName"].ToString();

string output = "Name: " + fname + " " + lname + " City: " + city;

edited Nov 01 '16 at 16:22

answered Nov 01 '16 at 15:06

Theo

885
6
16

I corrected my response. Note that even though you are selecting from two distinct tables, the join will result in a single "table" of data. As such it is not necessary to return a data set, you could just use a data reader. As it is, all of the columns you address should be available on `Tables[0]`. – Theo Nov 01 '16 at 16:25

score 0 · Answer 4 · answered Nov 01 '16 at 15:21

You are filling the dataset only with one table, this because the Sql statement only return a one set of data. For access the tables you use indexes, only the columns name will be mapped in the rows of the table so this values can be access by index or by name.

SELECT * FROM Users INNER JOIN Cities ON Cities.CityID=Users.CityID WHERE Users.UserID=1

Also avoid use SELECT * this because you force the database engine to search for the columns of all the tables instead to only use the you already provided, and if you will not use all or someone change te order of it in the table, can be a future problem.

C# - Select Data from two tables in SQL to Dataset

4 Answers4