public static void AddParams(this DbCommand command, object param)
{
_ = command ?? throw new ArgumentNullException($"{nameof(command)} must not be null!");
_ = param ?? throw new ArgumentNullException($"{nameof(param)} must not be null!");
var properties = AttributeParser.GetAllProperties(param.GetType()).ToList();
foreach (var propertyInfo in properties)
{
object value = null;
if (AttributeParser.HasForeignKey(propertyInfo))
{
value = AttributeParser.GetKey(propertyInfo.PropertyType)
.GetValue(propertyInfo.GetValue(param));
}
// Enum or Nullable<Enum>
else if (propertyInfo.PropertyType.IsEnum ||
propertyInfo.PropertyType.IsGenericType &&
propertyInfo.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>) &&
propertyInfo.PropertyType.GetGenericArguments()[0].IsEnum &&
propertyInfo.GetValue(param) != null)
{
value = propertyInfo.GetValue(param).ToString();
}
command.AddParam(propertyInfo.Name, value ?? propertyInfo.GetValue(param));
}
}
private IList <string> GetDeleteRelatedEntityDeleteSql(DbCommand cmd, RecordHierarchy record)
{
// {0} - Foreign table
// {1} - Primary key
// {2} - Key value
const string deleteFormat = "DELETE FROM {0} WHERE {1} = @{2};";
var paramIndex = cmd.Parameters.Count;
cmd.AddParam(record.KeyValue);
var sql = deleteFormat.Fill(
record.Entity.TableName,
record.Entity.Key.ColumnName,
paramIndex);
var sqls = new List <string>()
{
sql
};
foreach (var subRecord in record.SubRecordsHierarchies)
{
sqls.AddRange(GetDeleteRelatedEntityDeleteSql(cmd, subRecord));
}
return(sqls);
}
/// <summary>
/// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
/// </summary>
public virtual DbCommand CreateInsertCommand(object o)
{
DbCommand result = null;
var expando = o.ToExpando();
var settings = (IDictionary <string, object>)expando;
var sbKeys = new StringBuilder();
var sbVals = new StringBuilder();
const string stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
result = CreateCommand(stub, null);
int counter = 0;
foreach (var item in settings)
{
sbKeys.AppendFormat("{0},", item.Key);
sbVals.AppendFormat("@{0},", counter.ToString());
result.AddParam(item.Value);
counter++;
}
if (counter > 0)
{
var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
result.CommandText = string.Format(stub, TableName, keys, vals);
}
else
{
throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
}
return(result);
}
public static Task <DbCommand> CreateCommandAsync(DbConnection conn, string commandText, CmdParams cmdParams)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DbCommand result = conn.CreateCommand();
result.CommandText = commandText;
result.CommandType = CommandType.Text;
if (cmdParams != null)
{
foreach (var param in cmdParams)
{
object value;
if (param.Value is Guid)
{
value = ((Guid)param.Value).ToGuidStr();
}
else if (param.Value is bool)
{
value = ((bool)param.Value).ToShort();
}
else
{
value = param.Value;
}
result.AddParam(param.Key, value);
}
}
return(Task.FromResult(result));
}
/// <summary>
/// Creates a command for use with transactions - internal stuff mostly, but here for you to play with
/// </summary>
public DbCommand CreateInsertCommand(object o)
{
DbCommand result = null;
//turn this into an expando - we'll need that for the validators
var expando = o.ToExpando();
var settings = (IDictionary <string, object>)expando;
var sbKeys = new StringBuilder();
var sbVals = new StringBuilder();
var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2}); \r\nSELECT SCOPE_IDENTITY()";
result = CreateCommand(stub);
int counter = 0;
foreach (var item in settings)
{
sbKeys.AppendFormat("{0},", item.Key);
sbVals.AppendFormat("@{0},", counter.ToString());
result.AddParam(item.Value);
counter++;
}
if (counter > 0)
{
//strip off trailing commas
var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
var sql = string.Format(stub, TableName, keys, vals);
result.CommandText = sql;
}
else
{
throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
}
return(result);
}
private string GetSetToNullUpdateSql(DbCommand cmd, Entity entity, RecordHierarchy subRecord)
{
// {0} - Foreign table
// {1} - Foreign key
// {2} - Primary key
// {3} - Key value
const string updateFormat = "UPDATE {0} SET {1} = @{2} WHERE {3};";
//UPDATE Products SET CategoryID = null WHERE ProductID = 7
var foreignTable = subRecord.Entity.TableName;
var foreignKey = subRecord.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == entity).ColumnName;
var nullIndex = cmd.Parameters.Count;
var paramIndex = nullIndex + 1;
cmd.AddParam(null);
cmd.AddParams(subRecord.KeyValue.ToArray());
var whereParts = new List <string>();
foreach (var key in subRecord.Entity.Key)
{
whereParts.Add("{0} = @{1}".Fill(key.ColumnName, paramIndex++));
}
var wherePart = string.Join(" AND ", whereParts);
var updateSql = updateFormat.Fill(
foreignTable,
foreignKey,
nullIndex,
wherePart);
return(updateSql);
}
public static DbCommand CreateCommand(DbConnection conn, string commandText, ComplexParams cmdParams)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DbCommand result = conn.CreateCommand();
result.CommandText = commandText;
result.CommandType = CommandType.Text;
if (cmdParams != null)
{
foreach (ComplexParameter param in cmdParams)
{
object value;
if (param.Value is Guid)
{
value = ((Guid)param.Value).ToGuidStr();
}
else if (param.Value is bool)
{
value = ((bool)param.Value).ToShort();
}
else
{
value = param.Value;
}
result.AddParam(param.Key, value, param.DbType);
}
}
return(result);
}
public virtual DbCommand CreateInsertCommand(T insertItem)
{
DbCommand result = null;
var expando = insertItem.ToExpando();
var settings = (IDictionary <string, object>)expando;
var sbKeys = new StringBuilder();
var sbVals = new StringBuilder();
var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
result = this.CreateCommand(stub, null);
int counter = 0;
var autoPkColumn = this.TableMapping.PrimaryKeyMapping.FirstOrDefault(c => c.IsAutoIncementing == true);
if (autoPkColumn != null)
{
settings.Remove(autoPkColumn.PropertyName);
}
//if (!this.TableMapping.HasCompoundPk && this.TableMapping.PrimaryKeyMapping[0].IsAutoIncementing) {
// string mappedPropertyName = this.TableMapping.PrimaryKeyMapping[0].PropertyName;
// var col = settings.FirstOrDefault(x => x.Key.Equals(mappedPropertyName, StringComparison.OrdinalIgnoreCase));
// settings.Remove(col);
//}
foreach (var item in settings)
{
sbKeys.AppendFormat("{0},", this.TableMapping.ColumnMappings.FindByProperty(item.Key).DelimitedColumnName);
//this is a special case for a search directive
//TODO: This will need to be handled differently if SQL Server FTS is implemented:
if (item.Value.ToString().StartsWith("to_tsvector"))
{
sbVals.AppendFormat("{0},", item.Value);
}
else
{
sbVals.AppendFormat("@{0},", counter.ToString());
result.AddParam(item.Value);
}
counter++;
}
if (counter > 0)
{
var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
var sql = string.Format(stub, this.TableMapping.DelimitedTableName, keys, vals);
result.CommandText = sql;
}
else
{
throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
}
return(result);
}
private static void AddParam(DbCommand cmd, Property property)
{
if (property.TypeInfo.IsFileStoredInDb)
cmd.AddParam(property.Value.Raw, DbType.Binary);
else
{
if (property.Value.Raw.IsBehavior(DefaultValueBehavior.Now) ||
property.Value.Raw.IsBehavior(DefaultValueBehavior.NowOnCreate))
{
cmd.AddParam(DateTime.Now);
}
else if (property.Value.Raw.IsBehavior(DefaultValueBehavior.UtcNow) ||
property.Value.Raw.IsBehavior(DefaultValueBehavior.UtcNowOnCreate))
{
cmd.AddParam(DateTime.UtcNow);
}
else
{
cmd.AddParam(property.Value.Raw);
}
}
}
private void AddForeignsUpdate(DbCommand cmd, Entity entity)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var property in entity.GetForeignsForUpdate())
{
var actualRecords = _source.GetRecords(
property.ForeignEntity,
new List <IEntityFilter>
{
new ForeignEntityFilter(
entity.Key,
entity.Key.Value.Raw.ToStringSafe())
});
var idsToRemoveRelation = actualRecords
.Select(x => x.KeyValue)
.Except(property.Value.Values.OfType <string>())
.ToList();
if (idsToRemoveRelation.Any())
{
var removeValues = string.Join(",", idsToRemoveRelation.Select(x => "@" + paramIndex++));
sbUpdates.AppendLine();
sbUpdates.AppendFormat(
RelatedRecordsUpdateSqlFormat,
property.ForeignEntity.TableName,
entity.Key.ColumnName,
paramIndex++,
property.ForeignEntity.Key.ColumnName,
removeValues);
cmd.AddParams(idsToRemoveRelation);
cmd.AddParams(null);
}
var values = string.Join(",", property.Value.Values.Select(x => "@" + paramIndex++));
cmd.CommandText +=
Environment.NewLine +
RelatedRecordsUpdateSqlFormat.Fill(
property.ForeignEntity.TableName,
entity.Key.ColumnName,
paramIndex++,
property.ForeignEntity.Key.ColumnName,
values);
cmd.AddParams(property.Value.Values.ToArray());
cmd.AddParam(entity.Key.Value.Raw);
}
}
DbCommand CreateInsertCommand(T insertItem)
{
DbCommand result = null;
var expando = insertItem.ToExpando();
var settings = (IDictionary <string, object>)expando;
var sbKeys = new StringBuilder();
var sbVals = new StringBuilder();
var stub = "INSERT INTO {0} ({1}) \r\n VALUES ({2})";
result = CreateCommand(stub, null);
int counter = 0;
if (PkIsIdentityColumn)
{
var col = settings.FirstOrDefault(x => x.Key.Equals(PrimaryKeyField, StringComparison.OrdinalIgnoreCase));
settings.Remove(col);
}
foreach (var item in settings)
{
sbKeys.AppendFormat("{0},", item.Key);
//this is a special case for a search directive
if (item.Value.ToString().StartsWith("to_tsvector"))
{
sbVals.AppendFormat("{0},", item.Value);
}
else
{
sbVals.AppendFormat("@{0},", counter.ToString());
result.AddParam(item.Value);
}
counter++;
}
if (counter > 0)
{
var keys = sbKeys.ToString().Substring(0, sbKeys.Length - 1);
var vals = sbVals.ToString().Substring(0, sbVals.Length - 1);
var sql = string.Format(stub, TableName, keys, vals);
result.CommandText = sql;
}
else
{
throw new InvalidOperationException("Can't parse this object to the database - there are no properties set");
}
return(result);
}
private void AddManyToManyForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
if (entityRecord.Keys.Count > 1)
{
return;
}
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany()
.Where(x => x.Property.IsManyToMany))
{
var selectedValues = propertyValue.Values.Select(x => x.ToStringSafe()).ToList();
var mtmEntity = GetEntityToLoad(propertyValue.Property);
var idsToAdd = selectedValues
.ToList();
if (idsToAdd.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- add many to many records");
foreach (var idToAdd in idsToAdd)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(idToAdd);
sbUpdates.AppendLine($"INSERT INTO {foreignEntity.Table} ({key1.Column}, {key2.Column}) VALUES(@newID, @{paramIndex++})");
}
}
}
cmd.CommandText += Environment.NewLine + sbUpdates;
}
private void AddParam(DbCommand cmd, PropertyValue propertyValue)
{
if (propertyValue.Raw is ValueBehavior)
{
switch (propertyValue.Raw as ValueBehavior?)
{
case ValueBehavior.Now:
cmd.AddParam(DateTime.Now);
break;
case ValueBehavior.UtcNow:
cmd.AddParam(DateTime.UtcNow);
break;
case ValueBehavior.Guid:
cmd.AddParam(Guid.NewGuid());
break;
case ValueBehavior.CurrentUserId:
cmd.AddParam((int)_user.CurrentId());
break;
case ValueBehavior.CurrentUserName:
cmd.AddParam(_user.CurrentUserName());
break;
}
}
else
{
if (propertyValue.Property.TypeInfo.IsFileStoredInDb)
{
cmd.AddParam(propertyValue.Raw, DbType.Binary);
}
else
{
cmd.AddParam(propertyValue.Raw);
}
}
}
private void AddManyToManyForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
if (entityRecord.Keys.Count > 1)
{
return;
}
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany()
.Where(x => x.Property.IsManyToMany))
{
var recordKey = entityRecord.Keys.FirstOrDefault().AsString;
var actualRecords = _source.GetRecords(
propertyValue.Property.ForeignEntity,
new List <BaseFilter>
{
new ForeignEntityFilter(
entityRecord.Entity.Keys.FirstOrDefault(),
recordKey)
}).Records;
var selectedValues = propertyValue.Values.Select(x => x.ToStringSafe()).ToList();
var mtmEntity = GetEntityToLoad(propertyValue.Property);
var dbIds = actualRecords
.Select(x => x.Keys.FirstOrDefault(y => y.Property.ForeignEntity == mtmEntity).AsString)
.ToList();
var idsToRemove = dbIds
.Except(selectedValues)
.ToList();
if (idsToRemove.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- delete many to many records");
foreach (var idToRemove in idsToRemove)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(recordKey);
cmd.AddParam(idToRemove);
sbUpdates.AppendLine($"DELETE {foreignEntity.Table} WHERE {key1.Column} = @{paramIndex++} and {key2.Column} = @{paramIndex++}");
}
}
var idsToAdd = selectedValues
.Except(dbIds)
.ToList();
if (idsToAdd.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- add many to many records");
foreach (var idToAdd in idsToAdd)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(recordKey);
cmd.AddParam(idToAdd);
sbUpdates.AppendLine($"INSERT INTO {foreignEntity.Table} ({key1.Column}, {key2.Column}) VALUES(@{paramIndex++}, @{paramIndex++})");
}
}
}
cmd.CommandText += Environment.NewLine + sbUpdates;
}
private void AddForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
if (entityRecord.Keys.Count > 1)
{
return;
}
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany()
.Where(x => x.Property.IsManyToMany == false))
{
var actualRecords = _source.GetRecords(
propertyValue.Property.ForeignEntity,
new List <BaseFilter>
{
new ForeignEntityFilter(
entityRecord.Entity.Keys.FirstOrDefault(),
entityRecord.Keys.FirstOrDefault().Raw.ToStringSafe())
}).Records;
var idsToRemoveRelation = actualRecords
.Select(x => x.JoinedKeysValues)
.Except(propertyValue.Values.Select(x => x.ToStringSafe()))
.ToList();
if (idsToRemoveRelation.Any())
{
var values2 =
idsToRemoveRelation.Select(
x => x.Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts2 = new List <string>();
var addNullSqlPart = true;
for (int i = 0; i < propertyValue.Property.ForeignEntity.Keys.Count; i++)
{
var key = propertyValue.Property.ForeignEntity.Keys[i];
var joinedValues = string.Join(",", values2.Select(x => "@" + paramIndex++));
whereParts2.Add("{0} In ({1})".Fill(key.Column, joinedValues));
addNullSqlPart = joinedValues.HasValue();
if (addNullSqlPart == false)
{
break;
}
cmd.AddParams(values2.Select(x => x[i]).OfType <object>().ToArray());
}
if (addNullSqlPart)
{
var wherePart2 = string.Join(" AND ", whereParts2);
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- set to null update");
sbUpdates.AppendFormat(BuildForeignUpdateSql(
propertyValue.Property.ForeignEntity.Table,
entityRecord.Entity.Keys.FirstOrDefault().Column,
(paramIndex++).ToString(),
wherePart2));
cmd.AddParam(null);
}
}
var values =
propertyValue.Values.Select(
x => x.ToStringSafe().Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts = new List <string>();
var addSqlPart = true;
for (int i = 0; i < propertyValue.Property.ForeignEntity.Keys.Count; i++)
{
var key = propertyValue.Property.ForeignEntity.Keys[i];
var joinedValues = string.Join(",", values.Select(x => "@" + paramIndex++));
whereParts.Add("{0} In ({1})".Fill(key.Column, joinedValues));
addSqlPart = joinedValues.HasValue();
if (addSqlPart == false)
{
break;
}
cmd.AddParams(values.Select(x => x[i]).OfType <object>().ToArray());
}
if (addSqlPart)
{
var wherePart = string.Join(" AND ", whereParts);
sbUpdates.AppendLine();
sbUpdates.Append(BuildForeignUpdateSql(
propertyValue.Property.ForeignEntity.Table,
entityRecord.Entity.Keys.FirstOrDefault().Column,
(paramIndex++).ToString(),
wherePart));
cmd.AddParam(entityRecord.Keys.FirstOrDefault().Raw);
}
}
cmd.CommandText += Environment.NewLine + "-- update foreign entities records" + sbUpdates.ToString();
}
/// <summary>
/// A high-performance bulk-insert that can drop 10,000 documents in about 500ms
/// </summary>
public override int AddRange(List <T> items)
{
// These are SQL Server Max values:
const int MAGIC_SQL_PARAMETER_LIMIT = 2100;
const int MAGIC_SQL_ROW_VALUE_LIMIT = 1000;
int rowsAffected = 0;
var first = items.First();
// We need to add and remove an item to get the starting serial pk:
int nextSerialPk = 0;
if (this.PKIsIdentity)
{
// HACK: This is SO bad, but don't see ANY other way to do this:
nextSerialPk = this.getNextSerialPk(first);
}
string insertClause = "";
var sbSql = new StringBuilder("");
using (var connection = Model.OpenConnection()) {
using (var tdbTransaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead)) {
var commands = new List <DbCommand>();
// Lock the table, so nothing will disrupt the pk sequence:
string lockTableSQL = string.Format("SELECT 1 FROM {0} WITH(TABLOCKX) ", this.TableName);
DbCommand dbCommand = Model.CreateCommand(lockTableSQL, connection);
dbCommand.Transaction = tdbTransaction;
dbCommand.ExecuteNonQuery();
var paramCounter = 0;
var rowValueCounter = 0;
foreach (var item in items)
{
// Set the soon-to-be inserted serial int value:
if (this.PKIsIdentity)
{
var props = item.GetType().GetProperties();
var pk = props.First(p => p.Name == this.PrimaryKeyField);
pk.SetValue(item, nextSerialPk);
nextSerialPk++;
}
// Set the JSON object, including the interpolated serial PK
var itemEx = SetDataForDocument(item);
var itemSchema = itemEx as IDictionary <string, object>;
var sbParamGroup = new StringBuilder();
if (ReferenceEquals(item, first))
{
string stub = "INSERT INTO {0} ({1}) VALUES ";
insertClause = string.Format(stub, this.TableName, string.Join(", ", itemSchema.Keys));
sbSql = new StringBuilder(insertClause);
}
foreach (var key in itemSchema.Keys)
{
if (paramCounter + itemSchema.Count >= MAGIC_SQL_PARAMETER_LIMIT || rowValueCounter >= MAGIC_SQL_ROW_VALUE_LIMIT)
{
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
sbSql = new StringBuilder(insertClause);
paramCounter = 0;
rowValueCounter = 0;
dbCommand = Model.CreateCommand("", connection);
dbCommand.Transaction = tdbTransaction;
}
// FT SEARCH STUFF SHOULD GO HERE
sbParamGroup.AppendFormat("@{0},", paramCounter.ToString());
dbCommand.AddParam(itemSchema[key]);
paramCounter++;
}
// Add the row params to the end of the sql:
sbSql.AppendFormat("({0}),", sbParamGroup.ToString().Substring(0, sbParamGroup.Length - 1));
rowValueCounter++;
}
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
try {
foreach (var cmd in commands)
{
rowsAffected += cmd.ExecuteNonQuery();
}
tdbTransaction.Commit();
}
catch (Exception) {
tdbTransaction.Rollback();
}
}
}
this.Reload();
return(rowsAffected);
}
private void AddForeignsUpdate(DbCommand cmd, Entity entity)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var property in entity.GetForeignsForUpdate())
{
var actualRecords = _source.GetRecords(
property.ForeignEntity,
new List <BaseFilter>
{
new ForeignEntityFilter(
entity.Key.FirstOrDefault(),
entity.Key.FirstOrDefault().Value.Raw.ToStringSafe())
}).Records;
var idsToRemoveRelation = actualRecords
.Select(x => x.JoinedKeyValue)
.Except(property.Value.Values.Select(x => x.ToStringSafe()))
.ToList();
if (idsToRemoveRelation.Any())
{
var values2 =
idsToRemoveRelation.Select(
x => x.Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts2 = new List <string>();
for (int i = 0; i < property.ForeignEntity.Key.Count; i++)
{
var key = property.ForeignEntity.Key[i];
var joinedValues = string.Join(",", values2.Select(x => "@" + paramIndex++));
whereParts2.Add("{0} In ({1})".Fill(key.ColumnName, joinedValues));
cmd.AddParams(values2.Select(x => x[i]).OfType <object>().ToArray());
}
var wherePart2 = string.Join(" AND ", whereParts2);
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- set to null update");
sbUpdates.AppendFormat(
RelatedRecordsUpdateSqlFormat,
property.ForeignEntity.TableName,
entity.Key.FirstOrDefault().ColumnName,
paramIndex++,
wherePart2);
cmd.AddParam(null);
}
var values =
property.Value.Values.Select(
x => x.ToStringSafe().Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts = new List <string>();
for (int i = 0; i < property.ForeignEntity.Key.Count; i++)
{
var key = property.ForeignEntity.Key[i];
var joinedValues = string.Join(",", values.Select(x => "@" + paramIndex++));
whereParts.Add("{0} In ({1})".Fill(key.ColumnName, joinedValues));
cmd.AddParams(values.Select(x => x[i]).OfType <object>().ToArray());
}
var wherePart = string.Join(" AND ", whereParts);
sbUpdates.AppendLine();
sbUpdates.AppendFormat(
RelatedRecordsUpdateSqlFormat,
property.ForeignEntity.TableName,
entity.Key.FirstOrDefault().ColumnName,
paramIndex++,
wherePart);
cmd.AddParam(entity.Key.FirstOrDefault().Value.Raw);
}
cmd.CommandText += sbUpdates.ToString();
}
/// <summary>
/// Inserts a large range - does not check for existing entires, and assumes all
/// included records are new records. Order of magnitude more performant than standard
/// Insert method for multiple sequential inserts.
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public virtual List <T> BulkInsert(List <T> items)
{
int rowsAffected = 0;
const int MAGIC_PG_PARAMETER_LIMIT = 2100;
const int MAGIC_PG_ROW_VALUE_LIMIT = 1000;
var first = items.First();
string insertClause = "";
var sbSql = new StringBuilder("");
using (var conn = Cache.OpenConnection()) {
using (var transaction = conn.BeginTransaction()) {
var commands = new List <DbCommand>();
DbCommand dbCommand = conn.CreateCommand();
dbCommand.Transaction = transaction;
var paramCounter = 0;
var rowValueCounter = 0;
foreach (var item in items)
{
var itemEx = item.ToExpando();
var itemSchema = itemEx as IDictionary <string, object>;
var sbParamGroup = new StringBuilder();
if (this.PrimaryKeyMapping.IsAutoIncementing)
{
// Don't insert against a serial id:
string mappedPkPropertyName = this.PrimaryKeyMapping.PropertyName;
string key = itemSchema.Keys.First(k => k.ToString().Equals(mappedPkPropertyName, StringComparison.OrdinalIgnoreCase));
itemSchema.Remove(key);
}
// Build the first part of the INSERT, including delimited column names:
if (ReferenceEquals(item, first))
{
var sbFieldNames = new StringBuilder();
foreach (var field in itemSchema)
{
string mappedColumnName = this.tableMapping.ColumnMappings.FindByProperty(field.Key).DelimitedColumnName;
sbFieldNames.AppendFormat("{0},", mappedColumnName);
}
var keys = sbFieldNames.ToString().Substring(0, sbFieldNames.Length - 1);
string stub = "INSERT INTO {0} ({1}) VALUES ";
insertClause = string.Format(stub, this.tableMapping.DelimitedTableName, string.Join(", ", keys));
sbSql = new StringBuilder(insertClause);
}
foreach (var key in itemSchema.Keys)
{
if (paramCounter + itemSchema.Count >= MAGIC_PG_PARAMETER_LIMIT || rowValueCounter >= MAGIC_PG_ROW_VALUE_LIMIT)
{
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
sbSql = new StringBuilder(insertClause);
paramCounter = 0;
rowValueCounter = 0;
dbCommand = conn.CreateCommand();
dbCommand.Transaction = transaction;
}
// Add the Param groups to the end:
if (key == "search")
{
//TODO: This will need to be handled differently if SQL Server FTS is implemented:
sbParamGroup.AppendFormat("to_tsvector(@{0}),", paramCounter.ToString());
}
else
{
sbParamGroup.AppendFormat("@{0},", paramCounter.ToString());
}
dbCommand.AddParam(itemSchema[key]);
paramCounter++;
}
// Add the row params to the end of the sql:
sbSql.AppendFormat("({0}),", sbParamGroup.ToString().Substring(0, sbParamGroup.Length - 1));
rowValueCounter++;
}
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
try {
foreach (var cmd in commands)
{
rowsAffected += cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception) {
transaction.Rollback();
}
}
}
return(items);
}
/// <summary>
/// A high-performance bulk-insert that can drop 10,000 documents in about 500ms
/// </summary>
public override int AddRange(List <T> items)
{
//HACK: Refactor this to be prettier and also use a Transaction
const int MAGIC_PG_PARAMETER_LIMIT = 2100;
// ?? Unknown. Set this arbitrarily for now, haven't run into a limit yet.
const int MAGIC_PG_ROW_VALUE_LIMIT = 1000;
string stub = "INSERT INTO {0} ({1}) VALUES ";
var first = items.First();
var expando = this.SetDataForDocument(first);
var schema = expando as IDictionary <string, object>;
var keyColumn = schema.FirstOrDefault(x => x.Key.Equals(this.PrimaryKeyField, StringComparison.OrdinalIgnoreCase));
//HACK: I don't like this duplication here and below... we'll refactor at some point :)
if (this.Model.PkIsIdentityColumn)
{
//don't update the Primary Key
schema.Remove(keyColumn);
}
var insertClause = string.Format(stub, this.TableName, string.Join(", ", schema.Keys));
var sbSql = new StringBuilder(insertClause);
var paramCounter = 0;
var rowValueCounter = 0;
var commands = new List <DbCommand>();
var conn = Model.OpenConnection();
// Use the SAME connection, don't hit the pool for each command.
DbCommand dbCommand = Model.CreateCommand("", conn);
foreach (var item in items)
{
var itemEx = SetDataForDocument(item);
var itemSchema = itemEx as IDictionary <string, object>;
var sbParamGroup = new StringBuilder();
keyColumn = itemSchema.FirstOrDefault(x => x.Key.Equals(this.PrimaryKeyField, StringComparison.OrdinalIgnoreCase));
if (this.Model.PkIsIdentityColumn)
{
//don't update the Primary Key
itemSchema.Remove(keyColumn);
}
foreach (var key in itemSchema.Keys)
{
// Things explode if you exceed the param limit for pg:
if (paramCounter + schema.Count >= MAGIC_PG_PARAMETER_LIMIT || rowValueCounter >= MAGIC_PG_ROW_VALUE_LIMIT)
{
// Add the current command to the list, then start over with another:
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
sbSql = new StringBuilder(insertClause);
paramCounter = 0;
rowValueCounter = 0;
dbCommand = Model.CreateCommand("", conn);
}
sbParamGroup.AppendFormat("@{0},", paramCounter.ToString());
dbCommand.AddParam(itemSchema[key]);
paramCounter++;
}
// Add the row params to the end of the sql:
sbSql.AppendFormat("({0}),", sbParamGroup.ToString().Substring(0, sbParamGroup.Length - 1));
rowValueCounter++;
}
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
int rowsAffected = 0;
foreach (var cmd in commands)
{
rowsAffected += Model.Execute(cmd);
}
this.Reload();
return(rowsAffected);
}
/// <summary>
/// A high-performance bulk-insert that can drop 10,000 documents in about 500ms
/// </summary>
public override List <T> BulkInsert(List <T> items)
{
// These are SQL Server Max values:
const int MAGIC_SQL_PARAMETER_LIMIT = 2100;
const int MAGIC_SQL_ROW_VALUE_LIMIT = 1000;
int rowsAffected = 0;
var first = items.First();
string insertClause = "";
var sbSql = new StringBuilder("");
using (var connection = this.DbCache.OpenConnection()) {
using (var tdbTransaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead)) {
var commands = new List <DbCommand>();
// Lock the table, so nothing will disrupt the pk sequence:
string lockTableSQL = string.Format("SELECT 1 FROM {0} WITH(TABLOCKX) ", this.TableMapping.DelimitedTableName);
DbCommand dbCommand = this.Model.CreateCommand(lockTableSQL, connection);
dbCommand.Transaction = tdbTransaction;
dbCommand.ExecuteNonQuery();
int nextSerialPk = 0;
if (this.PrimaryKeyMapping.IsAutoIncementing)
{
// Now get the next Identity Id. ** Need to do this within the transaction/table lock scope **:
// NOTE: The application must have ownership permission on the table to do this!!
var sql_get_seq = string.Format("SELECT IDENT_CURRENT ('{0}' )", this.TableMapping.DelimitedTableName);
dbCommand.CommandText = sql_get_seq;
// if this is a fresh sequence, the "seed" value is returned. We will assume 1:
nextSerialPk = Convert.ToInt32(dbCommand.ExecuteScalar());
if (nextSerialPk > 1)
{
nextSerialPk++;
}
}
var paramCounter = 0;
var rowValueCounter = 0;
foreach (var item in items)
{
// Set the soon-to-be inserted serial int value:
if (this.PrimaryKeyMapping.IsAutoIncementing)
{
var props = item.GetType().GetProperties();
var pk = props.First(p => p.Name == this.PrimaryKeyMapping.PropertyName);
pk.SetValue(item, nextSerialPk);
nextSerialPk++;
}
// Set the JSON object, including the interpolated serial PK
var itemEx = SetDataForDocument(item);
var itemSchema = itemEx as IDictionary <string, object>;
var sbParamGroup = new StringBuilder();
if (itemSchema.ContainsKey(this.PrimaryKeyMapping.PropertyName) && this.PrimaryKeyMapping.IsAutoIncementing)
{
itemSchema.Remove(this.PrimaryKeyMapping.PropertyName);
}
if (ReferenceEquals(item, first))
{
var sbFieldNames = new StringBuilder();
foreach (var field in itemSchema)
{
string mappedColumnName = this.TableMapping.ColumnMappings.FindByProperty(field.Key).DelimitedColumnName;
sbFieldNames.AppendFormat("{0},", mappedColumnName);
}
var delimitedColumnNames = sbFieldNames.ToString().Substring(0, sbFieldNames.Length - 1);
string stub = "INSERT INTO {0} ({1}) VALUES ";
insertClause = string.Format(stub, this.TableMapping.DelimitedTableName, string.Join(", ", delimitedColumnNames));
sbSql = new StringBuilder(insertClause);
}
foreach (var key in itemSchema.Keys)
{
if (paramCounter + itemSchema.Count >= MAGIC_SQL_PARAMETER_LIMIT || rowValueCounter >= MAGIC_SQL_ROW_VALUE_LIMIT)
{
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
sbSql = new StringBuilder(insertClause);
paramCounter = 0;
rowValueCounter = 0;
dbCommand = this.Model.CreateCommand("", connection);
dbCommand.Transaction = tdbTransaction;
}
// FT SEARCH STUFF SHOULD GO HERE
sbParamGroup.AppendFormat("@{0},", paramCounter.ToString());
dbCommand.AddParam(itemSchema[key]);
paramCounter++;
}
// Add the row params to the end of the sql:
sbSql.AppendFormat("({0}),", sbParamGroup.ToString().Substring(0, sbParamGroup.Length - 1));
rowValueCounter++;
}
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
try {
foreach (var cmd in commands)
{
rowsAffected += cmd.ExecuteNonQuery();
}
tdbTransaction.Commit();
}
catch (Exception) {
tdbTransaction.Rollback();
}
}
}
return(items);
}
/// <summary>
/// A high-performance bulk-insert that can drop 10,000 documents in about 900 ms
/// </summary>
public override List <T> BulkInsert(List <T> items)
{
const int MAGIC_PG_PARAMETER_LIMIT = 2100;
const int MAGIC_PG_ROW_VALUE_LIMIT = 1000;
int rowsAffected = 0;
var first = items.First();
string insertClause = "";
var sbSql = new StringBuilder("");
using (var connection = this.DbCache.OpenConnection()) {
using (var tdbTransaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead)) {
var commands = new List <DbCommand>();
// Lock the table, so nothing will disrupt the pk sequence:
string lockTableSQL = string.Format("LOCK TABLE {0} in ACCESS EXCLUSIVE MODE", this.TableMapping.DelimitedTableName);
DbCommand dbCommand = this.Model.CreateCommand(lockTableSQL, connection);
dbCommand.Transaction = tdbTransaction;
dbCommand.ExecuteNonQuery();
var autoPkColumn = this.TableMapping.PrimaryKeyMapping.FirstOrDefault(c => c.IsAutoIncementing == true);
int nextSerialPk = 0;
if (autoPkColumn != null)
{
// Now get the next serial Id. ** Need to do this within the transaction/table lock scope **:
string sequence = string.Format("\"{0}_{1}_seq\"", this.TableMapping.DBTableName, autoPkColumn.ColumnName);
var sql_get_seq = string.Format("SELECT last_value FROM {0}", sequence);
dbCommand.CommandText = sql_get_seq;
// if this is a fresh sequence, the "seed" value is returned. We will assume 1:
nextSerialPk = Convert.ToInt32(dbCommand.ExecuteScalar());
// If this is not a fresh sequence, increment:
if (nextSerialPk > 1)
{
nextSerialPk++;
}
}
var paramCounter = 0;
var rowValueCounter = 0;
foreach (var item in items)
{
// Set the soon-to-be inserted serial int value:
if (autoPkColumn != null)
{
var props = item.GetType().GetProperties();
var pk = props.First(p => p.Name == autoPkColumn.PropertyName);
pk.SetValue(item, nextSerialPk);
nextSerialPk++;
}
// Set the JSON object, including the interpolated serial PK
var itemEx = SetDataForDocument(item);
var itemSchema = itemEx as IDictionary <string, object>;
var sbParamGroup = new StringBuilder();
if (autoPkColumn != null && itemSchema.ContainsKey(autoPkColumn.PropertyName))
{
itemSchema.Remove(autoPkColumn.PropertyName);
}
if (ReferenceEquals(item, first))
{
var sbFieldNames = new StringBuilder();
foreach (var field in itemSchema)
{
string mappedColumnName = this.TableMapping.ColumnMappings.FindByProperty(field.Key).DelimitedColumnName;
sbFieldNames.AppendFormat("{0},", mappedColumnName);
}
var delimitedColumnNames = sbFieldNames.ToString().Substring(0, sbFieldNames.Length - 1);
string stub = "INSERT INTO {0} ({1}) VALUES ";
insertClause = string.Format(stub, this.TableMapping.DelimitedTableName, string.Join(", ", delimitedColumnNames));
sbSql = new StringBuilder(insertClause);
}
foreach (var key in itemSchema.Keys)
{
if (paramCounter + itemSchema.Count >= MAGIC_PG_PARAMETER_LIMIT || rowValueCounter >= MAGIC_PG_ROW_VALUE_LIMIT)
{
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
sbSql = new StringBuilder(insertClause);
paramCounter = 0;
rowValueCounter = 0;
dbCommand = this.Model.CreateCommand("", connection);
dbCommand.Transaction = tdbTransaction;
}
if (key == "search")
{
sbParamGroup.AppendFormat("to_tsvector(@{0}),", paramCounter.ToString());
}
else
{
sbParamGroup.AppendFormat("@{0},", paramCounter.ToString());
}
dbCommand.AddParam(itemSchema[key]);
paramCounter++;
}
// Add the row params to the end of the sql:
sbSql.AppendFormat("({0}),", sbParamGroup.ToString().Substring(0, sbParamGroup.Length - 1));
rowValueCounter++;
}
dbCommand.CommandText = sbSql.ToString().Substring(0, sbSql.Length - 1);
commands.Add(dbCommand);
try {
foreach (var cmd in commands)
{
rowsAffected += cmd.ExecuteNonQuery();
}
tdbTransaction.Commit();
}
catch (Exception) {
tdbTransaction.Rollback();
}
}
}
return(items);
}
