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));
}
public async Task <Row> GetRowData(string connectionName, string tableName, string primaryKey, string tableColumnInfosJson)
{
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
var columnList = await GetColumnInfo(connectionName, tableName);
var sqlStmt = "";
if (string.IsNullOrEmpty(primaryKey))
{
var whereColumnListStmt = "";
var oldColumnList = JsonConvert.DeserializeObject <List <TableColumnInfo> >(tableColumnInfosJson).ToArray();
var builder = new System.Text.StringBuilder();
builder.Append(whereColumnListStmt);
for (int j = 0; j < columnList.Count(); j++)
{
if (string.IsNullOrEmpty(oldColumnList[j].Value))
{
builder.Append(columnList[j].Name + " is null and ");
}
else
{
builder.Append(columnList[j].Name + "='" + oldColumnList[j].Value + "' and ");
}
}
whereColumnListStmt = builder.ToString();
whereColumnListStmt = whereColumnListStmt.TrimEnd(' ', 'd', 'n', 'a');
sqlStmt = "select * from " + tableName + " where " + whereColumnListStmt;
}
else
{
var whereStmt = FindUniqueRowWhereStmt(primaryKey, columnList);
sqlStmt = "select * from " + tableName + " where " + whereStmt;
}
var sessionSqlHistory = new SessionSqlHistory
{
EventDate = DateTime.Now,
SqlText = sqlStmt,
BasicSqlText = sqlStmt
};
var row = new Row();
using (var oconn = new OracleConnection(connectionString))
{
oconn.Open();
using (var cmd = new OracleCommand
{
Connection = oconn,
CommandText = sqlStmt,
CommandType = CommandType.Text
})
{
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var rowData = new List <TableColumnInfo>();
for (int i = 0; i < columnList.Count; i++)
{
var columnInfo = columnList[i];
if (dr.GetValue(i) != DBNull.Value)
{
columnInfo.Value = dr.GetValue(i).ToString();
columnInfo.OldValue = dr.GetValue(i).ToString();
}
else
{
columnInfo.Value = "";
}
if (columnInfo.IsPrimaryKey)
{
row.PrimaryKey = string.IsNullOrEmpty(row.PrimaryKey) ? columnInfo.Value : row.PrimaryKey + ";" + columnInfo.Value;
}
if (!string.IsNullOrEmpty(columnInfo.Value) && columnInfo.IsForeignKey)
{
var foreignTable = columnInfo.ForeignTable;
var findForeignRowSql = "select * from " + foreignTable + " where " + columnInfo.ForeignTableKeyColumn + " = '" + columnInfo.Value + "'";
var foreignDesc = await FindForeignDescription(oconn, connectionName, foreignTable, columnInfo.ForeignTableKeyColumn, columnInfo.Value);
columnInfo.ForeignDescription = foreignDesc.Trim();
}
rowData.Add(columnInfo);
}
row.TableColumnInfos = rowData;
break;
}
}
}
await _context.SessionSqlHistory.AddAsync(sessionSqlHistory);
await _context.SaveChangesAsync();
return(row);
}
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));
}
public async Task <PagedData> GetTableDataListSearch(string connectionName, string tableName, int page, int pageSize, List <SearchFieldInfo> searchFields, string sortColumn, string sortDir, string sortColumnDataType)
{
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
var columnList = await GetColumnInfo(connectionName, tableName);
string whereStmt = "";
foreach (var field in searchFields)
{
whereStmt += field.Name + " like '" + field.Value + "%' and ";
}
if (!string.IsNullOrEmpty(whereStmt))
{
whereStmt = " where " + whereStmt.TrimEnd(' ', 'a', 'n', 'd', ' ');
}
var orderStmt = "";
if (!string.IsNullOrEmpty(sortColumn))
{
orderStmt = " order by " + sortColumn + " " + sortDir;
}
else
{
orderStmt = " order by ROWID ";
}
var tableDataListCountSql = @"select count(*) from " + tableName + whereStmt;
var pagedData = new PagedData
{
Page = page,
PageSize = pageSize
};
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 (" + orderStmt + ") rn " +
"from " + tableName + @" mt " + whereStmt + ") " +
"where rn between " + belowRowIndex + " and " + topRowIndex + (string.IsNullOrEmpty(orderStmt) ? " order by rn" : "");
var sessionSqlHistory = new SessionSqlHistory
{
EventDate = DateTime.Now,
SqlText = tableDataListSql,
BasicSqlText = "select * from " + tableName + whereStmt + (string.IsNullOrEmpty(orderStmt) ? "" : orderStmt)
};
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);
}
