public static ISimpleDataOrder RecordCopyQuery(string tableName, string primaryKeyFieldName, Clause tempTableClause, Tuple <string, string>[] fieldParameterNameTupleCollection, string[] fieldsToCopy)
{
string uniqueAppendage = SafeGuid();
string sourceAlias = "source";
string query = $"MERGE {tableName}\r\nUSING ("
+ $"\r\nSELECT{string.Join(",", fieldsToCopy.Concat(new[] { primaryKeyFieldName }).Distinct().Select(field => $" \ r \ n {
field
} "))}\r\nFROM {tableName}";
SimpleDataOrder firstDataOrder =
tempTableClause
.Build();
if (string.IsNullOrEmpty(firstDataOrder.Query) == false)
{
query += "\r\nWHERE" + firstDataOrder.Query;
}
query += $"\r\n) AS {sourceAlias}\r\nON (1 = 0)\r\nWHEN NOT MATCHED THEN";
IEnumerable <Tuple <string, string> > insertValues = fieldsToCopy.Select(field => Tuple.Create(field, $"{sourceAlias}.{field}")).Concat(fieldParameterNameTupleCollection);
query += $"\r\nINSERT ({string.Join(",", insertValues.Select(tuple => $"\r\n {tuple.Item1}"))}\r\n)\r\nVALUES (\r\n";
query += string.Join(",", insertValues.Select(tuple => $"\r\n {tuple.Item2}"));
query += $"\r\n)\r\nOUTPUT {sourceAlias}.{primaryKeyFieldName} AS OldId, INSERTED.{primaryKeyFieldName} AS NewId;";
return(new SimpleDataOrder(query, firstDataOrder.Parameters));
}
private static IAliasedDataOrder CreateQuery(string tableName,
IEnumerable <KeyValuePair <string, object> > columns,
Clause whereCondition,
IEnumerable <string> outputParameters,
Func <IEnumerable <KeyValuePair <string, string> >, string, string> queryProducer,
IEnumerable <Func <string, IEnumerable <string>, IAliasedDataOrder> > outputClauseFunctions
)
{
var columnArray = columns.ToArray();
var columnsWithParameterNames = new List <KeyValuePair <string, string> >();
var parameterNamesWithValue = new List <KeyValuePair <string, object> >();
for (int i = 0; i < columnArray.Length; i++)
{
KeyValuePair <string, object> pair = columnArray[i];
string parameterName = Clause.GenerateParameterName();
columnsWithParameterNames.Add(new KeyValuePair <string, string>(pair.Key, parameterName));
parameterNamesWithValue.Add(new KeyValuePair <string, object>(parameterName, pair.Value));
}
string outputClause = "";
List <IAliasedDataOrder> outputDataOrders = new List <IAliasedDataOrder>();
if (outputParameters.Any())
{
outputDataOrders.AddRange(outputClauseFunctions.Select(f => f(tableName, outputParameters)));
if (outputDataOrders.Any())
{
outputClause = "\r\nOUTPUT"
+ $"\r\n{string.Join(",\r\n", outputDataOrders.Select(dO => dO.Query))}";
}
}
ISimpleDataOrder whereClause = whereCondition.Build();
var parameters =
Get.List <KeyValuePair <string, object> >(list => {
list.AddRange(parameterNamesWithValue);
list.AddRange(whereClause.Parameters);
});
string query = queryProducer(columnsWithParameterNames, outputClause);
if (string.IsNullOrEmpty(whereClause.Query) == false)
{
query += "\r\nWHERE" + whereClause.Query;
}
return(new AliasedDataOrder(GenericQueryProvider.SurroundQueryWithTransaction(query), parameters, outputDataOrders.SelectMany(dO => dO.Aliases)));
}
