public void Convert_ToFloat(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <float>(input);
Assert.IsType <float>(o1);
Assert.Equal((float)12.177, o1);
}
public void Convert_ToInt16(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <short>(input);
Assert.IsType <short>(o1);
Assert.Equal((short)12, o1);
}
public void Convert_ToDouble(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <double>(input);
Assert.IsType <double>(o1);
Assert.Equal((double)12.177, o1);
}
public void Convert_ToInt64(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <Int64>(input);
Assert.IsType <Int64>(o1);
Assert.Equal((Int64)12, o1);
}
public void Convert_ToChar(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <char>(input);
Assert.IsType <char>(o1);
Assert.Equal('a', o1);
}
public void Convert_ToDecimal_Invariant(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <decimal>(input);
Assert.IsType <decimal>(o1);
Assert.Equal((decimal)12.177, o1);
}
public void Convert_ToUInt32(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <UInt32>(input);
Assert.IsType <UInt32>(o1);
Assert.Equal((UInt32)12, o1);
}
public void Convert_ToGuid(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <Guid>(input);
Assert.IsType <Guid>(o1);
Assert.Equal(new Guid("ddb67ac3-89df-430e-ad65-cbe691d237d8"), o1);
}
public void Convert_ToBoolean_False(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <bool>(input);
Assert.IsType <bool>(o1);
Assert.False(o1);
}
public void Convert_ToSByte(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <sbyte>(input);
Assert.IsType <sbyte>(o1);
Assert.Equal((sbyte)12, o1);
}
public void Convert_ToByteArray(object input)
{
var o1 = SyncTypeConverter.TryConvertTo <byte[]>(input);
var expected = BitConverter.GetBytes((dynamic)input);
Assert.Equal(expected, o1);
}
public void Convert_ToDateTime(object input)
{
var cultureInfo = CultureInfo.GetCultureInfo("fr-FR");
var o1 = SyncTypeConverter.TryConvertTo <DateTime>(input, cultureInfo);
Assert.IsType <DateTime>(o1);
Assert.Equal(new DateTime(2020, 02, 10), o1);
}
public void Convert_Byte_ArrayToGuid()
{
var bytearray = new Guid("ddb67ac3-89df-430e-ad65-cbe691d237d8").ToByteArray();
var o1 = SyncTypeConverter.TryConvertTo <Guid>(bytearray);
Assert.IsType <Guid>(o1);
Assert.Equal(new Guid("ddb67ac3-89df-430e-ad65-cbe691d237d8"), o1);
}
public void Convert_ToTimeSpan(object input)
{
TimeSpan ts = new TimeSpan(100000);
var o1 = SyncTypeConverter.TryConvertTo <TimeSpan>(input);
Assert.IsType <TimeSpan>(o1);
Assert.Equal(ts, o1);
}
public async Task <List <ScopeInfo> > GetAllClientScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction)
{
List <ScopeInfo> scopes = new List <ScopeInfo>();
var commandText =
$@"SELECT sync_scope_id
, sync_scope_name
, sync_scope_schema
, sync_scope_setup
, sync_scope_version
, scope_last_sync
, scope_last_server_sync_timestamp
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {scopeTableName.Quoted().ToString()}
WHERE sync_scope_name = @sync_scope_name";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var scopeInfo = new ScopeInfo();
scopeInfo.Name = reader["sync_scope_name"] as String;
scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]);
scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]);
scopeInfo.Version = reader["sync_scope_version"] as string;
scopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_server_sync_timestamp"] : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
scopes.Add(scopeInfo);
}
}
}
return(scopes);
}
}
public void Convert_ToDouble_WithNfi(object input)
{
var cultureInfo = CultureInfo.GetCultureInfo("fr-FR");
var o1 = SyncTypeConverter.TryConvertTo <double>(input, cultureInfo);
Assert.IsType <double>(o1);
Assert.Equal((double)12.177, o1);
var o2 = SyncTypeConverter.TryConvertTo <double>(input, cultureInfo);
Assert.IsType <double>(o2);
Assert.Equal((double)12.177, o2);
}
public void Convert_ToFloat_OtherCulture(object input)
{
var cultureInfo = CultureInfo.GetCultureInfo("en-US");
var o1 = SyncTypeConverter.TryConvertTo <float>(input, cultureInfo);
Assert.IsType <float>(o1);
Assert.Equal((float)12.177, o1);
SyncGlobalization.DataSourceNumberDecimalSeparator = ",";
var o2 = SyncTypeConverter.TryConvertTo <float>(input);
Assert.IsType <float>(o2);
Assert.Equal((float)12.177, o2);
SyncGlobalization.DataSourceNumberDecimalSeparator = CultureInfo.InvariantCulture.NumberFormat.NumberDecimalSeparator;
}
public void Convert_Base64String_ToByteArray()
{
string s = "I'm a drummer";
byte[] sByt = Encoding.UTF8.GetBytes(s);
string sBase64 = Convert.ToBase64String(sByt);
var o1 = SyncTypeConverter.TryConvertTo <byte[]>(sBase64);
Assert.IsType <byte[]>(o1);
Assert.Equal(sByt, o1);
var b = Encoding.UTF8.GetString(o1);
Assert.Equal(s, b);
}
/// <summary>
/// Set a parameter value
/// </summary>
public static void SetParameterValue(DbCommand command, string parameterName, object value)
{
var parameter = GetParameter(command, parameterName);
if (parameter == null)
{
return;
}
if (value == null || value == DBNull.Value)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = SyncTypeConverter.TryConvertFromDbType(value, parameter.DbType);
}
}
public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
List <ServerHistoryScopeInfo> scopes = new List <ServerHistoryScopeInfo>();
var commandText =
$@"SELECT sync_scope_id
, sync_scope_name
, scope_last_sync_timestamp
, scope_last_sync_duration
, scope_last_sync
FROM `{tableName}`
WHERE sync_scope_name = @sync_scope_name";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var serverScopeInfo = new ServerHistoryScopeInfo();
serverScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverScopeInfo.Name = reader["sync_scope_name"] as string;
serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
scopes.Add(serverScopeInfo);
}
}
}
return(scopes);
}
}
/// <summary>
/// Set a parameter value
/// </summary>
public static void SetParameterValue(DbCommand command, string parameterName, object value)
{
var parameter = GetParameter(command, parameterName);
if (parameter == null)
{
return;
}
if (value != null && value != DBNull.Value)
{
var columnType = parameter.DbType;
var valueType = value.GetType();
try
{
if (columnType == DbType.Guid && valueType != typeof(Guid) && (value as string) != null)
{
value = new Guid(value.ToString());
}
else if (columnType == DbType.Guid && valueType != typeof(Guid) && value.GetType() == typeof(byte[]))
{
value = new Guid((byte[])value);
}
else if (columnType == DbType.Int32 && valueType != typeof(int))
{
value = Convert.ToInt32(value);
}
else if (columnType == DbType.UInt32 && valueType != typeof(uint))
{
value = Convert.ToUInt32(value);
}
else if (columnType == DbType.UInt16 && valueType != typeof(short))
{
value = Convert.ToInt16(value);
}
else if (columnType == DbType.UInt16 && valueType != typeof(ushort))
{
value = Convert.ToUInt16(value);
}
else if (columnType == DbType.Int64 && valueType != typeof(long))
{
value = Convert.ToInt64(value);
}
else if (columnType == DbType.UInt64 && valueType != typeof(ulong))
{
value = Convert.ToUInt64(value);
}
else if (columnType == DbType.Byte && valueType != typeof(byte))
{
value = Convert.ToByte(value);
}
else if (columnType == DbType.Currency && valueType != typeof(Decimal))
{
value = Convert.ToDecimal(value);
}
else if (columnType == DbType.DateTime && valueType != typeof(DateTime))
{
value = Convert.ToDateTime(value);
}
else if (columnType == DbType.DateTime2 && valueType != typeof(DateTime))
{
value = Convert.ToDateTime(value);
}
else if (columnType == DbType.DateTimeOffset && valueType != typeof(DateTimeOffset))
{
value = SyncTypeConverter.TryConvertTo <DateTimeOffset>(value);
}
else if (columnType == DbType.Decimal && valueType != typeof(decimal))
{
value = Convert.ToDecimal(value);
}
else if (columnType == DbType.Double && valueType != typeof(double))
{
value = Convert.ToDouble(value);
}
else if (columnType == DbType.SByte && valueType != typeof(sbyte))
{
value = Convert.ToSByte(value);
}
else if (columnType == DbType.VarNumeric && valueType != typeof(float))
{
value = Convert.ToSingle(value);
}
else if (columnType == DbType.String && valueType != typeof(string))
{
value = Convert.ToString(value);
}
else if (columnType == DbType.StringFixedLength && valueType != typeof(string))
{
value = Convert.ToString(value);
}
else if (columnType == DbType.AnsiString && valueType != typeof(string))
{
value = Convert.ToString(value);
}
else if (columnType == DbType.AnsiStringFixedLength && valueType != typeof(string))
{
value = Convert.ToString(value);
}
else if (columnType == DbType.Boolean && valueType != typeof(bool))
{
value = Convert.ToBoolean(value);
}
}
catch
{
// if execption, just try to set the value, directly
}
}
parameter.Value = value;
}
public async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
bool exist;
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
try
{
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText = $@"Select count(*) from `{tableName}` where sync_scope_id = @sync_scope_id";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update `{tableName}` set sync_scope_name=@sync_scope_name, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync=@scope_last_sync, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into `{tableName}` (sync_scope_id, sync_scope_name, scope_last_sync_timestamp, scope_last_sync, scope_last_sync_duration) values (@sync_scope_id, @sync_scope_name, @scope_last_sync_timestamp, @scope_last_sync, @scope_last_sync_duration)";
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = stmtText;
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as string;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(serverHistoryScopeInfo);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during InsertOrUpdateServerHistoryScopeInfoAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public async Task <ScopeInfo> InsertOrUpdateClientScopeInfoAsync(ScopeInfo scopeInfo)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
bool exist;
try
{
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText = $@"Select count(*) from {scopeTableName.Quoted().ToString()} where sync_scope_id = @sync_scope_id";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update {scopeTableName.Quoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into {scopeTableName.Quoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, sync_scope_id, scope_last_server_sync_timestamp, scope_last_sync_timestamp, scope_last_sync_duration) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @sync_scope_id, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @scope_last_sync_duration)";
using (var command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = stmtText;
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_schema";
p.Value = scopeInfo.Schema == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Schema);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_setup";
p.Value = scopeInfo.Setup == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Setup);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_version";
p.Value = scopeInfo.Version;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = scopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_server_sync_timestamp";
p.Value = scopeInfo.LastServerSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = scopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
scopeInfo.Name = reader["sync_scope_name"] as string;
scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]);
scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]);
scopeInfo.Version = reader["sync_scope_version"] as string;
scopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_server_sync_timestamp"] : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(scopeInfo);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during InsertOrUpdateClientScopeInfoAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public async Task <List <ServerHistoryScopeInfo> > GetAllServerHistoryScopesAsync(string scopeName)
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
List <ServerHistoryScopeInfo> scopes = new List <ServerHistoryScopeInfo>();
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"SELECT sync_scope_id
, sync_scope_name
, scope_last_sync_timestamp
, scope_last_sync_duration
, scope_last_sync
FROM `{tableName}`
WHERE sync_scope_name = @sync_scope_name";
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var serverScopeInfo = new ServerHistoryScopeInfo();
serverScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverScopeInfo.Name = reader["sync_scope_name"] as string;
serverScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
serverScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0;
serverScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0;
scopes.Add(serverScopeInfo);
}
}
}
return(scopes);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during GetAllServerHistoryScopes : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
/// <summary>
/// Executing a batch command
/// </summary>
public override async Task ExecuteBatchCommandAsync(DbCommand cmd, Guid senderScopeId, IEnumerable <SyncRow> applyRows, SyncTable schemaChangesTable,
SyncTable failedRows, long?lastTimestamp, DbConnection connection, DbTransaction transaction = null)
{
var applyRowsCount = applyRows.Count();
if (applyRowsCount <= 0)
{
return;
}
var dataRowState = DataRowState.Unchanged;
var records = new List <SqlDataRecord>(applyRowsCount);
SqlMetaData[] metadatas = new SqlMetaData[schemaChangesTable.Columns.Count];
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
metadatas[i] = GetSqlMetadaFromType(schemaChangesTable.Columns[i]);
}
try
{
foreach (var row in applyRows)
{
dataRowState = row.RowState;
var record = new SqlDataRecord(metadatas);
int sqlMetadataIndex = 0;
for (int i = 0; i < schemaChangesTable.Columns.Count; i++)
{
var schemaColumn = schemaChangesTable.Columns[i];
// Get the default value
//var columnType = schemaColumn.GetDataType();
dynamic defaultValue = schemaColumn.GetDefaultValue();
dynamic rowValue = row[i];
// metadatas don't have readonly values, so get from sqlMetadataIndex
var sqlMetadataType = metadatas[sqlMetadataIndex].SqlDbType;
if (rowValue != null)
{
var columnType = rowValue.GetType();
switch (sqlMetadataType)
{
case SqlDbType.BigInt:
if (columnType != typeof(long))
{
rowValue = SyncTypeConverter.TryConvertTo <long>(rowValue);
}
break;
case SqlDbType.Bit:
if (columnType != typeof(bool))
{
rowValue = SyncTypeConverter.TryConvertTo <bool>(rowValue);
}
break;
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.SmallDateTime:
if (columnType != typeof(DateTime))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTime>(rowValue);
}
break;
case SqlDbType.DateTimeOffset:
if (columnType != typeof(DateTimeOffset))
{
rowValue = SyncTypeConverter.TryConvertTo <DateTimeOffset>(rowValue);
}
break;
case SqlDbType.Decimal:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
break;
case SqlDbType.Float:
if (columnType != typeof(double))
{
rowValue = SyncTypeConverter.TryConvertTo <double>(rowValue);
}
break;
case SqlDbType.Real:
if (columnType != typeof(float))
{
rowValue = SyncTypeConverter.TryConvertTo <float>(rowValue);
}
break;
case SqlDbType.Image:
case SqlDbType.Binary:
case SqlDbType.VarBinary:
if (columnType != typeof(byte[]))
{
rowValue = SyncTypeConverter.TryConvertTo <byte[]>(rowValue);
}
break;
case SqlDbType.Variant:
break;
case SqlDbType.Int:
if (columnType != typeof(int))
{
rowValue = SyncTypeConverter.TryConvertTo <int>(rowValue);
}
break;
case SqlDbType.Money:
case SqlDbType.SmallMoney:
if (columnType != typeof(decimal))
{
rowValue = SyncTypeConverter.TryConvertTo <decimal>(rowValue);
}
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 = SyncTypeConverter.TryConvertTo <string>(rowValue);
}
break;
case SqlDbType.SmallInt:
if (columnType != typeof(short))
{
rowValue = SyncTypeConverter.TryConvertTo <short>(rowValue);
}
break;
case SqlDbType.Time:
if (columnType != typeof(TimeSpan))
{
rowValue = SyncTypeConverter.TryConvertTo <TimeSpan>(rowValue);
}
break;
case SqlDbType.Timestamp:
break;
case SqlDbType.TinyInt:
if (columnType != typeof(byte))
{
rowValue = SyncTypeConverter.TryConvertTo <byte>(rowValue);
}
break;
case SqlDbType.Udt:
throw new ArgumentException($"Can't use UDT as SQL Type");
case SqlDbType.UniqueIdentifier:
if (columnType != typeof(Guid))
{
rowValue = SyncTypeConverter.TryConvertTo <Guid>(rowValue);
}
break;
}
}
if (rowValue == null)
{
rowValue = DBNull.Value;
}
record.SetValue(sqlMetadataIndex, rowValue);
sqlMetadataIndex++;
}
records.Add(record);
}
}
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_min_timestamp"].Value = lastTimestamp.HasValue ? (object)lastTimestamp.Value : DBNull.Value;
((SqlParameterCollection)cmd.Parameters)["@sync_scope_id"].Value = senderScopeId;
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
cmd.Transaction = transaction;
using var dataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
while (dataReader.Read())
{
//var itemArray = new object[dataReader.FieldCount];
var itemArray = new object[failedRows.Columns.Count];
for (var i = 0; i < dataReader.FieldCount; i++)
{
var columnValueObject = dataReader.GetValue(i);
var columnName = dataReader.GetName(i);
var columnValue = columnValueObject == DBNull.Value ? null : columnValueObject;
var failedColumn = failedRows.Columns[columnName];
var failedIndexColumn = failedRows.Columns.IndexOf(failedColumn);
itemArray[failedIndexColumn] = columnValue;
}
// don't care about row state
// Since it will be requested by next request from GetConflict()
failedRows.Rows.Add(itemArray, dataRowState);
}
dataReader.Close();
}
catch (DbException ex)
{
Debug.WriteLine(ex.Message);
throw;
}
finally
{
records.Clear();
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public async Task <ServerHistoryScopeInfo> InsertOrUpdateServerHistoryScopeInfoAsync(ServerHistoryScopeInfo serverHistoryScopeInfo, DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
var commandText = $@"Select count(*) from `{tableName}` where sync_scope_id = @sync_scope_id";
bool exist;
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update `{tableName}` set sync_scope_name=@sync_scope_name, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync=@scope_last_sync, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into `{tableName}` (sync_scope_id, sync_scope_name, scope_last_sync_timestamp, scope_last_sync, scope_last_sync_duration) values (@sync_scope_id, @sync_scope_name, @scope_last_sync_timestamp, @scope_last_sync, @scope_last_sync_duration)";
using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = serverHistoryScopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = serverHistoryScopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = serverHistoryScopeInfo.LastSync.HasValue ? (object)serverHistoryScopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = serverHistoryScopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = serverHistoryScopeInfo.Id;
p.DbType = DbType.Guid;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
serverHistoryScopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
serverHistoryScopeInfo.Name = reader["sync_scope_name"] as string;
serverHistoryScopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
serverHistoryScopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
serverHistoryScopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(serverHistoryScopeInfo);
}
}
public async Task <ScopeInfo> InsertOrUpdateClientScopeInfoAsync(ScopeInfo scopeInfo, DbConnection connection, DbTransaction transaction)
{
bool exist;
var commandText = $@"Select count(*) from {scopeTableName.Quoted().ToString()} where sync_scope_id = @sync_scope_id";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update {scopeTableName.Quoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into {scopeTableName.Quoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, sync_scope_id, scope_last_server_sync_timestamp, scope_last_sync_timestamp, scope_last_sync_duration) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @sync_scope_id, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @scope_last_sync_duration)";
using (var command = new MySqlCommand(stmtText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_schema";
p.Value = scopeInfo.Schema == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Schema);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_setup";
p.Value = scopeInfo.Setup == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Setup);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_version";
p.Value = scopeInfo.Version;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = scopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_server_sync_timestamp";
p.Value = scopeInfo.LastServerSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = scopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
scopeInfo.Name = reader["sync_scope_name"] as string;
scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]);
scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]);
scopeInfo.Version = reader["sync_scope_version"] as string;
scopeInfo.Id = SyncTypeConverter.TryConvertTo <Guid>(reader["sync_scope_id"]);
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? (long)reader["scope_last_sync_duration"] : 0L;
scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_server_sync_timestamp"] : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? (long)reader["scope_last_sync_timestamp"] : 0L;
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value ? (DateTime?)reader["scope_last_sync"] : null;
}
}
}
return(scopeInfo);
}
}
