CODEWITHSUNDEEP
c#ado.netschema
Navid Farhadi
Navid Farhadi

Reputation: 3467

Get Columns of a Table by GetSchema() method

I want to get list of columns of a table using GetSchema method in ADO.Net, my code is:

var dtCols = con.GetSchema("Columns", new[] { "DBName", "TableName" });

And i get an empty DataTable, what is the problem?

Upvotes: 19

Views: 30371

Answers (4)

Ash K
Ash K

Reputation: 3631

This is my complete solution.

You just need to provide tableName and connectionString to this method:

// I took HUGE help from this Microsoft website: - AshishK
// https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
public static List<string> GetAllColumnNamesOfATable(string tableName, string connectionString)
{
    var allColumnNames = new List<string>();

    using (var connection = new SqlConnection(connectionString))
    {
        // Connect to the database then retrieve the schema information.  
        connection.Open();

        // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
        // You can use four restrictions for Column, so you should create a 4 members array.
        String[] columnRestrictions = new String[4];

        // For the array, 0-member represents Catalog; 1-member represents Schema;
        // 2-member represents Table Name; 3-member represents Column Name.
        // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
        columnRestrictions[2] = tableName;

        DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);

        foreach (DataRow row in allColumnsSchemaTable.Rows)
        {
            var columnName = row.Field<string>("COLUMN_NAME");
                
            //You can capture other fields as well, like so:
            //var dataType = row.Field<string>("DATA_TYPE");
            //var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");

            allColumnNames.Add(columnName);
        }

        connection.Close();
    }

    return allColumnNames;
}

PS: If you'd like to capture other information about the columns this way, the following fields are also available:

Upvotes: 2

Philip Sobolik
Philip Sobolik

Reputation: 11

Could both of these answers be generalized a bit with:

dtCols = con.GetSchema("Columns", new[] {con.DataSource, null, "TableName"});

This is assuming that "TableName" is the name of the table that you want the schema for.

Upvotes: 1

Dimitris M
Dimitris M

Reputation: 79

I had a similar problem, the following worked..

using(SqlCommand command = new SqlCommand(sqlText, con)) {
    var sqlReader = command.ExecuteReader();
    var a = sqlReader.GetColumnSchema();                        
}

Upvotes: 0

Matthew
Matthew

Reputation: 25763

You must specify a parameter for the "owner" restriction.

var dtCols = con.GetSchema("Columns", new[] { "DBName", null, "TableName" });

Upvotes: 32

Related Questions