/// <summary>
///
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static TableColumnInfo[] GetColumns(this IDataReader reader)
{
if (reader == null)
{
return new TableColumnInfo[] { }
}
;
var dt = reader.GetSchemaTable();
if (dt == null)
{
return new TableColumnInfo[] { }
}
;
var columns = new TableColumnInfo[dt.Rows.Count];
for (var i = 0; i < columns.Length; i++)
{
var row = dt.Rows[i];
var columnName = row["ColumnName"].To <string>();
columns[i] = new TableColumnInfo(columnName)
{
DataType = reader.GetDataTypeName(i),
Nullable = row["AllowDBNull"].ToBool(),
RuntimeType = Type.GetType(row["DataType"].To <string>()),
Ordinal = row["ColumnOrdinal"].To <int>()
};
}
return(columns);
}
public override object GetValue(PropertyInfo entityPropertyInfo, TableColumnInfo columnInfo, byte[] message)
{
if (message == null || message.Length == 0)
{
return(null);
}
if (entityPropertyInfo.PropertyType.GetTypeInfo().IsEnum)
{
var stringValue = Encoding.Unicode.GetString(message);
var value = Enum.Parse(entityPropertyInfo.PropertyType, stringValue);
return(value.GetHashCode());
}
if (entityPropertyInfo.PropertyType == typeof(byte[]))
{
return(message);
}
if (entityPropertyInfo.PropertyType == typeof(bool) || entityPropertyInfo.PropertyType == typeof(bool?))
{
return(Encoding.Unicode.GetString(message).ToBoolean());
}
if (entityPropertyInfo.PropertyType == typeof(char[]))
{
return(Encoding.Unicode.GetString(message).ToCharArray());
}
return(base.GetValue(entityPropertyInfo, columnInfo, message));
}
public bool IsColumnEquals(TableMetadataInfo metadataInfo, TableColumnInfo columnInfo)
{
if (!StringUtils.EqualsIgnoreCase(metadataInfo.AttributeName, columnInfo.ColumnName)) return false;
if (metadataInfo.DataType != columnInfo.DataType) return false;
if (metadataInfo.DataLength != columnInfo.Length) return false;
return true;
}
private static List <TableColumnInfo> Analyze(
IEnumerator <MElement> en,
MarkdownWriterSettings settings,
MarkdownStringWriter writer)
{
MElement header = null;
var columns = new List <TableColumnInfo>();
header = en.Current;
if (header is MContainer container)
{
WriteHeaderCells(container, settings, writer, columns);
}
else
{
writer.Write(header);
columns.Add(TableColumnInfo.Create(header, writer));
}
if (settings.Format.FormatTableContent)
{
int index = writer.Length;
while (en.MoveNext())
{
int columnCount = columns.Count;
MElement row = en.Current;
if (row is MContainer rowContainer)
{
int i = 0;
foreach (MElement cell in rowContainer.Elements())
{
writer.Write(cell);
columns[i] = columns[i].UpdateWidthIfGreater(writer.Length - index);
index = writer.Length;
i++;
if (i == columnCount)
{
break;
}
}
}
else
{
writer.Write(row);
columns[0] = columns[0].UpdateWidthIfGreater(writer.Length - index);
index = writer.Length;
}
}
}
return(columns);
}
public static object ForColumn(TableColumnInfo columnInfo)
{
try
{
return(GenerateForColumn(columnInfo));
}
catch (Exception ex)
{
throw new Exception($"Could not generate value for column Name=[{columnInfo.Name}] Type=[{columnInfo.ColumnType}]", ex);
}
}
protected override void SetTableColumns()
{
TableColumnInfo[] columns = new TableColumnInfo[]
{
new TableColumnInfo("ID", "ID"),
new TableColumnInfo("Cena", "Price"),
new TableColumnInfo("Datum", "Date"),
new TableColumnInfo("Vreme", "Time")
};
view.CreateColumns(columns);
}
private static void WriteHeaderCells(
MContainer header,
MarkdownWriterSettings settings,
MarkdownStringWriter writer,
List <TableColumnInfo> columns)
{
int index = 0;
bool isFirst = true;
bool isLast = false;
int i = 0;
using (IEnumerator <MElement> en = header.Elements().GetEnumerator())
{
if (en.MoveNext())
{
MElement curr = en.Current;
isLast = !en.MoveNext();
WriteHeaderCell(curr);
if (!isLast)
{
isFirst = false;
do
{
curr = en.Current;
isLast = !en.MoveNext();
i++;
WriteHeaderCell(curr);
}while (!isLast);
}
}
}
void WriteHeaderCell(MElement cellContent)
{
if (isFirst ||
isLast ||
settings.Format.FormatTableHeader)
{
writer.Write(cellContent);
}
columns.Add(TableColumnInfo.Create(cellContent, writer, index));
index = writer.Length;
}
}
public void TableColumnInfo_ctor_ArgumentNullExceptionThrownWithNullColumnName()
{
// Arrange / Act / Assert
_biz = new TableColumnInfo(
null,
_columnDescription,
_columnDataType,
_columnLength,
_defaultValue,
_allowsNulls,
_keySequence
);
}
private static object GenerateForColumn(TableColumnInfo columnInfo)
{
switch (columnInfo.ColumnType)
{
case ColumnType.String:
// generates a random string
return(Any.String(columnInfo.MaxLength));
case ColumnType.Int:
// generates a random integer
long maxValue = (int)Math.Pow(10, columnInfo.Precision - 1);
if (maxValue > int.MaxValue)
{
return(Any.Long(1, maxValue));
}
return(Any.Int(1, (int)maxValue));
case ColumnType.Decimal:
// generates a random decimal
return(Any.Double(columnInfo.Precision, columnInfo.Scale));
case ColumnType.Double:
// generates a random double
return(Any.Double(columnInfo.Precision, columnInfo.Scale));
case ColumnType.Long:
// generates a random long
return(Any.Long(1, (int)Math.Pow(10, columnInfo.Precision - 1)));
case ColumnType.Boolean:
// generates a random boolean
return(Any.Boolean());
case ColumnType.Guid:
// generates a random guid
return(Guid.NewGuid());
case ColumnType.Date:
// generates a random date
return(Any.DateTime().Date);
case ColumnType.Datetime:
// generates a random DateTime
return(Any.DateTime());
default:
return(null);
}
}
public void TableColumnInfo_ctor_NotApplicableWithNullDefaultValue()
{
// Arrange / Act
_biz = new TableColumnInfo(
_columnName,
_columnDescription,
_columnDataType,
_columnLength,
null,
_allowsNulls,
_keySequence
);
Assert.AreEqual("N/A", _biz.DefaultValue, nameof(_biz.DefaultValue));
}
public void TableColumnInfo_ctor_EmptyStringWithNullColumnDescription()
{
// Arrange / Act
_biz = new TableColumnInfo(
_columnName,
null,
_columnDataType,
_columnLength,
_defaultValue,
_allowsNulls,
_keySequence
);
Assert.AreEqual(string.Empty, _biz.ColumnDescription, nameof(_biz.ColumnDescription));
}
/// <summary>
/// Inserts the generated value into database.
/// </summary>
/// <param name="values">Generated values dictionary.</param>
/// <returns>The dictionary itself.</returns>
private IDictionary <string, object> InsertInDatabase(IDictionary <string, object> values)
{
IDictionary <string, object> insertResult = _dbDialect.Insert(_tableName, _tableColumnInfoList, values);
// Adds the generated record identifier to list
TableColumnInfo identityColumn = _tableColumnInfoList.FirstOrDefault(t => t.IsIdentity) ?? _tableColumnInfoList.First();
_generatedRecords.Push(new RecordIdentifier
{
TableName = _tableName,
ColumnName = identityColumn.Name,
IdentifierValue = insertResult[identityColumn.Name]
});
return(insertResult);
}
/// <summary>
/// Create column row
/// </summary>
/// <param name="worksheet">The worksheet</param>
/// <param name="column">The current DB table column</param>
/// <param name="currentRow">The current row</param>
/// <param name="lastColumn">The last column</param>
private void CreateTableColumnRow(ref IXLWorksheet worksheet, TableColumnInfo column, ref int currentRow, int lastColumn)
{
IXLRange row = CreateRow(ref worksheet, ref currentRow, lastColumn);
row.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
row.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
int currentColumn = 1;
row.Cell(1, currentColumn++).Value = column.ColumnName;
row.Cell(1, currentColumn++).Value = column.ColumnDescription;
row.Cell(1, currentColumn++).Value = column.ColumnDataType;
row.Cell(1, currentColumn++).Value = column.AdditionalInfoFormatted;
row.Cell(1, currentColumn++).Value = column.DefaultValue;
row.Cell(1, currentColumn++).Value = column.AllowsNulls;
row.Cell(1, currentColumn++).Value = column.PartOfKeyFormatted;
}
public void TableColumnInfo_Property_Default()
{
// Arrange / Act
_biz = new TableColumnInfo(
_columnName,
_columnDescription,
_columnDataType,
null,
_defaultValue,
_allowsNulls,
null
);
// Assert
Assert.AreEqual(string.Empty, _biz.AdditionalInfoFormatted, nameof(_biz.AdditionalInfoFormatted));
Assert.AreEqual(false, _biz.PartOfKeyFormatted, nameof(_biz.PartOfKeyFormatted));
}
public override object GetValue(PropertyInfo entityPropertyInfo, TableColumnInfo columnInfo, byte[] message)
{
if (message == null || message.Length == 0)
{
return(null);
}
if (entityPropertyInfo.PropertyType.GetTypeInfo().IsEnum)
{
foreach (var fInfo in entityPropertyInfo.PropertyType.GetFields(BindingFlags.Public | BindingFlags.Static))
{
var underlyingType = Enum.GetUnderlyingType(entityPropertyInfo.PropertyType);
var stringValue = Encoding.Unicode.GetString(message);
var value = Convert.ChangeType(stringValue, underlyingType);
var enumVal = fInfo.GetRawConstantValue();
if (value == enumVal)
{
return(enumVal);
}
}
}
if (entityPropertyInfo.PropertyType == typeof(byte[]))
{
return(message);
}
if (entityPropertyInfo.PropertyType == typeof(bool) || entityPropertyInfo.PropertyType == typeof(bool?))
{
return(Encoding.Unicode.GetString(message).ToBoolean());
}
if (entityPropertyInfo.PropertyType == typeof(char[]))
{
return(Encoding.Unicode.GetString(message).ToCharArray());
}
return(base.GetValue(entityPropertyInfo, columnInfo, message));
}
public void Setup()
{
_columnName = "ColumnName";
_columnDescription = "Column description";
_columnDataType = "varchar";
_columnLength = 255;
_defaultValue = "Default";
_allowsNulls = true;
_keySequence = 1;
_biz = new TableColumnInfo(
_columnName,
_columnDescription,
_columnDataType,
_columnLength,
_defaultValue,
_allowsNulls,
_keySequence
);
_biz.Table = _tableInfo;
_tableInfo = new TableInfo(
"TableName",
"TableDescription",
new DateTime(2000, 1, 1),
new List <TableColumnInfo>()
{
new TableColumnInfo(
_columnName,
_columnDescription,
_columnDataType,
_columnLength,
_defaultValue,
_allowsNulls,
_keySequence
)
}
);
}
/// <summary>
/// Returns table metadata containing column info.
/// </summary>
private static TableInfo GetSectionBuildsTableInfo(
IDictionary <TestClass, int> testCounts,
IList <Build> builds)
{
var buildIdentifierColumns = new List <TableColumnInfo>()
{
new TableColumnInfo("LastName", "Last Name"),
new TableColumnInfo("FirstName", "First Name"),
new TableColumnInfo("BuildSucceeded", "Built?"),
new TableColumnInfo("BuildTime", "Build time")
};
var allTestsColumn = new TableColumnInfo("AllTests", $"All tests ({testCounts.Values.Sum()})");
var testResultColumns = testCounts
.Keys
.OrderBy(testClass => testClass.Order)
.Select
(
testClass => new TableColumnInfo
(
testClass.ClassName.ToAlphaNumeric(),
$"{testClass.DisplayName} ({testCounts[testClass]})"
)
).ToList();
return(new TableInfo
(
buildIdentifierColumns.Concat(new[] { allTestsColumn }).Concat(testResultColumns).ToList(),
new List <TableInfo>()
{
new TableInfo(typeof(TestClassTableEntry), showHeader: true)
},
showHeader: true
));
}
public List <TableColumnInfo> GetTableColumnInfoList(string connectionString, string databaseName, string tableName, string tableId)
{
if (string.IsNullOrEmpty(connectionString))
{
connectionString = ConnectionString;
}
var cacheList = SqlUtils.Cache_GetTableColumnInfoListCache(connectionString, databaseName, tableId);
if (cacheList != null && cacheList.Count > 0)
{
return(cacheList);
}
var list = new List <TableColumnInfo>();
var isIdentityExist = false;
if (WebConfigUtils.IsMySql)
{
string sqlString = $"select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY from information_schema.columns where table_schema = '{databaseName}' and table_name = '{tableName}' order by table_name,ordinal_position; ";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
var isNullable = Convert.ToString(rdr.GetValue(1)) == "YES";
var dataType = EDataTypeUtils.FromMySql(Convert.ToString(rdr.GetValue(2)));
var length = rdr.IsDBNull(3) || dataType == EDataType.NText || dataType == EDataType.Text ? 0 : Convert.ToInt32(rdr.GetValue(3));
var precision = rdr.IsDBNull(4) ? 0 : Convert.ToInt32(rdr.GetValue(4));
var scale = rdr.IsDBNull(5) ? 0 : Convert.ToInt32(rdr.GetValue(5));
var isPrimaryKey = Convert.ToString(rdr.GetValue(6)) == "PRI";
var isIdentity = isPrimaryKey && StringUtils.EqualsIgnoreCase(columnName, "ID");
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
}
else
{
string sqlString =
$"select C.name, T.name, C.length, C.xprec, C.xscale, C.colstat, C.isnullable, case when C.autoval is null then 0 else 1 end, SC.text, (select CForgin.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select O.name from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1), (select Sr.rkeyid from [{databaseName}].dbo.sysreferences Sr,[{databaseName}].dbo.sysobjects O,[{databaseName}].dbo.syscolumns CForgin where Sr.fkeyid={tableId} and Sr.fkey1=C.colid and Sr.rkeyid=O.id and CForgin.id=O.id and CForgin.colid=Sr.rkey1) from [{databaseName}].dbo.systypes T, [{databaseName}].dbo.syscolumns C left join [{databaseName}].dbo.syscomments SC on C.cdefault=SC.id where C.id={tableId} and C.xtype=T.xusertype order by C.colid";
using (var rdr = ExecuteReader(connectionString, sqlString))
{
while (rdr.Read())
{
var columnName = Convert.ToString(rdr.GetValue(0));
if (columnName == "msrepl_tran_version")//sqlserver 发布订阅字段，忽略
{
continue;
}
var dataType = EDataTypeUtils.FromSqlServer(Convert.ToString(rdr.GetValue(1)));
var length = GetDataLength(dataType, Convert.ToInt32(rdr.GetValue(2)));
var precision = Convert.ToInt32(rdr.GetValue(3));
var scale = Convert.ToInt32(rdr.GetValue(4));
var isPrimaryKeyInt = Convert.ToInt32(rdr.GetValue(5));
var isNullableInt = Convert.ToInt32(rdr.GetValue(6));
var isIdentityInt = Convert.ToInt32(rdr.GetValue(7));
var isPrimaryKey = isPrimaryKeyInt == 1;
var isNullable = isNullableInt == 1;
var isIdentity = isIdentityInt == 1 || StringUtils.EqualsIgnoreCase(columnName, "ID");
//sqlserver 2005 返回isIdentity结果不正确,so 在此假设所有ID字段为Idenity字段
if (isIdentity)
{
isIdentityExist = true;
}
var info = new TableColumnInfo(databaseName, tableId, columnName, dataType, length, precision, scale, isPrimaryKey, isNullable, isIdentity);
list.Add(info);
}
rdr.Close();
}
if (!isIdentityExist)
{
var sqlIdentity = "select name from syscolumns where id = object_id(N'" + tableName + "') and COLUMNPROPERTY(id, name,'IsIdentity')= 1";
var clName = "";
using (var rdr = ExecuteReader(sqlIdentity))
{
if (rdr.Read())
{
clName = GetString(rdr, 0);
}
rdr.Close();
}
foreach (var info in list)
{
if (clName == info.ColumnName)
{
info.IsIdentity = true;
}
}
}
}
SqlUtils.Cache_CacheTableColumnInfoList(connectionString, databaseName, tableId, list);
return(list);
}
public virtual object GetValue(PropertyInfo propertyInfo, TableColumnInfo columnInfo, byte[] message)
{
var stringValue = Convert.ToString(this.MessagesBag.Encoding.GetString(message), base.CultureInfo);
return(this.GetValueObject(propertyInfo, stringValue));
}
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);
}
/// <summary>
/// Create rows for missing column descriptions
/// </summary>
/// <param name="sheet">The worksheet</param>
/// <param name="currentRow">The current row</param>
/// <param name="column">The column to print</param>
private void CreateTableRowsForMissingColumnDescriptions(ref IXLWorksheet sheet, ref int currentRow, TableColumnInfo column)
{
if (!string.IsNullOrEmpty(column.ColumnDescription))
{
return;
}
IXLRange row = CreateRow(ref sheet, ref currentRow);
int currentColumn = 1;
row.Cell(1, currentColumn++).Value = column.Table.TableName;
row.Cell(1, currentColumn++).Value = column.ColumnName;
row.Cell(1, currentColumn++).Value = column.ColumnDescription;
row.Cell(1, currentColumn++).Value = string.Empty;
string replacementFormula = "RC[1]";
replacementFormula = @"SUBSTITUTE(" + replacementFormula + @", ""{0}"", RC[-4])"; // Replaces {0} with table name
replacementFormula = @"SUBSTITUTE(" + replacementFormula + @", ""{1}"", RC[-3])"; // Replaces {1} with columns name
replacementFormula = "SUBSTITUTE(" + replacementFormula + @", ""{2}"", SUBSTITUTE(RC[-2], ""'"", ""''""))"; // Replaces {2} with description, also replaces "'" in description with "''" for SQL single quote escape
row.Cell(1, currentColumn++).FormulaR1C1 = @"=IF(RC[-2] = """", ""Enter a Column description (Column C)"", " + replacementFormula + ")";
int sqlScriptColumn = currentColumn++;
row.Cell(1, sqlScriptColumn).Value = Common.SqlQuery._SCRIPT_TEMPLATE_FOR_TABLE_COLUMN;
row.Cell(1, sqlScriptColumn).Style.Font.SetFontColor(XLColor.White);
}
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 <List <TableColumnInfo> > GetColumnInfo(string connectionName, string tableName)
{
var columnInfoKey = connectionName + ":" + tableName;
if (ConnectionColumnInfo.ContainsKey(columnInfoKey))
{
return(ConnectionColumnInfo[columnInfoKey]);
}
var customConnection = await _context.CustomConnection.SingleOrDefaultAsync(x => x.Name == connectionName);
var connectionString = Util.GetConnectionString(customConnection);
var columnList = new List <TableColumnInfo>();
const string columnListSql = "select COLUMN_NAME, DATA_TYPE from user_tab_columns where TABLE_NAME = :tablename order by column_id";
using (var oconn = new OracleConnection(connectionString))
{
oconn.Open();
var primaryColumnList = new List <string>();
var foreignKeyColumnDict = new Dictionary <string, ForeignKeyInfo>();
var primaryForeignColumns = "SELECT ac.table_name, column_name, position, ac.constraint_name, constraint_type, " +
"(SELECT ac2.table_name FROM all_constraints ac2 WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME) fK_to_table, " +
"(SELECT ac3.column_name FROM all_cons_columns ac3 WHERE AC3.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME) fK_to_table_column " +
"FROM all_cons_columns acc, all_constraints ac " +
"WHERE acc.constraint_name = ac.constraint_name " +
"AND acc.table_name = ac.table_name " +
"AND CONSTRAINT_TYPE IN('P', 'R') " +
"AND ac.table_name = '" + tableName + "' " +
"ORDER BY table_name, constraint_type, position";
using (var cmdPrimaryForeignColumns = new OracleCommand
{
Connection = oconn,
CommandText = primaryForeignColumns,
CommandType = CommandType.Text
})
{
var drPrimForeignColumns = cmdPrimaryForeignColumns.ExecuteReader();
while (drPrimForeignColumns.Read())
{
var constraintType = drPrimForeignColumns.GetString(4);
if (constraintType == "P")
{
primaryColumnList.Add(drPrimForeignColumns.GetString(1));
}
else
{
var name = drPrimForeignColumns.GetString(1);
foreignKeyColumnDict[name] = new ForeignKeyInfo
{
Name = drPrimForeignColumns.GetString(1),
ForeignKeyTable = drPrimForeignColumns.GetString(5),
ForeignKeyColumn = drPrimForeignColumns.GetString(6)
};
}
}
using (var cmd = new OracleCommand
{
Connection = oconn,
CommandText = columnListSql,
CommandType = CommandType.Text
})
{
cmd.Parameters.Add("tablename", tableName);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var columnName = dr.GetString(0);
var isPrimary = false;
var isForeignKey = false;
if (primaryColumnList.Contains(columnName))
{
isPrimary = true;
}
var tableColumnInfo = new TableColumnInfo
{
Name = dr.GetString(0),
DataType = dr.GetString(1),
IsPrimaryKey = isPrimary,
IsForeignKey = isForeignKey
};
if (foreignKeyColumnDict.ContainsKey(columnName))
{
isForeignKey = true;
var foreignKeyInfo = foreignKeyColumnDict[columnName];
tableColumnInfo.IsForeignKey = true;
tableColumnInfo.ForeignTable = foreignKeyInfo.ForeignKeyTable;
tableColumnInfo.ForeignTableKeyColumn = foreignKeyInfo.ForeignKeyColumn;
}
columnList.Add(tableColumnInfo);
}
}
}
}
ConnectionColumnInfo[columnInfoKey] = columnList;
return(columnList);
}
public override TableColumnInfo[] GetTableSchemaInfo(string tableName)
{ //MaxLength is cut to half when the column is a N type. Those types use double space.
string sql = @"
SELECT
OUTCOLUMNS.NAME Name,
TYPES.NAME ColumnType,
FLOOR(OUTCOLUMNS.[MAX_LENGTH] *
CASE WHEN TYPES.NAME IN ('nvarchar','nchar','ntext')
then .5
else 1 end) [MaxLength],
OUTCOLUMNS.[PRECISION] [Precision],
OUTCOLUMNS.SCALE Scale,
OUTCOLUMNS.IS_NULLABLE IsNullable,
OUTCOLUMNS.IS_IDENTITY IsIdentity,
IIF(REFERENCED_COLUMN_NAME IS NULL, 0, 1) IsForeignKey,
CONCAT(REFERENCED_SCHEMA, '.', REFERENCED_TABLE_NAME) ForeignKeyTable,
REFERENCED_COLUMN_NAME ForeignKeyColumn,
IS_UNIQUE IsUnique
FROM SYS.COLUMNS OUTCOLUMNS
INNER JOIN SYS.TYPES ON OUTCOLUMNS.user_type_id = TYPES.user_type_id
outer apply(
SELECT DISTINCT tables.schemaName FK_SCHEMA,
tables.name FK_TABLE_NAME,
fkc.name FK_COLUMN_NAME,
rpk.schemaname REFERENCED_SCHEMA,
rpk.table_name REFERENCED_TABLE_NAME,
rpk.name REFERENCED_COLUMN_NAME
FROM sys.foreign_keys
CROSS apply
(SELECT INCOLUMNS.name, referenced_column_id, referenced_object_id
FROM sys.foreign_key_columns
INNER JOIN sys.columns INCOLUMNS ON INCOLUMNS.column_id = foreign_key_columns.parent_column_id AND INCOLUMNS.[object_id] = foreign_key_columns.parent_object_id and OUTCOLUMNS.column_id = INCOLUMNS.column_id
WHERE foreign_key_columns.constraint_object_id = foreign_keys.[object_id]) fkc
CROSS apply
(SELECT schema_name(tables.schema_id) schemaname, object_name(tables.object_id) TABLE_NAME, columns.name
FROM sys.tables
INNER JOIN sys.columns ON tables.object_id = columns.object_id
AND columns.column_id = referenced_column_id
WHERE tables.object_id = fkc.referenced_object_id) rpk
CROSS apply
(SELECT schema_name(tables.schema_id) schemaname, name
FROM sys.tables
WHERE tables.object_id = foreign_keys.parent_object_id) tables
WHERE foreign_keys.parent_object_id = OUTCOLUMNS.object_id
) outerapply
OUTER APPLY(
SELECT CAST(
CASE WHEN EXISTS (
SELECT * from sys.key_constraints C
WHERE PARENT_OBJECT_ID in (OBJECT_ID(@tableName))
AND unique_index_id = OUTCOLUMNS.column_id) THEN 1
ELSE 0
END
AS BIT) AS IS_UNIQUE
) PK
WHERE OBJECT_ID in (OBJECT_ID(@tableName))
AND OUTCOLUMNS.IS_COMPUTED = 0
ORDER BY OUTCOLUMNS.column_id
";
var informationSchemaCmd = new SqlCommand(sql);
informationSchemaCmd.Parameters.Add(new SqlParameter
{
ParameterName = "@tableName",
Value = tableName
});
informationSchemaCmd.Connection = _sqlConnection;
var dr = informationSchemaCmd.ExecuteReader();
var tableColumns = new List <TableColumnInfo>();
if (dr.HasRows)
{
while (dr.Read())
{
var tableColumnInfo = new TableColumnInfo();
tableColumnInfo.Name = (string)dr["Name"];
tableColumnInfo.Precision = Convert.ToInt32(dr["Precision"]);
tableColumnInfo.Scale = Convert.ToInt32(dr["Scale"]);
tableColumnInfo.MaxLength = Convert.ToInt32(dr["MaxLength"]);
tableColumnInfo.IsIdentity = Convert.ToBoolean(dr["IsIdentity"]);
tableColumnInfo.IsNullable = Convert.ToBoolean(dr["IsNullable"]);
tableColumnInfo.IsForeignKey = Convert.ToBoolean(dr["IsForeignKey"]);
tableColumnInfo.ForeignKeyTable = (string)dr["ForeignKeyTable"];
tableColumnInfo.ForeignKeyColumn = dr["ForeignKeyColumn"] is DBNull ? null : (string)dr["ForeignKeyColumn"];
tableColumnInfo.IsUnique = Convert.ToBoolean(dr["IsUnique"]);
switch (((string)dr["ColumnType"]).ToUpper())
{
case "REAL":
case "SMALLMONEY":
case "MONEY":
case "NUMERIC":
case "DECIMAL":
tableColumnInfo.ColumnType = ColumnType.Decimal;
break;
case "CHAR":
case "NCHAR":
case "NVARCHAR":
case "TEXT":
case "NTEXT":
case "VARCHAR":
tableColumnInfo.ColumnType = ColumnType.String;
break;
case "ROWVERSION":
case "FILESTREAM":
case "VARBINARY":
case "IMAGE":
tableColumnInfo.ColumnType = ColumnType.Byte;
break;
case "FLOAT":
tableColumnInfo.ColumnType = ColumnType.Double;
break;
case "BINARY":
tableColumnInfo.ColumnType = ColumnType.Binary;
break;
case "BIT":
tableColumnInfo.ColumnType = ColumnType.Boolean;
break;
case "TINYINT":
case "INT":
case "SMALLINT":
tableColumnInfo.ColumnType = ColumnType.Int;
break;
case "BIGINT":
tableColumnInfo.ColumnType = ColumnType.Long;
break;
case "UNIQUEIDENTIFIER":
tableColumnInfo.ColumnType = ColumnType.Guid;
break;;
case "DATE":
tableColumnInfo.ColumnType = ColumnType.Date;
break;
case "TIME":
case "SMALLDATETIME":
case "DATETIMEOFFSET":
case "DATETIME2":
case "DATETIME":
tableColumnInfo.ColumnType = ColumnType.Datetime;
break;
default:
tableColumnInfo.ColumnType = ColumnType.Unsuported;
break;
}
tableColumns.Add(tableColumnInfo);
}
dr.Dispose();
return(tableColumns.ToArray());
}
throw new Exception("Table not found!");
}
/// <summary>
/// Gets the filtered operations.
/// </summary>
/// <param name="operations">The operations.</param>
/// <param name="skippedOperationComments">The skipped operation comments.</param>
/// <returns></returns>
private List <MigrationOperation> GetFilteredOperations(IEnumerable <System.Data.Entity.Migrations.Model.MigrationOperation> operations, out List <string> skippedOperationComments)
{
List <MigrationOperation> includedOperations = new List <System.Data.Entity.Migrations.Model.MigrationOperation>();
skippedOperationComments = new List <string>();
// make comments on the skipped Down() operations and add to includedOperations ones that are not skipped
skippedOperationComments.Add(string.Empty);
skippedOperationComments.Add("// Up()...");
foreach (MigrationOperation operation in operations)
{
TableColumnInfo tableColumnInfo = GetOperationTableColumnInfo(operation);
if (!string.IsNullOrWhiteSpace(tableColumnInfo.TableName) && !dbContextEntities.ContainsKey(tableColumnInfo.TableName))
{
//// not found if Migration is trying to gen code for a table in another DbContext of a Multiple Context project
//// probably not found if this is the Down() migration
string columnInfo = string.Empty;
if (!string.IsNullOrWhiteSpace(tableColumnInfo.ColumnName))
{
columnInfo = ", column " + tableColumnInfo.ColumnName;
}
skippedOperationComments.Add(string.Format("// {0} for TableName {1}{2}.", operation.GetType().Name, tableColumnInfo.TableName, columnInfo));
}
else
{
includedOperations.Add(operation);
}
}
// make comments on the skipped Down() operations
skippedOperationComments.Add(string.Empty);
skippedOperationComments.Add("// Down()...");
foreach (MigrationOperation operation in operations.Select(a => a.Inverse).Where(a => a != null).Reverse())
{
TableColumnInfo tableColumnInfo = GetOperationTableColumnInfo(operation);
if (!string.IsNullOrWhiteSpace(tableColumnInfo.TableName) && !dbContextEntities.ContainsKey(tableColumnInfo.TableName))
{
//// not found if Migration is trying to gen code for a table in another DbContext of a Multiple Context project
//// probably not found if this is the Down() migration
string columnInfo = string.Empty;
if (!string.IsNullOrWhiteSpace(tableColumnInfo.ColumnName))
{
columnInfo = ", column " + tableColumnInfo.ColumnName;
}
skippedOperationComments.Add(string.Format("// {0} for TableName {1}{2}.", operation.GetType().Name, tableColumnInfo.TableName, columnInfo));
}
}
if (skippedOperationComments.Count == 4)
{
// if there are exactly four comments, nothing was skipped
skippedOperationComments.Clear();
}
return(includedOperations);
}
