Created
July 4, 2024 14:45
-
-
Save jpolvora/539439a5cd77b44f50f85c5106ed4a95 to your computer and use it in GitHub Desktop.
SQL Query Builder for SQL Server with paging, no lock (Dapper version parameters)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Collections.Generic; | |
using Dapper; | |
using System.Text; | |
public class SQLQueryBuilder | |
{ | |
private readonly StringBuilder _sb = new StringBuilder(); | |
private readonly List<string> _fieldNames = new List<string>(); | |
private readonly List<string> _joins = new List<string>(); | |
private readonly List<string> _wheres = new List<string>(); | |
private readonly Dictionary<string, object> _parameters = new Dictionary<string, object>(); | |
private readonly string _tableName; | |
private readonly string _orderBy; | |
private readonly bool _noLock; | |
public string SQLString { get; private set; } | |
public SQLQueryBuilder(string tableName, string orderBy, bool noLock = false) | |
{ | |
_tableName = tableName; | |
_orderBy = orderBy; | |
_noLock = noLock; | |
} | |
public SQLQueryBuilder Select(string fieldName) | |
{ | |
this._fieldNames.Add(fieldName); | |
return this; | |
} | |
public SQLQueryBuilder Select(IEnumerable<string> fieldNames) | |
{ | |
this._fieldNames.AddRange(fieldNames); | |
return this; | |
} | |
public SQLQueryBuilder Join(string join) | |
{ | |
if (!this._joins.Contains(join)) this._joins.Add(join); | |
return this; | |
} | |
public SQLQueryBuilder Where(string where) | |
{ | |
this._wheres.Add(where); | |
return this; | |
} | |
public SQLQueryBuilder Parameter(string name, object value) | |
{ | |
this._parameters.Add(name, value); | |
return this; | |
} | |
public DynamicParameters GetParameters() | |
{ | |
var sqlParameters = new DynamicParameters(); | |
//var sqlParameters = new List<SqlParameter>(); | |
foreach (var item in _parameters) | |
{ | |
sqlParameters.Add(item.Key, item.Value); | |
} | |
return sqlParameters; | |
} | |
public SQLQueryBuilder BuildForCount() | |
{ | |
this._sb.Clear(); | |
this._sb.AppendLine($"SELECT COUNT(*)"); | |
this._sb.AppendLine($"FROM {_tableName}"); | |
if (_noLock) this._sb.AppendLine(" WITH (NOLOCK) "); | |
foreach (var join in _joins) | |
{ | |
this._sb.AppendLine(join); | |
} | |
//this._sb.AppendLine($"WHERE 1 = 1"); | |
bool whereSQL = false; | |
foreach (var where in _wheres) | |
{ | |
string prefix = whereSQL ? " AND " : " WHERE "; | |
this._sb.AppendLine($"{prefix} {where}"); | |
whereSQL = true; | |
} | |
SQLString = this._sb.ToString(); | |
return this; | |
} | |
public SQLQueryBuilder Build() | |
{ | |
this._sb.Clear(); | |
this._sb.AppendLine($"SELECT {string.Join(", ", _fieldNames)}"); | |
this._sb.AppendLine($"FROM {_tableName}"); | |
if (_noLock) this._sb.AppendLine(" WITH (NOLOCK) "); | |
foreach (var join in _joins) | |
{ | |
this._sb.AppendLine(join); | |
} | |
//this._sb.AppendLine($"WHERE 1 = 1"); | |
bool whereSQL = false; | |
foreach (var where in _wheres) | |
{ | |
string prefix = whereSQL ? " AND " : " WHERE "; | |
this._sb.AppendLine($"{prefix} {where}"); | |
whereSQL = true; | |
} | |
_sb.AppendLine($"ORDER BY {_orderBy}"); | |
SQLString = this._sb.ToString(); | |
return this; | |
} | |
public SQLQueryBuilder BuildForPaging(int? skip = null, int? take = null) | |
{ | |
Build(); | |
if (skip.HasValue && take.HasValue) | |
{ | |
//add skip, take | |
this._sb.AppendLine("OFFSET @offsetRows ROWS FETCH NEXT @fetchNextRows ROWS ONLY"); | |
this._parameters.Add("offsetRows", skip); | |
this._parameters.Add("fetchNextRows", take); | |
} | |
SQLString = this._sb.ToString(); | |
return this; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment