public void addSchedule(DISchedule schedule)
{
string strQuery = "INSERT INTO [DI].[DATA_INTERGRATION_SCHEDULE]([StartTime],[StopTime],[Log]) VALUES (GETDATE(),GETDATE(),@Log)";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@Log", schedule.Log);
executeNonQuery(strQuery, parameters);
}
public void execute()
{
DI_DAO dao = null;
DISchedule schedule = new DISchedule();
schedule.Log = string.Empty;
try
{
dao = new DI_DAO();
dao.beginTransaction();
//modify
dao.CLEAN_ALL_INPUT_DATA();
List <DIDataSource> lstDataSource = dao.getDataSource();
foreach (DIDataSource source in lstDataSource)
{
schedule.Log += "Update data from Database: " + source.DataID + "\n";
source.queries = dao.getQueries(source.DataID);
foreach (Query query in source.queries)
{
query.parameters = dao.getParamters(query.QueryID);
query.mappings = dao.getMappings(query.QueryID);
List <DITableSource> lstTableSources = dao.getTableSource(query.QueryID);
// build query to data source
foreach (DITableSource table in lstTableSources)
{
query.String = query.String.Replace(table.Type, table.TableID);
}
// Create connection to Data Source
dao.connectSource(source.ConnectionString);
// Create parameter for query
Dictionary <string, object> lstParams = new Dictionary <string, object>();
foreach (DIParameter param in query.parameters)
{
if (param.Type != "QUERY")
{
lstParams.Add(param.ParameterID, param.Value);
}
else
{
lstParams.Add(param.ParameterID, dao.getQueryParameter(param.Value).Rows[0][0]);
}
}
// query data from source to DataTable
DataTable result = dao.selectSourceData(query.String, lstParams);
//System.Console.WriteLine(query.String);
// Build Insert comand
int counter = 0;//number of added records
foreach (DataRow row in result.Rows)
{
string insertCommand = "INSERT INTO " + query.DescTable + "(";
string values = "VALUES(";
Dictionary <string, object> lstParamsforInsert = new Dictionary <string, object>();
foreach (DIMapping mapping in query.mappings)
{
insertCommand += mapping.DesColumnID + ",";
values += "@" + mapping.SrcColumnName + ",";
lstParamsforInsert.Add("@" + mapping.SrcColumnName, row[mapping.SrcColumnName]);
}
insertCommand = insertCommand.Substring(0, insertCommand.Length - 1);
values = values.Substring(0, values.Length - 1);
insertCommand += ")" + values + ")";
if (dao.insertData(insertCommand, lstParamsforInsert))
{
counter++;
}
}
schedule.Log += counter + " records have been loaded to Table " + query.DescTable + "\n";
}
schedule.Log += "===============================================\n";
}
dao.addSchedule(schedule);
dao.commitTransaction();
}
catch (Exception ex)
{
dao.rollbackTransaction();
throw ex;
}
}
