public static async Task <bool> RecordExistsAsync(SqlDatabaseConnection conn, ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(RecordExistsQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
// check if record exists
var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var count = (long)reader["c"];
await conn.CloseAsync();
return(count != 0);
}
public static async Task <ReplicationMetaData> GetPreviousReplicationMetaDataAsync(SqlDatabaseConnection conn,
string jobId,
ReplicationTable table)
{
try
{
ReplicationMetaData replicationMetaData = null;
// ensure replication metadata table
await EnsureTableAsync(conn, table);
// check if metadata exists
await conn.OpenAsync();
var query = string.Format(GetMetaDataQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(Constants.ReplicationMetaDataJobId),
jobId);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
// metadata exists
var request = JsonConvert.DeserializeObject <PrepareWriteRequest>(
reader[Constants.ReplicationMetaDataRequest].ToString());
var shapeName = reader[Constants.ReplicationMetaDataReplicatedShapeName]
.ToString();
var shapeId = reader[Constants.ReplicationMetaDataReplicatedShapeId]
.ToString();
var timestamp = DateTime.Parse(reader[Constants.ReplicationMetaDataTimestamp]
.ToString());
replicationMetaData = new ReplicationMetaData
{
Request = request,
ReplicatedShapeName = shapeName,
ReplicatedShapeId = shapeId,
Timestamp = timestamp
};
}
await conn.CloseAsync();
return(replicationMetaData);
}
catch (Exception e)
{
Logger.Error(e, e.Message);
throw;
}
}
public static async Task <Dictionary <string, object> > GetRecordAsync(SqlDatabaseConnection conn,
ReplicationTable table,
string primaryKeyValue)
{
await conn.OpenAsync();
var query = string.Format(GetRecordQuery,
Utility.Utility.GetSafeName(table.SchemaName),
Utility.Utility.GetSafeName(table.TableName),
Utility.Utility.GetSafeName(table.Columns.Find(c => c.PrimaryKey == true).ColumnName),
primaryKeyValue
);
var cmd = new SqlDatabaseCommand
{
Connection = conn,
CommandText = query
};
var reader = await cmd.ExecuteReaderAsync();
Dictionary <string, object> recordMap = null;
// check if record exists
if (reader.HasRows)
{
await reader.ReadAsync();
recordMap = new Dictionary <string, object>();
foreach (var column in table.Columns)
{
try
{
recordMap[column.ColumnName] = reader[column.ColumnName];
}
catch (Exception e)
{
Logger.Error(e, $"No column with column name: {column.ColumnName}");
Logger.Error(e, e.Message);
recordMap[column.ColumnName] = null;
}
}
}
await conn.CloseAsync();
return(recordMap);
}