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();
}
}
}
