Giving table friendly name in SQL query using MS SQL server

Question

I am using ADO.NET to execute store procedure. The store procedure is having multiple select statement. When I access DataSet, the tables are named as Table1, Table2 and so on. I want to give user friend name to each of these table. I do not want to use Table variable or temp tables in my SQL query. DO I have any other alternatives?

I am using following code to get the dataset

SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();

DataSet ds = new DataSet();
try
{
    con.ConnectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
    con.Open();
    cmd = new SqlCommand("sp_GetData", con);
    cmd.Parameters.Add(new SqlParameter("@ParamOne", param));
    cmd.CommandType = CommandType.StoredProcedure;
    da.SelectCommand = cmd;
    da.Fill(ds);

}

I dont want to do this either

da.TableMappings.Add("Table", "MyTable1");
da.TableMappings.Add("Table1", "MyTable2");
da.TableMappings.Add("Table2", "MyTable3");

or this

ds.Tables[0].TableName = "NametbA";
ds.Tables[1].TableName = "NametbB";

Preferably I want to specify the name in the SQL query. The reason I want to use this approach is because, I will pass this dataset as it to a function which will write the table name into a file.

Please provide your suggestions.

Thank you

How do you **tell** it that what names should be used instead? — King King, Sep 01 '13 at 15:34
Are you looking for [Table Aliases](http://technet.microsoft.com/library/ms187455.aspx) `"SELECT [Column1], [Column2] FROM [Table] AS [YourTableName]"`? — Corak, Sep 01 '13 at 16:27
http://stackoverflow.com/questions/589976/how-can-you-name-the-datasets-tables-you-return-in-a-stored-proc?rq=1 — iabbott, Sep 01 '13 at 16:38

score 1 · Answer 1 · answered Sep 01 '13 at 16:45

It is unfortunately not possible to set it automatically. You will have to provide it to the code somehow.

One option would be to change the structure of your results to have twice as many result sets where the odd one is the name and the even is the data:

-- Table name
SELECT 'nameoftable' AS TableName

-- Data
SELECT * FROM ...

c# code (consider it to be psudo code):

myDataSet.Tables[1].TableName = myDataSet.Tables[0]["TableName"].ToString();

score -1 · Answer 2 · answered Sep 01 '13 at 15:34

-1

Table names in the ADO.Net dataset object are entirely .Net, C# (or vb.net) specific. They have nothing to do with the table names in the SQL query or in the database. Change them in your C# code, by simply writing

 myDataSet.Tables[0].TableName 'WhateverYouWant";

answered Sep 01 '13 at 15:34

Charles Bretana

143,358
22
150
216

You may want to look at the bottom of the OP's question stating clearly what he doesn't want. This is in fact one of those. – King King Sep 01 '13 at 15:37
Thank you for the reply. But I am hoping to specify that name in the SQL query. – SharpCoder Sep 01 '13 at 15:41

Giving table friendly name in SQL query using MS SQL server

2 Answers2