private (string sql, DynamicParameters dynParms) ConstructSelectSql(IEnumerable <Expression <Func <TEntity, object> > > selectFields, WhereClauseResult whereClause, string sqlOrderbyClause)
{
DynamicParameters dynParms = new DynamicParameters();
StringBuilder builder = new StringBuilder();
string escapeTableName = this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings);
var propertyMaps = _classMap.Properties.PropertyMaps.Where(p => !p.Ignored).ToList();
var selectFieldList = selectFields?.ToList() ?? new List <Expression <Func <TEntity, object> > >();
if (selectFieldList.Any())
{
propertyMaps = propertyMaps.Where(p => selectFieldList.Any(exp => string.Equals(p.Name,
exp.GetMemberName(), StringComparison.CurrentCultureIgnoreCase))).ToList();
}
builder.Append("SELECT ");
int index = 1;
string charString = AppUtility.GetCharString(" ", "SELECT".Length);
foreach (var item in propertyMaps)
{
string mn = $"{escapeTableName}.{this._storeMapping.GetEscapeColumnName(item.ColumnName, this._dialectSettings)}";
builder.Append(index == 1 ? $"{mn}{this.Newline}" : $"{charString},{mn}{this.Newline}");
index++;
}
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}FROM {escapeTableName}{this.Newline}");
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}WHERE ({whereClause.WhereClause}){this.Newline}");
foreach (var item in whereClause.Parameter)
{
dynParms.Add(item.Key, item.Value);
}
whereClause.Parameter.Clear();
}
sqlOrderbyClause = string.IsNullOrEmpty(sqlOrderbyClause) ? this.BuildOrderbyClause() : sqlOrderbyClause;
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}ORDER BY {sqlOrderbyClause}{this.Newline}");
builder.Append(";");
string sql = builder.ToString();
builder.Clear();
return(sql, dynParms);
}
private (string sql, DynamicParameters dynParms) ConstructUpdateSql(TEntity entity, IEnumerable <Expression <Func <TEntity, object> > > updateFields, WhereClauseResult whereClause)
{
DynamicParameters dynParms = new DynamicParameters();
StringBuilder builder = new StringBuilder();
string escapeTableName = this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings);
var propertyMaps = _classMap.Properties.PropertyMaps.Where(p => !p.Ignored && p.CanModified).ToList();
var updateFieldList = updateFields?.ToList() ?? new List <Expression <Func <TEntity, object> > >();
if (updateFieldList.Any())
{
propertyMaps = propertyMaps
.Where(p => updateFieldList.Any(exp => string.Equals(p.Name, exp.GetMemberName(),
StringComparison.CurrentCultureIgnoreCase))).ToList();
}
builder.Append($"UPDATE {escapeTableName}{this.Newline}");
builder.Append($"{AppUtility.GetCharString(" ", "UPD".Length)}SET");
var index = 1;
var charString = AppUtility.GetCharString(" ", "UPDATE".Length);
foreach (var item in propertyMaps)
{
string mn = $"{escapeTableName}.{this._storeMapping.GetEscapeColumnName(item.ColumnName, this._dialectSettings)}";
string pn = $"{this._dialectSettings.ParameterPrefix}{item.Name}";
object pv = item.Property.GetValue(entity, null) ?? DBNull.Value;
dynParms.Add(pn, pv);
builder.Append(index == 1 ? $" {mn} = {pn}{this.Newline}" : $"{charString},{mn} = {pn}{this.Newline}");
index++;
}
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "U".Length)}WHERE ({whereClause.WhereClause}){this.Newline}");
foreach (var item in whereClause.Parameter)
{
dynParms.Add(item.Key, item.Value);
}
whereClause.Parameter.Clear();
}
builder.Append(";");
string sql = builder.ToString();
builder.Clear();
return(sql, dynParms);
}
public virtual async Task <int> CountAsync(IDbConnection conn, Expression <Func <TEntity, object> > member = null, WhereClauseResult whereClause = null, IDbTransaction tr = null, CancellationToken cctoken = default(CancellationToken))
{
if (conn == null)
{
throw new ArgumentNullException(nameof(conn));
}
var escapeTableName = this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings);
var columnName = member == null ? "*" : $"{escapeTableName}.{this._storeMapping.GetEscapeColumnName(_classMap.Properties.Get(member.GetMemberName()).ColumnName, this._dialectSettings)}";
var builder = new StringBuilder();
var dynParms = new DynamicParameters();
builder.Append($"SELECT COUNT({columnName}) FROM {escapeTableName}{this.Newline}");
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}WHERE ({whereClause.WhereClause}){this.Newline}");
foreach (var item in whereClause.Parameter)
{
dynParms.Add(item.Key, item.Value);
}
whereClause.Parameter.Clear();
}
builder.Append(";");
var sql = builder.ToString();
builder.Clear();
try
{
var cmd = this.BuildDapperCmd(sql, dynParms, tr, cancellationToken: cctoken);
return(await conn.QuerySingleAsync(cmd));
}
catch (Exception ex)
{
IDictionary <string, object> parameters = new Dictionary <string, object>();
foreach (var item in dynParms.ParameterNames)
{
parameters.Insert(item, dynParms.Get <object>(item));
}
throw new ExecuteSqlErrorException(sql, parameters, ex.Message, ex);
}
}
private (string sql, DynamicParameters dynParms) ConstructDeleteSql(WhereClauseResult whereClause)
{
DynamicParameters dynParms = new DynamicParameters();
StringBuilder builder = new StringBuilder();
string escapeTableName = this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings);
builder.Append($"DELETE FROM {escapeTableName}{this.Newline}");
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "DELETE".Length)}WHERE ({whereClause.WhereClause}){this.Newline}");
foreach (var item in whereClause.Parameter)
{
dynParms.Add(item.Key, item.Value);
}
whereClause.Parameter.Clear();
}
builder.Append(";");
string sql = builder.ToString();
builder.Clear();
return(sql, dynParms);
}
private (string sql, DynamicParameters dynParms) ConstructInsertSql(TEntity entity, IEnumerable <Expression <Func <TEntity, object> > > incrementFields)
{
if (entity == null)
{
throw new ArgumentNullException(nameof(entity));
}
DynamicParameters dynParms = new DynamicParameters();
StringBuilder builder = new StringBuilder();
string escapeTableName = this._storeMapping.GetEscapeTableName(_classMap.TableName, this._dialectSettings);
var propertyMaps = _classMap.Properties.PropertyMaps.Where(p => !p.Ignored && !p.IsReadOnly).ToList();
var incrementFieldList = incrementFields?.ToList() ?? new List <Expression <Func <TEntity, object> > >();
if (incrementFieldList.Any())
{
propertyMaps = propertyMaps.Where(p => !incrementFieldList.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.CurrentCultureIgnoreCase))).ToList();
}
builder.Append($"INSERT INTO {escapeTableName}{this.Newline}");
string charString = AppUtility.GetCharString(" ", "INSERT INTO".Length);
builder.Append($"{charString}(");
var index = 1;
var total = propertyMaps.Count;
foreach (var item in propertyMaps)
{
string mn = $"{escapeTableName}.{this._storeMapping.GetEscapeColumnName(item.ColumnName, this._dialectSettings)}";
if (index == 1)
{
builder.Append($"{mn}{this.Newline}");
}
else if (index == total)
{
builder.Append($"{charString},{mn}");
}
else
{
builder.Append($"{charString},{mn}{this.Newline}");
}
index++;
}
builder.Append($"){this.Newline}");
builder.Append($"{AppUtility.GetCharString(" ", "INSERT".Length)}VALUES{this.Newline}");
builder.Append($"{charString}(");
index = 1;
foreach (var item in propertyMaps)
{
string pn = $"{this._dialectSettings.ParameterPrefix}{item.Name}";
object pv = item.Property.GetValue(entity, null) ?? DBNull.Value;
dynParms.Add(pn, pv);
if (index == 1)
{
builder.Append($"{pn}{this.Newline}");
}
else if (index == total)
{
builder.Append($"{charString},{pn}");
}
else
{
builder.Append($"{charString},{pn}{this.Newline}");
}
index++;
}
builder.Append($"){this.Newline}");
builder.Append(";");
string sql = builder.ToString();
builder.Clear();
return(sql, dynParms);
}
private (string sql, DynamicParameters dynParms) ConstructMsSqlSelectPage(int pageNumber, int pageSize, IEnumerable <Expression <Func <TEntity, object> > > selectFields, WhereClauseResult whereClause, string sqlOrderbyClause)
{
StringBuilder builder = new StringBuilder();
DynamicParameters dynParms = new DynamicParameters();
var escapeTableName = base.StoreMapping.GetEscapeTableName(base.ClassMap.TableName, base.DialectSettings);
builder.Append($"SELECT COUNT(*) AS _TotalNumberOfRecords FROM {escapeTableName}{base.Newline}");
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}WHERE ({whereClause.WhereClause}){base.Newline}");
}
builder.Append($";{base.Newline}");
//
var propertyMaps = base.ClassMap.Properties.PropertyMaps.Where(p => !p.Ignored).ToList();
IEnumerable <PropertyInfo> propertyInfos = typeof(TEntity).GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(p => p.CanRead && p.CanWrite && p.PropertyType.IsSimpleType());
var selectFieldList = selectFields?.ToList() ?? new List <Expression <Func <TEntity, object> > >();
if (selectFieldList.Any())
{
propertyMaps = propertyMaps.Where(p => selectFieldList.Any(exp => string.Equals(p.Name,
exp.GetMemberName(), StringComparison.CurrentCultureIgnoreCase))).ToList();
}
builder.Append($"SELECT TOP({pageSize}) ");
var propertyinfoList = propertyInfos.ToList();
var charString = AppUtility.GetCharString(" ", "SELECT".Length);
int index = 1;
foreach (var item in propertyMaps)
{
string pgn = $"PageResult.{base.StoreMapping.GetEscapeColumnName(item.ColumnName, base.DialectSettings)}";
builder.Append(index == 1 ? $"{pgn}{base.Newline}" : $"{charString},{pgn}{base.Newline}");
index++;
}
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}FROM (");
sqlOrderbyClause = string.IsNullOrEmpty(sqlOrderbyClause) ? base.BuildOrderbyClause() : sqlOrderbyClause;
builder.Append($"SELECT ROW_NUMBER() OVER(ORDER BY {sqlOrderbyClause}) AS _RowNumber{base.Newline}");
foreach (var item in propertyMaps)
{
string mn = $"{escapeTableName}.{base.StoreMapping.GetEscapeColumnName(item.ColumnName, base.DialectSettings)}";
builder.Append($"{charString},{mn}{base.Newline}");
}
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}FROM {escapeTableName}{base.Newline}");
if (whereClause != null)
{
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}WHERE ({whereClause.WhereClause}){base.Newline}");
foreach (var item in whereClause.Parameter)
{
dynParms.Add(item.Key, item.Value);
}
whereClause.Parameter.Clear();
}
builder.Append($"{charString}) ");
builder.Append($"AS PageResult{base.Newline}");
builder.Append($"{AppUtility.GetCharString(" ", "SE".Length)}WHERE PageResult._RowNumber > {(pageNumber - 1) * pageSize}{base.Newline}");
builder.Append(";");
string sql = builder.ToString();
builder.Clear();
return(sql, dynParms);
}