Columns
To access metadata for the columns available in the database, retrieve the Columns schema collection. You can also access the column metadata for views by retrieving the ViewColumns schema collection.
Alternatively, retrieve metadata from Result Sets. The same columns are returned for result set metadata as the Columns and ViewColumns schema collections; see below.
Retrieving Column Metadata
Call the GetSchema method of the AcumaticaConnection class to retrieve the Columns or ViewColumns schema collections. You can restrict results by table name, as shown in the example below.
C#
string connectionString = "Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;"; using (AcumaticaConnection conn = new AcumaticaConnection(connectionString)) { conn.Open(); DataTable databaseSchema = conn.GetSchema("Columns", new string[] {"Events"}); foreach (DataRow column in databaseSchema.Rows) { Console.WriteLine(column["COLUMN_NAME"]); Console.WriteLine(column["IS_KEY"]); Console.WriteLine(column["DATA_TYPE"]); } }
VB.NET
Dim connectionString As String = "Url = https://try.acumatica.com/ISV/entity/Default/17.200.001/;User=user;Password=password;Company=CompanyName;" Using conn As New AcumaticaConnection(connectionString) conn.Open() Dim databaseSchema As DataTable = conn.GetSchema("Columns", New String() {"Events"}) For Each column As DataRow In databaseSchema.Rows Console.WriteLine(column("COLUMN_NAME")) Console.WriteLine(column("IS_KEY")) Console.WriteLine(column("DATA_TYPE")) Next End Using
Columns Returned
The Columns and ViewColumns schema collections and DataTables returned from the query contain the following columns.
Column Name | Data Type | Description |
TABLE_CATALOG | System.String | The database containing the table. |
TABLE_SCHEMA | System.String | The schema containing the table. |
TABLE_NAME | System.String | The table containing the column. |
COLUMN_NAME | System.String | The column name. |
ORDINAL_POSITION | System.Int32 | The sequence number of the column. |
COLUMN_DEFAULT | System.String | The default value of the column. |
IS_NULLABLE | System.String | Whether the column allows null values. This value is YES or NO. |
DATA_TYPE | System.String | The column data type. |
CHARACTER_MAXIMUM_LENGTH | System.Int32 | The maximum length in characters of a column with character data. |
NUMERIC_PRECISION | System.Int32 | The maximum number of digits allowed for numeric data. |
NUMERIC_SCALE | System.Int32 | The maximum column scale or the number of digits to the right of the decimal point in numeric data. |
DATETIME_PRECISION | System.Int32 | Returns the precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL. |
CHARACTER_SET_NAME | System.String | The name of the character set for a column with character data. |
COLUMN_COMMENT | System.String | A brief description of the column. |
PROVIDER_TYPE | System.Type | Indicates the appropriate data type dependent on the language you are executing in. |