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