C# 你能从 SqlDataReader 中获取列名吗？

Can you get the column names from a SqlDataReader?

提问by Blankman

After connecting to the database, can I get the name of all the columns that were returned in my SqlDataReader?

连接到数据库后，我可以获取在我的返回的所有列的名称SqlDataReader吗？

采纳答案by Rob Stevenson-Leggett

var reader = cmd.ExecuteReader();

var columns = new List<string>();

for(int i=0;i<reader.FieldCount;i++)
{
   columns.Add(reader.GetName(i));
}

or

或者

var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

回答by Stephen Wrighton

There is a GetNamefunction on the SqlDataReaderwhich accepts the column index and returns the name of the column.

上有一个GetName函数，SqlDataReader它接受列索引并返回列的名称。

Conversely, there is a GetOrdinalwhich takes in a column name and returns the column index.

相反，有一个GetOrdinal它接受列名并返回列索引。

回答by Steven Lyons

You can get the column names from a DataReader.

您可以从 DataReader 中获取列名。

Here is the important part:

这是重要的部分：

  for (int col = 0; col < SqlReader.FieldCount; col++)
  {
    Console.Write(SqlReader.GetName(col).ToString());         // Gets the column name
    Console.Write(SqlReader.GetFieldType(col).ToString());    // Gets the column type
    Console.Write(SqlReader.GetDataTypeName(col).ToString()); // Gets the column database type
  }

回答by Jeremiah Peschka

You sure can.

你肯定可以。

protected void GetColumNames_DataReader()
{
  System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection("server=localhost;database=northwind;trusted_connection=true");
  System.Data.SqlClient.SqlCommand SqlCmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Products", SqlCon);

  SqlCon.Open();

  System.Data.SqlClient.SqlDataReader SqlReader = SqlCmd.ExecuteReader();
  System.Int32 _columncount = SqlReader.FieldCount;

  System.Web.HttpContext.Current.Response.Write("SqlDataReader Columns");
  System.Web.HttpContext.Current.Response.Write(" ");

  for ( System.Int32 iCol = 0; iCol < _columncount; iCol ++ )
  {
    System.Web.HttpContext.Current.Response.Write("Column " + iCol.ToString() + ": ");
    System.Web.HttpContext.Current.Response.Write(SqlReader.GetName( iCol ).ToString());
    System.Web.HttpContext.Current.Response.Write(" ");
  }

}

This is originally from: http://www.dotnetjunkies.ddj.com/Article/B82A22D1-8437-4C7A-B6AA-C6C9BE9DB8A6.dcik

这最初来自：http: //www.dotnetjunkies.ddj.com/Article/B82A22D1-8437-4C7A-B6AA-C6C9BE9DB8A6.dcik

回答by keith

I use the GetSchemaTablemethod, which is exposed via the IDataReader interface.

我使用GetSchemaTable方法，该方法通过 IDataReader 接口公开。

回答by Yakir Manor

If you want the column names only, you can do:

如果你只想要列名，你可以这样做：

List<string> columns = new List<string>();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
    DataTable dt = reader.GetSchemaTable();
    foreach (DataRow row in dt.Rows)
    {
        columns.Add(row.Field<String>("ColumnName"));
    }
}

But if you only need one row, I like my AdoHelper addition. This addition is great if you have a single line query and you don't want to deal with data table in you code. It's returning a case insensitive dictionary of column names and values.

但如果你只需要一行，我喜欢我添加的 AdoHelper。如果您有单行查询并且不想在代码中处理数据表，则此添加非常有用。它返回一个不区分大小写的列名和值字典。

public static Dictionary<string, string> ExecuteCaseInsensitiveDictionary(string query, string connectionString, Dictionary<string, string> queryParams = null)
{
    Dictionary<string, string> CaseInsensitiveDictionary = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;

                // Add the parameters for the SelectCommand.
                if (queryParams != null)
                    foreach (var param in queryParams)
                        cmd.Parameters.AddWithValue(param.Key, param.Value);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(reader);
                    foreach (DataRow row in dt.Rows)
                    {
                        foreach (DataColumn column in dt.Columns)
                        {
                            CaseInsensitiveDictionary.Add(column.ColumnName, row[column].ToString());
                        }
                    }
                }
            }
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    return CaseInsensitiveDictionary;
}

回答by nawfal

Already mentioned. Just a LINQanswer:

已经提到了。只是一个LINQ答案：

var columns = reader.GetSchemaTable().Rows
                                     .Cast<DataRow>()
                                     .Select(r => (string)r["ColumnName"])
                                     .ToList();

//Or

var columns = Enumerable.Range(0, reader.FieldCount)
                        .Select(reader.GetName)
                        .ToList();

The second one is cleaner and much faster. Even if you cache GetSchemaTablein the first approach, the querying is going to be very slow.

第二个更干净，速度更快。即使您GetSchemaTable使用第一种方法进行缓存，查询也会非常缓慢。

回答by Rob Sedgwick

Use an extension method:

使用扩展方法：

    public static List<string> ColumnList(this IDataReader dataReader)
    {
        var columns = new List<string>();
        for (int i = 0; i < dataReader.FieldCount; i++)
        {
            columns.Add(dataReader.GetName(i));
        }
        return columns;
    }

回答by Almett

It is easier to achieve it in SQL

用SQL更容易实现

var columnsList = dbContext.Database.SqlQuery<string>("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'SCHEMA_OF_YOUE_TABLE' AND TABLE_NAME = 'YOUR_TABLE_NAME'").ToList();