C# IDbConnection.Execute方法代码示例

        private static void Normalise(IDbConnection cnn, IDbTransaction transaction)
        {
            cnn.Execute("INSERT INTO FormType (Description) VALUES ('ChangeRequest')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Alpha')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Bravo')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Charlie')", null, transaction);
            cnn.Execute(@"INSERT INTO FieldType (FormTypeId, Description) VALUES (1, 'Delta')", null, transaction);

            var rows = cnn.Query<TestData>("SELECT * FROM Test", null, transaction);

            foreach (var row in rows)
            {
                cnn.Execute(@"INSERT INTO Form (FormTypeId) VALUES (1)", null, transaction);
                var formId = cnn.Query<int>("SELECT CAST(@@IDENTITY AS INT)", null, transaction).First();

                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (1, @formId, @value)", new { formId, value = row.Alpha }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (2, @formId, @value)", new { formId, value = row.Bravo }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (3, @formId, @value)", new { formId, value = row.Charlie }, transaction);
                cnn.Execute(@"INSERT INTO Field (FieldTypeId, FormId, Value) VALUES (4, @formId, @value)", new { formId, value = row.Delta }, transaction);
            }

            var results = cnn.Query(@"SELECT * FROM Form fo
                                      INNER JOIN Field fi ON fo.FormId = fi.FormId
                                      INNER JOIN FieldType ft ON ft.FieldTypeId = fi.FieldTypeId
                                      WHERE fo.FormId = 1", null, transaction).ToDictionary(
                row => row.Description,
                row => row.Value);
        }

        void Destroy(IDbConnection cn) {
            try {
                cn.Execute(_context.SqlDropOutputView());
            } catch { }

            try {
                cn.Execute(_context.SqlDropOutput());
            } catch { }
        }

        private void CreateFunctions(IDbConnection conn)
        {
            conn.Execute(@"
            CREATE OR REPLACE FUNCTION clear_database() RETURNS void AS $$
            DECLARE
            BEGIN
            DELETE FROM votes;
            DELETE FROM votes;
            DELETE FROM posts;
            DELETE FROM comments;
            DELETE FROM messages;
            DELETE FROM sub_scriptions;
            DELETE FROM sub_admins;
            DELETE FROM sub_user_bans;
            DELETE FROM subs;
            DELETE FROM user_logins;
            DELETE FROM user_roles;
            DELETE FROM roles;
            DELETE from users;
            END;
            $$ LANGUAGE plpgsql;");

            conn.Execute(@"
            CREATE OR REPLACE FUNCTION ensure_default_user_exists() RETURNS void AS $$
            DECLARE
            BEGIN

            IF NOT EXISTS (SELECT 1 FROM users WHERE user_name = 'skimur')
            THEN
              INSERT INTO users (id, created_date, user_name, password_hash) values ('81e2d7a7-1e56-e511-84d5-5404a632cbf7', '2015-09-08 11:42:17.961907+00', 'skimur', 'AEX8cmLa7jBdhKaG5Gh8I5IIlWF6GBc5pmcED5QIdRZ45qOwky5GhvXkCTYo3Aey7A==');
            END IF;

            END;
            $$ LANGUAGE plpgsql;
            ");

            conn.Execute(@"
            create or replace function hot(ups integer, downs integer, date timestamp with time zone) returns numeric as $$
            select round(cast(log(greatest(abs($1 - $2), 1)) * sign($1 - $2) + (date_part('epoch', $3) - 1134028003) / 45000.0 as numeric), 7)
            $$ language sql immutable;");

            conn.Execute(@"
            create or replace function score(ups integer, downs integer) returns integer as $$
            select $1 - $2
            $$ language sql immutable;");

            conn.Execute(@"
            create or replace function controversy(ups integer, downs integer) returns float as $$
            select CASE WHEN $1 <= 0 or $2 <= 0 THEN 0
                WHEN $1 > $2 THEN power($1 + $2, cast($2 as float) / $1)
                ELSE power($1 + $2, cast($1 as float) / $2)
            END;
            $$ language sql immutable;");
        }

 private void InsertCard(M2Card card, IDbConnection connection, IDbTransaction transaction)
 {
     connection.Execute(
         "INSERT INTO [Accounting].[Card] ([CardId], [CardNo], [CardVendorId], [HolderFirstName], [HolderLastName], [ExpirationDateUtc])" +
         "VALUES (@CardId, @CardNo, @CardVendorId, @HolderFirstName, @HolderLastName, @ExpirationDateUtc)",
         card.ToCard(), transaction);
     connection.Execute(
         "INSERT INTO [Accounting].[User_Card_Account] ([CardId], [UserId], [AccountNo]) VALUES (@CardId, @UserId, @AccountNo)",
         card.ToUserCard(), transaction);
     connection.Execute(
         "INSERT INTO [Accounting].[CardSettings] " +
         "([CardId], [Blocked], [LimitOperationsPerDayLocal], [LimitOperationsPerDayAbroad], [LimitAmountPerDayLocal], [LimitAmountPerDayAbroad])" +
         "VALUES (@CardId, 0, 20, 10, 1000.0, 500.0)", card, transaction);
 }

        public SqlServerDistributedLock(string resource, TimeSpan timeout, IDbConnection connection)
        {
            if (String.IsNullOrEmpty(resource)) throw new ArgumentNullException("resource");
            if (connection == null) throw new ArgumentNullException("connection");

            _resource = resource;
            _connection = connection;

            var parameters = new DynamicParameters();
            parameters.Add("@Resource", _resource);
            parameters.Add("@DbPrincipal", "public");
            parameters.Add("@LockMode", LockMode);
            parameters.Add("@LockOwner", LockOwner);
            parameters.Add("@LockTimeout", timeout.TotalMilliseconds);
            parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

            connection.Execute(
                @"sp_getapplock", 
                parameters, 
                commandType: CommandType.StoredProcedure);

            var lockResult = parameters.Get<int>("@Result");

            if (lockResult < 0)
            {
                throw new SqlServerDistributedLockException(
                    String.Format(
                    "Could not place a lock on the resource '{0}': {1}.",
                    _resource,
                    LockErrorMessages.ContainsKey(lockResult) 
                        ? LockErrorMessages[lockResult]
                        : String.Format("Server returned the '{0}' error.", lockResult)));
            }
        }

 public int Insert(GroupSaleVehicle poco, IDbConnection connection)
 {
     var dynamicParams = new DynamicParameters(mapper.Map(poco));
     dynamicParams.Add("@VehicleID", dbType: DbType.Int32, direction: ParameterDirection.Output);
     connection.Execute("InsertSaleVehicle7", dynamicParams ,commandType: CommandType.StoredProcedure);
     return dynamicParams.Get<int>("@VehicleID");
 }

        public void BuildSchema(IDbConnection conn)
        {
            conn.EnsureOpen();

            foreach (string statement in createSql)
                conn.Execute(statement);
        }

        public MigrationResult Migrate(IDbConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentNullException(nameof(connection));
            }

            if (!connection.IsAzureDatabase())
            {
                return new MigrationResult
                {
                    MigrationWasApplied = true,
                    Log = "Database is not an Azure SQL database so no action taken."
                };
            }

            var sql =
                [email protected]"
alter database {connection.Database} 
modify (MAXSIZE = {MaxSize},
        EDITION = '{Edition}',
        SERVICE_OBJECTIVE = '{ServiceObjective}')";

            connection.Execute(sql);

            return new MigrationResult
            {
                MigrationWasApplied = true,
                Log = sql
            };
        }

        public override void Insert(IDbConnection connection, object instance)
        {
            var transaction = instance as Transaction;

            var sql = GenerateInsertStatement();

            var values = new
            {
                Id = transaction.Id,
                Date = transaction.Date,
                PackageId = transaction.Package.Id,
                PackageVersion = transaction.Package.Version.Number,
                PackageCostPrice = transaction.Package.PackageCostPrice,
                PackageRecommendedPrice = transaction.Package.PackageRecommendedPrice,
                ContractId = transaction.Contract.Id,
                ContractVersion = transaction.Contract.Version.Number,
                UserId = transaction.User.Id,
                RequestId = transaction.Request.Id,
                System = transaction.Request.System.Name,
                Server = transaction.Request.System.Server.MachineName,
                State = transaction.State.Name,
                StateId = transaction.State.Id,
                AccountNumber = transaction.Account.AccountNumber
            };

            connection.Execute(sql, values);
        }

        public static Common.Models.Events.EventNote Create(Common.Models.Events.EventNote model,
            Common.Models.Account.Users creator,
            IDbConnection conn = null, bool closeConnection = true)
        {
            model.Created = model.Modified = DateTime.UtcNow;
            model.CreatedBy = model.ModifiedBy = creator;
            DBOs.Events.EventNote dbo = Mapper.Map<DBOs.Events.EventNote>(model);

            conn = DataHelper.OpenIfNeeded(conn);

            Common.Models.Events.EventNote currentModel = Get(model.Event.Id.Value, model.Note.Id.Value, conn, false);

            if (currentModel != null)
            { // Update
                conn.Execute("UPDATE \"event_note\" SET \"utc_modified\"[email protected], \"modified_by_user_pid\"[email protected] " +
                    "\"utc_disabled\"=null, \"disabled_by_user_pid\"=null WHERE \"id\"[email protected]", dbo);
                model.Created = currentModel.Created;
                model.CreatedBy = currentModel.CreatedBy;
            }
            else
            { // Create
                if (conn.Execute("INSERT INTO \"event_note\" (\"id\", \"note_id\", \"event_id\", \"utc_created\", \"utc_modified\", \"created_by_user_pid\", \"modified_by_user_pid\") " +
                    "VALUES (@Id, @NoteId, @EventId, @UtcCreated, @UtcModified, @CreatedByUserPId, @ModifiedByUserPId)",
                    dbo) > 0)
                    model.Id = conn.Query<DBOs.Events.EventNote>("SELECT currval(pg_get_serial_sequence('event_note', 'id')) AS \"id\"").Single().Id;
            }

            DataHelper.Close(conn, closeConnection);

            return model;
        }

        public bool OXORuleFeatureSave(RuleFeature obj, IDbConnection conn)
        {
            bool retVal = true;
            string procName = "dbo.OXO_Rule_Feature_New";

            try
            {

                obj.Save(this.CurrentCDSID);

                var para = new DynamicParameters();

                para.Add("@p_RuleId", obj.RuleId, dbType: DbType.Int32);
                para.Add("@p_ProgrammeId", obj.ProgrammeId, dbType: DbType.Int32);
                para.Add("@p_FeatureId", obj.FeatureId, dbType: DbType.Int32);
                para.Add("@p_CreatedBy", obj.CreatedBy, dbType: DbType.String, size: 8);
                para.Add("@p_CreatedOn", obj.CreatedOn, dbType: DbType.DateTime);
                para.Add("@p_UpdatedBy", obj.UpdatedBy, dbType: DbType.String, size: 8);
                para.Add("@p_LastUpdated", obj.LastUpdated, dbType: DbType.DateTime);

                conn.Execute(procName, para, commandType: CommandType.StoredProcedure);

            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }

            return retVal;

        }

 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 Result<customer_account> Login(IDbConnection db, string userName, string pwd, IPAddress ip)
        {
            string userNameLC = userName.ToLower();

            var loginInfo = db.Query<customer_login_password>("SELECT * FROM customer_login_password WHERE lower(username)[email protected] OR lower(email)[email protected] ", new { userName = userNameLC }).FirstOrDefault();

            if (loginInfo == null || string.Compare(loginInfo.unhashed_password, pwd, StringComparison.OrdinalIgnoreCase) != 0)
            {
                return Result<customer_account>.Null(ErrorCodes.InvalidUserNameOrPassword);
            }

            var user = GetCustomerById(db, loginInfo.customer_account_id);
            int id = user.Data.id;

            // Change country code only IF the database has not store this value before //
            if (string.IsNullOrEmpty(user.Data.country_code) || string.IsNullOrEmpty(user.Data.country_name))
            {
                ip.GetCountryCode(c => user.Data.country_code = c, n => user.Data.country_name = n);
            }
          

            // TODO: Update
            user.Data.last_login_at = DateTime.UtcNow;
            db.Execute("UPDATE customer_account SET [email protected]_login_at, [email protected]_code, [email protected]_name", user.Data);

            return user;
        }

        public static void Install(IDbConnection connection)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            
            Log.Info("Start installing Hangfire SQL objects...");

            var script = GetStringResource(
                typeof(SQLiteObjectsInstaller).Assembly, 
                "Hangfire.SQLite.Install.sql");

            script = script.Replace("SET @TARGET_SCHEMA_VERSION = 4;", "SET @TARGET_SCHEMA_VERSION = " + RequiredSchemaVersion + ";");

            for (var i = 0; i < RetryAttempts; i++)
            {
                try
                {
                    connection.Execute(script);
                    break;
                }
                catch (SqlException ex)
                {
                    if (ex.ErrorCode == 1205)
                    {
                        Log.WarnException("Deadlock occurred during automatic migration execution. Retrying...", ex);
                    }
                    else
                    {
                        throw;
                    }
                }
            }

            Log.Info("Hangfire SQL objects installed.");
        }

        public int Insert(
            IDbConnection connection,
            IDbTransaction transaction,
            int? commandTimeout,
            string tableName,
            string columnList,
            string parameterList,
            IEnumerable<PropertyInfo> keyProperties,
            object entityToInsert,
            bool autoIncrement)
        {
            var cmd = string.Format("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);

            connection.Execute(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout);

            if (autoIncrement)
            {
                // http://stackoverflow.com/questions/8517841/mysql-last-insert-id-connector-net
                var id = (int)(long)connection.Query<ulong>(
                        "SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER)",
                        transaction: transaction,
                        commandTimeout: commandTimeout).FirstOrDefault();

                var keyProperty = keyProperties.FirstOrDefault();
                if (keyProperty != null)
                {
                    keyProperty.SetValue(entityToInsert, id, null);
                }

                return id;
            }

            return 0;
        }