SQL query tables aliases [duplicate]

Question

is there a way to name/alias a data table of a SQL query?

I.e.:

SELECT TOP 10 * FROM Table_MOON;
SELECT TOP 10 * FROM Table_SUN;

When you load it in a dataset "ds", i.e. in VB.NET, it becomes:

ds.table(0)
ds.table(1)

Is there a way to alias tables in SQL query, so that one can call tables like:

ds.table("Table_MOON")
ds.table("Table_SUN")

The problem is, that when table(0) is nothing, then table(1) becomes table(0)...

I failed to google anything about this matter, because involved keywords are too general words.

EDIT: Please note, that the idea is to keep it in one transaction. Also, iteration through the datarow methods (adding a column with datatable name) are unacceptable due to nesting and resulting potentially high process times.

Regards,

Libor

Answer 1

Yes, you can.

SqlConnection connection = new SqlConnection("Data Source=localhost; Initial Catalog=myDatabase; Trusted_Connection=true;User Id=;Password=");
connection.Open();

string sqlUsers = @"select * from Users";
string sqlRoles = @"select * from Roles";

SqlDataAdapter daUsers = new SqlDataAdapter(sqlUsers, connection);
SqlDataAdapter daRoles = new SqlDataAdapter(sqlRoles, connection);

DataSet dsUsersAndRoles = new DataSet("UserAndRoles");

daUsers.Fill(dsUsersAndRoles, "users");
daRoles.Fill(dsUsersAndRoles, "roles");

var userTable = dsUsersAndRoles.Tables["users"];

You can specify a name when you're filling the dataset.

NOTE: this code is c# but I guess it should be easy to convert.

This is the converter, just in case.

UPDATE:

You can use TableMappings collection of the SqlDataAdapter to map each table in the query expression:

SqlConnection connection = new SqlConnection("Data Source=localhost; Initial Catalog=myDatabase; Trusted_Connection=true;User Id=;Password=");
connection.Open();

string sqlUsersRoles = @"select * from UserLogins;select * from Users;select * from Roles";

SqlDataAdapter daUsersRoles = new SqlDataAdapter(sqlUsersRoles, connection);

daUsersRoles.TableMappings.Add("Table", "UserLogins");
daUsersRoles.TableMappings.Add("Table1", "Users");
daUsersRoles.TableMappings.Add("Table2", "Roles");

DataSet dsUsersAndRoles = new DataSet("UserAndRoles");

daUsersRoles.Fill(dsUsersAndRoles);

DataTableCollection tables = dsUsersAndRoles.Tables;

DataTable users = tables["Users"];

As you can see my Sql Statement contains 3 query on 3 tables. Tables are named Table, Table1, Table2 and so on.

You can just rename them adding elements to the collection:

daUsersRoles.TableMappings.Add("Table", "UserLogins");
daUsersRoles.TableMappings.Add("Table1", "Users");
daUsersRoles.TableMappings.Add("Table2", "Roles");