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?
You must specify a parameter for the "owner" restriction.
var dtCols = con.GetSchema("Columns", new[] { "DBName", null, "TableName" });
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.
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;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With