Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

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: ,,
Posted on Monday, August 3, 2009 5:15 PM ADO.NET , ASP.NET , C# | Back to top


Comments on this post: Why DataSet creates TableN as the Default Table name?

# re: Why DataSet creates TableN as the Default Table name?
Requesting Gravatar...
Table mappings can save a lot of time, theres loads of random defaults that just confuse things otherwise
Left by pico projector reviews on Aug 03, 2009 11:01 PM

# re: Why DataSet creates TableN as the Default Table name?
Requesting Gravatar...
What happens if the tables being returned are in a different order each time? How would you handle this?
Left by Terry on Mar 03, 2010 10:19 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net