private string BuildUpateTemplate(TableMapping tableMapping)
{
//Build update template
string sql = @" UPDATE " + tableMapping.Destination + " SET ";
string colSet = "";
int i = 1;
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
if (columnMapping.IsKey && columnMapping.IsImmutableKey)
{
continue;
}
if (!string.IsNullOrEmpty(colSet))
{
colSet += ",";
}
colSet += "[" + columnMapping.Destination + "]" + " = " + "@Field" + i.ToString();
i++;
}
return(sql + colSet + " WHERE " + tableMapping.GetKeyColumnNames().FirstOrDefault() + " = @Id");
}
public long Write(DataTable data, TableMapping tableMapping, ref StringBuilder msgBuilder, ref int insertRowCount, ref int updateRowCount)
{
if (data.Rows.Count == 0)
{
return(0);
}
msgBuilder.Append(string.Format("Table sync detail for : {0} \n", tableMapping.Source));
var destColumnMetaDataList = LoadTableMetaData(tableMapping.Destination);
ValidateTableMapping(tableMapping, destColumnMetaDataList);
var destColumnNameTypeMappings = destColumnMetaDataList.ToDictionary(
columnMetaData => columnMetaData.ColumnName,
columnMetaData => columnMetaData.ColumnType,
StringComparer.OrdinalIgnoreCase);
if (tableMapping.IsContainFile)
{
return(SaveVarBinaryData(data, tableMapping, destColumnNameTypeMappings));
}
if (tableMapping.IsContainFilePath)
{
return(SaveVarBinaryDataFromPath(data, tableMapping, destColumnNameTypeMappings, tableMapping.FilePath, tableMapping.PathColumn));
}
var keyColumnNames = tableMapping.GetKeyColumnNames();
var sourceDataValues = new Dictionary <KeySet, Dictionary <string, object> >();
foreach (DataRow dataRow in data.Rows)
{
var destColumnNameValueMappings = LoadDestColumnNameValueMappings(dataRow, tableMapping);
var keySet = new KeySet();
foreach (var keyColumnName in keyColumnNames)
{
keySet.Add(keyColumnName, destColumnNameTypeMappings[keyColumnName], destColumnNameValueMappings[keyColumnName]);
}
if (sourceDataValues.ContainsKey(keySet))
{
throw new Exception(string.Format("The source data (table: {0}) contains rows with duplicated keys", tableMapping.Source));
}
sourceDataValues.Add(keySet, destColumnNameValueMappings);
}
var immutableKeySets = GetImmutableKeySets(sourceDataValues, tableMapping, destColumnNameTypeMappings);
var destinationKeySets = GetDestinationKeySets(immutableKeySets, tableMapping, destColumnNameTypeMappings, keyColumnNames);
if (!tableMapping.IsKeepObsoleteDestinationData)
{
RemoveObsoleteDestinationData(sourceDataValues, destinationKeySets, tableMapping, destColumnNameTypeMappings);
}
return(SaveSourceData(sourceDataValues, destinationKeySets, tableMapping, destColumnNameTypeMappings, ref msgBuilder, ref insertRowCount, ref updateRowCount));
}
private long SaveVarBinaryData(DataTable data, TableMapping tableMapping, Dictionary <string, DbColumnType> destColumnNameTypeMappings)
{
var keyColumnNames = tableMapping.GetKeyColumnNames();
var keyColumnName = keyColumnNames.FirstOrDefault();
string keyValues = ""; //values for key in table;
//fetch dest table
foreach (DataRow dataRow in data.Rows)
{
if (!string.IsNullOrEmpty(keyValues))
{
keyValues += ",";
}
if (destColumnNameTypeMappings[keyColumnName] == DbColumnType.INT)
{
keyValues += dataRow[keyColumnName];
}
else
{
keyValues += "'" + dataRow[keyColumnName] + "'";
}
}
SqlCommand qry = new SqlCommand("SELECT * FROM "
+ tableMapping.Destination
+ " WHERE " + keyColumnName
+ " IN (" + keyValues + ")",
(SqlConnection)_conn,
(SqlTransaction)_trans);
SqlDataAdapter da = new SqlDataAdapter(qry);
qry.CommandTimeout = this._sqlCommandTimeout;
DataSet ds = new DataSet();
da.Fill(ds, "DestTable");
DataTable dt = ds.Tables["DestTable"];
//update desttable
List <string> updateKeys = new List <string>();
foreach (DataRow row in dt.Rows)
{
string filter = "";
string updateKey = "";
foreach (var key in keyColumnNames)
{
if (filter != "")
{
filter += " AND ";
}
if (destColumnNameTypeMappings[key] == DbColumnType.INT)
{
filter += key + " = " + row[key];
}
else
{
filter += key + " = '" + row[key].ToString() + "'";
}
}
DataRow dr = data.Select(filter).FirstOrDefault();
if (dr != null)
{
foreach (var key in keyColumnNames)
{
updateKey += dr[key].ToString();
}
updateKeys.Add(updateKey);
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
if (columnMapping.IsKey && columnMapping.IsImmutableKey)
{
continue;
}
row[columnMapping.Destination] = dr[columnMapping.Source];
}
}
}
//insert into desttable
foreach (DataRow row in data.Rows)
{
string insertKey = "";
foreach (var key in keyColumnNames)
{
insertKey += row[key].ToString();
}
if (!updateKeys.Contains(insertKey))
{
row.SetAdded();
dt.ImportRow(row);
}
}
//build insert and update template
SqlDataAdapter adapter = new SqlDataAdapter();
string updatesql = BuildUpateTemplate(tableMapping);
SqlCommand updateCmd = new SqlCommand(updatesql, (SqlConnection)_conn, (SqlTransaction)_trans);
int i = 1;
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
SqlDbType type = GetColumnType(destColumnNameTypeMappings[columnMapping.Destination]);
int size = GetColumnSize(destColumnNameTypeMappings[columnMapping.Destination]);
if (columnMapping.IsKey && columnMapping.IsImmutableKey)
{
var param = updateCmd.Parameters.Add("@Id", type, size, tableMapping.GetKeyColumnNames().FirstOrDefault());
param.SourceVersion = DataRowVersion.Original;
}
else
{
updateCmd.Parameters.Add("@Field" + i.ToString(), type, size, columnMapping.Source);
i++;
}
}
String insertSql = BuildInsertTemplate(tableMapping);
SqlCommand insertCommand = new SqlCommand(insertSql, (SqlConnection)_conn, (SqlTransaction)_trans);
i = 1;
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
SqlDbType type = GetColumnType(destColumnNameTypeMappings[columnMapping.Destination]);
int size = GetColumnSize(destColumnNameTypeMappings[columnMapping.Destination]);
insertCommand.Parameters.Add("@Field" + i.ToString(), type, size, columnMapping.Source);
i++;
}
updateCmd.CommandTimeout = this._sqlCommandTimeout;
insertCommand.CommandTimeout = this._sqlCommandTimeout;
adapter.UpdateCommand = updateCmd;
adapter.InsertCommand = insertCommand;
adapter.Update(ds, "DestTable");
return(data.Rows.Count);
}
/*Sync file from storage service of .28 to storage service of production*/
private string SyncFile(System.Data.DataTable sourceData, TableMapping tableMapping, ref int successFileNo, ref int failFileNo)
{
var imeClient = new Luna.DataSync.Core.IME_SS.StorageServiceClient();
var prodClient = new Luna.DataSync.Core.PROD_SS.StorageServiceClient();
var tableName = tableMapping.Source;
var msg = new StringBuilder();
msg.AppendFormat("File sync detail for table:{0} : \n<ol>", tableMapping.Destination);
foreach (System.Data.DataRow dtRow in sourceData.Rows)
{
string path = "";
string fileName = "";
string fileId = "";
var keyColumns = tableMapping.GetKeyColumnNames().ToList();
fileId = keyColumns.Count == 1 ? dtRow[keyColumns.FirstOrDefault()].ToString() : keyColumns.Aggregate((i, j) => dtRow[i] + "_" + dtRow[j]);
//ignore if there is no physical path
//when tablename = INSTITUTIONINFO, phisicalpath = |Logo
var realPath = tableMapping.Source == "INSTITUTIONINFO" ? "|Logo" : dtRow[tableMapping.PathColumn].ToString();
if (string.IsNullOrEmpty(realPath))
{
continue;
}
realPath = realPath.Replace(@"\", "/");
var ext = "";
if (tableName.Equals("FILEDETAIL") || tableName.Equals("INSTITUTIONINFO"))
{
ext = "." + dtRow[tableMapping.ExtColumn];
path = tableMapping.PathRoot + (realPath.IndexOf("|") != 0 ? String.Format("|{0}", realPath) : realPath);
}
else
{
ext = Path.GetExtension(realPath);
path = tableMapping.PathRoot + realPath.Substring(0, realPath.LastIndexOf("/")).Replace("/", "|");
}
fileName = fileId + ext;
fileEntity obj = null;
obj = imeClient.RetriveFileObj(path, fileName);
if (obj.fileData == null)
{
//var strException = string.Format("Can not retrive file(ID:{0},path{1},name:{2}) \n from {3} from source table: {4} \n ", fileId, path, fileName, imeClient.Endpoint.Address, tableMapping.Source);
//throw new Exception(strException);
msg.AppendFormat("<li>{0}:{1}{2} <span style=\"color:red;\">failed</span>.<br/>(Cannot retrive file from {3})</li>", fileId, path, fileName,
imeClient.Endpoint.Address);
failFileNo++;
continue;
}
bool success;
//try
//{
// Luna.DataSync.Core.PROD_SS.fileEntity newObj = new Luna.DataSync.Core.PROD_SS.fileEntity { fileData = obj.fileData, fileName = fileName, path = path };
// success = prodClient.AddFileObj(newObj);
//}
//catch (Exception ex)
//{
// throw new Exception(string.Format("Get exception when call storage service (write file)\n " +
// "fileId: {0} in destination table: {1} \n {2} \n", fileId, tableMapping.Source, ex));
//}
var newObj = new Luna.DataSync.Core.PROD_SS.fileEntity {
fileData = obj.fileData, fileName = fileName, path = path
};
success = prodClient.AddFileObj(newObj);
if (!success)
{
//throw new Exception(string.Format("Can not save file(ID:{0},path{1},name:{2}) \n to {3} in destination table: {4}", fileId, path, fileName, imeClient.Endpoint.Address, tableMapping.Source));
msg.AppendFormat("<li>{0}:{1}{2} <span style=\"color:red;\">failed</span>.<br/>(Cannot save file to {3})</li>", fileId, path, fileName,
prodClient.Endpoint.Address);
failFileNo++;
continue;
}
msg.AppendFormat("<li>{0}:{1}{2} sync <span style=\"color:green;\">succeed</span>.</li>", fileId, path, fileName);
successFileNo++;
//try
//{
// if (!success)
// {
// if (tableName.Equals("FILEDETAIL"))
// dtRow["ISVALID"] = 0;
// else
// dtRow["ISSYNCED"] = 0;
// syncInfo += string.Format("{0}|{1}, fail \n", path, fileName);
// }
// sourceData.AcceptChanges();
//}
//catch (Exception ex)
//{
// throw new Exception(string.Format("Get exception when update file sync status\n " +
// "fileId: {0} \n destination table: {1} \n {2} \n", fileId, tableMapping.Destination, ex));
//}
}
msg.Append("</ol>");
return(msg.ToString());
}