SQL query table aliases
Is there a way to specify the name / alias of the SQL query data table?
Ie:
SELECT TOP 10 * FROM Table_MOON;
SELECT TOP 10 * FROM Table_SUN;
When you load it into the "ds" dataset, that is, in VB.NET, it becomes:
ds.table(0)
ds.table(1)
Is there a way to alias tables in SQL query so that 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 was unable to report this to Google because the keywords involved are too general.
EDIT: Please note that the idea is to keep it in one transaction. Also, iterating with the datarow methods (adding a column named datatable) is not acceptable due to the nesting and hence the potentially high process time.
Respectfully,
Libor
source to share
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 provide a name when filling out the dataset.
NOTE: This code is C #, but I think it's easy to convert.
It's a converter , just in case.
UPDATE
You can use a TableMappings
collection SqlDataAdapter
to map each table in your 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 queries in 3 tables. Tables are called Table
, Table1
, Table2
etc.
You can simply rename them by adding items to the collection:
daUsersRoles.TableMappings.Add("Table", "UserLogins");
daUsersRoles.TableMappings.Add("Table1", "Users");
daUsersRoles.TableMappings.Add("Table2", "Roles");
source to share