C# NpgsqlConnection.ChangeDatabase方法代码示例

		/// <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);
		}

		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 { }
         }
     }
 }