---
title: SQL meta-model
slug: commerce-connect-15/docs/sql-meta-model
docTags: 
createdAt: 2026-07-30T20:34:42.000Z
---

For information about working with database records, see [SQL records](docId\:K9aK1N2aHC81l4u9pfu2L).

Classes in this topic are available in the following namespaces:

- `Mediachase.BusinessFoundation.Data.Sql`
- `Mediachase.BusinessFoundation.Data.Sql.Management`

## Initialize

A `SqlContext` object represents a unique entry point to the SQL meta-model. When you create an instance of `SqlContext`, SQL meta-model is loaded, and properties are set to their initial values. Then, you should initialize the `SqlContext.Current` static property to declare the `SqlContext` in the current thread. The `SqlContext` is available in the current thread from `SqlContext.Current` static property. If the `SqlContext` goes out of scope, it is not closed. Therefore, you must explicitly close the context by calling `Dispose`.

The `SqlContext.Current` property is thread safe, and you should initialize it in all threads. When changing the `SqlContext.Current` property, you can connect to different databases, but only one connection will be active.

**Example:** The following example initializes `SqlContext` in the current thread:

```csharp
// Step 0. Connection String
string connectionString = "Data Source=(local);Initial Catalog=TestDatabase;User ID=sa;Password=;";
// Step 1. Initialize Sql Meta Model
SqlContext.Current = new SqlContext(connectionString);
```

## Database class

A `Database` object represents a SQL database. The database gets tables and relationships, creates tables or relationships, drops tables or relationships and creates storage procedures for the table. Use the `SqlContext.Database` property to get the current database object from `SqlContext`.

**Example:** The following example initializes `SqlContext` in the current thread and returns the `Database` object:

```csharp
// Step 0. Connection String
string connectionString = "Data Source=(local);Initial Catalog=TestDatabase;User ID=sa;Password=;";
// Step 1. Initialize Sql Meta Model
using (SqlContext.Current = new SqlContext(connectionString))
{
  // Step 2. Get Database
  Database database = SqlContext.Current.Database;
}
```

## Table class

A `Table` object represents a SQL user table, system table and view. Table gets columns and relationships, adds columns, and removes columns. The `TableType` (`SystemTable`, `Table`, `View`) is set as `Type` property for the Table.

Using the `Table` object, you can get complete information about the table:

- Name
- Owner
- Qualifier
- Remarks
- PrimaryKey
- Columns

### Get collection of tables

The collection of tables is available from the `Database.Tables` property. It returns the `TableCollection` object.

**Example:** Write to trace user tables only:

```csharp
// Step 2. Get Database
Database database = SqlContext.Current.Database;
// Step 3. Enum User Tables
foreach (Table table in database.Tables)
{
  if (table.Type == TableType.Table)
  {
    Trace.WriteLine(table.Name);
  }
}
```

### Create table

Call the CreateTable method of the `Database` class, passing the table name and default columns to create a new table.

**Example:** Create a new table with integers, and an auto-increment primary key:

```csharp
// Create table
Table newTable = SqlContext.Current.Database.CreateTable("Table_1", Column.CreatePrimaryKey("Id", System.Data.SqlDbType.Int, true));
```

### Drop table

Call the `DropTable` method of the `Database` class, passing the table object to the drop table. The method removes a table definition, data, indexes, constraints, and permission specifications for that table.

The `DropTable` method *cannot* be used to drop a table referenced by a `FOREIGN KEY` constraint. The referencing `FOREIGN KEY` constraint, or the referencing table, must be dropped first. Also, you *cannot* use the `DROP TABLE` statement on system tables.

When a table is dropped, rules or defaults on it lose their binding, and any associated constraints are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, and add necessary constraints.

**Example:** Find table by name and drop:

```csharp
// Find table
Table table = SqlContext.Current.Database.Tables["Table_1"];
// Drop table
SqlContext.Current.Database.DropTable(table);
```

## Column class

A `Column` object represents a SQL column. `Column` is used to get column properties.

