Retrieve Column name in C#

Question

I was looking around on SO on how to retrieve the column name and I tried out some solutions but when I use this method, for example, I recieve these column names and not my actual column namnes (ID, Status, Title etc.):

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME

• TABLE_TYPE

   using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
  
          System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
          builder.ConnectionString = this.ConnectionString;
          string server = builder.DataSource;
          string database = builder.InitialCatalog;
  
          connection.Open();
          DataTable schema = connection.GetSchema("Tables");
          Tables = new List<Table>();
          foreach (DataRow row in schema.Rows)
          {
              /* Add Table */
              Table t = new Table();
              string tableName = row[2].ToString();
              t.Name = tableName;
  
              /* Add columns */
              //DataTable dtCols = connection.GetSchema("Columns", new[] { "StarTrackerDB", null, "dbo.Tickets" });
              t.Columns = new List<Column>();
  
              foreach (DataColumn column in row.Table.Columns)
              {
                  Column c = new Column();
                  c.Name = column.ColumnName;
                  t.Columns.Add(c);
              }
  
              Tables.Add(t);
          }
      }
  

EDIT:

I want to retrieve it in C# i.e. not execute an SQL query string in my code.

EDIT2

Current output:

• TABLE_CATALOG
• TABLE_SCHEMA
• TABLE_NAME
• TABLE_TYPE

Expected output:

• ID
• Status
• Title

etc. the column names in the tables. the string tableName is set correctly.

Answer 1

Get schema information of all the columns in current database

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

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. For the array, 0-member represents Catalog; 1-member represents Schema; 2-member represents Table Name; 3-member represents Column Name.

e.g. get columns for table MyTable:

String[] columnRestrictions = new String[4];
columnRestrictions[2] = "MyTable";
DataTable myTableSchemaTable = connection.GetSchema("Columns", columnRestrictions);

To get the data from these tables:

var columnDetails = from info in table.AsEnumerable()
                         select new {
                            TableCatalog = info["TABLE_CATALOG"],
                            TableSchema = info["TABLE_SCHEMA"],
                            TableName = info["TABLE_NAME"],
                            ColumnName = info["COLUMN_NAME"],
                            DataType = info["DATA_TYPE"]
                         };

Get schema information of all the IndexColumns in current database

DataTable allIndexColumnsSchemaTable = connection.GetSchema("IndexColumns");

You can specify the Catalog, Schema, Table Name, Constraint Name, Column Name to get the specified column(s). You can use five restrictions for Column, so you should create a 5 members array. For the array, 0-member represents Catalog; 1-member represents Schema; 2-member represents Table Name; 3-member represents Constraint Name; 4-member represents Column Name.

String[] indexColumnsRestrictions = new String[5];
indexColumnsRestrictions[2] = "Course";
indexColumnsRestrictions[4] = "CourseID";
DataTable courseIdIndexSchemaTable = connection.GetSchema("IndexColumns", indexColumnsRestrictions);

To get the data from these tables:

var columnDetails = from info in indexColumnsTable.AsEnumerable()
                     select new {
                        TableSchema = info["table_schema"],
                        TableName = info["table_name"],
                        ColumnName = info["column_name"],
                        ConstraintSchema = info["constraint_schema"],
                        ConstraintName = info["constraint_name"],
                        KeyType = info["KeyType"]
                     };

Answer 2

I edited your code and was able to get the tables and columns with the code below.

   public void testeStackOverflow()
    {
        using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {

            System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            builder.ConnectionString = this.ConnectionString;
            string server = builder.DataSource;
            string database = builder.InitialCatalog;

            connection.Open();


            DataTable schemaTables = connection.GetSchema("Tables");

            foreach (System.Data.DataRow rowTable in schemaTables.Rows)
            {
                String TableName = rowTable.ItemArray[2].ToString();

                string[] restrictionsColumns = new string[4];
                restrictionsColumns[2] = TableName;
                DataTable schemaColumns = connection.GetSchema("Columns", restrictionsColumns);

                foreach (System.Data.DataRow rowColumn in schemaColumns.Rows)
                {
                    string ColumnName = rowColumn[3].ToString();
                }
            }


        }
    }