/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable PrimaryKeysForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
var commandColumn = @"select ind.name, col.name as columnName, ind_col.column_id, ind_col.key_ordinal
from sys.indexes ind
left outer join sys.index_columns ind_col on ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id
inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id
inner join sys.tables tbl on tbl.object_id = ind.object_id
where tbl.name = @tableName and ind.index_id >= 0 and ind.type <> 3 and ind.type <> 4 and ind.is_hypothetical = 0 and ind.is_primary_key = 1
order by ind.index_id, ind_col.key_ordinal";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var dmTable = new DmTable(tableNameNormalized);
using (var sqlCommand = new SqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Get relations table
/// </summary>
internal static DmTable RelationsForTable(OracleConnection connection, OracleTransaction transaction, string tableName)
{
var commandRelations = @"SELECT a.constraint_name AS ForeignKey, a.table_name AS TableName, a.column_name AS ColumnName,
c_pk.table_name AS ReferenceTableName, b.column_name AS ReferenceColumnName
FROM user_cons_columns a
JOIN user_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN user_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
JOIN user_cons_columns b ON C_PK.owner = b.owner
AND C_PK.CONSTRAINT_NAME = b.constraint_name AND b.POSITION = a.POSITION
WHERE c.constraint_type = 'R' AND a.table_name = :tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (OracleCommand oracleCommand = new OracleCommand(commandRelations, connection, transaction))
{
oracleCommand.Parameters.Add("tableName", tableNameParser.ObjectName);
using (var reader = oracleCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable RelationsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandRelations = @"Select CONSTRAINT_NAME as ForeignKey,
TABLE_NAME as TableName,
COLUMN_NAME as ColumnName,
REFERENCED_TABLE_NAME as ReferenceTableName,
REFERENCED_COLUMN_NAME as ReferenceColumnName
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Where upper(TABLE_SCHEMA) = upper(@schema)
and REFERENCED_TABLE_NAME is not null and upper(TABLE_NAME) = upper(@tableName)";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (MySqlCommand sqlCommand = new MySqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
sqlCommand.Parameters.AddWithValue("@schema", connection.Database);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable RelationsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandRelations = @"
SELECT
ke.CONSTRAINT_NAME as ForeignKey,
ke.POSITION_IN_UNIQUE_CONSTRAINT as ForeignKeyOrder,
ke.referenced_table_name as TableName,
ke.REFERENCED_COLUMN_NAME as ColumnName,
ke.table_name ReferenceTableName,
ke.COLUMN_NAME ReferenceColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
and ke.REFERENCED_TABLE_SCHEMA = schema()
AND ke.REFERENCED_TABLE_NAME = @tableName
ORDER BY
ke.referenced_table_name;";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.ObjectNameNormalized);
using (MySqlCommand sqlCommand = new MySqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Get columns for table
/// </summary>
public static DmTable ColumnsForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
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_computed, col.is_identity, ind.is_unique " +
$"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 " +
$"Left outer join sys.indexes ind on ind.object_id = col.object_id and ind.index_id = col.column_id " +
$"Where tbl.name = @tableName";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var dmTable = new DmTable(tableNameNormalized);
using (var sqlCommand = new SqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable RelationsForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
var commandRelations = @"SELECT f.name AS ForeignKey,
constraint_column_id as ForeignKeyOrder,
OBJECT_NAME (f.referenced_object_id) AS TableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ColumnName,
OBJECT_NAME(f.parent_object_id) AS ReferenceTableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.referenced_object_id) = @tableName";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var dmTable = new DmTable(tableNameNormalized);
using (var sqlCommand = new SqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Try to get a source row
/// </summary>
/// <returns></returns>
private DmTable GetRow(DmRow sourceRow)
{
// Get the row in the local repository
DbCommand selectCommand = GetCommand(DbCommandType.SelectRow);
// Deriving Parameters
this.SetCommandParameters(DbCommandType.SelectRow, selectCommand);
this.SetColumnParametersValues(selectCommand, sourceRow);
var alreadyOpened = Connection.State == ConnectionState.Open;
// Open Connection
if (!alreadyOpened)
{
Connection.Open();
}
if (Transaction != null)
{
selectCommand.Transaction = Transaction;
}
var dmTableSelected = new DmTable(this.TableDescription.TableName);
try
{
using (var reader = selectCommand.ExecuteReader())
dmTableSelected.Fill(reader);
// set the pkey since we will need them later
var pkeys = new DmColumn[this.TableDescription.PrimaryKey.Columns.Length];
for (int i = 0; i < pkeys.Length; i++)
{
var pkName = this.TableDescription.PrimaryKey.Columns[i].ColumnName;
pkeys[i] = dmTableSelected.Columns.First(dm => this.TableDescription.IsEqual(dm.ColumnName, pkName));
}
dmTableSelected.PrimaryKey = new DmKey(pkeys);
}
catch (Exception ex)
{
Debug.WriteLine("Server Error on Getting a row : " + ex.Message);
throw;
}
finally
{
// Close Connection
if (!alreadyOpened)
{
Connection.Close();
}
}
return(dmTableSelected);
}
internal static DmTable RelationsForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName)
{
var commandRelations = @"select
con.constraint_name as ""foreignKey"",
att2.attname as ""columnName"",
cl.relname as ""referenceTableName"",
att.attname as ""referenceColumnName"",
con.child_table as ""tableName"",
con.child_schema
from
(select
unnest(con1.conkey) as ""parent"",
unnest(con1.confkey) as ""child"",
con1.conname as constraint_name,
con1.confrelid,
con1.conrelid,
cl.relname as child_table,
ns.nspname as child_schema
from
pg_class cl
join pg_namespace ns on cl.relnamespace = ns.oid
join pg_constraint con1 on con1.conrelid = cl.oid
where con1.contype = 'f'
) con
join pg_attribute att on
att.attrelid = con.confrelid and att.attnum = con.child
join pg_class cl on
cl.oid = con.confrelid
join pg_attribute att2 on
att2.attrelid = con.conrelid and att2.attnum = con.parent
WHERE con.child_table = @tableName AND con.child_schema = current_schema()";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\"");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (var sqlCommand = new NpgsqlCommand(commandRelations, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
public override void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope)
{
if (applyTable.Count <= 0)
{
return;
}
// Insert data in bulk table
BulkInsertTemporyTable(BulkInsertTemporyTableText(applyTable));
((OracleParameterCollection)cmd.Parameters)["sync_scope_id"].Value = scope.Id;
((OracleParameterCollection)cmd.Parameters)["sync_min_timestamp"].Value = scope.LastTimestamp;
bool alreadyOpened = this._connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
this._connection.Open();
}
if (this._transaction != null)
{
cmd.Transaction = this._transaction;
}
using (DbDataReader dataReader = cmd.ExecuteReader())
{
failedRows.Fill(dataReader);
}
}
catch (DbException ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
if (!alreadyOpened && this._connection.State != ConnectionState.Closed)
{
this._connection.Close();
}
}
}
public static DmTable ColumnsForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
string commandColumn = "select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.ObjectNameNormalized);
using (MySqlCommand sqlCommand = new MySqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable ColumnsForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName)
{
string commandColumn = "select * from information_schema.columns WHERE table_schema = current_schema() and table_name = @tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\"");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (var sqlCommand = new NpgsqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable PrimaryKeysForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandColumn = @"select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_key='PRI'";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "`", "`");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (MySqlCommand sqlCommand = new MySqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Get columns for table
/// </summary>
public static DmTable ColumnsForTable(OracleConnection connection, OracleTransaction transaction, string tableName)
{
var commandColumn = @"SELECT column_name AS name, column_id, data_type, data_length, data_precision, data_scale, decode(nullable, 'N', 0, 1) AS is_nullable
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = :tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (OracleCommand oracleCommand = new OracleCommand(commandColumn, connection, transaction))
{
oracleCommand.Parameters.Add("tableName", tableNameParser.ObjectName);
using (var reader = oracleCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable ColumnsForTable(SqlConnection connection, SqlTransaction transaction, string tableName)
{
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";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (SqlCommand sqlCommand = new SqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
internal static DmTable PrimaryKeysForTable(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName)
{
var commandColumn = @"SELECT c.column_name, c.ordinal_position, *
FROM information_schema.key_column_usage AS c
LEFT JOIN information_schema.table_constraints AS t
ON t.constraint_name = c.constraint_name
WHERE t.table_schema = current_schema() AND t.table_name = @tableName AND t.constraint_type = 'PRIMARY KEY';";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName, "\"", "\"");
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (var sqlCommand = new NpgsqlCommand(commandColumn, connection, transaction))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.ObjectName);
using (var reader = sqlCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
/// <summary>
/// Get columns for table
/// </summary>
internal static DmTable PrimaryKeysForTable(OracleConnection connection, OracleTransaction transaction, string tableName)
{
var commandColumn = @"SELECT ac.constraint_name as name, column_name as columnName, position as column_id
FROM all_cons_columns acc
INNER JOIN all_constraints ac
ON acc.constraint_name = ac.constraint_name AND acc.table_name = ac.table_name
WHERE CONSTRAINT_TYPE IN ('P') AND ac.table_name = :tableName";
ObjectNameParser tableNameParser = new ObjectNameParser(tableName);
DmTable dmTable = new DmTable(tableNameParser.UnquotedStringWithUnderScore);
using (OracleCommand oracleCommand = new OracleCommand(commandColumn, connection, transaction))
{
oracleCommand.Parameters.Add("tableName", tableNameParser.ObjectName);
using (var reader = oracleCommand.ExecuteReader())
{
dmTable.Fill(reader);
}
}
return(dmTable);
}
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);
}
}
/// <summary>
/// Executing a batch command
/// </summary>
/// <param name="cmd">the DbCommand already prepared</param>
/// <param name="applyTable">the table rows to apply</param>
/// <param name="failedRows">the failed rows dmTable to store failed rows</param>
/// <param name="scope">the current scope</param>
public override void ExecuteBatchCommand(DbCommand cmd, DmTable applyTable, DmTable failedRows, ScopeInfo scope)
{
if (applyTable.Rows.Count <= 0)
{
return;
}
List <SqlDataRecord> records = new List <SqlDataRecord>(applyTable.Rows.Count);
SqlMetaData[] metadatas = new SqlMetaData[applyTable.Columns.Count];
for (int i = 0; i < applyTable.Columns.Count; i++)
{
var column = applyTable.Columns[i];
SqlMetaData metadata = GetSqlMetadaFromType(column);
metadatas[i] = metadata;
}
foreach (var dmRow in applyTable.Rows)
{
SqlDataRecord record = new SqlDataRecord(metadatas);
for (int i = 0; i < dmRow.ItemArray.Length; i++)
{
var c = dmRow.Table.Columns[i];
dynamic defaultValue = c.DefaultValue;
dynamic rowValue = dmRow[i];
if (c.AllowDBNull && rowValue == null)
{
rowValue = DBNull.Value;
}
else if (rowValue == null)
{
rowValue = defaultValue;
}
record.SetValue(i, rowValue);
}
records.Add(record);
}
((SqlParameterCollection)cmd.Parameters)["@changeTable"].TypeName = string.Empty;
((SqlParameterCollection)cmd.Parameters)["@changeTable"].Value = records;
((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = scope.Id;
((SqlParameterCollection)cmd.Parameters)["@sync_min_timestamp"].Value = scope.LastTimestamp;
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
cmd.Transaction = this.transaction;
}
using (DbDataReader dataReader = cmd.ExecuteReader())
{
failedRows.Fill(dataReader);
}
}
catch (DbException ex)
{
//DbException dbException = dbException1;
//Error = CheckZombieTransaction(tvpCommandNameForApplyType, Adapter.TableName, dbException);
//this.AddFailedRowsAfterRIFailure(applyTable, failedRows);
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
/// <summary>
/// Executing a batch command
/// </summary>
/// <param name="cmd">the DbCommand already prepared</param>
/// <param name="applyTable">the table rows to apply</param>
/// <param name="failedRows">the failed rows dmTable to store failed rows</param>
/// <param name="scope">the current scope</param>
public override void ExecuteBatchCommand(DbCommand cmd, DmView applyTable, DmTable failedRows, ScopeInfo scope)
{
if (applyTable.Count <= 0)
{
return;
}
var lstMutableColumns = applyTable.Table.Columns.Where(c => !c.IsReadOnly).ToList();
List <SqlDataRecord> records = new List <SqlDataRecord>(applyTable.Count);
SqlMetaData[] metadatas = new SqlMetaData[lstMutableColumns.Count];
for (int i = 0; i < lstMutableColumns.Count; i++)
{
var column = lstMutableColumns[i];
SqlMetaData metadata = GetSqlMetadaFromType(column);
metadatas[i] = metadata;
}
try
{
foreach (var dmRow in applyTable)
{
SqlDataRecord record = new SqlDataRecord(metadatas);
int sqlMetadataIndex = 0;
bool isDeleted = false;
// Cancel the delete state to be able to get the row, more simplier
if (dmRow.RowState == DmRowState.Deleted)
{
isDeleted = true;
dmRow.RejectChanges();
}
for (int i = 0; i < dmRow.ItemArray.Length; i++)
{
// check if it's readonly
if (applyTable.Table.Columns[i].IsReadOnly)
{
continue;
}
// Get the default value
// Since we have the readonly values in ItemArray, get the value from original column
dynamic defaultValue = applyTable.Table.Columns[i].DefaultValue;
dynamic rowValue = dmRow[i];
var columnType = applyTable.Table.Columns[i].DataType;
// metadatas don't have readonly values, so get from sqlMetadataIndex
var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType;
if (rowValue != null)
{
switch (sqlMetadataType)
{
case SqlDbType.BigInt:
if (columnType != typeof(long))
{
if (Int64.TryParse(rowValue.ToString(), out Int64 v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Int64");
}
}
break;
case SqlDbType.Bit:
if (columnType != typeof(bool))
{
if (Boolean.TryParse(rowValue.ToString(), out Boolean v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Boolean");
}
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
if (columnType != typeof(DateTime))
{
if (DateTime.TryParse(rowValue.ToString(), out DateTime v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to DateTime");
}
}
break;
case SqlDbType.DateTimeOffset:
if (columnType != typeof(DateTimeOffset))
{
if (DateTimeOffset.TryParse(rowValue.ToString(), out DateTimeOffset dt))
{
rowValue = dt;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to DateTimeOffset");
}
}
break;
case SqlDbType.Decimal:
if (columnType != typeof(Decimal))
{
if (Decimal.TryParse(rowValue.ToString(), out decimal v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Decimal");
}
}
break;
case SqlDbType.Float:
if (columnType != typeof(Double))
{
if (Double.TryParse(rowValue.ToString(), out Double v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Double");
}
}
break;
case SqlDbType.Real:
if (columnType != typeof(float))
{
if (float.TryParse(rowValue.ToString(), out float v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Double");
}
}
break;
case SqlDbType.Image:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
if (columnType != typeof(Byte[]))
{
rowValue = BitConverter.GetBytes(rowValue);
}
break;
case SqlDbType.Variant:
break;
case SqlDbType.Int:
if (columnType != typeof(Int32))
{
if (Int32.TryParse(rowValue.ToString(), out int v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Int32");
}
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (columnType != typeof(Decimal))
{
if (Decimal.TryParse(rowValue.ToString(), out Decimal v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Decimal");
}
}
break;
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Char:
if (columnType != typeof(string))
{
rowValue = rowValue.ToString();
}
break;
case SqlDbType.SmallInt:
if (columnType != typeof(Int16))
{
if (Int16.TryParse(rowValue.ToString(), out Int16 v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Int16");
}
}
break;
case SqlDbType.Time:
if (columnType != typeof(TimeSpan))
{
if (TimeSpan.TryParse(rowValue.ToString(), out TimeSpan v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to TimeSpan");
}
}
break;
case SqlDbType.Timestamp:
break;
case SqlDbType.TinyInt:
if (columnType != typeof(Byte))
{
if (Byte.TryParse(rowValue.ToString(), out byte v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Byte");
}
}
break;
case SqlDbType.Udt:
throw new ArgumentException($"Can't use UDT as SQL Type");
case SqlDbType.UniqueIdentifier:
if (columnType != typeof(Guid))
{
if (Guid.TryParse(rowValue.ToString(), out Guid v))
{
rowValue = v;
}
else
{
throw new InvalidCastException($"Can't convert value {rowValue} to Guid");
}
}
break;
}
}
if (applyTable.Table.Columns[i].AllowDBNull && rowValue == null)
{
rowValue = DBNull.Value;
}
else if (rowValue == null)
{
rowValue = defaultValue;
}
record.SetValue(sqlMetadataIndex, rowValue);
sqlMetadataIndex++;
}
records.Add(record);
// Apply the delete
// is it mandatory ?
if (isDeleted)
{
dmRow.Delete();
}
}
}
catch (Exception ex)
{
throw new InvalidOperationException($"Can't create a SqlRecord based on the rows we have: {ex.Message}");
}
((SqlParameterCollection)cmd.Parameters)["@changeTable"].TypeName = string.Empty;
((SqlParameterCollection)cmd.Parameters)["@changeTable"].Value = records;
((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = scope.Id;
((SqlParameterCollection)cmd.Parameters)["@sync_min_timestamp"].Value = scope.Timestamp;
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
cmd.Transaction = this.transaction;
}
using (DbDataReader dataReader = cmd.ExecuteReader())
{
failedRows.Fill(dataReader);
}
}
catch (DbException ex)
{
Debug.WriteLine(ex.Message);
//DbException dbException = dbException1;
//Error = CheckZombieTransaction(tvpCommandNameForApplyType, Adapter.TableName, dbException);
//this.AddFailedRowsAfterRIFailure(applyTable, failedRows);
throw;
}
finally
{
records.Clear();
records = null;
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
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);
}
}
