Skip to content

Instantly share code, notes, and snippets.

@atifaziz
Created July 24, 2015 15:59
Show Gist options
  • Save atifaziz/0ff6d42d68aeb89ff5d5 to your computer and use it in GitHub Desktop.
Save atifaziz/0ff6d42d68aeb89ff5d5 to your computer and use it in GitHub Desktop.
Fun with String Interpolation in C# 6 to do SQL literal encoding
using System;
using System.Linq;
using static System.Console;
using static System.Globalization.CultureInfo;
static class Program
{
static void Main()
{
var count = 5;
var name = "%foobar%";
var schema = "dbo";
var date = DateTime.Today;
var days = 10;
var mine = true;
FormattableString sql = $@"
SELECT * FROM sys.objects
WHERE is_ms_shipped = {!mine}
AND schema_id = SCHEMA_ID({schema})
AND name LIKE {name}
AND modify_date >= DATEADD(dd, {-days}, {date})
";
WriteLine("BEFORE ".PadRight(BufferWidth, '-'));
WriteLine(sql.Format);
var args =
from arg in sql.GetArguments()
select ( arg as string )?.Replace("'", "''").Wrap("'")
?? ( arg as DateTime?)?.ToString("yyyy-MM-dd HH:mm:ss", InvariantCulture).Wrap("'")
?? ( arg as int? )?.ToString(InvariantCulture)
?? ( arg as long? )?.ToString(InvariantCulture)
?? ( arg as float? )?.ToString(InvariantCulture)
?? ( arg as double? )?.ToString(InvariantCulture)
?? ( arg as decimal? )?.ToString(InvariantCulture)
?? ((arg as bool? ) == true ? "1" : null)
?? ((arg as bool? ) == false ? "0" : null)
?? $"/* UNKNOWN: {arg?.GetType()} */";
WriteLine("AFTER ".PadRight(BufferWidth, '-'));
WriteLine(sql.Format, args.ToArray());
}
}
static class Extensions
{
public static string Wrap(this string s, string wrapper) => s.Wrap(wrapper, wrapper);
public static string Wrap(this string s, string left, string right) => left + s + right;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment