How to get column names of table at runtime in C#?

Lets say I have a table in SQLServer named MyTable

ID FirstName   LastName
1  Harry       Dan
2  Maria       Vicente
3  Joe         Martin

Now if I have to insert any data in table, I will simply fire Insert Query like this

INSERT INTO MyTable (ID, FirstName, LastName) VALUES (4, Smith, Dan);

But what if I don't know the column names beforehand, I only know table name. Then is there a way to get the column name of table at runtime?

---

你可以使用sql-

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE_NAME') 

---

Or you can query for SELECT TOP 0 * FROM TableName . Then, you can get the columns:

using(var reader = command.ExecuteReader())
{
    reader.Read();
    var table = reader.GetSchemaTable();
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine(column.ColumnName);
    }
 }

---

Another option using pure C# / .NET code: First a helper method, that here returns a simple list of column names. Using a DataTable to hold table schema information, means that other information can also be retreived for each column, fx. if it is an AutoIncreament column etc.

    private IEnumerable<string> GetColumnNames(string conStr, string tableName)
    {
        var result = new List<string>();
        using (var sqlCon = new SqlConnection(conStr))
        {
            sqlCon.Open();
            var sqlCmd = sqlCon.CreateCommand();
            sqlCmd.CommandText = "select * from " + tableName + " where 1=0";  // No data wanted, only schema
            sqlCmd.CommandType = CommandType.Text;

            var sqlDR = sqlCmd.ExecuteReader();
            var dataTable = sqlDR.GetSchemaTable();

            foreach (DataRow row in dataTable.Rows) result.Add(row.Field<string>("ColumnName"));
        }

        return result;
    }

The method can be called as:

        var sortedNames = GetColumnNames("Data Source=localhost;Initial Catalog=OF2E;Integrated Security=SSPI", "Articles").OrderBy(x => x);
        foreach (var columnName in sortedNames) Console.WriteLine(columnName);

链接地址: http://www.djcxy.com/p/93840.html

上一篇: SQL语句来打印表名和它们的列名

下一篇: 如何在运行时在C＃中获取表的列名？