在C#中通过sql填充具有所有表名的列表，该表名具有指定的列 [英] Populate a List with all the table names which is having specified columns through sql in c#

问题描述

我在sql server中有一个数据库，里面只有几个表.

我需要填充一个列表框，该列表框包含来自数据库的表名列表，该数据库包含指定的列名 说特殊".

我尝试过类似的方法.

但是它显示了数据库中存在的所有表.

但是我只想要那些在列表中有特定列的表...

请给我建议的方式...:)

解决方案

您可以使用此linq查询(现已测试):

List<string> tNames= new List<string>();  // fill it with some table names
List<string> columnNames = new List<string>() { "special" };
// ...

IEnumerable<DataRow> tableRows = con.GetSchema("Tables").AsEnumerable()
  .Where(r => tNames.Contains(r.Field<string>("TABLE_NAME"), StringComparer.OrdinalIgnoreCase));
foreach (DataRow tableRow in tableRows)
{
    String database = tableRow.Field<String>("TABLE_CATALOG");
    String schema = tableRow.Field<String>("TABLE_SCHEMA");
    String tableName = tableRow.Field<String>("TABLE_NAME");
    String tableType = tableRow.Field<String>("TABLE_TYPE");
    IEnumerable<DataRow> columns = con.GetSchema("Columns", new[] { database, null, tableName }).AsEnumerable()
        .Where(r => columnNames.Contains(r.Field<string>("COLUMN_NAME"), StringComparer.OrdinalIgnoreCase));
    if (columns.Any())
    {
        tables.Add(tableName);
    }
}

I have a database in sql server which is having few tables in it.

I need to populate a listbox which contains a list of tables names from the database which contains a specified column name say 'special' .

i have tried something like..

using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                List<string> tables = new List<string>();
                DataTable dt = connection.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    string tablename = (string)row[2];
                    tables.Add(tablename);
                }
                listbox1.ItemsSource = tables;


                connection.Close();
            }

but it is showing all the tables present in the database..

but i want only those table which have a specific columns in a list...

Kindly suggest me the way ... :)

解决方案

You can use this linq query (now tested):

List<string> tNames= new List<string>();  // fill it with some table names
List<string> columnNames = new List<string>() { "special" };
// ...

IEnumerable<DataRow> tableRows = con.GetSchema("Tables").AsEnumerable()
  .Where(r => tNames.Contains(r.Field<string>("TABLE_NAME"), StringComparer.OrdinalIgnoreCase));
foreach (DataRow tableRow in tableRows)
{
    String database = tableRow.Field<String>("TABLE_CATALOG");
    String schema = tableRow.Field<String>("TABLE_SCHEMA");
    String tableName = tableRow.Field<String>("TABLE_NAME");
    String tableType = tableRow.Field<String>("TABLE_TYPE");
    IEnumerable<DataRow> columns = con.GetSchema("Columns", new[] { database, null, tableName }).AsEnumerable()
        .Where(r => columnNames.Contains(r.Field<string>("COLUMN_NAME"), StringComparer.OrdinalIgnoreCase));
    if (columns.Any())
    {
        tables.Add(tableName);
    }
}

这篇关于在C#中通过sql填充具有所有表名的列表，该表名具有指定的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！