Few days ago I have encountered a question in asp.net forums asking if why does the DataSet creates a default name as Table1 well in fact the name doesn’t really exist in the database. So I thought I’d share the answer that I have provided in that thread as a reference to others. As the documentation states that:
"Multiple Result Sets: If the DataAdapter encounters multiple result sets, it will create multiple tables in the DataSet. The tables will be given an incremental default name of TableN, starting with "Table" for Table0. If a table name is passed as an argument to the Fill method, the tables will be given an incremental default name of TableNameN, starting with "TableName" for TableName0."
To make it more clear then consider this example:
Suppose that you have a Sql Command that returns two tables namely, Employee and Customer.
If you are going to fill your DataSet like below
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
| DataSet ds = new DataSet(); SqlConnection connection = new SqlConnection(GetConnectionString()); connection.Open(); string sqlStatement = "SELECT * FROM Employee; SELECT * FROM Customer"; SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlDa.Fill(ds,"Employee"); string t1 = ds.Tables[0].TableName.ToString();// returns Employee string t2 = ds.Tables[1].TableName.ToString();// returns Employee1 connection.Close(); |
The result would be two tables are being created in the DataSet: Employee and Employee1. To avoid this, you can use table mappings to ensure that the second table is named Customer instead of Employee1. To do this, map the source table of Employee1 to the DataSet table Customer as shown in the following example below:
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
| DataSet ds = new DataSet(); SqlConnection connection = new SqlConnection(GetConnectionString()); connection.Open(); string sqlStatement = "SELECT * FROM Employee; SELECT * FROM Customer"; SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlDa.TableMappings.Add("Employee1", "Customer"); sqlDa.Fill(ds,"Employee"); string t1 = ds.Tables[0].TableName.ToString();// returns Employee string t2 = ds.Tables[1].TableName.ToString();// returns Customer connection.Close(); |
That’s it! Hope you will find this example useful!
Technorati Tags:
ADO.NET,
ASP.NET,
C#