//TODO list-------- public static void GenerateUserDefinTableScript() { List <int> userDefineEntityIds = null; using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); int userDefineEntityType = (int)EmEntityType.UserDefineTable; string qeuryUserDefineEntity = @" SELECT EntityID FROM pdmentity WHERE EntityType = " + userDefineEntityType; DataTable resultTabel = DataAcessHelper.GetDataTableQueryResult(conn, qeuryUserDefineEntity); userDefineEntityIds = resultTabel.GetDistinctOneColumnValueIds("EntityID").ToList(); } // List<PdmEntityClrUserDefineDto> listPdmEntityClrDto = null; List <PdmClrEntitySimpleStructureDto> listPdmEntityClrDto = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(userDefineEntityIds); using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); string aEntiValueScript = string.Empty; foreach (PdmClrEntitySimpleStructureDto aEntityDTO in listPdmEntityClrDto) { // int entityId = aEntityDTO.EntityId; // string entityCode = ; aEntityDTO.EntityCode = DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode); GenetateEntityDTOQuery(aEntityDTO, conn); string deleteOldEntityTable = @"delete [pdmDWTabGridScriptSetting] where EntityID=" + aEntityDTO.EntityId; // string sqlSelect = aEntityDTO.SQLSelect; string IntoUseDefineTablName = " insert into " + aEntityDTO.DWUserDefineTableName + aEntityDTO.SQLSelect; // save to dbms string deleteAndInsert = deleteOldEntityTable + System.Environment.NewLine + @" insert into " + PLMConstantString.PLM_DW_TabGridScripContainerTable + " ( EntityID,EntityName,InserIntoSQLScript,DWTableName,RootLevelSelectSQLScript) values (@EntityID,@EntityName,@queryResult,@DWTableName,@SQLRootLevelSelect)"; SqlCommand cmd = new SqlCommand(deleteAndInsert, conn); cmd.Parameters.Add(new SqlParameter("@EntityID", aEntityDTO.EntityId)); cmd.Parameters.Add(new SqlParameter("@EntityName", DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode))); cmd.Parameters.Add(new SqlParameter("@queryResult", IntoUseDefineTablName)); cmd.Parameters.Add(new SqlParameter("@DWTableName", aEntityDTO.DWUserDefineTableName)); cmd.Parameters.Add(new SqlParameter("@SQLRootLevelSelect", aEntityDTO.SQLSelect)); CLROutput.OutputDebug("aEntityDTO.entityCode" + aEntityDTO.EntityCode); CLROutput.OutputDebug("aEntityDTO.SQLSelect" + aEntityDTO.SQLSelect); cmd.ExecuteNonQuery(); } // end of for eahc entity } }
public static void TestExchangeDBConnection() { //step1: Get all Ex-change Tables from ex-Chang Dababae List <string> exChangeDbTableList = null; Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto = new Dictionary <string, List <PdmEntityColumnClrDto> >(); exChangeDbTableList = GetExistingExDatabaseTableNameAndStructure(exChangeDbTableList, dictExchangeTableColumnDto); //Step2: insert new Ex-change table AddNewExchangeTableToPLMSUserDefineEntity(exChangeDbTableList, dictExchangeTableColumnDto); // step2-2 update strcutre // Get all Refresh Userdefinetable from PLMS string queryllEntityImport = @" select EntityID from pdmEntity where IsImport =1 and EntityID > 3000 "; List <PdmEntityClrDto> plmImportEntityDtoList = null; using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString)) { List <int> enityIds = DataAcessHelper.GetDataTableQueryResult(conn, queryllEntityImport).AsEnumerable().Select(o => (int)o["EntityID"]).ToList();; plmImportEntityDtoList = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(enityIds, conn); } //Step3 Transfer NewRecord FromERP To PLM TransferNewRecordFromERPToPLM(plmImportEntityDtoList); ////Step4; upadte modfied fileds UpdateModifyRecordFromERPToPLM(plmImportEntityDtoList); //ste5: Delete--toDO //Step 6: update FK relation Ship UpdateForeignKeyRelationFromERPToPLM(plmImportEntityDtoList); }