VistaDBDataReader GetSchemaTable example

In This Topic

The VistaDBDataReader class's GetSchemaTable method is similar to using a DataTable FillSchema method. Each method lets you create a DataTable contains DataColumn objects that correspond to the columns returned by your query. The GetSchemaTable method accepts no parameters and returns a new DataTable object.

The purpose of the GetSchemaTable call is to allow you to pull schema information based upon a query. You build up the query, but rather than executing it normally you tell the provider to get just the schema for the query (it is not actually run).

VistaDBDataReader myReader = cmd.ExecuteReader( CommandBehavior.SchemaOnly );

Setting the CommandBehavior to SchemaOnly will make a complete round trip to the database, load the schema and it's information into the reader. Then you can call the Reader with the GetSchemaTable to get the information into a useable format.

DataTable schemaTable = myReader.GetSchemaTable();

Now the schemaTable can be walked to determine what the provider said about the query.

Example

Example Title	Copy Code
// This example loads the employees table from Northwind static void Main( string[] args ) { string dbName = @"C:\Users\Public\Documents\VistaDB 6\Databases\Northwind.vdb6"; string[] restrictions = new string[4] { null, null, "Employees", null }; dataGridView1.Rows.Clear(); using (VistaDB.Provider.VistaDBConnection connection = new VistaDB.Provider.VistaDBConnection( "Data Source=" + dbName )) { connection.Open(); using (VistaDBCommand cmd = new VistaDBCommand("Select * from Employees", connection)) { using (VistaDBDataReader myReader = cmd.ExecuteReader( CommandBehavior.SchemaOnly )) { //Retrieve column schema into a DataTable. DataTable schemaTable = myReader.GetSchemaTable(); foreach (DataRow myField in schemaTable.Rows) { foreach (DataColumn myProperty in schemaTable.Columns) { Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString()); } } } } } }

Example Title

Copy Code

// This example loads the employees table from Northwind
static void Main( string[] args )
{
   string dbName = @"C:\Users\Public\Documents\VistaDB 6\Databases\Northwind.vdb6";
   string[] restrictions = new string[4] { null, null, "Employees", null };

   dataGridView1.Rows.Clear();

   using (VistaDB.Provider.VistaDBConnection connection = new VistaDB.Provider.VistaDBConnection( "Data Source=" + dbName ))
   {
      connection.Open();

      using (VistaDBCommand cmd = new VistaDBCommand("Select * from Employees", connection))
      {
         using (VistaDBDataReader myReader =
               cmd.ExecuteReader( CommandBehavior.SchemaOnly ))
         {
            //Retrieve column schema into a DataTable.
            DataTable schemaTable = myReader.GetSchemaTable();

            foreach (DataRow myField in schemaTable.Rows)
            {
               foreach (DataColumn myProperty in schemaTable.Columns)
               {
                       Console.WriteLine(myProperty.ColumnName + " = " +
                             myField[myProperty].ToString());

               }
            }
         }
      }
   }
}

The above will output a console list like this listed below for each column in the table.

ColumnName = EmployeeID
ColumnOrdinal = 0
ColumnSize = 4
NumericPrecision = 255
NumericScale = 255
IsUnique = True
IsKey = True
BaseServerName = 
BaseCatalogName = 
BaseColumnName = EmployeeID
BaseSchemaName = 
BaseTableName = Employees
DataType = System.Int32
AllowDBNull = False
ProviderType = 10
IsAliased = False
IsExpression = False
IsIdentity = True
IsAutoIncrement = True
IsRowVersion = False
IsHidden = False
IsLong = False
IsReadOnly = False
ProviderSpecificDataType = System.Int32
DefaultValue = 
DataTypeName = Int