public static bool IsDbType(this Type type)
{
ClientTypeToDbTypeResolver dbTypeResolver = new ClientTypeToDbTypeResolver();
DbType?resolvedDbType = dbTypeResolver.Resolve(type);
return(resolvedDbType.HasValue);
}
/// <summary>
/// Creates a SQL Statement for insert-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsertAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
int batchSize = 1,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
var commandText = base.CreateInsertAll(builder,
tableName,
fields,
batchSize,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = "BIGINT";
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToPostgreSqlStringNameResolver().Resolve(dbType.Value);
}
}
// Variables needed
var commandTexts = new List <string>();
var splitted = commandText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
// Iterate the indexes
for (var index = 0; index < splitted.Length; index++)
{
var line = splitted[index].Trim();
// Set the return value
var returnValue = identityField != null?
string.IsNullOrEmpty(databaseType) ?
identityField.Name.AsQuoted(DbSetting) :
$"CAST({identityField.Name.AsQuoted(DbSetting)} AS {databaseType})" :
primaryField != null?primaryField.Name.AsQuoted(DbSetting) : "NULL";
commandTexts.Add(string.Concat(line, " RETURNING ", returnValue, " AS ", "Id".AsQuoted(DbSetting), ", ",
$"{DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index} AS ", "OrderColumn".AsQuoted(DbSetting), " ;"));
}
// Set the command text
commandText = commandTexts.Join(" ");
// Return the query
return(commandText);
}
/// <summary>
/// Creates a SQL Statement for insert-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsertAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
int batchSize = Constant.DefaultBatchOperationSize,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
var commandText = base.CreateInsertAll(builder,
tableName,
fields,
batchSize,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToDB2iSeriesStringNameResolver().Resolve(dbType.Value);
}
}
if (identityField != null)
{
// Variables needed
var commandTexts = new List <string>();
var splitted = commandText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
// Iterate the indexes
for (var index = 0; index < splitted.Count(); index++)
{
var line = splitted[index].Trim();
var returnValue = string.IsNullOrEmpty(databaseType) ?
"SELECT SCOPE_IDENTITY()" :
$"SELECT CONVERT({databaseType}, SCOPE_IDENTITY())";
commandTexts.Add(string.Concat(line, " ; ", returnValue, " ;"));
}
// Set the command text
commandText = commandTexts.Join(" ");
}
// Return the query
return(commandText);
}
/// <summary>
/// Creates a SQL Statement for insert-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsertAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
int batchSize = 1,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
var commandText = base.CreateInsertAll(builder,
tableName,
fields,
batchSize,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqLiteStringNameResolver().Resolve(dbType.Value);
}
}
if (identityField != null)
{
// Variables needed
var commandTexts = new List <string>();
var splitted = commandText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
// Iterate the indexes
for (var index = 0; index < splitted.Length; index++)
{
var line = splitted[index].Trim();
var returnValue = string.IsNullOrEmpty(databaseType) ?
"SELECT last_insert_rowid()" :
$"SELECT CAST(last_insert_rowid() AS {databaseType}) AS [Id]";
commandTexts.Add(string.Concat(line, " ; ", returnValue, $", {DbSetting.ParameterPrefix}__RepoDb_OrderColumn_{index} AS [OrderColumn] ;"));
}
// Set the command text
commandText = commandTexts.Join(" ");
}
// Return the query
return(commandText);
}
/// <summary>
/// Creates a SQL Statement for insert operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsert(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
base.CreateInsert(builder,
tableName,
fields,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToPostgreSqlStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField != null?
string.IsNullOrEmpty(databaseType) ?
identityField.Name.AsQuoted(DbSetting) :
string.Concat($"CAST({identityField.Name.AsQuoted(DbSetting)} AS {databaseType})") :
primaryField != null?primaryField.Name.AsQuoted(DbSetting) : "NULL";
// Get the string
var sql = builder.GetString().Trim();
// Append the result
sql = string.Concat(sql.Substring(0, sql.Length - 1),
"RETURNING ", result, " AS ", "Result".AsQuoted(DbSetting), " ;");
// Return the query
return(sql);
}
/// <summary>
/// Creates a SQL Statement for insert operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsert(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
base.CreateInsert(builder,
tableName,
fields,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = "BIGINT";
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToDB2iSeriesStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField != null?
string.Concat("CONVERT(", databaseType, ", SCOPE_IDENTITY())") :
primaryField != null?primaryField.Name.AsParameter(DbSetting) : "NULL";
builder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(builder.GetString());
}
/// <summary>
/// Creates a SQL Statement for insert operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for insert operation.</returns>
public override string CreateInsert(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Call the base
base.CreateInsert(builder,
tableName,
fields,
primaryField,
identityField,
hints);
// Variables needed
var databaseType = "BIGINT";
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqLiteStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField != null ?
$"CAST(last_insert_rowid() AS {databaseType})" :
primaryField != null?primaryField.Name.AsParameter(DbSetting) : "NULL";
builder
.Select()
.WriteText(result)
.As("Result".AsQuoted(DbSetting))
.End();
// Return the query
return(builder.GetString());
}
public static Expression GetPlainTypeToDbParametersForNonEnumCompiledFunction(Expression commandParameterExpression,
ClassProperty paramProperty,
ClassProperty entityProperty,
DbField dbField,
Type valueType,
Expression valueExpression)
{
// Automatic
if (Converter.ConversionType == ConversionType.Automatic && dbField?.Type != null)
{
valueType = dbField.Type.GetUnderlyingType();
valueExpression = ConvertExpressionWithAutomaticConversion(valueExpression, valueType);
}
// DbType
var dbType = (paramProperty == null ? null : TypeMapCache.Get(paramProperty.GetDeclaringType(), paramProperty.PropertyInfo)) ??
(entityProperty == null ? null : TypeMapCache.Get(entityProperty.GetDeclaringType(), entityProperty.PropertyInfo));
valueType ??= dbField?.Type.GetUnderlyingType();
if (dbType == null && valueType != null)
{
var resolver = new ClientTypeToDbTypeResolver();
dbType =
valueType.GetDbType() ?? // type level, use TypeMapCache
resolver.Resolve(valueType) ?? // type level, primitive mapping
(dbField?.Type != null ?
resolver.Resolve(dbField?.Type) : null); // Fallback to the database type
}
var dbTypeExpression = dbType == null?GetNullableTypeExpression(StaticType.DbType) :
ConvertExpressionToNullableExpression(Expression.Constant(dbType), StaticType.DbType);
// DbCommandExtension.CreateParameter
var methodInfo = GetDbCommandCreateParameterMethod();
return(Expression.Call(methodInfo, new Expression[]
{
commandParameterExpression,
Expression.Constant(paramProperty.GetMappedName()),
ConvertExpressionToTypeExpression(valueExpression, StaticType.Object),
dbTypeExpression
}));
}
/// <summary>
/// Creates a SQL Statement for merge-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMergeAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers = null,
int batchSize = Constant.DefaultBatchOperationSize,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Check the qualifiers
if (qualifiers?.Any() == true)
{
// Check if the qualifiers are present in the given fields
var unmatchesQualifiers = qualifiers.Where(field =>
fields.FirstOrDefault(f =>
string.Equals(field.Name, f.Name, StringComparison.OrdinalIgnoreCase)) == null);
// Throw an error we found any unmatches
if (unmatchesQualifiers?.Any() == true)
{
throw new InvalidQualifiersException($"The qualifiers '{unmatchesQualifiers.Select(field => field.Name).Join(", ")}' are not " +
$"present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
}
}
else
{
if (primaryField != null)
{
// Make sure that primary is present in the list of fields before qualifying to become a qualifier
var isPresent = fields?.FirstOrDefault(f => string.Equals(f.Name, primaryField.Name, StringComparison.OrdinalIgnoreCase)) != null;
// Throw if not present
if (isPresent == false)
{
throw new InvalidQualifiersException($"There are no qualifier field objects found for '{tableName}'. Ensure that the " +
$"primary field is present at the given fields '{fields.Select(field => field.Name).Join(", ")}'.");
}
// The primary is present, use it as a default if there are no qualifiers given
qualifiers = primaryField.AsField().AsEnumerable();
}
else
{
// Throw exception, qualifiers are not defined
throw new NullReferenceException($"There are no qualifier field objects found for '{tableName}'.");
}
}
// Get the insertable and updateable fields
var insertableFields = fields
.Where(field => !string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase));
var updateableFields = fields
.Where(field => !string.Equals(field.Name, primaryField?.Name, StringComparison.OrdinalIgnoreCase) &&
!string.Equals(field.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase));
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToDB2iSeriesStringNameResolver().Resolve(dbType.Value);
}
}
else if (primaryField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(primaryField.Type);
if (dbType != null)
{
databaseType = new DbTypeToDB2iSeriesStringNameResolver().Resolve(dbType.Value);
}
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Build the query
builder.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
// MERGE T USING S
queryBuilder.Merge()
.TableNameFrom(tableName, DbSetting)
.As("T")
.HintsFrom(hints)
.Using()
.OpenParen()
.Select()
.ParametersAsFieldsFrom(fields, index, DbSetting)
.CloseParen()
.As("S")
// QUALIFIERS
.On()
.OpenParen()
.WriteText(qualifiers?
.Select(
field => field.AsJoinQualifier("S", "T", DbSetting))
.Join(" AND "))
.CloseParen()
// WHEN NOT MATCHED THEN INSERT VALUES
.When()
.Not()
.Matched()
.Then()
.Insert()
.OpenParen()
.FieldsFrom(insertableFields, DbSetting)
.CloseParen()
.Values()
.OpenParen()
.AsAliasFieldsFrom(insertableFields, "S", DbSetting)
.CloseParen()
// WHEN MATCHED THEN UPDATE SET
.When()
.Matched()
.Then()
.Update()
.Set()
.FieldsAndAliasFieldsFrom(updateableFields, "T", "S", DbSetting);
// Set the output
var outputField = identityField ?? primaryField;
if (outputField != null)
{
queryBuilder
.WriteText(string.Concat("OUTPUT INSERTED.", outputField.Name.AsField(DbSetting)))
.As("[Result]");
}
// End the builder
queryBuilder.End();
}
// Return the query
return(builder.GetString());
}
/// <summary>
/// Creates a SQL Statement for merge-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMergeAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
int batchSize = 10,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Check the primay field
if (primaryField == null)
{
throw new PrimaryFieldNotFoundException($"DB2iSeries_SqLite is using the primary key as qualifier for (INSERT or REPLACE) operation.");
}
// Check the qualifiers
if (qualifiers?.Any() == true)
{
var others = qualifiers.Where(f => !string.Equals(f.Name, primaryField?.Name, StringComparison.OrdinalIgnoreCase));
if (others?.Any() == true)
{
throw new InvalidQualifiersException($"DB2iSeries_SqLite is using the primary key as qualifier for (INSERT or REPLACE) operation. " +
$"Consider creating 'PrimaryKey' in the {tableName} and set the 'qualifiers' to NULL.");
}
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Variables needed
var databaseType = "BIGINT";
// Set the return value
var result = (string)null;
// Set the type
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToDB2iSeriesStringNameResolver().Resolve(dbType.Value);
}
}
// Clear the builder
builder.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
// Build the query
builder
.Insert()
.Or()
.Replace()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(fields, DbSetting)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(fields, index, DbSetting)
.CloseParen()
.End();
// Check both primary and identity
if (identityField != null)
{
result = string.Concat($"CAST(COALESCE(last_insert_rowid(), {primaryField.Name.AsParameter(index, DbSetting)}) AS {databaseType})");
}
else
{
result = string.Concat($"CAST({primaryField.Name.AsParameter(index, DbSetting)} AS {databaseType})");
}
if (!string.IsNullOrEmpty(result))
{
// Set the result
builder
.Select()
.WriteText(result)
.As("Result".AsQuoted(DbSetting))
.End();
}
}
// Return the query
return(builder.GetString());
}
/// <summary>
/// Creates a SQL Statement for merge-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMergeAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers,
int batchSize = 10,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Check the primay field
if (primaryField == null)
{
throw new PrimaryFieldNotFoundException($"PostgreSql is using the primary key as qualifier for (INSERT or REPLACE) operation.");
}
// Check the qualifiers
if (qualifiers?.Any() == true)
{
var others = qualifiers.Where(f => !string.Equals(f.Name, primaryField?.Name, StringComparison.OrdinalIgnoreCase));
if (others?.Any() == true)
{
throw new InvalidQualifiersException($"PostgreSql is using the primary key as qualifier for (INSERT or REPLACE) operation. " +
$"Consider creating 'PrimaryKey' in the {tableName} and set the 'qualifiers' to NULL.");
}
}
// Set the qualifiers
if (qualifiers?.Any() != true)
{
qualifiers = primaryField.AsField().AsEnumerable();
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Remove the qualifers from the fields
var updatableFields = fields
.Where(f =>
qualifiers?.Any(qf => string.Equals(qf.Name, f.Name, StringComparison.OrdinalIgnoreCase)) != true)
.AsList();
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToPostgreSqlStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField != null?
string.IsNullOrEmpty(databaseType) ?
identityField.Name.AsQuoted(DbSetting) :
string.Concat($"CAST({identityField.Name.AsQuoted(DbSetting)} AS {databaseType})") :
primaryField != null?primaryField.Name.AsParameter(DbSetting) : "NULL";
// Clear the builder
builder.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
// Build the query
builder
.Insert()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(fields, DbSetting)
.CloseParen();
// Override the system value
if (identityField != null)
{
builder.WriteText("OVERRIDING SYSTEM VALUE");
}
// Continue
builder
.Values()
.OpenParen()
.ParametersFrom(fields, index, DbSetting)
.CloseParen()
.OnConflict(qualifiers, DbSetting)
.DoUpdate()
.Set()
.FieldsAndParametersFrom(updatableFields, index, DbSetting);
if (!string.IsNullOrEmpty(result))
{
// Get the string
var sql = string.Concat("RETURNING ", result, " AS ", "Result".AsQuoted(DbSetting));
// Set the result
builder
.WriteText(sql);
}
// End the builder
builder.End();
}
// Return the query
return(builder.GetString());
}
/// <summary>
/// Creates a SQL Statement for merge operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be merged.</param>
/// <param name="qualifiers">The list of the qualifier <see cref="Field"/> objects.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <param name="hints">The table hints to be used.</param>
/// <returns>A sql statement for merge operation.</returns>
public override string CreateMerge(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields,
IEnumerable <Field> qualifiers = null,
DbField primaryField = null,
DbField identityField = null,
string hints = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardHints(hints);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Set the qualifiers
if (qualifiers?.Any() != true && primaryField != null)
{
qualifiers = primaryField.AsField().AsEnumerable();
}
// Validate the qualifiers
if (qualifiers?.Any() != true)
{
if (primaryField == null)
{
throw new PrimaryFieldNotFoundException($"The is no primary field from the table '{tableName}' that can be used as qualifier.");
}
else
{
throw new InvalidQualifiersException($"There are no defined qualifier fields.");
}
}
// Initialize the builder
var builder = queryBuilder ?? new QueryBuilder();
// Remove the qualifiers from the fields
var updatableFields = fields
.Where(f =>
qualifiers?.Any(qf => string.Equals(qf.Name, f.Name, StringComparison.OrdinalIgnoreCase)) != true)
.AsList();
// Build the query
builder.Clear()
.Insert()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(fields, DbSetting)
.CloseParen();
// Override the system value
if (identityField != null)
{
builder.WriteText("OVERRIDING SYSTEM VALUE");
}
// Continue
builder
.Values()
.OpenParen()
.ParametersFrom(fields, 0, DbSetting)
.CloseParen()
.OnConflict(qualifiers, DbSetting)
.DoUpdate()
.Set()
.FieldsAndParametersFrom(updatableFields, 0, DbSetting);
// Variables needed
var databaseType = (string)null;
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToPostgreSqlStringNameResolver().Resolve(dbType.Value);
}
}
// Set the return value
var result = identityField == null?primaryField.Name.AsParameter(DbSetting) :
string.IsNullOrEmpty(databaseType) ? identityField.Name.AsQuoted(DbSetting) :
$"CAST({identityField.Name.AsQuoted(DbSetting)} AS {databaseType})";
if (!string.IsNullOrEmpty(result))
{
// Get the string
var sql = string.Concat("RETURNING ", result, " AS ", "Result".AsQuoted(DbSetting));
// Set the result
builder
.WriteText(sql);
}
// End the builder
builder.End();
// Return the query
return(builder.GetString());
}
/// <summary>
///
/// </summary>
/// <param name="paramType"></param>
/// <param name="entityType"></param>
/// <param name="dbFields"></param>
/// <returns></returns>
internal static Action <DbCommand, object> GetPlainTypeToDbParametersCompiledFunction(Type paramType,
Type entityType,
IEnumerable <DbField> dbFields = null)
{
var commandParameterExpression = Expression.Parameter(StaticType.DbCommand, "command");
var entityParameterExpression = Expression.Parameter(StaticType.Object, "entity");
var entityExpression = ConvertExpressionToTypeExpression(entityParameterExpression, paramType);
var methodInfo = GetDbCommandCreateParameterMethod();
var callExpressions = new List <Expression>();
var paramProperties = PropertyCache.Get(paramType);
var entityProperties = PropertyCache.Get(entityType);
// Iterate
foreach (var paramProperty in paramProperties)
{
// Ensure it matching any params
var entityProperty = entityProperties?.FirstOrDefault(e =>
string.Equals(e.GetMappedName(), paramProperty.GetMappedName(), StringComparison.OrdinalIgnoreCase) ||
string.Equals(e.PropertyInfo.Name, paramProperty.PropertyInfo.Name, StringComparison.OrdinalIgnoreCase));
// Variables
var dbField = dbFields?.FirstOrDefault(df =>
string.Equals(df.Name, paramProperty.GetMappedName(), StringComparison.OrdinalIgnoreCase));
var valueExpression = (Expression)Expression.Property(entityExpression, paramProperty.PropertyInfo);
var targetProperty = (entityProperty ?? paramProperty);
var valueType = targetProperty.PropertyInfo.PropertyType.GetUnderlyingType();
// Enum
if (targetProperty.PropertyInfo.PropertyType.IsEnum && dbField != null)
{
valueExpression = ConvertEnumExpressionToTypeExpression(valueExpression, dbField.Type);
}
// PropertyHandler
var(convertedExpression, handlerSetReturnType) = ConvertExpressionToPropertyHandlerSetExpressionTuple(valueExpression, paramProperty, valueType);
if (handlerSetReturnType != null)
{
valueExpression = convertedExpression;
valueType = handlerSetReturnType;
}
// Automatic
if (Converter.ConversionType == ConversionType.Automatic && dbField?.Type != null)
{
valueType = dbField.Type.GetUnderlyingType();
valueExpression = ConvertExpressionWithAutomaticConversion(valueExpression, valueType);
}
// DbType
var dbType =
(paramProperty == null ? null : TypeMapCache.Get(paramProperty.GetDeclaringType(), paramProperty.PropertyInfo)) ??
(entityProperty == null ? null : TypeMapCache.Get(entityProperty.GetDeclaringType(), entityProperty.PropertyInfo));
if (dbType == null && (valueType ??= dbField?.Type.GetUnderlyingType()) != null)
{
var resolver = new ClientTypeToDbTypeResolver();
dbType =
valueType.GetDbType() ?? // type level, use TypeMapCache
resolver.Resolve(valueType) ?? // type level, primitive mapping
(valueType.IsEnum ?
(dbField?.Type != null ? resolver.Resolve(dbField.Type) : null) ?? // use the DBField.Type
Converter.EnumDefaultDatabaseType : null); // use Converter.EnumDefaultDatabaseType
}
var dbTypeExpression = dbType == null?GetNullableTypeExpression(StaticType.DbType) :
ConvertExpressionToNullableExpression(Expression.Constant(dbType), StaticType.DbType);
// DbCommandExtension.CreateParameter
var expression = Expression.Call(methodInfo, new Expression[]
{
commandParameterExpression,
Expression.Constant(paramProperty.GetMappedName()),
ConvertExpressionToTypeExpression(valueExpression, StaticType.Object),
dbTypeExpression
});
// DbCommand.Parameters.Add
var parametersExpression = Expression.Property(commandParameterExpression, "Parameters");
var addExpression = Expression.Call(parametersExpression, GetDbParameterCollectionAddMethod(), expression);
// Add
callExpressions.Add(addExpression);
}
// Return
return(Expression
.Lambda <Action <DbCommand, object> >(Expression.Block(callExpressions), commandParameterExpression, entityParameterExpression)
.Compile());
}
/// <summary>
/// Creates a SQL Statement for insert-all operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="batchSize">The batch size of the operation.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <returns>A sql statement for insert operation.</returns>
public string CreateInsertAll(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
int batchSize = Constant.DefaultBatchOperationSize,
DbField primaryField = null,
DbField identityField = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of fields cannot be null or empty.");
}
// Ensure the primary is on the list if it is not an identity
if (primaryField != null)
{
if (primaryField != identityField)
{
var isPresent = fields.FirstOrDefault(f => string.Equals(f.Name, primaryField.Name, StringComparison.OrdinalIgnoreCase)) != null;
if (isPresent == false)
{
throw new InvalidOperationException("The non-identity primary field must be present during insert operation.");
}
}
}
// Variables needed
var databaseType = (string)null;
var insertableFields = fields
.Where(f => !string.Equals(f.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase));
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqlServerStringNameResolver().Resolve(dbType.Value);
}
}
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear();
// Iterate the indexes
for (var index = 0; index < batchSize; index++)
{
queryBuilder.Insert()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(insertableFields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(insertableFields, index, DbSetting)
.CloseParen()
.End();
// Set the return field
if (identityField != null)
{
var returnValue = string.Concat(identityField.UnquotedName.AsParameter(index, DbSetting), " = ",
string.IsNullOrEmpty(databaseType) ?
"SCOPE_IDENTITY()" :
"CONVERT(", databaseType, ", SCOPE_IDENTITY())");
queryBuilder
.Set()
.WriteText(returnValue)
.End();
}
}
// Return the query
return(queryBuilder.GetString());
}
/// <summary>
/// Creates a SQL Statement for insert operation.
/// </summary>
/// <param name="queryBuilder">The query builder to be used.</param>
/// <param name="tableName">The name of the target table.</param>
/// <param name="fields">The list of fields to be inserted.</param>
/// <param name="primaryField">The primary field from the database.</param>
/// <param name="identityField">The identity field from the database.</param>
/// <returns>A sql statement for insert operation.</returns>
public string CreateInsert(QueryBuilder queryBuilder,
string tableName,
IEnumerable <Field> fields = null,
DbField primaryField = null,
DbField identityField = null)
{
// Ensure with guards
GuardTableName(tableName);
GuardPrimary(primaryField);
GuardIdentity(identityField);
// Verify the fields
if (fields?.Any() != true)
{
throw new NullReferenceException($"The list of insertable fields must not be null for '{tableName}'.");
}
// Ensure the primary is on the list if it is not an identity
if (primaryField != null)
{
if (primaryField != identityField)
{
var isPresent = fields.FirstOrDefault(f => string.Equals(f.Name, primaryField.Name, StringComparison.OrdinalIgnoreCase)) != null;
if (isPresent == false)
{
throw new InvalidOperationException("The non-identity primary field must be present during insert operation.");
}
}
}
// Variables needed
var databaseType = "BIGINT";
var insertableFields = fields
.Where(f => !string.Equals(f.Name, identityField?.Name, StringComparison.OrdinalIgnoreCase));
// Check for the identity
if (identityField != null)
{
var dbType = new ClientTypeToDbTypeResolver().Resolve(identityField.Type);
if (dbType != null)
{
databaseType = new DbTypeToSqlServerStringNameResolver().Resolve(dbType.Value);
}
}
// Build the query
(queryBuilder ?? new QueryBuilder())
.Clear()
.Insert()
.Into()
.TableNameFrom(tableName, DbSetting)
.OpenParen()
.FieldsFrom(insertableFields)
.CloseParen()
.Values()
.OpenParen()
.ParametersFrom(insertableFields, 0, DbSetting)
.CloseParen()
.End();
// Set the return value
var result = identityField != null?
string.Concat("CONVERT(", databaseType, ", SCOPE_IDENTITY())") :
primaryField != null?primaryField.Name.AsParameter(DbSetting) : "NULL";
queryBuilder
.Select()
.WriteText(result)
.As("[Result]")
.End();
// Return the query
return(queryBuilder.GetString());
}
