const int AVERAGE_PROPERTY_LENGTH = 14 + 2; // assumed average property length, plus 2 characters for "@" and ("," or "=").
#region Insert<T>()
public static QueryInfo Insert<T>(T obj, Predicate<string> excludedProperties = null, string tableName = null) where T : class
{
if (tableName == null) tableName = obj.AsSqlName();
var dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
var dictCount = dict.Count;
var dictNew = new Dictionary<string, (object, Type)>(dictCount, Util.FastStringComparer.Instance);
int i = 0;
string currentKey, paramName;
var sb = new StringBuilder("INSERT ", AVERAGE_PROPERTY_LENGTH * dictCount * 2 + 25).Append(tableName).Append(" (");
var sbParams = new StringBuilder(AVERAGE_PROPERTY_LENGTH * dictCount);
foreach (var kvp in dict)
{
currentKey = kvp.Key;
if (excludedProperties != null && excludedProperties(currentKey))
continue;
if (i++ != 0)
{
sb.Append(',');
sbParams.Append(',');
}
sb.Append('[').Append(currentKey).Append(']');
paramName = "@#" + currentKey;
sbParams.Append(paramName);
dictNew.Add(paramName, kvp.Value);
}//foreach
sb.Append(") VALUES (").Append(sbParams.ToString()).Append(')');
var result = QueryInfo.Create(sql: sb.ToString(), parameterMap: dictNew);
return result;
}// Insert<T>()
}// Update<T, TParamType>()
#endregion
#region UpdateRaw<T>()
internal static QueryInfo UpdateRaw<T>(T obj, Predicate<string> excludedProperties = null, string tableName = null, Dictionary<string, object> dict = null) where T : class
{
if (tableName == null) tableName = obj.AsSqlName();
if (dict == null) dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
var dictCount = dict.Count;
var dictNew = new Dictionary<string, object>(dictCount, Util.FastStringComparer.Instance);
int i = 0;
string currentKey, paramName;
var sb = new StringBuilder("UPDATE ", AVERAGE_PROPERTY_LENGTH * dictCount * 2 + 12).Append(tableName).Append(" SET ");
foreach (var kvp in dict)
{
currentKey = kvp.Key;
if (excludedProperties != null && !excludedProperties(currentKey))
continue;
if (i++ != 0)
{
sb.Append(',');
}
paramName = "@@" + currentKey;
sb.Append('[').Append(currentKey).Append("]=").Append(paramName);
dictNew.Add(paramName, kvp.Value);
}//foreach
if (i == 0) throw new ArgumentException($"{nameof(excludedProperties)} predicate is false for all property names.", nameof(excludedProperties));
var result = new QueryInfo { SQL = sb.ToString(), ParameterMap = dictNew };
return result;
}// UpdateRaw<T>()
}// CreateQueryInfo()
public static QueryInfo CreateQueryInfo<TParamType>(string sql, TParamType param) where TParamType : class
{
var queryInfo = new QueryInfo() { SQL = sql };
if (param == null)
{
queryInfo.ParameterMap = new Dictionary<string, object>(0);
return queryInfo;
}
queryInfo.ParameterMap = ReflectionHelper_Shared.ObjectToDictionary<TParamType>(param, ReflectionHelper_Shared.PARAM_PREFIX);
return queryInfo;
}// CreateQueryInfo<TParamType>()
static Dictionary<string, object> GetObjectPropertiesAsDictionary<T>(T obj) where T : class
{
Dictionary<string, object> propertyMap;
var rowStore = obj as RowStore;
if (rowStore != null)
{
propertyMap = new Dictionary<string, object>(rowStore.RowValues.Length, Util.FastStringComparer.Instance);
foreach (var kvp in rowStore.Schema.FieldMap)
{
propertyMap.Add(kvp.Key, rowStore.RowValues[kvp.Value]);
}
}
else propertyMap = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
return propertyMap;
}//GetObjectPropertiesAsDictionary<T>()
}// Update<T>
public static QueryInfo Update<T, TParamType>(T obj, string whereSql = null, TParamType whereParam = null, Predicate<string> excludedProperties = null, string tableName = null, Dictionary<string, object> dict = null) where T : class where TParamType : class
{
if (dict == null) dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
QueryInfo queryInfo = UpdateRaw<T>(obj, excludedProperties, tableName, dict);
var paramDictAlias = queryInfo.ParameterMap;
var whereParamMap = default(Dictionary<string, object>);
if (string.IsNullOrEmpty(whereSql))
{
if (!dict.TryGetValue("Id", out var id)) throw new ArgumentException(@"""whereSql"" is empty and object does not contain ""Id"" property.");
whereSql = "Id=@w@Id";
paramDictAlias.Add("@w@Id", id);
}
else if (whereParam != null)
{
whereParamMap = whereParam as Dictionary<string, object> ?? ReflectionHelper_Shared.ObjectToDictionary<TParamType>(whereParam, ReflectionHelper_Shared.PARAM_PREFIX);
foreach (var kvp in whereParamMap)
paramDictAlias.Add(kvp.Key, kvp.Value);
}
whereSql = queryInfo.SQL + " WHERE " + whereSql;
queryInfo = new QueryInfo { SQL = whereSql, ParameterMap = paramDictAlias };
return queryInfo;
}// Update<T, TParamType>()
}// Delete<TParamType>()
#endregion
#region Upsert<T>
public static QueryInfo Upsert<T>(T obj, string tableName = null, Predicate<string> excludedInsertProperties = null, Predicate<string> includedUpdateProperties = null, string mergeOnSql = null) where T : class
{
if (tableName == null) tableName = obj.AsSqlName();
if (string.IsNullOrEmpty(mergeOnSql)) mergeOnSql = "S.Id=T.Id"; // "S" is source; "T" is target
var dict = ReflectionHelper_Shared.ObjectToDictionary<T>(obj);
var dictCount = dict.Count;
var dictParams = new Dictionary<string, object>(dictCount, Util.FastStringComparer.Instance);
string currentKey, currentKeyBracketed, paramName;
var sbSql = new StringBuilder(";WITH S(");
var sbParams = new StringBuilder();
var sbInsertColumns = new StringBuilder();
var sbInsertValues = new StringBuilder();
var sbUpdateSql = new StringBuilder();
int i = 0, insertColumnCount = 0, updateColumnCount = 0;
foreach (var kvp in dict)
{
currentKey = kvp.Key;
currentKeyBracketed = "[" + currentKey + "]";
if (i++ != 0)
{
sbSql.Append(',');
sbParams.Append(',');
}
sbSql.Append(currentKeyBracketed);
paramName = "@@" + currentKey;
sbParams.Append(paramName);
dictParams.Add(paramName, kvp.Value);
if (excludedInsertProperties == null || !excludedInsertProperties(currentKey))
{
if (insertColumnCount++ > 0)
{
sbInsertColumns.Append(',');
sbInsertValues.Append(',');
}
sbInsertColumns.Append(currentKeyBracketed);
sbInsertValues.Append(paramName);
}
if (includedUpdateProperties == null || includedUpdateProperties(currentKey))
{
if (updateColumnCount++ > 0)
{
sbUpdateSql.Append(',');
}
sbUpdateSql.Append(currentKeyBracketed).Append('=').Append(paramName);
}
}//foreach
sbSql
.Append(") AS (SELECT ")
.Append(sbParams.ToString())
.Append(")\nMERGE ")
.Append(tableName)
.Append(" WITH (HOLDLOCK) T USING S ON ")
.Append(mergeOnSql)
.Append("\nWHEN NOT MATCHED THEN\n\tINSERT (")
.Append(sbInsertColumns.ToString()).Append(") VALUES (")
.Append(sbInsertValues);
if (updateColumnCount > 0)
{
sbSql
.Append(")\nWHEN MATCHED THEN\n\tUPDATE SET ")
.Append(sbUpdateSql.ToString());
}
else
{
sbSql.Append(")\n");
}
var result = new QueryInfo { SQL = sbSql.ToString(), ParameterMap = dictParams };
return result;
}//Upsert()
}// Delete()
public static QueryInfo Delete<TParamType>(string tableName = null, string whereSql = null, TParamType whereParam = null) where TParamType : class
{
if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName");
if (string.IsNullOrEmpty(whereSql)) throw new ArgumentNullException("whereSql");
string sql = "DELETE FROM " + tableName + " WHERE " + whereSql;
var whereParamMap = default(Dictionary<string, object>);
if (whereParam != null) whereParamMap = whereParam as Dictionary<string, object> ?? ReflectionHelper_Shared.ObjectToDictionary<TParamType>(whereParam, ReflectionHelper_Shared.PARAM_PREFIX);
var result = new QueryInfo { SQL = sql, ParameterMap = whereParamMap };
return result;
}// Delete<TParamType>()
static void SetParameters<T>(this SqlCommand command, T obj) where T : class
{
var objPropertyValueDictionary = ReflectionHelper_Shared.ObjectToDictionary<T>(obj, ReflectionHelper_Shared.PARAM_PREFIX);
SetParameters(command, objPropertyValueDictionary);
}// SetParameters<T>()