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