public WebDataTable(string name, string sortIndex, string sortOrder, long pageIndex, long pageSize, FilterDataObject filterData, WebISGDatabaseType databaseType)
{
Name = name;
Rows = new List<WebDataTableRow>();
// The "Actions" column will be the same across every table
Columns =
new List<WebDataTableColumn>
{
new WebDataTableColumn("act")
{
ParentTable = name,
DatabaseType = databaseType
}
};
using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
{
var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
var rtrn = serviceClient.GetData(Name, sortIndex, sortOrder, pageIndex, pageSize, filterData.FilterQuery, database);
foreach (var column in rtrn.Columns)
{
Columns.Add(
new WebDataTableColumn(column.Name)
{
IsNullable = column.IsNullable,
Length = column.Length,
Precision = column.Precision,
Scale = column.Scale,
TypeName = column.TypeName,
ParentTable = name,
IsPrimaryKey = column.IsPrimaryKey,
IsReadonlyIdentity = column.IsReadonlyIdentity,
DatabaseType = databaseType
});
}
Columns = Columns.OrderBy(x => x.Order).ThenBy(x => x.FriendlyName).ToList();
foreach (var row in rtrn.Rows)
{
var dataRow = new WebDataTableRow(row.RowId);
foreach (var column in Columns.Where(c => !c.Name.Equals("act")))
{
var cell = row.Cells.FirstOrDefault(x => string.Equals(x.ColumnName, column.Name, StringComparison.CurrentCultureIgnoreCase));
var value = (cell != null) ? cell.Value : string.Empty;
dataRow.Cells.Add(
new WebDataTableCell(column)
{
Value = value
});
}
Rows.Add(dataRow);
}
}
}
public static long GetRowCount(string tableName, FilterDataObject filterData, WebISGDatabaseType databaseType)
{
long rtrn;
using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
{
var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
rtrn = serviceClient.GetRowCount(tableName, filterData.FilterQuery, database);
}
return rtrn;
}
public static bool SaveData(string tableName, NeonDataTableRowMetaData row, WebISGDatabaseType databaseType)
{
try
{
using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
{
var neonDatabase = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
// ReSharper disable once UnusedVariable
var rtrn = serviceClient.SaveRow(tableName, row, neonDatabase);
}
return true;
}
catch (Exception ex)
{
throw new Exception(string.Format("Unable to save information to database for table {0}", tableName), ex);
}
}
public WebDataTableMetaData(NeonDataTableMetaData serviceMetadata)
{
Name = serviceMetadata.Name;
DatabaseType = Mapper.Map<NeonISGDatabaseType, WebISGDatabaseType>(serviceMetadata.DatabaseType);
// The "Actions" column will be the same across every table
Columns =
new List<WebDataTableColumn>
{
new WebDataTableColumn("act")
{
ParentTable = serviceMetadata.Name,
IsNullable = true,
DatabaseType = DatabaseType
}
};
foreach (var metaData in serviceMetadata.Columns)
{
Columns.Add(
new WebDataTableColumn(metaData.Name)
{
IsNullable = metaData.IsNullable,
Length = metaData.Length,
Precision = metaData.Precision,
Scale = metaData.Scale,
TypeName = metaData.TypeName,
ParentTable = serviceMetadata.Name,
ParentSourceTable = metaData.ParentSourceTable,
IsPrimaryKey = metaData.IsPrimaryKey,
IsReadonlyIdentity = metaData.IsReadonlyIdentity,
DatabaseType = DatabaseType
});
}
Columns = Columns.OrderBy(x => x.Name).ToList();
var firstColumn = Columns.FirstOrDefault(c => !string.Equals(c.FriendlyName, "Actions", StringComparison.CurrentCultureIgnoreCase));
if (firstColumn != null)
{
SourceName = firstColumn.ParentSourceTable;
}
}
public ActionResult GetGridData(string sidx, string sord, string filters, int page, int rows, string tableName, WebISGDatabaseType databaseType)
{
var jsonData = new JsonResult();
if (string.IsNullOrEmpty(tableName))
{
return jsonData;
}
var filterData = filters != null ? JsonConvert.DeserializeObject<FilterDataObject>(filters) : new FilterDataObject();
var rowCount = WebDataTable.GetRowCount(tableName, filterData, databaseType);
var currentPage = page;
var pageIndex = currentPage - 1;
var pageSize = rows != -1 ? rows : rowCount;
var totalRecords = rowCount;
var totalPages = (int)Math.Ceiling(totalRecords / (float)pageSize);
var dataTable = new WebDataTable(tableName, sidx, sord, pageIndex, pageSize, filterData, databaseType);
var rowData = new
{
total = totalPages,
page = currentPage,
records = totalRecords,
rows =
(
from row in dataTable.Rows
select new
{
id = row.RowId,
cell = row.CellValueArray
}
).ToArray()
};
jsonData = Json(rowData, JsonRequestBehavior.AllowGet);
return jsonData;
}
public ActionResult GetDropdownOptions(string table, string column, WebISGDatabaseType databaseType)
{
List<string> distinctValues;
using (var serviceClient = new NeonISGDataServiceClient(Configuration.ActiveNeonDataServiceEndpoint))
{
var database = Mapper.Map<WebISGDatabaseType, NeonISGDatabaseType>(databaseType);
distinctValues = serviceClient.GetDistinctValues(table, column, database);
}
var jsonData = Json(distinctValues, JsonRequestBehavior.AllowGet);
return jsonData;
}
/// <summary>
/// This method is called to allow the user to download the entire table as a CSV file.
/// </summary>
/// <returns></returns>
public FileStreamResult DownloadFullExcel(string id, WebISGDatabaseType databaseType, bool showHiddenColumns = false)
{
//Create a workbook & worksheet
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(id);
if (!string.IsNullOrEmpty(id))
{
var filterData = new FilterDataObject();
var rowCount = WebDataTable.GetRowCount(id, filterData, databaseType);
var dataTable = new WebDataTable(id, "", "asc", 0, rowCount, filterData, databaseType);
// get column headers
var colHeaders = dataTable.Columns
.Where(c => !c.Name.Equals("act") &&
(showHiddenColumns || c.Hidden.ToLower() != "true"))
.Select(cd => cd.FriendlyName ?? string.Empty)
.ToArray();
// get row data
var rowsToInsert = new List<Array>();
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (var row in dataTable.Rows)
{
rowsToInsert.Add(
row.Cells
.Where(cd => showHiddenColumns || cd.Column.Hidden.ToLower() != "true")
.Select(cd => cd.Value ?? string.Empty)
.ToArray()
);
}
// add data to worksheet
for (var i = 0; i < colHeaders.Length; i++)
{
ws.Cell(1, i + 1).Value = colHeaders[i];
}
// ReSharper disable once UnusedVariable
var table = ws.Cell(1, 1).InsertTable(rowsToInsert);
ws.Columns().AdjustToContents(1,99);
}
var memoryStream = new MemoryStream();
wb.SaveAs(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0;
return File(memoryStream, "application/vnd.ms-excel", string.Format("{0}.xlsx", id));
}