public IEnumerable <int> Execute(params SqlCommand[] sqlCommands)
{
using (var connection = new PgSqlConnection(_connectionString))
{
var allRecordsAffected = new List <int>();
connection.Open();
connection.BeginTransaction();
try
{
sqlCommands.ToList().ForEach(sqlCommand =>
{
var recordsAffected = connection.Execute(sqlCommand.Sql, sqlCommand.Param);
allRecordsAffected.Add(recordsAffected);
});
connection.Commit();
}
catch
{
connection.Rollback();
throw;
}
finally
{
connection.Close();
}
return(allRecordsAffected);
}
}
public int Execute(string sql, object param = null)
{
using (var connection = new PgSqlConnection(_connectionString))
{
connection.Open();
var recordsAffected = connection.Execute(sql, param);
return(recordsAffected);
}
}
private void CreateDB(string database = "customer_test")
{
var sql = @"
CREATE TABLE customers (
id SERIAL PRIMARY KEY ,
firstname varchar(100) NOT NULL,
lasttname varchar(100) NOT NULL,
deleted bool DEFAULT false NOT NULL,
CreatedUtc timestamp without time zone default (now() at time zone 'utc') NULL,
UpdatedAt timestamp without time zone default (now() at time zone 'utc') NULL,
CreatedAtLocalNullable timestamp without time zone default (now() at time zone 'utc') NULL,
CreateAtLocal timestamp without time zone default (now() at time zone 'utc') NOT NULL,
version varchar(100) NULL,
Amount numeric(10,2) DEFAULT 0 NOT NULL ,
Age int null
);";
var connection = new PgSqlConnection(DbConnectionString);
connection.Open();//if this line is commented then we will get connection is already open .
//I need to use single connection to use across all these queries to run async // single connection is to manage transaction.
var name = connection.ExecuteScalar <string>(
$"SELECT datname FROM pg_database WHERE datistemplate = false and datname='{database}'");
if (string.IsNullOrWhiteSpace(name))
{
connection.Execute($"create database {database}");
connection.Close();
connection = new PgSqlConnection(GetConnection(database));
connection.Execute(sql);
}
else
{
connection.Close();
}
}
