public static DataRow DecodeRow(DataRow sourceRow, DataTable destTable)
{
var resultRow = destTable.NewRow();
var fkDict = TableKeys.GetTableKeys(destTable.TableName, TableKeys.KeyType.ForeignKey);
foreach (DataColumn column in sourceRow.Table.Columns)
{
if (destTable.Columns.Contains(column.ColumnName))
{
var value = sourceRow[column];
if (value is string && string.IsNullOrWhiteSpace((string)value))
{
value = DBNull.Value;
}
if (value != null && value != DBNull.Value)
{
if (fkDict.Contains(column.ColumnName))
{
value = GetKeyByValue(destTable.TableName, column.ColumnName, value);
}
else
{
value = Convert.ChangeType(value, destTable.Columns[column.ColumnName].DataType);
}
}
resultRow[column.ColumnName] = value;
}
}
return(resultRow);
}
public static DataRow CreateRow(string tableName)
{
var row = GetTable(tableName).NewRow();
row[TableKeys.GetPrimaryKey(tableName)] = GetNewId(tableName);
return(row);
}
public static string GetValueByKey(string tableName, string columnName, object key, bool extension = false)
{
string value = string.Empty;
var relation = TableKeys.GetColumnRelation(tableName, columnName, key);
if (string.IsNullOrEmpty(relation.columnName) == false)
{
var cacheDict = GetValuesCache();
var cacheKey = CreateCacheKey(tableName, columnName, key, true, extension);
if (cacheDict.ContainsKey(cacheKey))
{
value = cacheDict[cacheKey];
}
else
{
var table = TableManager.GetTable(relation.tableName);
string valueColName = GetValueColumnName(table, relation.columnName);
try {
if (extension)
{
value = SelectValueByKey(table, relation.columnName, key, valueColName);
}
else
{
value = TableManager.SelectScalarFromTable(table, relation.columnName, key, valueColName).ToString();
}
cacheDict.Add(cacheKey, value.ToString());
} catch {
//TODO вывод в лог
}
}
}
return(value);
}
private static void SetNewId(string tableName, DataRow currentRow)
{
string aiColumn = null;
var aiDict = TableKeys.GetTableKeys(tableName, TableKeys.KeyType.AutoIncrement);
if (aiDict.Count != 0)
{
aiColumn = aiDict[0];
}
if (aiColumn != null)
{
var newId = TableManager.GetMaxValue(tableName, aiColumn);
currentRow[aiColumn] = newId;
}
}
private static void SetTablePrimaryKey(DataTable table)
{
var pkList = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);
var pkColumns = new DataColumn[pkList.Count];
for (int i = 0; i < pkList.Count; i++)
{
var colName = pkList[i];
if (table.Columns.Contains(colName))
{
pkColumns[i] = table.Columns[colName];
}
}
table.PrimaryKey = pkColumns;
}
public static string CreateWhereString(DataRow row)
{
var query = new StringBuilder();
var sourceTable = row.Table;
var pkList = TableKeys.GetTableKeys(sourceTable.TableName, TableKeys.KeyType.PrimaryKey);
for (int i = 0; i < pkList.Count; i++)
{
var column = pkList[i];
query.AppendFormat("[{0}] = '{1}'", column, row[column]);
if (i != pkList.Count - 1)
{
query.Append(" and ");
}
}
return(query.ToString());
}
public static string CreateWhereString(DataTable table, object[] values)
{
var query = new StringBuilder();
var pkList = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);
if (values.Length == pkList.Count)
{
for (int i = 0; i < pkList.Count; i++)
{
var column = pkList[i];
query.AppendFormat("[{0}] = '{1}'", column, values[i]);
if (i != pkList.Count - 1)
{
query.Append(" and ");
}
}
}
return(query.ToString());
}
private static FbCommand CreateUpdateCommand(DataRow oldRow, DataRow newRow)
{
var table = oldRow.Table;
var pkList = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.PrimaryKey);
var query = new StringBuilder();
var command = new FbCommand();
int paramIndex = 1;
query.AppendFormat("update {0} set", table.TableName);
foreach (DataColumn column in table.Columns)
{
if (!oldRow[column].Equals(newRow[column.ColumnName])) //если значение поменялось
{
var parameter = CreateParameter(column, newRow[column.ColumnName], paramIndex);
query.AppendFormat(" {0} = {1},", column.ColumnName, parameter.ParameterName);
command.Parameters.Add(parameter);
paramIndex++;
}
}
if (command.Parameters.Count != 0)
{
query.Remove(query.Length - 1, 1);
query.Append(" where ");
for (int i = 0; i < pkList.Count; i++)
{
var column = table.Columns[pkList[i]];
var parameter = CreateParameter(column, oldRow[column], paramIndex);
paramIndex++;
command.Parameters.Add(parameter);
query.AppendFormat("{0} = {1}", column.ColumnName, parameter.ParameterName);
if (i != pkList.Count - 1)
{
query.Append(" and ");
}
}
command.CommandText = query.ToString();
}
return(command);
}
/// <param name="tableName">Текущая таблица</param>
/// <param name="colName">Название текущей колонки</param>
public static object GetKeyByValue(string tableName, string colName, object value)
{
object result = value;
var relation = TableKeys.GetColumnRelation(tableName, colName);
if (string.IsNullOrWhiteSpace(relation.columnName) == false)
{
int key;
if (value is int)//если пришел уже ключ
{
result = value;
}
else if (int.TryParse(value.ToString(), out key))//пробуем парсить ключ
{
result = key;
}
else
{
var cacheDict = GetKeysCache();
var cacheKey = CreateCacheKey(tableName, colName, value, false, false);
if (cacheDict.ContainsKey(cacheKey))//если закешировано
{
result = cacheDict[cacheKey];
}
else
{
var relationTable = TableManager.GetTable(relation.tableName);
var valueColumnName = GetValueColumnName(relationTable, relation.columnName);
object selectObj = SelectKeyByValue(relationTable, relation.columnName, value, valueColumnName);
if (selectObj != DBNull.Value)
{
result = selectObj;
cacheDict.Add(cacheKey, result);
}
}
}
}
return(result);
}
private static FbCommand CreateInsertCommand(DataRow newRow)
{
var table = newRow.Table;
var nnList = TableKeys.GetTableKeys(table.TableName, TableKeys.KeyType.NotNull);
var query = new StringBuilder();
var command = new FbCommand();
int paramIndex = 1;
query.AppendFormat("insert into {0} (", table.TableName);
foreach (DataColumn column in table.Columns)
{
var value = newRow[column];
if (value == DBNull.Value && nnList.Contains(column.ColumnName)) //если недопустимое нулевое поле
//TODO error
{
}
else
{
var parameter = CreateParameter(column, value, paramIndex);
query.AppendFormat(" {0},", column.ColumnName);
command.Parameters.Add(parameter);
paramIndex++;
}
}
if (command.Parameters.Count != 0)
{
query.Remove(query.Length - 1, 1);
query.Append(" ) values ( ");
foreach (FbParameter parameter in command.Parameters)
{
query.AppendFormat("{0},", parameter.ParameterName);
}
query.Remove(query.Length - 1, 1);
query.Append(")");
command.CommandText = query.ToString();
}
return(command);
}
public static SortedDictionary <string, int> GetRelativeItemsSource(string tableName, string columnName, bool addNullItem)
{
var relation = TableKeys.GetColumnRelation(tableName, columnName);
var result = new SortedDictionary <string, int>();
if (string.IsNullOrEmpty(relation.tableName) == false)
{
var table = TableManager.GetTable(relation.tableName);
string valueColName = GetValueColumnName(table, relation.columnName);
DataRow[] rows = table.Select();
foreach (DataRow row in rows)
{
var key = (int)row[relation.columnName];
var value = GetRowStringView(row, relation.columnName, valueColName);
result.Add(value, key);
}
}
if (addNullItem)
{
result.Add("", -1);
}
return(result);
}
public static SortedDictionary <string, int> GetTableKeyItems(string tableName, string columnName, bool addNullItem = true)
{
DataTable tab = TableManager.GetTable(tableName);
bool isFk = TableKeys.CheckColumnKeyType(tableName, columnName, TableKeys.KeyType.ForeignKey);
var result = new SortedDictionary <string, int>();
if (isFk)
{
var keysList = GetUniqueSet(tab, columnName);
foreach (var key in keysList)
{
var value = GetValueByKey(tableName, columnName, key, true);
if (result.ContainsKey(value) == false)
{
result.Add(value, (int)key);
}
}
if (result.ContainsKey("") == false && addNullItem)
{
result.Add("", -1);
}
}
return(result);
}
public static SortedSet <string> GetTableItems(string tableName, string columnName)
{
DataTable tab = TableManager.GetTable(tableName);
bool isFk = TableKeys.CheckColumnKeyType(tableName, columnName, TableKeys.KeyType.ForeignKey);
var keysList = GetUniqueSet(tab, columnName);
var valuesList = new SortedSet <string>();
foreach (var value in keysList)
{
if (isFk)
{
valuesList.Add(GetValueByKey(tableName, columnName, value, true));
}
else
{
valuesList.Add(value.ToString());
}
}
if (valuesList.Contains("") == false)
{
valuesList.Add("");
}
return(valuesList);
}