Using the `From Column` object, you can get complete information about a column:

- Name
- Default Value
- Formula for auto-calculated columns
- Identity
- Identity Increment
- Identity Seed
- Is Nullable
- Is Primary Key
- Length
- Precision
- Scale
- SqlDbType

### Get collection of columns

The collection of columns is available using the `Table.Columns` property. It returns the `ColumnCollection` object.

**Example:** The following example writes columns to trace:

```csharp
foreach (Column column in newTable.Columns)
{
  System.Diagnostics.Trace.WriteLine(column.Name);
}
```

### Add column

Call the `AddColumn` method of the `Table` class, passing the new column object to create a new column.

**Example:** Create a NVARCHAR\[150] column:

```csharp
// Step 4. Create a NVARCHAR[150] column
Column newColumn = new Column("Title", System.Data.SqlDbType.NVarChar, false, 150, "N''", false, false, 0, 0);
newTable.AddColumn(newColumn);
```

### Remove column

Call the `DropColumn` method of the `Table` class, passing the columns object to the drop column.

**Example:** Find column by name and drop:

```csharp
// Step 5. Drop Column
newTable.DropColumn(newTable.Columns["Title"]);
```

## Relationship

A `Relationship` object represents a SQL relationship between two tables and is used to create these types of relationships.

### Get collection of relationships

The collection of relationships is available using the `Table.GetRelationships` method. It returns an array of `Relationship` objects associated with the current table.

### Create relationship

Call the `CreateRelation` method of the `Database` class, passing primary table, foreign table, and foreign column name to create a new relation.

**Example:** Create a new relationship between two tables:

```csharp
SqlContext.Current.Database.CreateRelation(refPTable, srcTable, columnName, bCascadeDelete);
```

### Remove relationship

Call the `DropRelation` method of the `Database` class, passing a relationship object to a drop column.

**Example:** Find column by name and drop:

```csharp
// Step 5. Drop Relationship
SqlContext.Current.Database.DropRelation(relationship);
```

## Table index

A `TableIndex` object represents a SQL index that you can use to view, create, edit, and drop a table.

- Call the `GetTableIndex` static method of the `TableIndex` class, passing the table name to get the entire index from the table.
- Call the `CreateIndex` method of the `TableIndex` class to create a new index.
- Call the `DropIndex` method of the `TableIndex` class to drop an index.

## Transactions

A `SqlTransactionScope` object represents a SQL transaction. Upon instantiating a `SqlTransactionScope` by calling a `SqlContext.BeginTransaction` statement, the transaction manager determines which transaction to participate in. When determined, the scope always participates in that transaction. You can obtain a reference to the ambient transaction by calling the `TransactionScope` property of the `SqlContext` class.

If no exception occurs within the transaction scope (that is, between the initialization of the `SqlTransactionScope` object and the calling of its `Dispose` method), then the transaction in which the scope participates can proceed. If an exception occurs within the transaction scope, the transaction in which it participates is rolled back.

When your application completes the work it wants to perform in a transaction, call the `Commit` method only once to inform the transaction manager to commit the transaction. Failing to call this method aborts the transaction.

A call to the `Dispose` method marks the end of the transaction scope. Exceptions that occur after calling this method may not affect the transaction.

A call to the `AddCallback` method registers a custom callback. You can use a transaction callback to handle the final transaction commit or rollback.

**Example:** Create table in transaction scope:

```csharp
// Step 1. Initialize Sql Meta Model
using (SqlContext.Current = new SqlContext(connectionString))
{
  using (SqlTransactionScope tran = SqlContext.Current.BeginTransaction())
  {
    // Step 2. Get Database
    Database database = SqlContext.Current.Database;
    // Step 3. Create a new table - Book
    Table bookTable = database.CreateTable("Book", Column.CreatePrimaryKey("Id", System.Data.SqlDbType.Int, true));
    // Step N. Call Commit to commit transaction
    tran.Commit();
  }
}
```