コード例 #1
0
        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;
            }
        }