|
public BeginTransaction ( ) : |
||
| return | ||
public void Append(string name, byte[] data, long expectedVersion)
{
using (var conn = new NpgsqlConnection(_connectionString))
{
conn.Open();
using (var tx = conn.BeginTransaction())
{
var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx);
const string txt =
@"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data)
VALUES(:customerId, :name, :version, :data)";
using (var cmd = new NpgsqlCommand(txt, conn, tx))
{
cmd.Parameters.AddWithValue(":name", name);
cmd.Parameters.AddWithValue(":version", version+1);
cmd.Parameters.AddWithValue(":data", data);
cmd.Parameters.AddWithValue(":customerId", customerId);
cmd.ExecuteNonQuery();
}
tx.Commit();
}
}
}
public static void CleanTables(NpgsqlConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
var script = GetStringResource(
typeof (PostgreSqlTestObjectsInitializer).Assembly,
"Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));
//connection.Execute(script);
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
using (var command = new NpgsqlCommand(script, connection, transaction))
{
command.CommandTimeout = 120;
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException ex)
{
throw;
}
}
}
static void Main(string[] args)
{
// Connect to a PostgreSQL database
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres; " +
"Password=1905;Database=Lab3;");
conn.Open();
// Start a transaction as it is required to work with result sets (cursors) in PostgreSQL
NpgsqlTransaction tran = conn.BeginTransaction();
// Define a command to call add_ogrenci() procedure
NpgsqlCommand command1 = new NpgsqlCommand("add_ogrenci(9060256,Fatih,Taştemur,01-01-1991,Ankara,50693671313 )", conn);
command1.CommandType = CommandType.StoredProcedure;
NpgsqlCommand command2 = new NpgsqlCommand("count_ogrenci", conn);
command2.CommandType = CommandType.StoredProcedure;
// Execute the procedure and obtain a result set
NpgsqlDataReader dr1 = command1.ExecuteReader();
NpgsqlDataReader dr2 = command2.ExecuteReader();
// Output
while (dr1.Read())
Console.WriteLine("Adding records to a successful");
while (dr2.Read())
return sayi;
tran.Commit();
conn.Close();
}
public static void CleanTables(NpgsqlConnection connection)
{
if (connection == null) throw new ArgumentNullException(nameof(connection));
string script = null;
#if (NETCORE1 || NETCORE50 || NETSTANDARD1_5 || NETSTANDARD1_6)
script = GetStringResource(
typeof(PostgreSqlTestObjectsInitializer).GetTypeInfo().Assembly,
$"Hangfire.PostgreSql.NetCore.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));
#else
script = GetStringResource(
typeof (PostgreSqlTestObjectsInitializer).Assembly,
"Hangfire.PostgreSql.Tests.Clean.sql").Replace("'hangfire'", string.Format("'{0}'", ConnectionUtils.GetSchemaName()));
#endif
//connection.Execute(script);
using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
using (var command = new NpgsqlCommand(script, connection, transaction))
{
command.CommandTimeout = 120;
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
throw;
}
}
}
public PostgresProcessor(NpgsqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options)
: base(generator, announcer, options)
{
Connection = connection;
connection.Open();
Transaction = connection.BeginTransaction();
}
public int Salvarcliente(string nome, int codempresa)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
int id;
using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans))
{
id = int.Parse(cmd.ExecuteScalar().ToString());
}
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
return id;
}
}
catch (Exception)
{
trans.Rollback();
return 0;
}
}
}
}
public void SalvarGrupo(GrupclienteDto entidade)
{
using (var conn = new NpgsqlConnection(Connectionstring))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
using (
var cmd =
new NpgsqlCommand(
"INSERT INTO grupcliente (grcl_nome, grcl_ativo,empr_codigo) values ('" + entidade.grcl_nome + "'," + entidade.grcl_ativo + ","+entidade.empr_codigo+")", conn,
trans))
{
cmd.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception)
{
trans.Rollback();
}
}
}
}
private static void TruncateAndCopy(string table, string[] columns, BlockingCollection <object[]> data, Action <string> log)
{
try
{
log("Connecting to database");
using (var c = new Npgsql.NpgsqlConnection("Host=/var/run/postgresql;Port=5433"))
{
c.Open();
using (var t = c.BeginTransaction())
{
log($"TRUNCATE {Q(table)}");
using (var cmd = new NpgsqlCommand($"TRUNCATE {Q(table)}", c))
cmd.ExecuteNonQuery();
log($"COPY {Q(table)}");
Copy(
c,
tablename: table,
colnames: columns,
data: data.GetConsumingEnumerable()
);
log("COMMIT");
t.Commit();
}
}
}
finally
{
log("Disposing data...");
data.CompleteAdding();
data.Dispose();
}
}
//Ska detta vara med?
/* public static List<string> GetGolfSpelarInfo(int GolfId)
{
List<string> medlemslista = new List<string>();
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[conString];
NpgsqlConnection conn = new NpgsqlConnection(settings.ConnectionString);
conn.Open();
NpgsqlCommand command2 = new NpgsqlCommand(@"SELECT *
FROM
golfspelare
WHERE
golf_id = :GolfId;
", conn);
command2.Parameters.Add(new NpgsqlParameter("GolfId", DbType.Int32));
command2.Parameters[0].Value = Convert.ToString(GolfId);
NpgsqlDataReader dr = command2.ExecuteReader();
while (dr.Read())
{
medlemslista.Add((string)dr["*"]);
}
conn.Close();
return medlemslista;
}*/
public static void AddSpelareTillTävling(string Fornamn, string Efternamn, string Tävlingsnamn)
{
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[conString];
NpgsqlConnection conn = new NpgsqlConnection(settings.ConnectionString);
NpgsqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction();
NpgsqlCommand command3 = new NpgsqlCommand(@"SELECT golf_id
FROM golfspelare
WHERE fornamn = :Fornamn
AND efternamn =:Efternamn", conn);
command3.Parameters.Add(new NpgsqlParameter("Fornamn", DbType.String));
command3.Parameters[0].Value = Fornamn;
command3.Parameters.Add(new NpgsqlParameter("Efternamn", DbType.String));
command3.Parameters[1].Value = Efternamn;
command3.Transaction = trans;
int GolfId = (int)command3.ExecuteScalar();
//Tävlingsnamn är tomt när den anropas
NpgsqlCommand command4 = new NpgsqlCommand(@"SELECT id
FROM tavling
WHERE tavlingsnamn = :TävlingsNamn", conn);
command4.Parameters.Add(new NpgsqlParameter("TävlingsNamn", DbType.String));
command4.Parameters[0].Value = Tävlingsnamn;
command4.Transaction = trans;
int TävlingsID = (int)command4.ExecuteScalar();
/* NpgsqlCommand command4 = new NpgsqlCommand(@"SELECT id
FROM tavling
WHERE tavlingsnamn = :TävlingsNamn", conn);
command4.Parameters.Add(new NpgsqlParameter("tavlingsnamn", DbType.String));
command4.Parameters[0].Value = Tävlingsnamn;
command4.Transaction = trans;
int TävlingsID = (int)command4.ExecuteScalar(); */
NpgsqlCommand command5 = new NpgsqlCommand(@"INSERT INTO spelar_resultat (golf_id, tavlings_id)
VALUES (:GolfId, :TävlingsID)", conn);
command5.Parameters.Add(new NpgsqlParameter("GolfId", DbType.Int32));
command5.Parameters[0].Value = GolfId;
command5.Parameters.Add(new NpgsqlParameter("TävlingsID", DbType.Int32));
command5.Parameters[1].Value = TävlingsID;
command5.Transaction = trans;
int numberOfAffectedRows = command5.ExecuteNonQuery();
trans.Commit();
}
catch (NpgsqlException ex)
{
trans.Rollback();
}
finally
{
conn.Close();
}
}
public PostgreSqlTransaction(NpgsqlConnection connection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
_transaction = connection.BeginTransaction(IsolationLevel.Serializable);
}
public void DownloadQuestao(Dominio.Questao questao)
{
Dominio.Configuracao configuracao = new Dominio.Configuracao();
string caminhoEntrada = System.IO.Path.Combine(configuracao.CaminhoSalvarSubmissoes, questao.ArquivoEntrada);
string caminhoSaida = System.IO.Path.Combine(configuracao.CaminhoSalvarSubmissoes, questao.ArquivoSaida);
NpgsqlConnection conexao = new NpgsqlConnection("Server=187.45.196.224;Database=bubblesort9;User ID=bubblesort9;Password=BSboca;");
NpgsqlTransaction transacao = null;
try
{
conexao.Open();
transacao = conexao.BeginTransaction();
LargeObjectManager lbm = new LargeObjectManager(conexao);
LargeObject lo = lbm.Open(questao.IdArquivoEntrada, LargeObjectManager.READ);
FileStream fsout = File.OpenWrite(caminhoEntrada);
byte[] buf = new byte[lo.Size()];
buf = lo.Read(lo.Size());
fsout.Write(buf, 0, (int)lo.Size());
fsout.Flush();
fsout.Close();
lo.Close();
lo = lbm.Open(questao.IdArquivoSaida, LargeObjectManager.READ);
fsout = File.OpenWrite(caminhoSaida);
byte[] buffer = new byte[lo.Size()];
buffer = lo.Read(lo.Size());
fsout.Write(buffer, 0, (int)lo.Size());
fsout.Flush();
fsout.Close();
lo.Close();
transacao.Commit();
}
catch
{
if (transacao != null)
transacao.Rollback();
throw;
}
finally
{
conexao.Close();
}
}
/// <summary>
/// Gets a fresh, open and ready-to-use connection wrapper
/// </summary>
public async Task<PostgresConnection> GetConnection()
{
var connection = new NpgsqlConnection(_connectionString);
await connection.OpenAsync();
var currentTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
return new PostgresConnection(connection, currentTransaction);
}
public static long SaveExchangeRates(string catalog, int officeId, string baseCurrency, IEnumerable<ExchangeRate> exchangeRates)
{
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
string sql = "UPDATE core.exchange_rates SET status = false WHERE office_id=@OfficeId";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@OfficeId", officeId);
command.ExecuteNonQuery();
}
sql = "INSERT INTO core.exchange_rates(office_id, status) SELECT @OfficeId, true RETURNING exchange_rate_id;";
long exchangeRateId;
using (NpgsqlCommand er = new NpgsqlCommand(sql, connection))
{
er.Parameters.AddWithValue("@OfficeId", officeId);
exchangeRateId = Conversion.TryCastLong(er.ExecuteScalar());
}
foreach (ExchangeRate exchangeRate in exchangeRates)
{
sql =
"INSERT INTO core.exchange_rate_details(exchange_rate_id, local_currency_code, foreign_currency_code, unit, exchange_rate) " +
"SELECT @ExchangeRateId, @LocalCurrencyCode, @ForiegnCurrencyCode, 1, @ExchangeRate;";
using (NpgsqlCommand rate = new NpgsqlCommand(sql, connection))
{
rate.Parameters.AddWithValue("@ExchangeRateId", exchangeRateId);
rate.Parameters.AddWithValue("@LocalCurrencyCode", baseCurrency);
rate.Parameters.AddWithValue("@ForiegnCurrencyCode", exchangeRate.CurrencyCode);
rate.Parameters.AddWithValue("@ExchangeRate", exchangeRate.Rate);
rate.ExecuteNonQuery();
}
}
transaction.Commit();
return exchangeRateId;
}
catch (NpgsqlException ex)
{
Log.Warning(@"Could not update exchange rate. {Exception}", ex);
transaction.Rollback();
throw;
}
}
}
}
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();
}
/// <summary>
/// Initializes a new instance of the <see cref="Connection" /> class.
/// </summary>
/// <param name="connectionInfo">The connection information.</param>
/// <param name="options">The options.</param>
public Connection(IConnectionInformation connectionInfo,
PostgreSqlMessageQueueTransportOptions options)
{
NpgsqlConnection = new NpgsqlConnection(connectionInfo.ConnectionString);
NpgsqlConnection.Open();
if (options.EnableHoldTransactionUntilMessageCommited)
{
NpgsqlTransaction = NpgsqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
public static bool Save(string catalog, int userId, string book, long id, Collection<Attachment> attachments)
{
const string sql =
"INSERT INTO core.attachments(user_id, resource, resource_key, resource_id, original_file_name, file_extension, file_path, comment) " +
"SELECT @UserId, core.attachment_lookup.resource, core.attachment_lookup.resource_key, @ResourceId, @OriginalFileName, @FileExtension, @FilePath, @Comment" +
" FROM core.attachment_lookup WHERE book=@Book;";
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
if (attachments != null && attachments.Count > 0)
{
try
{
foreach (Attachment attachment in attachments)
{
using (NpgsqlCommand attachmentCommand = new NpgsqlCommand(sql, connection))
{
attachmentCommand.Parameters.AddWithValue("@UserId", userId);
attachmentCommand.Parameters.AddWithValue("@Book", book);
attachmentCommand.Parameters.AddWithValue("@ResourceId", id);
attachmentCommand.Parameters.AddWithValue("@OriginalFileName",
attachment.OriginalFileName);
attachmentCommand.Parameters.AddWithValue("@FileExtension",
Path.GetExtension(attachment.OriginalFileName));
attachmentCommand.Parameters.AddWithValue("@FilePath", attachment.FilePath);
attachmentCommand.Parameters.AddWithValue("@Comment", attachment.Comment);
attachmentCommand.ExecuteNonQuery();
}
}
transaction.Commit();
return true;
}
catch (NpgsqlException)
{
Log.Warning(
@"Could not insert attachment into database. Book: {Book}, Id: {Id}, Attachments: {Attachments}.\n{Sql}",
book, id, attachments, sql);
transaction.Rollback();
return false;
}
}
}
}
return false;
}
public void DoWork()
{
using (NpgsqlConnection conn = new NpgsqlConnection(GlobalUtils.TopSecret.PostgresCS))
using (NpgsqlCommand command = new NpgsqlCommand())
{
try
{
conn.Open();
//conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (NpgsqlTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
NpgsqlDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader);
while (reader.NextResult())
ShowResultSet(reader);
}
}
//var stats = conn.RetrieveStatistics();
//using (TextWriter tw = new StreamWriter(path + ".stats"))
//{
// tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
// Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
// stats["SelectRows"],
// stats["IduRows"]);
//}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}
static void Main(string[] args)
{
//Npgsql.NpgsqlEventLog.Level = Npgsql.LogLevel.Debug;
//Npgsql.NpgsqlEventLog.LogName = "c:\\cygwin\\tmp\\npgsql-debug-log2";
string connstr = "Server=hagbard.apathetic.discordia.org.uk;Database=moma;User ID=dick;Password=test";
NpgsqlConnection conn = new NpgsqlConnection(connstr);
conn.Open();
NpgsqlTransaction trans = conn.BeginTransaction();
using (NpgsqlCommand command = new NpgsqlCommand ("SELECT rep.id, todo.todo, niex.niex, miss.miss, pinv.pinv, total.total FROM report rep LEFT JOIN (SELECT issue_report.report_id, COUNT(issue_report.report_id) AS miss FROM issue_report, issue, issue_type WHERE issue.issue_type_id = issue_type.id AND issue_type.lookup_name = 'MISS' AND issue_report.issue_id = issue.id GROUP BY report_id) AS miss ON rep.id = miss.report_id LEFT JOIN (SELECT issue_report.report_id, COUNT(issue_report.report_id) AS niex FROM issue_report, issue, issue_type WHERE issue.issue_type_id = issue_type.id AND issue_type.lookup_name = 'NIEX' AND issue_report.issue_id = issue.id GROUP BY report_id) AS niex ON rep.id = niex.report_id LEFT JOIN (SELECT issue_report.report_id, COUNT(issue_report.report_id) AS pinv FROM issue_report, issue, issue_type WHERE issue.issue_type_id = issue_type.id AND issue_type.lookup_name = 'PINV' AND issue_report.issue_id = issue.id GROUP BY report_id) AS pinv ON rep.id = pinv.report_id LEFT JOIN (SELECT issue_report.report_id, COUNT(issue_report.report_id) AS todo FROM issue_report, issue, issue_type WHERE issue.issue_type_id = issue_type.id AND issue_type.lookup_name = 'TODO' AND issue_report.issue_id = issue.id GROUP BY report_id) AS todo ON rep.id = todo.report_id LEFT JOIN (SELECT report_id, COUNT(report_id) AS total FROM issue_report GROUP BY report_id) AS total ON rep.id = total.report_id;", conn)) {
using (NpgsqlDataReader dr = command.ExecuteReader ()) {
while (dr.Read ()) {
// Should have 6 columns
if (dr.FieldCount != 6) {
throw new Exception ("Didn't get 6 columns");
}
long todo = 0;
long niex = 0;
long miss = 0;
long pinv = 0;
long total = 0;
if (!dr.IsDBNull(1)) {
todo = dr.GetInt64(1);
}
if (!dr.IsDBNull(2)) {
niex = dr.GetInt64(2);
}
if (!dr.IsDBNull(3)) {
miss = dr.GetInt64(3);
}
if (!dr.IsDBNull(4)) {
pinv = dr.GetInt64(4);
}
if (!dr.IsDBNull(5)) {
total = dr.GetInt64(5);
}
UpdateReportTotals (conn, dr.GetInt32(0), todo, niex, miss, pinv, total);
}
}
}
trans.Commit();
//trans.Rollback();
conn.Close();
}
public string getData(string latitude, string longitude)
{
//string latitude = latlon.Split(',')[0];
//string longitude = latlon.Split(',')[1];
string connstring = ConfigurationManager.ConnectionStrings["gisdb"].ConnectionString;
NpgsqlConnection conn = new NpgsqlConnection(connstring);
NpgsqlTransaction t = null;
DataSet dsStops = new DataSet();
try
{
conn.Open();
t = conn.BeginTransaction();
//select * from nearest_stops_detail('51.4027','-0.2653');
NpgsqlCommand command = new NpgsqlCommand("select * from nearest_stops_detail(:latitude,:longitude)", conn);
// Now add the parameter to the parameter collection of the command specifying its type.
command.Parameters.Add(new NpgsqlParameter("latitude", NpgsqlTypes.NpgsqlDbType.Text));
command.Parameters.Add(new NpgsqlParameter("longitude", NpgsqlTypes.NpgsqlDbType.Text));
command.Prepare();
command.Parameters[0].Value = latitude;
command.Parameters[1].Value = longitude;
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
da.Fill(dsStops);
}
finally
{
t.Commit();
conn.Close();
}
StringWriter sw = new StringWriter();
XmlWriter xw = XmlWriter.Create(sw);
dsStops.WriteXml(xw);
return sw.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;
}
}
public void RollbackOnClose()
{
ExecuteNonQuery("DROP TABLE IF EXISTS rollback_on_close");
ExecuteNonQuery("CREATE TABLE rollback_on_close (name TEXT)");
NpgsqlTransaction tx;
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
tx = conn.BeginTransaction();
ExecuteNonQuery("INSERT INTO rollback_on_close (name) VALUES ('X')", conn, tx);
}
Assert.That(tx.Connection, Is.Null);
Assert.That(ExecuteScalar("SELECT COUNT(*) FROM rollback_on_close"), Is.EqualTo(0));
}
protected static ConnectionHolder CreateConnection(string connectionString)
{
var connection = new NpgsqlConnection(connectionString);
connection.Open();
if (Transaction.Current == null)
{
var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
return ConnectionHolder.ForTransactionalWork(connection, transaction);
}
return ConnectionHolder.ForNonTransactionalWork(connection);
}
public void DownloadArquivos(Dominio.Submissao submissao)
{
Dominio.Configuracao configuracao = new Dominio.Configuracao();
string caminho=string.Empty;
switch (submissao.Linguagem)
{
case Dominio.Linguagem.C:
caminho = System.IO.Path.Combine(configuracao.CaminhoSalvarSubmissoes, submissao.Id + ".c");
break;
case Dominio.Linguagem.Cmaismais:
caminho = System.IO.Path.Combine(configuracao.CaminhoSalvarSubmissoes, submissao.Id + ".cpp");
break;
}
NpgsqlConnection conexao = new NpgsqlConnection("Server=187.45.196.224;Database=bubblesort9;User ID=bubblesort9;Password=BSboca;");
NpgsqlTransaction transacao = null;
try
{
conexao.Open();
transacao = conexao.BeginTransaction();
LargeObjectManager lbm = new LargeObjectManager(conexao);
LargeObject lo = lbm.Open(submissao.IdData, LargeObjectManager.READWRITE);
FileStream fsout = File.OpenWrite(caminho);
byte[] buf = new byte[lo.Size()];
buf = lo.Read(lo.Size());
fsout.Write(buf, 0, (int)lo.Size());
fsout.Flush();
fsout.Close();
lo.Close();
transacao.Commit();
}
catch
{
if (transacao != null)
transacao.Rollback();
throw;
}
finally
{
conexao.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
public void executeQuery(string sql)
{
using (var conn = new NpgsqlConnection(ConfigurationManager.AppSettings.Get("DataSource")))
{
conn.Open();
using (var sqlt = conn.BeginTransaction())
{
using (NpgsqlCommand command = conn.CreateCommand())
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
sqlt.Commit();
}
conn.Close();
}
}
private static void addAtoms(String connectionParams)
{
NpgsqlConnection connection = new NpgsqlConnection(connectionParams);
connection.Open();
NpgsqlTransaction transaction = connection.BeginTransaction();
try
{
RoutesReader routesReader = new RoutesReader(connection);
AtomGenerator generator = new AtomGenerator(connection);
RouteGenerator routeGenerator = new RouteGenerator(connection);
generator.deleteAllAtomsAndActivities();
Route source1 = routesReader.readRouteByName("Escape3");
Route source2 = routesReader.readRouteByName("Escape3_reversed");
//Route source3 = routesReader.readRouteByName("Source3");
//Route cornerRoute = routesReader.readRouteByName("Corner");
//addAtomsToRoute(source1, generator, 100);
//addAtomsToRoute(source2, generator, 100);
//addAtomsToRoute(source3, generator, 100);
//addAtomsToRoute(cornerRoute, generator);
//AtomObject ambulance = new AtomObject("Ambulance1", -1, 34.8514473088014, 32.1008536878526);
//generator.createAtom(ambulance);
//generator.addAtomToTreeObject(ambulance);
//routeGenerator.generateReversedRoute("Escape3");
transaction.Commit();
}
catch (Exception exception)
{
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
Console.WriteLine("Rollback failed :(");
}
}
connection.Close();
}
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");
}
/// <summary>Saves a snapshot of the specified event source.</summary>
public void SaveSnapshot(Snapshot snapshot)
{
using (var connection = new NpgsqlConnection(_connectionString))
{
// Open connection and begin a transaction so we can
// commit or rollback all the changes that has been made.
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
using (var dataStream = new MemoryStream())
{
var formatter = new BinaryFormatter();
formatter.Serialize(dataStream, snapshot.Payload);
byte[] data = dataStream.ToArray();
using (var command = new NpgsqlCommand(Queries.InsertSnapshot, transaction.Connection))
{
command.Transaction = transaction;
command.Parameters.AddWithValue("EventSourceId", snapshot.EventSourceId);
command.Parameters.AddWithValue("Version", snapshot.Version);
command.Parameters.AddWithValue("Type", snapshot.GetType().AssemblyQualifiedName);
command.Parameters.AddWithValue("Data", data);
command.ExecuteNonQuery();
}
}
// Everything is handled, commit transaction.
transaction.Commit();
}
catch
{
// Something went wrong, rollback transaction.
transaction.Rollback();
throw;
}
}
}
}
public void addMatchResults(string sqlString)
{
try
{
string connstring = String.Format("Server=127.0.0.1;Port=5432;" +
"User Id=postgres;Password=admin;Database=league;");
NpgsqlConnection conn = new NpgsqlConnection(connstring);
conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand da = new NpgsqlCommand(sqlString, conn);
da.ExecuteNonQuery();
tran.Commit();
conn.Close();
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
MessageBox.Show(msg.ToString());
throw;
}
}
public void CheckoutBook(int BookID, string DueDate)
{
var connectionString = "Server = 127.0.0.1; Port = 5432; Database = bookish; User Id = Bookish; Password = password; ";
var UserID = AccessDB.FindUserIDByEmail(Email, connectionString);
var anyCopies = AccessDB.AreThereAvailableCopies(BookID, connectionString);
var DateTimeDue = DateTime.Parse(DueDate);
if (anyCopies == true)
{
NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
using (var transaction = connection.BeginTransaction())
{
connection.Execute("INSERT INTO \"Books Taken Out\" (\"UserID\",\"BookID\", \"DueDate\") VALUES (@UserID, @BookID, @DateTimeDue)", new { UserID, BookID, DateTimeDue });
connection.Execute("UPDATE \"Books\" SET \"AvailableCopies\" = \"AvailableCopies\" - 1 WHERE \"BookID\" = @BookID", new { BookID });
transaction.Commit();
}
connection.Close();
}
}
public void TestPostresqlArrayParameters()
{
using (var conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=dappertest;Password=dapperpass;Database=dappertest;Encoding=UNICODE"))
{
conn.Open();
IDbTransaction transaction = conn.BeginTransaction();
conn.Execute("create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);");
conn.Execute("insert tcat(breed, name) values(:breed, :name) ", Cats);
var r = conn.Query<Cat>("select * from tcat where id=any(:catids)", new { catids = new[] { 1, 3, 5 } });
r.Count().IsEqualTo(3);
r.Count(c => c.Id == 1).IsEqualTo(1);
r.Count(c => c.Id == 3).IsEqualTo(1);
r.Count(c => c.Id == 5).IsEqualTo(1);
transaction.Rollback();
}
}
public void commit()
{
trans.Commit();
trans = con.BeginTransaction(IsolationLevel.Chaos);
}
public static long Add(string catalog, DateTime valueDate, DateTime bookDate, int officeId, int userId,
long loginId, int costCenterId, string referenceNumber, Collection<JournalDetail> details,
Collection<Attachment> attachments)
{
if (details == null)
{
throw new InvalidOperationException(Errors.NoTransactionToPost);
}
if (details.Count.Equals(0))
{
throw new InvalidOperationException(Errors.NoTransactionToPost);
}
decimal debitTotal = (from detail in details select detail.LocalCurrencyDebit).Sum();
decimal creditTotal = (from detail in details select detail.LocalCurrencyCredit).Sum();
if (debitTotal != creditTotal)
{
throw new InvalidOperationException(Errors.ReferencingSidesNotEqual);
}
int decimalPlaces = CultureManager.GetCurrencyDecimalPlaces();
if ((from detail in details
where
decimal.Round(detail.Credit*detail.ExchangeRate, decimalPlaces) !=
decimal.Round(detail.LocalCurrencyCredit, decimalPlaces) ||
decimal.Round(detail.Debit*detail.ExchangeRate, decimalPlaces) !=
decimal.Round(detail.LocalCurrencyDebit, decimalPlaces)
select detail).Any())
{
throw new InvalidOperationException(Errors.ReferencingSidesNotEqual);
}
using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog)))
{
connection.Open();
using (NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
string sql =
"INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, book_date, user_id, login_id, office_id, cost_center_id, reference_number) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LoginId), @Book, @ValueDate, @BookDate, @UserId, @LoginId, @OfficeId, @CostCenterId, @ReferenceNumber;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
long transactionMasterId;
using (NpgsqlCommand master = new NpgsqlCommand(sql, connection))
{
master.Parameters.AddWithValue("@ValueDate", valueDate);
master.Parameters.AddWithValue("@BookDate", bookDate);
master.Parameters.AddWithValue("@OfficeId", officeId);
master.Parameters.AddWithValue("@UserId", userId);
master.Parameters.AddWithValue("@LoginId", loginId);
master.Parameters.AddWithValue("@Book", "Journal");
master.Parameters.AddWithValue("@CostCenterId", costCenterId);
master.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
transactionMasterId = Conversion.TryCastLong(master.ExecuteScalar());
}
foreach (JournalDetail model in details)
{
sql =
"INSERT INTO transactions.transaction_details(value_date, transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, currency_code, amount_in_currency, local_currency_code, er, amount_in_local_currency) " +
"SELECT @ValueDate, @TransactionMasterId, @TranType, core.get_account_id_by_account_number(@AccountNumber::text), @StatementReference, office.get_cash_repository_id_by_cash_repository_code(@CashRepositoryCode), @CurrencyCode, @AmountInCurrency, transactions.get_default_currency_code_by_office_id(@OfficeId), @Er, @AmountInLocalCurrency;";
if (model.Credit > 0 && model.Debit > 0)
{
throw new InvalidOperationException(Errors.BothSidesCannotHaveValue);
}
if (model.LocalCurrencyCredit > 0 && model.LocalCurrencyDebit > 0)
{
throw new InvalidOperationException(Errors.BothSidesCannotHaveValue);
}
decimal amountInCurrency;
decimal amountInLocalCurrency;
string tranType;
if (model.Credit.Equals(0) && model.Debit > 0)
{
tranType = "Dr";
amountInCurrency = model.Debit;
amountInLocalCurrency = model.LocalCurrencyDebit;
}
else
{
tranType = "Cr";
amountInCurrency = model.Credit;
amountInLocalCurrency = model.LocalCurrencyCredit;
}
using (NpgsqlCommand transactionDetail = new NpgsqlCommand(sql, connection))
{
transactionDetail.Parameters.AddWithValue("@ValueDate", valueDate);
transactionDetail.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
transactionDetail.Parameters.AddWithValue("@TranType", tranType);
transactionDetail.Parameters.AddWithValue("@AccountNumber", model.AccountNumber);
transactionDetail.Parameters.AddWithValue("@StatementReference",
model.StatementReference);
transactionDetail.Parameters.AddWithValue("@CashRepositoryCode",
model.CashRepositoryCode);
transactionDetail.Parameters.AddWithValue("@CurrencyCode", model.CurrencyCode);
transactionDetail.Parameters.AddWithValue("@AmountInCurrency", amountInCurrency);
transactionDetail.Parameters.AddWithValue("@OfficeId", officeId);
transactionDetail.Parameters.AddWithValue("@Er", model.ExchangeRate);
transactionDetail.Parameters.AddWithValue("@AmountInLocalCurrency",
amountInLocalCurrency);
transactionDetail.ExecuteNonQuery();
}
}
#region Attachment
if (attachments != null && attachments.Count > 0)
{
foreach (Attachment attachment in attachments)
{
sql =
"INSERT INTO core.attachments(user_id, resource, resource_key, resource_id, original_file_name, file_extension, file_path, comment) SELECT @UserId, @Resource, @ResourceKey, @ResourceId, @OriginalFileName, @FileExtension, @FilePath, @Comment;";
using (NpgsqlCommand attachmentCommand = new NpgsqlCommand(sql, connection))
{
attachmentCommand.Parameters.AddWithValue("@UserId", userId);
attachmentCommand.Parameters.AddWithValue("@Resource",
"transactions.transaction_master");
attachmentCommand.Parameters.AddWithValue("@ResourceKey", "transaction_master_id");
attachmentCommand.Parameters.AddWithValue("@ResourceId", transactionMasterId);
attachmentCommand.Parameters.AddWithValue("@OriginalFileName",
attachment.OriginalFileName);
attachmentCommand.Parameters.AddWithValue("@FileExtension",
Path.GetExtension(attachment.OriginalFileName));
attachmentCommand.Parameters.AddWithValue("@FilePath", attachment.FilePath);
attachmentCommand.Parameters.AddWithValue("@Comment", attachment.Comment);
attachmentCommand.ExecuteNonQuery();
}
}
}
#endregion Attachment
#region Auto Verification
sql = "SELECT * FROM transactions.auto_verify(@TranId::bigint, @OfficeId::integer);";
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@TranId", transactionMasterId);
command.Parameters.AddWithValue("@OfficeId", officeId);
command.ExecuteNonQuery();
}
#endregion
transaction.Commit();
return transactionMasterId;
}
catch (NpgsqlException ex)
{
Log.Warning(
@"Could not post transaction. ValueDate: {ValueDate}, OfficeId: {OfficeId}, UserId: {UserId}, LoginId: {LoginId}, CostCenterId:{CostCenterId}, ReferenceNumber: {ReferenceNumber}, Details: {Details}, Attachments: {Attachments}. {Exception}.",
valueDate, officeId, userId, loginId, costCenterId, referenceNumber, details, attachments,
ex);
transaction.Rollback();
throw;
}
catch (InvalidOperationException ex)
{
Log.Warning(
@"Could not post transaction. ValueDate: {ValueDate}, OfficeId: {OfficeId}, UserId: {UserId}, LoginId: {LoginId}, CostCenterId:{CostCenterId}, ReferenceNumber: {ReferenceNumber}, Details: {Details}, Attachments: {Attachments}. {Exception}.",
valueDate, officeId, userId, loginId, costCenterId, referenceNumber, details, attachments,
ex);
transaction.Rollback();
throw;
}
}
}
}
public static long Add(DateTime valueDate, int officeId, int userId, long logOnId, int costCenterId, string referenceNumber, Collection<MixERP.Net.Common.Models.Transactions.TransactionDetailModel> details)
{
if(details == null)
{
return 0;
}
if(details.Count.Equals(0))
{
return 0;
}
string sql = string.Empty;
long transactionMasterId = 0;
decimal debitTotal = details.Sum(d => (d.Debit));
decimal creditTotal = details.Sum(d => (d.Credit));
string tranType = string.Empty;
decimal amount = 0;
if(debitTotal != creditTotal)
{
return 0;
}
using(NpgsqlConnection connection = new NpgsqlConnection(DBFactory.DBConnection.ConnectionString()))
{
connection.Open();
using(NpgsqlTransaction transaction = connection.BeginTransaction())
{
try
{
sql = "INSERT INTO transactions.transaction_master(transaction_master_id, transaction_counter, transaction_code, book, value_date, user_id, login_id, office_id, cost_center_id, reference_number) SELECT nextval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id')), transactions.get_new_transaction_counter(@ValueDate), transactions.get_transaction_code(@ValueDate, @OfficeId, @UserId, @LogOnId), @Book, @ValueDate, @UserId, @LogOnId, @OfficeId, @CostCenterId, @ReferenceNumber;SELECT currval(pg_get_serial_sequence('transactions.transaction_master', 'transaction_master_id'));";
using(NpgsqlCommand master = new NpgsqlCommand(sql, connection))
{
master.Parameters.AddWithValue("@ValueDate", valueDate);
master.Parameters.AddWithValue("@OfficeId", officeId);
master.Parameters.AddWithValue("@UserId", userId);
master.Parameters.AddWithValue("@LogOnId", logOnId);
master.Parameters.AddWithValue("@Book", "Journal");
master.Parameters.AddWithValue("@CostCenterId", costCenterId);
master.Parameters.AddWithValue("@ReferenceNumber", referenceNumber);
transactionMasterId = MixERP.Net.Common.Conversion.TryCastLong(master.ExecuteScalar());
}
foreach(MixERP.Net.Common.Models.Transactions.TransactionDetailModel model in details)
{
sql = "INSERT INTO transactions.transaction_details(transaction_master_id, tran_type, account_id, statement_reference, cash_repository_id, amount) SELECT @TransactionMasterId, @TranType, core.get_account_id_by_account_code(@AccountCode::text), @StatementReference, office.get_cash_repository_id_by_cash_repository_name(@CashRepositoryName::text), @Amount;";
if(model.Credit > 0 && model.Debit > 0)
{
throw new InvalidOperationException(MixERP.Net.Common.Helpers.LocalizationHelper.GetResourceString("Warnings", "BothSidesHaveValue"));
}
else
{
if(model.Credit.Equals(0) && model.Debit > 0)
{
tranType = "Dr";
amount = model.Debit;
}
else
{
tranType = "Cr";
amount = model.Credit;
}
using(NpgsqlCommand transactionDetail = new NpgsqlCommand(sql, connection))
{
transactionDetail.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId);
transactionDetail.Parameters.AddWithValue("@TranType", tranType);
transactionDetail.Parameters.AddWithValue("@AccountCode", model.AccountCode);
transactionDetail.Parameters.AddWithValue("@StatementReference", model.StatementReference);
transactionDetail.Parameters.AddWithValue("@CashRepositoryName", model.CashRepositoryName);
transactionDetail.Parameters.AddWithValue("@Amount", amount);
transactionDetail.ExecuteNonQuery();
}
}
}
transaction.Commit();
return transactionMasterId;
}
catch(NpgsqlException)
{
transaction.Rollback();
throw;
}
catch(InvalidOperationException)
{
transaction.Rollback();
throw;
}
}
}
}
/// <summary>
/// Begins a new transaction.
/// </summary>
/// <param name="isolationLevel">The isolation level.</param>
/// <returns>Returns an IDisposeable instance.</returns>
public IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
{
_currentTransaction = _connection.BeginTransaction(isolationLevel);
return(_currentTransaction);
}
/// <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();
}
}
}
