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(); } } }