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); }