/// <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; } }
List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, NpgsqlConnection database) { const string query = "with source as " + "(select player.champion_id, player.won, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game_result, player where game_result.map = cast(:map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = player.team_id or game_result.team2_id = player.team_id) and player.summoner_id = :summoner_id) " + "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " + "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " + "as statistics " + "left outer join " + "(select champion_id, count(*) as wins from source where won = true group by champion_id) " + "as champion_wins " + "on statistics.champion_id = champion_wins.champion_id " + "left outer join " + "(select champion_id, count(*) as losses from source where won = false group by champion_id) " + "as champion_losses " + "on statistics.champion_id = champion_losses.champion_id;"; DatabaseCommand select = GetCommand(query, database); select.SetEnum("map", map.ToEnumString()); select.SetEnum("game_mode", gameMode.ToEnumString()); select.Set("summoner_id", summoner.Id); using (NpgsqlDataReader reader = select.ExecuteReader()) { List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>(); while (reader.Read()) { AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader); statistics.ChampionName = GetChampionName(statistics.ChampionId); output.Add(statistics); } output.Sort(); return output; } }
public void UseAllConnectionsInPool() { // As this method uses a lot of connections, clear all connections from all pools before starting. // This is needed in order to not reach the max connections allowed and start to raise errors. NpgsqlConnection.ClearAllPools(); try { var openedConnections = new List<NpgsqlConnection>(); // repeat test to exersize pool for (var i = 0; i < 10; ++i) { try { // 18 since base class opens two and the default pool size is 20 for (var j = 0; j < 18; ++j) { var connection = new NpgsqlConnection(ConnectionString); connection.Open(); openedConnections.Add(connection); } } finally { openedConnections.ForEach(delegate(NpgsqlConnection con) { con.Dispose(); }); openedConnections.Clear(); } } } finally { NpgsqlConnection.ClearAllPools(); } }
public void ResetOnClose() { var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public"); conn.Open(); ExecuteNonQuery("SET search_path=foo", conn); conn.Close(); conn.Open(); Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public")); conn.Close(); }
public void MinPoolSize() { var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=30;MaxPoolSize=30"); conn.Open(); conn.Close(); conn = new NpgsqlConnection(ConnectionString + ";MaxPoolSize=30;MinPoolSize=30"); conn.Open(); conn.Close(); }
public void MinPoolSizeEqualsMaxPoolSize() { using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 30, MaxPoolSize = 30 })) { conn.Open(); } }
public static void MinimumPgVersion(NpgsqlConnection conn, string minVersion, string ignoreText=null) { var min = new Version(minVersion); if (conn.PostgreSqlVersion < min) { var msg = $"Postgresql backend version {conn.PostgreSqlVersion} is less than the required {min}"; if (ignoreText != null) msg += ": " + ignoreText; Assert.Ignore(msg); } }
public void MinPoolSizeLargeThanMaxPoolSize() { using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 2, MaxPoolSize = 1 })) { Assert.That(() => conn.Open(), Throws.Exception.TypeOf<ArgumentException>()); } }
/// <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; } } } }
public void RejectSelfSignedCertificate(bool useSslStream) { var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { SslMode = SslMode.Require, UseSslStream = useSslStream }; using (var conn = new NpgsqlConnection(csb)) { // The following is necessary since a pooled connector may exist from a previous // SSL test NpgsqlConnection.ClearPool(conn); // TODO: Specific exception, align with SslStream Assert.That(() => conn.Open(), Throws.Exception); } }
/// <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 ResetOnClose() { var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public"); conn.Open(); ExecuteNonQuery("DROP SCHEMA IF EXISTS foo"); ExecuteNonQuery("CREATE SCHEMA foo"); try { ExecuteNonQuery("SET search_path=foo", conn); conn.Close(); conn.Open(); Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public")); conn.Close(); } finally { ExecuteNonQuery("DROP SCHEMA foo"); } }
public void MinPoolSize() { var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 2 }; using (var conn = new NpgsqlConnection(connString)) { connString = conn.Settings; // Shouldn't be necessary conn.Open(); conn.Close(); } var pool = PoolManager.Pools[connString]; Assert.That(pool.Idle, Has.Count.EqualTo(2)); // Now open 2 connections and make sure they're good using (var conn1 = OpenConnection(connString)) using (var conn2 = OpenConnection(connString)) { Assert.That(pool.Idle, Has.Count.Zero); Assert.That(conn1.ExecuteScalar("SELECT 1"), Is.EqualTo(1)); Assert.That(conn2.ExecuteScalar("SELECT 1"), Is.EqualTo(1)); } }
// // 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; }
// // 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.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; }
protected virtual Task SaveIndex(NpgsqlConnection conn, Guid id, TEntity data) => Task.CompletedTask;
public void MinPoolSizeLargeThanPoolSizeLimit() { var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=1025;"); conn.Open(); conn.Close(); }
public TaxonSqlData[] Get() { const string sql = @"SELECT s.code AS TaxonId, ch.predecessor AS ParentScientificNameId, s.title AS ScientificName, s.code AS ScientificNameId, s.title AS PopularName FROM data.codeshierarchy ch, data.codes p, data.codes s WHERE ch.successor = s.code and ch.predecessor = p.code and p.level = s.level - 1 and s.code like 'AR_%' and p.code like 'AR_%'" ; using (var conn = new NpgsqlConnection(_connectionString)) { conn.Open(); var taxons = conn.Query <TaxonSqlData>(sql).ToArray(); var taxonDict = taxons.ToDictionary(t => t.TaxonId.Remove(0, 3), t => t); var eastNorths = conn.Query <EastNorthDto>("SELECT t.code AS TaxonId, st_X(st_centroid(st_transform(o.geography::geometry, 25833)))::integer as East, st_Y(st_centroid(st_transform(o.geography::geometry, 25833)))::integer as North FROM data.codes_geometry t JOIN data.geometry o ON o.id = t.geometry_id where t.code like 'AR_%'"); foreach (var eastNorth in eastNorths) { if (!taxonDict.ContainsKey(eastNorth.TaxonId.Remove(0, 3))) { taxonDict[eastNorth.TaxonId.Remove(0, 3)] = new TaxonSqlData(); } var set = taxonDict[eastNorth.TaxonId.Remove(0, 3)].EastNorths; var tuple = (eastNorth.East, eastNorth.North); set.Add(tuple); } var geographicalAreaConnectionSql = @"select cg_art.code as TaxonId, cg_omr.code as Number from data.geometry art, data.geometry omr, data.codes_geometry cg_art, data.codes_geometry cg_omr where cg_art.code like 'AR-%' AND ( cg_omr.code like 'VV-%' OR cg_omr.code like 'AO-%') AND art.id = cg_art.geometry_id AND omr.id = cg_omr.geometry_id AND st_intersects(art.geography, omr.geography)"; var geoAreaConnections = conn.Query <TaxonGeoAreaConnectionDto>(geographicalAreaConnectionSql, commandTimeout: 24 * 60 * 60); foreach (var geoAreaConnection in geoAreaConnections) { var taxon = taxonDict[geoAreaConnection.TaxonId.Remove(0, 3)]; if (geoAreaConnection.Number.StartsWith("AO-")) { taxon.Municipalities.Add(int.Parse(geoAreaConnection.Number.Remove(0, 3).Replace("-", ""))); } else { taxon.ConservationAreas.Add(int.Parse(geoAreaConnection.Number.Remove(0, 3))); } } return(taxons); } }
/// <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; }
public FeatureDataTable QueryFeatures(Geometry geom, double distance) { //Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>(); using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString)) { string strGeom = "GeomFromText('" + geom.AsText() + "')"; if (SRID > 0) { strGeom = "setSRID(" + strGeom + "," + SRID + ")"; } string strSQL = "SELECT * , AsBinary(\"" + GeometryColumn + "\") As sharpmap_tempgeometry FROM " + QualifiedTable + " WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) { strSQL += DefinitionQuery + " AND "; } strSQL += "\"" + GeometryColumn + "\" && " + "buffer(" + strGeom + "," + distance.ToString(Map.NumberFormatEnUs) + ")"; strSQL += " AND distance(\"" + GeometryColumn + "\", " + strGeom + ")<" + distance.ToString(Map.NumberFormatEnUs); using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSQL, conn)) { DataSet ds = new DataSet(); conn.Open(); adapter.Fill(ds); conn.Close(); if (ds.Tables.Count > 0) { FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]); foreach (DataColumn col in ds.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression); } } foreach (DataRow dr in ds.Tables[0].Rows) { FeatureDataRow fdr = fdt.NewRow(); foreach (DataColumn col in ds.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdr[col.ColumnName] = dr[col]; } } fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); fdt.AddRow(fdr); } return(fdt); } else { return(null); } } } }
/// <summary> /// Returns all features with the view box /// </summary> /// <param name="bbox">view box</param> /// <param name="ds">FeatureDataSet to fill data into</param> public void ExecuteIntersectionQuery(BoundingBox bbox, FeatureDataSet ds) { //List<Geometry> features = new List<Geometry>(); using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString)) { string strBbox = "box2d('BOX3D(" + bbox.Min.X.ToString(Map.NumberFormatEnUs) + " " + bbox.Min.Y.ToString(Map.NumberFormatEnUs) + "," + bbox.Max.X.ToString(Map.NumberFormatEnUs) + " " + bbox.Max.Y.ToString(Map.NumberFormatEnUs) + ")'::box3d)"; if (SRID > 0) { strBbox = "setSRID(" + strBbox + "," + SRID.ToString(Map.NumberFormatEnUs) + ")"; } string strSQL = "SELECT *, AsBinary(\"" + GeometryColumn + "\") AS sharpmap_tempgeometry "; strSQL += "FROM " + QualifiedTable + " WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) { strSQL += DefinitionQuery + " AND "; } if (_SupportSTIntersects) { strSQL += "ST_Intersects(\"" + GeometryColumn + "\"," + strBbox + ")"; } else { strSQL += "\"" + GeometryColumn + "\" && " + strBbox; } #if DEBUG Debug.WriteLine(string.Format("{0}\n{1}\n", "ExecuteIntersectionQuery: executing sql:", strSQL)); #endif using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(strSQL, conn)) { conn.Open(); DataSet ds2 = new DataSet(); adapter.Fill(ds2); conn.Close(); if (ds2.Tables.Count > 0) { FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]); foreach (DataColumn col in ds2.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression); } } foreach (DataRow dr in ds2.Tables[0].Rows) { FeatureDataRow fdr = fdt.NewRow(); foreach (DataColumn col in ds2.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdr[col.ColumnName] = dr[col]; } } fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); fdt.AddRow(fdr); } ds.Tables.Add(fdt); } } } }
public CidadeRepositorio(NpgsqlConnection context, NpgsqlTransaction transaction) { this._context = context; this._transaction = transaction; }
private void ReadPrijava(string brojPrijave, string kupac) { StaticFilters = new List <string>(); StaticFilters.Add("(broj like concat(@broj,'%') or @broj is null)"); StaticFilters.Add("(lower(kupac_ime) like concat('%',lower(@kupac_ime),'%') or (lower(predmet) like concat('%',lower(@kupac_ime),'%')) or @kupac_ime is null)"); List <string> filters = new List <string>(); filters = filters.Concat(StaticFilters).ToList(); if (DynamicFilters != null) { filters = filters.Concat(DynamicFilters).ToList(); } using (var conn = new NpgsqlConnection(PersistanceManager.GetConnectionString())) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; dgvPrijave.DataSource = null; dgvPrijave.AutoGenerateColumns = false; dgvPrijave.Columns.Clear(); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "R.broj", DataPropertyName = "broj", Width = 80 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Datum prijema", DataPropertyName = "datum", Width = 100, DefaultCellStyle = new DataGridViewCellStyle() { Format = "dd.MM.yyyy." } }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Šifra kupca", DataPropertyName = "kupac_sifra", Width = 50, Visible = false }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Kupac", DataPropertyName = "kupac_ime", Width = 180 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Adresa", DataPropertyName = "kupac_adresa", Width = 180 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Telefon", DataPropertyName = "kupac_telefon", Width = 130 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "E-mail", DataPropertyName = "kupac_email", Visible = false }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Predmet", DataPropertyName = "predmet", Width = 250 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Model", DataPropertyName = "model", Visible = true }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "SB", DataPropertyName = "serijski_broj", Visible = false }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Dodatna oprema", DataPropertyName = "dodatna_oprema", Visible = false }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Dobavljač", DataPropertyName = "dobavljac_sifra", Width = 80 }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Email dobavljaču", DataPropertyName = "poslat_mejl_dobavljacu", Width = 100, DefaultCellStyle = new DataGridViewCellStyle() { Format = "dd.MM.yyyy." } }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Datum vraćanja", DataPropertyName = "datum_vracanja", Width = 100, DefaultCellStyle = new DataGridViewCellStyle() { Format = "dd.MM.yyyy." } }); dgvPrijave.Columns.Add(new DataGridViewTextBoxColumn() { Name = "Završeno", DataPropertyName = "zavrseno", Width = 100, DefaultCellStyle = new DataGridViewCellStyle() { Format = "dd.MM.yyyy." } }); // Retrieve all rows cmd.Parameters.Clear(); Npgsql.NpgsqlParameter p1 = new NpgsqlParameter("@kupac_ime", DbType.String); cmd.Parameters.Add(p1); Npgsql.NpgsqlParameter p2 = new NpgsqlParameter("@broj", DbType.String); cmd.Parameters.Add(p2); if (kupac == "") { p1.Value = DBNull.Value; } else { p1.Value = kupac; } if (brojPrijave == "") { p2.Value = DBNull.Value; } else { p2.Value = brojPrijave; } cmd.CommandText = @"SELECT broj, broj_naloga, datum, kupac_sifra, kupac_ime, kupac_adresa, kupac_telefon, kupac_email, model, serijski_broj, dodatna_oprema, predmet, napomena_servisera, serviser, serviser_primio, zavrseno, dobavljac_sifra, dobavljac, datum_vracanja, poslat_mejl_dobavljacu, garantni_rok, broj_garantnog_lista, broj_racuna,instalacija_os,instalacija_office, instalacija_ostalo,instalacija FROM prijava" ; if (filters.Count > 0) { cmd.CommandText += " WHERE "; foreach (string f in filters) { cmd.CommandText += f + " AND "; } cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 4); } cmd.CommandText += " order by datum desc"; using (var reader = cmd.ExecuteReader()) { DataTable dt = new DataTable(); dt.Load(reader); dgvPrijave.DataSource = dt; dgvPrijave.Refresh(); } } } }
public object Execute(Expression expression) { var translator = new KasbahNpgsqlQueryTranslator(); var res = translator.Translate(expression); var sql = new StringBuilder(); sql.Append(@" select n.id as Id, n.parent_id as Parent, n.alias as Alias, n.display_name as DisplayName, n.type as Type, n.published_version_id as PublishedVersion, n.created_at as Created, n.modified_at as Modified, n.id_taxonomy as Ids, n.alias_taxonomy as Aliases, nc.content as Content from node n inner join node_content nc on nc.id = n.id and n.published_version_id = nc.version where "); var parameters = new Dapper.DynamicParameters(res.Parameters); var types = _typeRegistry.GetTypesThatImplement(_targetType); if (types.Any()) { var typeWhere = types.Select((ent, index) => new { Alias = ent.Alias, Index = index }) .Select(ent => new { Statement = $"n.type = @t{ent.Index}", Key = $"t{ent.Index}", Value = ent.Alias }); sql.Append($"({string.Join(" or ", typeWhere.Select(ent => ent.Statement))}) and "); // TODO: this still isn't the greatest foreach (var type in typeWhere) { parameters.Add(type.Key, type.Value); } } sql.Append(string.IsNullOrEmpty(res.WhereClause) ? "1=1" : res.WhereClause); if (res.Take.HasValue) { sql.Append(" limit "); sql.Append(res.Take); } if (res.Skip.HasValue) { sql.Append(" offset "); sql.Append(res.Skip); } sql.Append(';'); _log.LogDebug($"{nameof(Execute)}: {sql}"); using (var connection = new NpgsqlConnection(_settings.ConnectionString)) { var rawData = connection.Query <Node, string, QueryResult>( sql: sql.ToString(), map: (node, json) => new QueryResult { Node = node, Json = json }, param: parameters, splitOn: "Content"); var mappedData = rawData.Select(ent => { var dict = JsonConvert.DeserializeObject <IDictionary <string, object> >(ent.Json); return(_typeMapper.MapTypeAsync(dict, ent.Node.Type, ent.Node, ent.Node.PublishedVersion).Result); }); // TODO: this isn't great var ret = Activator.CreateInstance(typeof(List <>).MakeGenericType(_targetType)) as IList; foreach (var ent in mappedData) { ret.Add(ent); } return(ret); } }
public override IDictionary <string, string> GetMetaDatabases() { IDictionary <string, string> TablesList = new Dictionary <string, string>(); try { conn.Open(); DataTable DBs = conn.GetSchema("Databases"); DbConnection dbConn; foreach (DataRow db in DBs.Rows) { if (initDB.Equals(db[0])) { var dbConnString = "Server=" + serverName + ";Database=" + db[0] + ";User Id=" + user + ";Password="******";"; dbConn = new NpgsqlConnection(dbConnString); dbConn.Open(); IEnumerable <DataRow> Tables = dbConn.GetSchema("Tables").Select().Where(e => e[2].ToString().ToLower().Equals("liodatabases")); command = ""; foreach (DataRow table in Tables) { if (command != "") { command += " UNION "; } command += @"SELECT DBSYN_S, NAME_S FROM " + table[1] + "." + table[2] + @" WHERE DBNAME_S = 'MetaDB'"; } if (command != "") { MetaDBs = new DataTable(); sqlCommand = new NpgsqlCommand(command, (NpgsqlConnection)dbConn); sqlAdapter = new NpgsqlDataAdapter((NpgsqlCommand)sqlCommand); sqlAdapter.Fill(MetaDBs); foreach (DataRow row in MetaDBs.Rows) { TablesList.Add(row[0].ToString(), row[1].ToString()); } } } } } catch (Exception e) { MessageBox.Show(e.Message); } finally { if (null != conn) { conn.Close(); conn.Dispose(); } } return(TablesList); }
static void Main(string[] args) { var version = Assembly.GetEntryAssembly().GetName().Version; Console.WriteLine($"tool: pg2b3dm {version}"); Parser.Default.ParseArguments <Options>(args).WithParsed(o => { o.User = string.IsNullOrEmpty(o.User) ? Environment.UserName : o.User; o.Database = string.IsNullOrEmpty(o.Database) ? Environment.UserName : o.Database; var connectionString = $"Host={o.Host};Username={o.User};Database={o.Database};Port={o.Port}"; var istrusted = TrustedConnectionChecker.HasTrustedConnection(connectionString); if (!istrusted) { Console.Write($"Password for user {o.User}: "); password = PasswordAsker.GetPassword(); connectionString += $";password={password}"; Console.WriteLine(); } Console.WriteLine($"Start processing...."); var stopWatch = new Stopwatch(); stopWatch.Start(); // Do in try catch as may not have acces rights. string output = o.Output; string outputTiles = output + "/tiles"; if (!Directory.Exists(output)) { Directory.CreateDirectory(output); } if (!Directory.Exists(outputTiles)) { Directory.CreateDirectory(outputTiles); } var geometryTable = o.GeometryTable; var geometryColumn = o.GeometryColumn; Console.WriteLine("Calculating bounding boxes..."); var conn = new NpgsqlConnection(connectionString); conn.Open(); var bbox3d = BoundingBoxRepository.GetBoundingBox3D(conn, geometryTable, geometryColumn); var translation = bbox3d.GetCenter().ToVector(); var zupBoxes = GetZupBoxes(conn, geometryTable, geometryColumn, translation); var tree = TileCutter.ConstructTree(zupBoxes); Console.WriteLine("Writing tileset.json..."); WiteTilesetJson(translation, tree, o.Output); Console.WriteLine($"Writing {Counter.Instance.Count} tiles..."); WriteTiles(conn, geometryTable, geometryColumn, translation, tree, o.Output, o.RoofColorColumn); conn.Close(); stopWatch.Stop(); Console.WriteLine(); Console.WriteLine($"Elapsed: {stopWatch.ElapsedMilliseconds / 1000} seconds"); Console.WriteLine("Program finished."); }); }
// // 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; }
// // 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; }
public PostgresqlExclusiveDbConnection(string connectionString) { _connection = new NpgsqlConnection(connectionString); _connection.Open(); _transaction = _connection.BeginTransaction(IsolationLevel.Serializable); }
public DatabaseFunc() { this.connection = new NpgsqlConnection(); }
public TableCreatonBuilder(NpgsqlConnection connection, string owner, string schema, string name) : base(connection, schema, name) { Owner = owner; }
internal UnmappedEnumHandler(PostgresType pgType, INpgsqlNameTranslator nameTranslator, NpgsqlConnection connection) : base(pgType, connection) { _nameTranslator = nameTranslator; }
//[Timeout(5000)] public void BasicLifecycle() { using (var conn = new NpgsqlConnection(ConnectionString)) { bool eventOpen = false, eventClosed = false, eventBroken = false; conn.StateChange += (s, e) => { if (e.OriginalState == ConnectionState.Closed && e.CurrentState == ConnectionState.Open) { eventOpen = true; } if (e.OriginalState == ConnectionState.Open && e.CurrentState == ConnectionState.Closed) { eventClosed = true; } }; Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Closed)); // TODO: Connecting state? conn.Open(); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Ready)); Assert.That(eventOpen, Is.True); using (var cmd = new NpgsqlCommand("SELECT 1", conn)) using (var reader = cmd.ExecuteReader()) { reader.Read(); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open | ConnectionState.Fetching)); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Fetching)); } Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Ready)); using (var cmd = CreateSleepCommand(conn, 1)) { var exitFlag = false; var pollingTask = Task.Factory.StartNew(() => { while (true) { if (exitFlag) { Assert.Fail("Connection did not reach the Executing state"); } if (conn.Connector.State == ConnectorState.Executing) { Assert.That(conn.FullState & ConnectionState.Executing, Is.Not.EqualTo(0)); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); return; } } }); cmd.ExecuteNonQuery(); exitFlag = true; pollingTask.Wait(); } conn.Close(); Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Closed)); Assert.That(eventClosed, Is.True); conn.Open(); Assert.That(conn.State, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Open)); Assert.That(conn.Connector.State, Is.EqualTo(ConnectorState.Ready)); // Use another connection to kill our connection ExecuteNonQuery(string.Format("SELECT pg_terminate_backend({0})", conn.ProcessID)); conn.StateChange += (sender, args) => { if (args.CurrentState == ConnectionState.Closed) { eventBroken = true; } }; Assert.That(() => ExecuteScalar("SELECT 1", conn), Throws.Exception.TypeOf <IOException>()); Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed)); Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Broken)); Assert.That(eventBroken, Is.True); } }
public PostgresCategoriaDAO(NpgsqlConnection pConexion) { _conexion = pConexion; }
/// <inheritdoc /> public override NpgsqlTypeHandler <TimeSpan> Create(PostgresType postgresType, NpgsqlConnection conn) => conn.HasIntegerDateTimes // Check for the legacy floating point timestamps feature ? new IntervalHandler(postgresType) : throw new NotSupportedException($"The deprecated floating-point date/time format is not supported by {nameof(Npgsql)}.");
/// <summary> /// ユーザー情報更新 /// </summary> /// <param name="userInfoList">ユーザー情報</param> public void UpdateUserInfo(List <UserInfo> userInfoList) { //データベースに接続するための文字列を取得 string connString = new CommonDBAccess().MakeAccessString(); using (var conn = new NpgsqlConnection(connString)) { conn.Open(); using (var transaction = conn.BeginTransaction()) { try { foreach (var userInfo in userInfoList) { //パスワードを更新しない場合 if (userInfo.Pass == "") { string sql = @"update ユーザーマスタ set 名前 = :UserName, 権限id = :AuthorityID where ユーザーid = :UserID"; var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.Add(new NpgsqlParameter("UserID", NpgsqlTypes.NpgsqlDbType.Varchar)); cmd.Parameters["UserID"].Value = userInfo.UserID; cmd.Parameters.Add(new NpgsqlParameter("UserName", NpgsqlTypes.NpgsqlDbType.Varchar)); cmd.Parameters["UserName"].Value = userInfo.UserName; cmd.Parameters.Add(new NpgsqlParameter("AuthorityID", NpgsqlTypes.NpgsqlDbType.Numeric)); cmd.Parameters["AuthorityID"].Value = userInfo.AuthorityID; cmd.ExecuteNonQuery(); } //パスワードを更新する場合 else { string sql = @"update ユーザーマスタ set 名前 = :UserName, 権限id = :AuthorityID, パスワード = :Pass where ユーザーid = :UserID"; var cmd = new NpgsqlCommand(sql, conn); cmd.Parameters.Add(new NpgsqlParameter("UserID", NpgsqlTypes.NpgsqlDbType.Varchar)); cmd.Parameters["UserID"].Value = userInfo.UserID; cmd.Parameters.Add(new NpgsqlParameter("UserName", NpgsqlTypes.NpgsqlDbType.Text)); cmd.Parameters["UserName"].Value = userInfo.UserName; cmd.Parameters.Add(new NpgsqlParameter("AuthorityID", NpgsqlTypes.NpgsqlDbType.Numeric)); cmd.Parameters["AuthorityID"].Value = userInfo.AuthorityID; cmd.Parameters.Add(new NpgsqlParameter("Pass", NpgsqlTypes.NpgsqlDbType.Text)); cmd.Parameters["Pass"].Value = userInfo.Pass; cmd.ExecuteNonQuery(); } } transaction.Commit(); } catch (NpgsqlException) { transaction.Rollback(); throw; } } } }
public IndexCreationBuilder(NpgsqlConnection connection, string schema, string table) : base(connection, schema, table) { }
// // 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(); } }
/* each thread uses different offset to store phashes from database * after that they go into loop waiting for startScan to become true * then they compare phashes they stored to avatars to identify */ private static void ComparePhashes(int threadN, long offset, long limit) { var t = DateTime.Now; var phashes = new List <byte[]>(); // create separate instance of database connection for each thread var connectionClone = new NpgsqlConnection( @"server=localhost;port=5432;database=steamtracker;userid=postgres;password='******';timeout=0"); connectionClone.Open(); var reader = ReaderQuery($"SELECT phash FROM ph_distinct ORDER BY id ASC OFFSET {offset} LIMIT {limit}", connectionClone); Console.WriteLine($"Thread {threadN} query done, reading ({DateTime.Now - t})"); while (reader.Read()) { var phashStr = reader.GetString(0); phashes.Add(phashStr.ToByteArray()); } connectionClone.Close(); //Serializer.Save(byteDumpPath, phashes); //Console.WriteLine("Serialized offset " + offset); while (true) { ThreadStatus.SetAvailableStatus(threadN, true); while (!_startScan) { Thread.Sleep(100); } ThreadStatus.SetAvailableStatus(threadN, false); foreach (var phash in phashes) { foreach (var(marketAvatar, marketAvatarPhash) in _avatars) { if (!DifferenceBetweenBytes(phash, marketAvatarPhash, 10)) { continue; // working on different approach, for now this will improve performance } var match = ImagePhash.GetCrossCorrelation(phash, marketAvatarPhash); if (!(match > 0.99f)) { continue; } try { var phashString = phash.ByteArrayToString(); var sql = new NpgsqlCommand($"SELECT avatar FROM users WHERE phash = '{phashString}'", SteamTracker); var avatar = Query(sql).Rows[0]["avatar"].ToString(); Console.WriteLine($"Thread {threadN} identified {avatar}"); Query( $"UPDATE listings SET avatar=avatar || '{avatar}' || ';' WHERE marketAvatar='{marketAvatar}'", PizzaDb); // append avatar to avatar, delimiter ";" Query( $"UPDATE litelistings SET avatar=avatar || '{avatar}' || ';' WHERE marketAvatar='{marketAvatar}'", PizzaDb); // append avatar to avatar, delimiter ";" } catch { Console.WriteLine(phash.ByteArrayToString()); } } lock (CountLock) { _count++; } } } }
// // 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); }
public NpgsqlUtility(string connectionString) { _ConnectionString = connectionString; _Connection = this.OpenConnection(); }
// // 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); }
public List <LocationViewModel> SearchLocation_SP(int?LocationTypeCD, string Floor, int Skip) { var result = new List <LocationViewModel>(); using (NpgsqlConnection connection = new NpgsqlConnection(ConfigureSetting.GetConnectionString)) { connection.Open(); string sql = "sp_searchlocation"; using (NpgsqlCommand command = new NpgsqlCommand(sql, connection)) { command.CommandType = System.Data.CommandType.StoredProcedure; if (LocationTypeCD == null) { command.Parameters.AddWithValue("@locationtypecd", NpgsqlTypes.NpgsqlDbType.Integer, DBNull.Value); } else { command.Parameters.AddWithValue("@locationtypecd", NpgsqlTypes.NpgsqlDbType.Integer, LocationTypeCD); } if (Floor == null) { command.Parameters.AddWithValue("@floor", NpgsqlTypes.NpgsqlDbType.Varchar, DBNull.Value); } else { command.Parameters.AddWithValue("@floor", NpgsqlTypes.NpgsqlDbType.Varchar, Floor); } command.Parameters.AddWithValue("@skip", NpgsqlTypes.NpgsqlDbType.Integer, Skip); using (var reader = command.ExecuteReader()) { while (reader.Read()) { result.Add(new LocationViewModel() { Location = new LocationSearchResult() { ID = Convert.ToInt32(reader[0]), Code = reader[1].ToString(), Description = reader[2].ToString(), No = reader[3].ToString(), Name = reader[4].ToString(), Floor = reader[5].ToString(), LocationTypeCD = Convert.ToInt32(reader[6]), StatusCD = Convert.ToInt32(reader[7]), LocationTypeCDName = reader[8].ToString(), StatusCDName = reader[9].ToString(), TotalRow = Convert.ToInt32(reader[10]) } }); } } } connection.Close(); } return(result); }
// // 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; }
protected virtual async Task DeleteIndex(NpgsqlConnection conn, Guid id) { await conn.ExecuteAsync(@$ "delete from {relationName} where id = @Id", new { id }).ConfigureAwait(false); }
/// <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; }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; Process process = new Process(); process.StartInfo.FileName = "/bin/bash"; process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'"; process.StartInfo.UseShellExecute = false; process.StartInfo.RedirectStandardOutput = true; process.Start(); using (StreamReader reader = process.StandardOutput) { tainted_2 = reader.ReadToEnd(); process.WaitForExit(); process.Close(); } tainted_3 = tainted_2; while ((1 == 1)) { StringBuilder escape = new StringBuilder(); for (int i = 0; i < tainted_2.Length; ++i) { char current = tainted_2[i]; switch (current) { case '\\': escape.Append(@"\5c"); break; case '*': escape.Append(@"\2a"); break; case '(': escape.Append(@"\28"); break; case ')': escape.Append(@"\29"); break; case '\u0000': escape.Append(@"\00"); break; case '/': escape.Append(@"\2f"); break; default: escape.Append(current); break; } } tainted_3 = escape.ToString(); break; } //flaw 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> /// 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; }
public async void GetLastMessage(TLChannel channel, TelegramClient client, string name_chat, int offsetId) { int temp_offset = 0; int now_time = 0; if (offsetId == 0) { TimeSpan hours = new TimeSpan(-1, 0, 0); DateTime time = DateTime.Now.Add(hours); now_time = DateTimeToUnixTimeStamp(time); } while (true) { TLChannelMessages res = await client.SendRequestAsync <TLChannelMessages> (new TLRequestGetHistory() { Peer = new TLInputPeerChannel { ChannelId = channel.Id, AccessHash = channel.AccessHash.Value }, Limit = 100, AddOffset = temp_offset, OffsetId = 0 }); temp_offset += 100; var msgs = (TLChannelMessages)res; foreach (TLAbsMessage message in msgs.Messages) { var tlMessage = message as TLMessage; if (tlMessage == null) { continue; } if (offsetId == 0) { if (tlMessage.Date < now_time) { break; } } else { if (tlMessage.Id < offsetId) { break; } } int replyId = -1; if (tlMessage.ReplyToMsgId != null) { replyId = Convert.ToInt16(tlMessage.ReplyToMsgId); } string insert = "INSERT INTO public.message(messageid, fromid, chatname, processing_dttm, message, replyid)VALUES("; using (var conn = new NpgsqlConnection(connString)) { conn.Open(); // Insert some data using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = insert + tlMessage.Id.ToString() + "," + tlMessage.FromId + ",\'" + name_chat + "\'," + tlMessage.Date.ToString() + ",\'" + tlMessage.Message.Replace("'", "\"") + "\'," + replyId.ToString() + ");"; //cmd.Parameters.AddWithValue("p", "Hello world"); try { NpgsqlDataReader reader = cmd.ExecuteReader(); } catch (Exception e) { MessageBox.Show(e.Message); } conn.Close(); } } } } }
public void IntegratedSecurityWithoutUsername() { var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { IntegratedSecurity = true, Username = null, Password = null, }; using (var conn = new NpgsqlConnection(csb)) { try { conn.Open(); } catch (Exception e) { if (TestUtil.IsOnBuildServer) throw; Console.WriteLine(e); Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up"); } } }
public void DistributedTransactionRollback() { int field_serial1; int field_serial2; try { string connectionString = TheConnectionString + ";enlist=true"; using (TransactionScope scope = new TransactionScope()) { //UseStringParameterWithNoNpgsqlDbType using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { connection.Open(); NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:p0)", connection); command.Parameters.Add(new NpgsqlParameter("p0", "test")); Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Text); Assert.AreEqual(command.Parameters[0].DbType, DbType.String); Object result = command.ExecuteNonQuery(); Assert.AreEqual(1, result); field_serial1 = (int)new NpgsqlCommand("select max(field_serial) from tablea", connection).ExecuteScalar(); NpgsqlCommand command2 = new NpgsqlCommand("select field_text from tablea where field_serial = (select max(field_serial) from tablea)", connection); result = command2.ExecuteScalar(); Assert.AreEqual("test", result); } //UseIntegerParameterWithNoNpgsqlDbType using (NpgsqlConnection connection = new NpgsqlConnection(connectionString)) { connection.Open(); NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_int4) values (:p0)", connection); command.Parameters.Add(new NpgsqlParameter("p0", 5)); Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Integer); Assert.AreEqual(command.Parameters[0].DbType, DbType.Int32); Object result = command.ExecuteNonQuery(); Assert.AreEqual(1, result); field_serial2 = (int)new NpgsqlCommand("select max(field_serial) from tablea", connection).ExecuteScalar(); NpgsqlCommand command2 = new NpgsqlCommand("select field_int4 from tablea where field_serial = (select max(field_serial) from tablea)", connection); result = command2.ExecuteScalar(); Assert.AreEqual(5, result); // using new connection here... make sure we can't see previous results even though // it is the same distributed transaction NpgsqlCommand command3 = new NpgsqlCommand("select field_text from tablea where field_serial = :p0", connection); command3.Parameters.Add(new NpgsqlParameter("p0", field_serial1)); result = command3.ExecuteScalar(); // won't see value of "test" since that's // another connection Assert.AreEqual(null, result); } // not commiting here. } // This is an attempt to wait for the distributed transaction to rollback // not guaranteed to work, but should be good enough for testing purposes. System.Threading.Thread.Sleep(500); AssertNoTransactions(); // ensure they no longer exist since we rolled back AssertRowNotExist("field_text", field_serial1); AssertRowNotExist("field_int4", field_serial2); } catch (NotImplementedException) { } }
public void IntegratedSecurityWithUsername() { var username = Environment.GetEnvironmentVariable("USERNAME") ?? Environment.GetEnvironmentVariable("USER"); if (username == null) throw new Exception("Could find username"); var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { IntegratedSecurity = true, Username = username, Password = null, }; using (var conn = new NpgsqlConnection(csb)) { try { conn.Open(); } catch (Exception e) { if (TestUtil.IsOnBuildServer) throw; Console.WriteLine(e); Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up"); } } }
public static void Main(string[] args) { string tainted_0 = null; string tainted_1 = null; tainted_0 = args[1]; tainted_1 = tainted_0; StringBuilder escape = new StringBuilder(); for (int i = 0; i < tainted_0.Length; ++i) { char current = tainted_0[i]; switch (current) { case '\\': escape.Append(@"\5c"); break; case '*': escape.Append(@"\2a"); break; case '(': escape.Append(@"\28"); break; case ')': escape.Append(@"\29"); break; case '\u0000': escape.Append(@"\00"); break; case '/': escape.Append(@"\2f"); break; default: escape.Append(current); break; } } tainted_1 = escape.ToString(); //flaw string query = "SELECT * FROM '" + tainted_1 + "'"; string connectionString = "Server=localhost;port=1337;User Id=postgre_user;Password=postgre_password;Database=dbname"; NpgsqlConnection dbConnection = null; try{ dbConnection = new NpgsqlConnection(connectionString); dbConnection.Open(); NpgsqlCommand cmd = new NpgsqlCommand(query, dbConnection); NpgsqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Console.Write("{0}\n", dr[0]); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
public void MinPoolSizeLargeThanMaxPoolSize() { var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=2;MaxPoolSize=1"); conn.Open(); conn.Close(); }
private static void Parseresume(string url, string resume_id) { var htmlDoc = GetHtmlDocument(url); string gender = null; string age = null; string address = null; string job = null; string salary = null; string general_exp = null; JArray work_for = new JArray(); JValue work_interval = new JValue(""); ////div[@itemprop='worksFor']/div/div[1] JValue work_place = new JValue(""); ////div[@itemprop='worksFor']//div[@class='resume-block__sub-title'][1] JValue work_pos = new JValue(""); ////div[@itemprop='worksFor']//div[@class='resume-block__sub-title'][2] JArray skills = new JArray(); try { gender = htmlDoc.DocumentNode.SelectSingleNode("//span[@itemprop='gender']").InnerText.Trim(); } catch (Exception) { } try { age = htmlDoc.DocumentNode.SelectSingleNode("//span[@data-qa='resume-personal-age']").InnerText.Trim(); } catch (Exception) { } try { address = htmlDoc.DocumentNode.SelectSingleNode("//span[@data-qa='resume-personal-address']").InnerText .Trim(); } catch (Exception) { } try { job = htmlDoc.DocumentNode.SelectSingleNode("//span[@data-qa='resume-block-title-position']").InnerText .Trim(); } catch (Exception) { } try { salary = htmlDoc.DocumentNode.SelectSingleNode("//span[@data-qa='resume-block-salary']").InnerText .Trim(); } catch (Exception) { } try { general_exp = htmlDoc.DocumentNode.SelectSingleNode("//span[contains(.,'Опыт работы')]").InnerText .Trim(); } catch (Exception) { } try { var skill_nodes = htmlDoc.DocumentNode.SelectNodes("//div[@class='bloko-tag-list']//span/span"); foreach (var node in skill_nodes) { skills.Add(node.InnerText.Trim()); } } catch (Exception) { } try { var work_nodes = htmlDoc.DocumentNode.SelectNodes("//div[@itemprop='worksFor']"); foreach (var node in work_nodes) { work_interval.Value = node.SelectSingleNode("./div/div[1]").InnerText; work_place.Value = node.SelectSingleNode(".//div[@class='resume-block__sub-title'][1]").InnerText; work_pos.Value = node.SelectSingleNode(".//div[@class='resume-block__sub-title'][2]").InnerText; var description = new JObject(new JProperty("work_interval", work_interval), new JProperty("work_place", work_place), new JProperty("work_pos", work_pos)); work_for.Add(description); } } catch (Exception) { } using (var conn = new NpgsqlConnection( Configuration.ConnectionString) ) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO " + Configuration.SchemaName + "." + Configuration.TableName + " (resume_id, gender,age, address, job,salary,general_exp,work_for, skills , url,relevance_date) values (@resume_id, @gender,@age, @address, @job, @salary, @general_exp, @work_for, @skills , @url ,current_timestamp) ON CONFLICT (resume_id) DO UPDATE SET resume_id= @resume_id, gender = @gender,age = @age, address= @address, job= @job,salary = @salary,general_exp = @general_exp, work_for= @work_for,skills= @skills , url = @url,relevance_date = current_timestamp"; cmd.Parameters.AddWithValue("resume_id", (object)long.Parse(resume_id) ?? DBNull.Value); cmd.Parameters.AddWithValue("gender", (object)gender ?? DBNull.Value); cmd.Parameters.AddWithValue("age", (object)age ?? DBNull.Value); cmd.Parameters.AddWithValue("address", (object)address ?? DBNull.Value); cmd.Parameters.AddWithValue("job", (object)job ?? DBNull.Value); cmd.Parameters.AddWithValue("salary", (object)salary ?? DBNull.Value); cmd.Parameters.AddWithValue("general_exp", (object)general_exp ?? DBNull.Value); cmd.Parameters.AddWithValue("work_for", (object)work_for.ToString() ?? DBNull.Value); cmd.Parameters.AddWithValue("skills", (object)skills.ToString() ?? DBNull.Value); cmd.Parameters.AddWithValue("url", (object)url ?? DBNull.Value); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } } Console.WriteLine(" title :" + work_pos + " " + DateTime.Now + ">"); Console.ResetColor(); }
public static void Update(Product product, string nname, string nsource, string nproductLink, string nprice, string ngroup, string nphotoLink) { var updateQuery = ""; updateQuery += Check(product.name, nname, "name"); updateQuery += Check(product.source, nsource, "source"); updateQuery += Check(product.product_link, nproductLink, "product_link"); updateQuery += Check(product.price, nprice, "price"); updateQuery += Check(product.group, ngroup, "group"); updateQuery += Check(product.photo_link, nphotoLink, "photo_link"); if (updateQuery.Length > 0) { using (var con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["database"].ConnectionString)) { con.Open(); updateQuery = updateQuery.TrimEnd(',', ' '); // var sql = new NpgsqlCommand( "UPDATE dbo.\"Products\" SET " + updateQuery + " where name='" + product.name + "' and source='" + product.source + "';", con); sql.Parameters.AddWithValue("@name", nname); sql.Parameters.AddWithValue("@source", nsource); sql.Parameters.AddWithValue("@product_link", nproductLink); sql.Parameters.AddWithValue("@price", nprice); sql.Parameters.AddWithValue("@group", ngroup); sql.Parameters.AddWithValue("@photo_link", nphotoLink); var dataAdapter = new NpgsqlDataAdapter("select * FROM dbo.\"Products\"", con) { UpdateCommand = sql }; var dataSet = new DataSet(); dataAdapter.AcceptChangesDuringUpdate = true; dataAdapter.Fill(dataSet, "dbo.\"Products\""); Debug.WriteLine(sql.CommandText); var dataTable = dataSet.Tables["dbo.\"Products\""]; //dataTable.Rows.Find(row => row[1].ToString().Equals(product.name)); foreach (DataRow dataRow in dataTable.Rows) { if (!dataRow[1].ToString().Equals(product.name)) { continue; } if (ShouldUpdate(product.name, nname)) { dataRow[1] = nname; } if (ShouldUpdate(product.source, nsource)) { dataRow[2] = nname; } if (ShouldUpdate(product.product_link, nproductLink)) { dataRow[5] = nname; } if (ShouldUpdate(product.price, nprice)) { dataRow[3] = nname; } if (ShouldUpdate(product.@group, ngroup)) { dataRow[6] = nname; } if (ShouldUpdate(product.photo_link, nphotoLink)) { dataRow[4] = nname; } break; } dataAdapter.Update(dataTable); dataAdapter.Dispose(); } } }
public Docente(string nome, string cognome, string username, string password, DateTime dataNascita, Sede sede, List <Materia> materie, List <Classe> classi) : base(nome, cognome, username, password, dataNascita, sede) { this.materie = materie; this.classi = classi; sede.Docenti.Add(this); using (NpgsqlConnection conn = new NpgsqlConnection(Risorse.StringaDiConnessione)) { conn.Open(); using (NpgsqlCommand command = new NpgsqlCommand("Insert into Docenti (iddocente) values(@id)", conn)) { command.Parameters.AddWithValue("id", matricola); command.ExecuteNonQuery(); } /* * docentiTableAdapter adapter = new docentiTableAdapter(); * * adapter.Insert(matricola); * * CompetenzeTableAdapter compAdapter = new CompetenzeTableAdapter(); * insegnamentiTableAdapter insAdapter = new insegnamentiTableAdapter(); * */ using (NpgsqlCommand inserimentoClassi = new NpgsqlCommand("Insert into Insegnamenti (iddocente, idclasse) values (@doc, @clas)", conn)) { foreach (Classe classe in classi) { inserimentoClassi.Parameters.Clear(); inserimentoClassi.Parameters.AddWithValue("doc", matricola); inserimentoClassi.Parameters.AddWithValue("clas", classe.Id); inserimentoClassi.Prepare(); inserimentoClassi.ExecuteNonQuery(); classe.Docenti.Add(this); } } this.classi = classi; using (NpgsqlCommand competenze = new NpgsqlCommand("Insert into Competenze (iddocente, nomemateria) values (@doc, @mat)", conn)) { foreach (Materia materia in materie) { materia.Docenti.Add(this); competenze.Parameters.Clear(); competenze.Parameters.AddWithValue("doc", matricola); competenze.Parameters.AddWithValue("mat", materia.Nome); competenze.Prepare(); competenze.ExecuteNonQuery(); } } this.materie = materie; } }
public void ExceedConnectionsInPool() { var openedConnections = new List<NpgsqlConnection>(); try { // exceed default pool size of 20 for (var i = 0; i < 21; ++i) { var connection = new NpgsqlConnection(ConnectionString + ";Timeout=1"); connection.Open(); openedConnections.Add(connection); } } finally { openedConnections.ForEach(delegate(NpgsqlConnection con) { con.Dispose(); }); NpgsqlConnection.ClearAllPools(); } }
/// <summary> /// Creates an NpgsqlLargeObjectManager for this connection. The connection must be opened to perform remote operations. /// </summary> /// <param name="connection"></param> public NpgsqlLargeObjectManager(NpgsqlConnection connection) { _connection = connection; MaxTransferBlockSize = 4 * 1024 * 1024; // 4MB }