//select EntityID ,EntityCode ,EntityType,SysTableName from pdmEntity public static PdmEntityColumnClrUserDefineDto ConvertErpExchangeDatabaseTableColumnDataRowToPdmEntityColumnDto(DataRow row) { PdmEntityColumnClrUserDefineDto aPdmEntityDto = new PdmEntityColumnClrUserDefineDto(); // aPdmEntityDto.UserDefineEntityColumnID = (int)row["UserDefineEntityColumnID"]; // aPdmEntityDto.EntityId = (int)row["EntityID"]; aPdmEntityDto.ColumnName = row["ColumnName"] as string; aPdmEntityDto.DataBaseDataType = row["DataType"] as string; aPdmEntityDto.IsPrimaryKey = row["PrimaryKey"] as bool?; aPdmEntityDto.UicontrolType = PLMDataRowConverter.ConvertDataBaseDataTypeToUicontrolType(aPdmEntityDto.DataBaseDataType); if (aPdmEntityDto.DataBaseDataType == "float" || aPdmEntityDto.DataBaseDataType == "real" || aPdmEntityDto.DataBaseDataType == "decimal") { aPdmEntityDto.Nbdecimal = row["Scale"] as int?; } // aPdmEntityDto.Nbdecimal //aPdmEntityDto.UicontrolType // aPdmEntityDto.IsIdentity = row["PrimaryKey"] as bool?; //aPdmEntityDto.UsedByDropDownList = row["UsedByDropDownList"] as bool?; //aPdmEntityDto.DataRowSort = row["DataRowSort"] as int?; //aPdmEntityDto.IsPrimaryKey = row["IsPrimaryKey"] as bool?; //aPdmEntityDto.IsIdentity = row["IsIdentity"] as bool?; //aPdmEntityDto.SystemTableColumnName = row["SystemTableColumnName"] as string; //aPdmEntityDto.FkentityId = row["FkentityId"] as int?; // aPdmEntityDto.UicontrolType = row["UicontrolType"] as int?; // aPdmEntityDto.Nbdecimal = row["Nbdecimal"] as int?; return(aPdmEntityDto); }
public static List <PdmEntityColumnClrUserDefineDto> GetErpExchangeDatabaseTableColumnDto(SqlConnection conn, string databaseTableName) { string query = string.Format(@"SELECT Columns.COLUMN_NAME as ColumnName,Columns.DATA_TYPE as DataType, Columns.CHARACTER_MAXIMUM_LENGTH as MaxLength, Columns.DATETIME_PRECISION as Precision, Columns.NUMERIC_SCALE as Scale, cast( (case when PK.COLUMN_NAME Is not null then 1 else 0 end) as bit) as PrimaryKey FROM information_schema.columns as Columns left join ( SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '{0}' ) as PK on Pk.COLUMN_NAME = Columns.COLUMN_NAME WHERE table_name = '{0}' and ( Columns.DATA_TYPE <> 'timestamp' and Columns.DATA_TYPE <>'image' and Columns.DATA_TYPE <>'xml' and Columns.DATA_TYPE <>'varbinary' ) and Columns.COLUMN_NAME NOT IN ('{1}') ", databaseTableName, PLMConstantString.ExchangeRowDataChangeTimeStamp ); List <PdmEntityColumnClrUserDefineDto> toreturnList = new List <PdmEntityColumnClrUserDefineDto>(); // PLMSDataImport.ExchangeRowDataPLMImportDateTimeColumn // CLROutput.Output(query); var result = DataAcessHelper.GetDataTableQueryResult(conn, query); // CLROutput.SendDataTable(result); foreach (DataRow arow in result.Rows) { PdmEntityColumnClrUserDefineDto fromDataBaseDto = PLMDataRowConverter.ConvertErpExchangeDatabaseTableColumnDataRowToPdmEntityColumnDto(arow); toreturnList.Add(fromDataBaseDto); } var textboxColumn = toreturnList.Where(o => o.UicontrolType == (int)EmControlType.TextBox).FirstOrDefault(); if (textboxColumn != null) { textboxColumn.UsedByDropDownList = true; } return(toreturnList); }
public static List <PdmClrEntitySimpleStructureDto> GetEntityAndColumnStrcutureInfoList(List <int> entityIDs) { List <PdmClrEntitySimpleStructureDto> listPdmEntityDto = new List <PdmClrEntitySimpleStructureDto>(); if (entityIDs != null && entityIDs.Count > 0) { string entityQuery = "select distinct EntityID ,EntityCode ,EntityType,SysTableName from pdmEntity "; string entityIdInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false); entityQuery = entityQuery + " where " + entityIdInClause; using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString)) { conn.Open(); DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, entityQuery); foreach (DataRow row in entityDataTable.Rows) { listPdmEntityDto.Add(PLMDataRowConverter.ConvertRowToPdmEntityDto(row)); } string queryColumn = @" select UserDefineEntityColumnID, EntityId, ColumnName,DataType,UsedByDropDownList,DataRowSort,IsPrimaryKey,IsIdentity,SystemTableColumnName,UicontrolType,Nbdecimal,FkentityId from PdmUserDefineEntityColumn " + " where " + entityIdInClause; DataTable columnDataTable = DataAcessHelper.GetDataTableQueryResult(conn, queryColumn); List <PdmEntityColumnClrUserDefineDto> allColumnDto = new List <PdmEntityColumnClrUserDefineDto>(); foreach (DataRow row in columnDataTable.Rows) { allColumnDto.Add(PLMDataRowConverter.ConvertUserDefineEntityColumnDataRowToPdmEntityColumnDto(row)); } foreach (var entityDto in listPdmEntityDto) { entityDto.Columns = allColumnDto.Where(o => o.EntityId == entityDto.EntityId).ToList(); } } } return(listPdmEntityDto); }