예제 #1
0
        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));
            }
        }
예제 #2
0
        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);
        }
예제 #3
0
        /// <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);
        }
예제 #4
0
        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));
        }
예제 #5
0
        /// <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);
        }
예제 #6
0
        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);
        }
예제 #7
0
        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);
        }
예제 #8
0
        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);
        }
예제 #9
0
 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);
         }
     }
 }
예제 #10
0
        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);
            }
        }
예제 #11
0
파일: DBTable.cs 프로젝트: mikakolari/biggy
        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);
        }
예제 #12
0
        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;
        }
예제 #13
0
        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);
                }
            }
        }
예제 #14
0
        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;
        }
예제 #15
0
        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();
        }
예제 #16
0
        /// <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);
        }
예제 #17
0
        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();
        }
예제 #18
0
        /// <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);
        }
예제 #19
0
        /// <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);
        }
예제 #20
0
        /// <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);
        }
예제 #21
0
        /// <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);
        }