//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);
}
