public DataManagementModel GetData([FromBody] List <DMParameter> param, string dmId)
{
int definitionId = 0;
if (!int.TryParse(dmId, out definitionId))
{
throw new Exception("Definition Id must be a valid integer number!");
}
DataManagementModel dataModel = new DataManagementModel();
QueryManager qm = new QueryManager();
List <DMDefinition> defList = qm.GetQueryResultDirect <DMDefinition>("SELECT * FROM dmdefinition WHERE id=@dmid", qm.GetSingleParameter <int>("dmid", definitionId));
List <IDataParameter> tableParameters = GenereateDBParameterFromBody(param);
List <DMColumnRelation> columnRelationList = qm.GetQueryResultDirect <DMColumnRelation>("SELECT * FROM dmcolumnrelation WHERE dmid=@dmid", qm.GetSingleParameter <int>("dmid", definitionId));
dataModel.QueryName = defList[0].QueryName;
dataModel.Definition = defList[0].Description;
DataTable tblData = qm.GetQueryResultDataTable(defList[0].QueryName, tableParameters);//todo: parse parameters..
List <DataManagementColumns> columnList = new List <DataManagementColumns>();
foreach (var crel in columnRelationList)
{
string schema = "";
string table = crel.TableName;
StringBuilder sbQuery = new StringBuilder();
List <AppEnum> enumlist = ApplicationBusiness.GetEnum(crel.TableName);
sbQuery.Append("select column_name, data_type, is_nullable, column_default from information_schema.columns where table_name = @table_name ");
sbQuery.Append("and(column_default not like '%nextval%' and column_default not like '%now%' or column_default is null) ");
sbQuery.Append("and column_name not in (select columnname from dmrestriction where tablename = @table_name)");
string query = sbQuery.ToString();
if (crel.TableName.Contains('.'))
{
string[] slicedTableNameArray = crel.TableName.Split('.');
schema = slicedTableNameArray[0];
table = slicedTableNameArray[1];
//query = "select column_name,data_type,is_nullable,column_default from information_schema.columns where table_name = @table_name and(column_default not like '%nextval%' and column_default not like '%now%' or column_default is null) and table_schema='"+ schema + "';";
query = query + "and table_schema = '" + schema + "'; ";
}
List <TableStructure> columnNames = qm.GetQueryResultDirect <TableStructure>(query, qm.GetSingleParameter <string>("table_name", table));
crel.TableColumns = GenerateTableStructureWithEnum(columnNames, enumlist);
}
foreach (DataColumn column in tblData.Columns)
{
columnList.Add(new DataManagementColumns
{
title = column.ColumnName,
DataType = column.DataType.ToString()
});
}
dataModel.Columns = columnList.ToArray <DataManagementColumns>();
dataModel.Data = GenerateStringArrayListForWebGrid(tblData);
dataModel.ColumnRelations = columnRelationList;
return(dataModel);
}
public DataManagementModel GetTableModel(string tableName, string queryKeyColumn, string tableKeyColumn)
{
DataManagementModel dataModel = new DataManagementModel();
QueryManager qm = new QueryManager();
List <DMColumnRelation> columnRelationList = new List <DMColumnRelation>();
columnRelationList.Add(new DMColumnRelation {
TableName = tableName, QueryColumnName = queryKeyColumn, TableColumnName = tableKeyColumn
});
List <DataManagementColumns> columnList = new List <DataManagementColumns>();
foreach (var crel in columnRelationList)
{
string schema = "";
string table = crel.TableName;
StringBuilder sbQuery = new StringBuilder();
List <AppEnum> enumlist = ApplicationBusiness.GetEnum(crel.TableName);
sbQuery.Append("select column_name, data_type, is_nullable, column_default from information_schema.columns where table_name = @table_name ");
sbQuery.Append("and(column_default not like '%nextval%' and column_default not like '%now%' or column_default is null) ");
sbQuery.Append("and column_name not in (select columnname from dmrestriction where tablename = @table_name)");
string query = sbQuery.ToString();
if (crel.TableName.Contains('.'))
{
string[] slicedTableNameArray = crel.TableName.Split('.');
schema = slicedTableNameArray[0];
table = slicedTableNameArray[1];
//query = "select column_name,data_type,is_nullable,column_default from information_schema.columns where table_name = @table_name and(column_default not like '%nextval%' and column_default not like '%now%' or column_default is null) and table_schema='"+ schema + "';";
query = query + "and table_schema = '" + schema + "'; ";
}
List <TableStructure> columnNames = qm.GetQueryResultDirect <TableStructure>(query, qm.GetSingleParameter <string>("table_name", table));
crel.TableColumns = GenerateTableStructureWithEnum(columnNames, enumlist);
}
//foreach (DataColumn column in tblData.Columns)
//{
// columnList.Add(new DataManagementColumns
// {
// title = column.ColumnName,
// DataType = column.DataType.ToString()
// });
//}
// dataModel.Columns = columnList.ToArray<DataManagementColumns>();
// dataModel.Data = GenerateStringArrayListForWebGrid(tblData);
dataModel.ColumnRelations = columnRelationList;
return(dataModel);
}
