protected override void LoadColumnsFromDatabase(string database)
{
// In PostgreSQL you have to connect to every database to read the Schema
client.ChangeDatabase(database);
// Use a list to store the data in Memory and bulk insert it
List <Columns> list = new List <Columns>();
using (NpgsqlCommand command = client.CreateCommand())
{
command.CommandText = @"SELECT c.table_schema, c.table_name, c.column_name, CASE WHEN character_maximum_length is null THEN c.data_type ELSE CONCAT(c.data_type, '(', c.character_maximum_length, ')') END as data_type
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t USING (table_catalog, table_schema, table_name)
WHERE c.table_schema not in ('pg_catalog', 'information_schema') and t.table_type = 'BASE TABLE'
ORDER BY table_schema, table_name, column_name, ordinal_position";
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Filter for Schema-Regex (only applied if user wants it this way)
// The || is short-circuit enabled!
if ((!this.FilterSchema) || (this.MatchSchema.Contains(reader.GetString(0))))
{
list.Add(new Columns {
DatabaseName = database, Schema = reader.GetString(0), TableName = reader.GetString(1), ColumnName = reader.GetString(2), DataType = reader.GetString(3)
});
}
}
}
}
// Now that all fetched rows are in our list, lets add them in bulk to the SQLite database
db.Columns.AddRange(list);
}
public PostgreSqlFixture()
{
Connection = new NpgsqlConnection(Config.Value.DefaultConnection);
CreateTestDatabase(Connection);
Connection.ChangeDatabase(Config.Value.TestDatabase);
ApplyMigrations(Connection, MigrationDirection.Up);
}
public void ChangeDatabaseTestConnectionCache()
{
NpgsqlConnection conn1 = new NpgsqlConnection(TheConnectionString);
NpgsqlConnection conn2 = new NpgsqlConnection(TheConnectionString);
NpgsqlCommand command;
// connection 1 change database
conn1.Open();
conn1.ChangeDatabase("template1");
command = new NpgsqlCommand("select current_database()", conn1);
string db1 = (String)command.ExecuteScalar();
Assert.AreEqual("template1", db1);
// connection 2 's database should not changed, so should different from conn1
conn2.Open();
command = new NpgsqlCommand("select current_database()", conn2);
string db2 = (String)command.ExecuteScalar();
Assert.AreNotEqual(db1, db2);
conn1.Close();
conn2.Close();
}
public void RecreateDatabase()
{
string sql = string.Empty;
var assembly = typeof(DapperMapping).Assembly;
var stream = assembly.GetManifestResourceStream("TarokScoreBoard.Infrastructure.Scripts.intitdb.sql");
using (var sr = new StreamReader(stream))
sql = sr.ReadToEnd();
var realConn = new NpgsqlConnection(connectionString);
tempConn = realConn.CloneWith($"User ID={realConn.UserName};Host={realConn.Host};Port={realConn.Port};Database={maintenaceDbName}");
tempConn.Open();
var testDatabaseInstanceName = realConn.Database;
var dropCommand = tempConn.CreateCommand();
dropCommand.CommandText = $"drop database if exists {testDatabaseInstanceName}";
output?.WriteLine("dropping db");
dropCommand.ExecuteNonQuery();
var createDbCOmmand = tempConn.CreateCommand();
createDbCOmmand.CommandText = $"create database {testDatabaseInstanceName}";
output?.WriteLine("creating db");
createDbCOmmand.ExecuteNonQuery();
output?.WriteLine($"changing db to {testDatabaseInstanceName}");
tempConn.ChangeDatabase(testDatabaseInstanceName);
var initDbCommand = tempConn.CreateCommand();
initDbCommand.CommandText = sql;
output?.WriteLine($"executing init script");
initDbCommand.ExecuteNonQuery();
}
public async Task <NpgsqlConnectionFactory> CreateDatabaseAsync(
CancellationToken cancellationToken)
{
// Create test database.
var databaseSuffix = Guid.NewGuid();
var databaseName = $"sequence_{databaseSuffix:N}";
string connectionString;
using (var connection = new NpgsqlConnection(_connectionString))
{
await connection.OpenAsync(cancellationToken);
await connection.ExecuteAsync($"CREATE DATABASE {databaseName};");
connection.ChangeDatabase(databaseName);
connectionString = connection.ConnectionString;
}
// Apply migrations.
var postgresOptions = new PostgresOptions {
ConnectionString = connectionString
};
var options = Options.Create(postgresOptions);
var logger = NullLogger <PostgresMigrations> .Instance;
var migrations = new PostgresMigrations(options, logger);
await migrations.UpgradeDatabaseAsync(cancellationToken);
return(new NpgsqlConnectionFactory(options));
}
public static bool initDB()
{
try
{
var settings = (new Form_Params()).getSettings();
var cs = "Host=" + settings[0] + ";Port=" + settings[1] + ";Username="******";Password="******";Database=" + settings[4];
var con = new NpgsqlConnection(cs);
con.Open();
var cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandText = "select datname from pg_database where datname='tzspd_user_errors';";
var str = cmd.ExecuteScalar();
if (str == null) //in case if tzspd_user_errors does not exist
{
cmd.CommandText = "create database tzspd_user_errors;"; //create it
cmd.ExecuteNonQuery();
con.ChangeDatabase("tzspd_user_errors");
cmd.CommandText = "create table error_data( err_time timestamp, " +
" err_lab varchar, err_text varchar);"; //create table within new databese
cmd.ExecuteNonQuery();
}
//con.ChangeDatabase("tzspd_user_errors");
cmd.Cancel();
con.Close();
return(true);
}
catch
{
return(false);
}
}
public void ChangeDatabaseConnectionNotOpen()
{
using (var conn = new NpgsqlConnection(ConnectionString))
Assert.That(() => conn.ChangeDatabase("template1"), Throws.Exception
.TypeOf <InvalidOperationException>()
.With.Message.EqualTo("Connection is not open"));
}
override public ADODB.Recordset ExecuteSql(string sql)
{
NpgsqlConnection cn = new NpgsqlConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(this.Name);
return(this.ExecuteIntoRecordset(sql, cn));
}
public void ConnectToDatabase(string db)
{
if (connection == null)
{
return;
}
//catch ex
connection.ChangeDatabase(db);
}
public override void ExecuteScript(string script)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
conn.ChangeDatabase(Database.ToLower());
NpgsqlCommand cmd = new NpgsqlCommand(script, conn);
cmd.ExecuteNonQuery();
}
}
public override void SetVersion(string version, string upgradeBy)
{
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
conn.ChangeDatabase(Database.ToLower());
NpgsqlCommand cmd = new NpgsqlCommand("COMMENT ON DATABASE " + Database.ToLower() + " IS :version", conn);
cmd.Parameters.Add(new NpgsqlParameter(":version", version + ";" + upgradeBy));
cmd.ExecuteNonQuery();
}
}
public void ChangeDatabase()
{
_conn.Open();
_conn.ChangeDatabase("template1");
NpgsqlCommand command = new NpgsqlCommand("select current_database()", _conn);
String result = (String)command.ExecuteScalar();
Assert.AreEqual("template1", result);
}
public void ChangeDatabase()
{
_conn.Open();
_conn.ChangeDatabase("template1");
NpgsqlCommand command = new NpgsqlCommand("select current_database()", _conn);
String result = (String)command.ExecuteScalar();
Console.WriteLine(result);
//Assertion.AssertEquals("template1", result);
}
public override string GetVersion()
{
string version = string.Empty;
using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
conn.ChangeDatabase(Database.ToLower());
NpgsqlCommand cmd = new NpgsqlCommand("SELECT d.description FROM pg_shdescription d JOIN pg_database b ON b.oid = d.objoid WHERE datname = :database_name", conn);
cmd.Parameters.Add(new NpgsqlParameter(":database_name", Database.ToLower()));
version = cmd.ExecuteScalar() as string;
}
return(version);
}
private async Task InsertNullVersion(string versionFieldName)
{
using (NpgsqlConnection connection = new NpgsqlConnection(base.ConnectionString))
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = @"INSERT INTO public.""DbSettings""(""" + versionFieldName + @""") VALUES(NULL);";
await connection.OpenAsync();
connection.ChangeDatabase(this.dbName);
await command.ExecuteNonQueryAsync();
}
}
}
public void ChangeDatabaseDoesNotAffectOtherConnections()
{
using (var conn1 = new NpgsqlConnection(ConnectionString))
using (var conn2 = new NpgsqlConnection(ConnectionString))
{
// Connection 1 changes database
conn1.Open();
conn1.ChangeDatabase("template1");
Assert.That(conn1.ExecuteScalar("SELECT current_database()"), Is.EqualTo("template1"));
// Connection 2's database should not changed
conn2.Open();
Assert.That(conn2.ExecuteScalar("SELECT current_database()"), Is.Not.EqualTo(conn1.Database));
}
}
public async Task RunUpdateAsync(string updateContent)
{
using (NpgsqlConnection connection = new NpgsqlConnection(base.ConnectionString))
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = updateContent;
await connection.OpenAsync();
connection.ChangeDatabase(this.dbName);
await command.ExecuteNonQueryAsync();
}
}
}
public void CreateDbIfnotExists()
{
var query = "SELECT 'CREATE DATABASE " + this.dbName + "' WHERE NOT EXISTS ( SELECT datname FROM pg_catalog.pg_database WHERE datname = '" + Utility.GetConnectionString("ConnectionStrings:DbName") + "' );";
using var cmd = new NpgsqlCommand(query, conn);
var createDb = cmd.ExecuteScalar();
if (createDb != null)
{
using var cmdCreateDb = new NpgsqlCommand(createDb.ToString(), conn);
cmdCreateDb.ExecuteScalar();
}
cmd.Dispose();
conn.ChangeDatabase(Utility.GetConnectionString("ConnectionStrings:DbName"));
}
public static bool alteraSenhaUsudac(CL_EsqueciSenha objEsqueciSenha, string con)
{
//connString = "Server=diretivasistemas.ddns.com.br;Port=5432;User Id=postgres;Password=Pgadmin12345;Database=11157076000141";
connString = "Server=postgres-diretiva.ddns.net;Port=5432;User Id=postgres;Password=Diretiva!@#;Database=Diretiva";
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
string bd = Conn.Database;
Conn = new NpgsqlConnection(connString);
Conn.Open();
NpgsqlTransaction transaction = Conn.BeginTransaction();
string sql = "UPDATE usudac set u_senha=@u_nova WHERE u_email=@u_email AND u_senha=@u_antiga";
try
{
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn, transaction);
comand.Parameters.AddWithValue("u_nova", objEsqueciSenha.u_esqSenhaNov);
comand.Parameters.AddWithValue("u_antiga", objEsqueciSenha.u_esqSenhaAnt);
comand.Parameters.AddWithValue("u_email", objEsqueciSenha.u_esqEmail);
comand.ExecuteScalar();
transaction.Commit();
Conn.ChangeDatabase(bd);
transaction = Conn.BeginTransaction();
comand = new NpgsqlCommand(sql, Conn, transaction);
comand.Parameters.AddWithValue("u_nova", objEsqueciSenha.u_esqSenhaNov);
comand.Parameters.AddWithValue("u_antiga", objEsqueciSenha.u_esqSenhaAnt);
comand.Parameters.AddWithValue("u_email", objEsqueciSenha.u_esqEmail);
comand.ExecuteScalar();
transaction.Commit();
return(true);
}
catch (Exception ex)
{
ex.ToString();
transaction.Rollback();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public static bool excluiUsudac(CL_Usudac objUsudac, string con)
{
//connString = "Server=diretivasistemas.ddns.com.br;Port=5432;User Id=postgres;Password=Pgadmin12345;Database=11157076000141";
connString = "Server=postgres-diretiva.ddns.net;Port=5432;User Id=postgres;Password=Diretiva!@#;Database=Diretiva";
Conn = new NpgsqlConnection(connString);
Conn.Open();
NpgsqlTransaction transaction = Conn.BeginTransaction();
string sql = "DELETE FROM usudac WHERE u_codigo=@u_codigo AND u_email=@u_email";
try
{
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn);
comand.Parameters.AddWithValue("u_codigo", objUsudac.u_codigo);
comand.Parameters.AddWithValue("u_email", objUsudac.u_email);
comand.ExecuteScalar();
transaction.Commit();
Conn.Close();
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
Conn.Open();
Conn.ChangeDatabase(objUsudac.u_cgc);
transaction = Conn.BeginTransaction();
comand = new NpgsqlCommand(sql, Conn, transaction);
comand.Parameters.AddWithValue("u_codigo", objUsudac.u_codigo);
comand.Parameters.AddWithValue("u_email", objUsudac.u_email);
comand.ExecuteScalar();
transaction.Commit();
return(true);
}
catch (Exception ex)
{
ex.ToString();
transaction.Rollback();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}
public async Task <int?> GetVersionAsync(string versionFieldName)
{
if (String.IsNullOrEmpty(versionFieldName))
{
throw new ArgumentException("versionFileName cannot be nul or empty. Provide appropriate version field name.");
}
int?dbVersion = null;
using (NpgsqlConnection connection = new NpgsqlConnection(base.ConnectionString))
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = @"SELECT """ + versionFieldName + @""" FROM ""DbSettings"";";
await connection.OpenAsync();
connection.ChangeDatabase(this.dbName);
try
{
using (DbDataReader dbDataReader = await command.ExecuteReaderAsync())
{
if (await dbDataReader.ReadAsync())
{
if (await dbDataReader.IsDBNullAsync(0))
{
dbVersion = null;
}
else
{
dbVersion = Convert.ToInt32(dbDataReader[versionFieldName]);
}
}
}
}
catch (PostgresException)
{
connection.Close();
await this.CreateVersions(versionFieldName);
await this.InsertNullVersion(versionFieldName);
}
}
}
return(dbVersion);
}
public override DbConnection GetConnection(string database)
{
try
{
DbConnection connection = new NpgsqlConnection(this.connectionString);
connection.Open();
if (database != "")
{
connection.ChangeDatabase(database);
}
return(connection);
}
catch (Exception ex)
{
this.message = ex.Message;
LogHelper.Error(ex);
return(null);
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder()
{
Host = txtHost.Text,
Port = int.Parse(txtPort.Text),
Username = txtId.Text,
Password = txtPw.Text,
Database = txtDb.Text,
};
Debug.WriteLine(builder.ConnectionString);
if (connection == null)
{
connection = new NpgsqlConnection(builder.ConnectionString);
try
{
connection.Open();
connection.ChangeDatabase(txtDb.Text);
}
catch (Exception ex)
{
string exMsg = ex.Message;
if (ex.InnerException != null)
{
exMsg += "\r\n" + ex.InnerException.Message;
}
MessageBox.Show(exMsg, "DB error");
connection = null;
return;
}
txtHost.Enabled = false;
txtPort.Enabled = false;
txtId.Enabled = false;
txtPw.Enabled = false;
txtDb.Enabled = false;
btnConnect.Enabled = false;
btnDisconnect.Enabled = true;
}
}
private static void CreateDB(string repositoryName, TestContext testContext, bool isProtected)
{
using (NpgsqlConnection con = CreateConnectionForDBCreation())
{
using (NpgsqlCommand cmd = con.CreateCommand())
{
//we always have low char strings
repositoryName = repositoryName.ToLower();
//get the sql script
string TablesCmdString = File.ReadAllText(Path.Combine(testContext.TestDeploymentDir, Properties.Resources.MLIFTERDBSQL));
string DBCreateCmdString;
//create the db
DBCreateCmdString = "CREATE DATABASE " + repositoryName + " ENCODING 'UTF8';";
cmd.CommandText = DBCreateCmdString;
cmd.ExecuteNonQuery();
//Why?? Fabthe
//fill it with data from sql script above
con.ChangeDatabase(repositoryName);
cmd.CommandText = TablesCmdString;
cmd.ExecuteNonQuery();
//create test user
string DBCreateTestuserCmdString = "SELECT \"InsertUserProfile\"('testuser', '', '', 'ListAuthentication'); SELECT \"AddGroupToUserByName\"('testuser', 'Student');";
cmd.CommandText = DBCreateTestuserCmdString;
cmd.ExecuteNonQuery();
if (isProtected)
{
using (NpgsqlCommand cmd2 = con.CreateCommand())
{
cmd2.CommandText = "UPDATE \"LearningModules\" SET content_protected = true";
cmd2.ExecuteNonQuery();
}
}
}
}
}
public async Task SetVersionAsync(int version, string versionFieldName)
{
using (NpgsqlConnection connection = new NpgsqlConnection(base.ConnectionString))
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = @"UPDATE ""DbSettings"" SET """ + versionFieldName + @"""=@DbVersion;";
NpgsqlParameter parameter = command.CreateParameter();
parameter.ParameterName = "@DbVersion";
parameter.Value = version;
command.Parameters.Add(parameter);
await connection.OpenAsync();
connection.ChangeDatabase(this.dbName);
await command.ExecuteNonQueryAsync();
}
}
}
private async Task CreateVersions(string versionFieldName)
{
using (NpgsqlConnection connection = new NpgsqlConnection(base.ConnectionString))
{
using (NpgsqlCommand command = connection.CreateCommand())
{
command.CommandText = @"
CREATE TABLE public.""DbSettings""
(
""" + versionFieldName + @""" integer NULL
)
WITH(
OIDS = FALSE
)
TABLESPACE pg_default;
";
await connection.OpenAsync();
connection.ChangeDatabase(this.dbName);
await command.ExecuteNonQueryAsync();
}
}
}
public void ChangeDatabase(string databaseName)
{
_connection.ChangeDatabase(databaseName);
}
public void use(string database)
{
Conn.ChangeDatabase(database);
}
public void ChangeDatabase(string databaseName)
{
_impl.ChangeDatabase(databaseName);
}
public static bool alteraUsudac(CL_Usudac objUsudac, string con)
{
//connString = "Server=diretivasistemas.ddns.com.br;Port=5432;User Id=postgres;Password=Pgadmin12345;Database=11157076000141";
connString = "Server=postgres-diretiva.ddns.net;Port=5432;User Id=postgres;Password=Diretiva!@#;Database=Diretiva";
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
objUsudac.u_cgc = Conn.Database;
Conn = new NpgsqlConnection(connString);
Conn.Open();
NpgsqlTransaction transaction = Conn.BeginTransaction();
try
{
string sql = "UPDATE usudac SET u_nome=@u_nome, u_email=@u_email, u_bd=@u_bd, u_usuario=@u_usuario, u_mcliente=@u_mcliente, u_cvendas=@u_cvendas " +
"u_mctarec=@u_mctarec, u_mempresa=@u_mempresa, u_mvend=@u_mvend, u_arma=@u_arma, u_mcheques=@u_mcheques WHERE u_codigo=@u_codigo";
NpgsqlCommand comand = new NpgsqlCommand(sql, Conn, transaction);
comand.Parameters.AddWithValue("u_nome", objUsudac.u_nome);
comand.Parameters.AddWithValue("u_email", objUsudac.u_email);
comand.Parameters.AddWithValue("u_bd", objUsudac.u_cgc);
comand.Parameters.AddWithValue("u_codigo", objUsudac.u_codigo);
comand.Parameters.AddWithValue("u_usuario", objUsudac.u_usudac);
comand.Parameters.AddWithValue("u_mcliente", objUsudac.u_particip);
comand.Parameters.AddWithValue("u_mctarec", objUsudac.u_financeiro);
comand.Parameters.AddWithValue("u_mempresa", objUsudac.u_empresa);
comand.Parameters.AddWithValue("u_mvend", objUsudac.u_parceiro);
comand.Parameters.AddWithValue("u_arma", objUsudac.u_proposta);
comand.Parameters.AddWithValue("u_mcheques", objUsudac.u_sinistro);
comand.Parameters.AddWithValue("u_cvendas", objUsudac.u_conssinistro);
comand.ExecuteScalar();
transaction.Commit();
Conn.Close();
DB_Funcoes.DesmontaConexao(con);
CONEXAO = montaDAO(CONEXAO);
Conn = new NpgsqlConnection(CONEXAO);
Conn.Open();
Conn.ChangeDatabase(objUsudac.u_cgc);
transaction = Conn.BeginTransaction();
comand = new NpgsqlCommand(sql, Conn, transaction);
comand.Parameters.AddWithValue("u_nome", objUsudac.u_nome);
comand.Parameters.AddWithValue("u_email", objUsudac.u_email);
comand.Parameters.AddWithValue("u_bd", objUsudac.u_cgc);
comand.Parameters.AddWithValue("u_codigo", objUsudac.u_codigo);
comand.Parameters.AddWithValue("u_usuario", objUsudac.u_usudac);
comand.Parameters.AddWithValue("u_mcliente", objUsudac.u_particip);
comand.Parameters.AddWithValue("u_mctarec", objUsudac.u_financeiro);
comand.Parameters.AddWithValue("u_mempresa", objUsudac.u_empresa);
comand.Parameters.AddWithValue("u_mvend", objUsudac.u_parceiro);
comand.Parameters.AddWithValue("u_arma", objUsudac.u_proposta);
comand.Parameters.AddWithValue("u_mcheques", objUsudac.u_sinistro);
comand.ExecuteScalar();
transaction.Commit();
return(true);
}
catch (Exception ex)
{
ex.ToString();
transaction.Rollback();
return(false);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
}