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