C# - how to display metadata about sql tables that are retrieved from another table?

Question

So, I have a database with 7 tables. One of these tables, lets call it tablesOfInterest, simply contains rows of other tables in the database. This table changes regularly.

What I'm trying to do is:

Retrieve the table names from tablesOfInterest.
Display metadata about these tables. Specifically, all column names and the number of rows.

I read here Microsoft about using string[] index to display metadata about specific tables, but when I run my current code I get the error:

More restrictions were provided than the requested schema [tables] supports.

Where am I going wrong?

Method One:

        public static List<string> GetTables() {

        SqlConnection sqlConnection = null;
        SqlCommand cmd = null;
        string sqlString;
        List<string> result = new List<string>();

        try
        {
            sqlConnection = DBConnection.GetSqlConnection();

            sqlString = "select * from TablesOfInterest";
            cmd = new SqlCommand(sqlString, sqlConnection);

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result.Add(reader.GetString(0));
            }
        }
        catch (SqlException e)
        {
            MessageBox.Show("SQL Error! \n" + e);
        }
        catch (Exception e)
        {
            MessageBox.Show("General Error! \n" + e);
        }
        finally
        {
            sqlConnection.Close();
        }
        return result;


    }

Method two:

 public static List<string> GetMetaDataTables()
    {
        SqlConnection con = null;
        List<string> result = new List<string>();
        String[] restrictions = new String[5];

        try
        {
            con = DBConnection.GetSqlConnection();

            foreach (string s in GetMetaData())
            {
                restrictions[2] = s;
            }

            DataTable schemaTable = con.GetSchema("Tables", restrictions);

            foreach (DataRow row in schemaTable.Rows)
            {
                result.Add(row[2].ToString());
            }
        }
        catch (SqlException e)
        {
            MessageBox.Show("Sql Error! \n " + e);
        }
        catch (Exception e)
        {
            MessageBox.Show("General Error! \n " + e);
        }
        finally
        {
            con.Close();
        }
        return result;
    }

Update:

I tried Mikes suggestions and that certainly helped! It now displays the names of the tables correctly. It does not however display the amount of rows. To try and achieve that I did this:

dataGridView2.DataSource = Controller.GetMetaDataTables().Select(x => new { 
Value = x.Value, Name = x.Key }).ToList();

In my gridView I only see the table names though, how can I select the no of rows aswell?

So the first column `reader.GetString(0)` of `TablesOfInterest` is the table name? — Mike Perrenoud, Feb 14 '19 at 09:39

score 2 · Accepted Answer · answered Feb 14 '19 at 09:45

The specific error you're getting:

More restrictions were provided than the requested schema [tables] supports.

is because the Tables collection requires 3 restrictions: database, owner and table.

However, the next problem you have is even if restrictions was defined as:

var restrictions = new string[3];

this code would only retrieve the schema for the last table in TablesOfInterest:

foreach (string s in GetMetaData())
{
    restrictions[2] = s;
}

DataTable schemaTable = con.GetSchema("Tables", restrictions);

foreach (DataRow row in schemaTable.Rows)
{
    result.Add(row[2].ToString());
}

That's because the call to GetSchema belongs inside the iteration of the first for loop like this:

foreach (string s in GetMetaData())
{
    restrictions[2] = s;

    DataTable schemaTable = con.GetSchema("Tables", restrictions);

    foreach (DataRow row in schemaTable.Rows)
    {
        result.Add(row[2].ToString());
    }
}

The next problem you have is that result really needs to be a dictionary:

var result = new Dictionary<string, List<string>>();

because you're recovering column information for multiple tables. That would change that iteration to be something like this:

foreach (string s in GetMetaData())
{
    restrictions[2] = s;

    DataTable schemaTable = con.GetSchema("Tables", restrictions);

    result.Add(s, new List<string>());
    foreach (DataRow row in schemaTable.Rows)
    {
        result[s].Add(row[2].ToString());
    }
}

C# - how to display metadata about sql tables that are retrieved from another table?

1 Answers1