Using GetSchema Method (2023)

In This Topic

dotConnect for SugarCRM provides two ways of getting SugarCRM 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 Reference
• GetSchema Resultset Columns
• Samples

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 SugarCRM. 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 SugarCRM 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.Sugar.SugarMetadataCollectionNames 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, somedefault values are assumed, if they were not specified explicitly.

GetSchema Resultset Columns

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

Columns

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

DataSourceInformation

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

DataTypes

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

ForeignKeyColumns

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

ForeignKeys

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

MetaDataCollections

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

PrimaryKeyColumns

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

PrimaryKeys

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

ReservedWords

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

Restrictions

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

Tables

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

UniqueKeyColumns

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

UniqueKeys

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

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(SugarConnection 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 SugarConnection, 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 SugarDataReader

See Also

Metadata |Metadata Overview |Information Schema |