public static List<DALDataTableMetaData> GetAllMetaData()
{
var rtrn = new List<DALDataTableMetaData>();
var databaseTypes =
new List<DALISGDatabaseType>
{
DALISGDatabaseType.ISGInput,
DALISGDatabaseType.ISGTransient,
DALISGDatabaseType.ISGOutput
};
foreach (var databaseType in databaseTypes)
{
using (var context = new ISGOutputEntities(Database.GetEFConnectionString(databaseType)))
{
var metaDatas = context.TableMetaData.ToList().OrderBy(x => x.tableName);
var dalDataTable = new DALDataTableMetaData();
foreach (var metaData in metaDatas)
{
if (!string.Equals(metaData.tableName, dalDataTable.Name, StringComparison.CurrentCultureIgnoreCase))
{
dalDataTable =
new DALDataTableMetaData
{
Name = metaData.tableName,
Columns = new List<DALDataTableColumn>(),
DatabaseType = databaseType
};
rtrn.Add(dalDataTable);
}
dalDataTable.Columns.Add(
new DALDataTableColumn
{
Name = metaData.columnName,
IsNullable = (metaData.columnIsNullable.HasValue && metaData.columnIsNullable == 1),
Length = metaData.columnLength,
Precision = metaData.columnPrecision,
ColumnOrdinal = metaData.columnOrdinal,
Scale = metaData.columnScale,
TypeName = metaData.columnTypeName,
ParentTable = metaData.tableName,
IsPrimaryKey = (metaData.isPrimaryKey == 1),
IsReadOnlyIdentity = (metaData.isReadonlyIdentity == 1)
});
}
}
}
return rtrn;
}
public static DALDataTable GetData(string tableName, string sortIndex, string sortOrder, long pageIndex, long pageSize, string filterQuery, DALISGDatabaseType databaseType)
{
var dataTable =
new DALDataTable
{
Name = tableName,
Columns = new List<DALDataTableColumn>(),
Rows = new List<DALDataTableRow>()
};
using (var context = new ISGOutputEntities(Database.GetEFConnectionString(databaseType)))
{
var metaDatas = context.TableMetaData.Where(f => string.Equals(f.tableName, tableName, StringComparison.CurrentCultureIgnoreCase)).ToList();
if (metaDatas.Any())
{
foreach (var metaData in metaDatas)
{
dataTable.Columns.Add(
new DALDataTableColumn
{
Name = metaData.columnName,
IsNullable = (metaData.columnIsNullable.HasValue && metaData.columnIsNullable == 1),
Length = metaData.columnLength,
Precision = metaData.columnPrecision,
Scale = metaData.columnScale,
TypeName = metaData.columnTypeName,
ParentTable = tableName,
IsPrimaryKey = (metaData.isPrimaryKey == 1)
});
}
sortOrder = (!string.IsNullOrEmpty(sortOrder)) ? sortOrder : "ASC";
sortIndex = (!string.IsNullOrEmpty(sortIndex) && dataTable.Columns.Any(x => string.Equals(x.Name, sortIndex, StringComparison.CurrentCultureIgnoreCase))) ? sortIndex : "OSC";
var startingRowNum = pageIndex * (pageSize - 1) + 1;
var endingRowNum = (pageIndex + 1) * pageSize;
var sql = new StringBuilder("");
sql.Append("SELECT * FROM ");
sql.AppendFormat("(SELECT ROW_NUMBER() OVER (ORDER BY {0} {1}) AS RowNum, * FROM {2} {5}) AS Result WHERE RowNum >= {3} AND RowNum <= {4} ORDER BY {0} {1}", sortIndex, sortOrder, tableName, startingRowNum, endingRowNum, filterQuery);
var connection = (SqlConnection)context.Database.Connection;
var dataRows = connection.Query(sql.ToString()).ToList();
foreach (var row in dataRows)
{
var dataRow =
new DALDataTableRow
{
Cells = new List<DALDataTableCell>()
};
var rowId = new StringBuilder(tableName);
foreach (var column in dataTable.Columns)
{
var value = (((IDictionary<string, object>)row)[column.Name]);
if (column.IsPrimaryKey && value != null)
{
var newId = Regex.Replace(value.ToString(), "[^0-9a-zA-Z]+", "");
rowId.Append(newId);
}
dataRow.Cells.Add(
new DALDataTableCell
{
ColumnName = column.Name,
Value = (value != null) ? value.ToString() : string.Empty
});
}
dataRow.RowId = rowId.ToString();
dataTable.Rows.Add(dataRow);
}
}
else
{
dataTable = null;
}
}
return dataTable;
}
public static bool UpdateRow(string tableName, DALDataTableRowMetaData row, DALISGDatabaseType databaseType)
{
try
{
using (var context = new ISGOutputEntities(Database.GetEFConnectionString(databaseType)))
{
var connection = (SqlConnection)context.Database.Connection;
var setQuery = BuildSetQuery(row.Cells.Where(c => !c.IsPrimaryKey));
var whereQuery = BuildWhereQuery(row.Cells.Where(c => c.IsPrimaryKey));
var query = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, setQuery, whereQuery);
connection.Execute(query);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Unable to update row in table: {0}", tableName), ex.InnerException);
}
return true;
}
public static List<string> GetDistinctValues(string tableName, string columnName, DALISGDatabaseType databaseType)
{
List<string> rtrn;
using (var context = new ISGOutputEntities(Database.GetEFConnectionString(databaseType)))
{
var connection = (SqlConnection)context.Database.Connection;
var queryString = string.Format("SELECT DISTINCT {0} AS DISTINCT_VALUE FROM {1} ORDER BY {0} ASC", columnName, tableName);
var dataRows = connection.Query(queryString).ToList();
if (!dataRows.Any())
{
return null;
}
rtrn = new List<string>();
// ReSharper disable once LoopCanBeConvertedToQuery
foreach(var x in dataRows)
{
if (x.DISTINCT_VALUE != null) { rtrn.Add(Convert.ToString(x.DISTINCT_VALUE)); }
}
}
return rtrn;
}