public SqlBuilderTableTest() { databaseName = "TESTDB" + DateTime.Now.ToString("yyyyMMddHHmm"); masterConnectionString = String.Format(connectionString, "master"); clientConnectionString = String.Format(connectionString, databaseName); // Create database using (SqlConnection connection = new SqlConnection(masterConnectionString)) { using (var cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = $"if exists(select * from sys.databases where name = '{databaseName}') " + $"Begin " + $"ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE " + $"Drop Database {databaseName} " + $"End " + $"Create Database {databaseName} "; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); } } // Generate the DmSet schema 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 = false; 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); }
public void Properties() { DmSet set = new DmSet("DMSET"); DmTable clientsTable = new DmTable("Clients"); DmTable productsTable = new DmTable("Products"); set.Tables.Add(clientsTable); set.Tables.Add(productsTable); DmColumn productId = new DmColumn <Int32>("Id"); productId.AllowDBNull = false; productId.IsAutoIncrement = true; productId.IsCompute = false; productId.IsUnicode = false; productId.IsUnsigned = false; productsTable.Columns.Add(productId); DmColumn fkClientId = new DmColumn <Guid>("clientId"); fkClientId.AllowDBNull = true; productsTable.Columns.Add(fkClientId); DmColumn productName = new DmColumn <string>("name"); productName.AllowDBNull = true; productName.DbType = System.Data.DbType.StringFixedLength; productName.MaxLength = 150; productId.IsCompute = false; productId.IsUnicode = true; productId.IsUnsigned = true; productsTable.Columns.Add(productName); DmColumn productPrice = new DmColumn <Decimal>("price"); productPrice.AllowDBNull = false; productPrice.DbType = System.Data.DbType.VarNumeric; productPrice.Precision = 6; productPrice.Scale = 2; // for test purpose productId.IsCompute = true; productsTable.Columns.Add(productPrice); productsTable.PrimaryKey = new DmKey(new DmColumn[] { productId, productName, productPrice }); 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); var clientGuid = Guid.NewGuid(); var drClient = clientsTable.NewRow(); drClient["Name"] = "Pertus"; drClient["Id"] = clientGuid; clientsTable.Rows.Add(drClient); var drProduct = productsTable.NewRow(); drProduct["clientId"] = clientGuid; drProduct["name"] = "Ensemble bleu blanc rouge"; drProduct["price"] = 12.23d; productsTable.Rows.Add(drProduct); // Convert to DmSetSurrogate var surrogateDs = new DmSetSurrogate(set); // serialize as json string var stringSurrogate = JsonConvert.SerializeObject(surrogateDs); // deserialize as surrogate var surrogateDsFromJson = JsonConvert.DeserializeObject <DmSetSurrogate>(stringSurrogate); // Convert to DmSet var set2 = surrogateDs.ConvertToDmSet(set); // Assertions on DmSet properties Assert.Equal(set.DmSetName, set2.DmSetName); Assert.Equal(set.Culture, set2.Culture); Assert.Equal(set.CaseSensitive, set2.CaseSensitive); Assert.Equal(set.Relations.Count, set2.Relations.Count); Assert.Equal(set.Tables.Count, set2.Tables.Count); //Assertions on Table properties var productsTable2 = set2.Tables["Products"]; var clientsTable2 = set2.Tables["Clients"]; AssertIsEqual(productsTable, productsTable2); AssertIsEqual(clientsTable, clientsTable2); // Assertions on columns var productId2 = set2.Tables["Products"].Columns["Id"]; AssertIsEqual(productId, productId2); var fkClientId2 = set2.Tables["Products"].Columns["clientId"]; AssertIsEqual(fkClientId, fkClientId2); var productName2 = set2.Tables["Products"].Columns["name"]; AssertIsEqual(productName, productName2); var productPrice2 = set2.Tables["Products"].Columns["price"]; AssertIsEqual(productPrice, productPrice2); var clientId2 = set2.Tables["Clients"].Columns["Id"]; AssertIsEqual(clientId, clientId2); var clientName2 = set2.Tables["Clients"].Columns["Name"]; AssertIsEqual(clientName, clientName2); }
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(); } } }