The ConnectionString property has not been initialized Error with Dapper

Question

My code to execute an SQL insert command is like this

foreach (var id in idlist)
{
    _connection.Open();
    var sql = "sql query"
    var parameters = ....

    await _connection.ExecuteAsync(sql, parameters);
    _connection.Close();
}

All looks good, But the loop runs successfully only once, when it enters a second time it throws an error

The ConnectionString property has not been initialized.

Can anyone please point out what I may be doing wrong here?

Note: I have tried moving the connection open and close out of the loop too, But still the same result.

Answer 1

Your code should look like:

_connection.Open();
var sql = "sql query"

foreach (var id in idlist)
{
    var parameters = ....
    await _connection.ExecuteAsync(sql, parameters);
}
_connection.Close();

If you're executing only once, omit the open and close (Dapper will do it), unless your connection is enrolled in a transaction

_connection is named like as if it's a class level variable. Be sure that you aren't manipulating it elsewhere while the loop is running. I wouldn't say you gain much by holding a reference to a connection, perhaps consider:

using var connection = new SqlConnection("your conn string");
connection.Open();
var sql = "sql query"

foreach (var id in idlist)
{
    var parameters = ....
    await _connection.ExecuteAsync(sql, parameters);
}
connection.Close();

Error handling will vary according to what you want to do if eg one insert fails mid batch - dump it all, halt and fix, ignore and proceed.