没有获得特定表的列

本文关键字： | 更新日期: 2023-09-27 18:11:20

我试图获得特定表的模式，但我得到null列。

参考

我想在下面的课上得到结果:

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

代码:

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;

虽然在数据中我得到表列表，即Table0,Table1,Table2,Table3等，但我没有得到这个Table1 and Table2选定表的列。

更新:我正在寻找这样的东西:

 // 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();

如何注入selectedTables?

没有获得特定表的列

有几种方法可以实现这个目标。

一种方法是获取所有表，按表名过滤结果，然后获取每个选定表的列:

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;
}

更新:根据评论，你实际上想要所有的表，但与列填充仅为选定的，所以，而不是where，你可以使用相同的条件列填充标准:

        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();