public async Task <IActionResult> TableDataSearch(string connectionName, string tableName, string searchFields, int page = 0, int pageSize = 10, string sortColumn = "", string sortDir = "", string sortColumnDataType = "")
{
var tableDataVM = new TableDataVM
{
TableList = await _util.GetTableList(connectionName),
TableName = tableName,
ConnectionName = connectionName
};
var assemblyVersion = typeof(Startup).Assembly.GetName().Version.ToString();
tableDataVM.Version = assemblyVersion;
var columnList = new List <TableColumnInfo>();
var tableDataDict = new PagedData {
Data = new Dictionary <int, Row>()
};
var searchFieldsArray = JsonConvert.DeserializeObject <List <SearchFieldInfo> >(searchFields);
tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList);
await _util.ResetTableMetadata(connectionName, tableName);
if (tableName != "undefined")
{
columnList = await _util.GetColumnInfo(connectionName, tableName);
tableDataDict = await _util.GetTableDataListSearch(connectionName, tableName, page, pageSize, searchFieldsArray, sortColumn, sortDir, sortColumnDataType);
}
;
tableDataVM.ColumnList = columnList;
tableDataVM.TableDataList = tableDataDict;
foreach (var field in searchFieldsArray)
{
tableDataVM.SearchValues[field.Name] = field.Value;
}
tableDataVM.SortColumn = sortColumn;
tableDataVM.SortDir = sortDir;
tableDataVM.PagerStart = Util.FindPagerStart(page);
return(View("TableData", tableDataVM));
}
public async Task <JsonResult> DeleteRowAsync(string connectionName, string tableName, string id, int page, int tableRowIndx, string searchFields)
{
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
var tableDataDict = new PagedData {
Data = new Dictionary <int, Row>()
};
var searchFieldsArray = JsonConvert.DeserializeObject <List <SearchFieldInfo> >(searchFields);
var columnList = await _util.GetColumnInfo(connectionName, tableName);
tableDataDict = searchFieldsArray.Any() ? await _util.GetTableDataListSearch(connectionName, tableName, 0, searchFieldsArray[0].Name, searchFieldsArray[0].Value) : await _util.GetTableDataList(columnList, connectionName, tableName, page);
var row = tableDataDict.Data[tableRowIndx];
var tableColumnInfosJson = row.TableColumnInfosJson;
var whereStmt = Util.FindUniqueRowWhereStmt(id, columnList);
var deleteSqlStmt = "delete " + tableName + " where " + whereStmt;
if (string.IsNullOrEmpty(id))
{
var whereColumnListStmt = "";
var oldColumnList = JsonConvert.DeserializeObject <List <TableColumnInfo> >(tableColumnInfosJson).ToArray();
var builder = new System.Text.StringBuilder();
builder.Append(whereColumnListStmt);
for (int j = 0; j < oldColumnList.Count(); j++)
{
builder.Append(oldColumnList[j].Name + "='" + oldColumnList[j].Value + "' and ");
}
whereColumnListStmt = builder.ToString();
whereColumnListStmt = whereColumnListStmt.TrimEnd(' ', 'd', 'n', 'a');
deleteSqlStmt = "delete " + tableName + " where " + whereColumnListStmt;
}
var sessionHistorySql = new SessionSqlHistory
{
EventDate = DateTime.Now,
SqlText = deleteSqlStmt,
BasicSqlText = deleteSqlStmt
};
using (var oconn = new OracleConnection(connectionString))
{
oconn.Open();
using (var cmd = new OracleCommand
{
Connection = oconn,
CommandText = deleteSqlStmt,
CommandType = CommandType.Text
})
{
var result = cmd.ExecuteNonQuery();
}
}
await _context.SessionSqlHistory.AddAsync(sessionHistorySql);
await _context.SaveChangesAsync();
return(Json(true));
}