Skip to content

Instantly share code, notes, and snippets.

@hoangitk
Forked from NickCraver/ExampleUsage.cs
Created November 27, 2021 04:24
Show Gist options
  • Save hoangitk/0d0d54951352badfeb0c3092a13ded52 to your computer and use it in GitHub Desktop.
Save hoangitk/0d0d54951352badfeb0c3092a13ded52 to your computer and use it in GitHub Desktop.
Code to mark a SQL string before it's passed to Dapper.
public static List<T> Query<T>(this DataContext db, string sql, object param = null, int? commandTimeout = null, IDbTransaction transaction = null, [CallerFilePath]string fromFile = null, [CallerLineNumber]int onLine = 0, string comment = null)
{
using (db.Connection.EnsureOpen())
{
try
{
return db.Connection.Query<T>(MarkSqlString(sql, fromFile, onLine, comment), param, transaction ?? db.Transaction, true, commandTimeout).AsDapperList();
}
catch (SqlException ex) when (ex.Is(SqlErrorCode.DatabaseReadOnly_3906))
{
HandleReadOnlyException(db, ex);
return EmptyDapperList<T>();
}
}
}
private static readonly ConcurrentDictionary<MarkedSqlStringKey, string> MarkedSql = new ConcurrentDictionary<MarkedSqlStringKey, string>();
private struct MarkedSqlStringKey : IEquatable<MarkedSqlStringKey>
{
public string Sql { get; }
public string Path { get; }
public int LineNumber { get; }
public string Comment { get; }
public MarkedSqlStringKey(string sql, string path, int lineNumber, string comment)
{
Sql = sql;
Path = path;
LineNumber = lineNumber;
Comment = comment;
}
public bool Equals(MarkedSqlStringKey other)
{
return
string.Equals(Sql, other.Sql) &&
string.Equals(Path, other.Path) &&
LineNumber == other.LineNumber &&
string.Equals(Comment, other.Comment);
}
public override bool Equals(object obj)
{
if (ReferenceEquals(null, obj)) return false;
return obj is MarkedSqlStringKey && Equals((MarkedSqlStringKey) obj);
}
public override int GetHashCode()
{
unchecked
{
var hashCode = Sql?.GetHashCode() ?? 0;
hashCode = (hashCode*397) ^ (Path?.GetHashCode() ?? 0);
hashCode = (hashCode*397) ^ LineNumber;
hashCode = (hashCode*397) ^ (Comment?.GetHashCode() ?? 0);
return hashCode;
}
}
}
/// <summary>
/// Takes a SQL query, and inserts the path and line in as a comment.
/// </summary>
private static string MarkSqlString(string sql, string path, int lineNumber, string comment)
{
if (path.IsNullOrEmpty() || lineNumber == 0) return sql;
var key = new MarkedSqlStringKey(sql, path, lineNumber, comment);
// Have we seen this before???
string output;
if (MarkedSql.TryGetValue(key, out output)) return output;
// nope
var commentWrap = " ";
var i = sql.IndexOf(Environment.NewLine);
// if we didn't find \n, or it was the very end, go to the first space method
if (i < 0 || i == sql.Length - 1)
{
i = sql.IndexOf(' ');
commentWrap = Environment.NewLine;
}
if (i < 0) return sql;
// Grab one directory and the file name worth of the path
// this dodges problems with the build server using temp dirs
// but also gives us enough info to uniquely identify a queries location
var split = path.LastIndexOf('\\') - 1;
if (split < 0) return sql;
split = path.LastIndexOf('\\', split);
if (split < 0) return sql;
split++; // just for Craver
var sqlComment = " /* " + path.Substring(split) + "@" + lineNumber.ToString() + (comment.HasValue() ? " - " + comment : "") + " */" + commentWrap;
var ret = sql.Substring(0, i) + sqlComment + sql.Substring(i);
// Cache, don't allocate all this pass again
MarkedSql[key] = ret;
return ret;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment