How to get the real table names from dataset when use DataAdapter to exec a store procedure?

Article
12/29/2010

Question

_{Wednesday, December 29, 2010 7:26 AM}

I have a SP which return several tables:

CREATE PROCEDURE [dbo].[USP_ExternalInterface_PersonalInfo_Select]
    --@Id nvarchar(19)
AS 
BEGIN
    SELECT * FROM Career --WHERE Id=@Id
    SELECT * FROM Education --WHERE Id=@Id
    --...
END

And my C# code like below:

      string connectionString = "server=.;database=ActivateCMBC;Integrated Security=true;Max Pool Size=100;Min Pool Size=10";
      string commandText = "EXEC USP_ExternalInterface_PersonalInfo_Select";
      SqlDataAdapter adp=new SqlDataAdapter(commandText,connectionString);
      DataSet ds=new DataSet();

      adp.FillSchema(ds, SchemaType.Source);
      adp.Fill(ds);

      foreach (DataTable dt in ds.Tables)
      {
        Console.WriteLine(dt.TableName);
      }

I want get real table names such as (Career, Education), but not (Table, Table1)

I hope I´ve made a good explication and you can help me. Thanks

All replies (8)

_{Friday, December 31, 2010 3:30 AM ✅Answered | 1 vote}

Hi,

I think we cannot get the table names when using SqlDataAdapter.Fill method. If there are multiple result sets returned, SqlDataAdapter will create the table names automatically.

Please refer to another similar thread: http://forums.asp.net/t/1452859.aspx

Vinz uses TableMapping to specify table names.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

_{Friday, December 31, 2010 5:52 AM ✅Answered | 1 vote}

If I'm not mistaken, you should be able to assign name to a table in the code.

If your SelectCommand returns multiple tables, Fill automatically generates table names with incremental values for the tables in the DataSet, starting with the specified table name and continuing on in the form TableNameN, starting with TableName1. You can use table mappings to map the automatically generated table name to a name you want specified for the table in the DataSet. For example, for a SelectCommand that returns two tables, Customers and Orders, issue the following call to Fill.

Copy

adapter.Fill(customersDataSet, "Customers")

Two tables are created in the DataSet: Customers and Customers1. You can use table mappings to ensure that the second table is named Orders instead of Customers1. To do this, map the source table of Customers1 to the DataSet table Orders, as shown in the following example.

Copy

adapter.TableMappings.Add("Customers1", "Orders")
adapter.Fill(customersDataSet, "Customers")

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Friday, December 31, 2010 4:14 AM | 1 vote}

Your result set doesn't have any name, so Adapter assigning default name. Check your procedure , more over, execute your procedure as Stored Procedure not as command Text. Check this example

SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
 Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();

_{Tuesday, January 4, 2011 4:26 AM}

Thank you Jian kang, Mike and Naom, you're correct,

We can use SqlDataAdapter.FillSchema(ds, SchemaType.Source) to get column names, but cannot get tabel names, this is unpleasant.

TableMappings.Add("Customers1", "Orders") is a alternative methods, but not flexible for a common ExecuteQuery() function.

