Bulk insert into PostgreSQL using dapper in .NET core

Question

I am new to using Dapper and I just could not find out how to insert the list of attributes from a json file into my postgres database.

so here we go my object class:

public class UniqueIdField
{
    public string name { get; set; }
    public bool isSystemMaintained { get; set; }
}

public class SpatialReference
{
    public int wkid { get; set; }
    public int latestWkid { get; set; }
}

public class Field
{
    public string name { get; set; }
    public string type { get; set; }
    public string alias { get; set; }
    public string sqlType { get; set; }
    public int length { get; set; }
    public object domain { get; set; }
    public object defaultValue { get; set; }
}

public class Attributes
{
    public string GEN { get; set; }
    public string BL { get; set; }
    public string BL_ID { get; set; }
    public string county { get; set; }
    public string last_update { get; set; }
    public double cases7_per_100k { get; set; }
    public double cases7_bl_per_100k { get; set; }
    public string cases7_per_100k_txt { get; set; }
    public string BEZ { get; set; }
}

public class Feature
{
    public Attributes attributes { get; set; }
}

public class Root
{
    public string objectIdFieldName { get; set; }
    public UniqueIdField uniqueIdField { get; set; }
    public string globalIdFieldName { get; set; }
    public string geometryType { get; set; }
    public SpatialReference spatialReference { get; set; }
    public List<Field> fields { get; set; }
    public List<Feature> features { get; set; }
}

And this is my method to insert the data:

    public async Task SeedCoronaInformationTable(Root CoronaData)
    {
        var constr = _configuration.GetConnectionString("PostgresConnection");

        using var connection = new NpgsqlConnection(constr);
        connection.Open();

        var deleteStatement = @"DELETE FROM public.rki_corona_information";
        var insertStatement = @"INSERT INTO public.rki_corona_information (id, landkreis, bl, bl_id, county, last_update, cases7_per_100k, cases7_bl_per_100k, cases7_per_100k_txt, BEZ)
                               Values (@id, @landkreis, @bl, @bl_id, @county, @last_update, @cases7_per_100k, @cases7_bl_per_100k, @cases7_per_100k_txt, @BEZ)";

        connection.Query(deleteStatement);

        int index = 1;
        foreach (var feature in CoronaData.features)
        {
            var affectedRows = await connection.ExecuteAsync(insertStatement, new
            {
                id = index,
                landkreis = feature.attributes.GEN,
                bl = feature.attributes.BL,
                bl_id = feature.attributes.BL_ID,
                feature.attributes.county,
                feature.attributes.last_update,
                feature.attributes.cases7_per_100k,
                feature.attributes.cases7_bl_per_100k,
                feature.attributes.cases7_per_100k_txt,
                feature.attributes.BEZ
            }); ;
            index++;
        }

        connection.Close();
    }

The latter works, however, I think it quite unnecessary that I have to loop through the object myself. I'd rather insert it and letting dapper take care of the looping. I just could not find a way how to pass on the list of Attributes as it is a nested element of features.

How can I grab the attributes and insert them like var affectedRows = await connection.ExecuteAsync(insertStatement, attributes) ?

Answer 1

There is no supported syntax to do that. What you have should work fine - note that the delete step could/should use Execute instead of Query.