public async Task <IActionResult> RemoveConnection(long?Id) { var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(m => m.Id == Id); _context.CustomConnection.Remove(customConnection); await _context.SaveChangesAsync(); return(RedirectToAction(nameof(Index))); }
public async Task <IActionResult> Create([Bind("Id,Name,Host")] CustomConnection customConnection) { if (ModelState.IsValid) { _context.Add(customConnection); await _context.SaveChangesAsync(); return(RedirectToAction(nameof(Index))); } return(View(customConnection)); }
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)); }
public async Task <PagedData> GetTableDataList(List <TableColumnInfo> columnList, string connectionName, string tableName, int page = 0) { var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName); var connectionString = Util.GetConnectionString(customConnection); var tableDataListCountSql = @"select count(*) from " + tableName; var pagedData = new PagedData { Page = page }; decimal count = 0; var belowRowIndex = 1; var topRowIndex = 1 * pagedData.PageSize; if (page > 0) { belowRowIndex = page * pagedData.PageSize + 1; topRowIndex = (page + 1) * pagedData.PageSize; } var tableDataListSql = @"select * from ( " + "select mt.*, " + "row_number() over (order by ROWID) rn " + "from " + tableName + @" mt) " + "where rn between " + belowRowIndex + " and " + topRowIndex + " order by rn"; var sessionSqlHistory = new SessionSqlHistory { EventDate = DateTime.Now, SqlText = tableDataListSql, BasicSqlText = "select * from " + tableName }; var myRowDict = new Dictionary <int, Row>(); using (var oconn = new OracleConnection(connectionString)) { oconn.Open(); using (var cmdCount = new OracleCommand { Connection = oconn, CommandText = tableDataListCountSql, CommandType = CommandType.Text }) { count = (decimal)cmdCount.ExecuteScalar(); oconn.Open(); using (var cmd = new OracleCommand { Connection = oconn, CommandText = tableDataListSql, CommandType = CommandType.Text }) { var dr = cmd.ExecuteReader(); var viewRowId = 0; while (dr.Read()) { var row = new Row(); var rowData = new List <TableColumnInfo>(); for (int i = 0; i < columnList.Count; i++) { var columnInfo = columnList[i]; var tableColumnInfo = new TableColumnInfo { DataType = columnInfo.DataType, IsPrimaryKey = columnInfo.IsPrimaryKey, Name = columnInfo.Name }; if (dr.GetValue(i) != DBNull.Value) { tableColumnInfo.Value = dr.GetValue(i).ToString(); } else { tableColumnInfo.Value = ""; } if (tableColumnInfo.IsPrimaryKey) { row.PrimaryKey = string.IsNullOrEmpty(row.PrimaryKey) ? tableColumnInfo.Value : row.PrimaryKey + ";" + tableColumnInfo.Value; } rowData.Add(tableColumnInfo); } row.TableColumnInfos = rowData; row.TableColumnInfosJson = JsonConvert.SerializeObject(rowData); myRowDict.Add(viewRowId++, row); } } } } await _context.SessionSqlHistory.AddAsync(sessionSqlHistory); await _context.SaveChangesAsync(); pagedData.Data = myRowDict; pagedData.Total = Convert.ToInt32(count); pagedData.PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(count) / (Convert.ToDouble(pagedData.PageSize)))); return(pagedData); }