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