internal static List <BlockSubitemClrUserDefineDto> RetriveBlockSubitemFullPathNameAndControlType(SqlConnection conn, string qeuryTabBlockSubitem) { List <BlockSubitemClrUserDefineDto> aDictSubitemID_Name = new List <BlockSubitemClrUserDefineDto>(); SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); System.Data.DataTable resultTabel = new DataTable(); adapter.Fill(resultTabel); foreach (DataRow aRow in resultTabel.Rows) { int subItemID = (int)aRow["SubItemID"]; string aStringSubItemId = subItemID.ToString(); string subItemName = DataTableUtility.FilterSQLDBInvalidChar(aRow["SubItemName"].ToString()) + "_" + aStringSubItemId; //dbo.pdmBlockSubItem.EntityID dbo.pdmEntity.EntityCode, dbo.pdmEntity.SysTableName int?entityID = aRow["EntityID"] as int?; if (entityID.HasValue) { string entityCode = DataTableUtility.FilterSQLDBInvalidChar(aRow["EntityCode"].ToString()); string systemTable = aRow["SysTableName"].ToString(); PdmEntityBlClrDto aEntityBlClrDto = PdmCacheManager.DictPdmEntityBlEntity[entityID.Value]; if (aEntityBlClrDto.EntityType == (int)EmEntityType.UserDefineTable) { subItemName += "_FK_" + PLMConstantString.PLM_DW_UserDefineTablePrefix + entityCode + "_" + entityID; } else { subItemName += "_FK_" + systemTable; } } // need to int controlType = int.Parse(aRow["ControlType"].ToString()); BlockSubitemClrUserDefineDto aDto = new BlockSubitemClrUserDefineDto(); aDto.SubItemID = subItemID; aDto.SubItemName = subItemName; aDto.EntityID = entityID; aDto.ControlType = controlType; //!!!!!!!!!!!!!!!!!!!!!!! aDictSubitemID_Name.Add(aDto); } return(aDictSubitemID_Name); }
//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 List <GridColumnClrUserDefineDto> GetGridColumnIDAndName(int gridID) { List <GridColumnClrUserDefineDto> aGridColumns = new List <GridColumnClrUserDefineDto>(); using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); // call udf function RetriveProductTabCopyRootTabID string qeuryGridColumn = string.Format(@"SELECT PdmGridMetaColumn.GridColumnID, PdmGridMetaColumn.ColumnName, PdmGridMetaColumn.ColumnTypeID , pdmEntity.EntityCode, pdmEntity.EntityID, pdmEntity.SysTableName FROM PdmGridMetaColumn LEFT OUTER JOIN pdmEntity ON PdmGridMetaColumn.EntityID = pdmEntity.EntityID WHERE GridID={0} ", gridID); SqlCommand cmd = new SqlCommand(qeuryGridColumn, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); System.Data.DataTable resultTabel = new DataTable(); adapter.Fill(resultTabel); foreach (DataRow aRow in resultTabel.Rows) { int aColumnID = (int)aRow["GridColumnID"]; string colName = DataTableUtility.FilterSQLDBInvalidChar(aRow["ColumnName"].ToString() + "_" + aColumnID); int? entityID = aRow["EntityID"] as int?; string entityCode = DataTableUtility.FilterSQLDBInvalidChar(aRow["EntityCode"].ToString()); string systemTable = aRow["SysTableName"].ToString(); if (entityID.HasValue) { PdmEntityBlClrDto aEntityBlClrDto = PdmCacheManager.DictPdmEntityBlEntity[entityID.Value]; if (aEntityBlClrDto.EntityType == (int)EmEntityType.UserDefineTable) { colName += "_FK_" + PLMConstantString.PLM_DW_UserDefineTablePrefix + entityCode + "_" + entityID; } else { colName += "_FK_" + systemTable; } } int controlType = int.Parse(aRow["ColumnTypeID"].ToString()); GridColumnClrUserDefineDto aDto = new GridColumnClrUserDefineDto(); aDto.GridColumnID = aColumnID; aDto.ColumnName = colName; aDto.EntityID = entityID; aDto.ColumnTypeId = controlType; aGridColumns.Add(aDto); } } return(aGridColumns); }
private static void TestDatatableScheme(SqlConnection conn) { string aQuery = "select TabID,TabName,FolderID from pdmtab "; DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, aQuery); Dictionary <string, string> dictDataTableAndDatabaseTableMappingColumn = new Dictionary <string, string>(); dictDataTableAndDatabaseTableMappingColumn.Add("TabID", "TabID"); dictDataTableAndDatabaseTableMappingColumn.Add("TabName", "TabName"); dictDataTableAndDatabaseTableMappingColumn.Add("FolderID", "FolderID"); aQuery = "select * from pdmgrid "; DataTableUtility.CreateDataBaseTableFromQuery(aQuery, conn, "testpdmgrid"); }
internal static string GetDWMainTabTableName(int rootabID) { string aRootTabName = string.Empty; using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); string qeuryTabBlockSubitem = " SELECT TabName FROM pdmtab WHERE tabid = " + rootabID; SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn); aRootTabName = cmd.ExecuteScalar().ToString(); } // remove Space, ( ) aRootTabName = DataTableUtility.FilterSQLDBInvalidChar(aRootTabName); return(string.Format("{0}" + aRootTabName + "_" + rootabID + "", PLMConstantString.PLM_DW_TabPreifxTableName)); }
internal static string GetDWGridIntoTalbeName(int gridID) { string aRootGridName = string.Empty; using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); string qeuryTabBlockSubitem = " SELECT GridName FROM pdmGrid WHERE GridID = " + gridID; SqlCommand cmd = new SqlCommand(qeuryTabBlockSubitem, conn); aRootGridName = cmd.ExecuteScalar().ToString(); } // remove Space, ( ) aRootGridName = DataTableUtility.FilterSQLDBInvalidChar(aRootGridName); //.Replace(' ', '_').Replace('(', '_').Replace(')', '_').Replace('-', '_'); return(string.Format(" {0}" + aRootGridName + "_" + gridID + " ", PLMConstantString.PLM_DW_GridPreifxTableName)); // return IntoDWTableName; }
public static DataTable Join(DataTable First, DataTable Second, string FJC, string SJC) { return(DataTableUtility.Join(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { Second.Columns[SJC] })); }
// it always take left ( keep first table all rows) public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC) { return(DataTableUtility.Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC })); }
//TODO public static void GenetateEntityDTOQuery(PdmClrEntitySimpleStructureDto aEntityDTO, SqlConnection conn) { List <PdmEntityColumnClrUserDefineDto> listColumnDto = aEntityDTO.Columns; if (listColumnDto.Count == 0) { aEntityDTO.SQLSelect = " invalid column (column no exist !)"; return; } StringBuilder aUserDefineTableQuery = new StringBuilder(); aUserDefineTableQuery.Append(" SELECT RowID as ValueID , SortOrder ,"); string userDefineColumnIdInClause = string.Empty; //WHERE UserDefineEntityColumnID in (4583,4584) foreach (var columnClrDto in listColumnDto) { // columnClrDto.ColumnName = DataTableUtility.FilterSQLDBInvalidChar(columnClrDto.ColumnName + "_" + columnClrDto.UserDefineEntityColumnID); columnClrDto.ColumnName = DataTableUtility.FilterSQLDBInvalidChar(columnClrDto.ColumnName); string aUserDefineEntityColumnID = columnClrDto.UserDefineEntityColumnID.ToString(); userDefineColumnIdInClause += aUserDefineEntityColumnID + ","; string aCaseQuery = string.Empty; if (columnClrDto.UicontrolType == (int)EmControlType.DDL || columnClrDto.UicontrolType == (int)EmControlType.Image) { aCaseQuery = string.Format(" {0} (dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToInt, aUserDefineEntityColumnID, columnClrDto.ColumnName); } else if (columnClrDto.UicontrolType == (int)EmControlType.Date) { aCaseQuery = string.Format(" {0} (dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToDateTime, aUserDefineEntityColumnID, columnClrDto.ColumnName); } else if ( columnClrDto.UicontrolType == (int)EmControlType.Numeric ) { aCaseQuery = string.Format(" {0} ( dbo.Concatenate ( case when UserDefineEntityColumnID={1} then ValueText end )) as '{2}' ,", PLMConstantString.ConvertValueTextToDecimal, aUserDefineEntityColumnID, columnClrDto.ColumnName); } else { aCaseQuery = string.Format(" dbo.Concatenate ( case when UserDefineEntityColumnID={0} then ValueText end ) as '{1}' ,", aUserDefineEntityColumnID, columnClrDto.ColumnName); } aUserDefineTableQuery.Append(aCaseQuery); } aUserDefineTableQuery.Remove(aUserDefineTableQuery.Length - 1, 1); if (userDefineColumnIdInClause != string.Empty) { userDefineColumnIdInClause = userDefineColumnIdInClause.Substring(0, userDefineColumnIdInClause.Length - 1); userDefineColumnIdInClause = " WHERE UserDefineEntityColumnID in ( " + userDefineColumnIdInClause + " ) " + " and EntityID=" + aEntityDTO.EntityId; aUserDefineTableQuery.Append(@" FROM " + PLMSEntityClrBL.PdmUserDefineEntityRowCellValueView + userDefineColumnIdInClause + " group by RowID, SortOrder "); aEntityDTO.SQLSelect = aUserDefineTableQuery.ToString(); } else { aEntityDTO.SQLSelect = "invalid column "; } // return aEntityDTO; }
public static void GetTabAndGridExternalColumnValue(int gridTabId, int currentGridBlockId, string referenceIds) { if (string.IsNullOrEmpty(referenceIds)) { return; } PdmBlockClrDto dmBlockClrDto = PdmCacheManager.DictBlockCache[currentGridBlockId]; if (dmBlockClrDto.BlockPdmGridDto == null) { return; } bool IsGetAliasname = false; DataTable tabFieldResultDataTable = GetTabDataTable(gridTabId, referenceIds, false, false); // Get Grading Size will call PrepareGetGridDataTable and set DataTable gridcolumnResultDataTable = PLMSGetGridValueHeler.LoadVariousGridColumnValue(gridTabId, currentGridBlockId, referenceIds, false, dmBlockClrDto, false, false, IsGetAliasname); List <string> gridExternalMappingNameList = GetExternalNappingName(gridTabId, currentGridBlockId); string firstReferencecolumn = gridcolumnResultDataTable.Columns[0].ColumnName; gridExternalMappingNameList.Add(firstReferencecolumn); List <string> allCoumns = new List <string>(); foreach (DataColumn column in gridcolumnResultDataTable.Columns) { allCoumns.Add(column.ColumnName); } var needTOremovecolumn = allCoumns.Except(gridExternalMappingNameList); foreach (string needToRemove in needTOremovecolumn) { gridcolumnResultDataTable.Columns.Remove(needToRemove); } DataTable mergeTable = null; if (gridcolumnResultDataTable.Rows.Count > 0) { mergeTable = DataTableUtility.Join(tabFieldResultDataTable, gridcolumnResultDataTable, tabFieldResultDataTable.Columns[0], gridcolumnResultDataTable.Columns[0]); } else { //mergeTable foreach (DataColumn gridColumn in gridcolumnResultDataTable.Columns) { if (!tabFieldResultDataTable.Columns.Contains(gridColumn.ColumnName)) { tabFieldResultDataTable.Columns.Add(gridColumn.ColumnName); } } mergeTable = tabFieldResultDataTable; } CLROutput.SendDataTable(mergeTable); }