public void BuilderTable_CreateTable() { var provider = new SqlSyncProvider(clientConnectionString); using (var connection = provider.CreateConnection()) { var options = DbBuilderOption.CreateOrUseExistingSchema; var builder = provider.GetDatabaseBuilder(set.Tables["Products"], options); var tableBuilder = builder.CreateTableBuilder(connection); tableBuilder.TableDescription = builder.TableDescription; connection.Open(); // Check if we need to create the tables if (tableBuilder.NeedToCreateTable(options)) { tableBuilder.CreateTable(); tableBuilder.CreatePrimaryKey(); tableBuilder.CreateForeignKeyConstraints(); } connection.Close(); } // Check result using (var connection = new SqlConnection(clientConnectionString)) { var table = set.Tables["Products"]; // Check Columns var commandColumn = $"Select col.name as name, col.column_id, typ.name as [type], col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity from sys.columns as col " + $"Inner join sys.tables as tbl on tbl.object_id = col.object_id " + $"Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " + $"Where tbl.name = @tableName " + $"Order by col.column_id"; ObjectNameParser tableNameParser = new ObjectNameParser(table.TableName); DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore); using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName); connection.Open(); using (var reader = sqlCommand.ExecuteReader()) { dmTable.Fill(reader); } connection.Close(); } // Check columns number Assert.Equal(4, dmTable.Rows.Count); var rows = dmTable.Rows.OrderBy(r => (int)r["column_id"]).ToList(); var c = rows[0]; var name = c["name"].ToString(); var ordinal = (int)c["column_id"]; var typeString = c["type"].ToString(); var maxLength = (Int16)c["max_length"]; var precision = (byte)c["precision"]; var scale = (byte)c["scale"]; var isNullable = (bool)c["is_nullable"]; var isIdentity = (bool)c["is_identity"]; Assert.Equal("Id", name); Assert.False(isNullable); Assert.True(isIdentity); c = rows[1]; name = c["name"].ToString(); ordinal = (int)c["column_id"]; typeString = c["type"].ToString(); maxLength = (Int16)c["max_length"]; precision = (byte)c["precision"]; scale = (byte)c["scale"]; isNullable = (bool)c["is_nullable"]; isIdentity = (bool)c["is_identity"]; Assert.Equal("clientId", name); Assert.True(isNullable); Assert.False(isIdentity); c = rows[2]; name = c["name"].ToString(); ordinal = (int)c["column_id"]; typeString = c["type"].ToString(); maxLength = (Int16)c["max_length"]; precision = (byte)c["precision"]; scale = (byte)c["scale"]; isNullable = (bool)c["is_nullable"]; isIdentity = (bool)c["is_identity"]; Assert.Equal("name", name); Assert.False(isNullable); Assert.Equal(300, maxLength); Assert.Equal("nvarchar", typeString); c = rows[3]; name = c["name"].ToString(); ordinal = (int)c["column_id"]; typeString = c["type"].ToString(); maxLength = (Int16)c["max_length"]; precision = (byte)c["precision"]; scale = (byte)c["scale"]; isNullable = (bool)c["is_nullable"]; isIdentity = (bool)c["is_identity"]; Assert.Equal("salary", name); Assert.False(isNullable); Assert.Equal(6, precision); Assert.Equal(2, scale); } }
public void BuilderTable_CreateTrackingTable() { var provider = new SqlSyncProvider(clientConnectionString); using (var connection = provider.CreateConnection()) { var options = DbBuilderOption.CreateOrUseExistingSchema; var builder = provider.GetDatabaseBuilder(set.Tables["Products"], options); var tableBuilder = builder.CreateTrackingTableBuilder(connection); tableBuilder.TableDescription = builder.TableDescription; connection.Open(); // Check if we need to create the tables tableBuilder.CreateTable(); tableBuilder.CreatePk(); tableBuilder.CreateIndex(); connection.Close(); } // Check result using (var connection = new SqlConnection(clientConnectionString)) { var table = set.Tables["Products"]; // Check Columns var commandColumn = $"Select col.name as name, col.column_id, typ.name as [type], col.max_length, col.precision, col.scale, col.is_nullable, col.is_identity from sys.columns as col " + $"Inner join sys.tables as tbl on tbl.object_id = col.object_id " + $"Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " + $"Where tbl.name = @tableName " + $"Order by col.column_id"; ObjectNameParser tableNameParser = new ObjectNameParser(table.TableName + "_tracking"); DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore); using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName); connection.Open(); using (var reader = sqlCommand.ExecuteReader()) { dmTable.Fill(reader); } connection.Close(); } // Check columns number Assert.Equal(10, dmTable.Rows.Count); var rows = dmTable.Rows.OrderBy(r => (int)r["column_id"]).ToList(); var c = rows[0]; var name = c["name"].ToString(); Assert.Equal("Id", name); c = rows[1]; name = c["name"].ToString(); Assert.Equal("name", name); c = rows[2]; name = c["name"].ToString(); Assert.Equal("salary", name); c = rows[3]; name = c["name"].ToString(); Assert.Equal("create_scope_id", name); c = rows[4]; name = c["name"].ToString(); Assert.Equal("update_scope_id", name); c = rows[5]; name = c["name"].ToString(); Assert.Equal("create_timestamp", name); c = rows[6]; name = c["name"].ToString(); Assert.Equal("update_timestamp", name); c = rows[7]; name = c["name"].ToString(); Assert.Equal("timestamp", name); c = rows[8]; name = c["name"].ToString(); Assert.Equal("sync_row_is_tombstone", name); c = rows[9]; name = c["name"].ToString(); Assert.Equal("last_change_datetime", name); } }
private static void TestCreateTrackingTable(SqlSyncProvider provider) { DmSet set = new DmSet(); DmTable clientsTable = new DmTable("Clients"); DmTable productsTable = new DmTable("Products"); // orders matters !! set.Tables.Add(clientsTable); set.Tables.Add(productsTable); DmColumn id = new DmColumn <Int32>("Id"); id.AllowDBNull = false; id.AutoIncrement = true; productsTable.Columns.Add(id); DmColumn fkClientId = new DmColumn <Guid>("clientId"); fkClientId.AllowDBNull = true; productsTable.Columns.Add(fkClientId); DmColumn name = new DmColumn <string>("name"); name.AllowDBNull = true; name.DbType = System.Data.DbType.StringFixedLength; name.MaxLength = 150; productsTable.Columns.Add(name); DmColumn salary = new DmColumn <Decimal>("salary"); salary.AllowDBNull = false; salary.DbType = System.Data.DbType.VarNumeric; salary.Precision = 6; salary.Scale = 2; productsTable.Columns.Add(salary); productsTable.PrimaryKey = new DmKey(new DmColumn[] { id, name, salary }); DmColumn clientId = new DmColumn <Guid>("Id"); clientId.AllowDBNull = false; clientsTable.Columns.Add(clientId); DmColumn clientName = new DmColumn <string>("Name"); clientsTable.Columns.Add(clientName); clientsTable.PrimaryKey = new DmKey(clientId); // ForeignKey DmRelation fkClientRelation = new DmRelation("FK_Products_Clients", clientId, fkClientId); productsTable.AddForeignKey(fkClientRelation); DbConnection connection = null; try { using (connection = provider.CreateConnection()) { foreach (var table in set.Tables) { var builder = provider.GetDatabaseBuilder(table, DbBuilderOption.CreateOrUseExistingSchema); if (table.TableName == "Clients") { builder.AddFilterColumn("Id"); } if (table.TableName == "Products") { builder.AddFilterColumn("clientId"); } builder.Apply(connection); Console.WriteLine(builder.Script(connection)); } } } catch (Exception ex) { Console.WriteLine(ex); return; } finally { if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } }