/// <summary>
/// 执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public void DeriveParametersVarious()
{
using (var conn = OpenConnection())
{
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE OR REPLACE FUNCTION pg_temp.func(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS
'
BEGIN
param2 = ''sometext'';
param3 = param1 + param3;
END;
' LANGUAGE 'plpgsql';
");
var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
cmd.Parameters[0].Value = 5;
cmd.Parameters[2].Value = 4;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
}
}
/// <summary>
/// 在事务中执行 Transact-SQL 语句并返回受影响的行数。
/// </summary>
public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 在事务中执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
public void RecordWithNonIntField()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand("SELECT ('one', 2)", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var record = reader.GetFieldValue<object[]>(0);
Assert.That(record[0], Is.EqualTo("one"));
Assert.That(record[1], Is.EqualTo(2));
}
}
public void Bug1285()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand { Connection = conn })
{
cmd.CommandText = Bug1285CreateStatement;
cmd.ExecuteNonQuery();
cmd.CommandText = Bug1285SelectStatement;
cmd.Parameters.Add(new NpgsqlParameter("@1", Guid.NewGuid()));
cmd.ExecuteNonQuery();
}
}
public void BaseCatalogName()
{
var dbName = new NpgsqlConnectionStringBuilder(ConnectionString).Database;
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo INTEGER)");
using (var cmd = new NpgsqlCommand("SELECT foo,8 FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].BaseCatalogName, Is.EqualTo(dbName));
Assert.That(columns[1].BaseCatalogName, Is.EqualTo(dbName));
}
}
}
public void AllowDBNull()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (nullable INTEGER, non_nullable INTEGER NOT NULL)");
using (var cmd = new NpgsqlCommand("SELECT nullable,non_nullable,8 FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].AllowDBNull, Is.True);
Assert.That(columns[1].AllowDBNull, Is.False);
Assert.That(columns[2].AllowDBNull, Is.Null);
}
}
}
public void BaseColumnName()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo INTEGER)");
using (var cmd = new NpgsqlCommand("SELECT foo,8 AS bar,8,'8'::VARCHAR(10) FROM data", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
Assert.That(columns[0].BaseColumnName, Is.EqualTo("foo"));
Assert.That(columns[1].BaseColumnName, Is.EqualTo("bar"));
Assert.That(columns[2].BaseColumnName, Is.Null);
Assert.That(columns[3].BaseColumnName, Is.EqualTo("varchar"));
}
}
}
public void PrimaryKeyFieldMetadataSupport()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (id SERIAL PRIMARY KEY, serial SERIAL)");
using (var command = new NpgsqlCommand("SELECT * FROM data", conn))
{
using (var dr = command.ExecuteReader(CommandBehavior.KeyInfo))
{
dr.Read();
var metadata = dr.GetSchemaTable();
var key = metadata.Rows.Cast<DataRow>().Single(r => (bool)r["IsKey"]);
Assert.That(key["ColumnName"], Is.EqualTo("id"));
}
}
}
}
/// <summary>
/// 执行查询，返回DataSet
/// </summary>
public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
}
}
}
/// <summary>
/// 执行查询，返回DataReader
/// </summary>
public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
NpgsqlCommand cmd = new NpgsqlCommand();
NpgsqlConnection conn = new NpgsqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public void ManyParametersWithMixedFormatCode()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
var sb = new StringBuilder("SELECT @text_param");
cmd.Parameters.AddWithValue("@text_param", "some_text");
for (var i = 0; i < conn.BufferSize; i++)
{
var paramName = $"@binary_param{i}";
sb.Append(",");
sb.Append(paramName);
cmd.Parameters.AddWithValue(paramName, 8);
}
cmd.CommandText = sb.ToString();
Assert.That(() => cmd.ExecuteNonQuery(), Throws.Exception
.TypeOf<PostgresException>()
.With.Property(nameof(PostgresException.SqlState)).EqualTo("54000")
);
}
}
/**
* <summary>Metodo que retorna la Lista de eventos por un id de categoria dada</summary>
* <params name="id_categoria">Id de la categria</params>
* <returns>La lista de eventos
* </returns>
*/
public List <Evento> ListaEventosPorCategoria(int id_categoria)
{
List <Evento> list = new List <Evento>();
try
{
comando = new NpgsqlCommand("ConsultarEventoPorIdCategoria", conexion.SqlConexion);
comando.CommandType = CommandType.StoredProcedure;
comando.Parameters.AddWithValue(NpgsqlTypes.NpgsqlDbType.Integer, id_categoria);
read = comando.ExecuteReader();
while (read.Read())
{
DateTime horaInicio = new DateTime();
horaInicio.AddHours(read.GetTimeSpan(6).Hours);
horaInicio.AddMinutes(read.GetTimeSpan(6).Minutes);
DateTime horaFin = new DateTime();
horaFin.AddHours(read.GetTimeSpan(7).Hours);
horaFin.AddMinutes(read.GetTimeSpan(7).Minutes);
Evento evento = new Evento(read.GetInt32(0), read.GetString(1), read.GetString(2), read.GetInt64(3), read.GetDateTime(4), read.GetDateTime(5),
horaInicio, horaFin, read.GetString(8), read.GetInt32(9));
list.Add(evento);
}
}
catch (BaseDeDatosExcepcion e)
{
e.NombreMetodos.Add(this.GetType().FullName + "." + MethodBase.GetCurrentMethod().Name);
e.Mensaje = "Problemas en la base de datos, en ListaEventosPorCategoria";
throw e;
}
finally
{
conexion.Desconectar();
}
return(list);
}
/// <summary>
/// Insert a new database record, or updates one if it already exists from the supplied data transfer object.
/// This is typically used during the import of existing data to the Database.
/// </summary>
/// <param name="transaction">
/// The current <see cref="NpgsqlTransaction"/> to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource will be stored.
/// </param>
/// <param name="parameterGroup">
/// The parameterGroup DTO that is to be persisted.
/// </param>
/// <param name="container">
/// The container of the DTO to be persisted.
/// </param>
/// <returns>
/// True if the concept was successfully persisted.
/// </returns>
public virtual bool Upsert(NpgsqlTransaction transaction, string partition, CDP4Common.DTO.ParameterGroup parameterGroup, CDP4Common.DTO.Thing container = null)
{
var valueTypeDictionaryAdditions = new Dictionary <string, string>();
base.Upsert(transaction, partition, parameterGroup, container);
var valueTypeDictionaryContents = new Dictionary <string, string>
{
{ "Name", !this.IsDerived(parameterGroup, "Name") ? parameterGroup.Name.Escape() : string.Empty },
}.Concat(valueTypeDictionaryAdditions).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
using (var command = new NpgsqlCommand())
{
var sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendFormat("INSERT INTO \"{0}\".\"ParameterGroup\"", partition);
sqlBuilder.AppendFormat(" (\"Iid\", \"ValueTypeDictionary\", \"Container\", \"ContainingGroup\")");
sqlBuilder.AppendFormat(" VALUES (:iid, :valueTypeDictionary, :container, :containingGroup)");
command.Parameters.Add("iid", NpgsqlDbType.Uuid).Value = parameterGroup.Iid;
command.Parameters.Add("valueTypeDictionary", NpgsqlDbType.Hstore).Value = valueTypeDictionaryContents;
command.Parameters.Add("container", NpgsqlDbType.Uuid).Value = container.Iid;
command.Parameters.Add("containingGroup", NpgsqlDbType.Uuid).Value = !this.IsDerived(parameterGroup, "ContainingGroup") ? Utils.NullableValue(parameterGroup.ContainingGroup) : Utils.NullableValue(null);
sqlBuilder.Append(" ON CONFLICT (\"Iid\")");
sqlBuilder.Append(" DO UPDATE ");
sqlBuilder.Append(" SET (\"ValueTypeDictionary\", \"Container\", \"ContainingGroup\")");
sqlBuilder.Append(" = (:valueTypeDictionary, :container, :containingGroup);");
command.CommandText = sqlBuilder.ToString();
command.Connection = transaction.Connection;
command.Transaction = transaction;
this.ExecuteAndLogCommand(command);
}
return(true);
}
public ActionResult Login([FromBody] Login user)
{
NpgsqlConnection conn = getDbConnection();
conn.Open();
using (var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM \"Users\" WHERE username=@username AND password=@password", conn))
{
cmd.Parameters.AddWithValue("username", user.username);
cmd.Parameters.AddWithValue("password", user.password);
var result = cmd.ExecuteScalar();
int i = Convert.ToInt32(result);
if (i == 0)
{
conn.Close();
return(NotFound());
}
else
{
using (var cmd2 = new NpgsqlCommand("SELECT id FROM \"Users\" WHERE username=@username", conn))
{
cmd2.Parameters.AddWithValue("username", user.username);
NpgsqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
int id = Convert.ToInt32(dr["id"]);
conn.Close();
return(Ok(id));
}
}
conn.Close();
return(NotFound());
}
}
}
public void AddItem(ShoppingItem shoppingRequest, int userHouseId)
{
_connection.Open();
try
{
var command = new NpgsqlCommand("INSERT INTO public.\"ShoppingItem\" (\"Name\", \"AddedOn\", \"AddedBy\", \"Purchased\", \"HouseId\") " +
$"VALUES ('{shoppingRequest.Name}', '{shoppingRequest.Added:yyyy-MM-dd}', {shoppingRequest.AddedBy}, FALSE, {userHouseId}) " +
"RETURNING \"Id\"", _connection);
Int64 itemId = -1;
var reader = command.ExecuteReader();
while (reader.Read())
{
itemId = (Int64)reader[0];
}
reader.Close();
foreach (var peopleId in shoppingRequest.ItemFor)
{
command = new NpgsqlCommand("INSERT INTO public.\"ShoppingItemFor\" (\"ShoppingItemId\", \"PersonId\") " +
$"VALUES ({itemId}, {peopleId})", _connection);
reader = command.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
}
}
catch (System.Exception exception)
{
throw new System.Exception($"An Error occured while adding the shopping item '{shoppingRequest.Name}'", exception);
}
finally
{
_connection.Close();
}
}
public override bool Contains(UUID regionID, string parameter)
{
RwLockedDictionary <string, string> regParams;
if (m_Cache.TryGetValue(regionID, out regParams) &&
regParams.ContainsKey(parameter))
{
return(true);
}
using (var connection = new NpgsqlConnection(m_ConnectionString))
{
connection.Open();
using (var cmd = new NpgsqlCommand("SELECT * FROM serverparams WHERE regionid = @regionid AND parametername = @parametername LIMIT 1", connection))
{
cmd.Parameters.AddParameter("@regionid", regionID);
cmd.Parameters.AddParameter("@parametername", parameter);
using (NpgsqlDataReader dbReader = cmd.ExecuteReader())
{
if (dbReader.Read())
{
m_Cache[regionID][parameter] = (string)dbReader["parametervalue"];
return(true);
}
}
}
}
if (UUID.Zero != regionID &&
Contains(UUID.Zero, parameter))
{
return(true);
}
return(false);
}
public long InsertMain(string targetTable, long timeStamp)
{
// Open the connection to the psqlDb
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectToNpgSQL.GetConnection()))
try
{
conn.Open();
// Console.WriteLine("Connection Initilized");
// Create insert command.
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO " +
$"{targetTable}(time_stamp) VALUES(:time_stamp) returning *;", conn);
// Add paramaters.
command.Parameters.Add(new NpgsqlParameter("time_stamp",
NpgsqlTypes.NpgsqlDbType.Bigint));
// Prepare the command.
command.Prepare();
// Add value to the paramater.
command.Parameters[0].Value = timeStamp;
// Execute SQL command.
object recordAffected = command.ExecuteScalar();
if (Convert.ToBoolean(recordAffected))
{
return(Convert.ToInt64(recordAffected));
}
conn.Close();
}
catch (NpgsqlException ex)
{
Console.WriteLine(ex);
long err = 0;
return(err);
}
return(0);
}
/// <summary>
/// Este método obtém um registro da tabela <b>servicos</b> correspondente ao id
/// </summary>
/// <returns>Um objeto do tipo Servico</returns>
public Servico GetServicoPorId(int id)
{
// Declara parâmetros a serem usados na criação do objeto
string nome;
double valorBase;
Servico servico;
List <Servico> servicos = new List <Servico>(1);
// Cria objeto da conexão com o banco de dados e escopo deste objeto
using (NpgsqlConnection conexao = new NpgsqlConnection(config))
{
// Abre conexão com o banco
conexao.Open();
// Declara sentença SQL
string selecionar = $"SELECT * FROM public.servicos WHERE id = {id};";
// Cria escopo e objeto do comando SQL utilizando a sentença e a conexão como parâmetros
using (NpgsqlCommand comando = new NpgsqlCommand(selecionar, conexao))
{
// Executa query
NpgsqlDataReader query = comando.ExecuteReader();
// Itera sobre os resultados da query
while (query.Read())
{
// Cria um objeto do tipo Servico com os dados das colunas
nome = query.GetString(1).Trim();
valorBase = query.GetDouble(2);
servico = new Servico(id, nome, valorBase);
// Adiciona objeto na lista servicos
servicos.Add(servico);
}
}
// Fecha conexão com o banco de dados
conexao.Close();
}
// Retorna o primeiro e único objeto da lista do tipo Servico
return(servicos[0]);
}
public bool AtualizarFesta()
{
try
{
this.conn = new NpgsqlConnection(this.connString);
Encoding enc = new UTF8Encoding(true, true);
//Abra a conexão com o PgSQL
this.conn.Open();
string cmdInserir = String.Format("UPDATE festa SET local = '{0}', convidados = {1}, data = '{2}', nome_festa = '{3}' where id = {4} and id_usuario = {5}", Festa.pegarLocal(),
Festa.pegarConvidados(), Festa.pegarData(), Festa.pegarNome(), Festa.pegarId(), AutenticacaoCliente.pegarId());
byte[] bytes = Encoding.Default.GetBytes(cmdInserir);
cmdInserir = Encoding.UTF8.GetString(bytes);
using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(cmdInserir, this.conn))
{
pgsqlcommand.ExecuteNonQuery();
return(true);
}
}
catch (NpgsqlException ex)
{
//throw ex;
return(false);
}
catch (Exception ex)
{
//throw ex;
return(false);
}
finally
{
this.conn.Close();
}
}
public bool CadastrarFesta()
{
try
{
Encoding enc = new UTF8Encoding(true, true);
this.conn = new NpgsqlConnection(this.connString);
//Abra a conexão com o PgSQL
this.conn.Open();
string cmdInserir = String.Format("Insert Into festa(local, id_usuario, convidados, data, nome_festa) values('{0}',{1},{2},'{3}','{4}')", Festa.pegarLocal(), AutenticacaoCliente.pegarId(),
Festa.pegarConvidados(), Festa.pegarData(), Festa.pegarNome());
byte[] bytes = Encoding.Default.GetBytes(cmdInserir);
cmdInserir = Encoding.UTF8.GetString(bytes);
using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand(cmdInserir, this.conn))
{
pgsqlcommand.ExecuteNonQuery();
return(true);
}
}
catch (NpgsqlException ex)
{
throw ex;
//return true;
}
catch (Exception ex)
{
throw ex;
//return true;
}
finally
{
this.conn.Close();
}
}
public virtual void DoInsertWithCommandBuilderCaseSensitive()
{
DataSet ds = new DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablei", TheConnection);
NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(da);
Assert.IsNotNull(builder);
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
dr["Field_Case_Sensitive"] = 4;
dt.Rows.Add(dr);
DataSet ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
NpgsqlDataReader dr2 = new NpgsqlCommand("select * from tablei", TheConnection).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[1]);
dr2.Close();
}
private void Btn_Search_Click(object sender, EventArgs e)
{
if (dataGridView1.RowCount != 1)
{
dataGridView1.Rows.Clear();
}
using (var conn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=postgres;Database=testdb"))
{
String result = "";
try
{
conn.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT path.node, m_node.statnnm, path.agg_cost, m_linenm.linenm FROM " +
"(SELECT * FROM pgr_dijkstra('SELECT gid as id, source, target, cost FROM m_network', " +
"(select a.gid from m_node as a, m_linenm as b WHERE a.statnnm = '" + comboSS.SelectedItem.ToString() + "' and a.linenum = b.id and b.linenm = '" + comboSL.SelectedItem.ToString() + "' limit 1), " +
"(select a.gid from m_node as a, m_linenm as b WHERE a.statnnm = '" + comboTS.SelectedItem.ToString() + "' and a.linenum = b.id and b.linenm = '" + comboTL.SelectedItem.ToString() + "' limit 1), " +
"false)) as path, m_node, m_linenm WHERE path.node = m_node.gid AND m_linenm.id = m_node.linenum";
using (var reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
count++;
string[] row0 = { count.ToString(), reader[1].ToString(), String.Format("{0:0.##}", Convert.ToDouble(reader[2].ToString())), reader[3].ToString() };
dataGridView1.Rows.Add(row0);
}
}
}
}
catch (Exception ex)
{
}
}
}
public void loadData()
{
try
{
using (clsConnection oConn = new clsConnection())
{
NpgsqlCommand ocmd = new NpgsqlCommand();
frmMain.setLoadDialog(true, "Loading data...");
string strSql = @"select a.carid, a.platnumber,a.imagename,a.typeid,a.filepath_car,a.passengercapacity,a.price,a.description,
a.statusavailable,
b.typeid,b.carcategoryid as categoryid, b.carbrandid,b.typename,
c.carcategoryid as categoryid,c.carcategoryname as categoryname,
d.carbrandid as brandids,d.brandname
from tbm_car a
left join tbm_cartype b on b.typeid = a.typeid and b.dlt='0'
left join tbm_carcategory c on c.carcategoryid = b.carcategoryid and c.dlt='0'
left join tbm_carbrand d on d.carbrandid = b.carbrandid and d.dlt='0'
where a.dlt=false order by a.statusavailable;";
DataTable dtData = oConn.GetData(strSql);
dgData.DataSource = dtData;
foreach (DataRow row in dtData.Rows)
{
}
gridViewData.Columns[1].ColumnEdit = ricmbCarType;
}
}
catch (NpgsqlException ex)
{
}
catch (Exception ex)
{
}
finally
{
frmMain.setLoadDialog(false, "");
}
}
/* Ниже приведена генерическая функция, кот принимает "conn_string", имя СП и его параметры ввиде массива параметров.
* List<Dictionary<string, object> -- лист получает название поля и его значение как объект
*/
private static List <Dictionary <string, object> > Run_sp(string conn_string, string sp_name,
NpgsqlParameter[] parameters)
{
List <Dictionary <string, object> > items = new List <Dictionary <string, object> >();
try
{
using (var conn = new NpgsqlConnection(conn_string))
{
conn.Open();
NpgsqlCommand command = new NpgsqlCommand(sp_name, conn);
command.CommandType = System.Data.CommandType.StoredProcedure; // this is default
//используя AddRange мы можем сразу передать СП полученый массив параметров
command.Parameters.AddRange(parameters);
var reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> one_row = new Dictionary <string, object>();
foreach (var item in reader.GetColumnSchema())
{
object column_value = reader[item.ColumnName];
one_row.Add(item.ColumnName, column_value);
}
items.Add(one_row);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
Console.WriteLine($"Function {sp_name} failed. parameters: {string.Join(",", parameters.Select(_ => _.ParameterName + " : " + _.Value))}");
}
return(items);
}
public void ExceptionFieldsArePopulated()
{
const string dropTable = @"DROP TABLE IF EXISTS public.uniqueviolation";
const string createTable = @"CREATE TABLE public.uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))";
const string insertStatement = @"INSERT INTO public.uniqueviolation (id) VALUES(1)";
// Since the 5 error fields were added as of PostgreSQL 9.3, we'll skip testing for versions previous to that.
if (Conn.PostgreSqlVersion < new Version("9.3"))
{
Assert.Ignore("Postgres version is {0} (< 9.3))", Conn.PostgreSqlVersion);
}
// In this case we'll test a simple unique violation, we're not too interested in testing more
// cases than this as the same code is executed in all error situations.
try
{
var command = new NpgsqlCommand(dropTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createTable, Conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(insertStatement, Conn);
command.ExecuteNonQuery();
//Now cause the unique violation...
command.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
Assert.AreEqual("", ex.ColumnName); // Should not be populated for unique violations.
Assert.AreEqual("uniqueviolation", ex.TableName);
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("uniqueviolation_pkey", ex.ConstraintName);
Assert.AreEqual("", ex.DataTypeName); // Should not be populated for unique violations.
}
}
public void Int32()
{
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", conn))
{
var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer);
var p2 = new NpgsqlParameter("p2", DbType.Int32);
var p3 = new NpgsqlParameter {
ParameterName = "p3", Value = 8
};
Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(p3.DbType, Is.EqualTo(DbType.Int32));
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
p1.Value = p2.Value = (long)8;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
for (var i = 0; i < cmd.Parameters.Count; i++)
{
Assert.That(reader.GetInt32(i), Is.EqualTo(8));
Assert.That(reader.GetInt64(i), Is.EqualTo(8));
Assert.That(reader.GetInt16(i), Is.EqualTo(8));
Assert.That(reader.GetByte(i), Is.EqualTo(8));
Assert.That(reader.GetFloat(i), Is.EqualTo(8.0f));
Assert.That(reader.GetDouble(i), Is.EqualTo(8.0d));
Assert.That(reader.GetDecimal(i), Is.EqualTo(8.0m));
Assert.That(reader.GetValue(i), Is.EqualTo(8));
Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(8));
Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(int)));
Assert.That(reader.GetDataTypeName(i), Is.EqualTo("int4"));
}
}
}
}
/// <summary>
/// Obtain a "Categoria" by his ID
/// </summary>
/// <param name="pIdCategoria">ID to search by</param>
public Categoria Obtener(int pIdCategoria)
{
string query = "SELECT * FROM \"Categoria\" WHERE \"idCategoria\" = '" + pIdCategoria + "'";
Categoria categoria = null;
try
{
using NpgsqlCommand comando = this._conexion.CreateCommand();
comando.CommandText = query;
using (NpgsqlDataAdapter adaptador = new NpgsqlDataAdapter(comando))
{
DataTable tabla = new DataTable();
adaptador.Fill(tabla);
foreach (DataRow fila in tabla.Rows)
{
int _id = Convert.ToInt32(fila["idCategoria"]);
int _idUser = Convert.ToInt32(fila["idUsuario"]);
bool _estado = Convert.ToBoolean(fila["estado"]);
string _nombre = Convert.ToString(fila["nombre"]);
categoria = new Categoria(_id, _nombre, _estado, _idUser);
}
tabla.Dispose();
}
return(categoria);
}
catch (PostgresException e)
{
throw new DAOException("Error al obtener categoría: " + e.Message);
}
catch (NpgsqlException e)
{
throw new DAOException("Error al obtener categoría: " + e.Message);
}
}
public void RestoreProfile(ObservableServerProfile profile)
{
using (var con = new NpgsqlConnection(_connectionString))
using (var cmd = new NpgsqlCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "SELECT profile_values.* " +
"FROM profile_values " +
"INNER JOIN accounts " +
"ON accounts.account_id = profile_values.account_id " +
"WHERE username = @username;";
cmd.Parameters.AddWithValue("@username", profile.Username);
var reader = cmd.ExecuteReader();
// There's no such data
if (!reader.HasRows)
{
return;
}
var data = new Dictionary <short, string>();
while (reader.Read())
{
//var key = reader.GetInt16("value_key");
var key = short.Parse(reader["value_key"].ToString());
var value = reader["value_value"] as string ?? "";
data.Add(key, value);
}
profile.FromStrings(data);
}
}
//BUATAN KITAH
public DataTable articleList(int id)
{
dbHandler = new DbHandler();
articleModel = new ArticleModel();
string query = "SELECT a.id_article, a.tittle, a.year, ao.id_author FROM article a LEFT JOIN article_author aa on aa.id_article = a.id_article LEFT JOIN author ao on ao.id_author = aa.id_author WHERE ao.id_author = " + id + "";
try
{
con = dbHandler.connection();
con.Open();
this.dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4] {
new DataColumn("ID Artikel"), new DataColumn("Judul Artikel"), new DataColumn("Tahun"), new DataColumn("ID Author")
});
using (command = new NpgsqlCommand(query, con))
{
reader = command.ExecuteReader();
while (reader.Read())
{
articleModel.idArtikel = int.Parse(reader[0].ToString());
articleModel.judulArtikel = reader[1].ToString();
articleModel.tahunArtikel = int.Parse(reader[2].ToString());
articleModel.penulisArtikel = int.Parse(reader[3].ToString());
}
}
dt.Rows.Add(articleModel.idArtikel, articleModel.judulArtikel, articleModel.tahunArtikel, articleModel.penulisArtikel);
con.Close();
}
catch (Exception msg)
{
System.Diagnostics.Debug.WriteLine(msg.ToString());
}
return(dt);
}
public void CompositePostgresType()
{
var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
{
ApplicationName = nameof(PostgresType),
Pooling = false
};
using (var conn = OpenConnection(csb))
{
conn.ExecuteNonQuery("CREATE TYPE pg_temp.comp1 AS (x int, some_text text)");
conn.ExecuteNonQuery("CREATE TYPE pg_temp.comp2 AS (comp comp1, comps comp1[])");
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT ROW(ROW(8, 'foo')::comp1, ARRAY[ROW(9, 'bar')::comp1, ROW(10, 'baz')::comp1])::comp2", conn))
{
using (var reader = cmd.ExecuteReader())
{
reader.Read();
var comp2Type = (PostgresCompositeType)reader.GetPostgresType(0);
Assert.That(comp2Type.Name, Is.EqualTo("comp2"));
Assert.That(comp2Type.FullName, Does.StartWith("pg_temp_") & Does.EndWith(".comp2"));
Assert.That(comp2Type.Fields, Has.Count.EqualTo(2));
var field1 = comp2Type.Fields[0];
var field2 = comp2Type.Fields[1];
Assert.That(field1.Name, Is.EqualTo("comp"));
Assert.That(field2.Name, Is.EqualTo("comps"));
var comp1Type = (PostgresCompositeType)field1.Type;
Assert.That(comp1Type.Name, Is.EqualTo("comp1"));
var arrType = (PostgresArrayType)field2.Type;
Assert.That(arrType.Name, Is.EqualTo("comp1[]"));
var elemType = arrType.Element;
Assert.That(elemType, Is.SameAs(comp1Type));
}
}
}
}
public void Macaddr8()
{
using (var conn = OpenConnection())
{
if (conn.PostgreSqlVersion < new Version(10, 0))
{
Assert.Ignore("macaddr8 only supported on PostgreSQL 10 and above");
}
using (var cmd = new NpgsqlCommand("SELECT @p1, @p2", conn))
{
var send6 = PhysicalAddress.Parse("08-00-2B-01-02-03");
var expected6 = PhysicalAddress.Parse("08-00-2B-FF-FE-01-02-03"); // 6-byte macaddr8 gets FF and FE inserted in the middle
var expected8 = PhysicalAddress.Parse("08-00-2B-01-02-03-04-05");
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.MacAddr8)
{
Value = send6
});
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlDbType.MacAddr8)
{
Value = expected8
});
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader.GetFieldValue <PhysicalAddress>(0), Is.EqualTo(expected6));
Assert.That(reader.GetValue(0), Is.EqualTo(expected6));
Assert.That(reader.GetFieldType(0), Is.EqualTo(typeof(PhysicalAddress)));
Assert.That(reader.GetFieldValue <PhysicalAddress>(1), Is.EqualTo(expected8));
Assert.That(reader.GetValue(1), Is.EqualTo(expected8));
Assert.That(reader.GetFieldType(1), Is.EqualTo(typeof(PhysicalAddress)));
}
}
}
}
public string nuevaLocalidad(int id_provincia, string nombre_provincia, string nombre, DateTime fecha, string schema)
{
string retorno = null;
List <Localidad> listLocalidades = listaLocalidades(schema);
bool existe = validarObjetoExistente(listLocalidades, nombre, nombre_provincia);
if (existe)
{
retorno = "La localidad ya existe en ese país";
return(retorno);
}
NpgsqlConnection conexion = null;
NpgsqlCommand cmd = null;
try
{
conexion = Conexion.getInstance().ConexionDB();
cmd = new NpgsqlCommand("logueo.spnuevalocalidad", conexion);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("parm_idprovincia", id_provincia);
cmd.Parameters.AddWithValue("parm_nombre", nombre);
cmd.Parameters.AddWithValue("parm_fecha", fecha);
cmd.Parameters.AddWithValue("parm_schema", schema);
conexion.Open();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
conexion.Close();
}
retorno = "Nueva provincia añadida correctamente";
return(retorno);
}
public static void Insert(string task, string dsk, string rdns)
{
NpgsqlConnection con = new NpgsqlConnection(srv);
NpgsqlCommand com = new NpgsqlCommand("select * from sq", con);
con.Open();
NpgsqlDataReader rdr = com.ExecuteReader();
if (rdns.ToLower() != "false" && rdns.ToLower() != "true")
{
throw new InvalidOperationException();
}
while (rdr.Read())
{
if (task.ToLower() == rdr["task"].ToString().ToLower() && dsk.ToLower() == rdr["description"].ToString().ToLower() && rdns.ToLower() == rdr["readiness"].ToString().ToLower())
{
throw new InvalidOperationException();
}
}
con.Close();
sql += "'" + task + "'," + "'" + dsk + "'," + rdns + ")";
com = new NpgsqlCommand(sql, con);
con.Open();
int qqq = com.ExecuteNonQuery();
Console.WriteLine("SUCCESS!");
con.Close();
}
/// <summary>
/// Update a database record from the supplied data transfer object.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource will be updated.
/// </param>
/// <param name="elementUsage">
/// The elementUsage DTO that is to be updated.
/// </param>
/// <param name="container">
/// The container of the DTO to be updated.
/// </param>
/// <returns>
/// True if the concept was successfully updated.
/// </returns>
public virtual bool Update(NpgsqlTransaction transaction, string partition, CDP4Common.DTO.ElementUsage elementUsage, CDP4Common.DTO.Thing container = null)
{
bool isHandled;
var valueTypeDictionaryAdditions = new Dictionary <string, string>();
var beforeUpdate = this.BeforeUpdate(transaction, partition, elementUsage, container, out isHandled, valueTypeDictionaryAdditions);
if (!isHandled)
{
beforeUpdate = beforeUpdate && base.Update(transaction, partition, elementUsage, container);
var valueTypeDictionaryContents = new Dictionary <string, string>
{
{ "InterfaceEnd", !this.IsDerived(elementUsage, "InterfaceEnd") ? elementUsage.InterfaceEnd.ToString() : string.Empty },
}.Concat(valueTypeDictionaryAdditions).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
using (var command = new NpgsqlCommand())
{
var sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendFormat("UPDATE \"{0}\".\"ElementUsage\"", partition);
sqlBuilder.AppendFormat(" SET (\"Container\", \"ElementDefinition\", \"ValueTypeDictionary\")");
sqlBuilder.AppendFormat(" = (:container, :elementDefinition, \"ValueTypeDictionary\" || :valueTypeDictionary)");
sqlBuilder.AppendFormat(" WHERE \"Iid\" = :iid;");
command.Parameters.Add("iid", NpgsqlDbType.Uuid).Value = elementUsage.Iid;
command.Parameters.Add("container", NpgsqlDbType.Uuid).Value = container.Iid;
command.Parameters.Add("elementDefinition", NpgsqlDbType.Uuid).Value = !this.IsDerived(elementUsage, "ElementDefinition") ? elementUsage.ElementDefinition : Utils.NullableValue(null);
command.Parameters.Add("valueTypeDictionary", NpgsqlDbType.Hstore).Value = valueTypeDictionaryContents;
command.CommandText = sqlBuilder.ToString();
command.Connection = transaction.Connection;
command.Transaction = transaction;
this.ExecuteAndLogCommand(command);
}
}
return(this.AfterUpdate(beforeUpdate, transaction, partition, elementUsage, container));
}
/// <summary>
/// Insert a new database record from the supplied data transfer object.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource will be stored.
/// </param>
/// <param name="participantPermission">
/// The participantPermission DTO that is to be persisted.
/// </param>
/// <param name="container">
/// The container of the DTO to be persisted.
/// </param>
/// <returns>
/// True if the concept was successfully persisted.
/// </returns>
public virtual bool Write(NpgsqlTransaction transaction, string partition, CDP4Common.DTO.ParticipantPermission participantPermission, CDP4Common.DTO.Thing container = null)
{
bool isHandled;
var valueTypeDictionaryAdditions = new Dictionary <string, string>();
var beforeWrite = this.BeforeWrite(transaction, partition, participantPermission, container, out isHandled, valueTypeDictionaryAdditions);
if (!isHandled)
{
beforeWrite = beforeWrite && base.Write(transaction, partition, participantPermission, container);
var valueTypeDictionaryContents = new Dictionary <string, string>
{
{ "AccessRight", !this.IsDerived(participantPermission, "AccessRight") ? participantPermission.AccessRight.ToString() : string.Empty },
{ "ObjectClass", !this.IsDerived(participantPermission, "ObjectClass") ? participantPermission.ObjectClass.ToString() : string.Empty },
{ "IsDeprecated", !this.IsDerived(participantPermission, "IsDeprecated") ? participantPermission.IsDeprecated.ToString() : string.Empty },
}.Concat(valueTypeDictionaryAdditions).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
using (var command = new NpgsqlCommand())
{
var sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendFormat("INSERT INTO \"{0}\".\"ParticipantPermission\"", partition);
sqlBuilder.AppendFormat(" (\"Iid\", \"ValueTypeDictionary\", \"Container\")");
sqlBuilder.AppendFormat(" VALUES (:iid, :valueTypeDictionary, :container);");
command.Parameters.Add("iid", NpgsqlDbType.Uuid).Value = participantPermission.Iid;
command.Parameters.Add("valueTypeDictionary", NpgsqlDbType.Hstore).Value = valueTypeDictionaryContents;
command.Parameters.Add("container", NpgsqlDbType.Uuid).Value = container.Iid;
command.CommandText = sqlBuilder.ToString();
command.Connection = transaction.Connection;
command.Transaction = transaction;
this.ExecuteAndLogCommand(command);
}
}
return(this.AfterWrite(beforeWrite, transaction, partition, participantPermission, container));
}
/// <summary>
/// Método para obtener las Categorías de la base de datos.
/// </summary>
/// <param name="nombreFuncion">Tipo de función a llamar: consultacategoriaactivos o categoriashabilitadas</param></param>
/// <returns></returns>
public MensajesCategoriasActivos ObtenerCategoriasActivos(string nombreFuncion)
{
List <CategoriaActivo> lstCategorias = new List <CategoriaActivo>();
MensajesCategoriasActivos msjCategorias = new MensajesCategoriasActivos();
try
{
using (NpgsqlCommand cmd = new NpgsqlCommand(nombreFuncion, conn_BD))
{
cmd.CommandType = CommandType.StoredProcedure;
using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CategoriaActivo objCategorias = new CategoriaActivo
{
IdCategoriaActivo = (int)dr[0],
NombreCategoriaActivo = dr[1].ToString().Trim(),
DescripcionCategoriaActivo = dr[2].ToString().Trim(),
HabilitadoCategoriaActivo = (bool)dr[3]
};
lstCategorias.Add(objCategorias);
}
conn_BD.Close();
msjCategorias.ListaObjetoInventarios = lstCategorias;
msjCategorias.OperacionExitosa = true;
}
}
}
catch (Exception e)
{
conn_BD.Close();
msjCategorias.OperacionExitosa = false;
msjCategorias.MensajeError = e.Message;
}
return(msjCategorias);
}
public static void Main(string[] args)
{
string tainted_2 = null;
string tainted_3 = null;
tainted_2 = "hardcoded";
tainted_3 = tainted_2;
if ((Math.Sqrt(42) <= 42))
{
//No filtering (sanitization)
tainted_3 = tainted_2;
}
string query = "SELECT * FROM Articles WHERE id=" + 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());
}
}
/// <summary>
/// Update a database record from the supplied data transfer object.
/// </summary>
/// <param name="transaction">
/// The current transaction to the database.
/// </param>
/// <param name="partition">
/// The database partition (schema) where the requested resource will be updated.
/// </param>
/// <param name="referencerRule">
/// The referencerRule DTO that is to be updated.
/// </param>
/// <param name="container">
/// The container of the DTO to be updated.
/// </param>
/// <returns>
/// True if the concept was successfully updated.
/// </returns>
public virtual bool Update(NpgsqlTransaction transaction, string partition, CDP4Common.DTO.ReferencerRule referencerRule, CDP4Common.DTO.Thing container = null)
{
bool isHandled;
var valueTypeDictionaryAdditions = new Dictionary <string, string>();
var beforeUpdate = this.BeforeUpdate(transaction, partition, referencerRule, container, out isHandled, valueTypeDictionaryAdditions);
if (!isHandled)
{
beforeUpdate = beforeUpdate && base.Update(transaction, partition, referencerRule, container);
var valueTypeDictionaryContents = new Dictionary <string, string>
{
{ "MinReferenced", !this.IsDerived(referencerRule, "MinReferenced") ? referencerRule.MinReferenced.ToString() : string.Empty },
{ "MaxReferenced", !this.IsDerived(referencerRule, "MaxReferenced") ? referencerRule.MaxReferenced.ToString() : string.Empty },
}.Concat(valueTypeDictionaryAdditions).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
using (var command = new NpgsqlCommand())
{
var sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.AppendFormat("UPDATE \"{0}\".\"ReferencerRule\"", partition);
sqlBuilder.AppendFormat(" SET (\"ReferencingCategory\", \"ValueTypeDictionary\")");
sqlBuilder.AppendFormat(" = (:referencingCategory, \"ValueTypeDictionary\" || :valueTypeDictionary)");
sqlBuilder.AppendFormat(" WHERE \"Iid\" = :iid;");
command.Parameters.Add("iid", NpgsqlDbType.Uuid).Value = referencerRule.Iid;
command.Parameters.Add("referencingCategory", NpgsqlDbType.Uuid).Value = !this.IsDerived(referencerRule, "ReferencingCategory") ? referencerRule.ReferencingCategory : Utils.NullableValue(null);
command.Parameters.Add("valueTypeDictionary", NpgsqlDbType.Hstore).Value = valueTypeDictionaryContents;
command.CommandText = sqlBuilder.ToString();
command.Connection = transaction.Connection;
command.Transaction = transaction;
this.ExecuteAndLogCommand(command);
}
}
return(this.AfterUpdate(beforeUpdate, transaction, partition, referencerRule, container));
}
private void cmdOk_Click(object sender, EventArgs e)
{
if (txtAnneeDebut.Text != "" && txtAnneeFin.Text != "")
{
try
{
rptTauxCroissance rpt = new rptTauxCroissance();
NpgsqlCommand cmd = new NpgsqlCommand(Factory1.Instance.fonctionCalculTauxCroissance(obj, Convert.ToInt32(txtAnneeDebut.Text), Convert.ToInt32(txtAnneeFin.Text)), Factory1.Instance.connect());
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DtsTauxCroissance ds = new DtsTauxCroissance();
da.Fill(ds, "doc");
rpt.SetDataSource(ds.Tables["doc"]);
crvTauxCroissance.ReportSource = rpt;
crvTauxCroissance.Refresh();
da.Dispose();
ds.Dispose();
cmd.Dispose();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message, "Erreur de l'afichage du rapport", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("vous devez specifier l'année de début et celle de fin pout afficher le Taux de croissance", "Taux de croissance", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
if (string.IsNullOrEmpty(txtAnneeDebut.Text))
{
txtAnneeDebut.Focus();
}
else if (string.IsNullOrEmpty(txtAnneeFin.Text))
{
txtAnneeFin.Focus();
}
}
}
public void DeriveParametersInOnly()
{
using (var conn = OpenConnection())
{
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE OR REPLACE FUNCTION pg_temp.func(IN param1 INT, IN param2 INT) RETURNS int AS
'
BEGIN
RETURN param1 + param2;
END;
' LANGUAGE 'plpgsql';
");
var cmd = new NpgsqlCommand("pg_temp.func", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input));
cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = 4;
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(9));
}
}
/// <summary>
/// update row in the table
/// </summary>
/// <param name="businessObject">business object</param>
/// <returns>true for successfully updated</returns>
public bool Update(CProfile_system businessObject)
{
NpgsqlCommand sqlCommand = new NpgsqlCommand();
sqlCommand.CommandText = "public.sp_profile_system_Update";
sqlCommand.CommandType = CommandType.StoredProcedure;
// Use connection object of base class
sqlCommand.Connection = MainConnection;
try
{
sqlCommand.Parameters.AddWithValue("p_idprofile", businessObject.Idprofile);
sqlCommand.Parameters["p_idprofile"].NpgsqlDbType = NpgsqlDbType.Smallint;
sqlCommand.Parameters.AddWithValue("p_profile_name", businessObject.Profile_name);
sqlCommand.Parameters["p_profile_name"].NpgsqlDbType = NpgsqlDbType.Varchar;
MainConnection.Open();
if (Convert.ToInt32(sqlCommand.ExecuteScalar()) > 0)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
throw new Exception("CProfile_system::Update::Error occured.", ex);
}
finally
{
MainConnection.Close();
sqlCommand.Dispose();
}
}
public void PrimaryKeyFieldsMetadataSupport()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("DROP TABLE IF EXISTS DATA2 CASCADE");
conn.ExecuteNonQuery(@"CREATE TEMP TABLE DATA2 (
field_pk1 INT2 NOT NULL,
field_pk2 INT2 NOT NULL,
field_serial SERIAL,
CONSTRAINT data2_pkey PRIMARY KEY (field_pk1, field_pk2)
) WITH OIDS");
using (var command = new NpgsqlCommand("SELECT * FROM DATA2", conn))
using (var dr = command.ExecuteReader(CommandBehavior.KeyInfo))
{
dr.Read();
var keyColumns =
dr.GetSchemaTable().Rows.Cast<DataRow>().Where(r => (bool)r["IsKey"]).ToArray();
Assert.That(keyColumns, Has.Length.EqualTo(2));
Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk1"), Is.EqualTo(1));
Assert.That(keyColumns.Count(c => (string)c["ColumnName"] == "field_pk2"), Is.EqualTo(1));
}
}
}
public static ContCard CheckKendaraan(long contCardID)
{
ContCard contCard = null;
try
{
using (NpgsqlConnection npgsqlConnection = AppConfig.GetConnection())
{
if (npgsqlConnection.State == ConnectionState.Closed)
{
npgsqlConnection.Open();
}
string query = string.Format("SELECT {0} FROM {1} WHERE contcardid=@ContCardId ",
string.Format(DEFAULT_COLUMN, string.Empty),
DEFAULT_TABLE);
using (NpgsqlCommand npgsqlCommand = new NpgsqlCommand(query, npgsqlConnection))
{
npgsqlCommand.Parameters.AddWithValue("@ContCardId", contCardID);
using (NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader())
{
if (npgsqlDataReader.Read())
{
contCard = new ContCard();
MappingDataReaderToContCard(npgsqlDataReader, contCard);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(contCard);
}
/// <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>
/// <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;
}
/// <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;
}
//
// 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;
}
//
// 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.");
}
}
//
// 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); }
//
// 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;
}
//
// 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);
}
//
// 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();
}
}
//
// 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;
}
//
// 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();
}
}
//
// 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;
}
//
// 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); }
//
// 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;
}
