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