public void ResetOnClose()
{
var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
conn.Open();
ExecuteNonQuery("SET search_path=foo", conn);
conn.Close();
conn.Open();
Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public"));
conn.Close();
}
/// <summary>
/// Tests a database connection.
/// </summary>
public override bool TestDbConnection(DbConnectionOptions connectionOptions, out string errMsg)
{
if (connectionOptions.KnownDBMS == KnownDBMS.PostgreSQL)
{
NpgsqlConnection conn = null;
try
{
conn = CreateDbConnection(connectionOptions);
conn.Open();
errMsg = "";
return(true);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(false);
}
finally
{
conn?.Close();
}
}
else
{
errMsg = CommonPhrases.DatabaseNotSupported;
return(false);
}
}
private void OnDisconnectItemClick(object sender, EventArgs e)
{
Connection?.Close();
Connection = null;
_updateTimer?.Change(0, 0);
_updateTimer = null;
}
// <summary>
// Ejecuta una funcion almacenada, pasando los argumentos recibidos
// como parametros a la base de datos.
// Devuelve:
// DataTable del resultado de la ejecucion de la funcion
// Excepciones:
// - DatabaseException: Error conectandose a la base de datos, o ejecutando la funcion
// - InvalidStoredProcedureSignatureException: Cuando la firma de la funcion no puede ser
// procesada correctamente, esta excepcion no se deberia atrapar pues es generada por errores
// del programador.
//
// Ejemplos de uso correcto:
// ExecuteFunction("algunaFuncion(@param1, @param2, @param3)");
// ExecuteFunction("funcionSinParams()");
//
// Ejemplos de uso incorrecto:
// ExecuteFunction("algunaFuncion(param1, param2, param3)"); [No tiene los '@']
// ExecuteFunction("funcion mal escrita(();") [Espacios en el nombre de la funcion, parentesis de más]
// </summary>
public DataTable ExecuteFunction(string functionSignature, params object[] arguments)
{
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(_connectionParameters);
connection.Open();
var command = new NpgsqlCommand("select * from " + functionSignature, connection);
if (arguments.Length > 0)
{
var keys = ExtractParameters(functionSignature);
for (var i = 0; i < keys.Length; i++)
{
command.Parameters.AddWithValue(keys[i].Trim(), arguments[i]);
}
}
var dataTable = new DataTable();
dataTable.Load(command.ExecuteReader());
return(dataTable);
}
catch (Exception e)
{
Console.WriteLine(e);
throw new DatabaseException(
$"Error ejecutando funcion: {functionSignature}.{Environment.NewLine}{e.Message}");
}
finally
{
connection?.Close();
}
}
public CallResult ExecuteSqlFromString(
string s,
NpgsqlConnection connection = null)
{
try
{
//Debug.Log(" executing ~~> " + s);
connection = connection ?? ProvideConnection();
connection.TryOpen();
using (var cmd = new NpgsqlCommand(s, connection))
{
var reader = cmd.ExecuteReader();
JArray data = new JArray();
while (reader.Read())
{
var x = reader.GetString(0);
CallResult?breakingResult = null;
if (reader.FieldCount == 1)
{
breakingResult = x == CallResult.DbError
? CallResult.Error("Conference API authorization failed.")
: CallResult.Ok;
}
if (breakingResult != null)
{
reader.Dispose();
return(breakingResult.Value);
}
// db returned data:
data.Add(
new JObject(
Enumerable.Range(0, reader.FieldCount).Select(
index =>
new JProperty(reader.GetName(index),
typeof(DBNull) == reader.GetValue(index).GetType()
? "null" : reader.GetValue(index)
)
)
)
);
}
reader.Dispose();
connection.Close();
//connection.Dispose();
//Debug.Log("Output from db: " + data);
return(new CallResult(CallResult.Status.Ok, data));
}
}
catch (PostgresException e)
{
connection?.Close();
//connection?.Dispose();
return(CallResult.Error("Postgres exception catched in ExecuteSqlFromString. " + e.Message));
}
}
protected virtual void Dispose(bool disposing)
{
if (!disposing)
{
return;
}
Transaction?.Dispose();
DbConnection?.Close();
DbConnection?.Dispose();
}
public Task CloseConnectionAsync()
{
if (_npgSqlConnection != null && _npgSqlConnection.State != ConnectionState.Closed)
{
_npgSqlConnection?.Close();
Log("Connection closed");
}
return(Task.CompletedTask);
}
public void MinPoolSize()
{
var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=30;MaxPoolSize=30");
conn.Open();
conn.Close();
conn = new NpgsqlConnection(ConnectionString + ";MaxPoolSize=30;MinPoolSize=30");
conn.Open();
conn.Close();
}
public void ResetOnClose()
{
var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
conn.Open();
ExecuteNonQuery("DROP SCHEMA IF EXISTS foo");
ExecuteNonQuery("CREATE SCHEMA foo");
try
{
ExecuteNonQuery("SET search_path=foo", conn);
conn.Close();
conn.Open();
Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public"));
conn.Close();
}
finally
{
ExecuteNonQuery("DROP SCHEMA foo");
}
}
public static void CloseConnection(NpgsqlConnection connection)
{
try
{
connection?.Close();
} catch (NpgsqlException e)
{
Console.WriteLine(e.Message);
}
}
public void CreateTestEvenementTable()
{
string connStr = "Host = LocalHost; Port = 5432; Database = ConformitDbTest; Username = postgres; Password = admin";
var m_conn = new NpgsqlConnection(connStr);
var m_Createtbl_cmd = new NpgsqlCommand(
"CREATE TABLE evenementTest (id SERIAL PRIMARY KEY, titre VARCHAR(100), description VARCHAR(255), personneresponsable VARCHAR(50), listecommentaire test[][]);");
m_conn.Open();
m_Createtbl_cmd.ExecuteNonQuery();
m_conn?.Close();
}
public void CreateTestCommentaireTable()
{
string connStr = "Host = LocalHost; Port = 5432; Database = ConformitDbTest; Username = postgres; Password = admin";
var m_conn = new NpgsqlConnection(connStr);
var m_Createtbl_cmd = new NpgsqlCommand(
"CREATE TABLE commentaireTest (id SERIAL PRIMARY KEY, evenementid INT, description VARCHAR(255), date DATE, FOREIGN KEY (evenementid) REFERENCES evenement(id));");
m_conn.Open();
m_Createtbl_cmd.ExecuteNonQuery();
m_conn?.Close();
}
private void noQuery()
{
try
{
string sql = txtQuery.Text;
conn.Open();
NpgsqlCommand comando = new NpgsqlCommand(sql, conn);
comando.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Erro de ligação");
}
finally
{
if (conn?.FullState == ConnectionState.Open)
{
conn?.Close();
}
}
}
public void FillTestEvenementTable()
{
string connStr = "Host = LocalHost; Port = 5432; Database = ConformitDbTest; Username = postgres; Password = admin";
var m_conn = new NpgsqlConnection(connStr);
var m_addtbl_cmd = new NpgsqlCommand(
"INSERT INTO `evenementTest` (`id`,`titre`,`description`,`personneresponsable`,`listecommentaire`) VALUES" +
"(1,'test#1','description test #1','jordan','vide')," +
"(2,'test#2','description test #2','jordan2','vide');");
m_conn.Open();
m_addtbl_cmd.ExecuteNonQuery();
m_conn?.Close();
}
public void FillTestCommentaireTable()
{
string connStr = "Host = LocalHost; Port = 5432; Database = ConformitDbTest; Username = postgres; Password = admin";
var m_conn = new NpgsqlConnection(connStr);
var m_addtbl_cmd = new NpgsqlCommand(
"INSERT INTO `commentaireTest` (`id`,`evenementid`,`description`,`date`) VALUES" +
"(1,1,'description test #1','2021-05-21')," +
"((2,1,'description test #2','2021-05-21');");
m_conn.Open();
m_addtbl_cmd.ExecuteNonQuery();
m_conn?.Close();
}
public void FillContratos(Contrato contrato)
{
try
{
PropertyInfo[] properties = contrato.GetType().GetProperties(
//BindingFlags.NonPublic | // Include protected and private properties
BindingFlags.Public | // Also include public properties
BindingFlags.Instance // Specify to retrieve non static properties
);
string sql = "Insert Into \"Contratos\" " +
"(" + String.Join(",", properties.Select(x => "\"" + x.Name + "\"").ToList()) + ") " +
"values(" + String.Join(",", properties.Select((x) => "'" + x.GetValue(contrato).ToString() + "'").ToList()) + ")";
/*var list = properties.Select((x) => x.Name).ToList();
* System.Diagnostics.Debug.WriteLine(properties.Length.ToString());
* foreach (var z in list) {
* System.Diagnostics.Debug.WriteLine(z);
* }*/
System.Diagnostics.Debug.WriteLine(sql);
using (NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString))
{
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
//String.Format(
//string cmdInserir = "Insert Into \"Contratos\" " +
//"(" + String.Join(",", contrato.fields.Select(x => x = "\""+x+"\"")) + ") " +
//"values(" + String.Join(",", contrato.fields.Select((x,index) => x = "'{"+index.ToString()+"}'")) + ")"
using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(sql, pgsqlConnection))
{
pgsqlcommand.ExecuteNonQuery();
}
}
}
catch (NpgsqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
pgsqlConnection?.Close();
}
}
private void Dispose(bool disposing)
{
if (_disposed)
{
return;
}
if (disposing)
{
_logger.LogInformation("Closing database connection");
_connection?.Close();
_connection?.Dispose();
_connection = null;
}
_disposed = true;
}
public void MinPoolSize()
{
var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 2 };
using (var conn = new NpgsqlConnection(connString))
{
connString = conn.Settings; // Shouldn't be necessary
conn.Open();
conn.Close();
}
var pool = PoolManager.Pools[connString];
Assert.That(pool.Idle, Has.Count.EqualTo(2));
// Now open 2 connections and make sure they're good
using (var conn1 = OpenConnection(connString))
using (var conn2 = OpenConnection(connString))
{
Assert.That(pool.Idle, Has.Count.Zero);
Assert.That(conn1.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
Assert.That(conn2.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public static DataTable DtByProcedure(string procedureName, string conString)
{
procedureName = $"\"{procedureName}\"";
var dt = new DataTable();
NpgsqlConnection conn = null;
try
{
conn = new NpgsqlConnection(conString);
conn.Open();
using (var cmd = new NpgsqlCommand(procedureName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(dr);
return(dt);
};
}
catch (NpgsqlException)
{
// handle error
return(dt);
}
catch (Exception)
{
// handle error
return(dt);
}
finally
{
conn?.Close();
}
}
public void Desconect()
{
con.Close();
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
tainted_2 = Console.ReadLine();
tainted_3 = tainted_2;
if ((4 + 2 <= 42))
{
{}
}
else
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Server=localhost;port=1337;User Id=postgre_user;Password=postgre_password;Database=dbname";
NpgsqlConnection dbConnection = null;
try{
dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, dbConnection);
NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.Write("{0}\n", dr[0]);
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
public int CreateSection(Section section)
{
conn = DAO.getConnection();
NpgsqlCommand command = new NpgsqlCommand(DAOSectionResource.CreateSectionSP, conn);
NpgsqlTransaction transaction = conn.BeginTransaction();
NpgsqlParameter name = new NpgsqlParameter();
NpgsqlParameter amount = new NpgsqlParameter();
NpgsqlParameter description = new NpgsqlParameter();
NpgsqlParameter fkcourse = new NpgsqlParameter();
name.ParameterName = DAOSectionResource.Name;
amount.ParameterName = DAOSectionResource.Amount;
description.ParameterName = DAOSectionResource.Description;
fkcourse.ParameterName = DAOSectionResource.CourseID;
name.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
amount.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Double;
description.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
fkcourse.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar;
name.Direction = ParameterDirection.Input;
amount.Direction = ParameterDirection.Input;
description.Direction = ParameterDirection.Input;
fkcourse.Direction = ParameterDirection.Input;
name.Value = section.Name;
amount.Value = section.Amount;
description.Value = section.Description;
fkcourse.Value = section.Course.Id;
command.Parameters.Add(name);
command.Parameters.Add(amount);
command.Parameters.Add(description);
command.Parameters.Add(fkcourse);
command.CommandType = CommandType.StoredProcedure;
int response = 500;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
response = dr.GetInt32(0);
}
dr.Close();
transaction.Commit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(response);
}
/// <summary>
/// MembershipProvider.GetUser(string, bool)
/// </summary>
/// <param name="username"></param>
/// <param name="userIsOnline"></param>
/// <returns></returns>
public override MembershipUser GetUser(string username, bool userIsOnline)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("SELECT UserId, user_name, Email, password_question, Comment, is_approved, is_locked_out, creation_date, last_login_date, last_activity_date, last_password_changed_date, last_locked_out_date FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
MembershipUser u = null;
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
u = GetUserFromReader(reader);
reader.Close();
if (userIsOnline)
{
NpgsqlCommand updateCmd =
new NpgsqlCommand(
string.Format("UPDATE {0} SET last_activity_date = @last_activity_date WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
updateCmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
// fixed by Alex .ToString("yyyy/MM/dd HH:mm:ss");
updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
updateCmd.ExecuteBlind();
}
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUser(String, Boolean)");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return u;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public string GetUserId()
{
NpgsqlMembershipProvider _provider = null;
ProviderCollection _providers = null;
// Get a reference to the <imageService> section
MembershipSection section = (MembershipSection) WebConfigurationManager.GetSection("system.web/membership");
// Load registered providers and point _provider
// to the default provider
_providers = new ProviderCollection();
ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (NpgsqlMembershipProvider));
_provider = (NpgsqlMembershipProvider) _providers[section.DefaultProvider];
HttpContext currentContext = HttpContext.Current;
NpgsqlConnection conn = new NpgsqlConnection(_provider.connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT UserId FROM " + tableName + " WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = currentContext.User.Identity.Name;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = _provider.ApplicationName;
string UserId = "";
try
{
conn.Open();
UserId = cmd.ExecuteScalar().ToString();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUserId()");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return UserId;
}
//
// RoleProvider.RoleExists
//
public override bool RoleExists(string rolename)
{
bool exists = false;
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT COUNT(*) FROM " + rolesTable + "" +
" WHERE role_name = @role_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
try
{
conn.Open();
long numRecs = Convert.ToInt64(cmd.ExecuteScalar());
if (numRecs > 0)
{
exists = true;
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RoleExists");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return exists;
}
/// <summary>
/// Gets number of inactive Profiles
/// </summary>
/// <param name="authenticationOption"></param>
/// <param name="userInactiveSinceDate"></param>
/// <returns></returns>
public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
NpgsqlConnection conn = null;
NpgsqlCommand cmd = null;
try
{
conn = new NpgsqlConnection(_NpgsqlConnectionString);
conn.Open();
cmd = new NpgsqlCommand(GenerateQuery(false, authenticationOption), conn);
cmd.CommandTimeout = CommandTimeout;
cmd.Parameters.Add("@InactiveSinceDate", NpgsqlDbType.Timestamp).Value = userInactiveSinceDate.ToUniversalTime();
object o = cmd.ExecuteScalar();
if (o == null || !(o is int))
{
return 0;
}
return (int) o;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
conn = null;
}
}
}
public bool AddSale(string from_station, string to_station, int route_id, int traveller_id, short amount, string ticket_name)
{
conn.Open();
NpgsqlTransaction transaction = conn.BeginTransaction();
try
{
RouteSubrouteRepository routeSubrouteRepository = new RouteSubrouteRepository();
List <RouteSubroute> routeParts = routeSubrouteRepository.GetRoutePart(route_id, from_station, to_station);
foreach (var routePart in routeParts)
{
if (routePart.seats_amount <= 0)
{
throw new Exception();
}
}
int saleId = NextId();
Sale sale = new Sale();
sale.id = saleId;
sale.payment_status = true;
sale.sale_date = DateTime.Now;
sale.to_station = to_station;
sale.from_station = from_station;
sale.route_id = route_id;
sale.traveller_id = traveller_id;
NpgsqlCommand addSale = new NpgsqlCommand(" insert into sale " +
"(id, payment_status, from_station, to_station, route_id, traveller_id) " +
"values(:id,:payment_status, :from_station, :to_station, :route_id, :traveller_id); ", conn);
var id_db_sale = new NpgsqlParameter(":id", DbType.Int32);
id_db_sale.Value = sale.id;
addSale.Parameters.Add(id_db_sale);
var payment_status_db = new NpgsqlParameter(":payment_status", DbType.Boolean);
payment_status_db.Value = sale.payment_status;
addSale.Parameters.Add(payment_status_db);
var to_station_db = new NpgsqlParameter(":to_station", DbType.String);
to_station_db.Value = sale.to_station;
addSale.Parameters.Add(to_station_db);
var from_station_db = new NpgsqlParameter(":from_station", DbType.String);
from_station_db.Value = sale.from_station;
addSale.Parameters.Add(from_station_db);
var route_id_db = new NpgsqlParameter(":route_id", DbType.Int32);
route_id_db.Value = sale.route_id;
addSale.Parameters.Add(route_id_db);
var traveller_id_db = new NpgsqlParameter(":traveller_id", DbType.Int32);
traveller_id_db.Value = sale.traveller_id;
addSale.Parameters.Add(traveller_id_db);
addSale.Prepare();
//throw new Exception(); <-- TEST
NpgsqlCommand selectTicket = new NpgsqlCommand("SELECT id FROM ticket" +
" WHERE name=:ticket_name", conn);
var ticket_name_db = new NpgsqlParameter(":ticket_name", DbType.String);
ticket_name_db.Value = ticket_name;
selectTicket.Parameters.Add(ticket_name_db);
selectTicket.Prepare();
int ticketId = (int)selectTicket.ExecuteScalar();
SaleTicket saleTicket = new SaleTicket
{
sale_id = saleId,
ticket_id = ticketId,
amount = amount
};
NpgsqlCommand addSaleTicket = new NpgsqlCommand("insert into sale_ticket (amount,sale_id,ticket_id) " +
" values(:amount, :sale_id, :ticket_id); ", conn);
var amount_db = new NpgsqlParameter(":amount", DbType.Int32);
amount_db.Value = saleTicket.amount;
addSaleTicket.Parameters.Add(amount_db);
var sale_id_db = new NpgsqlParameter(":sale_id", DbType.Int32);
sale_id_db.Value = saleTicket.sale_id;
addSaleTicket.Parameters.Add(sale_id_db);
var ticket_id_db = new NpgsqlParameter(":ticket_id", DbType.Int32);
ticket_id_db.Value = saleTicket.ticket_id;
addSaleTicket.Parameters.Add(ticket_id_db);
addSaleTicket.Prepare();
int rowsAddedToSale = addSale.ExecuteNonQuery();
int rowsAddedToTicketSale = addSaleTicket.ExecuteNonQuery();
for (int i = 0; i < routeParts.Count; i++)
{
routeParts[i].seats_amount -= 1;
NpgsqlCommand updateRouteSubroute = new NpgsqlCommand("update route_subroute set seats_amount = :seats_amount " +
"where id = :id;", conn);
var seats_amount_db = new NpgsqlParameter(":seats_amount", DbType.Int32);
seats_amount_db.Value = routeParts[i].seats_amount;
updateRouteSubroute.Parameters.Add(seats_amount_db);
var id_db = new NpgsqlParameter(":id", DbType.Int32);
id_db.Value = routeParts[i].id;
updateRouteSubroute.Parameters.Add(id_db);
updateRouteSubroute.Prepare();
int rowsUpdated = updateRouteSubroute.ExecuteNonQuery();
}
}
catch (Exception ex)
{
transaction.Rollback();
conn.Close();
return(false);
}
transaction.Commit();
conn.Close();
return(true);
}
public void Start(string requestId)
{
try
{
status.Code = RequestStatus.StatusCode.InProgress;
// PostgeSQL-style connection string
string server = (string)Settings["Server"];
string port = (string)Settings["Port"];
string userId = (string)Settings["UserId"];
string password = (string)Settings["Password"];
string database = (string)Settings["Database"];
string connectionTimout = "15";
if (Settings.ContainsKey("ConnectionTimeout"))
{
connectionTimout = (string)Settings["ConnectionTimeout"];
}
string commandTimeout = "120";
if (Settings.ContainsKey("CommandTimeout"))
{
commandTimeout = (string)Settings["CommandTimeout"];
}
string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Timeout={5};CommandTimeout={6}", server, port, userId, password, database, connectionTimout, commandTimeout);
log.Debug("Connection timeout: " + connectionTimout + ", Command timeout: " + commandTimeout);
// Making connection with Npgsql provider
using (NpgsqlConnection conn = new NpgsqlConnection(connstring))
{
try
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(query, conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
resultDataset.Reset();
da.Fill(resultDataset);
}
catch (Npgsql.NpgsqlException ex)
{
status.Code = RequestStatus.StatusCode.Complete;
status.Message = ex.Message;
// This code will catch SQL errors, both conneciton related and syntax which I expect will happen often, so we really don't want to throw an error here.
// A better approach would be to handle this by displaying a message in the the result area. The output of this request is a table, so perhaps we could
// build a table with a single rown whose colums are the information returned by the exception.
DataTable table = resultDataset.Tables.Add("Error");
table.Columns.Add("ErrorMessage");
DataRow row = table.Rows.Add();
row["ErrorMessage"] = ex.Message;
//throw ex;
}
catch (Exception exc)
{
status.Code = RequestStatus.StatusCode.Error;
status.Message = exc.Message;
throw exc;
}
finally
{
conn.Close();
}
}
if (status.Code == RequestStatus.StatusCode.InProgress)
{
status.Code = RequestStatus.StatusCode.Complete;
status.Message = "";
}
}
catch (Exception e)
{
status.Code = RequestStatus.StatusCode.Error;
status.Message = e.Message;
throw e;
}
}
private void Form6_Load(object sender, EventArgs e)
{
//Вот тут необходимо после объединения модулей исправить подключение к БД
String connectionString = "Server=hrd.cx7kyl76gv42.us-east-2.rds.amazonaws.com;User Id=postgres;Password=Ntcnbhjdfybt_01;Database=HRD;";
NpgsqlConnection npgSqlConnection = new NpgsqlConnection(connectionString);
try
{
//Вот тут я запросом считываю из базы тип образования в comboBox
string sqlExpression = "SELECT * FROM public.\"Education\"";
npgSqlConnection.Open();
// MessageBox.Show("Подключение открыто!!");
NpgsqlCommand command = new NpgsqlCommand(sqlExpression, npgSqlConnection);
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
while (reader.Read()) // построчно считываем данные
{
object Name = reader.GetValue(1);
comboBox1.Items.Add(Name);
}
}
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
npgSqlConnection.Close();
// MessageBox.Show("Подключение закрыто!!");
}
try
{
//Вот тут я запросом считываю из базы образовательное учереждение в comboBox
string sqlExpression1 = "SELECT * FROM public.\"Institution\"";
npgSqlConnection.Open();
// MessageBox.Show("Подключение открыто!!");
NpgsqlCommand command1 = new NpgsqlCommand(sqlExpression1, npgSqlConnection);
NpgsqlDataReader reader1 = command1.ExecuteReader();
if (reader1.HasRows) // если есть данные
{
while (reader1.Read()) // построчно считываем данные
{
object Name = reader1.GetValue(1);
comboBox2.Items.Add(Name);
}
}
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
npgSqlConnection.Close();
// MessageBox.Show("Подключение закрыто!!");
}
try
{
//Вот тут я запросом считываю из базы профиль подготовки в comboBox
string sqlExpression2 = "SELECT * FROM public.\"Specialty\"";
npgSqlConnection.Open();
// MessageBox.Show("Подключение открыто!!");
NpgsqlCommand command2 = new NpgsqlCommand(sqlExpression2, npgSqlConnection);
NpgsqlDataReader reader2 = command2.ExecuteReader();
if (reader2.HasRows) // если есть данные
{
while (reader2.Read()) // построчно считываем данные
{
object Name = reader2.GetValue(1);
comboBox3.Items.Add(Name);
}
}
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
npgSqlConnection.Close();
// MessageBox.Show("Подключение закрыто!!");
}
}
private void urunekle_FormClosed(object sender, FormClosedEventArgs e)
{
baglanti.Close();
}
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"DELETE FROM " + rolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlCommand cmd2 =
new NpgsqlCommand(
"DELETE FROM " + usersInRolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name",
conn);
cmd2.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd2.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteBlind();
cmd.ExecuteBlind();
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return false;
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return true;
}
//
// RoleProvider.GetAllRoles
//
public override string[] GetAllRoles()
{
string tmpRoleNames = "";
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand("SELECT role_name FROM " + rolesTable + "" + " WHERE application_name = @application_name", conn);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tmpRoleNames += reader.GetString(0) + ",";
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetAllRoles");
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
if (tmpRoleNames.Length > 0)
{
// Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
return new string[0];
}
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"DELETE FROM " + usersInRolesTable + "" +
" WHERE user_name = @user_name AND role_name = @role_name AND application_name = @application_name", conn);
NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255);
NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteBlind();
}
}
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
public void CloseConnection()
{
_connection?.Close();
}
private void alternative7()
{
string errorMsg = null;
while (true)
{
Console.Clear();
Console.WriteLine("Creation of a new movie in the database.(write go back to go back)\n");
if (errorMsg != null)
{
Console.WriteLine("Error: " + errorMsg);
}
Console.Write("Please write the title:");
String title = Console.ReadLine();
if (title == "go back")
{
return;
}
Console.Write("Please write the year:");
String year = Console.ReadLine();
if (year == "go back")
{
return;
}
Console.Write("Please write the age restriction:");
String ageRestriction = Console.ReadLine();
if (ageRestriction == "go back")
{
return;
}
Console.Write("Please write it's price:");
String price = Console.ReadLine();
if (price == "go back")
{
return;
}
NpgsqlTransaction transaction = null;
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(connection_string);
connection.Open();
transaction = connection.BeginTransaction();
Movie movie = new Movie(title, Int32.Parse(year), Int32.Parse(ageRestriction), Int32.Parse(price));
movie.Save();
Copy copy = new Copy(true, movie.Id);
copy.Save();
transaction.Commit();
}
catch (Exception e)
{
errorMsg = e.Message;
transaction?.Rollback();
continue;
}
finally
{
connection?.Close();
}
Console.WriteLine("Victory! Movie was created!");
break;
}
while (true)
{
Console.Write("\n\nPress ESC to go back...");
if (Console.ReadKey().Key == ConsoleKey.Escape)
{
return;
}
}
}
//
// MembershipProvider.UnlockUser
//
public override bool UnlockUser(string username)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"UPDATE " + tableName + " " + " SET is_locked_out = false, last_locked_out_date = @last_locked_out_date " +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@last_locked_out_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UnlockUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
if (rowsAffected > 0)
{
return true;
}
return false;
}
public void MinPoolSizeLargeThanPoolSizeLimit()
{
var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=1025;");
conn.Open();
conn.Close();
}
//
// MembershipProvider.GetUserNameByEmail
//
public override string GetUserNameByEmail(string email)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT user_name" + " FROM " + tableName + " WHERE Email = @Email AND application_name = @application_name", conn);
cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
string username = "";
try
{
conn.Open();
username = (string) cmd.ExecuteScalar();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUserNameByEmail");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
if (username == null)
{
username = "";
}
return username;
}
//
// MembershipProvider.GetPassword
//
public override string GetPassword(string username, string answer)
{
if (!EnablePasswordRetrieval)
{
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Password Retrieval Not Enabled.");
}
if (PasswordFormat == MembershipPasswordFormat.Hashed)
{
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Cannot retrieve Hashed passwords.");
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("SELECT Password, password_answer, is_locked_out FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
string password = "";
string passwordAnswer = "";
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
reader.Read();
if (reader.GetBoolean(2))
{
throw new MembershipPasswordException("The supplied user is locked out.");
}
password = reader.GetString(0);
passwordAnswer = reader.GetString(1);
}
else
{
throw new MembershipPasswordException("The supplied user name is not found.");
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetPassword");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
{
UpdateFailureCount(username, "passwordAnswer");
throw new MembershipPasswordException("Incorrect password answer.");
}
if (PasswordFormat == MembershipPasswordFormat.Encrypted)
{
password = UnEncodePassword(password);
}
return password;
}
//
// RoleProvider.CreateRole
//
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') > 0)
{
throw new ArgumentException("Role names cannot contain commas.");
}
if (RoleExists(rolename))
{
throw new ProviderException("Role name already exists.");
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"INSERT INTO " + rolesTable + "" + " (role_name, application_name) " + " Values(@role_name, @application_name)",
conn);
cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteBlind();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateRole");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public MembershipUser GetCustomUser(string username)
{
NpgsqlMembershipProvider _provider = null;
ProviderCollection _providers = null;
// Get a reference to the <imageService> section
MembershipSection section = (MembershipSection) WebConfigurationManager.GetSection("system.web/membership");
// Load registered providers and point _provider
// to the default provider
_providers = new ProviderCollection();
ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (NpgsqlMembershipProvider));
_provider = (NpgsqlMembershipProvider) _providers[section.DefaultProvider];
NpgsqlConnection conn = new NpgsqlConnection(_provider.connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT UserId, user_name, Email, password_question," +
" Comment, is_approved, is_locked_out, creation_date, last_login_date," +
" last_activity_date, last_password_changed_date, last_locked_out_date" + " FROM " + tableName +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = _provider.ApplicationName;
MembershipUser u = null;
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
u = GetUserFromReader(reader);
reader.Close();
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUser(String, Boolean)");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return u;
}
//
// MembershipProvider.ValidateUser
//
public override bool ValidateUser(string username, string password)
{
bool isValid = false;
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT Password, is_approved FROM " + tableName + "" +
" WHERE user_name = @user_name AND application_name = @application_name AND is_locked_out = false", conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
NpgsqlDataReader reader = null;
bool isApproved = false;
string pwd = "";
try
{
conn.Open();
using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
reader.Read();
pwd = reader.GetString(0);
isApproved = reader.GetBoolean(1);
}
else
{
return false;
}
reader.Close();
}
if (CheckPassword(password, pwd))
{
if (isApproved)
{
isValid = true;
NpgsqlCommand updateCmd =
new NpgsqlCommand(
"UPDATE " + tableName + " SET last_login_date = @last_login_date, last_activity_date = @last_activity_date" +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
updateCmd.Parameters.Add("@last_login_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
updateCmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
// fixed by Alex .ToString("yyyy/MM/dd HH:mm:ss");
updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
updateCmd.ExecuteBlind();
}
}
else
{
cmd.Dispose();
conn.Close();
UpdateFailureCount(username, "password");
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ValidateUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return isValid;
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
Process process = new Process();
process.StartInfo.FileName = "/bin/bash";
process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'";
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardOutput = true;
process.Start();
using (StreamReader reader = process.StandardOutput) {
tainted_2 = reader.ReadToEnd();
process.WaitForExit();
process.Close();
}
tainted_3 = tainted_2;
if ((Math.Sqrt(42) <= 42))
{
StringBuilder escape = new StringBuilder();
for (int i = 0; i < tainted_2.Length; ++i)
{
char current = tainted_2[i];
switch (current)
{
case '\\':
escape.Append(@"\5c");
break;
case '*':
escape.Append(@"\2a");
break;
case '(':
escape.Append(@"\28");
break;
case ')':
escape.Append(@"\29");
break;
case '\u0000':
escape.Append(@"\00");
break;
case '/':
escape.Append(@"\2f");
break;
default:
escape.Append(current);
break;
}
}
tainted_3 = escape.ToString();
}
else
{
{}
}
//flaw
string query = "SELECT * FROM '" + tainted_3 + "'";
string connectionString = "Server=localhost;port=1337;User Id=postgre_user;Password=postgre_password;Database=dbname";
NpgsqlConnection dbConnection = null;
try{
dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
NpgsqlCommand cmd = new NpgsqlCommand(query, dbConnection);
NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.Write("{0}\n", dr[0]);
}
dbConnection.Close();
}catch (Exception e) {
Console.WriteLine(e.ToString());
}
}
//
// UpdateFailureCount
// A helper method that performs the checks and updates associated with
// password failure tracking.
//
private void UpdateFailureCount(string username, string failureType)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("SELECT failed_password_attempt_count, failed_password_attempt_window_start, failed_password_answer_attempt_count, failed_password_answer_attempt_window_start FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
NpgsqlDataReader reader = null;
DateTime windowStart = new DateTime();
int failureCount = 0;
try
{
conn.Open();
using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
reader.Read();
if (failureType == "password")
{
failureCount = reader.GetInt32(0);
windowStart = reader.GetDateTime(1);
}
if (failureType == "passwordAnswer")
{
failureCount = reader.GetInt32(2);
windowStart = reader.GetDateTime(3);
}
}
reader.Close();
}
DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);
if (failureCount == 0 || DateTime.Now > windowEnd)
{
// First password failure or outside of PasswordAttemptWindow.
// Start a new password failure count from 1 and a new window starting now.
if (failureType == "password")
{
cmd.CommandText = string.Format("UPDATE {0} SET failed_password_attempt_count = @Count, failed_password_attempt_window_start = @WindowStart WHERE user_name = @user_name AND application_name = @application_name", tableName);
}
if (failureType == "passwordAnswer")
{
cmd.CommandText = string.Format("UPDATE {0} SET failed_password_answer_attempt_count = @Count, failed_password_answer_attempt_window_start = @WindowStart WHERE user_name = @user_name AND application_name = @application_name", tableName);
}
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", NpgsqlDbType.Integer).Value = 1;
cmd.Parameters.Add("@WindowStart", NpgsqlDbType.Timestamp).Value = DateTime.Now;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
{
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Unable to update failure count and window start.");
}
}
else
{
if (failureCount++ >= MaxInvalidPasswordAttempts)
{
// Password attempts have exceeded the failure threshold. Lock out
// the user.
cmd.CommandText = string.Format("UPDATE {0} SET is_locked_out = @is_locked_out, last_locked_out_date = @last_locked_out_date WHERE user_name = @user_name AND application_name = @application_name", tableName);
cmd.Parameters.Clear();
cmd.Parameters.Add("@is_locked_out", NpgsqlDbType.Boolean).Value = true;
cmd.Parameters.Add("@last_locked_out_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
{
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Unable to lock out user.");
}
}
else
{
// Password attempts have not exceeded the failure threshold. Update
// the failure counts. Leave the window the same.
if (failureType == "password")
{
cmd.CommandText = string.Format("UPDATE {0} SET failed_password_attempt_count = @Count WHERE user_name = @user_name AND application_name = @application_name", tableName);
}
if (failureType == "passwordAnswer")
{
cmd.CommandText = string.Format("UPDATE {0} SET failed_password_answer_attempt_count = @Count WHERE user_name = @user_name AND application_name = @application_name", tableName);
}
cmd.Parameters.Clear();
cmd.Parameters.Add("@Count", NpgsqlDbType.Integer).Value = failureCount;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
if (cmd.ExecuteNonQuery() < 0)
{
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Unable to update failure count.");
}
}
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateFailureCount");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
}
public static void Load()
{
try
{
using (NpgsqlConnection connection = new NpgsqlConnection(SQLManager.ConnectionString))
using (NpgsqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "SELECT * FROM shop";
using (NpgsqlDataReader data = command.ExecuteReader())
{
while (data.Read())
{
GoodItem good = new GoodItem
{
id = data.GetInt32(0),
price_gold = data.GetInt32(3),
price_cash = data.GetInt32(4),
auth_type = data.GetInt32(6), //1 = unidade 2 = dias
buy_type2 = data.GetInt32(7),
buy_type3 = data.GetInt32(8),
tag = data.GetInt32(9),
title = data.GetInt32(10),//0= Sem titulo Id do Slot=requer titulo
visibility = data.GetInt32(11)
};
good.item.SetItemId(data.GetInt32(1));
good.item.name = data.GetString(2);
good.item.count = data.GetInt32(5);
ShopAllList.Add(good);
if (good.visibility != 2 && good.visibility != 4)
{
ShopBuyableList.Add(good);
}
if (!ShopUniqueList.ContainsKey(good.item.id) && good.auth_type > 0)
{
ShopUniqueList.TryAdd(good.item.id, good);
if (good.visibility == 4)
{
set4p++;
}
}
}
LoadDataMatching1Goods(); //Pccafe 0
LoadDataMatching2(); //Pccafe basic/premium
LoadDataItems();
data.Close();
connection.Close();
}
}
if (set4p > 0)
{
Logger.Informations($" [ShopManager] Loaded {set4p} itens invisíveis com ícones liberados.");
}
}
catch (Exception ex)
{
Logger.Exception(ex);
}
//XIEMIELE();
//CreateJsonShop();
}
//
// System.Web.Security.MembershipProvider methods.
//
//
// MembershipProvider.ChangePassword
//
public override bool ChangePassword(string username, string oldPwd, string newPwd)
{
if (!ValidateUser(username, oldPwd))
{
return false;
}
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPwd, true);
OnValidatingPassword(args);
if (args.Cancel)
{
if (args.FailureInformation != null)
{
throw args.FailureInformation;
}
else
{
throw new MembershipPasswordException("Change password canceled due to new password validation failure.");
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("UPDATE {0} SET Password = @Password, last_password_changed_date = @last_password_changed_date WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(newPwd);
cmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ChangePassword");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw;// e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return (rowsAffected > 0); }
public List <TrainConnection> SearchForTrainConnection(DateTime date, string from_station, string to_station)
{
List <TrainConnection> results = new List <TrainConnection>();
using (var command = new NpgsqlCommand(
"SELECT s1.travel_id , s1.train_name, s1.departure_date, s1.departure_hour," +
" sum(s1.price) AS total_price, sum(s1.duration) AS total_duration " +
"FROM " +
"(SELECT route.id AS travel_id, route.train_name AS train_name, route.departure_date AS departure_date, " +
"route.departure_hour AS departure_hour, subroute.price AS price, subroute.travel_duration AS duration, " +
"route_subroute.route_order_number AS from_station_number, route_subroute.route_order_number AS to_station_number " +
"FROM public.route " +
"INNER JOIN public.route_subroute ON route.id = route_subroute.route_id " +
"INNER JOIN public.subroute ON subroute.id = route_subroute.subroute_id) s1 " +
"RIGHT JOIN " +
"(SELECT t1.travel_id AS travel_id, t1.from_station_number AS from_station_number,t2.to_station_number AS to_station_number " +
"FROM " +
"(SELECT route.id AS travel_id, route_subroute.route_order_number AS from_station_number " +
"FROM public.route " +
"INNER JOIN public.route_subroute ON route.id = route_subroute.route_id " +
"INNER JOIN public.subroute ON subroute.id = route_subroute.subroute_id " +
"WHERE subroute.from_station = :from_station) t1 " +
"INNER join " +
"(SELECT route.id AS travel_id, route_subroute.route_order_number AS to_station_number " +
"FROM public.route " +
"INNER JOIN public.route_subroute ON route.id = route_subroute.route_id " +
"INNER JOIN public.subroute ON subroute.id = route_subroute.subroute_id " +
"WHERE subroute.to_station = :to_station) t2 ON(t1.travel_id = t2.travel_id)) s2 " +
"ON s1.travel_id=s2.travel_id AND s1.from_station_number>=s2.from_station_number " +
"and s1.to_station_number<=s2.to_station_number " +
"WHERE " +
"s1.departure_date=:date " +
"GROUP BY " +
"s1.travel_id, s1.train_name, s1.departure_date, s1.departure_hour " +
"ORDER BY " +
"s1.departure_hour;", conn))
{
try
{
conn.Open();
var from_station_db = new NpgsqlParameter(":from_station", DbType.String);
from_station_db.Value = from_station;
command.Parameters.Add(from_station_db);
var to_station_db = new NpgsqlParameter(":to_station", DbType.String);
to_station_db.Value = to_station;
command.Parameters.Add(to_station_db);
var date_db = new NpgsqlParameter(":date", DbType.DateTime);
date_db.Value = date;
command.Parameters.Add(date_db);
command.Prepare();
var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
results.Add(new TrainConnection(
reader.GetInt32(0),
reader.GetString(1),
reader.GetDateTime(2),
reader.GetTimeSpan(3),
reader.GetDecimal(4),
reader.GetTimeSpan(5)));
}
}
}
catch
{
return(null);
}
conn.Close();
}
for (int i = 0; i < results.Count; i++)
{
using (var cmd = new NpgsqlCommand(
"SELECT " +
"sum(subroute.travel_duration) " +
"FROM " +
"route " +
"INNER JOIN route_subroute " +
"ON route.id = route_subroute.route_id " +
"INNER JOIN subroute " +
"ON subroute.id = route_subroute.subroute_id " +
"WHERE " +
"route.id = :route_id AND route_subroute.route_order_number < ( " +
"SELECT " +
"route_subroute.route_order_number " +
"FROM " +
"route " +
"INNER JOIN route_subroute " +
"ON route.id = route_subroute.route_id " +
"INNER JOIN subroute " +
"ON subroute.id = route_subroute.subroute_id " +
"WHERE " +
"route.id = :route_id AND subroute.from_station = :from_station " +
") " +
"GROUP BY route.id " +
"ORDER BY route.departure_hour;", conn))
{
try
{
conn.Open();
var from_station_db2 = new NpgsqlParameter(":from_station", DbType.String);
from_station_db2.Value = from_station;
cmd.Parameters.Add(from_station_db2);
var route_id_db = new NpgsqlParameter(":route_id", DbType.Int32);
route_id_db.Value = results[i].travel_id;
cmd.Parameters.Add(route_id_db);
cmd.Prepare();
var reader2 = cmd.ExecuteReader();
if (reader2.HasRows)
{
while (reader2.Read())
{
var timeToAdd =
results[i].departure_hour += reader2.GetTimeSpan(0);
}
}
conn.Close();
}
catch
{
return(null);
}
}
}
return(results);
}
//
// MembershipProvider.ChangePasswordQuestionAndAnswer
//
public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPwdQuestion,
string newPwdAnswer)
{
if (!ValidateUser(username, password))
{
return false;
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("UPDATE {0} SET password_question = @Question, password_answer = @Answer WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@Question", NpgsqlDbType.Text, 255).Value = newPwdQuestion;
cmd.Parameters.Add("@Answer", NpgsqlDbType.Text, 255).Value = EncodePassword(newPwdAnswer);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw;// e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return (rowsAffected > 0);
}
private void Option7()
{
string errorMsg = null;
while (true)
{
Console.Clear();
Console.WriteLine("Creation of a new movie.(write exit to go back)\n");
if (errorMsg != null)
{
Console.WriteLine("Error: " + errorMsg);
}
Console.Write("Please write a title:");
String title = Console.ReadLine();
if (title == "exit")
{
return;
}
Console.Write("Please write a year:");
String year = Console.ReadLine();
if (year == "exit")
{
return;
}
Console.Write("Please write an age restriction:");
String ageRestriction = Console.ReadLine();
if (ageRestriction == "exit")
{
return;
}
Console.Write("Please write a price:");
String price = Console.ReadLine();
if (price == "exit")
{
return;
}
NpgsqlTransaction transaction = null;
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(Configuration.CONNECTION_STRING);
connection.Open();
transaction = connection.BeginTransaction();
Movie movie = new Movie(title, Int32.Parse(year), Int32.Parse(ageRestriction), Int32.Parse(price));
movie.Save();
Copy copy = new Copy(true, movie.Id);
copy.Save();
transaction.Commit();
}
catch (Exception e)
{
errorMsg = e.Message;
transaction?.Rollback();
continue;
}
finally
{
connection?.Close();
}
Console.WriteLine("Success! Movie was created!");
break;
}
while (true)
{
Console.Write("\n\nPress ESC to go back...");
if (Console.ReadKey().Key == ConsoleKey.Escape)
{
return;
}
}
}
//
// MembershipProvider.CreateUser
//
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion,
string passwordAnswer, bool isApproved, object providerUserKey,
out MembershipCreateStatus status)
{
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
MembershipUser u = GetUser(username, false);
if (u == null)
{
DateTime createDate = DateTime.Now;
if (providerUserKey == null)
{
providerUserKey = Guid.NewGuid();
}
else
{
if (!(providerUserKey is Guid))
{
status = MembershipCreateStatus.InvalidProviderUserKey;
return null;
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("INSERT INTO {0} (UserId, user_name, Password, Email, password_question, password_answer, is_approved, Comment, creation_date, last_password_changed_date, last_activity_date, application_name, is_locked_out, last_locked_out_date, failed_password_attempt_count, failed_password_attempt_window_start, failed_password_answer_attempt_count, failed_password_answer_attempt_window_start) Values(@UserId, @user_name, @Password, @Email, @password_question, @password_answer, @is_approved, @Comment, @creation_date, @last_password_changed_date, @last_activity_date, @application_name, @is_locked_out, @last_locked_out_date, @failed_password_attempt_count, @failed_password_attempt_window_start, @failed_password_answer_attempt_count, @failed_password_answer_attempt_window_start)", tableName), conn);
cmd.Parameters.Add("@UserId", NpgsqlDbType.Text).Value = providerUserKey.ToString();
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(password);
cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
cmd.Parameters.Add("@password_question", NpgsqlDbType.Text, 255).Value = passwordQuestion;
cmd.Parameters.Add("@password_answer", NpgsqlDbType.Text, 255).Value = passwordAnswer == null
? null
: EncodePassword(passwordAnswer);
cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = isApproved;
cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = "";
cmd.Parameters.Add("@creation_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
cmd.Parameters.Add("@is_locked_out", NpgsqlDbType.Boolean).Value = false; //false
cmd.Parameters.Add("@last_locked_out_date", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@failed_password_attempt_count", NpgsqlDbType.Integer).Value = 0;
cmd.Parameters.Add("@failed_password_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;
cmd.Parameters.Add("@failed_password_answer_attempt_count", NpgsqlDbType.Integer).Value = 0;
cmd.Parameters.Add("@failed_password_answer_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;
try
{
conn.Open();
int recAdded = cmd.ExecuteNonQuery();
if (recAdded > 0)
{
status = MembershipCreateStatus.Success;
}
else
{
status = MembershipCreateStatus.UserRejected;
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateUser");
}
status = MembershipCreateStatus.ProviderError;
}
finally
{
cmd.Dispose();
conn.Close();
}
return GetUser(username, false);
}
else
{
status = MembershipCreateStatus.DuplicateUserName;
}
return null;
}
public List <Student> ReadAllStudentBySectionBetweenDates(DateTime initDate, DateTime endDate, Section section)
{
conn = DAO.getConnection();
Student readStudent = new Student();
List <Student> students = new List <Student>();
String email;
String password;
String name;
String lastName;
DateTime birthDate;
String phone;
String country;
int gradeId;
String gradeName;
Char status;
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand(DAOReportResource.ReadStudentsBySectionPeriodSP, conn);
NpgsqlParameter parameter = new NpgsqlParameter();
NpgsqlParameter parameter_2 = new NpgsqlParameter();
NpgsqlParameter parameter_3 = new NpgsqlParameter();
parameter.ParameterName = DAOReportResource.IniDate;
parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter.Direction = ParameterDirection.Input;
parameter.Value = initDate;
command.Parameters.Add(parameter);
parameter_2.ParameterName = DAOReportResource.EndDate;
parameter_2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
parameter_2.Direction = ParameterDirection.Input;
parameter_2.Value = endDate;
command.Parameters.Add(parameter_2);
parameter_3.ParameterName = DAOReportResource.Id;
parameter_3.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Integer;
parameter_3.Direction = ParameterDirection.Input;
parameter_3.Value = section.Id;
command.Parameters.Add(parameter_3);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
email = dr.GetString(0);
password = dr.GetString(1);
name = dr.GetString(2);
lastName = dr.GetString(3);
birthDate = dr.GetDateTime(4);
phone = dr.GetString(5);
country = dr.GetString(6);
status = dr.GetChar(7);
//gradeId = dr.GetInt32(8);
//gradeName = dr.GetString(9);
Grade grade = new Grade();
readStudent = new Student(email, password, name, lastName, birthDate, phone, country, grade);
readStudent.Status = status;
students.Add(readStudent);
}
dr.Close();
tran.Commit();
return(students);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex2)
{
throw ex2;
}
finally
{
conn.Close();
}
}
//
// MembershipProvider.DeleteUser
//
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("DELETE FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int rowsAffected = 0;
try
{
conn.Open();
rowsAffected = cmd.ExecuteNonQuery();
if (deleteAllRelatedData)
{
// Process commands to delete all data for the user in the database.
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw;//e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return (rowsAffected > 0); }
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"INSERT INTO " + usersInRolesTable + "" + " (user_name, role_name, application_name) " +
" Values(@user_name, @role_name, @application_name)", conn);
NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255);
NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
NpgsqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteBlind();
}
}
tran.Commit();
}
catch (NpgsqlException e)
{
try
{
if (tran != null)
{
tran.Rollback();
}
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
//
// MembershipProvider.GetAllUsers
//
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(string.Format("SELECT Count(*) FROM {0} WHERE application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
MembershipUserCollection users = new MembershipUserCollection();
NpgsqlDataReader reader = null;
totalRecords = 0;
try
{
conn.Open();
totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
if (totalRecords <= 0)
{
return users;
}
cmd.CommandText = string.Format("SELECT UserId, user_name, Email, password_question, Comment, is_approved, is_locked_out, creation_date, last_login_date, last_activity_date, last_password_changed_date, last_locked_out_date FROM {0} WHERE application_name = @application_name ORDER BY user_name Asc", tableName);
using (reader = cmd.ExecuteReader())
{
int counter = 0;
int startIndex = pageSize*pageIndex;
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
MembershipUser u = GetUserFromReader(reader);
users.Add(u);
}
if (counter >= endIndex)
{
cmd.Cancel();
}
counter++;
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetAllUsers");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw;// e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
return users;
}
public void MinPoolSizeLargeThanMaxPoolSize()
{
var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=2;MaxPoolSize=1");
conn.Open();
conn.Close();
}
//
// MembershipProvider.GetNumberOfUsersOnline
//
public override int GetNumberOfUsersOnline()
{
TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
string.Format("SELECT Count(*) FROM {0} WHERE last_activity_date > @CompareDate AND application_name = @application_name", tableName), conn);
cmd.Parameters.Add("@CompareDate", NpgsqlDbType.Timestamp).Value = compareTime;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int numOnline = 0;
try
{
conn.Open();
numOnline = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetNumberOfUsersOnline");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw;// e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
return numOnline;
}
//
// MembershipProvider.ResetPassword
//
public override string ResetPassword(string username, string answer)
{
if (!EnablePasswordReset)
{
throw new NotSupportedException("Password reset is not enabled.");
}
if (answer == null && RequiresQuestionAndAnswer)
{
UpdateFailureCount(username, "passwordAnswer");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException("Password answer required for password reset.");
}
string newPassword = Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(args);
if (args.Cancel)
{
if (args.FailureInformation != null)
{
throw args.FailureInformation;
}
else
{
throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
}
}
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT password_answer, is_locked_out FROM " + tableName + "" +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
int rowsAffected = 0;
string passwordAnswer = "";
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.HasRows)
{
reader.Read();
if (reader.GetBoolean(1))
{
throw new MembershipPasswordException("The supplied user is locked out.");
}
passwordAnswer = reader.GetString(0);
}
else
{
throw new MembershipPasswordException("The supplied user name is not found.");
}
reader.Close();
}
if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
{
UpdateFailureCount(username, "passwordAnswer");
throw new MembershipPasswordException("Incorrect password answer.");
}
NpgsqlCommand updateCmd =
new NpgsqlCommand(
"UPDATE " + tableName + "" + " SET Password = @Password, last_password_changed_date = @last_password_changed_date" +
" WHERE user_name = @user_name AND application_name = @application_name AND is_locked_out = false", conn);
updateCmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(newPassword);
updateCmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
rowsAffected = updateCmd.ExecuteNonQuery();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "ResetPassword");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
if (rowsAffected > 0)
{
return newPassword;
}
else
{
throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
}
}
//
// RoleProvider.FindUsersInRole
//
public override string[] FindUsersInRole(string rolename, string usernameToMatch)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"SELECT user_name FROM " + usersInRolesTable + " " +
"WHERE user_name LIKE @UsernameSearch AND role_name = @role_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@UsernameSearch", NpgsqlDbType.Text, 255).Value = usernameToMatch;
cmd.Parameters.Add("@RoleName", NpgsqlDbType.Text, 255).Value = rolename;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
string tmpUserNames = "";
NpgsqlDataReader reader = null;
try
{
conn.Open();
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tmpUserNames += reader.GetString(0) + ",";
}
reader.Close();
}
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersInRole");
}
else
{
throw e;
}
}
finally
{
if (reader != null)
{
reader.Close();
}
cmd.Dispose();
conn.Close();
}
if (tmpUserNames.Length > 0)
{
// Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
return new string[0];
}
public void OneTimeTearDown()
{
_connection?.Close();
_connection?.Dispose();
_connection = null;
}
//
// MembershipProvider.UpdateUser
//
public override void UpdateUser(MembershipUser user)
{
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
NpgsqlCommand cmd =
new NpgsqlCommand(
"UPDATE " + tableName + "" + " SET Email = @Email, Comment = @Comment," + " is_approved = @is_approved" +
" WHERE user_name = @user_name AND application_name = @application_name", conn);
cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = user.Email;
cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = user.Comment;
cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = user.IsApproved;
cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = user.UserName;
cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
try
{
conn.Open();
cmd.ExecuteBlind();
}
catch (NpgsqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateUser");
// use fully qualified name so as not to conflict with System.Data.ProviderException
// in System.Data.Entity assembly
throw new System.Configuration.Provider.ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
cmd.Dispose();
conn.Close();
}
}
