Populate database with Dictionary entries (postgresql, C#)

Question

I have a Dictionary<string, string> with a few entries in it. I want to populate my postgresql database with its data.

I could do a for loop and perform one INSERT query at a time, but that looks ugly, and it is probably slow.

I wonder if there is any way to do this at once, how to do that?

Postgres allows multiple "values" set in one INSERT statement to insert multiple rows. So you could iterate over your dictionary to create that single INSERT statement and then send that to the database. — Ralf, Nov 19 '20 at 16:40
[this question](https://stackoverflow.com/questions/452859) shows how to do multiple rows in a single statement in SQL Server, though many of the answers also apply to Postgres. — mason, Nov 19 '20 at 16:48

score 0 · Answer 1 · answered Nov 19 '20 at 19:30

Thanks to @Ralf and @mason who helped me find a way to achieve this.

That's what I got to make it work:

        using (NpgsqlConnection sqlCon = new NpgsqlConnection(sql))
        {
            sqlCon.Open();

            string values = String.Join("), (", mdoCodes.ToArray()).Replace("[", "").Replace("]", "");

            // string sqlString = @"Insert into codigos_mdo(code, descricao) Values('teste', 'teste');";
            string sqlString = @"INSERT INTO codigos_mdo(code, descricao) 
                               Values ( " + values + ");";
            using (NpgsqlCommand sqlCmd = new NpgsqlCommand(sqlString, sqlCon))
            {
                sqlCmd.ExecuteNonQuery();
            }    
        }

Populate database with Dictionary entries (postgresql, C#)

1 Answers1