public void SchemaDrop(string schemaName, bool force, bool throwIfNotExists)
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
try
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaDrop(schemaName, force, throwIfNotExists));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
catch (PostgresException pex)
{
if (pex.SqlState == "2BP01")
{
throw new DependentObjectsException(pex.Detail);
}
if (pex.SqlState == "3F000")
{
throw new SchemaDoesntExistsException(schemaName);
}
throw;
}
}
public Schema CreateSchema(string schemaName, bool throwIfAlreadyExists)
{
try
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
//schemaName = schemaName.ToLower();
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaCreate(schemaName, throwIfAlreadyExists));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
return(GetSchema(schemaName));
}
catch (PostgresException pex)
{
if (pex.SqlState == "42P06")
{
throw new SchemaAlreadyExistsException(schemaName);
}
throw;
}
}
public Schema SchemaRename(string schemaName, string newSchemaName)
{
try
{
if (String.IsNullOrWhiteSpace(schemaName))
{
throw new ArgumentNullException(nameof(schemaName));
}
if (String.IsNullOrWhiteSpace(newSchemaName))
{
throw new ArgumentNullException(nameof(newSchemaName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.SchemaRename(schemaName, newSchemaName));
var resp = new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
return(GetSchema(newSchemaName));
}
catch (PostgresException pex)
{
if (pex.SqlState == "3F000")
{
throw new SchemaDoesntExistsException(schemaName);
}
if (pex.SqlState == "42P06")
{
throw new SchemaAlreadyExistsException(newSchemaName);
}
throw;
}
}
public int ExecuteNonQuery(string sqlStatement)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(sqlStatement);
return(ExecuteNonQuery(sqlCommand));
}
public TOut ExecuteScalar <TOut>(string sqlStatement)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(sqlStatement);
return(ExecuteScalar <TOut>(sqlCommand));
}
public TOut ExecuteScalar <TOut>(PgSqlCommand sqlCommand)
{
Logger.Debug(() =>
{
return($"ExecuteScalar<{typeof(TOut).FullName}>::{sqlCommand.CommandAsPlainText()}");
});
TOut ret = default(TOut);
try
{
lock (_lock)
{
var command = PrepareCommand(sqlCommand.Command);
foreach (var npgsqlParameter in sqlCommand.Parameters)
{
command.Parameters.Add(npgsqlParameter.ToNpgsqlParameter());
}
ret = command.ExecuteScalar().CloneTo <TOut>();
EndCommand(command);
}
}
catch (Exception e)
{
Logger.DebugFormat($"ExecuteScalar<{typeof(TOut).FullName}>::{{command}}", sqlCommand.CommandAsPlainText());
Logger.DebugFormat($"ExecuteScalar<{typeof(TOut).FullName}>::{{e}}", e);
throw;
}
return(ret);
}
public IEnumerable <JObject> ExecuteReader(string sqlStatement)
{
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(sqlStatement);
return(ExecuteReader(sqlCommand));
}
public int ExecuteNonQuery(PgSqlCommand sqlCommand)
{
Logger.Debug(() =>
{
return($"ExecuteNonQuery::{sqlCommand.CommandAsPlainText()}");
});
try
{
lock (_lock)
{
int ret;
var command = PrepareCommand(sqlCommand.Command);
foreach (var param in sqlCommand.Parameters)
{
command.Parameters.Add(param.ToNpgsqlParameter());
}
ret = command.ExecuteNonQuery();
EndCommand(command);
return(ret);
}
}
catch (Exception e)
{
Logger.ErrorFormat($"ExecuteNonQuery::{{e}}", e);
throw;
}
}
public void DomainDrop(string domainName, bool throwIfNotExists)
{
if (String.IsNullOrWhiteSpace(domainName))
{
throw new ArgumentNullException(nameof(domainName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.DomainDrop(domainName, throwIfNotExists));
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
public void ExtensionCreate(string extensionName, bool throwIfAlreadyExists)
{
if (String.IsNullOrWhiteSpace(extensionName))
{
throw new ArgumentNullException(nameof(extensionName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.ExtensionCreate(extensionName, throwIfAlreadyExists));
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
public IEnumerable <T> ExecuteCollection <T>(QueryModel queryModel)
{
var psqlCommand = PsqlGeneratingQueryModelVisitor.GeneratePsqlQuery(queryModel, _table);
var pgCommand = new PgSqlCommand();
pgCommand.Command = psqlCommand.Statement;
pgCommand.Parameters.AddRange(psqlCommand.Parameters);
Debug.WriteLine(pgCommand.CommandAsPlainText());
return(new DbExecuter(_table.GetConnectionString()).ExecuteReader <T>(pgCommand));
}
public IEnumerable <JObject> ExecuteReader(PgSqlCommand sqlCommand)
{
Logger.Debug(() =>
{
return($"ExecuteReader::{sqlCommand.CommandAsPlainText()}");
});
try
{
lock (_lock)
{
List <JObject> items = new List <JObject>();
var command = PrepareCommand(sqlCommand.Command);
foreach (var sqlCommandParameter in sqlCommand.Parameters)
{
var p = sqlCommandParameter.ToNpgsqlParameter();
command.Parameters.Add(p);
}
using (var reader = command.ExecuteReader())
{
var columns = reader.GetColumnSchema();
while (reader.Read())
{
var jo = new JObject();
for (int i = 0; i < reader.FieldCount; i++)
{
jo.Add(columns[i].ColumnName, DbExecuterHelper.ConvertFromDB(reader[i], columns[i]));
}
items.Add(jo);
}
}
EndCommand(command);
return(items);
}
}
catch (Exception e)
{
Console.WriteLine(sqlCommand.CommandAsPlainText());
Logger.Error(e, "ExecuteReader", sqlCommand.CommandAsPlainText());
throw;
}
}
public void DomainCreate(string domainName, string postgresTypeName, bool throwIfExists)
{
if (String.IsNullOrWhiteSpace(domainName))
{
throw new ArgumentNullException(nameof(domainName));
}
var sqlCommand = new PgSqlCommand();
sqlCommand.AppendCommand(SQLStatements.DomainCreate(domainName, postgresTypeName));
if (!throwIfExists)
{
if (!DomainExists(domainName))
{
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
}
else
{
new DbExecuter(ConnectionString).ExecuteNonQuery(sqlCommand);
}
}
public object ExecuteScalar(PgSqlCommand sqlCommand)
{
return(ExecuteScalar <object>(sqlCommand));
}
public IEnumerable <T> ExecuteReader <T>(PgSqlCommand sqlCommand)
{
return(ExecuteReader(sqlCommand).Select(JSON.ToObject <T>));
}
