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