Impulsive Comments - Answer Overflow

Impulsive

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

yes! i wasn't clear I was hoping/expecting to see none when using the singleton. In my work project I was seeing some leak when navigating the front end~ I went across every place an NpgsqlConnection was used and ensured it was wrapped in usings, but yea, some still leaking about, I've obviously missing someting, maybe an NpgsqlCommand or NpgsqlParams or something underneath the connection that must close fbefore the Connection is allowed to be released. Example~

using (var connection = new NpgsqlConnection(connectionString))
{
//code code code
}

using (var connection = new NpgsqlConnection(connectionString))
{
//code code code
}

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

sick! I'll do that. Thanks again 🙂

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

I understand EF reduces my interaction with SQL substantially~ i've heard a bit about Dapper from those like Nick chapsas and others online. I'll do some reasearch into Dapper and see where it leads, thanks for the guidance.

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

p.s. i've never heard of Temu before, a quick google shows it's a shitty knock off brand online store 😛

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

so what you are saying is screw all this off and use dapper? 😄

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

ohh really..

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

We have a class just for Postgres, we pass it the connection string and it returns a NpgsqlConnection. we are masochists for sure! I was playing (studying?) today with using the Npgsql.DependencyInjection package and Npgsql It seems like an OK approach. This is a few snippets from that playground. From Program.cs:

builder.Services.AddNpgsqlDataSource(builder.Configuration.GetConnectionString("PostgresConnection"));
            builder.Services.AddTransient<GetBookDetails>();

builder.Services.AddNpgsqlDataSource(builder.Configuration.GetConnectionString("PostgresConnection"));
            builder.Services.AddTransient<GetBookDetails>();

I implemented this GetBookDetails class to get the count of rows from some sample data i found online on my home lab Postgres~ (RIP Tteck) When smashing the call from a razor page and I didn't see the database connection count increase even once after application startup.

using Npgsql;

namespace DemoProject.Respository;

public class GetBookDetails
{
    private readonly NpgsqlDataSource _dataSource;
    public GetBookDetails(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }
    public int GetBookCount()
    {
        const string query = "SELECT COUNT(*)::int FROM public.books";

        using var connection = _dataSource.OpenConnection();
        using var command = new NpgsqlCommand(query, connection);

        int bookCount = (int)command.ExecuteScalar();
        return bookCount;
    }
}

using Npgsql;

namespace DemoProject.Respository;

public class GetBookDetails
{
    private readonly NpgsqlDataSource _dataSource;
    public GetBookDetails(NpgsqlDataSource dataSource)
    {
        _dataSource = dataSource;
    }
    public int GetBookCount()
    {
        const string query = "SELECT COUNT(*)::int FROM public.books";

        using var connection = _dataSource.OpenConnection();
        using var command = new NpgsqlCommand(query, connection);

        int bookCount = (int)command.ExecuteScalar();
        return bookCount;
    }
}

Let's suppose i rename GetBookDetails to PostgresDB implement a few methods that open/close the connections for each query, probaly need to create a NpgsqlParameter implenentation as well methods for the below, likely with optional inputs for Parameterised queries (we have many) ExecuteQuery ExecuteNonQuery ExecuteScalar ... the rest of the types we use in the project today~ I Could probably get away with minimal re-factoring and have a far more efficient way of communicating with my database. I feel like i'd be re-inventing the wheel here though -.-

19 replies

CC#

•Created by Impulsive on 11/23/2024 in #help

.NET 8.0, PostgreSql and left over Idle sessions

thanks this helps, I see what you mean here, this should also de-couple all the components as well right? With this kind of approach realistically we wouldn't have API call construction code + SQL + creating/disposing connections all in the one class. I think currently we as a team have muddied a lot of things up and we'd likely need to re-factor a bunch of stuff to swap to this design. Thanks for the info about transient too, I knew it creates a new instance of the transient component I figured if they took the singleton in it wouldn't be spawning a bunch of connections, but you are right, we do end up with several of the same object open at one time. really good food for thought. Many of the team are pretty new to C#, with not a huge amount of OO experience. I foresee a lot of re-factoring for us in the future 😉 i'll attempt to add time to the project to re-factor stuff as we add new features/enhance existing~

19 replies

Gaming

Programming