public ObjectToSql(DataBaseType type, bool includeNonPublicAccessor) { DatabaseType = type; IncludeNonPublicAccessor = includeNonPublicAccessor; SqlSyntaxHelper = new SqlSyntaxHelper(type); //AlwaysCreateParamaterizedSql = alwaysUseParamerizedSql; }
/// <summary> /// Builds the upsert query. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sqlBuilder">The SQL builder.</param> /// <param name="tableName">Name of the table.</param> private void BuildUpsertQuery <T>(StringBuilder sqlBuilder, string tableName, params Expression <Func <T, object> >[] overrideKeys) where T : class { var outputFields = overrideKeys.GetPropertyNamesFromExpressions(); var keyFields = ExtFastMember.GetMemberWrappers <T>(IncludeNonPublicAccessor).Where(m => outputFields.Contains(m.Name)).AsList(); if (keyFields.IsNullOrEmpty()) { throw new MissingKeyAttributeException(ExceptionHelper.MissingKeyMessage); } var sb1 = new StringBuilder(); BuildInsertQuery <T>(sb1, tableName); var sb2 = new StringBuilder(); sb2.Append($"{SqlGenerator.BuildWhereClauseFromMembers(SqlSyntaxHelper, keyFields, !AlwaysCreateParamaterizedSql)}"); if (DatabaseType == DataBaseType.Sqlite) { tableName = tableName ?? typeof(T).GetTableNameFromCustomAttributeOrDefault(); SqLiteBuildUpsertQuery <T>(sqlBuilder, keyFields, tableName, sb2.ToString(), sb1.ToString()); } else if (DatabaseType == DataBaseType.MySql) { sqlBuilder.Append(sb1); MySqlBuildOnDuplicateKeyUpdate(sqlBuilder, typeof(T)); } else { var sb = new StringBuilder(); BuildUpdateQuery(sb, tableName, overrideKeys); sqlBuilder.Append(SqlSyntaxHelper.BuildIfExistStatement($"{SqlSyntaxHelper.ConstSqlServerSelectTop1} {tableName ?? typeof(T).GetTableNameFromCustomAttributeOrDefault()} {sb2}", sb.ToString(), sb1.ToString())); } }
public static string BuildDeleteQuery(SqlSyntaxHelper syntax, string tableName, List <MemberWrapper> keyColumns, bool isReadableSql) { var deleteFromClause = ($"{BuildDeleteFromTable(tableName)} "); var whereClause = ($"{BuildWhereClauseFromMembers(syntax, keyColumns, isReadableSql)}"); return($"{deleteFromClause}{whereClause}"); }
/// <summary> /// Builds the upsert query. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sqlBuilder">The SQL builder.</param> /// <param name="tableName">Name of the table.</param> /// <param name="type">relection is done on this type to generate sql</param> private void BuildUpsertQuery(StringBuilder sqlBuilder, string tableName, Type type) { var keyFields = GetKeyFields(IncludeNonPublicAccessor, type); if (keyFields.IsNullOrEmpty()) { throw new MissingKeyAttributeException(ExceptionHelper.MissingKeyMessage); } tableName = tableName ?? type.GetTableNameFromCustomAttributeOrDefault(); var sb1 = new StringBuilder(); BuildInsertQuery(sb1, tableName, type); var sb2 = new StringBuilder(); sb2.Append($"{SqlGenerator.BuildWhereClauseFromMembers(SqlSyntaxHelper, keyFields, !AlwaysCreateParamaterizedSql)}"); if (DatabaseType == DataBaseType.Sqlite) { SqLiteBuildUpsertQuery(sqlBuilder, keyFields, tableName, sb2.ToString(), sb1.ToString(), type); } else if (DatabaseType == DataBaseType.MySql) { sqlBuilder.Append(sb1); MySqlBuildOnDuplicateKeyUpdate(sqlBuilder, type); } else { var sb = new StringBuilder(); BuildUpdateQuery(sb, tableName, type); sqlBuilder.Append(SqlSyntaxHelper.BuildIfExistStatement($"{SqlSyntaxHelper.ConstSqlServerSelectTop1} {tableName ?? type.GetTableNameFromCustomAttributeOrDefault()} {sb2}", sb.ToString(), sb1.ToString())); } }
/// <summary> /// Example *SET A=@A, B=@b, C=@C* /// </summary> /// <param name="syntax"></param> /// <param name="columns"></param> /// <param name="parameterColumns"></param> /// <returns></returns> internal static string BuildSetColumns(SqlSyntaxHelper syntax, List <string> columns, List <string> parameterColumns, bool isReadableSql) { var sqlBuilder = new StringBuilder("SET "); sqlBuilder.Append(BuildColumnsEqualColumns(syntax, columns, parameterColumns, isReadableSql)); return(sqlBuilder.ToString()); }
/// <summary> /// Builds the insert query. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="columns">column names</param> /// <param name="valueColumns">values of columns</param> /// <param name="isReadableSql"></param> public static string BuildInsertQuery(SqlSyntaxHelper syntax, string tableName, List <string> columns, List <string> valueColumns, bool isReadableSql) { var columnsInParenthesesSection = BuildColumnsInParentheses(syntax, columns); var valueSection = BuildValues(syntax, valueColumns, isReadableSql); return($"{BuildInsertIntoTable(syntax, tableName)} {columnsInParenthesesSection} {valueSection}"); // INSERT INTO TABLE (A,B,C) VALUES (@A,@B,@C) }
/// <summary> /// BUilds a where clause from a list of member wrappers /// </summary> /// <param name="syntax"></param> /// <param name="members"> </param> /// <returns></returns> public static string BuildWhereClauseFromMembers(SqlSyntaxHelper syntax, List <MemberWrapper> members, bool isReadableSql) { // This uses the .net property attribute mapto else default property name var columns = members.Select(c => c.GetNameFromCustomAttributeOrDefault()).AsList(); // This uses the .net property name & ignores any attribute mapto name to ensure duplication is prevented var values = members.Select(c => c.Name).AsList(); return(BuildWhereClause(syntax, columns, values, isReadableSql)); }
/// <summary> /// Example *VALUES (@A,@B,@C)* /// </summary> /// <param name="syntax"></param> /// <param name="columns"></param> /// <returns></returns> internal static string BuildValues(SqlSyntaxHelper syntax, List <string> columns, bool isReadableSql) { var sqlBuilder = new StringBuilder(); var prefix = isReadableSql ? string.Empty : syntax.ConstAt.ToString(); sqlBuilder.Append($"{syntax.ConstValues} {syntax.ConstOpenParens}"); // VALUES ( columns.ForEach(s => sqlBuilder.Append($"{prefix}{s},")); // @test, sqlBuilder.Remove(sqlBuilder.Length - 1, 1); // Remove the last comma sqlBuilder.Append(syntax.ConstCloseParens); // ) return(sqlBuilder.ToString()); // VALUES (@TEST,TEST) }
/// <summary> /// Example *([A],[B],[C])* /// </summary> /// <param name="syntax"></param> /// <param name="columns"></param> /// <returns></returns> internal static string BuildColumnsInParentheses(SqlSyntaxHelper syntax, List <string> columns) { var sqlBuilder = new StringBuilder(); var o = syntax.GetKeywordEscapeOpenChar(); // alias to keep code short [ var c = syntax.GetKeywordEscapeClosedChar(); // alias to keep code short ] sqlBuilder.Append($"{syntax.ConstOpenParens}"); // ( columns.ForEach(s => sqlBuilder.Append($"{o}{s}{c},")); // [test],[test2] sqlBuilder.Remove(sqlBuilder.Length - 1, 1); // Remove the last comma sqlBuilder.Append($"{syntax.ConstCloseParens}"); // ) return(sqlBuilder.ToString()); // ([test],[test2]) }
/// <summary> /// Example *A=@A, B=@b, C=@C* /// </summary> /// <param name="syntax"></param> /// <param name="columns"></param> /// <param name="parameterColumns"></param> /// <returns></returns> internal static string BuildColumnsEqualColumns(SqlSyntaxHelper syntax, List <string> columns, List <string> parameterColumns, bool isReadableSql) { var sqlBuilder = new StringBuilder(string.Empty); var i = 0; foreach (var col in columns) { sqlBuilder.Append($"{BuildColumnEqualColumn(syntax, col, parameterColumns[i], isReadableSql)}"); i++; } sqlBuilder.Remove(sqlBuilder.Length - 1, 1); // Remove the last comma return(sqlBuilder.ToString()); }
private string RemoveBrackets(string content) { var syntaxHelper = new SqlSyntaxHelper(DbType); if (content.StartsWith(syntaxHelper.GetKeywordEscapeOpenChar())) { content = content.ReplaceFirstOccurrence(syntaxHelper.GetKeywordEscapeOpenChar(), string.Empty, StringComparison.Ordinal); } if (content.EndsWith(syntaxHelper.GetKeywordEscapeClosedChar())) { content = content.ReplaceLastOccurrence(syntaxHelper.GetKeywordEscapeClosedChar(), string.Empty, StringComparison.Ordinal); } return(content); }
private string AddBrackets(string content) { var syntaxHelper = new SqlSyntaxHelper(DbType); if (!content.StartsWith(syntaxHelper.GetKeywordEscapeOpenChar())) { content = $"{syntaxHelper.GetKeywordEscapeOpenChar()}{content}"; } if (!content.EndsWith(syntaxHelper.GetKeywordEscapeClosedChar())) { content = $"{content}{syntaxHelper.GetKeywordEscapeClosedChar()}"; } return(content); }
/// <summary> /// Example *OUTPUT INSERTED.A , INSERTED.B, INSERTED.C* /// </summary> /// <typeparam name="T"></typeparam> /// <param name="syntax"></param> /// <param name="outputFields"></param> /// <returns></returns> internal static string BuildOutputFields <T>(SqlSyntaxHelper syntax, List <string> outputFields, OutputType outputType) where T : class { var o = syntax.GetKeywordEscapeOpenChar(); // alias to keep code short [ var c = syntax.GetKeywordEscapeClosedChar(); // alias to keep code short ] var sqlBuilder = new StringBuilder(); //sqlBuilder.Append($"{Environment.NewLine} OUTPUT"); sqlBuilder.Append($"OUTPUT"); var members = ExtFastMember.GetMemberWrappers <T>(true); outputFields.ForEach(delegate(string s) { sqlBuilder.Append($" {outputType}.{o}{members.First(av => av.Name == s).GetNameFromCustomAttributeOrDefault()}{c} ,"); }); sqlBuilder.Remove(sqlBuilder.Length - 1, 1); return(sqlBuilder.ToString()); }
/// <summary> /// Builds the update query and return the expression. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sqlBuilder">The SQL builder.</param> /// <param name="tableName">Name of the table.</param> /// <param name="outFields"></param> private void BuildUpsertQueryWithOutputs <T>(StringBuilder sqlBuilder, string tableName, params Expression <Func <T, object> >[] outFields) where T : class { var keyFields = GetKeyFields <T>(IncludeNonPublicAccessor); if (keyFields.IsNullOrEmpty()) { throw new MissingKeyAttributeException(ExceptionHelper.MissingKeyMessage); } var sb = new StringBuilder(); BuildUpdateQueryWithOutputs(sb, tableName, outFields); var sb1 = new StringBuilder(); BuildInsertQueryWithOutputs(sb, tableName, outFields); var sb2 = new StringBuilder(); sb2.Append($"{SqlGenerator.BuildWhereClauseFromMembers(SqlSyntaxHelper, keyFields, !AlwaysCreateParamaterizedSql)}"); sqlBuilder.Append(SqlSyntaxHelper.BuildIfExistStatement($"SELECT * FROM {tableName} {sb2}", sb.ToString(), sb1.ToString())); }
/// <summary> /// Builds the where clause. /// Example *WHERE A=@A AND B=@b* /// </summary> public static string BuildWhereClause(SqlSyntaxHelper syntax, List <string> columns, List <string> parameterColumns, bool isReadableSql) { if (columns.IsNullOrEmpty()) { return(string.Empty); } else { var sqlBuilder = new StringBuilder("WHERE"); var i = 0; foreach (var col in columns) { sqlBuilder.Append($" {BuildColumnEqualColumn(syntax, col, parameterColumns[i], isReadableSql)}"); sqlBuilder.Remove(sqlBuilder.Length - 1, 1); // Remove the last comma sqlBuilder.Append(" AND"); i++; } sqlBuilder.Remove(sqlBuilder.Length - 4, 4); // Remove the last , AND return(sqlBuilder.ToString()); } }
private void SqLiteBuildUpsertQuery(StringBuilder sqlBuilder, List <MemberWrapper> keyFields, string tableName, string whereClause, string normalInsertSQl, Type type) { var updateFields = GetNonKeyFields(IncludeNonPublicAccessor, type); var trueForAll = keyFields.TrueForAll(w => (w.Type == typeof(int) || w.Type == typeof(long))); // THESE ARE TREATED LKE IDENTITY FIELDS IF NOT SPECIFIED https://www.sqlite.org/autoinc.html if (trueForAll) { sqlBuilder.Append($@"{SqlGenerator.BuildInsertOrReplace(tableName)} "); // sqlBuilder.Append($@"{SqlGenerator.BuildColumnsInParentheses(SqlSyntaxHelper, keyFields.Select(w => w.GetNameFromCustomAttributeOrDefault()).AsList())} "); sqlBuilder.Append($@"({string.Join(",", keyFields.Select(w => $"{SqlSyntaxHelper.GetKeywordEscapeOpenChar()}{w.GetNameFromCustomAttributeOrDefault()}{SqlSyntaxHelper.GetKeywordEscapeClosedChar()}"))},{string.Join(",", updateFields.Select(w => $"{SqlSyntaxHelper.GetKeywordEscapeOpenChar()}{w.GetNameFromCustomAttributeOrDefault()}{SqlSyntaxHelper.GetKeywordEscapeClosedChar()}"))}) VALUES ({string.Join(",", keyFields.Select(w => $"(SELECT {w.GetNameFromCustomAttributeOrDefault()} FROM {tableName} {whereClause})"))}, {string.Join(",", updateFields.Select(w => $"@{w.Name}"))} )"); } else { sqlBuilder.Append($"{normalInsertSQl} ON CONFLICT ({string.Join(",", keyFields.Select(w => $"{SqlSyntaxHelper.GetKeywordEscapeOpenChar()}{w.GetNameFromCustomAttributeOrDefault()}{SqlSyntaxHelper.GetKeywordEscapeClosedChar()}"))} DO UPDATE "); // Build Set fields sqlBuilder.Append(SqlGenerator.BuildSetColumns(SqlSyntaxHelper, updateFields.Select(w => w.GetNameFromCustomAttributeOrDefault()).AsList(), updateFields.Select(w => w.Name).AsList(), !AlwaysCreateParamaterizedSql)); // Build Where clause. sqlBuilder.Append($" {whereClause}"); } }
public ObjectToSql(DataBaseType type) { DatabaseType = type; SqlSyntaxHelper = new SqlSyntaxHelper(type); //AlwaysCreateParamaterizedSql = alwaysUseParamaterizedSql; }
/// <summary> /// Example *INSERT INTO TABLE* /// </summary> /// <param name="syntax"></param> /// <param name="tableName"></param> /// <returns></returns> internal static string BuildInsertIntoTable(SqlSyntaxHelper syntax, string tableName) { return($"{syntax.ConstInsertInto} {tableName}"); }
/// <summary> /// Example *A=@A,* /// </summary> /// <param name="syntax"></param> /// <param name="column"></param> /// <param name="parameterColumn"></param> /// <returns></returns> internal static string BuildColumnEqualColumn(SqlSyntaxHelper syntax, string column, string parameterColumn, bool isReadableSql) { var prefix = isReadableSql ? string.Empty : syntax.ConstAt.ToString(); return($"{syntax.GetKeywordEscapeOpenChar()}{column}{syntax.GetKeywordEscapeClosedChar()}={prefix}{parameterColumn},"); }