/// <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; }