3

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

Oak_3260548
  • 1,882
  • 3
  • 23
  • 41

2 Answers2

3

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");
LeftyX
  • 35,328
  • 21
  • 132
  • 193
  • I'm sorry, I didn't specified one thing I considered naturall: I want to have the tables in ONE transaction (which is a correct approach). I see two separate transactions in your example... I have as much as 15 datatables in one process, each with many joins and that would be already very bad design, I think. – Oak_3260548 Jul 10 '15 at 09:41
  • @user3260548: You can find an update to my answer. – LeftyX Jul 10 '15 at 10:05
  • I see your point, I'll try it. It seems to be a right answer and I mark it once I try it. I'm particularly interested to see what will happen if first or second table is Nothing... "Roles" should stay Table2, not jump into Table1. – Oak_3260548 Jul 10 '15 at 10:12
  • I don't quite understand why your tables should be Nothing. Give us some feedback. Cheers. – LeftyX Jul 10 '15 at 10:16
  • OK, a simple example is an Item, which may contain a few types of subitems. Each type of subitems is listed in a separate table and may have 0 rows (no subitems). But looking closely at your code, it should work. Another problem which arose is, that I have the function in a separate Data Access Layer and it is general for all the queries and I'll have to think through, whether I can make it work generally, with any number of tables. – Oak_3260548 Jul 10 '15 at 10:22
  • @user3260548: To be honest with you I would give up datasets. That's history. I gave up with that stuff long time ago. Large datasets use huge amounts of memory and the garbage collector struggles to dispose them as they tend to be stacked in the "large object heap". Cheers. – LeftyX Jul 10 '15 at 10:33
  • Well, the resulting dataset is not huge, not even large, it's some 20 to 30 fields (yes, fields, not rows). Yes, the source datatables are large, but this is exactly what DBs should handle - provide a bit of data based on a query from a huge amount of stored data... – Oak_3260548 Jul 12 '15 at 10:05
0

As I know directly from SQL query you can't do that, but you can name them manually in following:

ds.table[0].TableName = "Table_MOON";
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • :-). But you need to know that Table(0) is the "Table_MOON", which you don't know. I wrote an example explaining, that I need it to IDENTIFY the tables, so that (1) doesn't jump into (0), in which case there are wrong data in first datatable and no data in the second datatable. And that's why this is not a VB.NET question, but most likely SQL only, as I wonder if I can add an alias to Query tables. Thanks anyway. – Oak_3260548 Jul 10 '15 at 09:44