public async Task <bool> ExecuteQuery(BulkCopyViewModel viewModel)
{
bool returnValue = false;
try
{
using (SqlConnection destConnection = new SqlConnection(viewModel.DestinationConnectionString))
using (SqlConnection sourceConnection = new SqlConnection(viewModel.SourceConnectionString))
{
if (destConnection.State == System.Data.ConnectionState.Closed)
{
destConnection.Open();
}
if (sourceConnection.State == System.Data.ConnectionState.Closed)
{
sourceConnection.Open();
}
#region For Each Table
foreach (var item in viewModel.ChangedItems)
{
var tableName = item.Name;
if (string.IsNullOrEmpty(item.Target) == false)
{
tableName = item.Target;
}
#region Check Exist
try
{
if (ConstantHelper.TableActions.TruncateAdd == item.Action)
{
// checking whether the table selected from the dataset exists in the database or not
var checkTableIfExistsCommand = new SqlCommand("IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '" + tableName + "' and xtype = 'U') SELECT 1 ELSE SELECT 0", destConnection);
var exists = checkTableIfExistsCommand.ExecuteScalar().ToString().Equals("1");
// if does not exist
if (!exists)
{
var createTableBuilder = new StringBuilder("CREATE TABLE [" + tableName + "]");
createTableBuilder.AppendLine("(");
SqlDataAdapter sqlDataAdapterClmn = new SqlDataAdapter($@"SELECT name, system_type_name, is_nullable FROM
sys.dm_exec_describe_first_result_set('select * from {item.Name}', NULL, 0)", sourceConnection);
DataTable clmDt = new DataTable("Columns");
sqlDataAdapterClmn.Fill(clmDt);
foreach (DataRow clmRow in clmDt.Rows)
{
var nullStr = "NULL";
if (clmRow[2].ToString() == "1")
{
nullStr = "NOT NULL";
}
createTableBuilder.AppendLine($" [{(string)clmRow[0]}] {(string)clmRow[1]} {nullStr},");
}
createTableBuilder.Remove(createTableBuilder.Length - 1, 1);
createTableBuilder.AppendLine(")");
var createTableCommand = new SqlCommand(createTableBuilder.ToString(), destConnection);
createTableCommand.ExecuteNonQuery();
}
else
{
var createTableCommand = new SqlCommand($"TRUNCATE TABLE {tableName}", destConnection);
createTableCommand.ExecuteNonQuery();
}
}
}
catch (Exception exTbl)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "Check Exist Error",
Message = exTbl.Message,
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "ExecuteQuery"),
new LogItemProperty("TableName", tableName),
new LogItemProperty("ViewModel", viewModel)
},
LogLevel = LogLevel.Error,
Exception = exTbl
});
}
#endregion
SqlDataReader reader = null;
try
{
#region Execute Command
if (sourceConnection.State == System.Data.ConnectionState.Closed)
{
sourceConnection.Open();
}
var whereClause = item.Where?.Trim();
if (string.IsNullOrEmpty(item.Where) == false)
{
whereClause = item.Where;
if (whereClause.ToLower().StartsWith("where") == false)
{
whereClause = "WHERE " + whereClause;
}
}
SqlCommand myCommand = new SqlCommand($"SELECT * FROM {item.Name} {whereClause}", sourceConnection);
SqlCommand myRowCommand = new SqlCommand($"SELECT count(*) FROM {item.Name} {whereClause}", sourceConnection);
var rowCount = myRowCommand.ExecuteScalar()?.ToString();
tableRowCounts[tableName] = rowCount;
reader = myCommand.ExecuteReader();
// if table exists, just copy the data to the destination table in the database
// copying the data from datatable to database table
using (var bulkCopy = new SqlBulkCopy(destConnection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = 50;
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bulkCopy.NotifyAfter = 50;
bulkCopy.WriteToServer(reader);
}
#endregion
}
catch (Exception exTbl)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "BbulkCopy Error",
Message = exTbl.Message,
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "ExecuteQuery"),
new LogItemProperty("TableName", tableName),
new LogItemProperty("ViewModel", viewModel)
},
LogLevel = LogLevel.Error,
Exception = exTbl
});
}
finally
{
if (reader != null)
{
try
{
reader.Close();
}
catch (Exception)
{
}
}
}
}
#endregion
try
{
destConnection.Close();
sourceConnection.Close();
}
catch (Exception)
{
}
}
}
catch (Exception ex)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = "ExecuteQuery Error",
Message = ex.Message,
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ActionName", "ExecuteQuery"),
new LogItemProperty("ViewModel", viewModel)
},
LogLevel = LogLevel.Error,
Exception = ex
});
}
return(returnValue);
}
public async Task Execute(IJobExecutionContext context)
{
var schedulerName = context.Scheduler.SchedulerName;
var scheduleName = context.Scheduler.SchedulerName;
var jobName = context.JobDetail.Key.Name;
var jobGroup = context.JobDetail.Key.Group;
var trgName = context.Trigger.Key.Name;
var trgGroup = context.Trigger.Key.Group;
var jobDataKeys = context.JobDetail.JobDataMap.GetKeys();
var customFormDataModel = new BulkCopyViewModel();
if (jobDataKeys.Contains(ConstantHelper.CustomData))
{
var customFormData = context.JobDetail.JobDataMap.GetString(ConstantHelper.CustomData);
customFormDataModel = JsonConvert.DeserializeObject <BulkCopyViewModel>(customFormData);
}
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = $"{this.GetType()} Started",
Message = $"{this.GetType()} Started",
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ScheduleName", scheduleName),
new LogItemProperty("JobName", jobName),
new LogItemProperty("JobGroup", jobGroup),
new LogItemProperty("TriggerName", trgName),
new LogItemProperty("TriggerGroup", trgGroup)
},
LogLevel = LogLevel.Info
});
if (customFormDataModel != null)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
try
{
new BulkCopyManager().ExecuteQuery(customFormDataModel);
}
catch (Exception ex)
{
LoggerService.GetLogger(ConstantHelper.JobLog).Log(new LogItem()
{
LoggerName = ConstantHelper.JobLog,
Title = $"{this.GetType()} Execution Error",
Message = ex.Message,
LogItemProperties = new List <LogItemProperty>()
{
new LogItemProperty("ServiceName", ConstantHelper.JobLog),
new LogItemProperty("ScheduleName", scheduleName),
new LogItemProperty("JobName", jobName),
new LogItemProperty("JobGroup", jobGroup),
new LogItemProperty("TriggerName", trgName),
new LogItemProperty("TriggerGroup", trgGroup),
new LogItemProperty("ActionName", "ExecuteQuery"),
new LogItemProperty("FormData", new { CustomFormDataModel = customFormDataModel }),
},
LogLevel = LogLevel.Error,
Exception = ex
});
}
stopwatch.Stop();
}
}