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