I have a C# Dotnet Core console Application. I am trying to connect NpgsqlConnection for PostgreSQL database (which is crate db). Im using this database for time series entry.I created the NpgsqlConnection in constructor of service.it's called every time . Is this a good way to call connection open in every time?. What is the alternative . In my project tables are not defined at the initial time. So DbContext is not useful. I think so?. Also some times below error is occurring.
{Npgsql.NpgsqlOperationInProgressException: A command is already in progress
My code is added below. How to create connection using NpgsqlConnection in time efficient way? Program.cs
static void Main(string[] args)
{
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
var configuration = builder.Build();
builder.RegisterType<TimeSeriesService>().As<ITimeSeriesService>();
var npsqlconnectionString = "Host=172.18.0.2;Username=crate;SSL Mode=Prefer;Database=doc";
builder.Register(c => new SensorDataRepository(npsqlconnectionString)).As<ISensorDataRepository>().SingleInstance();
var DataProcessor = container.Resolve<ITimeSeriesService>();
DataProcessor.ConsumeMsgs(brokeraddress, topic, groupid).GetAwaiter().GetResult();
builder.RegisterInstance<IConfigurationRoot>(configuration);
return builder.Build();
}
Database Service.cs
public class SensorDataRepository : ISensorDataRepository
{
public NpgsqlConnection conn { get; set; }
public SensorDataRepository(string npsqlconnectionString)
{
NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory());
}
public async Task InsertSensorData(SensorDataViewModel sensordata)
{
string sqlString = "Host=172.18.0.2;Username=crate;SSL Mode=Prefer;Database=doc";
using (NpgsqlConnection sqlCon = new NpgsqlConnection(sqlString))
{
sqlCon.Open();
string cmdString = $"INSERT INTO test1 (sensorid,deviceid,reading,timestamp) VALUES (@s,@d,@r,@t)";
using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmdString, sqlCon))
{
sqlCmd.Parameters.AddWithValue("s", sensordata.sensorid);
sqlCmd.Parameters.AddWithValue("d", sensordata.deviceid);
sqlCmd.Parameters.AddWithValue("r", sensordata.reading);
sqlCmd.Parameters.AddWithValue("t", sensordata.timestamp);
await sqlCmd.ExecuteNonQueryAsync();
}
}
}
Edited: NpgsqlConnection added. Also console application is runs only once. And waiting for the next message.that means Constructor invoked only once
