} // End Function GetConnectionString
public static void CreateUser(string userName, string password)
{
string role = "CREATE ROLE \"" + userName.Replace("\"", "\"\"") + "\" "
+ "WITH PASSWORD '" + password.Replace("'", "''") + "' "
+ "LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity'; ";
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
// https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
// https://stackoverflow.com/questions/8546759/how-to-check-if-a-postgres-user-exists
cmd.CommandText = "SELECT COUNT(*) FROM pg_roles WHERE rolname = '" + userName.Replace("'", "''") + "'; ";
// cmd.CommandText = "SELECT COUNT(*) FROM pg_catalog.pg_user WHERE usename = '" + userName.Replace("'", "''") + "'";
long countOfExistingUsersWithThisName = (long)cmd.ExecuteScalar();
if (countOfExistingUsersWithThisName == 0)
{
cmd.CommandText = role;
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub CreateUser
public async Task<IEnumerable<Subdivision>> Load100()
{
var result = new List<Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result.Add(subdivision);
}
}
return result;
}
public async Task<Subdivision> LoadById(int id)
{
Subdivision result = null;
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, population, ST_AsGeoJSON(boundry) as boundry from subdivisions where id = @id";
cmd.Parameters.AddWithValue("@id", id);
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var subdivision = new Subdivision() { Id = Convert.ToInt32(reader["id"]) };
if (reader["population"] != DBNull.Value)
{
subdivision.Population = Convert.ToInt32(reader["population"]);
}
if (reader["boundry"] != DBNull.Value)
{
subdivision.GeoJSON = reader["boundry"] as string;
}
result = subdivision;
}
}
return result;
}
public IEnumerable <CazCaritabil> findAll()
{
log.Info("getting all cases");
lock (this)
{
IDbConnection con = dbUtils.DBUtils.getConnection();
Npgsql.NpgsqlConnection con1 = (Npgsql.NpgsqlConnection)con;
Npgsql.NpgsqlConnection.ClearPool(con1);
List <CazCaritabil> cazuri = new List <CazCaritabil>();
using (var comm = con1.CreateCommand())
{
comm.CommandText = "select * from cazuri";
using (var dataR = comm.ExecuteReader())
{
while (dataR.Read())
{
CazCaritabil c = new CazCaritabil(dataR.GetString(1));
c.Id = dataR.GetInt32(0);
cazuri.Add(c);
}
}
}
log.InfoFormat("exiting findall with value", cazuri);
return(cazuri);
}
}
private static List <T> ReturnItemList <T>(Database db, string query) where T : ItemComparable, new()
{
List <T> list = new List <T>();
query = query.Replace(Constants.REPLACEMENT_SCHEMA, db.Schema);
using (var con = new Npgsql.NpgsqlConnection(db.ConnectionString()))
{
using (var command = con.CreateCommand())
{
command.CommandText = query;
con.Open();
DbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
T item = new T();
item.Fill(reader);
list.Add(item);
}
}
}
return(list);
}
public static void RunSql(string sql)
{
if (string.IsNullOrEmpty(sql))
{
return;
}
using (var connection = new Npgsql.NpgsqlConnection(CreateConfiguration().GetConnectionString(connectionName)))
{
try
{
var command = connection.CreateCommand();
command.CommandText = sql;
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
connection.Close();
}
}
}
public override IEtlOperationResult Execute(EtlPipelineContext context)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
con.Open();
using (var trx = con.BeginTransaction(_isolationLevel))
using (var cmd = con.CreateCommand())
{
cmd.CommandText = _commandText;
cmd.CommandType = CommandType.Text;
cmd.Transaction = trx;
foreach (var param in _parameters)
{
var p = cmd.CreateParameter();
p.ParameterName = param.Key;
p.Value = param.Value;
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
}
return(new EtlOperationResult(true));
}
public byte[] GetFluxoVeiculosRadaresCSV(string[] Radares, string DataConsulta)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("codigo;data_hora;tipo_veiculo;contagem;autuacoes;placas;qtde_faixas;lat;lon;bairro;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select c.localidade as codigo, c.data_e_hora as data_hora, c.tipo as tipo_veiculo, c.contagem, c.autuacoes, c.placas, b.qtde_fxs_f as qtde_faixas,\n" +
"b.velocidade, b.lat, b.lon, b.bairro\n" +
"from contagens c inner join \"BaseRadares\" b \n" +
"on b.codigo like concat('%', c.localidade, '%')\n" +
"where cast(c.data_e_hora as date) = '" + DataConsulta + "'\n" + //TODO: tirar a concatenacao pra evitar SQL injection
"and c.localidade in (";
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
comm.CommandText += "'" + Radares[i] + "'";
}
else
{
comm.CommandText += ",'" + Radares[i] + "'";
}
}
comm.CommandText += ");";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["codigo"].ToString() + ";";
linha += dr["data_hora"].ToString() + ";";
linha += dr["tipo_veiculo"].ToString() + ";";
linha += dr["contagem"].ToString() + ";";
linha += dr["autuacoes"].ToString() + ";";
linha += dr["placas"].ToString() + ";";
linha += dr["qtde_faixas"].ToString() + ";";
linha += dr["lat"].ToString().Replace(".", ",") + ";";
linha += dr["lon"].ToString().Replace(".", ",") + ";";
linha += dr["bairro"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
/// <summary>
/// Execute scalar query
/// </summary>
public static object QueryScalar(string sql)
{
using var db = new Npgsql.NpgsqlConnection(Globals.ConnectionString);
db.Open();
using var cmd = db.CreateCommand();
cmd.CommandText = sql;
return(cmd.ExecuteScalar());
}
public byte[] GetViagensCSV(string DataConsulta, string[] Radares)
{
string InClause = "";
InClause += "(";
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
InClause += "'" + Radares[i] + "'"; //TODO: tirar a concatenacao pra evitar SQL injection
}
else
{
InClause += ",'" + Radares[i] + "'";
}
}
InClause += ")";
StringBuilder sb = new StringBuilder();
sb.AppendLine("viagem_id;codigo_radar_inicio;data_hora_inicio;codigo_radar_final;data_hora_final;tipo_veiculo;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select id, inicio, data_inicio, final, data_final, tipo from viagens where cast(data_inicio as date) = '" + DataConsulta + "'\n" +
"and (inicio in " + InClause + " or final in " + InClause + ");";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["id"].ToString() + ";";
linha += dr["inicio"].ToString() + ";";
linha += dr["data_inicio"].ToString() + ";";
linha += dr["final"].ToString() + ";";
linha += dr["data_final"].ToString() + ";";
linha += dr["tipo"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
private async Task ExecuteNonQueryAsync(Npgsql.NpgsqlConnection connection, string command)
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandTimeout = CommandTimeout ?? cmd.CommandTimeout;
cmd.CommandText = command;
await cmd.ExecuteNonQueryAsync();
}
}
private void ExecuteNonQuery(Npgsql.NpgsqlConnection connection, string command)
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandTimeout = CommandTimeout ?? cmd.CommandTimeout;
cmd.CommandText = command;
cmd.ExecuteNonQuery();
}
}
public void Cleanup()
{
using (var conn = new Npgsql.NpgsqlConnection("User ID=transact_test;Password=qwerty12345;Host=localhost;Port=5432;Database=transact;Pooling = true;"))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "delete from tr.transactions;";
cmd.ExecuteNonQuery();
}
}
}
public List <ViagensDTO> GetViagens(string dataConsulta, string[] Radares)
{
List <ViagensDTO> lstRetorno = new List <ViagensDTO>();
string InClause = "";
InClause += "(";
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
InClause += "'" + Radares[i] + "'";
}
else
{
InClause += ",'" + Radares[i] + "'";
}
}
InClause += ")";
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select id, inicio, data_inicio, final, data_final, tipo from viagens\n" +
"where data_inicio between ('" + dataConsulta + " 00:00:00') and ('" + dataConsulta + " 23:59:59')\n" +
"and (inicio in " + InClause + " or final in " + InClause + ");";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
ViagensDTO ett = new ViagensDTO();
ett.ViagemId = Convert.ToInt32(dr["id"]);
ett.CodigoRadarInicio = Convert.ToInt32(dr["inicio"]);
ett.DataHoraInicio = Convert.ToDateTime(dr["data_inicio"]);
ett.CodigoRadarFinal = Convert.ToInt32(dr["final"]);
ett.DataHoraFinal = Convert.ToDateTime(dr["data_final"]);
ett.TipoVeiculo = Convert.ToInt32(dr["tipo"]);
lstRetorno.Add(ett);
}
}
return(lstRetorno);
}
public void CreateDatabase()
{
using (var connection = new Npgsql.NpgsqlConnection(ConnectionStrings.MasterDatabase))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "create database " + ConnectionStrings.TestDatabaseName;
cmd.ExecuteNonQuery();
}
}
}
public byte[] GetAcuraciaIdentificacaoRadaresCSV(string[] Radares, string DataConsulta)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("codigo;data_hora;tipo_veiculo;contagem;placas;acuracia_identificacao;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select localidade as codigo, data_e_hora, tipo, contagem, placas, cast(placas as decimal)/cast(contagem as decimal) as acuracia_identificacao\n" +
"from contagens where cast(data_e_hora as date) = '" + DataConsulta + "' and localidade in ("; //TODO: tirar a concatenacao pra evitar SQL injection
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
comm.CommandText += "'" + Radares[i] + "'"; //TODO: tirar a concatenacao pra evitar SQL injection
}
else
{
comm.CommandText += ",'" + Radares[i] + "'";
}
}
comm.CommandText += ");";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["codigo"].ToString() + ";";
linha += dr["data_e_hora"].ToString() + ";";
linha += dr["tipo"].ToString() + ";";
linha += dr["contagem"].ToString() + ";";
linha += dr["placas"].ToString() + ";";
linha += dr["acuracia_identificacao"].ToString().Replace(".", ",") + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
public async Task CreateTestTable(string tableName)
{
using (var connection = new Npgsql.NpgsqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = $"CREATE TABLE {tableName} (id serial, message text NOT NULL, created_at timestamp with time zone NOT NULL);";
await command.ExecuteNonQueryAsync();
}
}
}
public async Task DropTable(string tableName)
{
using (var connection = new Npgsql.NpgsqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = $"DROP TABLE {tableName};";
await command.ExecuteNonQueryAsync();
}
}
}
internal void delete(int id)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "delete from resources where id =@id";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("@id", id);
dbcmd.ExecuteNonQuery();
}
public byte[] GetRadaresLoteCSV(int lote)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("lote;codigo;endereco;sentido;referencia;tipo_equip;enquadrame;qtde_fxs_f;data_publi;velocidade;lat;lon;bairro;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select lote, codigo, endereco, sentido, referencia, tipo_equip, enquadrame, qtde_fxs_f, \n" +
"data_publi, velocidade, lat, lon, bairro from \"BaseRadares\" b where lote = '" + lote + "';"; //TODO: tirar a concatenacao pra evitar SQL injection
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = String.Empty;
linha += dr["lote"].ToString() + ";";
linha += dr["codigo"].ToString() + ";";
linha += dr["endereco"].ToString() + ";";
linha += dr["sentido"].ToString() + ";";
linha += dr["referencia"].ToString() + ";";
linha += dr["tipo_equip"].ToString() + ";";
linha += dr["enquadrame"].ToString() + ";";
try
{
linha += dr["qtde_fxs_f"].ToString() + ";";
}
catch { linha += ";"; }
linha += dr["data_publi"].ToString() + ";";
linha += dr["velocidade"].ToString() + ";";
linha += dr["lat"].ToString().Replace(".", ",") + ";";
linha += dr["lon"].ToString().Replace(".", ",") + ";";
linha += dr["bairro"].ToString() + ";";
sb.AppendLine(linha);
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
}
public byte[] GetInfracoesPorRadarCSV(string[] Radares, string DataConsulta)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("codigo_radar;data_hora;tipo_veiculo;contagem;autuacoes;placas;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select id,data_e_hora,localidade,tipo,contagem,autuacoes,placas from contagens where cast(data_e_hora as date) = '" + DataConsulta + "' and localidade in ("; //TODO: tirar a concatenacao pra evitar SQL injection
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
comm.CommandText += "'" + Radares[i] + "'";
}
else
{
comm.CommandText += ",'" + Radares[i] + "'";
}
}
comm.CommandText += ");";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["localidade"].ToString() + ";";
linha += dr["data_e_hora"].ToString() + ";";
linha += dr["tipo"].ToString() + ";";
linha += dr["contagem"].ToString() + ";";
linha += dr["autuacoes"].ToString() + ";";
linha += dr["placas"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
// ===== FUNCOES ========
public async Task LogRequest(string Usuario, string Endpoint, long TempoRequisicao)
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.Text;
comm.CommandText = "INSERT INTO LogRequest (Usuario, Endpoint, TempoRequisicao, DataRequest) VALUES ('" + Usuario + "', '" + Endpoint + "', " + TempoRequisicao.ToString() + ", now());";
conn.Open();
await comm.ExecuteNonQueryAsync();
}
}
public byte[] GetTipoVeiculosRadaresCSV(string[] Radares, string DataConsulta)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("codigo;data_hora;contagem;autuacoes;placas;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select localidade as codigo, data_e_hora as data_hora, sum(contagem) as contagem, sum(autuacoes) as autuacoes, sum(placas) as placas from contagens where localidade in (";
for (int i = 0; i < Radares.Count(); i++)
{
if (i == 0)
{
comm.CommandText += "'" + Radares[i] + "'"; //TODO: tirar a concatenacao pra evitar SQL injection
}
else
{
comm.CommandText += ",'" + Radares[i] + "'";
}
}
comm.CommandText += ") and cast(data_e_hora as date) = '" + DataConsulta + "' group by localidade, data_e_hora order by codigo, data_e_hora;";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["codigo"].ToString() + ";";
linha += dr["data_hora"].ToString() + ";";
linha += dr["contagem"].ToString() + ";";
linha += dr["autuacoes"].ToString() + ";";
linha += dr["placas"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
public async Task Save(string message, string tableName)
{
using (var connection = new Npgsql.NpgsqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = $"insert into {tableName} (message, created_at) values (@message, clock_timestamp());";
command.Parameters.Add("message", NpgsqlDbType.Text).Value = message;
await command.ExecuteNonQueryAsync();
}
}
}
public byte[] GetVelocidadeMediaTrajetoCSV(string DataConsulta, string[] Radares, string connString)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("codigo_radar_origem;periodo_dia;velocidade_media;codigo_radar_destino;");
string InClause = AssembleClause(Radares);
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select t.viagem_id,\n" +
"t.origem,\n" +
"case when date_part('hour', t.data_inicio) between 0 and 4 then 'madrugada'\n" +
"when date_part('hour', t.data_inicio) between 5 and 12 then 'manha'\n" +
"when date_part('hour', t.data_inicio) between 13 and 18 then 'tarde'\n" +
"when date_part('hour', t.data_inicio) between 18 and 23 then 'noite'\n" +
"end as periodo_dia,\n" +
"avg((t.v0 + t.v1) / 2 ) as velocidade_media,\n" +
"t.destino\n" +
"from trajetos t\n" +
"where cast(data_inicio as date) = '" + DataConsulta + "'\n" + //TODO: tirar a concatenacao pra evitar SQL injection
"and (t.origem in " + InClause + " or t.destino in " + InClause + ")\n" +
"group by t.viagem_id, t.origem, periodo_dia, t.destino;";
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["origem"].ToString() + ";";
linha += dr["periodo_dia"].ToString() + ";";
linha += dr["velocidade_media"].ToString().Replace(".", ",") + ";";
linha += dr["destino"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
} // End Sub CreateUser
public static void DropCreateDb(string dbName)
{
string sql = @"
CREATE DATABASE " + dbName + @"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
";
// sql = "CREATE ROLE \"" + System.Environment.UserName + "\" WITH PASSWORD 'TopSecret' LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION VALID UNTIL 'infinity';";
// System.Console.WriteLine(sql);
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(GetConnectionString()))
{
using (Npgsql.NpgsqlCommand cmd = con.CreateCommand())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
cmd.CommandText = "SELECT COUNT(*) FROM pg_database WHERE datname = '" + dbName.Replace("'", "''") + "'";
long countOfExistingDbsWithTHisName = (long)cmd.ExecuteScalar();
if (countOfExistingDbsWithTHisName > 0)
{
cmd.CommandText = @"SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '" + dbName.Replace("'", "''") + @"'
AND pid <> pg_backend_pid();";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP DATABASE " + dbName + ";";
cmd.ExecuteNonQuery();
} // End if (dbCount > 0)
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using cmd
} // End using con
} // End Sub DropCreateDb
public void TestMethod1()
{
using (Npgsql.NpgsqlConnection con = new Npgsql.NpgsqlConnection(ApplicationEnv.Env.ConnectionString))
{
con.Open();
using (Npgsql.NpgsqlCommand command = con.CreateCommand())
{
command.CommandText = "DELETE FROM tran_event_overview WHERE event_id='test1'";
command.ExecuteNonQuery();
}
con.Close();
}
DatabaseManager.Executor.InsertEventOverview("test1", "test1");
}
public IActionResult Index()
{
string connectionString = configuration["Data:LinuxPGConnection:ConnectionString"];
try
{
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connectionString))
{
Trace.WriteLine("NpgsqlConnection created");
conn.Open();
Trace.WriteLine("NpgsqlConnection opened");
using (Npgsql.NpgsqlCommand cmd = conn.CreateCommand())
{
Trace.WriteLine("NpgsqlCommand created");
cmd.CommandText = @"select ""Email"" from ""AspNetUsers"" Limit 1";
var email = cmd.ExecuteScalar();
Trace.WriteLine(email, "Email from npsql");
ViewData.Add("SimpleRequestEmail", email);
}
}
}
catch (Exception ex)
{
ViewData.Add("SimpleRequestException", ex.ToString());
}
Regex re = new Regex(@"Password\s*=\s*([^;]+)\s*;");
ViewData.Add("ConnectionString", re.Replace(connectionString, "Password=*******;") );
bool isConnected = false;
string additionalInfo = "";
try
{
int cnt = context.Users.Count();
isConnected = true;
additionalInfo = string.Format("Users count: {0}", cnt);
}
catch(Exception ex)
{
additionalInfo = "exception: " + ex.ToString();
}
ViewData.Add("ConnectionStatus", isConnected ? "Connected" : "Error");
ViewData.Add("ConnectionInfo", additionalInfo);
return View();
}
internal void update(int id, Resources value)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "update resources set \"name\"=:name,\"salery\"=:salery,\"age\"=:age,\"place\"=:place where id = " + id + "";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("name", value.name);
dbcmd.Parameters.AddWithValue("age", value.age);
dbcmd.Parameters.AddWithValue("salery", value.salery);
dbcmd.Parameters.AddWithValue("place", value.place);
dbcmd.ExecuteNonQuery();
}
internal void insert(Resources EmpData)
{
var sql_con = new Npgsql.NpgsqlConnection("Host=localhost;Username=postgres;Password=SmartWork@123;Database=postgres");
sql_con.Open();
Npgsql.NpgsqlCommand dbcmd = sql_con.CreateCommand();
var commandText = "insert into resources (id,name,salery,age,place) values(@id,@name,@age,@salery,@place)";
var cmd = new Npgsql.NpgsqlCommand(commandText, sql_con);
dbcmd.CommandText = commandText;
dbcmd.Parameters.AddWithValue("@id", EmpData.id);
dbcmd.Parameters.AddWithValue("@name", EmpData.name);
dbcmd.Parameters.AddWithValue("@age", EmpData.age);
dbcmd.Parameters.AddWithValue("@salery", EmpData.salery);
dbcmd.Parameters.AddWithValue("@place", EmpData.place);
dbcmd.ExecuteNonQuery();
}
public override void OnExecute(EtlPipelineContext context)
{
using (var con = new Npgsql.NpgsqlConnection(_connectionString))
{
con.Open();
using (var trx = con.BeginTransaction(_isolationLevel))
using (var cmd = con.CreateCommand())
{
cmd.CommandText = _commandText;
cmd.CommandType = CommandType.Text;
cmd.Transaction = trx;
foreach (var param in _parameters)
{
var p = cmd.CreateParameter();
p.ParameterName = param.Key;
p.Value = param.Value;
cmd.Parameters.Add(p);
}
using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (!reader.HasRows)
{
return;
}
while (reader.Read())
{
var row = new Row();
for (var i = 0; i < reader.FieldCount; i++)
{
row[reader.GetName(i)] = reader[i];
}
Emit(row);
}
}
}
}
TypedEmitter.SignalEnd();
}
public static void SendStatmentExecuter(Npgsql.NpgsqlConnection conn, List <string> colNames, List <string> rowValues, string tableName, List <object> CollumnsTypes)
{
try
{
int j;
var command = conn.CreateCommand();
var cmd = "";
//start sql string creation
cmd += @"INSERT INTO public." + tableName + " (";
//enter columns names
for (j = 1; j < colNames.Count; j++)//get column names
{
cmd += colNames[j - 1] + ", ";
}
//last intersection was removed for optimization
cmd += colNames[j - 1] + @") VALUES (";
//enter the values except the last one
for (j = 0; j < (colNames.Count - 1); j++)
{
//if its string we need to add single brackets else we don't need to add it
cmd += (((CellType)CollumnsTypes[j]) is CellType.Text) ?
"\'" + rowValues[j] + "\', "
: rowValues[j] + ", ";
Console.WriteLine(rowValues[j]);
}
Console.WriteLine(rowValues[j]);
//last tail value (difference in the end of the strings)
cmd += (((CellType)CollumnsTypes[j]) is CellType.Text) ?
"\'" + rowValues[j] + "\')"
: rowValues[j] + " )";
command.CommandText = cmd;
command.ExecuteNonQuery();
}
catch (Exception e)
{
if (e is ConstraintException || e is InvalidConstraintException || e is Npgsql.PostgresException)
{
MessageBox.Show("problem with input for table " + tableName);
}
}
}
public byte[] GetPerfilVelocidadesRadarCSV(int VelocidadeMin, int VelocidadeMax, string connString)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("lote;codigo;endereco;sentido;referencia;tipo_enquadramento;enquadramento;qtde_faixas;data_publicacao;velocidade;lat;lon;bairro;");
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(connString))
{
Npgsql.NpgsqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 420;
comm.CommandType = CommandType.Text;
comm.CommandText = "select lote, codigo, endereco, sentido, referencia, tipo_equip, enquadrame, qtde_fxs_f, \n" +
"data_publi, velocidade, lat, lon, bairro from \"BaseRadares\" b where velocidade_carro_moto between " + VelocidadeMin.ToString() + " and " + VelocidadeMax + ";"; //TODO: tirar a concatenacao pra evitar SQL injection
conn.Open();
Npgsql.NpgsqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
string linha = "";
linha += dr["lote"].ToString() + ";";
linha += dr["codigo"].ToString() + ";";
linha += dr["endereco"].ToString() + ";";
linha += dr["sentido"].ToString() + ";";
linha += dr["referencia"].ToString() + ";";
linha += dr["tipo_equip"].ToString() + ";";
linha += dr["enquadrame"].ToString() + ";";
linha += dr["qtde_fxs_f"].ToString() + ";";
linha += dr["data_publi"].ToString() + ";";
linha += dr["velocidade"].ToString() + ";";
linha += dr["lat"].ToString().Replace(".", ",") + ";";
linha += dr["lon"].ToString().Replace(".", ",") + ";";
linha += dr["bairro"].ToString() + ";";
sb.AppendLine(linha);
}
}
byte[] csv = Encoding.Unicode.GetBytes(sb.ToString());
return(csv);
}
public async Task<List<LcboStore>> GetLcboStores()
{
var result = new List<LcboStore>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = "select id, name, ST_AsGeoJSON(location) as location, beer_volume, wine_volume, spirits_volume from stores limit 100";
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
public async Task<List<LcboStore>> StoresInArea(string geoJson)
{
var result = new List<LcboStore>();
var query = $@"select s.id, s.name, s.beer_volume, s.wine_volume, s.spirits_volume
from stores s
where ST_Intersects(ST_GeomFromGeoJSON('{geoJson}'), s.location)";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
var store = new LcboStore(reader);
result.Add(store);
}
}
return result;
}
public async Task<List<Subdivision>> Density(AlcoholType alcoholType, EndOfDistribution distribution, int count)
{
var volumeField = alcoholTypeVolumeFieldMap[alcoholType];
var sortDirection = endOfDistributionMap[distribution];
var result = new List<Subdivision>();
var query = $@"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
beer_volume / 1000.0 as beer_volume,
wine_volume / 1000.0 as wine_volume,
spirits_volume / 1000.0 as spirits_volume,
{volumeField} / population as density
from subdivisions
where {volumeField} > 0
order by density {sortDirection}
limit {count}";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
public async Task<List<Subdivision>> Top10WineDensity()
{
var result = new List<Subdivision>();
var query = @"select id,
population,
name,
St_AsGeoJSON(st_centroid(boundry::geometry)) as centre,
wine_volume,
wine_volume / population as density
from subdivisions
where wine_volume > 0
order by density desc
limit 10";
var resultDict = new Dictionary<int, Subdivision>();
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
var subdiv = new Subdivision(reader);
result.Add(subdiv);
resultDict.Add(subdiv.Id, subdiv);
}
}
}
if (result.Any())
{
var stores = await GetStoresForSubdivisions(result.Select(s => s.Id), conn);
foreach (var store in stores)
{
resultDict[store.SubdivisionId].LcboStores.Add(store);
}
}
}
return result;
}
public async Task<Feature> BoundaryGeoJson(int subdivId)
{
var result = new Feature(new Point(new Position()));
var query = @"select ST_AsGeoJSON(boundry, 15, 4) as boundary
from subdivisions
where id = @subdivId";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
conn.Open();
cmd.Parameters.AddWithValue("@subdivId", subdivId);
var boundary = await cmd.ExecuteScalarAsync() as string;
if (boundary.Contains("\"type\":\"MultiPolygon\""))
{
var multipolygon = JsonConvert.DeserializeObject<MultiPolygon>(boundary);
result = new Feature(multipolygon);
}
else if (boundary.Contains("\"type\":\"Polygon\""))
{
var polygon = JsonConvert.DeserializeObject<Polygon>(boundary);
result = new Feature(polygon);
}
}
}
return result;
}
public async Task<List<Subdivision>> SubdivisionsAndVolumes()
{
var result = new List<Subdivision>();
var query = @"select id, population, name, ST_AsGeoJSON(boundry) as boundary, beer_volume, wine_volume, spirits_volume
from subdivisions
where province = 'Ontario'";
using (var conn = new Npgsql.NpgsqlConnection(connString))
{
var cmd = conn.CreateCommand();
cmd.CommandText = query;
conn.Open();
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
result.Add(new Subdivision(reader));
}
}
return result;
}