CODEWITHSUNDEEP
c#postgresqlasp.net-core.net-coredapper
Marc Ottenbacher
Marc Ottenbacher

Reputation: 156

Bulk insert into PostgreSQL using dapper in .NET core

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) ?

Upvotes: 5

Views: 4991

Answers (2)

Cosmin Sontu
Cosmin Sontu

Reputation: 1104

You could serialize your features array into a json array or a composite array as described here: Table valued Parameter Equivalent in Postgresql.

I've used table valued parameters to achieve this with SQL Server. Depending on how big your records are, you might want to batch the requests.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062550

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.

Upvotes: 3

Related Questions