So I decide to use regex to match table names and it works fine for my case, please advice if there are better solutions.

  [STAThread]
  static void Main(string[] args)
  {
   string connectionString = "server=.;database=ActivateCMBC;Integrated Security=true;Max Pool Size=100;Min Pool Size=10";
   string commandText = "USP_ExternalInterface_PersonalInfo_Select";
   DbAccess dba = new DbAccess(connectionString);
   DataSet ds = dba.ExecuteQuery(CommandType.StoredProcedure, commandText, true);
   foreach (DataTable dt in ds.Tables)
   {
    Console.WriteLine(dt.TableName);
   }
  }


 public class DbAccess
 {
  public DbAccess(string connectionString)
  {
   _connectionString = connectionString;
  }
  public string _connectionString { get; set; }

  public virtual DataSet ExecuteQuery(CommandType commandType, string commandText, params IDbDataParameter[] parameters)
  {
   DataSet ds = new DataSet();
   using (DbDataAdapter adapter = new SqlDataAdapter(commandText, _connectionString))
   {
    adapter.SelectCommand.CommandText = commandText;
    adapter.SelectCommand.CommandType = commandType;
    adapter.SelectCommand.Parameters.AddRange(parameters);
    adapter.Fill(ds);
   }

   return ds;
  }

  public virtual DataSet ExecuteQuery(CommandType commandType, string commandText, bool mappingTableName, params IDbDataParameter[] parameters)
  {
   DataSet ds = ExecuteQuery(commandType, commandText, parameters);
   if (ds != null && ds.Tables.Count > 0)
   {
    if (commandType == CommandType.StoredProcedure)
    {
     MappingTableName(ds, GetSPContent(commandText));
    }
    else if (commandType == CommandType.Text)
    {
     MappingTableName(ds, commandText);
    }
    else
    {
     ds.Tables[0].TableName = commandText;
    }
   }
   return ds;
  }

  private string GetSPContent(string spName)
  {
   string spContent = "";
   SqlParameter parameter = new SqlParameter("@objname", spName);
   DataSet ds = ExecuteQuery(CommandType.StoredProcedure, "SP_HELPTEXT", new IDbDataParameter[] { parameter });
   if (ds != null && ds.Tables.Count > 0)
   {
    foreach (DataRow row in ds.Tables[0].Rows)
    {
     spContent += row[0] == DBNull.Value ? "" : row[0].ToString();
    }
   }

   return spContent;
  }

  public List<string> GetTableNames(string commandText)
  {
   string pattern = @"(?<!(INSERT\s*INTO\s*[^\s]*\s*|(\(\s*)))SELECT[^@]*?FROM\s*(?<Table>[^\s]+)";
   string group = "Table";

   return Matches(commandText, pattern, group);
  }

  public virtual bool MappingTableName(DataSet dataSet, string commandText)
  {
   List<string> tableNames = GetTableNames(commandText);

   int count = Math.Min(tableNames.Count, dataSet.Tables.Count);
   for (int i = 0; i < count; i++)
   {
    if (!string.IsNullOrEmpty(tableNames[i]))
    {
     dataSet.Tables[i].TableName = tableNames[i];
    }
   }

   return true;
  }

  private List<string> Matches(string content, string pattern, string group)
  {
   MatchCollection matchs = Regex.Matches(content, pattern, RegexOptions.IgnoreCase);
   List<string> items = new List<string>();

   foreach (Match match in matchs)
   {
    if (match != null && match.Groups[group] != null)
    {
     items.Add(match.Groups[group].Value);
    }
   }

   return items;
  }
 }

_{Thursday, November 10, 2011 5:53 AM}

Thank you so much.

I was looking for how to access the table name in a dataset which is filled by generalized execute query and found this as verrry helpfull.

_{Thursday, October 2, 2014 7:25 PM | 1 vote}

I realise this is a very old thread, but it's still coming up near the top on Google when you search for this question, so forgive the response.

I searched for a while for an elegant way of doing this and eventually gave up; the regex however didn't appeal to me. Instead, I return a "fake" table from my stored proc, e.g.:

CREATE PROCEDURE [Reports].[GetADataSet]

AS
    Select 'TableNames','TableA','Misc','TableB' -- return the table names so we can name them in the resultset, otherwise the sqldataadapter just names them Table, Table1, Table2 etc
    SELECT * from TableA
    select * Misc 
    select * from TableB

RETURN 0

and then iterate through that to name the tables in the dataset:

// if Table[0].Rows[0][0] says "TableNames" then assume it's a table of the names for our latter tables. Otherwise, carry on regardless...
                if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0].ItemArray.Length >= 1 && ds.Tables[0].Rows[0][0].ToString().ToLowerInvariant() == "tablenames")
                {
                    for (int i = 0; i < ds.Tables[0].Rows[0].ItemArray.Length; i++)
                    {
                        ds.Tables[i].TableName = ds.Tables[0].Rows[0][i].ToString();
                    }
                }

If you don't include the extra table with the value TableNames in its first column, then your tables are auto-numbered. If you return more tables than you've provided names for, the ones after you run out of names will be auto-numbered.

Hope that helps someone!

Kev

_{Wednesday, April 19, 2017 11:26 PM}

forget table mapping - 100% useless...

it "works" but is no different than using "Table3" etc...

The problem: no hard-name matching from stored procedure to resulting tables.

If the stored procedure changes or conditional results sets are returned - it all breaks

I think i have a workable solution:

Software developer

_{Monday, March 25, 2019 7:25 PM}

A workaround is to add tablename as first column in sql

select '' as MyFirstTable, col1, col2 from MyFirstTable;
select '' as MySecondTable, col1, col2 from MySecondTable;

Then set tablename to the name of first column, and remove it.

da.Fill(ds);
foreach(DataTable dt in ds.Tables)
{
  if(dt.Columns.Count>1) dt.TableName = dt.Columns[0].ColumnName;
  dt.Columns.RemoveAt(0);
}

/Magnus

Magnus Burk

Share via

How to get the real table names from dataset when use DataAdapter to exec a store procedure?

Question

All replies (8)

Additional resources