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