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