public static string GetExistsQuery(IDbConnection connection, TypeQueryInfo queryInfo)
{
var sqlHelper = GetSqlHelper(connection);
var existsQuery = sqlHelper.GetExistsQuery(queryInfo.ExistsQuery);
return(existsQuery);
}
private static TypeQueryInfo CreateQueryInfo(IDbConnection connection, TypeInfo typeInfo)
{
TypeQueryInfo typeQueryInfo = new TypeQueryInfo();
ISqlDbHelper sqlHelper = GetSqlHelper(connection);
var sb = new StringBuilder();
GenerateColumnEqualsParamSequence(typeInfo.PrimaryKeyProperties, sqlHelper, sb, " and ");
var keyEqualsParams = sb.ToString(); // Id1 = @Id1 and Id2 = @Id2
sb.Clear();
GenerateColumnEqualsParamSequence(typeInfo.WritableProperties, sqlHelper, sb, ", ");
var updateColumnsEqualsParams = sb.ToString(); // Attr1 = @Attr1, Attr2 = @Attr2
sb.Clear();
GenerateColumnSequence(sb, typeInfo.ReadableProperties, sqlHelper);
var readableColumns = sb.ToString(); // Attr1, Attr2
sb.Clear();
GenerateColumnSequence(sb, typeInfo.InsertableProperties, sqlHelper); // Id1, Id2, Attr1, Attr2
var insertColumns = sb.ToString();
sb.Clear();
GenerateColumnSequence(sb, typeInfo.InsertableProperties, sqlHelper, true); // @Id1, @Id2, @Attr1, @Attr2
var insertParams = sb.ToString();
var tableName = sqlHelper.FormatDbEntityName(typeInfo.TableName);
typeQueryInfo.ExistsQuery = string.Format(ExistsTemplate, tableName, keyEqualsParams);
typeQueryInfo.SelectQuery = string.Format(SelectTemplate, readableColumns, tableName, keyEqualsParams);
typeQueryInfo.SelectAllQuery = string.Format(SelectAllTemplate, readableColumns, tableName);
typeQueryInfo.SelectCountQuery = string.Format(SelectCountTemplate, tableName);
typeQueryInfo.InsertQuery = string.Format(InsertTemplate, tableName, insertColumns, insertParams);
typeQueryInfo.InsertNoValuesQuery = string.Format(InsertNoValuesTemplate, tableName, insertColumns);
typeQueryInfo.UpdateQuery = string.Format(UpdateTemplate, tableName, updateColumnsEqualsParams, keyEqualsParams);
typeQueryInfo.UpdateFieldsQuery = string.Format(UpdateFieldsTemplate, tableName, keyEqualsParams);
typeQueryInfo.DeleteQuery = string.Format(DeleteTemplate, tableName, keyEqualsParams);
typeQueryInfo.DeleteAllQuery = string.Format(DeleteAllTemplate, tableName);
QueryInfos.TryAdd(typeInfo.TypeHandle, typeQueryInfo);
return(typeQueryInfo);
}
/// <summary>
/// Gets the insert query for a given type.
/// </summary>
/// <param name="connection">A database connection object to represent the origin of the given type.</param>
/// <param name="typeInfo">The TypeInfo object containing all type required information.</param>
/// <param name="queryInfo">The TypeQueryInfo object containing all queries for the given type.</param>
/// <returns>The insert query.</returns>
public static string GetInsertQuery(IDbConnection connection, TypeInfo typeInfo, TypeQueryInfo queryInfo)
{
var surrogateKeySql = typeInfo.KeyType == KeyType.Surrogate ? $"{GetSurrogateKeyReturnQuery(connection)};" : string.Empty;
return($"{queryInfo.InsertQuery};{surrogateKeySql};");
}
/// <summary>
/// Gets the personalized update query statement for specific fields.
/// </summary>
/// <param name="connection">A database connection object to represent the origin of the given type.</param>
/// <param name="typeInfo">The TypeInfo object containing all type required information.</param>
/// <param name="queryInfo">The TypeQueryInfo object containing all queries for the given type.</param>
/// <param name="fields">The fields to be used in the query assembly.</param>
/// <param name="exclude">True for exclusive, False for inclusive</param>
/// <returns>The personalized update query statement.</returns>
public static string GetUpdateFieldsQuery(IDbConnection connection, TypeInfo typeInfo, TypeQueryInfo queryInfo,
IEnumerable <string> fields, bool exclude = false)
{
var key = $"{string.Join(null, fields)}{exclude}";
if (!UpdateFieldsQueryCache.TryGetValue(key, out var sqlFields))
{
var sb = new StringBuilder();
var sqlHelper = GetSqlHelper(connection);
var props = exclude ?
typeInfo.WritableProperties.Where(p => !fields.Contains(p.Name)) :
typeInfo.WritableProperties.Where(p => fields.Contains(p.Name));
GenerateColumnEqualsParamSequence(props, sqlHelper, sb, ", ");
sqlFields = sb.ToString();
UpdateFieldsQueryCache.TryAdd(key, sqlFields);
}
var sql = queryInfo.UpdateFieldsQuery.Replace("FIELDS", sqlFields);
return(sql);
}
/// <summary>
/// Gets the insert query for multiple entities in one single statement.
/// </summary>
/// <param name="connection">A database connection object to represent the origin of the given type.</param>
/// <param name="typeInfo">The TypeInfo object containing all type required information.</param>
/// <param name="queryInfo">The TypeQueryInfo object containing all queries for the given type.</param>
/// <param name="entitiesCount">The amount of entities to be processed.</param>
/// <returns>The insert query for multiple entities.</returns>
public static string GetInsertManyQuery(IDbConnection connection, TypeInfo typeInfo, TypeQueryInfo queryInfo,
int entitiesCount)
{
var surrogateKeySql = typeInfo.KeyType == KeyType.Surrogate ? $"{GetSurrogateKeyReturnQuery(connection)};" : string.Empty;
var sb = new StringBuilder();
var sbSql = new StringBuilder();
var sqlHelper = GetSqlHelper(connection);
for (int i = 0; i < entitiesCount; i++)
{
GenerateColumnSequence(sb, typeInfo.InsertableProperties, sqlHelper, true, $"_{i}");
var insertParams = sb.ToString();
sbSql.AppendLine($"{queryInfo.InsertNoValuesQuery.Replace("PARAMS", insertParams)};{surrogateKeySql}");
sb.Clear();
}
var finalSql = sbSql.ToString();
return(finalSql);
}
