In SQL Query how to set table name

Question

I use C#. I fill a dataset with the result of sql query. Suppose ds is my dataset:

Dataset ds = new Dataset();
ds = GetTablesFromDataBase("Select * from Order, Select * from OrderDetails, Select * from Product");

After running the code above, my dataset contains three tables named Table, Table1, Table2. But I want my dataset table names to be the same as database tables names like: Order, OrderDetails, Product. Is there any way to write query or code, so that dataset table names become the same as database table names?

Please, don't propose the code below to change the table names of a dataset:

Dataset.TableName = "RequiredTableName";

It is usually considered bad practice to select * from table: better practice is to list the specific columns required. Using * makes the application more fragile under change of the database schema. — Chris Walton
– Chris Walton, Commented Feb 27, 2011 at 11:09

Arsen Mkrtchyan · Accepted Answer · 2011-02-27 10:48:14Z

5

You can use typed datasets or tablemapping

something like this

SqlDataAdapter da = new SqlDataAdapter(...);
DataSet ds = new DataSet();
DataTableMapping dtm1, dtm2, dtm3;
dtm1 = da.TableMappings.Add("Table", "Employees"); 
dtm2 = da.TableMappings.Add("Table1", "Products");
dtm3 = da.TableMappings.Add("Table2", "Orders");
da.Fill(ds);

edited Feb 27, 2011 at 10:48

answered Feb 27, 2011 at 10:40

Arsen Mkrtchyan

50.9k33 gold badges155 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Baz1nga · Accepted Answer · 2011-02-27 10:48:44Z

1

you should probably use the DataTable class instead. Here is the mSDN reference for the same: http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx It lets you define all the properties that you want plus give you lot more functions to work with the table.

and since you seem to like having all the tables in one class I would suggest you create a DTO that basically resembles whatever property you wanted to use of the DataSet class and add IList as one of the properties of this DTO.

Hope that makes sense and helps you.

edited Feb 27, 2011 at 10:48

answered Feb 27, 2011 at 10:43

Baz1nga

15.6k3 gold badges38 silver badges63 bronze badges

Comments

Ujjwal Manandhar · Accepted Answer · 2011-02-27 10:47:26Z

0

ds.Tables[0].TableName = "Required Table Name";
ds.Tables[1].TableName = "Required Table Name";
ds.Tables[2].TableName = "Required Table Name";

You can use this method.

answered Feb 27, 2011 at 10:47

Ujjwal Manandhar

2,26417 silver badges20 bronze badges

Comments

AlexS · Accepted Answer · 2011-09-21 08:16:45Z

0

You could return the names of the tables with an separate select.

SELECT 'Employees','Orders';
SELECT * FROM Employees;
SELECT * FROM Orders;

(you could also select the Tablenames from the global systemtables...)

Then you can set the Tablenames in your DataSet automatically:

for (int cIdx = 0; cIdx < ds.Tables[0].Columns.Count; ++cIdx) {
    int tIdx = cIdx + 1;
    if (tIdx >= ds.Tables.Count) {
        break;
    }
    ds.Tables[tIdx].TableName = ds.Tables[0].Rows[0][cIdx].ToString().Trim();
}
ds.Tables.RemoveAt[0];

This is also only a workaround, but I don't know a better way.

edited Sep 21, 2011 at 8:16

answered Sep 21, 2011 at 7:55

AlexS

5,3553 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

In SQL Query how to set table name

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related