C# Npgsql.NpgsqlTransaction類代碼示例

        /// <summary>
        /// Constructs a <see cref="ConnectionHolder"/> instance with the given connection and transaction. The connection
        /// will be used for transactional work
        /// </summary>
        public static ConnectionHolder ForTransactionalWork(NpgsqlConnection connection, NpgsqlTransaction transaction)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (transaction == null) throw new ArgumentNullException("transaction");

            return new ConnectionHolder(connection, transaction);
        }

 public static int Delete(IEnumerable<int> malAnimeIds, NpgsqlConnection conn, NpgsqlTransaction transaction)
 {
     string idList = string.Join(", ", malAnimeIds.Select(id => id.ToString(CultureInfo.InvariantCulture)));
     string deleteSql = string.Format("DELETE FROM mal_anime_synonym WHERE mal_anime_id IN ({0})", idList);
     int numRowsDeleted = conn.Execute(deleteSql, transaction: transaction);
     return numRowsDeleted;
 }

        private void Init(PostgresProcessor processor, IEnumerable<string> columnDefinitions)
        {
            Connection = (NpgsqlConnection)processor.Connection;
            Transaction = (NpgsqlTransaction)processor.Transaction;

            NameWithSchema = string.IsNullOrEmpty(_schemaName) ? Name : string.Format("\"{0}\".{1}", _schemaName, Name);
            Create(columnDefinitions);
        }

        public static int ExecuteNonQuery(NpgsqlTransaction transaction, CommandType commandType, string commandText, params NpgsqlParameter[] commandParameters)
        {
            if (transaction == null) { throw new ArgumentNullException("transaction"); }
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");

            NpgsqlCommand command = new NpgsqlCommand();
            PrepareCommand(command, transaction.Connection, transaction, commandType, commandText, commandParameters);
            return command.ExecuteNonQuery();
        }

        public PostgresTestSequence(PostgresProcessor processor, string schemaName, string sequenceName)
        {
            _schemaName = schemaName;
            Name = quoter.QuoteSequenceName(sequenceName);

            Connection = (NpgsqlConnection)processor.Connection;
            Transaction = (NpgsqlTransaction)processor.Transaction;
            NameWithSchema = string.IsNullOrEmpty(_schemaName) ? Name : string.Format("\"{0}\".{1}", _schemaName, Name);
            Create();
        }

        /// <summary>
        /// Returns a data source wrapped around the transaction.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="transaction">The transaction.</param>
        /// <returns>SqlServerOpenDataSource.</returns>
        /// <exception cref="ArgumentNullException"></exception>
        public static PostgreSqlOpenDataSource AsDataSource(this NpgsqlConnection connection, NpgsqlTransaction transaction)
        {
            if (connection == null)
                throw new ArgumentNullException(nameof(connection), $"{nameof(connection)} is null.");
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            var dataSourceBase = s_CachedDataSources.GetOrAdd(connection.ConnectionString, cs => new PostgreSqlDataSource(cs));
            return new PostgreSqlOpenDataSource(dataSourceBase, connection, transaction);
        }

 /// <summary>
 /// Initializes a new instance of the <see cref="SetJobLastKnownEventCommand" /> class.
 /// </summary>
 /// <param name="jobName">Name of the job.</param>
 /// <param name="jobEventTime">The time.</param>
 /// <param name="jobScheduledTime">The job scheduled time.</param>
 /// <param name="connection">The connection.</param>
 /// <param name="transaction">The transaction.</param>
 public SetJobLastKnownEventCommand(string jobName,
     DateTimeOffset jobEventTime,
     DateTimeOffset jobScheduledTime,
     NpgsqlConnection connection,
     NpgsqlTransaction transaction)
 {
     JobName = jobName;
     JobEventTime = jobEventTime;
     JobScheduledTime = jobScheduledTime;
     Connection = connection;
     Transaction = transaction;
 }

        public static void DeleteOldestUsers(long numUsers, NpgsqlConnection conn, NpgsqlTransaction transaction)
        {
            if (numUsers == 0)
            {
                return;
            }
            if (numUsers < 0)
            {
                throw new ArgumentOutOfRangeException("numUsers", numUsers, string.Format("Cannot delete {0} oldest users", numUsers));
            }

            string deleteSql = @"DELETE FROM mal_user WHERE mal_user_id IN
            (SELECT mal_user_id FROM mal_user
            ORDER BY time_added
            LIMIT :NumToDelete)";

            conn.Execute(deleteSql, new { NumToDelete = numUsers }, transaction);
        }

        public static int Insert(IEnumerable<mal_anime_synonym> synonyms, NpgsqlConnection conn, NpgsqlTransaction transaction)
        {
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO mal_anime_synonym (mal_anime_id, synonym) VALUES ");

            bool anyRows = false;
            using (NpgsqlCommand cmd = new NpgsqlCommand())
            {
                int numSynonym = 1;
                foreach (var synIter in synonyms.AsSmartEnumerable())
                {
                    mal_anime_synonym synonym = synIter.Value;
                    if (!synIter.IsFirst)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    sqlBuilder.AppendFormat("(:MalAnimeId{0}, :Synonym{0})", numSynonym);

                    cmd.Parameters.AddWithValue(string.Format("MalAnimeId{0}", numSynonym), synonym.mal_anime_id);
                    cmd.Parameters.AddWithValue(string.Format("Synonym{0}", numSynonym), synonym.synonym);

                    numSynonym++;
                    anyRows = true;
                }

                if (anyRows)
                {
                    cmd.Connection = conn;
                    cmd.Transaction = transaction;
                    cmd.CommandText = sqlBuilder.ToString();
                    int numRowsInserted = cmd.ExecuteNonQuery();
                    return numRowsInserted;
                }
                else
                {
                    return 0;
                }
            }
        }

 public void Enlist(Transaction tx)
 {
     NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Enlist");
     if (tx != null)
     {
         _isolationLevel = tx.IsolationLevel;
         if (!tx.EnlistPromotableSinglePhase(this))
         {
             // must already have a durable resource
             // start transaction
             _npgsqlTx = _connection.BeginTransaction(ConvertIsolationLevel(_isolationLevel));
             _inTransaction = true;
             _rm = CreateResourceManager();
             _callbacks = new NpgsqlTransactionCallbacks(_connection);
             _rm.Enlist(_callbacks, TransactionInterop.GetTransmitterPropagationToken(tx));
             // enlisted in distributed transaction
             // disconnect and cleanup local transaction
             _npgsqlTx.Cancel();
             _npgsqlTx.Dispose();
             _npgsqlTx = null;
         }
     }
 }

        public static List<string> GetTableNames(this NpgsqlConnection connection, NpgsqlTransaction transaction = null)
        {
            var tableNames = new List<string>();

            using (var command = connection.CreateCommand())
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                command.CommandText = "select * from information_schema.tables where table_schema not in ('pg_catalog', 'information_schema')";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tableNames.Add(reader["table_name"].ToString());
                    }
                }
            }

            return tableNames;
        }

 protected async Task<object> ExecuteScalarAsync(string sql, NpgsqlConnection conn = null, NpgsqlTransaction tx = null)
 {
     if (conn == null)
         conn = Conn;
     var cmd = tx == null ? new NpgsqlCommand(sql, conn) : new NpgsqlCommand(sql, conn, tx);
     using (cmd)
         return await cmd.ExecuteScalarAsync();
 }

 public void commitTransaction()
 {
     if ((con != null) && (trans != null) && (con.State == System.Data.ConnectionState.Open))
     {
         trans.Commit();
         trans = null;
     }
 }

        public PostgreSqlTransaction(NpgsqlConnection connection)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            _transaction = connection.BeginTransaction(IsolationLevel.Serializable);
        }

        void BeginTransaction()
        {
            if (currentTransaction != null)
            {
                throw new InvalidOperationException("Cannot begin new transaction when a transaction has already been started!");
            }

            currentTransaction = GetOrCreateConnection().Connection.BeginTransaction();
        }