Creating database structure from code

In some projects, the first class object is not a database structure, but business objects classes structure or description of abstract metadata. In this situation, synchronization of database structure and the main structure is performed manually and as a rule takes much time.

You can easily automate this process with SQL Schema Sync API by generating database structure as object graph based on your metadata.

If an ORM solution is used in the project, information on database structure is in the system. Writing generation procedure is simple as SQL Schema Sync API objects structure is fully isomorphic to MS SQL Server contents.

For example, let’s create description of database structure including two connected tables with primary keys, computable column, default value for column and custom scheme:

Database database = Database.CreateDatabaseWithSystemObjects();

Schema schema = new Schema("TestSchema");

schema.Owner = database.Objects.GetElement("dbo");

database.Objects.Add(schema);

Table masterTable = new Table("Master", schema);

database.Objects.Add(masterTable);

DataColumn masterIdColumn = new DataColumn("id", new DataType.Int(), false);

masterIdColumn.Identity = new Identity();

masterIdColumn.Identity.Increment = 1;

masterIdColumn.Identity.Seed = 1;

masterTable.Columns.Add(masterIdColumn);

IndexColumn indexColumn = new IndexColumn();

indexColumn.Column = masterIdColumn;

PrimaryKey primaryKey = new PrimaryKey();

primaryKey.Name = "PK_Master";

primaryKey.Clustered = true;

primaryKey.Table = masterTable;

primaryKey.Columns.Add(indexColumn);

database.Objects.Add(primaryKey);

DataColumn masterColumn;

masterColumn = new DataColumn("datetimeCol", new DataType.DateTime(), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("decimalCol", new DataType.Decimal(6, 2), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("intCol", new DataType.Int(), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("nvarcharCol", new DataType.NVarChar(100), true);

masterColumn.Collation = "Latin1_General_CI_AS";

masterTable.Columns.Add(masterColumn);

DefaultConstraint masterDefaultConstraint = new DefaultConstraint("decimalColDefaultConstraint", masterTable, masterColumn, "((50))");

database.Objects.Add(masterDefaultConstraint);

Table detailTable = new Table("Detail", database.Objects.GetElement("dbo"));

database.Objects.Add(detailTable);

DataColumn detailIdColumn = new DataColumn("id", new DataType.Int(), false);

detailIdColumn.Identity = new Identity();

detailIdColumn.Identity.Increment = 1;

detailIdColumn.Identity.Seed = 1;

detailTable.Columns.Add(detailIdColumn);

indexColumn = new IndexColumn();

indexColumn.Column = detailIdColumn;

primaryKey = new PrimaryKey("PK_Detail", detailTable);

primaryKey.Clustered = true;

primaryKey.Columns.Add(indexColumn);

database.Objects.Add(primaryKey);

ComputedColumn detailComputedColumn = new ComputedColumn();

detailComputedColumn.Name = "computedCol";

detailComputedColumn.Expression = "([masterId]*(10))";

detailComputedColumn.ExtendedProperties.Add(new ExtendedProperty("compColExtProp", "value"));

detailTable.Columns.Add(detailComputedColumn);

DataColumn detailMasterIdColumn = new DataColumn("masterId", new DataType.Int(), false);

detailTable.Columns.Add(detailMasterIdColumn);

ColumnRef masterIdRef = new ColumnRef(masterIdColumn);

ColumnRef detailMasterIdRef = new ColumnRef(detailMasterIdColumn);

ForeignKey foreignKey = new ForeignKey("FK_Detail_Master", detailTable, masterTable);

foreignKey.Columns.Add(detailMasterIdRef);

foreignKey.ForeignColumns.Add(masterIdRef);

database.Objects.Add(foreignKey);

DataColumn detailColumn = new DataColumn("varCharCol", new DataType.VarChar(), true);

detailColumn.Collation = "Latin1_General_CI_AS";

detailTable.Columns.Add(detailColumn);

During forming database structure from code, it is necessary to pay attention to SQL dependencies between the objects. First of all, this should be applied to the following objects: CheckConstraint, DefaultConstraint and View. As SQL Schema Sync API doesn’t recognize such dependencies automatically, it is necessary to assign them at the stage of forming objects structure by adding corresponding elements in the DependOn collection of every object.

You should also pay attention to the fact that SQL Schema Sync API doesn’t assign default values for such properties as Schema and Owner. In case you create objects featuring the mentioned properties, you should initialize them explicitly.

The delivery package includes demo applications: GenerateDatabaseFromSourceCode which creates database on the basis of object model created in the application source code and GenerateDatabaseFromDataSet, which creates database on the basis of a DataSet.