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);
     }
 }
Пример #3
0
        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();
            }
        }