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;
}
/// <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 void commitTransaction()
{
if ((con != null) && (trans != null) && (con.State == System.Data.ConnectionState.Open))
{
trans.Commit();
trans = null;
}
}
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 PostgreSqlTransaction(NpgsqlConnection connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
_transaction = connection.BeginTransaction(IsolationLevel.Serializable);
}
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();
}
void BeginTransaction()
{
if (currentTransaction != null)
{
throw new InvalidOperationException("Cannot begin new transaction when a transaction has already been started!");
}
currentTransaction = GetOrCreateConnection().Connection.BeginTransaction();
}
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);
}
public void Setup()
{
OnSetupBeforeTransaction();
connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sampledb;User Id=philipp;Password=test_pwd;");
connection.Open();
transaction = connection.BeginTransaction();
OnSetupInTransaction();
}
public void Dispose()
{
if (_transaction != null)
{
if (!_committed)
_transaction.Rollback();
_transaction.Dispose();
_transaction = null;
}
}
/// <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;
}
private void createTransaction(string dataConnectProviders)
{
Connector _connector = new Connector(dataConnectProviders);
if (String.IsNullOrEmpty(_connector.ConnectionString))
{
_connector.ConnectionString = "zstoreConnectionString";
}
string conString = "";
if (_connector.RegistryKeyValue.ToLower() != "config" && _connector.RegistryKeyValue.ToUpper() != "CONNECTIONVAR")
{
conString = RegistryAccess.getRegistryKeyValue(_connector.RegistryKeyValue, _connector.ConnectionString);
}
else
{
conString = _connector.ConnectionString;
}
try
{
if (_connector.ProviderType == "PostgreSQL")
{
NpgsqlConnection conn = new NpgsqlConnection(_connector.ConnectionString);
conn.Open();
dbConnect = conn;
Npgsql.NpgsqlTransaction _trans = conn.BeginTransaction();
_transaction = _trans;
_inTrans = true;
}
else
{
DbProviderFactory dbFactory = DbProviderFactories.GetFactory(_connector.ProviderType);
dbConnect = dbFactory.CreateConnection();
dbConnect.ConnectionString = conString;
dbConnect.Open();
_transaction = dbConnect.BeginTransaction();
//transaction = new CommittableTransaction();
//dbConnect.EnlistTransaction(transaction);
_inTrans = true;
}
}
catch (Exception ex)
{
this.SentEmail(conString, ex.ToString());
}
}
public void connect()
{
//Definición de la conexión
string connectionString =
"Server=127.0.0.1;" +
"Database=mono;" +
"Port=5432;" +
"User Id=conflux;" + // Aquí debes de indicar algún usuario existente en la BD
"Password=;"; // que tenga permisos, ademas de su contraseña
conexion = new NpgsqlConnection (connectionString);
conexion.Open();
//Comenzamos una transaccion
transaccion = conexion.BeginTransaction ();
}
public void connect()
{
if (con != null)
{
return;
}
con = new Npgsql.NpgsqlConnection((string)param ["cs"]);
trans = con.BeginTransaction(IsolationLevel.Chaos);
try {
con.Open();
} catch {
con = null;
throw;
}
}
/// <summary>
/// Método ComenzarTransaccion.
/// Permite iniciar las transacciones que se van a producir en la base de datos.
/// </summary>
public override void ComenzarTransaccion()
{
if (this.iConexion == null)
{
throw new DAOException("No se puede iniciar una transacción sin una conexión abierta");
}
try
{
this.iTransaccion = this.iConexion.BeginTransaction();
}
catch (NpgsqlException)
{
throw new DAOException("Hubo un problema al iniciar la transacción en la Base de Datos");
}
}
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 DatabaseService()
{
_databaseConnection = new NpgsqlConnection("Server=Localhost;Port=5432;User Id=Tony;Database=Adjuster;");
//_databaseConnection = new NpgsqlConnection(connectString);
_databaseConnection.Open();
_upsertCommand = new NpgsqlCommand("SELECT upsert(:ID, :VALUE);", _databaseConnection);
_upsertCommand.Parameters.Add(new NpgsqlParameter("ID", NpgsqlTypes.NpgsqlDbType.Bigint));
_upsertCommand.Parameters.Add(new NpgsqlParameter("VALUE", NpgsqlTypes.NpgsqlDbType.Text));
_queryCommand = new NpgsqlCommand("SELECT * FROM \"OpenStreetMap\" WHERE \"id\"=:ID;", _databaseConnection);
_queryCommand.Parameters.Add(new NpgsqlParameter("ID", NpgsqlTypes.NpgsqlDbType.Bigint));
_deleteCommand = new NpgsqlCommand("DELETE FROM \"OpenStreetMap\" WHERE \"id\"=:ID;", _databaseConnection);
_deleteCommand.Parameters.Add(new NpgsqlParameter("ID", NpgsqlTypes.NpgsqlDbType.Bigint));
_transaction = _databaseConnection.BeginTransaction();
Console.WriteLine("Connected to PostgreSQL Database.\n");
}
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;
}
/// <summary>トランザクション開始</summary>
/// <param name="iso">分離レベル（Oracleに同じ）</param>
public override void BeginTransaction(DbEnum.IsolationLevelEnum iso)
{
// 分離レベル設定のチェック
if (iso == DbEnum.IsolationLevelEnum.NoTransaction)
{
// トランザクションを開始しない（nullのまま）。
}
else if (iso == DbEnum.IsolationLevelEnum.DefaultTransaction)
{
// 規定の分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction();
}
else if (iso == DbEnum.IsolationLevelEnum.ReadUncommitted)
{
// 無効な分離レベル
throw new ArgumentException(String.Format(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_UC, "PostgreSQL"));
}
else if (iso == DbEnum.IsolationLevelEnum.ReadCommitted)
{
// コミット済み読み取りの分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.ReadCommitted);
}
else if (iso == DbEnum.IsolationLevelEnum.RepeatableRead)
{
// 無効な分離レベル
throw new ArgumentException(String.Format(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_RR, "PostgreSQL"));
}
else if (iso == DbEnum.IsolationLevelEnum.Serializable)
{
// 直列化可能の分離レベルでトランザクションを開始する。
this._tx = this._cnn.BeginTransaction(IsolationLevel.Serializable);
}
else if (iso == DbEnum.IsolationLevelEnum.Snapshot)
{
//// スナップショット分離レベルでトランザクションを開始する。
//this._tx = this._cnn.BeginTransaction(IsolationLevel.Snapshot);
// 無効な分離レベル（スナップショット分離レベル）
throw new ArgumentException(String.Format(
PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_SS, "PostgreSQL"));
}
else if (iso == DbEnum.IsolationLevelEnum.User)
{
// 無効な分離レベル（ユーザ指定）。
throw new ArgumentException(PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_USR);
}
else if (iso == DbEnum.IsolationLevelEnum.NotConnect)
{
// 無効な分離レベル（NotConnect指定）。
throw new ArgumentException(PublicExceptionMessage.DB_ISO_LEVEL_PARAM_ERROR_NC);
}
else
{
// 通らない予定
}
// 分離レベル（iso）をメンバ変数に保存
_iso = iso;
}
internal void ClearTransaction()
{
if (_txStatus == TransactionStatus.Idle) { return; }
_tx.Connection = null;
_tx = null;
_txStatus = TransactionStatus.Idle;
}
private int MakeSureLastVersionMatches(string name, long expectedVersion, NpgsqlConnection conn,
NpgsqlTransaction tx)
{
const string sql =
@"SELECT COALESCE(MAX(Version),0)
FROM ES_Events2
WHERE Name=:name AND CustomerId=:customerId";
int version;
using (var cmd = new NpgsqlCommand(sql, conn, tx))
{
cmd.Parameters.AddWithValue(":name", name);
cmd.Parameters.AddWithValue(":customerId", customerId);
version = (int) cmd.ExecuteScalar();
if (expectedVersion != -1)
{
if (version != expectedVersion)
{
throw new AppendOnlyStoreConcurrencyException(version, expectedVersion, name);
}
}
}
return version;
}
public void Initialize()
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Initialize");
_npgsqlTx = _connection.BeginTransaction(ConvertIsolationLevel(_isolationLevel));
_inTransaction = true;
}
public void SinglePhaseCommit(SinglePhaseEnlistment singlePhaseEnlistment)
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "SinglePhaseCommit");
if (_npgsqlTx != null)
{
_npgsqlTx.Commit();
_npgsqlTx.Dispose();
_npgsqlTx = null;
singlePhaseEnlistment.Committed();
_connection.PromotableLocalTransactionEnded();
}
else if (_callbacks != null)
{
if (_rm != null)
{
_rm.CommitWork(_callbacks.GetName());
singlePhaseEnlistment.Committed();
}
else
{
_callbacks.CommitTransaction();
singlePhaseEnlistment.Committed();
}
_callbacks = null;
}
_inTransaction = false;
}
public void Initialize()
{
_log.Debug("Initialize");
_npgsqlTx = _connection.BeginTransaction(ConvertIsolationLevel(_isolationLevel));
_inTransaction = true;
}
public byte[] Promote()
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Promote");
_rm = CreateResourceManager();
// may not be null if Prepare or Enlist is called first
if (_callbacks == null)
{
_callbacks = new NpgsqlTransactionCallbacks(_connection);
}
byte[] token = _rm.Promote(_callbacks);
// mostly likely case for this is the transaction has been prepared.
if (_npgsqlTx != null)
{
// cancel the NpgsqlTransaction since this will
// be handled by a two phase commit.
_npgsqlTx.Cancel();
_npgsqlTx.Dispose();
_npgsqlTx = null;
_connection.PromotableLocalTransactionEnded();
}
return token;
}
public void commit()
{
trans.Commit();
trans = con.BeginTransaction(IsolationLevel.Chaos);
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
PostgreSqlClient.NpgsqlConnection pgConnection = null;
PostgreSqlClient.NpgsqlTransaction pgTransaction = null;
try
{
// Create a new connection.
using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
{
// Open the connection.
pgConnection.Open();
// Start a new transaction.
pgTransaction = pgConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.PostgreSqlDataType, commandText), pgConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = pgTransaction;
if (values != null)
{
foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
pgTransaction.Commit();
// Close the database connection.
pgConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (pgTransaction != null)
{
pgTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (pgConnection != null)
{
pgConnection.Close();
}
}
}
/// <summary>トランザクションのコミット</summary>
/// <remarks>必要に応じて利用する。</remarks>
public override void CommitTransaction()
{
// Txオブジェクトの存在チェック
if (this._tx == null)
{
// nullのためなにもしない。
}
else
{
// トランザクションのコミット
this._tx.Commit();
// nullクリア
this._tx = null;
}
}
public void Rollback(SinglePhaseEnlistment singlePhaseEnlistment)
{
NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, "Rollback");
// try to rollback the transaction with either the
// ADO.NET transaction or the callbacks that managed the
// two phase commit transaction.
if (_npgsqlTx != null)
{
_npgsqlTx.Rollback();
_npgsqlTx.Dispose();
_npgsqlTx = null;
singlePhaseEnlistment.Aborted();
_connection.PromotableLocalTransactionEnded();
}
else if (_callbacks != null)
{
if (_rm != null)
{
_rm.RollbackWork(_callbacks.GetName());
singlePhaseEnlistment.Aborted();
}
else
{
_callbacks.RollbackTransaction();
singlePhaseEnlistment.Aborted();
}
_callbacks = null;
}
_inTransaction = false;
}
/// <summary>トランザクションのロールバック</summary>
/// <remarks>必要に応じて利用する。</remarks>
public override void RollbackTransaction()
{
// Txオブジェクトの存在チェック
if (this._tx == null)
{
// nullのためなにもしない。
}
else
{
// トランザクションのロールバック
this._tx.Rollback();
// nullクリア
this._tx = null;
}
}
protected object ExecuteScalar(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 cmd.ExecuteScalar();
}
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();
}
/// <summary>
/// Initializes a new instance of the <see cref="NpgsqlCommand">NpgsqlCommand</see> class with the text of the query, a <see cref="NpgsqlConnection">NpgsqlConnection</see>, and the <see cref="NpgsqlTransaction">NpgsqlTransaction</see>.
/// </summary>
/// <param name="cmdText">The text of the query.</param>
/// <param name="connection">A <see cref="NpgsqlConnection">NpgsqlConnection</see> that represents the connection to a PostgreSQL server.</param>
/// <param name="transaction">The <see cref="NpgsqlTransaction">NpgsqlTransaction</see> in which the <see cref="NpgsqlCommand">NpgsqlCommand</see> executes.</param>
public NpgsqlCommand(string cmdText, NpgsqlConnection connection, NpgsqlTransaction transaction)
{
Init(cmdText);
Connection = connection;
Transaction = transaction;
}