DataTables: restrictionValues and table names

Question

I have got a single function using OleDb:

I think maybe the use of a list to find out if a specific table exist could be made better, like this: Check for MS Access database table if not exist create it

But I also want to understand the old code given, to learn something.

Questions:

1) What does the exact restrictionValues mean in the example code below? ( not solved)

2) Why row.ItemArray[2] of all cells is containing the table names? (solved)

3) Is there a better way to get table names out of a database? (solved)

This is the code I have got:

    public List<string> GetTableNames(string tableName, string[] field_names)
    {
       List<string> retTableNames = new List<string>();

       if (dbConnection != null)
       {
          dbConnection.Open();

          string strSQL = "SELECT * ";
          string[] restrictionValues = new string[4] { null, null, null, "TABLE" };

          OleDbCommand cmd = new OleDbCommand(strSQL, dbConnection);
          try
          {
             /*search after all possible tables in dataset*/
             DataTable schemaInformation = dbConnection.GetSchema("Tables", restrictionValues);

             foreach (DataRow row in schemaInformation.Rows)
             {
                retTableNames.Add(row.ItemArray[2].ToString());
             }
          }
          catch
          {
             retTableNames = null;
          }
       }

       return retTableNames;
    }

See sample code as msdn : https://msdn.microsoft.com/en-us/library/ms136366(v=vs.110).aspx — jdweng, Aug 08 '18 at 09:59
I think I also understand now why ... string[4] {null, null, null,...} . But I do not understand why here "TABLE" has been chosen for TABLE_TYPE. What else could it be instead of "TABLE" ? Or is the 4th string not defining TABLE_TYPE ? — Tinitus, Aug 08 '18 at 11:10
I think it just a quirk of oledb. You need to put something into the array to indicate there are four items. You probably can use Object. — jdweng, Aug 08 '18 at 11:23

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

(I just noticed that you said you already get this first part, but I'll leave it in anyway. There's a little more below about "TABLE".)

Some of the explanation of the restrictions is in the Main program in the link that @jdweng gave.

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

The rest of the explanation is in the overload of GetSchema: GetSchema Method (String, String[]), which is what you're using.

Verbatim:

In order to set values on a given restriction, and not set the values of other restrictions, you need to set the preceding restrictions to null and then put the appropriate value in for the restriction that you would like to specify a value for.

An example of this is the "Tables" collection. If the "Tables" collection has three restrictions -- database, owner, and table name--and you want to get back only the tables associated with the owner "Carl", you need to pass in the following values: null, "Carl". If a restriction value is not passed in, the default values are used for that restriction. This is the same mapping as passing in null, which is different from passing in an empty string for the parameter value. In that case, the empty string ("") is considered to be the value for the specified parameter.

A more complete article on schema restrictions.

It looks like you can omit some parameters, which is why the example above only lists 3 restrictions.

I've been looking for an explanations of the "TABLE" parameter but it's hard to find. It's either a default that gets all tables, or it's ignored, or something else. The easiest way to get all table types might be to do a basic DataTable table = connection.GetSchema("Tables"); then get the types of each table to see what the options are. Otherwise, sticking to "TABLE" will no doubt get the commonly used tables, not system tables or anything like that.

Hope your ears are ok.

DataTables: restrictionValues and table names

1 Answers1