public void addNewSource(DIDataSource datasource, List<DITableSource> tables)
 {
     DI_DAO dao = null;
     try
     {
         dao = new DI_DAO();
         dao.beginTransaction();
         dao.addDataSource(datasource);
         foreach (DITableSource table in tables)
         {
             dao.addTableSource(table);
         }
         dao.commitTransaction();
     }
     catch (Exception ex)
     {
         dao.rollbackTransaction();
         throw ex;
     }
 }
        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 void updateSource(DIDataSource datasouce)
 {
     DI_DAO dao = null;
     try
     {
         dao = new DI_DAO();
         dao.beginTransaction();
         dao.updateDataSouce(datasouce);
         dao.commitTransaction();
     }
     catch (Exception ex)
     {
         dao.rollbackTransaction();
         throw ex;
     }
 }
 public void removeDataSource(string DataID)
 {
     DI_DAO dao = null;
     try
     {
         dao = new DI_DAO();
         dao.beginTransaction();
         dao.removeDataSource(DataID);
         dao.removeTableSource(DataID);
         dao.commitTransaction();
     }
     catch (Exception ex)
     {
         dao.rollbackTransaction();
         throw ex;
     }
 }
 public List<DITableSourceType> getTableSourceTypes()
 {
     DI_DAO dao = null;
     List<DITableSourceType> list = null;
     try
     {
         dao = new DI_DAO();
         dao.beginTransaction();
         list = dao.getTableSourceType();
         dao.commitTransaction();
     }
     catch (Exception ex)
     {
         dao.rollbackTransaction();
         throw ex;
     }
     return list;
 }
 public List<Query> getQuery()
 {
     DI_DAO dao = null;
     List<Query> list = null;
     try
     {
         dao = new DI_DAO();
         dao.beginTransaction();
         //list = dao.getQueries();
         dao.commitTransaction();
     }
     catch (Exception ex)
     {
         dao.rollbackTransaction();
         throw ex;
     }
     return list;
 }
 public List<string> getListTables(string connectionString, out string message)
 {
     DI_DAO dao = null;
     List<string> tables = null;
     try
     {
         dao = new DI_DAO();
         dao.connectSource(connectionString);
         tables = dao.getTablesFromSource();
         message = string.Empty;
     }
     catch (Exception ex)
     {
         message = ex.Message;
     }
     return tables;
 }