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:
C#
string connectionString = "Data Source=(local);Initial Catalog=TestDatabase;User ID=sa;Password=;";
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:
C#
string connectionString = "Data Source=(local);Initial Catalog=TestDatabase;User ID=sa;Password=;";
using (SqlContext.Current = newSqlContext(connectionString))
{
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:
C#
Database database = SqlContext.Current.Database;
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:
C#
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.
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
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:
C#
Table table = SqlContext.Current.Database.Tables["Table_1"];
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:
C#
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:
C#
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:
C#
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:
C#
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:
C#
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:
C#
using (SqlContext.Current = new SqlContext(connectionString))
{
using (SqlTransactionScope{color} tran = SqlContext.Current.BeginTransaction())
{
Database database = SqlContext.Current.Database;
Table bookTable = database.CreateTable("Book",
Column.CreatePrimaryKey("Id", System.Data.SqlDbType.Int, true));
tran.Commit();
}
}
Do you find this information helpful? Please log in to provide feedback.