public void OneTimeSetup()
{
var settings = new TestSettings();
var connection = new PostgresConnection(settings.PostgresSettings);
classUnderTest = new ToDoItemRepository(connection);
}
public static IEnumerable <object[]> AllConnectionsWithoutSQLite(string section) => new[] {
new object[] { (IConnectionManager)MariaDbConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)OracleConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)SqlConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)PostgresConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)MySqlConnection.ConnectionManager(section) },
};
protected void DataBags_Load(object sender, EventArgs e)
{
try
{
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
DataSet ds = new System.Data.DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select var_table , alive ,gather_data_time from existing_bags eb ", databaseConnection.connection());
da.Fill(ds, "DataBags");
DataBags_gridview.DataSource = ds.Tables[0];
DataBags_gridview.ReadOnly = true;
DataBags_gridview.AutoResizeColumns();
foreach (DataGridViewRow row in DataBags_gridview.Rows)
{
row.Height = (DataBags_gridview.ClientRectangle.Height - DataBags_gridview.ColumnHeadersHeight) / DataBags_gridview.Rows.Count;
}
//DataBags_gridview.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
//DataBags_gridview.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders;
databaseConnection.connection().Close();
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
internal void update_counter_database(int value)
{
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
var cmd = new NpgsqlCommand("update existing_bags set gather_data_time =:p1 where var_table =:p2 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = value;
cmd.Parameters[1].Value = table_name;
cmd.Prepare();
cmd.ExecuteNonQuery();
databaseConnection.connection().Close();
}
private void GatherData_save_to_databse(string value)
{
DateTime now = DateTime.Now;
string datetime = DateTime.Today.ToString();
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
try
{
var cmd = new NpgsqlCommand("update tools_data set last_changed_time =:p1, value =:p2, last_changed_date =:p3 where var =:p4 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p3", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p4", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = (now.TimeOfDay).ToString();
cmd.Parameters[1].Value = value;
cmd.Parameters[2].Value = datetime.ToString();
cmd.Parameters[3].Value = "MB5";
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
databaseConnection.connection().Close();
}
public void GatherData_save_to_databse()
{
DateTime now = DateTime.Now;
string datetime = DateTime.Today.ToString();
PostgresConnection databaseConnection; //= new PostgresConnection();
databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
// need to create lambda expression instead of foreach!
// foreach (var pair in opcVariables.getMap())
foreach (KeyValuePair <string, string> pair in this.getMap_plc())
{
var cmd = new NpgsqlCommand("update " + table_name + " set last_changed_time =:p1, value =:p2, last_changed_date =:p3 where var =:p4 ", databaseConnection.connection());
// cmd.Parameters.AddWithValue("p1", (now.TimeOfDay).ToString());
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p2", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p3", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters.Add(new NpgsqlParameter("p4", NpgsqlTypes.NpgsqlDbType.Text));
cmd.Parameters[0].Value = (now.TimeOfDay).ToString();
cmd.Parameters[1].Value = pair.Value;
cmd.Parameters[2].Value = datetime.ToString();
cmd.Parameters[3].Value = pair.Key;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
databaseConnection.connection().Close();
}
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
//Application.Run(new CreateConnection());
PostgresConnection.CreateConnectionString();
}
public static IEnumerable <object[]> AllSqlConnectionsWithValue(string section, int value) => new[] {
new object[] { (IConnectionManager)MariaDbConnection.ConnectionManager(section), value },
new object[] { (IConnectionManager)OracleConnection.ConnectionManager(section), value },
new object[] { (IConnectionManager)SqlConnection.ConnectionManager(section), value },
new object[] { (IConnectionManager)PostgresConnection.ConnectionManager(section), value },
new object[] { (IConnectionManager)MySqlConnection.ConnectionManager(section), value },
new object[] { (IConnectionManager)SQLiteConnection.ConnectionManager(section), value }
};
private void insert_example_values_to_table(string temporarty_bag_record, PostgresConnection con)
{
var m_createdb_cmd = new NpgsqlCommand(@"insert into " + temporarty_bag_record + @"
(var ,var_meaning ,created_on ,last_changed_date ,status ,var_path ,value ,last_changed_time )
values ('MB5','testing','2020-07-21','2020-07-21',true,'/Plc/','true','15:20:20')"
, con.connection());
m_createdb_cmd.ExecuteNonQuery();
}
public PostgresConnectionInfo(PostgresConnection postgresConfig)
{
connectionFactories = new Dictionary <string, OrmLiteConnectionFactory>();
Hostname = postgresConfig.Server.Server;
Port = postgresConfig.Server.Port;
DefaultDatabase = postgresConfig.Database.Name;
Username = postgresConfig.User.Username;
Password = postgresConfig.User.Password;
}
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
services.AddSingleton(Configuration);
services.AddSingleton <IHttpContextAccessor, HttpContextAccessor>();
services.AddTransient <IDapperContext>(p =>
{
var connection = PostgresConnection
.Init()
.SetarHost("localhost")
.SetarUsuario("postgres")
.SetarSenha("postgres")
.SetarNomeBaseDeDados("bdgomi")
.SetarPorta(5433)
.Criar();
if (!_iniciouBancoDados)
{
var dbContext = new PostgresDbContext(connection);
dbContext.CreateDatabaseAsync(Assembly.GetAssembly(typeof(Versao))).Wait();
dbContext.ExecutarScriptsDaVersao();
_iniciouBancoDados = true;
}
return(new PostgresDbContext(connection));
});
services.AddTransient <ICaixaRepository, CaixaRepository>();
services.AddTransient <ICentroCustoRepository, CentroCustoRepository>();
services.AddTransient <IContaRepository, ContaRepository>();
services.AddTransient <IEmpresaRepository, EmpresaRepository>();
services.AddTransient <IPagarRepository, PagarRepository>();
services.AddTransient <IPessoaRepository, PessoaRepository>();
services.AddTransient <IPlanoContaRepository, PlanoContaRepository>();
services.AddTransient <IReceberRepository, ReceberRepository>();
services.AddTransient <ITransferenciaRepository, TransferenciaRepository>();
services.AddTransient <IUsuarioRepository, UsuarioRepository>();
services.AddSingleton <ICaixaService, CaixaService>();
services.AddSingleton <ICentroCustoService, CentroCustoService>();
services.AddSingleton <IContaService, ContaService>();
services.AddSingleton <IDashboardService, DashboardService>();
services.AddSingleton <IEmpresaService, EmpresaService>();
services.AddSingleton <IPagarService, PagarService>();
services.AddSingleton <IPessoaService, PessoaService>();
services.AddSingleton <IPlanoContaService, PlanoContaService>();
services.AddSingleton <IReceberService, ReceberService>();
services.AddSingleton <ITransferenciaService, TransferenciaService>();
services.AddSingleton <IUsuarioService, UsuarioService>();
services.AddTransient <IPessoaService, PessoaService>();
}
static void ExecuteCommands(PostgresConnection connection, string sqlCommands)
{
foreach (var sqlCommand in sqlCommands.Split(new[] { "----" }, StringSplitOptions.RemoveEmptyEntries))
{
using (var command = connection.CreateCommand())
{
command.CommandText = sqlCommand;
Execute(command);
}
}
}
public static IConnection Create(string connectiontype)
{
switch (connectiontype)
{
case "PostgreSQL":
PostgresConnection postgresConnection = new PostgresConnection();
return(postgresConnection);
case "SQLite":
SQLiteConnection sqliteConnection = new SQLiteConnection();
return(sqliteConnection);
}
return(null);
}
public PostgresConnectionInfo(PostgresConnection postgresConfig)
{
connectionFactories = new Dictionary <string, OrmLiteConnectionFactory>();
Hostname = postgresConfig.Server.Server;
Port = postgresConfig.Server.Port;
DefaultDatabase = postgresConfig.Database.Name;
Username = postgresConfig.User.Username;
Password = postgresConfig.User.Password;
CommandTimeoutSeconds = postgresConfig.CommandTimeout;
TcpKeepAliveSeconds = postgresConfig.TcpKeepalive;
WriteBufferSizeBytes = postgresConfig.WriteBufferSize;
}
private void Enter_Click(object sender, EventArgs e)
{
server = server_box.Text;
user_id = user_name.Text;
password = password_box.Text;
database = db_name.Text;
if (!PostgresConnection.CreateConnectionString(server, user_id, password, database))
{
MessageBox.Show("Введены некорректные данные", "Ошибка подключения!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
Application.Restart();
}
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddCors(options =>
{
options.AddPolicy("MyPolicy", builder =>
{
builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader();
});
});
services.AddDbContext <ToDoContext>(options =>
{
var env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");
string connectionString;
if (env == "Development")
{
connectionString = Configuration.GetConnectionString("DefaultConnection");
}
else
{
connectionString = PostgresConnection.GetConnection();
}
options.UseNpgsql(connectionString);
});
services.AddTransient <IToDoService, ToDoService>();
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1.0", new OpenApiInfo
{
Version = "v1.0",
Title = "ToDoList API",
});
var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
if (File.Exists(xmlPath))
{
c.IncludeXmlComments(xmlPath);
}
});
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext <DataContext>(options =>
{
var connection = new PostgresConnection(Configuration.GetConnectionString("DefaultConnection"));
options.UseNpgsql(connection.GetConnectionString());
});
services.AddControllers().AddNewtonsoftJson(opts =>
{
opts.SerializerSettings.ReferenceLoopHandling =
Newtonsoft.Json.ReferenceLoopHandling.Ignore;
});
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.TokenValidationParameters = new TokenValidationParameters()
{
ValidateIssuerSigningKey = true,
IssuerSigningKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(Environment.GetEnvironmentVariable("TOKENKEY"))),
ValidateIssuer = false,
ValidateAudience = false
};
});
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo {
Title = "Seedling", Version = "v1"
});
});
services.AddHttpClient("gbif", c =>
{
c.BaseAddress = new Uri("https://api.gbif.org/v1/");
c.DefaultRequestHeaders.Add("Accept", "application/json, text/plain, */*");
});
services.AddScoped <IUnitOfWork, UnitOfWork>();
services.AddScoped <IBiodiversityResource, BiodiversityResource>();
services.AddAutoMapper(typeof(PlantRepository).Assembly, typeof(UserRepository).Assembly);
}
async Task CreateIndex(ISagaData sagaData, PostgresConnection connection, IEnumerable <KeyValuePair <string, string> > propertiesToIndex)
{
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value ?? "",
PropertyNameParameter = $"@n{i}",
PropertyValueParameter = $"@v{i}"
})
.ToList();
// lastly, generate new index
using (var command = connection.CreateCommand())
{
// generate batch insert with SQL for each entry in the index
var inserts = parameters
.Select(a =>
$@"
INSERT
INTO ""{_indexTableName}"" (""saga_type"", ""key"", ""value"", ""saga_id"")
VALUES (@saga_type, {a.PropertyNameParameter}, {a.PropertyValueParameter}, @saga_id)
");
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter.PropertyNameParameter, NpgsqlDbType.Text).Value = parameter.PropertyName;
command.Parameters.Add(parameter.PropertyValueParameter, NpgsqlDbType.Text).Value = parameter.PropertyValue;
}
command.Parameters.Add("saga_type", NpgsqlDbType.Text).Value = sagaTypeName;
command.Parameters.Add("saga_id", NpgsqlDbType.Uuid).Value = sagaData.Id;
await command.ExecuteNonQueryAsync();
}
}
private void create_additional_table(string temporarty_bag_record, PostgresConnection con)
{
/*
* CAREFUL, SQL INJECTION RIGHT BELOW! CORRECT IT!
*/
var m_createdb_cmd = new NpgsqlCommand(@"create table if not exists " + temporarty_bag_record + @"
(
id serial primary key,
last_changed_time varchar(255) not null,
status varchar(255) not null,
var varchar(255) not null,
last_changed_date varchar(255) not null,
var_meaning varchar(255) not null,
value varchar(255) not null,
var_path varchar(255) not null,
created_on varchar(255) not null
)", con.connection());
m_createdb_cmd.ExecuteNonQuery();
}
private void AddPlcVariablesToMap(PostgresConnection con)
{
NpgsqlCommand cmd = new NpgsqlCommand("SELECT var_path , var FROM " + table_name + " pdv ", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
map_from_database.Add(rdr.GetString(1), rdr.GetString(0));
}
rdr.Close();
foreach (var pair in this.map_from_database)
{
string dup = pair.Value;
string dupa = pair.Key;
nodesToRead.Add(new NodeId(pair.Value + pair.Key, 2).ToString());
}
}
private bool records_exists_in_table(infinite_loops infinite_loops)
{
PostgresConnection con = new PostgresConnection();
con.connection().Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT CASE WHEN EXISTS (SELECT * FROM " + infinite_loops.getTable_name() + " LIMIT 1) THEN 1 ELSE 0 END", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
int value = -1;
while (rdr.Read())
{
value = rdr.GetInt16(0);
}
rdr.Close();
con.connection().Close();
return(value == 1 ? true : false);
}
private void showElementsOfBag(string table_name)
{
try
{
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
DataSet ds = new System.Data.DataSet();
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select id,last_changed_time ,status ,var ,var_meaning , value ,var_path ,created_on from " + table_name
+ " pdv order by id ", databaseConnection.connection());
da.Fill(ds, "DataBags");
valuesInBag_gridview.DataSource = ds.Tables[0];
valuesInBag_gridview.ReadOnly = true;
valuesInBag_gridview.AutoResizeColumns();
valuesInBag_gridview.ScrollBars = ScrollBars.Both;
valuesInBag_gridview.PerformLayout();
foreach (DataGridViewRow row in valuesInBag_gridview.Rows)
{
row.Height = (valuesInBag_gridview.ClientRectangle.Height - valuesInBag_gridview.ColumnHeadersHeight) / valuesInBag_gridview.Rows.Count;
}
databaseConnection.connection().Close();
}
catch (Exception ex)
{
MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public OpcVairables(string tablename)
{
table_name = tablename;
// empty, previously save to database, migrated to another function
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
AddPlcVariablesToMap(databaseConnection);
//finall result of collected items gathered by OPC_server
results = OpcUastartup.Instance.get_m_server().ReadValues(nodesToRead);
int index = 0;
foreach (var pair in this.map_from_database)
{
map_plc.Add(pair.Key, results[index]);
index++;
}
}
private void button1_Click_2(object sender, EventArgs e)
{
List <string> list_of_var_in_database = new List <string>();
// empty, previously save to database, migrated to another function
PostgresConnection databaseConnection = new PostgresConnection();
databaseConnection.connection().Open();
// NpgsqlCommand cmd = new NpgsqlCommand("select plc_var from plc_data_variables order by id_var", databaseConnection.connection());
// NpgsqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
// SqlDataAdapter da = new SqlDataAdapter("select plc_var from plc_data_variables order by id_var", databaseConnection.connection());
// NpgsqlDataAdapter da = new NpgsqlDataAdapter("select concat(plc_var_path , plc_var) as var from plc_data_variables order by id_var ", databaseConnection.connection());
NpgsqlDataAdapter da = new NpgsqlDataAdapter("select id_var , concat(plc_var_path , plc_var) as var, created_on da from plc_data_variables order by id_var", databaseConnection.connection());
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.ReadOnly = true;
DataGridViewColumn column = dataGridView1.Columns[0];
column.Width = 32;
DataGridViewColumn column2 = dataGridView1.Columns[1];
column2.Width = 110;
DataGridViewColumn column3 = dataGridView1.Columns[2];
column3.Width = 110;
}
private void createBag()
{
existing_bags = new Dictionary <string, int>();
string varu = null;
PostgresConnection con = new PostgresConnection();
con.connection().Open();
// NpgsqlCommand cmd = new NpgsqlCommand("select var_table , gather_data_time from existing_bags eb order by id ", con.connection());
// testting purpose
NpgsqlCommand cmd = new NpgsqlCommand("select var_table , gather_data_time from existing_bags eb where alive = true order by id ", con.connection());
NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
varu = rdr.GetString(0);
existing_bags.Add(rdr.GetString(0), rdr.GetInt32(1));
}
rdr.Close();
}
public static IEnumerable <object[]> AllSqlConnections(string section) => new[] {
new object[] { (IConnectionManager)SqlConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)PostgresConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)MySqlConnection.ConnectionManager(section) },
new object[] { (IConnectionManager)SQLiteConnection.ConnectionManager(section) },
};
public NHSampleUnitOfWorkFactory(PostgresConnection postgres) : base(postgres)
{
}
async Task CreateIndex(ISagaData sagaData, PostgresConnection connection, IEnumerable<KeyValuePair<string, string>> propertiesToIndex)
{
var sagaTypeName = GetSagaTypeName(sagaData.GetType());
var parameters = propertiesToIndex
.Select((p, i) => new
{
PropertyName = p.Key,
PropertyValue = p.Value ?? "",
PropertyNameParameter = $"@n{i}",
PropertyValueParameter = $"@v{i}"
})
.ToList();
// lastly, generate new index
using (var command = connection.CreateCommand())
{
// generate batch insert with SQL for each entry in the index
var inserts = parameters
.Select(a =>
$@"
INSERT
INTO ""{_indexTableName}"" (""saga_type"", ""key"", ""value"", ""saga_id"")
VALUES (@saga_type, {a.PropertyNameParameter}, {a.PropertyValueParameter}, @saga_id)
");
var sql = string.Join(";" + Environment.NewLine, inserts);
command.CommandText = sql;
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter.PropertyNameParameter, NpgsqlDbType.Text).Value = parameter.PropertyName;
command.Parameters.Add(parameter.PropertyValueParameter, NpgsqlDbType.Text).Value = parameter.PropertyValue;
}
command.Parameters.Add("saga_type", NpgsqlDbType.Text).Value = sagaTypeName;
command.Parameters.Add("saga_id", NpgsqlDbType.Uuid).Value = sagaData.Id;
await command.ExecuteNonQueryAsync();
}
}
public SampleDbContext(PostgresConnection postgres) : base(postgres)
{
}
public CityRepository(PostgresConnection connection)
{
_connection = connection;
}
public ConnectionWrapper(PostgresConnection connection)
{
Connection = connection;
Semaphore = new SemaphoreSlim(1, 1);
}
