Does DbDataAdapter.Fill guarantee returned table names to be Table, Table1, Table2 etc.?

Question

When the query specified returns multiple results, the result set for each row returning query is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on).

And I can see from the example for DataSet that they use Table:

//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();

// A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");

// Open the connection.
connection.Open();
Console.WriteLine("The SqlConnection is open.");

// Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand(
    "SELECT SupplierID, CompanyName FROM dbo.Suppliers;",
    connection);
command.CommandType = CommandType.Text;

// Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;

// Fill the DataSet.
DataSet dataSet = new DataSet("Suppliers");
adapter. Fill(dataset);

But are Table, Table1, Table2 and so on guaranteed? Can I safely use them when I want to set custom names for the DataSet tables with TableMappings?

If you only have a single table use [DataTable](https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable?view=net-7.0) instead. [DataSet](https://learn.microsoft.com/en-us/dotnet/api/system.data.dataset?view=net-7.0) is if you need to have multiple DataTables and need to create relationships between them. — Tu deschizi eu inchid, Dec 22 '22 at 04:19
@user09938 I intend to retrieve 5 tables with a single query and put them into a DataSet. — formicini, Dec 22 '22 at 04:22

score 1 · Accepted Answer · edited Mar 06 '23 at 15:41

Given the following two tables (in SQL Server):

Employee:

CREATE TABLE Employee(Id int not null Identity(1,1),
                      FirstName varchar(50),
                      LastName varchar(50),
                      CONSTRAINT PK_Employee_Id Primary Key(Id));

Asset:

CREATE TABLE Asset(Id int Identity(1,1),
                   Manufacturer varchar(50),
                   Model varchar(50),
                   Description varchar(50),
                   Category varchar(25),
                   Comments varchar(150),
                   EmployeeId int,
                   CONSTRAINT PK_Asset_Id Primary Key(Id),
                   Constraint FK_Asset_EmployeeId_Employee_Id Foreign Key(EmployeeId) references Employee(Id))"

Try the following:

Add the following using directives:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="ConnectionStringWindowsAuth" connectionString="Data Source=.\SQLEXPRESS;Database=HR;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" providerName="System.Data.SqlClient" />
        <add name="ConnectionStringWindowsAuthMars" connectionString="Data Source=.\SQLEXPRESS;Database=HR;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
        <add name="ConnectionStringSqlServerAuth" connectionString="Server=.\SQLExpress;Database=HR;User Id=testUser;Password=mySuperSecretPassword;" />
        <add name="ConnectionStringSqlServerAuthMars" connectionString="Server=.\SQLExpress;Database=HR;User Id=testUser;Password=mySuperSecretPassword;MultipleActiveResultSets=True" />
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
</configuration>

GetData:

private string _connectionStr = ConfigurationManager.ConnectionStrings["ConnectionStringSqlServerAuth"].ConnectionString;
private string _connectionStrMars = ConfigurationManager.ConnectionStrings["ConnectionStringSqlServerAuthMars"].ConnectionString;

public DataSet GetData()
{
    //create new instance
    DataSet ds = new DataSet();

    using (SqlConnection con = new SqlConnection(_connectionStr))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            da.SelectCommand = new SqlCommand("SELECT * FROM Employee", con);
            DataTable dtEmployee = new DataTable("Employee");
            da.Fill(dtEmployee);

            //add
            ds.Tables.Add(dtEmployee);

            da.SelectCommand = new SqlCommand("SELECT * FROM Asset", con);
            DataTable dtAsset = new DataTable("Asset");
            da.Fill(dtAsset);

            //add
            ds.Tables.Add(dtAsset);

            foreach (DataTable dt in ds.Tables)
            {
                Debug.WriteLine($"dt[{dt.TableName}] row count: {dt.Rows.Count}");

                foreach (DataColumn col in dt.Columns)
                {
                    Debug.WriteLine($"    ColumnName: {col.ColumnName}");
                }

                Debug.Write(Environment.NewLine);
            }
        }

        return ds;
    }
}

Update:

But are Table, Table1, Table2 and so on guaranteed?

According to Populating a DataSet from a DataAdapter, it states:

Multiple Result Sets

If the DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet. The tables are 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 are given an incremental default name of TableNameN, starting with "TableName" for TableName0.

Let's add some sample data:

Employee:

Asset:

If we execute the following code:

//Note: This doesn't work correctly

public DataSet GetData()
{
    //create new instance
    DataSet ds = new DataSet();

    using (SqlConnection con = new SqlConnection(_connectionStr))
    {
        //open
        con.Open();

        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            //1st query
            da.SelectCommand = new SqlCommand("SELECT * FROM Employee", con);
            da.Fill(ds);

            foreach (DataTable dt in ds.Tables)
            {
                Debug.WriteLine($"dt[{dt.TableName}] row count: {dt.Rows.Count}{Environment.NewLine}");

                foreach (DataColumn col in dt.Columns)
                {
                    Debug.WriteLine($"    ColumnName: {col.ColumnName}");
                }
            }

            //2nd query
            da.SelectCommand = new SqlCommand("SELECT * FROM Asset", con);
            da.Fill(ds);

            Debug.Write(Environment.NewLine);

            foreach (DataTable dt in ds.Tables)
            {
                Debug.WriteLine($"dt[{dt.TableName}] row count: {dt.Rows.Count}{Environment.NewLine}");

                foreach (DataColumn col in dt.Columns)
                {
                    Debug.WriteLine($"    ColumnName: {col.ColumnName}");
                }
            }
        }

        return ds;
    }
}

