public void execute() { DI_DAO dao = null; DISchedule schedule = new DISchedule(); schedule.Log = string.Empty; try { dao = new DI_DAO(); dao.beginTransaction(); //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; } }
public List<DIDataSource> getDataSources() { DI_DAO dao = null; List<DIDataSource> list = null; try { dao = new DI_DAO(); dao.beginTransaction(); list = dao.getDataSource(); dao.commitTransaction(); } catch (Exception ex) { dao.rollbackTransaction(); throw ex; } return list; }