SQL meta-model

17 min

See Work with SQL recordsWork with SQL records for information about working with database records. Classes in this topic are available in the Mediachase.BusinessFoundation.Data.Sql and Mediachase.BusinessFoundation.Data.Sql.Management namespaces.

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:

// 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:

// 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

// 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 auto increment primary key:

// 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:

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

Column class

A Columns 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:

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 new NVARCHAR[150] column:

// Step 4. Create a new 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:

// 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:

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:

// 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 transaction callback to handle the final transaction commit or rollback.

Example: Create new table in transaction scope

// 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();
  }
}