The output is:

dt[Table] row count: 3

    ColumnName: Id
    ColumnName: FirstName
    ColumnName: LastName

dt[Table] row count: 7

    ColumnName: Id
    ColumnName: FirstName
    ColumnName: LastName
    ColumnName: Manufacturer
    ColumnName: Model
    ColumnName: Description
    ColumnName: Category
    ColumnName: Comments
    ColumnName: EmployeeId

As you can see, the table name is Table, in both the 1st and 2nd query. Additionally, the column names from the 2nd query were added to the table (name: "Table") in the DataSet. The final result is a table with 3 + 4 = 7 rows. This isn't the desired result.

Let's try with multiple result sets (ie: more than one select statement within a single SelectCommand). We'll use 2 select queries separated by semi-colon: SELECT * FROM Employee; SELECT * FROM Asset;

public DataSet GetDataMultipleResultSets()
{
    //create new instance
    DataSet ds = new DataSet();

    //'Open' is explicitly called; the connection will be opened implicitly
    using (SqlConnection con = new SqlConnection(_connectionStr))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            da.SelectCommand = new SqlCommand("SELECT * FROM Employee; SELECT * FROM Asset;", con);
            da.Fill(ds);

            foreach (DataTable dt in ds.Tables)
            {
                Debug.WriteLine($"dt[{dt.TableName}] row count: {dt.Rows.Count}");

                foreach (DataColumn col in dt.Columns)
                {
                    Debug.WriteLine($"    ColumnName: {col.ColumnName}");
                }

                Debug.Write(Environment.NewLine);
            }
        }

        return ds;
    }
}

The output is:

dt[Table] row count: 3
    ColumnName: Id
    ColumnName: FirstName
    ColumnName: LastName

dt[Table1] row count: 4
    ColumnName: Id
    ColumnName: Manufacturer
    ColumnName: Model
    ColumnName: Description
    ColumnName: Category
    ColumnName: Comments
    ColumnName: EmployeeId

This is one gives the desired result, however as stated in the documentation the table names are Table and Table1.

If one desires for each DataTable within a DataSet to have the same name as the table within the database, one can use DataAdapter.TableMappings as shown below:

public DataSet GetDataMultipleResultSets()
{
    //create new instance
    DataSet ds = new DataSet();

    //'Open' is explicitly called; the connection will be opened implicitly
    //using (SqlConnection con = new SqlConnection(_connectionStrMars))
    using (SqlConnection con = new SqlConnection(_connectionStr))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            //DataSet tables names are 'Table' (ie: Table0), 'Table1', 'Table2', 'Table3', etc...
            //which is determined by the number of queries one specifies within a SelectCommand

            da.SelectCommand = new SqlCommand("SELECT * FROM Employee; SELECT * FROM Asset;", con);

            //since we have 2 select statements, 
            //the table names in the DataSet will be 'Table' and 'Table1'
            //if we had 3 select statements, 
            //the table names in the DataSet would be 'Table', 'Table1', and 'Table2'
            da.TableMappings.Add("Table", "Employee");
            da.TableMappings.Add("Table1", "Asset");

            da.Fill(ds);

            foreach (DataTable dt in ds.Tables)
            {
                Debug.WriteLine($"dt[{dt.TableName}] row count: {dt.Rows.Count}");

                foreach (DataColumn col in dt.Columns)
                {
                    Debug.WriteLine($"    ColumnName: {col.ColumnName}");
                }

                Debug.Write(Environment.NewLine);
            }
        }

        return ds;
    }
}

The output is:

dt[Employee] row count: 3
    ColumnName: Id
    ColumnName: FirstName
    ColumnName: LastName

dt[Asset] row count: 4
    ColumnName: Id
    ColumnName: Manufacturer
    ColumnName: Model
    ColumnName: Description
    ColumnName: Category
    ColumnName: Comments
    ColumnName: EmployeeId

Notice that for each DataTable within the DataSet, that the name now matches the name of table in the database (Employee and Asset).

Summary:

Given:

SqlConnection con = new SqlConnection(_connectionStr)
SqlDataAdapter da = new SqlDataAdapter()

As demonstrated above, for a single result set (ie: a single select statement within a SelectCommand),

da.SelectCommand = new SqlCommand("SELECT * FROM Employee;", con);
da.Fill(ds);

or

da.SelectCommand = new SqlCommand("SELECT * FROM Asset;", con);
da.Fill(ds);

the result is the (default) DataTable name Table.

If one uses multiple result sets (ie: multiple select statements within a SelectCommand),

da.SelectCommand = new SqlCommand("SELECT * FROM Employee; SELECT * FROM Asset;", con);
da.Fill(ds);

the result is the (default) DataTable names Table, Table1, etc.., unless one adds a mapping using DataAdapter.TableMappings.

Resources:

Additional Resources:

Thanks for showing me how I can replace SelectCommand and FIll to name result tables directly, but it doesn't answer my question. — formicini, Dec 22 '22 at 07:17
@formicini: The updated post and/or resources should answer your question. — Tu deschizi eu inchid, Dec 22 '22 at 20:31

Does DbDataAdapter.Fill guarantee returned table names to be Table, Table1, Table2 etc.?

1 Answers1