dotConnect for FreshBooks Documentation
In This Topic
    Using GetSchema Method
    In This Topic

    dotConnect for FreshBooks provides two ways of getting FreshBooks metadata: GetSchema method and metadata tables, compliant with the SQL-92 information schema standard.

    GetSchema method allows you to read server schema information without writing queries and parsing the output. All information you may want to obtain is brought to you by single function in easy-to-process format. You can get information on tables, columns, and constraints. The method is introduced in System.Data.Common.DbConnection.

    This article consists of the following sections:

    How To Use

    GetSchema method is available in three overloads, each of them serves its own purpose. All overloads return System.Data.DataTable object that contains information about server elements.

    public virtual abstract DataTable GetSchema();
    
    
    Overloads Public Overridable MustOverride Function GetSchema() As DataTable
    
    

    If you call the GetSchema method without parameters, or with single parameter "MetaDataCollections" (which is actually the same), the table object returned by the method will contain three columns. The first field of every row is a keyword allowed to be passed to the method (as collectionName argument). The second field is the number of restriction values for this keywords (passed through restrictionValues argument). The third field is not used in dotConnect for FreshBooks. It is always zero.

    public virtual abstract DataTable GetSchema(
       string collectionName
    );
    
    
    Overloads Public Overridable MustOverride Function GetSchema( _
       ByVal collectionName As String _
    ) As DataTable
    
    

    GetSchema with 1 argument returns general information about the collection queried. For example, GetSchema("Tables") returns the list of the tables (objects) in your FreshBooks account.

    public virtual abstract DataTable GetSchema(
       string collectionName,
       string[] restrictionValues
    );
    
    
    Overloads Public Overridable MustOverride Function GetSchema( _
       ByVal collectionName As String, _
       ByVal restrictionValues() As String _
    ) As DataTable
    
    

    In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the table name (in this case, only collection elements belonging to this table will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.

    Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.FreshBooks.FreshBooksMetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.

    GetSchema Method Reference

    The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. When calling the GetSchema method, you can pass all or few arguments. In the latter case, some default values are assumed, if they were not specified explicitly.

    Collection Name

    Number of restrictions

    Return value

    Restriction descriptions

    Columns

    2

    Returns list of columns, their type and some extra information.

    • The first restriction is the name of a table that the GetSchema method should search in.
    • The second one allows you to set the column name as described in "Tables" collection.
    DataSourceInformation

    0

    Returns information about the data source.

     

    DataTypes

    0

    Returns information about data types supported by the data source.

     

    ForeignKeyColumns

    3

    Returns the list of columns used by foreign keys.

    • The first restriction for this collection is name of a table.
    • The second restriction is the foreign key name.
    • The third restriction is the referenced table name.
    ForeignKeys

    3

    Returns the list of foreign keys.

    • The first restriction is the table name.
    • The second restriction is the key name.
    • The third restriction is the referenced table name.
    IndexColumns

    2

    Returns the list of columns that participate in indexes.

    • The first restriction for this collection is name of a table.
    • The second restriction is the index name.
    Indexes

    2

    Returns the list of indexes.

    • The first restriction for this collection is name of a table.
    • The second restriction is the index name.
    MetaDataCollections

    0

    Returns this list. Same as using GetSchema() method without parameters.

     

    PrimaryKeyColumns

    2

    Returns the list of columns that participate in primary keys.

    • The first restriction for this collection is name of a table.
    • The second restriction is the Primary Key name.
    PrimaryKeys

    2

    Returns the list of primary keys.

    • The first restriction is table name.
    • The second restriction is primary key name.
    ReservedWords

    0

    Lists all reserved words used in the server.

     

    Restrictions

    0

    Returns the list of possible restrictions and their default values for the metadata collections.

     

    Tables

    1

    GetSchema("Tables") returns the list of the account tables (objects).

    • The only restriction is the table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve names of tables that match the mask.
    UniqueKeyColumns

    3

    Returns the list of columns that participate in unique keys.

    • The first restriction for this collection is name of a table.
    • The second restriction is the Unique Key name.
    • The second restriction is the column name.
    UniqueKeys

    1

    Returns the list of unique keys of the specified tables.

    • The first restriction is table name.
    • The second restriction is key name.

    GetSchema Resultset Columns

    For each metadata collection, dotConnect for FreshBooks returns DataTable with its own set of columns.

    Columns

    For the Columns collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Table

    string

    The table, containing the column.
    Name

    string

    The name of the column.
    Position

    int

    The position of the column in the table.
    DataType

    string

    The data type of the column.
    ProviderType

    int

    The provider type of the column. Corresponds to one of the System.Data.DbType values.
    NativeProviderType

    int

    The provider type of the column. Corresponds to one of the FreshBooksType values.
    Length

    Int64

    The length of the column.
    Precision

    int

    The numeric precision of the column. Available for numeric column data types.
    Scale

    int

    The scale of the column. Available for numeric column data types.
    HasDefaultValue

    bool

    Indicates whether the column has a default value.
    DefaultValue

    string

    The default value of the column.
    Nullable

    bool

    Indicates whether the column is nullable.
    IsUnique

    bool

    Indicates whether the column values must be unique.
    IsPrimaryKey

    bool

    Determines whether the column is a part of the table primary key.
    KeyName

    string

    The name of the key that includes the column.
    Required

    bool

    Indicates whether FreshBooks forbids the NULL value for this column.
    Queryable

    bool

    Indicates whether FreshBooks allows querying data in the column.
    Createable

    bool

    Indicates whether FreshBooks allows inserting data to this column.
    Updateable

    bool

    Indicates whether FreshBooks allows updating data in the column.
    Sortable

    bool

    Indicates whether the column can be included into the ORDER BY clause. Always 1.
    Filterable

    bool

    Indicates whether the column can be included into the WHERE clause. Always 1.
    Custom

    bool

    Indicates whether this column is a custom column.
    IsEnum

    bool

    Indicates whether this column is of Enum type in the data source (can have one of the values assigned from a predefined set).
    IsMultiEnum

    bool

    Indicates whether this column is of Enum type in the data source (can have one or several values from a predefined set assigned at the same time).
    Flags

    int

    An integer field, that presents the information about the column as a set of flags. The bits have the following meaning (starting from the rightmost bit):

    • Whether the column is a custom one.
    • Whether FreshBooks forbids the NULL value for this column.
    • Whether the column is computed.
    • Whether the column belongs to the table primary key.
    • Whether the column belongs to a table unique key.
    • Whether the column can contain NULL values.
    • Whether you can perform INSERT into this column.
    • Whether you can perform UPDATE of the column data.
    • Whether the column can be included into the ORDER BY clause. Always set.
    • Whether the column can be included into the WHERE clause. Always set.
    • Whether the column has a default value.
    • Whether the column is an unsigned numeric column.
    • Whether the column has an enum type.
    • Whether the column has a multienum type.
    Unsigned

    bool

    Indicates whether this column does not store numeric sign. Available for numeric column data types.

    DataSourceInformation

    For the DataSourceInformation collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    DataSourceProductName

    string

    The name of the product accessed by the provider, such as FreshBooks.
    DataSourceProductVersion

    string

    The version of the product accessed by the provider in the FreshBooks native format.
    DataSourceProductVersionNormalized

    string

    The version of the product accessed by the provider, normalized so that it can be compared with String.Compare().
    ParameterMarkerFormat

    string

    A format string that represents how to format a parameter. For example, for named parameters, prefixed by a prefix character, it would be prefix character followed by "{0}" (without quotes).
    ParameterMarkerPattern

    string

    A regular expression that matches a parameter marker. It will match parameter names with the prefix.
    ParameterNameMaxLength

    int

    The maximum length of parameter names in characters.
    ParameterNamePattern

    string

    A regular expression that matches valid parameter names.
    StatementSeparatorPattern

    string

    A regular expression that matches the statement separator.
    StringLiteralPattern

    string

    A regular expression that matches a string literal, and its match value is the literal itself.
    NumberOfIdentifierParts

    int

    Always 1.
    QuotedIdentifierPattern

    string

    A regular expression that matches a quoted identifier, and its match value is the literal itself.

    DataTypes

    For the DataTypes collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    TypeName

    string

    The name of the .NET type.
    ProviderDbType

    string

    The corresponding provider type.
    DataType

    string

    The corresponding FreshBooks data type.
    IsConcurrencyType

    string

    Indicates whether the values of this data type are updated by the data source every time the row is changed and the value of the column of this data type is different from all previous values.

    ForeignKeyColumns

    For the ForeignKeyColumns collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the foreign key column.
    Table

    string

    The name of the table, containing the foreign key.
    ReferencedTable

    string

    The name of the table, referenced by the foreign key.
    ReferencedConstraint

    string

    The name of the primary key, referenced by the foreign key.
    Position

    int

    The position of the column in the foreign key.
    ReferencedColumn

    string

    The name of the primary key column, referenced by the foreign key column.
    Constraint

    string

    The name of the foreign key, containing the column.
    CascadeDelete

    string

    Indicates whether the foreign key has the cascade delete rule.

    ForeignKeys

    For the ForeignKeys collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the foreign key.
    Table

    string

    The name of the table, containing the foreign key.
    ReferencedTable

    string

    The name of the table, referenced by the foreign key.
    ReferencedConstraint

    string

    The name of the primary key, referenced by the foreign key.
    CascadeDelete

    string

    Indicates whether the foreign key has the cascade delete rule.

    MetaDataCollections

    For the MetaDataCollections collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    CollectionName

    string

    The name of the collection.
    NumberOfRestrictions

    int

    The cnumber of restrictions for the collection.

    PrimaryKeyColumns

    For the PrimaryKeyColumns collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the primary key column.
    Table

    string

    The name of the table, containing the primary key.
    Constraint

    string

    The name of the primary key, containing the column.
    Position

    int

    The position of the column in the primary key.

    PrimaryKeys

    For the PrimaryKeys collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the primary key.
    Table

    string

    The name of the table, containing the primary key.

    ReservedWords

    For the ReservedWords collection, the result DataTable contains the following column:

    Column Name

    Data type

    Description

    ReservedWord

    string

    The reserved word.

    Restrictions

    For the Restrictions collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    CollectionName

    string

    The name of the collection.
    RestrictionName

    string

    The name of the restriction.
    RestrictionDefault

    string

    The default value, used if the restriction is not specified.
    RestrictionNumber

    string

    The number of the restriction for the metadata collection.

    Tables

    For the Tables collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the table.
    Createable

    bool

    Indicates whether the table allows inserts.
    Updateable

    bool

    Indicates whether the table allows updates.
    Deletable

    bool

    Indicates whether the table allows deletes.
    Queryable

    bool

    Indicates whether the table allows querying.
    Custom

    bool

    Indicates whether these table is a custom object.

    UniqueKeyColumns

    For the UniqueKeyColumns collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the unique key column.
    Table

    string

    The name of the table, containing the unique key.
    Constraint

    string

    The name of the unique key, containing the column.
    Position

    int

    The position of the column in the unique key.

    UniqueKeys

    For the UniqueKeys collection, the result DataTable contains the following columns:

    Column Name

    Data type

    Description

    Name

    string

    The name of the unique key.
    Table

    string

    The name of the table, containing the unique key.

    Samples

    The following code fragment is an elegant way to detect existence of a table.

    string tableName = "Account";
    if (myConnection.GetSchema("Tables", new string[] {tableName }).Rows.Count > 0)
    {
    Console.WriteLine("Table " + tableName + " exists in the catalog.");
    }
    
    
    Dim tableName As String = "Account"
    Dim restrictions() As String = { tableName}
    If (myConnection.GetSchema("Tables", restrictions).Rows.Count > 0) Then
      Console.WriteLine("Table " + tableName + " exists in the catalog.")
    End If
    
    

    The next sample shows how to retrieve columns information from a table and render it to console.

    static void GetTableInfo(FreshBooksConnection myConnection, string tableName)
    {
      myConnection.Open();
      DataTable myDataTable = myConnection.GetSchema(
      "Columns", new string[] {  tableName });
      for (int i = 0; i < myDataTable.Columns.Count; i++)
      {
        Console.Write(myDataTable.Columns[i].Caption + "\t");
      }
      Console.WriteLine();
      foreach (DataRow myRow in myDataTable.Rows)
      {
        foreach (DataColumn myCol in myDataTable.Columns)
        {
          Console.Write(myRow[myCol] + "\t");
        }
        Console.WriteLine();
      }
      myConnection.Close();
    }
    
    
    Public Sub GetTableInfo(ByVal myConnection As FreshBooksConnection, ByVal tableName As String)
      myConnection.Open()
      Dim restrictions() As String = { tableName}
      Dim myDataTable As DataTable = myConnection.GetSchema("Columns", restrictions)
      Dim i As Int32
      For i = 0 To myDataTable.Columns.Count - 1
        Console.Write(myDataTable.Columns(i).Caption & Chr(9))
      Next
      Console.WriteLine()
      Dim myRow As DataRow
      Dim myCol As DataColumn
      For Each myRow In myDataTable.Rows
        For Each myCol In myDataTable.Columns
          Console.Write(myRow(myCol) & Chr(9))
        Next
        Console.WriteLine()
      Next
      myConnection.Close()
    End Sub
    
    

    Also you can get a metadata of query result set using the GetSchemaTable method of FreshBooksDataReader

    See Also

    Metadata | Metadata Overview | Information Schema |