SQL Meta Model

Introduction

This section describes how to work with the SQL Meta Model from Business Foundation (BF). For information on how to work with database records refer to Working with SQL Records. All classes are available in the Mediachase.BusinessFoundation.Data.Sql and Mediachase.BusinessFoundation.Data.Sql.Management namespaces.

Initialization

An 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 all 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 will be available in the current thread fromSqlContext.Current static property. If the SqlContext goes out of scope, it will not be closed. Therefore, you must explicitly close the context by calling Dispose.

The SqlContext.Current property is thread save 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. Initiaze Sql Meta Model
SqlContext.Current = new SqlContext(connectionString);

Database class

A Database object represent an SQL database. The database is used to get tables and relationships, create tables or relationships, drop tables or relationships and create storage procedures for the table. You should 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. Initiaze Sql Meta Model
using (SqlContext.Current = newSqlContext(connectionString))
{
// Step 2. Get Database
Database database = SqlContext.Current.Database;
}

Table class

A Table object represents an SQL user table, system table and view. Table is used to get columns and relationships, add columns, and remove 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: The following example writes to only trace user tables:

// 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 and all data, indexes, constraints, and permission specifications for that table.

When a table is dropped, rules or defaults on it lose their binding, and any constraints associated with it are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, and add all 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 an 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 all 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\] columnsColumn 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 Columns
newTable.DropColumn(newTable.Columns\["Title"\]);

Relationship

A Relationship object represents an SQL relationship between two tables and it 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 that are 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 relationship object to drop column.

Example: Find column by name and drop:

// Step 5. Drop RelationShip
SqlContext.Current.Database.DropRelation(relationship);

Table index

A TableIndex object represents an SQL index. It can be used 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 index

Transactions

An SqlTransactionScope object represents an SQL transaction. Upon instantiating an SqlTransactionScope by calling SqlContext.BeginTransaction statement, the transaction manager determines which transaction to participate in. Once 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 is allowed to proceed. If an exception does occur within the transaction scope, the transaction in which it participates will be rolled back.

When your application completes all work it wants to perform in a transaction, you should call the Commit method only once to inform that transaction manager that it is acceptable 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 custom callback. You can use transaction callback to handle the final transaction commit or rollback.

Example: Create a new table in the transaction scope:

// Step 1. Initiaze Sql Meta Model
using (SqlContext.Current = new SqlContext(connectionString))
{
  using (SqlTransactionScope{color} tran = SqlContext.Current.BeginTransaction())
  {
   // Step 2. Get Database{color}
   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{color}
   tran.Commit();
  }
}