public static SqlCommand CreateSelectCommand
(
[NotNull] this SqlConnection connection,
[NotNull] SqlFourPartName tableName,
[NotNull] string name,
[CanBeNull] IImmutableDictionary <SqlServerColumn, SoftString> columnMappings,
[CanBeNull] IImmutableDictionary <string, object> @where)
{
where = where ?? ImmutableDictionary <string, object> .Empty;
var sql = new StringBuilder();
var table = tableName.Render(connection);
sql.Append($"SELECT *").AppendLine();
sql.Append($"FROM {table}").AppendLine();
sql.Append(where.Aggregate(
$"WHERE [{columnMappings.MapOrDefault(SqlServerColumn.Name)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Name)}",
(current, next) => $"{current} AND {connection.CreateIdentifier(next.Key)} = @{next.Key}")
);
var command = connection.CreateCommand();
command.CommandText = sql.ToString();
// --- add parameters & values
command.AddParameters(where.Add(columnMappings.MapOrDefault(SqlServerColumn.Name), name));
return(command);
}
public void ToString_ServerOmitted_Identifier()
{
var fourPartName = new SqlFourPartName(null, "bar", "baz", "qux");
var identifier = SqlHelper.Execute("name=TestDb", fourPartName.Render);
Assert.AreEqual("[bar].[baz].[qux]", identifier);
}
public void ToString_ServerAndDatabaseAndSchemaOmitted_Identifier()
{
var fourPartName = new SqlFourPartName(null, null, null, "qux");
var identifier = SqlHelper.Execute("name=TestDb", fourPartName.Render);
Assert.AreEqual("[qux]", identifier);
}
public void ToString_DatabaseOmitted_Identifier()
{
var fourPartName = new SqlFourPartName("foo", null, "baz", "qux");
var identifier = SqlHelper.Execute("name=TestDb", fourPartName.Render);
Assert.AreEqual("[foo]..[baz].[qux]", identifier);
}
////public static SqlCommand CreateDeleteCommand(SqlConnection connection, IIdentifier id, IImmutableDictionary<string, object> where)
////{
//// /*
//// DELETE FROM [dbo].[Setting] WHERE [Name] LIKE 'baz%' AND [Environment] = 'boz'
//// */
//// var sql = new StringBuilder();
//// var dbProviderFactory = DbProviderFactories.GetFactory(connection);
//// using (var commandBuilder = dbProviderFactory.CreateCommandBuilder())
//// {
//// string Sanitize(string identifier) => commandBuilder.QuoteIdentifier(identifier);
//// var table = $"{Sanitize(_tableMetadata.SchemaName)}.{Sanitize(_tableMetadata.TableName)}";
//// sql.Append($"DELETE FROM {table}").AppendLine();
//// sql.Append(where.Keys.Aggregate(
//// $"WHERE ([{EntityProperty.Name}] = @{EntityProperty.Name} OR [{EntityProperty.Name}] LIKE @{EntityProperty.Name} + N'[[]%]')",
//// (result, next) => $"{result} AND {Sanitize(next)} = @{next} ")
//// );
//// }
//// var command = connection.CreateCommand();
//// command.CommandType = CommandType.Text;
//// command.CommandText = sql.ToString();
//// // --- add parameters & values
//// (command, _tableMetadata).AddParameter(
//// ImmutableDictionary<string, object>.Empty
//// .Add(EntityProperty.Name, id.ToString())
//// .AddRange(where)
//// );
//// return command;
////}
public static SqlCommand CreateUpdateCommand
(
[NotNull] this SqlConnection connection,
[NotNull] SqlFourPartName tableName,
[NotNull] string name,
[CanBeNull] IImmutableDictionary <SqlServerColumn, SoftString> columnMappings,
[CanBeNull] IImmutableDictionary <string, object> @where,
[CanBeNull] object value)
{
where = where ?? ImmutableDictionary <string, object> .Empty;
/*
*
* UPDATE [Setting]
* SET [Value] = 'Hallo update!'
* WHERE [Name]='baz' AND [Environment] = 'boz'
* IF @@ROWCOUNT = 0
* INSERT INTO [Setting]([Name], [Value], [Environment])
* VALUES ('baz', 'Hallo insert!', 'boz')
*
*/
var sql = new StringBuilder();
var table = tableName.Render(connection);
sql.Append($"UPDATE {table}").AppendLine();
sql.Append($"SET [{columnMappings.MapOrDefault(SqlServerColumn.Value)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Value)}").AppendLine();
sql.Append(where.Aggregate(
$"WHERE [{columnMappings.MapOrDefault(SqlServerColumn.Name)}] = @{columnMappings.MapOrDefault(SqlServerColumn.Name)}",
(result, next) => $"{result} AND {connection.CreateIdentifier(next.Key)} = @{next.Key} ")
).AppendLine();
sql.Append($"IF @@ROWCOUNT = 0").AppendLine();
var columns = where.Keys.Select(key => connection.CreateIdentifier(key)).Aggregate(
$"[{columnMappings.MapOrDefault(SqlServerColumn.Name)}], [{columnMappings.MapOrDefault(SqlServerColumn.Value)}]",
(result, next) => $"{result}, {next}"
);
sql.Append($"INSERT INTO {table}({columns})").AppendLine();
var parameterNames = where.Keys.Aggregate(
$"@{columnMappings.MapOrDefault(SqlServerColumn.Name)}, @{columnMappings.MapOrDefault(SqlServerColumn.Value)}",
(result, next) => $"{result}, @{next}"
);
sql.Append($"VALUES ({parameterNames})");
var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql.ToString();
// --- add parameters
command.Parameters.AddWithValue($"@{columnMappings.MapOrDefault(SqlServerColumn.Name)}", name);
command.Parameters.AddWithValue($"@{columnMappings.MapOrDefault(SqlServerColumn.Value)}", value);
//command.Parameters.Add($"@{sqlServer.ColumnMapping.Value}", SqlDbType.NVarChar, 200).Value = setting.Value;
command.AddParameters(where);
return(command);
}
