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