public async Task <IActionResult> TableDataLookup(string connectionName, string tableName = "undefined", int page = 0) { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; if (tableName != "undefined") { columnList = await _util.GetColumnInfo(connectionName, tableName); tableDataDict = await _util.GetTableDataList(columnList, connectionName, tableName, page); } ; tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; return(View("LookupSearch", tableDataVM)); }
public async Task <IActionResult> LookupSearch(string parentColumn, string connectionName, string tableName, string tableColumn, int page = 0) { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; columnList = await _util.GetColumnInfo(connectionName, tableName); tableDataDict = await _util.GetTableDataList(columnList, connectionName, tableName, page); tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; tableDataVM.ForeignTableColumn = tableColumn; tableDataVM.ParentColumn = parentColumn; tableDataVM.PagerStart = Util.FindPagerStart(page); return(View(tableDataVM)); }
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 <IActionResult> EditData(string connectionName, string tableName, string id, int page, int tableRowIndx, string searchFields = "", string sortColumn = "", string sortDir = "") { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; columnList = await _util.GetColumnInfo(connectionName, tableName); tableDataDict = await _util.GetTableDataList(columnList, connectionName, tableName, page); var row = tableDataDict.Data[tableRowIndx]; var tableColumnInfosJson = row.TableColumnInfosJson; var tablemetadata = await _util.GetTableMetadata(connectionName, tableName); tableDataVM.SequenceName = tablemetadata.SequenceName; tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; tableDataVM.RowData = await _util.GetRowData(connectionName, tableName, id, tableColumnInfosJson); tableDataVM.TableColumnInfosJson = tableColumnInfosJson; tableDataVM.SortColumn = sortColumn; tableDataVM.SortDir = sortDir; var searchFieldsArray = JsonConvert.DeserializeObject <List <SearchFieldInfo> >(searchFields); foreach (var field in searchFieldsArray) { tableDataVM.SearchValues[field.Name] = field.Value; } tableDataVM.SearchFields = searchFields; return(View(tableDataVM)); }
public async Task <IActionResult> TableData(string connectionName, string tableName = "undefined", int page = 0) { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; var assemblyVersion = typeof(Startup).Assembly.GetName().Version.ToString(); tableDataVM.Version = assemblyVersion; tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); await _util.ResetTableMetadata(connectionName, tableName); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; if (tableName != "undefined") { columnList = await _util.GetColumnInfo(connectionName, tableName); tableDataDict = await _util.GetTableDataList(columnList, connectionName, tableName, page); } ; tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; tableDataVM.PagerStart = Util.FindPagerStart(page); ViewBag.ConnectionInfo = connectionName; return(View(tableDataVM)); }
public async Task <IActionResult> AddDataPost(string connectionName, string tableName, IEnumerable <string> dataFields) { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName); var connectionString = Util.GetConnectionString(customConnection); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; columnList = await _util.GetColumnInfo(connectionName, tableName); var tablemetadata = await _util.GetTableMetadata(connectionName, tableName); tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; tableDataVM.SequenceName = tablemetadata.SequenceName; var columnListStmt = ""; var builderColumn = new System.Text.StringBuilder(); builderColumn.Append(columnListStmt); foreach (var column in columnList) { builderColumn.Append(column.Name + ", "); } columnListStmt = builderColumn.ToString(); columnListStmt = columnListStmt.TrimEnd(' ').TrimEnd(','); var valueListStmt = ""; var dataFieldArray = dataFields.ToArray(); var builder = new System.Text.StringBuilder(); builder.Append(valueListStmt); for (int i = 0; i < dataFieldArray.Count(); i++) { if (columnList[i].IsPrimaryKey && tableDataVM.SequenceName != "undefined") { builder.Append(tablemetadata.SequenceName + ".NEXTVAL, "); } else { if (columnList[i].DataType.Equals("DATE")) { builder.Append("TO_DATE('" + dataFieldArray[i] + "','dd.mm.yyyy HH24:MI:SS'), "); } else { builder.Append("'" + dataFieldArray[i] + "', "); } } } valueListStmt = builder.ToString(); valueListStmt = valueListStmt.TrimEnd(' ').TrimEnd(','); var insertSqlStmt = "insert into " + tableName + " (" + columnListStmt + ") values (" + valueListStmt + ")"; var sessionHistorySql = new SessionSqlHistory { EventDate = DateTime.Now, SqlText = insertSqlStmt, BasicSqlText = insertSqlStmt }; using (var oconn = new OracleConnection(connectionString)) { oconn.Open(); using (var cmd = new OracleCommand { Connection = oconn, CommandText = insertSqlStmt, CommandType = CommandType.Text }) { var result = cmd.ExecuteNonQuery(); } } await _context.SessionSqlHistory.AddAsync(sessionHistorySql); await _context.SaveChangesAsync(); ViewBag.Message = "Added successfully"; return(View("AddData", tableDataVM)); }
public async Task <IActionResult> EditDataPost(string connectionName, string tableName, IEnumerable <string> dataFields, IEnumerable <string> oldDataFields, string tableColumnInfosJson) { var tableDataVM = new TableDataVM { TableList = await _util.GetTableList(connectionName), TableName = tableName, ConnectionName = connectionName }; var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName); var connectionString = Util.GetConnectionString(customConnection); tableDataVM.TableGroups = await _util.GetTableGroups(tableDataVM.TableList); var columnList = new List <TableColumnInfo>(); var tableDataDict = new PagedData { Data = new Dictionary <int, Row>() }; columnList = await _util.GetColumnInfo(connectionName, tableName); var tablemetadata = await _util.GetTableMetadata(connectionName, tableName); tableDataVM.ColumnList = columnList; tableDataVM.TableDataList = tableDataDict; tableDataVM.SequenceName = tablemetadata.SequenceName; var dataFieldArray = dataFields.ToArray(); var oldDataFieldArray = oldDataFields.ToArray(); var primaryKey = GetPrimaryKey(columnList, dataFieldArray); var whereColumnListStmt = ""; if (string.IsNullOrEmpty(primaryKey)) { var oldColumnList = JsonConvert.DeserializeObject <List <TableColumnInfo> >(tableColumnInfosJson).ToArray(); var builderWhere = new System.Text.StringBuilder(); builderWhere.Append(whereColumnListStmt); for (int j = 0; j < columnList.Count(); j++) { builderWhere.Append(columnList[j].Name + "='" + oldColumnList[j].Value + "' and "); } whereColumnListStmt = builderWhere.ToString(); whereColumnListStmt = whereColumnListStmt.TrimEnd(' ', 'd', 'n', 'a'); } var columnListStmt = ""; var builder = new System.Text.StringBuilder(); builder.Append(columnListStmt); for (int j = 0; j < columnList.Count(); j++) { if (columnList[j].IsPrimaryKey) { continue; } if (columnList[j].DataType.Equals("DATE")) { builder.Append(columnList[j].Name + "=TO_DATE('" + dataFieldArray[j] + "','dd.mm.yyyy HH24:MI:SS'), ");; } else { builder.Append(columnList[j].Name + "='" + dataFieldArray[j] + "', ");; } } columnListStmt = builder.ToString(); columnListStmt = columnListStmt.TrimEnd(' ').TrimEnd(','); var updateSqlStmt = ""; var whereStmt = Util.FindUniqueRowWhereStmt(primaryKey, columnList); updateSqlStmt = string.IsNullOrEmpty(primaryKey) ? "update " + tableName + " set " + columnListStmt + " where " + whereColumnListStmt : "update " + tableName + " set " + columnListStmt + " where " + whereStmt; var sessionHistorySql = new SessionSqlHistory { EventDate = DateTime.Now, SqlText = updateSqlStmt, BasicSqlText = updateSqlStmt }; using (var oconn = new OracleConnection(connectionString)) { oconn.Open(); using (var cmd = new OracleCommand { Connection = oconn, CommandText = updateSqlStmt, CommandType = CommandType.Text }) { var result = cmd.ExecuteNonQuery(); } } await _context.SessionSqlHistory.AddAsync(sessionHistorySql); await _context.SaveChangesAsync(); var tableColumnInfos = new List <TableColumnInfo>(); for (int j = 0; j < columnList.Count(); j++) { var tableColumnInfo = new TableColumnInfo { DataType = columnList[j].DataType, IsPrimaryKey = columnList[j].IsPrimaryKey, Name = columnList[j].Name, Value = dataFieldArray[j], OldValue = columnList[j].OldValue }; tableColumnInfos.Add(tableColumnInfo); } var newTableColumnInfosJson = JsonConvert.SerializeObject(tableColumnInfos); tableDataVM.RowData = await _util.GetRowData(connectionName, tableName, primaryKey, newTableColumnInfosJson); tableDataVM.TableColumnInfosJson = newTableColumnInfosJson; ViewBag.Message = "Successfully saved"; return(View(nameof(EditData), tableDataVM)); }