public IDbCommand CreateCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)
 {
     IDbCommand cmd = connection.CreateCommand();
     cmd.CommandText = cmdText;
     cmd.Transaction = transaction;
     return cmd;
 }

 private void ConvertQualityModels(IDbConnection conn, IDbTransaction tran)
 {
     // Converts the QualityModel JSON objects to their new format (only storing the QualityId instead of the entire object)
     ConvertQualityModel(conn, tran, "Blacklist");
     ConvertQualityModel(conn, tran, "EpisodeFiles");
     ConvertQualityModel(conn, tran, "History");
 }

 public RuntimeContext(IDbConnection connection, IDbTransaction transaction, IDbCommandExecutor executor, IProviderMetadata providerMetadata, IMigrationStepMetadata stepMetadata)
     : base(providerMetadata, stepMetadata)
 {
     _connection = connection;
     _transaction = transaction;
     _executor = executor;
 }

        public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams)
        {
            bool mustClose = false;
            if (myConn == null)
            {
                mustClose = true;
                myConn = clsConn.getConnOLE();
            }
            if (myConn.State != ConnectionState.Open)
                myConn.Open();
            OleDbCommand myCMD = new OleDbCommand();
            //
            myCMD.Connection = myConn as OleDbConnection;
            if (myTrans != null)
                myCMD.Transaction = myTrans as OleDbTransaction;
            //
            myCMD.CommandType = CommandType.Text;
            myCMD.CommandText = TSQL;
            myCMD.CommandTimeout = 180000;//3 phut
            //
            if (myParams != null)
                AttachParameters(myCMD, myParams);

            int CMDResult = myCMD.ExecuteNonQuery();
            //
            if (mustClose) myConn.Close();
            return CMDResult;
        }

 /// <summary>
 /// Проверить возможность удаления компонента
 /// </summary>
 /// <param name="request">бъект-оболочка RequestPackage, содержащая в поле requestID ID компонента</param>
 /// <param name="connectionID">Объект подключения к базе данных</param>
 /// <returns>Объект-оболочка ResponsePackagе</returns>
 public ResponsePackage CheckDeleteControl(RequestPackage request, IDbConnection connectionID, IDbTransaction transactionID)
 {
     string sql = string.Format(
         " select id from CONTROL_QUERY_MAPPING where control_id = {0} " +
         "  union " +
         " select id from FORM_IN_PARAMETERS where control_id = {0} " +
         "  union " +
         " select id from FORM_OUT_PARAMETERS where control_id = {0} " +
         "  union " +
         " select id from ACTION_PARAMETERS where control_id = {0} " +
         "  union " +
         " select id from QUERY_QUERY_IN_PARAMETER where control_id = {0} ",
         request.requestID
     );
     ResponseTablePackage res = DBUtils.OpenSQL(sql, connectionID, transactionID);
     res.ThrowExceptionIfError();
     if (res.resultData.Rows.Count > 0)
     {
         return new ResponsePackage() { resultCode = -1, resultMessage = "Удаление компонента невозможно. Проверьте все ссылки на него." };
     }
     else
     {
         return new ResponsePackage() { resultCode = 0, resultMessage = "Удаление возможно." };
     }
 }

 public void ExecuteWithTransaction(IDbConnection db, IDbTransaction transaction)
 {
     db.Execute(@"IF @EndPointType = 3
                     BEGIN
                         UPDATE dbo.Company SET QueueId = @QueueId
                         WHERE Company.CompanyId = @EndPointId
                     END
                 ELSE IF @EndPointType = 0
                     BEGIN
                         UPDATE dbo.Agent SET QueueId = @QueueId
                         WHERE Agent.AgentId = @EndPointId
                     END
                 ELSE IF @EndPointType = 1
                     BEGIN
                         UPDATE dbo.Team SET QueueId = @QueueId
                         WHERE Team.TeamId = @EndPointId
                     END
                 ELSE IF @EndPointType = 2
                     BEGIN
                         UPDATE dbo.Department SET QueueId = @QueueId
                         WHERE Department.DepartmentId = @EndPointId
                     END",
         new
         {
             EndPointType = _endPoint.EndPointType,
             EndPointId = _endPoint.EndpointId,
             QueueId = _endPoint.QueueId
         }, transaction);
 }

        public SqlServerFetchedJob(
            [NotNull] SqlServerStorage storage,
            [NotNull] IDbConnection connection, 
            [NotNull] IDbTransaction transaction, 
            string jobId, 
            string queue)
        {
            if (storage == null) throw new ArgumentNullException(nameof(storage));
            if (connection == null) throw new ArgumentNullException(nameof(connection));
            if (transaction == null) throw new ArgumentNullException(nameof(transaction));
            if (jobId == null) throw new ArgumentNullException(nameof(jobId));
            if (queue == null) throw new ArgumentNullException(nameof(queue));

            _storage = storage;
            _connection = connection;
            _transaction = transaction;

            JobId = jobId;
            Queue = queue;

            if (!_storage.IsExistingConnection(_connection))
            {
                _timer = new Timer(ExecuteKeepAliveQuery, null, KeepAliveInterval, KeepAliveInterval);
            }
        }

        public override IDbStatement BuildStatement(
			IDbConnection connection,
			IDbTransaction transaction,
			params IDisposable[] resources)
        {
            return new FirebirdDbStatement(this, connection, transaction, resources);
        }

 public UnitOfWork(IDbTransaction transaction, Action<UnitOfWork> rolledBack, Action<UnitOfWork> committed)
 {
     Transaction = transaction;
      _transaction = transaction;
      _rolledBack = rolledBack;
      _committed = committed;
 }

        public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, string tableName, IEnumerable<IDictionary<string, object>> data, IDbTransaction transaction, Func<IDictionary<string,object>, Exception, bool> onError, bool resultRequired)
        {
            var table = adapter.GetSchema().FindTable(tableName);
            var columns = table.Columns.Where(c => !c.IsIdentity).ToList();

            string columnList = string.Join(",", columns.Select(c => c.QuotedName));
            string valueList = string.Join(",", columns.Select(c => "?"));

            string insertSql = "insert into " + table.QualifiedName + " (" + columnList + ") values (" + valueList + ")";

            var helper = transaction == null
                             ? new BulkInserterHelper(adapter, data, table, columns)
                             : new BulkInserterTransactionHelper(adapter, data, table, columns, transaction);

            if (resultRequired)
            {
                var identityColumn = table.Columns.FirstOrDefault(col => col.IsIdentity);
                if (identityColumn != null)
                {
                    var identityFunction = adapter.GetIdentityFunction();
                    if (!string.IsNullOrWhiteSpace(identityFunction))
                    {
                        return InsertRowsAndReturn(adapter, identityFunction, helper, insertSql, table, onError);
                    }
                }
            }

            helper.InsertRowsWithoutFetchBack(insertSql, onError);

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="dbTransaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public IGridReader Create(IDbConnection dbConnection, string storedProcName, IList<IDbParameter> parameters = null, IDbTransaction dbTransaction = null, int? commandTimeout = null,
                                  CommandType? commandType = null)
        {
            var dynamicParameters = ConvertToDynamicParameters(parameters);

            return new DapperGridReader(dbConnection.QueryMultiple(storedProcName, dynamicParameters, dbTransaction, commandTimeout, commandType));
        }

        public IEnumerable<ResultSet> Execute(IDictionary<string, object> suppliedParameters, IDbTransaction transaction)
        {
            var procedure = _adapter.GetSchema().FindProcedure(_procedureName);
            if (procedure == null)
            {
                throw new UnresolvableObjectException(_procedureName.ToString(), string.Format("Procedure '{0}' not found.", _procedureName));
            }

            var cn = transaction == null ? _adapter.CreateConnection() : transaction.Connection;
            using (cn.MaybeDisposable())
            using (var command = cn.CreateCommand(_adapter.AdoOptions))
            {
                command.Transaction = transaction;
                command.CommandText = procedure.QualifiedName;
                command.CommandType = CommandType.StoredProcedure;
                SetParameters(procedure, command, suppliedParameters);
                try
                {
                    var result = _executeImpl(command);
                    if (command.Parameters.Contains(SimpleReturnParameterName))
                        suppliedParameters["__ReturnValue"] = command.Parameters.GetValue(SimpleReturnParameterName);
                    RetrieveOutputParameterValues(procedure, command, suppliedParameters);
                    return result;
                }
                catch (DbException ex)
                {
                    throw new AdoAdapterException(ex.Message, command, ex);
                }
            }
        }

        public static IDbCommand CreateCommand(IDbConnection db, IDbTransaction transaction, string sql, params object[] parameters)
        {
            IDbCommand cmd;

            // handle named/numbered etc parameters, fixing sql if required.
            var new_parameters = new List<object>();
            sql = ParseParameters(sql, parameters, new_parameters);
            parameters = new_parameters.ToArray();

            sql = sql.Replace("@@", "@");	// remove double escaped

            cmd = db.CreateCommand();

            cmd.CommandText = sql;
            //cmd.CommandTimeout = commandTimeout;
            //cmd.CommandType = commandType;

            if (transaction != null)
            {
                cmd.Transaction = transaction;
            }

            foreach (var param in parameters)
            {
                AddParameter(cmd, param);
            }

            return cmd;
        }

        private void ConvertQualityProfiles(IDbConnection conn, IDbTransaction tran)
        {
            var qualityProfileItemConverter = new EmbeddedDocumentConverter(new QualityIntConverter());

            // Convert 'Allowed' column in QualityProfiles from Json List<object> to Json List<int> (int = Quality)
            using (IDbCommand qualityProfileCmd = conn.CreateCommand())
            {
                qualityProfileCmd.Transaction = tran;
                qualityProfileCmd.CommandText = @"SELECT Id, Allowed FROM QualityProfiles";
                using (IDataReader qualityProfileReader = qualityProfileCmd.ExecuteReader())
                {
                    while (qualityProfileReader.Read())
                    {
                        var id = qualityProfileReader.GetInt32(0);
                        var allowedJson = qualityProfileReader.GetString(1);

                        var allowed = Json.Deserialize<List<Quality>>(allowedJson);

                        var items = Quality.DefaultQualityDefinitions.OrderBy(v => v.Weight).Select(v => new ProfileQualityItem { Quality = v.Quality, Allowed = allowed.Contains(v.Quality) }).ToList();

                        var allowedNewJson = qualityProfileItemConverter.ToDB(items);

                        using (IDbCommand updateCmd = conn.CreateCommand())
                        {
                            updateCmd.Transaction = tran;
                            updateCmd.CommandText = "UPDATE QualityProfiles SET Items = ? WHERE Id = ?";
                            updateCmd.AddParameter(allowedNewJson);
                            updateCmd.AddParameter(id);

                            updateCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }

        private static Guid BeginConversationInternal(IDbTransaction transaction, string initiatorServiceName, string targetServiceName, string messageContractName, int? lifetime, bool? encryption) {
            EnsureSqlTransaction(transaction);
            var cmd = transaction.Connection.CreateCommand() as SqlCommand;
            var query = new StringBuilder();

            query.Append("BEGIN DIALOG @ch FROM SERVICE " + initiatorServiceName + " TO SERVICE @ts ON CONTRACT @cn WITH ENCRYPTION = ");

            if (encryption.HasValue && encryption.Value)
                query.Append("ON ");
            else
                query.Append("OFF ");

            if (lifetime.HasValue && lifetime.Value > 0) {
                query.Append(", LIFETIME = ");
                query.Append(lifetime.Value);
                query.Append(' ');
            }

            var param = cmd.Parameters.Add("@ch", SqlDbType.UniqueIdentifier);
            param.Direction = ParameterDirection.Output;
            param = cmd.Parameters.Add("@ts", SqlDbType.NVarChar, 256);
            param.Value = targetServiceName;
            param = cmd.Parameters.Add("@cn", SqlDbType.NVarChar, 128);
            param.Value = messageContractName;

            cmd.CommandText = query.ToString();
            cmd.Transaction = transaction as SqlTransaction;
            var count = cmd.ExecuteNonQuery();

            var handleParam = cmd.Parameters["@ch"] as SqlParameter;
            return (Guid)handleParam.Value;
        }

        public static ResponsePackage Save(RequestObjectPackage<ActionTypePropertyModel> request, IDbConnection connectionID, IDbTransaction transactionID)
        {
            ActionTypePropertyModel obj = request.requestData;
            string sql = string.Empty;

            if (obj.ID > 0)
            {
                sql = string.Format(
                    " update ACTION_TYPE_PROPERTIES set \"VALUE\" = {0}, ACTION_TYPE_ID = {1}, ACTION_KIND_PROPERTY_ID = {2} " + Environment.NewLine +
                    " where ID = {3} returning ID",
                    SQL.FromNumber(obj.ID),
                    SQL.FromNumber(obj.actionTypeID),
                    SQL.FromNumber(obj.actionKindPropertyID),
                    SQL.FromNumber(obj.ID)
                );
            }
            else
            {
                sql = string.Format(
                    " insert into ACTION_TYPE_PROPERTIES (\"VALUE\", ACTION_TYPE_ID, ACTION_KIND_PROPERTY_ID) " + Environment.NewLine +
                    " values ({0}, {1}, {2}) returning ID",
                    SQL.FromNumber(obj.ID),
                    SQL.FromNumber(obj.actionTypeID),
                    SQL.FromNumber(obj.actionKindPropertyID)
                );
            }
            ResponseTablePackage res = DBUtils.ExecuteSQL(sql, connectionID, true, transactionID);
            res.ThrowExceptionIfError();
            return new ResponsePackage() { resultID = res.resultID };
        }

 public IDbCommand CreateCommand(string commandText, IDbConnection connection, IDbTransaction transaction)
 {
     var command = connection.CreateCommand();
     command.CommandText = commandText;
     command.Transaction = transaction;
     return command;
 }

 /// <summary>
 /// Inserts a new record. There is no unique ID column so nothing is returned.
 /// </summary>
 /// <param name="connection"></param>
 /// <param name="transaction"></param>
 /// <param name="log"></param>
 /// <param name="dbInfo"></param>
 public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, BaseStationDBInfo dbInfo)
 {
     var preparedCommand = PrepareCommand(connection, transaction, "Insert", _InsertCommandText, 2);
     Sql.SetParameters(preparedCommand, dbInfo.OriginalVersion, dbInfo.CurrentVersion);
     Sql.LogCommand(log, preparedCommand.Command);
     preparedCommand.Command.ExecuteNonQuery();
 }

 /// <summary>
 /// Adds a new record to the database.
 /// </summary>
 /// <param name="connection"></param>
 /// <param name="transaction"></param>
 /// <param name="log"></param>
 /// <param name="client"></param>
 public void Insert(IDbConnection connection, IDbTransaction transaction, TextWriter log, LogClient client)
 {
     SqlPreparedCommand command = PrepareInsert(connection, transaction, "Insert",
         "Id",
         "IpAddress", "ReverseDns", "ReverseDnsDate");
     client.Id = Sql.ExecuteInsert(command, log, client.IpAddress, client.ReverseDns, client.ReverseDnsDate);
 }

        protected override bool RunOperation(
            SqlConnection sqlConnection,
            IDbTransaction dbTransaction,
            DbFunctionType dbfunctionType,
            string operationName,
            IEnumerable<KeyValuePair<string, object>> parameters,
            out object result)
        {
            var context = parameters.First().Value as ISearchContext;

            if (context != null)
            {
                var sql = new SearchBuilder().BuildSearchSql(context);

                using (var cmd = this.DbAccess.GetCommand(sql, CommandType.Text))
                {
                    result = this.DbAccess.GetReader(cmd, dbTransaction);
                    return true;
                }
            }

            // failure...
            result = null;
            return false;
        }