CODEWITHSUNDEEP
c#.netsql-serversqldatareader
Nir
Nir

Reputation: 29584

How to get table name of a column from SqlDataReader

I have an SQL query I get from a configuration file, this query usually contains 3-6 joins.

I need to find at run time, based on the result set represented by SqlDataReader, to find the name of the table for each column.

Here are some thing that don't work:

I'm using .net 3.5SP1/ C#/ SQL Server 2008 in a console application.

EDIT: I know this is not possible for all cases since a "column" can be combined from multiple tables, a function or even a constant expression - I'm looking for something that works in the simple case.

EDIT 2: Found out why it didn't work - You can use SqlDataReader.GetSchemaTable to get table information but you have to set CommandBehavior to KeyInfo, you do that in the ExecuteReader call:

reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

Upvotes: 11

Views: 16158

Answers (8)

Zam
Zam

Reputation: 2940

How to get database name, table name & column name. Also possible to get Schema name as well. Tested with MS SQL 2016

CommandBehavior.KeyInfo must be indicated

        SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.KeyInfo);

        DataTable dataTable = sqlDataReader.GetSchemaTable();

        for (int i = 0; i < dataTable.Rows.Count - 1; i++)
        {
            string ii =
                dataTable.Rows[i]["BaseCatalogName"].ToString() + "\\" +
                dataTable.Rows[i]["BaseTableName"].ToString() + "\\" + 
                dataTable.Rows[i]["ColumnName"].ToString();
        }

Upvotes: 0

Madacol
Madacol

Reputation: 4266

reader = cmd.ExecuteReader();
reader.GetSchemaTable().Rows[0]["BaseTableName"];

Upvotes: 2

Emre Kilinc Arslan
Emre Kilinc Arslan

Reputation: 2189

SqlCeConnection conn = new SqlCeConnection("Data Source = Database1.sdf");
SqlCeCommand query = conn.CreateCommand();
query.CommandText = "myTableName";
query.CommandType = CommandType.TableDirect;
conn.Open();
SqlCeDataReader myreader = query.ExecuteReader(CommandBehavior.KeyInfo);
DataTable myDataTable= myreader.GetSchemaTable();
//thats the code you asked. in the loop
for (int i = 0; i < myDataTable.Rows.Count; i++)
{
    listView1.Columns.Add(myDataTable.Rows[i][0].ToString());
}

Upvotes: 0

user2380889
user2380889

Reputation:

you can solve it like the following :

DataTable schemaTable = sqlReader.GetSchemaTable();

foreach (DataRow row in schemaTable.Rows)
{
    foreach (DataColumn column in schemaTable.Columns)
    {
        MessageBox.Show (string.Format("{0} = {1}", column.ColumnName, row[column]));
    }
}

Upvotes: 1

A-K
A-K

Reputation: 17080

In general, this is not possible. Consider the following query:

SELECT col1 FROM table1
UNION ALL
SELECT col1 FROM table2

Clearly col1 comes from more than one table.

Upvotes: 1

Nir
Nir

Reputation: 29584

You can use SqlDataReader.GetSchemaTable to get table information but you have to set CommandBehavior to KeyInfo, you do that in the ExecuteReader call:

reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

Upvotes: 15

Kyra
Kyra

Reputation: 5407

This unanswered question on stackoverflow uses SqlDataReader.GetSchemaTable to get the table name. Their problem is that it returns the actual table name rather than the alias that the table has. Not sure if this works with your sql but figured I'd let you know just in case.

Upvotes: 3

John Saunders
John Saunders

Reputation: 161773

I don't know if this information is available. In particular, not all columns of a result set come from a table. From a relational point of view, tables and resultsets are the same thing.

Upvotes: 2

Related Questions