Not getting columns for specific tables

Question

I am trying to get schema for specific table but i am getting null for columns.

Reference

I am trying to get result in this below class:

public class Tables
    {
        public string Name { get; set; }
        public string[] Columns { get; set; }
    }

Code:

string[] selectedTables = { "Table1", "Table2"};
  var conectionString = new SqlConnection("MyconnectionString");
  var data = new List<Tables>();
  data = conectionString.GetSchema("Tables").AsEnumerable()
       .Select
       (
        t => new Tables
         {
           Name =  t[2].ToString(),
           Columns = conn.GetSchema("Columns",
                     new string[] { databaseName,t[2].ToString() }).AsEnumerable() // t[2].ToString() represent tablename
                    .Where(c => selectedTables.Contains(t[2].ToString()))
                    .Select(c => c[3].ToString()).ToArray()  // c[3].ToString() represent Columns
       }).ToList();

 return data;

Although in data I am getting list of tables i.e Table0,Table1,Table2,Table3 etc. but I am not getting columns for this Table1 and Table2 selected tables.

Update: I am looking for something like this:

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

         // For the array, 0-member represents Catalog; 1-member represents Schema; 
         // 2-member represents Table Name; 3-member represents Table Type. 
         // Now we specify the Table Name of the table what we want to get schema information.
         tableRestrictions[2] = "Course";

         DataTable courseTableSchemaTable = conn.GetSchema("Tables", tableRestrictions);

data = conectionString.GetSchema("Tables").AsEnumerable()
       .Select
       (
        t => new Tables
         {
           Name =  t[2].ToString(),
            Columns = (from useTable in selectedTables
            let columns = conn.GetSchema("Columns",new string[] { databaseName, t["TABLE_SCHEMA"].ToString(), useTable })
                     select new { columns[3]})  //Error
       }).ToList();

How do I inject my selectedTables?

Answer 1

There are several ways to accomplish the goal.

One way could be to get all tables, filter the result by the table name and then get the columns for each selected table:

string[] selectedTables = { "Table1", "Table2"};
using (var conection = new SqlConnection("MyconnectionString"))
{
    connection.Open();

    var tables = (
        from table in connection.GetSchema("Tables").AsEnumerable()
        let name = (string)table["TABLE_NAME"]
        where selectedTables.Contains(name)
        let catalog = (string)table["TABLE_CATALOG"]
        let schema = (string)table["TABLE_SCHEMA"]
        select new Tables // this should really be called Table
        {
            Name = name,
            Columns = (
                from column in connection.GetSchema("Columns", new [] { catalog, schema, name }).AsEnumerable()
                select (string)column["COLUMN_NAME"]).ToArray()
        }).ToList();

    return tables;
}

Update: According to the comment, you actually want all tables but with columns populated only for the selected ones, so instead of where you could use the same criteria for conditional columns population:

        from table in connection.GetSchema("Tables").AsEnumerable()
        let name = (string)table["TABLE_NAME"]
        let catalog = (string)table["TABLE_CATALOG"]
        let schema = (string)table["TABLE_SCHEMA"]
        select new Tables // this should really be called Table
        {
            Name = name,
            Columns = selectedTables.Contains(name) ? (
                from column in connection.GetSchema("Columns", new [] { catalog, schema, name }).AsEnumerable()
                select (string)column["COLUMN_NAME"]).ToArray() : null
        }).ToList();