public override IEtlOperationResult Execute(EtlPipelineContext context) { using (var con = new Npgsql.NpgsqlConnection(_connectionString)) { con.Open(); using (var trx = con.BeginTransaction(_isolationLevel)) using (var cmd = con.CreateCommand()) { cmd.CommandText = _commandText; cmd.CommandType = CommandType.Text; cmd.Transaction = trx; foreach (var param in _parameters) { var p = cmd.CreateParameter(); p.ParameterName = param.Key; p.Value = param.Value; cmd.Parameters.Add(p); } cmd.ExecuteNonQuery(); } } return(new EtlOperationResult(true)); }
/// <summary> /// Inserts many Venue Tags /// </summary> /// <returns>Returns a result containing the created resource id</returns> public async Task <Result> CreateVenueTags(int venueId, List <int> tagIds) { try { using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString); await con.OpenAsync().ConfigureAwait(false); var transaction = con.BeginTransaction(); await Task.WhenAll(tagIds.Select(async _ => { return(await con.ExecuteAsync( @"INSERT INTO ""VenueTag""( venueId, tagId ) VALUES ( @VenueId, @TagId )", tagIds.Select(t => new { VenueId = venueId, TagId = t }).ToList() ).ConfigureAwait(false)); }).ToArray()).ConfigureAwait(false); await transaction.CommitAsync().ConfigureAwait(false); return(Result.Ok()); } catch (Exception ex) { return(Result.Fail(ex.ToString())); } }
// pobierz: download // Rysunek: drawing //take a picture from database and convert to Image type public System.Drawing.Image GetDrawing(int idOfOID) { System.Drawing.Image img; using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString())) { if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction()) { NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection); NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID byte[] buf = new byte[lo.Size()]; buf = lo.Read(lo.Size()); using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { ms.Write(buf, 0, lo.Size()); img = System.Drawing.Image.FromStream(ms); } // End Using ms lo.Close(); trans.Commit(); if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } // End Using trans } // End Using connection return(img); } // End Function GetDrawing
/// <summary> /// Query with no return /// </summary> /// <param name="query">Query string</param> /// <param name="connectionString">Database connection string</param> /// <returns>Boolean</returns> public static bool Void(string query, string connectionString) { if (Generics.StringExtensions.IsEmpty(query) || Generics.StringExtensions.IsEmpty(connectionString)) { return(false); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection)) { command.CommandType = System.Data.CommandType.Text; try { connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; command.ExecuteNonQuery(); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(true); }
/// <summary> /// Inserts many Venue Tags /// </summary> /// <returns>Returns a result containing the created resource id</returns> public async Task <Result> ReplaceVenueDocuments(int venueId, List <VenueDocument> documents) { try { using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString); await con.OpenAsync().ConfigureAwait(false); var transaction = con.BeginTransaction(); await con.ExecuteAsync(@"DELETE FROM ""VenueDocuments"" WHERE venueId = @VenueId", new { @VenueId = venueId }).ConfigureAwait(false); if (documents.Count > 0) { await con.ExecuteAsync(@"INSERT INTO ""VenueDocuments"" (venueId, documentId) VALUES (@VenueId, @DocumentId)", documents).ConfigureAwait(false); } await transaction.CommitAsync().ConfigureAwait(false); return(Result.Ok()); } catch (Exception ex) { return(Result.Fail(ex.ToString())); } }
public void Open(bool transaction) { connection = new Npgsql.NpgsqlConnection(connstring); connection.Open(); comm = new Npgsql.NpgsqlCommand("", connection); if (transaction) { comm.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); } }
/// <summary> /// Runs a query and returns void /// </summary> /// <param name="name">Stored function name</param> /// <param name="param">List of parameter to use. Set null for no parameters.</param> /// <param name="connectionString">Connection sting to use</param> /// <param name="cancellationToken">Cancellation Token</param> /// <returns>boolean</returns> public static async Task <bool> VoidAsync(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString, CancellationToken cancellationToken = default(CancellationToken)) { if (Generics.StringExtensions.IsEmpty(name) || Generics.StringExtensions.IsEmpty(connectionString) || cancellationToken.IsCancellationRequested) { return(false); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; if (param != null) { foreach (var item in param) { command.Parameters.Add(item); } } else { command.Parameters.Clear(); } try { if (cancellationToken.IsCancellationRequested) { throw new OperationCanceledException(cancellationToken); } await connection.OpenAsync(cancellationToken); var transaction = connection.BeginTransaction(); command.Transaction = transaction; var t = await command.ExecuteNonQueryAsync(cancellationToken); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(true); }
protected static bool IsSuperUser() { using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { var value = IsSuperUser(conn, trans); trans.Commit(); return(value); } } }
protected static bool TableExists(string tableName) { using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { var value = TableExists(tableName, conn, trans); trans.Commit(); return(value); } } }
/// <summary> /// Runs a query and returns value by using a converter /// </summary> /// <param name="query">Query to run</param> /// <param name="connectionString">Connection to use</param> /// <param name="transformer">Cancellation Token</param> /// <param name="cancellationToken"></param> /// <returns>Object</returns> public static async Task <object> HelperAsync( string query, string connectionString, Func <Npgsql.NpgsqlCommand, CancellationToken, Task <object> > transformer, CancellationToken cancellationToken) { object result = null; if (Generics.StringExtensions.IsEmpty(query) || Generics.StringExtensions.IsEmpty(connectionString) || cancellationToken.IsCancellationRequested) { return(result); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection)) { command.CommandType = System.Data.CommandType.Text; try { if (cancellationToken.IsCancellationRequested) { throw new OperationCanceledException(cancellationToken); } await connection.OpenAsync(cancellationToken); var transaction = connection.BeginTransaction(); command.Transaction = transaction; result = await transformer(command, cancellationToken); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(result); }
} // End Function GetLargeDrawing // http://stackoverflow.com/questions/14509747/inserting-large-object-into-postgresql-returns-53200-out-of-memory-error // https://github.com/npgsql/Npgsql/wiki/User-Manual public int InsertLargeObject() { int noid; byte[] BinaryData = new byte[123]; // Npgsql.NpgsqlCommand cmd ; // long lng = cmd.LastInsertedOID; using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString())) { using (Npgsql.NpgsqlTransaction transaction = connection.BeginTransaction()) { try { NpgsqlTypes.LargeObjectManager manager = new NpgsqlTypes.LargeObjectManager(connection); noid = manager.Create(NpgsqlTypes.LargeObjectManager.READWRITE); NpgsqlTypes.LargeObject lo = manager.Open(noid, NpgsqlTypes.LargeObjectManager.READWRITE); // lo.Write(BinaryData); int i = 0; do { int length = 1000; if (i + length > BinaryData.Length) { length = BinaryData.Length - i; } byte[] chunk = new byte[length]; System.Array.Copy(BinaryData, i, chunk, 0, length); lo.Write(chunk, 0, length); i += length; } while (i < BinaryData.Length); lo.Close(); transaction.Commit(); } // End Try catch { transaction.Rollback(); throw; } // End Catch return(noid); } // End Using transaction } // End using connection } // End Function InsertLargeObject
private void button3_Click(object sender, EventArgs e) { string _conStr = ConfigurationManager.AppSettings["DB_SK"]; Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(_conStr); connection.Open(); Npgsql.NpgsqlTransaction et = connection.BeginTransaction(); Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand("nolstedt01(:p_personnr,:p_contactchannel_id)", connection, et); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_personnr", "196402230319"); cmd.Parameters.AddWithValue("p_contactchannel_id", "48AF6816C15B49A9956079BD6D8DE561"); cmd.Prepare(); cmd.ExecuteNonQuery(); et.Save("apa"); et.Commit(); connection.Close(); }
public override void OnExecute(EtlPipelineContext context) { using (var con = new Npgsql.NpgsqlConnection(_connectionString)) { con.Open(); using (var trx = con.BeginTransaction(_isolationLevel)) using (var cmd = con.CreateCommand()) { cmd.CommandText = _commandText; cmd.CommandType = CommandType.Text; cmd.Transaction = trx; foreach (var param in _parameters) { var p = cmd.CreateParameter(); p.ParameterName = param.Key; p.Value = param.Value; cmd.Parameters.Add(p); } using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (!reader.HasRows) { return; } while (reader.Read()) { var row = new Row(); for (var i = 0; i < reader.FieldCount; i++) { row[reader.GetName(i)] = reader[i]; } Emit(row); } } } } TypedEmitter.SignalEnd(); }
/// <summary> /// Runs a query and returns void /// </summary> /// <param name="name">Stored function name</param> /// <param name="param">List of parameter to use. Set null for no parameters.</param> /// <param name="connectionString">Connection sting to use</param> /// <returns>boolean</returns> public static bool Void(string name, IEnumerable <Npgsql.NpgsqlParameter> param, string connectionString) { if (Generics.StringExtensions.IsEmpty(name) || Generics.StringExtensions.IsEmpty(connectionString)) { return(false); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(name, connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; if (param != null) { foreach (var item in param) { command.Parameters.Add(item); } } else { command.Parameters.Clear(); } try { connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; command.ExecuteNonQuery(); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(true); }
public bool UpdateObject(IDBObject dbObject) { Npgsql.NpgsqlConnection conn = null; Npgsql.NpgsqlTransaction trans = null; Npgsql.NpgsqlConnection readerConn = null; try { if (dbObject != null) { conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); object result = this.GetDataReader(dbObject.GetObjectByIdQuery(), readerConn); if (result != null) { string strSql = dbObject.GetUpdateStatement(); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(strSql); command.Connection = conn; trans = conn.BeginTransaction(); command.ExecuteNonQuery(); trans.Commit(); return(true); } } } catch (Exception ex) { if (trans != null) { trans.Rollback(); } throw ex; } finally { if (conn != null) { conn.Close(); } if (readerConn != null) { readerConn.Close(); } } return(false); }
} // End Function GetDrawing public System.Drawing.Image GetLargeDrawing(int idOfOID) { System.Drawing.Image img; using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString())) { lock (connection) { if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction()) { NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection); NpgsqlTypes.LargeObject lo = lbm.Open(takeOID(idOfOID), NpgsqlTypes.LargeObjectManager.READWRITE); //take picture oid from metod takeOID byte[] buffer = new byte[32768]; using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { int read; while ((read = lo.Read(buffer, 0, buffer.Length)) > 0) { ms.Write(buffer, 0, read); } // Whend img = System.Drawing.Image.FromStream(ms); } // End Using ms lo.Close(); trans.Commit(); if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } // End Using trans } // End lock connection } // End Using connection return(img); } // End Function GetLargeDrawing
/// <summary> /// Runs a query and returns value by using a converter /// </summary> /// <param name="query">Query to run</param> /// <param name="connectionString">Connection to use</param> /// <param name="transformer">Transformer function</param> /// <returns>Object</returns> public static object Helper( string query, string connectionString, Func <Npgsql.NpgsqlCommand, object> transformer) { object result = null; if (Generics.StringExtensions.IsEmpty(query) || Generics.StringExtensions.IsEmpty(connectionString)) { return(result); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection)) { command.CommandType = System.Data.CommandType.Text; try { connection.Open(); var transaction = connection.BeginTransaction(); command.Transaction = transaction; result = transformer(command); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(result); }
public void DeleteLargeObject(int noid) { using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(GetConnectionString())) { if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } using (Npgsql.NpgsqlTransaction trans = connection.BeginTransaction()) { NpgsqlTypes.LargeObjectManager lbm = new NpgsqlTypes.LargeObjectManager(connection); lbm.Delete(noid); trans.Commit(); if (connection.State != System.Data.ConnectionState.Closed) { connection.Close(); } } // End Using trans } // End Using connection } // End Sub DeleteLargeObject
public bool DeleteObject(IDBObject dbObject) { Npgsql.NpgsqlTransaction trans = null; Npgsql.NpgsqlConnection conn = null; try { if (dbObject != null) { conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(dbObject.GetDeleteStatement()); command.Connection = conn; trans = conn.BeginTransaction(); int count = command.ExecuteNonQuery(); trans.Commit(); if (count > 0) { return(true); } } } catch (Exception ex) { if (trans != null) { trans.Rollback(); } throw ex; } finally { if (conn != null) { conn.Close(); } } return(false); }
/// <summary> /// Query with no return /// </summary> /// <param name="query">Query string</param> /// <param name="connectionString">Database connection string</param> /// <param name="cancellationToken">Cancellation Token</param> /// <returns>Boolean</returns> public static async Task <bool> VoidAsync(string query, string connectionString, CancellationToken cancellationToken = default(CancellationToken)) { if (Generics.StringExtensions.IsEmpty(query) || Generics.StringExtensions.IsEmpty(connectionString)) { return(false); } using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString)) { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(query, connection)) { command.CommandType = System.Data.CommandType.Text; try { await connection.OpenAsync(cancellationToken); var transaction = connection.BeginTransaction(); command.Transaction = transaction; var t = await command.ExecuteNonQueryAsync(cancellationToken); transaction.Commit(); } catch (AggregateException e) { ExceptionDispatchInfo.Capture(e.InnerException).Throw(); } finally { connection.Close(); } } } return(true); }
internal static void ValidateVersion(string connectionStringName, string owner) { _ConnectionStringName = connectionStringName; //open the connection, start the transaction using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { #region v1.1 //test up to v1.1. This is basicially the entire original schema. Log.Debug(d => d("Checking for v1.1 schema...")); if (!TableExists("users", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.1. Updating...")); RunScript(GetDDLResource("v1._1.InitializeSettings.sql"), null, conn, trans); RunScript(GetDDLResource("v1._1.Tables.sql"), null, conn, trans); RunScript(GetDDLResource("v1._1.Types.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_all_users.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_online_count.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_by_id.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.create_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.create_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.delete_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.delete_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_credentials.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_name_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.record_login_event.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.unlock_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user_password.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user_q_and_a.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_roles_for_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_all_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.user_is_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.remove_users_from_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.role_exists.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.assign_users_to_roles.sql"), null, conn, trans); } #endregion #region v1.2 //test up to v1.2. This adds the purge activity feature, for cleaning up old data. Log.Debug(d => d("Checking for v1.2 schema...")); if (!FunctionExists("purge_activity", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.2. Updating...")); RunStatement(GetDDLResource("v1._2.purge_activity.sql"), null, conn, trans); } #endregion #region v1.3 //test up to v1.3. This makes users, roles, and applications case-insensitive. //also adds the version table to check against future versions. Log.Debug(d => d("Checking for v1.3 schema...")); if (!TableExists("versions", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.3. Updating...")); RunScript(GetDDLResource("v1._3.TableChanges.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.assign_users_to_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.create_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.create_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.delete_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.delete_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_all_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_all_users.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_online_count.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_roles_for_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_credentials.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_name_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.record_login_event.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.role_exists.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.remove_users_from_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.unlock_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user_password.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user_q_and_a.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.user_is_in_role.sql"), null, conn, trans); } #endregion //v1.3 is the first version that includes version tracking in the database. var currentVersion = GetDBVersion(conn, trans); #region v1.4 Log.Debug(d => d("Checking for v1.4 schema (no DDL action required)...")); if (currentVersion == "1.3") { currentVersion = "1.4"; } #endregion #region v1.5 Log.Debug(d => d("Checking for v1.5 schema (no DDL action required)...")); if (currentVersion == "1.4") { currentVersion = "1.5"; } #endregion #region v1.6 if (currentVersion == "1.5") { //v1.6 fixes an update problem where the last login time was not being persisted. RunStatement(GetDDLResource("v1._6.record_login_event.sql"), null, conn, trans); currentVersion = "1.6"; } #endregion #region v1.7 if (currentVersion == "1.6") { RunStatement(GetDDLResource("v1._7.get_number_of_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._7.get_users_online.sql"), null, conn, trans); currentVersion = "1.7"; } #endregion /* * Other checks and updates will go here. * * */ SetDBVersion(conn, trans, currentVersion); trans.Commit(); } } }
public void Open(bool transaction) { connection = new Npgsql.NpgsqlConnection(connstring); connection.Open(); comm = new Npgsql.NpgsqlCommand("", connection); if (transaction) { comm.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); } }
protected static bool IsSuperUser() { using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { var value = IsSuperUser(conn, trans); trans.Commit(); return value; } } }
public bool AddObject(IDBObject dbObject) { Npgsql.NpgsqlConnection conn = null; Npgsql.NpgsqlConnection readerConn = null; Npgsql.NpgsqlTransaction trans = null; try { if (dbObject != null) { bool result = this.UpdateObject(dbObject); if (!result) { //Add Object conn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); readerConn = (Npgsql.NpgsqlConnection)NpgsqlConnectionImpl.GetInstance().GetNewConnection(); string strSql = dbObject.GetAddStatement(); Npgsql.NpgsqlCommand insertCommand = new Npgsql.NpgsqlCommand(strSql); trans = conn.BeginTransaction(); insertCommand.Connection = conn; insertCommand.ExecuteNonQuery(); trans.Commit(); object savedObject = this.GetDataReader(dbObject.GetUniqueObjectQuery(), readerConn); if (savedObject != null) { Npgsql.NpgsqlDataReader reader = (Npgsql.NpgsqlDataReader)savedObject; if (reader.HasRows) { while (reader.Read()) { dbObject.SetId(reader.GetInt32(reader.GetOrdinal("id"))); } return(true); } } return(false); } return(result); } } catch (Exception ex) { if (trans != null) { trans.Rollback(); } throw ex; } finally { if (conn != null) { conn.Close(); } if (readerConn != null) { readerConn.Close(); } } return(false); }
public void PgSql() { PgSqlCommandExecutor.Configure(); using (var connection = new Npgsql.NpgsqlConnection("host=localhost;username=postgres;password=Norther;database=Test")) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var testData = new TestTable() { Foo = "Foo", Name = "Hello", TheDate = DateTime.Now }; TestTable result; using (Time(elapsed => Console.WriteLine("Insert : " + elapsed))) { result = connection.Insert(testData, transaction); } Console.WriteLine("Database generated UID : " + result.TheSecondId); List<TestTable> bulkResult; using (Time(elapsed => Console.WriteLine("Bulk insert : " + elapsed))) { bulkResult = connection.Insert(Enumerable.Range(0, 100).Select(i => new TestTable() { Name = i.ToString() }), transaction).ToList(); Assert.AreEqual(100, bulkResult.Count()); } using (Time(elapsed => Console.WriteLine("Bulk update : " + elapsed))) { bulkResult = connection.Update( bulkResult.Select(item => item.SetProperties(new TestTable() { Name = item.Name + " strongly typed mutation" })), transaction).ToList(); } using (Time(elapsed => Console.WriteLine("Update : " + elapsed))) { var newDate = DateTime.Now.AddYears(-50); var update = connection.Update( result.SetProperties( new { TheDate = newDate } ), transaction ); Assert.AreEqual(newDate.Year, update.TheDate.Year); } using (Time(elapsed => Console.WriteLine("FindById : " + elapsed))) { Assert.IsNotNull(connection.FindById<TestTable>(new { result.Id, result.TheSecondId }, transaction)); } transaction.Commit(); } } }
/// <summary> /// Upserts venue opening times /// </summary> public async Task <Result> UpsertVenueOpeningTimes ( int venueId, TimeSpan?SundayOpeningTimeStart, TimeSpan?SundayOpeningTimeEnd, TimeSpan?MondayOpeningTimeStart, TimeSpan?MondayOpeningTimeEnd, TimeSpan?TuesdayOpeningTimeStart, TimeSpan?TuesdayOpeningTimeEnd, TimeSpan?WednesdayOpeningTimeStart, TimeSpan?WednesdayOpeningTimeEnd, TimeSpan?ThursdayOpeningTimeStart, TimeSpan?ThursdayOpeningTimeEnd, TimeSpan?FridayOpeningTimeStart, TimeSpan?FridayOpeningTimeEnd, TimeSpan?SaturdayOpeningTimeStart, TimeSpan?SaturdayOpeningTimeEnd ) { try { using var con = new Npgsql.NpgsqlConnection(settings.Connection.DatabaseConnectionString); await con.OpenAsync().ConfigureAwait(false); var transaction = con.BeginTransaction(); const string query = @"INSERT INTO ""VenueOpeningTime"" (venueId, startTime, endTime, dayOfWeek) VALUES (@VenueId, @StartTime, @EndTime, @DayOfWeek) ON CONFLICT (venueId, dayOfWeek) DO UPDATE SET startTime = @StartTime, endTime = @EndTime"; if (SundayOpeningTimeStart != null && SundayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 0, @StartTime = SundayOpeningTimeStart.Value, @EndTime = SundayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 0", new { VenueId = venueId }).ConfigureAwait(false); } if (MondayOpeningTimeStart != null && MondayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 1, @StartTime = MondayOpeningTimeStart.Value, @EndTime = MondayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 1", new { VenueId = venueId }).ConfigureAwait(false); } if (TuesdayOpeningTimeStart != null && TuesdayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 2, @StartTime = TuesdayOpeningTimeStart.Value, @EndTime = TuesdayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 2", new { VenueId = venueId }).ConfigureAwait(false); } if (WednesdayOpeningTimeStart != null && WednesdayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 3, @StartTime = WednesdayOpeningTimeStart.Value, @EndTime = WednesdayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 3", new { VenueId = venueId }).ConfigureAwait(false); } if (ThursdayOpeningTimeStart != null && ThursdayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 4, @StartTime = ThursdayOpeningTimeStart.Value, @EndTime = ThursdayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 4", new { VenueId = venueId }).ConfigureAwait(false); } if (FridayOpeningTimeStart != null && FridayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 5, @StartTime = FridayOpeningTimeStart.Value, @EndTime = FridayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 5", new { VenueId = venueId }).ConfigureAwait(false); } if (SaturdayOpeningTimeStart != null && SaturdayOpeningTimeEnd != null) { await con.ExecuteAsync(query, new { @DayOfWeek = 6, @StartTime = SaturdayOpeningTimeStart.Value, @EndTime = SaturdayOpeningTimeEnd.Value, @VenueId = venueId }).ConfigureAwait(false); } else { await con.ExecuteAsync(@"DELETE FROM ""VenueOpeningTime"" WHERE venueId = @VenueId AND dayOfWeek = 6", new { VenueId = venueId }).ConfigureAwait(false); } await transaction.CommitAsync().ConfigureAwait(false); return(Result.Ok()); } catch (Exception ex) { return(Result.Fail(ex.ToString())); } }
protected static bool TableExists(string tableName) { using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { var value = TableExists(tableName, conn, trans); trans.Commit(); return value; } } }
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) { return(new PostgresTransaction(con.BeginTransaction(), this)); }
internal static void ValidateVersion(string connectionStringName, string owner) { _ConnectionStringName = connectionStringName; //open the connection, start the transaction using (var conn = new Npgsql.NpgsqlConnection(ConfigurationManager.ConnectionStrings[_ConnectionStringName].ConnectionString)) { conn.Open(); using (var trans = conn.BeginTransaction()) { #region v1.1 //test up to v1.1. This is basicially the entire original schema. Log.Debug(d => d("Checking for v1.1 schema...")); if (!TableExists("users", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.1. Updating...")); RunScript(GetDDLResource("v1._1.InitializeSettings.sql"), null, conn, trans); RunScript(GetDDLResource("v1._1.Tables.sql"), null, conn, trans); RunScript(GetDDLResource("v1._1.Types.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_all_users.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_online_count.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_by_id.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.create_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.create_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.delete_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.delete_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_credentials.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_user_name_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.record_login_event.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.unlock_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user_password.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.update_user_q_and_a.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_users_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_roles_for_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.get_all_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.user_is_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.remove_users_from_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.role_exists.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._1.assign_users_to_roles.sql"), null, conn, trans); } #endregion #region v1.2 //test up to v1.2. This adds the purge activity feature, for cleaning up old data. Log.Debug(d => d("Checking for v1.2 schema...")); if (!FunctionExists("purge_activity", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.2. Updating...")); RunStatement(GetDDLResource("v1._2.purge_activity.sql"), null, conn, trans); } #endregion #region v1.3 //test up to v1.3. This makes users, roles, and applications case-insensitive. //also adds the version table to check against future versions. Log.Debug(d => d("Checking for v1.3 schema...")); if (!TableExists("versions", conn, trans)) { Log.Info(i => i("The database does not seem to be compatible with v1.3. Updating...")); RunScript(GetDDLResource("v1._3.TableChanges.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.assign_users_to_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.create_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.create_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.delete_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.delete_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_all_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_all_users.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_online_count.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_roles_for_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_credentials.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_user_name_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_by_email.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_by_username.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_in_role.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.get_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.record_login_event.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.role_exists.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.remove_users_from_roles.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.unlock_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user_password.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.update_user_q_and_a.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._3.user_is_in_role.sql"), null, conn, trans); } #endregion //v1.3 is the first version that includes version tracking in the database. var currentVersion = GetDBVersion(conn, trans); #region v1.4 Log.Debug(d => d("Checking for v1.4 schema (no DDL action required)...")); if (currentVersion == "1.3") currentVersion = "1.4"; #endregion #region v1.5 Log.Debug(d => d("Checking for v1.5 schema (no DDL action required)...")); if (currentVersion == "1.4") currentVersion = "1.5"; #endregion #region v1.6 if (currentVersion == "1.5") { //v1.6 fixes an update problem where the last login time was not being persisted. RunStatement(GetDDLResource("v1._6.record_login_event.sql"), null, conn, trans); currentVersion = "1.6"; } #endregion #region v1.7 if (currentVersion == "1.6") { RunStatement(GetDDLResource("v1._7.get_number_of_users_online.sql"), null, conn, trans); RunStatement(GetDDLResource("v1._7.get_users_online.sql"), null, conn, trans); currentVersion = "1.7"; } #endregion /* * Other checks and updates will go here. * * */ SetDBVersion(conn, trans, currentVersion); trans.Commit(); } } }