| public ChangeDatabase ( String dbName ) : void | ||
| dbName | String | The name of the database to use in place of the current database. |
| 리턴 | void | |
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="intitString"></param>
public pgsql_API(string intitString)
{
// connectionstring=
string[] parameters = intitString.Replace("\r\n","\n").Split('\n');
foreach(string param in parameters){
if(param.ToLower().IndexOf("connectionstring=") > -1){
m_ConStr = param.Substring(17);
}
}
SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(m_ConStr);
string database = b.InitialCatalog;
b.InitialCatalog = "";
using(NpgsqlConnection con = new NpgsqlConnection(b.ToString().ToLower().Replace("data source","server"))){
con.Open();
// See if database exists
try{
con.ChangeDatabase(database);
}
catch{
// Database don't exist, try to create it
try{
con.Close();
con.ConnectionString = b.ToString().ToLower().Replace("data source","server");
con.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "create database \"" + database + "\"";
cmd.ExecuteNonQuery();
con.ChangeDatabase(database);
// Create tables
cmd.CommandText = ResManager.GetText("tables.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
// Create procedures
cmd.CommandText = ResManager.GetText("procedures.sql",System.Text.Encoding.Default);
cmd.ExecuteNonQuery();
}
catch{
throw new Exception("Database '" + database + "' doesn''t exist ! Create failed, specified user doesn't have enough permisssions to create database ! Create database manually.");
}
}
}
}
//
// static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
// {
// string cnstr = dbRoot.ConnectionString + "Database=" + database + ";";
// NpgsqlConnection cn = new Npgsql.NpgsqlConnection(cnstr);
// return cn;
// }
static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
{
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(database);
return cn;
}
override public ADODB.Recordset ExecuteSql(string sql)
{
NpgsqlConnection cn = new NpgsqlConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(this.Name);
return this.ExecuteIntoRecordset(sql, cn);
}
//
// static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
// {
// string cnstr = dbRoot.ConnectionString + "Database=" + database + ";";
// NpgsqlConnection cn = new Npgsql.NpgsqlConnection(cnstr);
// return cn;
// }
static public NpgsqlConnection CreateConnection(MyMeta.dbRoot dbRoot, string database)
{
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(dbRoot.ConnectionString);
cn.Open();
cn.ChangeDatabase(database);
return(cn);
}
override internal void LoadAll()
{
try
{
string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
"n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
"idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
"JOIN pg_class cls ON cls.oid=indexrelid " +
"JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
"JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
"JOIN pg_am a ON a.oid = cls.relam " +
"LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
"LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
"WHERE con.conname IS NULL ORDER BY cls.relname;";
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString);
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn);
cn.Open();
cn.ChangeDatabase(this.Table.Tables.Database.Name);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
cn.Close();
PopulateArrayNoHookup(metaData);
for(int i = 0; i < this.Count; i++)
{
Index index = this[i] as Index;
if(null != index)
{
string s = index._row["columns"] as string;
string[] colIndexes = s.Split(' ');
foreach(string colIndex in colIndexes)
{
if(colIndex != "0")
{
int id = Convert.ToInt32(colIndex);
Column column = this.Table.Columns[id-1] as Column;
index.AddColumn(column.Name);
}
}
}
}
}
catch {}
}
override internal void LoadAll()
{
try
{
string select = @"SELECT current_database() as table_catalog, tab.relname AS table_name, " +
"n.nspname as TABLE_NAMESPACE, cls.relname as INDEX_NAME, idx.indisunique as UNIQUE, " +
"idx.indisclustered as CLUSTERED, a.amname as TYPE, indkey AS columns FROM pg_index idx " +
"JOIN pg_class cls ON cls.oid=indexrelid " +
"JOIN pg_class tab ON tab.oid=indrelid AND tab.relname = '" + this.Table.Name + "' " +
"JOIN pg_namespace n ON n.oid=tab.relnamespace AND n.nspname = '" + this.Table.Schema + "' " +
"JOIN pg_am a ON a.oid = cls.relam " +
"LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0') " +
"LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid) " +
"WHERE con.conname IS NULL ORDER BY cls.relname;";
NpgsqlConnection cn = new Npgsql.NpgsqlConnection(this.dbRoot.ConnectionString);
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(select, cn);
cn.Open();
cn.ChangeDatabase(this.Table.Tables.Database.Name);
DataTable metaData = new DataTable();
adapter.Fill(metaData);
cn.Close();
PopulateArrayNoHookup(metaData);
for (int i = 0; i < this.Count; i++)
{
Index index = this[i] as Index;
if (null != index)
{
string s = index._row["columns"] as string;
string[] colIndexes = s.Split(' ');
foreach (string colIndex in colIndexes)
{
if (colIndex != "0")
{
int id = Convert.ToInt32(colIndex);
Column column = this.Table.Columns[id - 1] as Column;
index.AddColumn(column.Name);
}
}
}
}
}
catch {}
}
public void ChangeDatabaseTestConnectionCache()
{
using (var conn1 = new NpgsqlConnection(ConnectionString))
using (var conn2 = new NpgsqlConnection(ConnectionString))
{
// connection 1 change database
conn1.Open();
conn1.ChangeDatabase("template1");
var command = new NpgsqlCommand("select current_database()", conn1);
var 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);
var db2 = (String)command.ExecuteScalar();
Assert.AreNotEqual(db1, db2);
}
}
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"));
}
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 PostgreSQLProvider(IDatabase db, bool createDatabaseIfNotExist)
{
this.db = db;
try
{
connection = new NpgsqlConnection(db.ConnectionString);
if (createDatabaseIfNotExist)
{
connection.Open();
connection.Close();
}
}
catch
{
if (createDatabaseIfNotExist)
{
// ihtimal, veritabaný create edilmemiþ. create edelim o zaman:
string newConnStr = "";
string dbName = "";
foreach (string param in db.ConnectionString.Split(';'))
{
if (param.StartsWith("Database=", StringComparison.InvariantCultureIgnoreCase))
dbName = param.Split('=')[1];
else
newConnStr += param + ";";
}
connection = new NpgsqlConnection(newConnStr);
try
{
connection.Open();
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "create database " + dbName + ";";
cmd.ExecuteNonQuery();
connection.ChangeDatabase(dbName);
CreatedNow = true;
connection.Close();
connection = new NpgsqlConnection(db.ConnectionString);
}
catch { }
}
}
}
