C# OleDbConnection.ChangeDatabase方法代码示例

		private void LoadDescriptions()
		{
			try
			{
				string select = @"SELECT objName, value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'procedure', null, null, null)";

				OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase("[" + this.Database.Name + "]");

				OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
				DataTable dataTable = new DataTable();

				adapter.Fill(dataTable);

				cn.Close();

				Procedure p;

				foreach(DataRow row in dataTable.Rows)
				{
					p = this[row["objName"] as string] as Procedure;

					if(null != p)
					{
						p._row["DESCRIPTION"] = row["value"] as string;
					}
				}
			}
			catch
			{
			
			}
		}

	public void run()
	{
		Exception exp = null;
		OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();
		OleDbCommand cmd = new OleDbCommand("Select * From Orders", con);
		OleDbDataReader rdr = cmd.ExecuteReader();

		//change a connection's state without closing the datareader (should fail
		try
		{
			BeginCase("InvalidOperationException");
			try
			{
				con.ChangeDatabase("msdb");
				ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName);
			}
			catch (InvalidOperationException ex) 
			{
				ExpectedExceptionCaught(ex);
			}
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		if (con.State == ConnectionState.Open) con.Close();
	}

		private void LoadSubViewInfo()
		{
			_views  = (Views)this.dbRoot.ClassFactory.CreateViews();
			_tables = (Tables)this.dbRoot.ClassFactory.CreateTables();

			try
			{
				string select = "SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE VIEW_NAME = '" 
					+ this.Name + "' AND VIEW_SCHEMA = '" + this.Schema + "';";
	
				OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase(this.Database.Name);

				OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
				DataTable dataTable = new DataTable();

				adapter.Fill(dataTable);

				cn.Close();

				string entity = "";

				Table table;
				View view;

				foreach(DataRow row in dataTable.Rows)
				{
					entity = row["TABLE_NAME"] as string;

					// It might be a table or a view
					table = this.Database.Tables[entity] as Table;

					if(null != table)
					{
						// It's a table
						_tables.AddTable(table);
					}
					else
					{
						// Check for View
						view = this.Database.Views[entity] as View;

						if(null != view)
						{
							// It's a table
							_views.AddView(view);
						}
					}
				}
			}
			catch {}
		}

		override internal void LoadAll()
		{
			try
			{
				string select = "SELECT * FROM INFORMATION_SCHEMA.DOMAINS";

				OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase("[" + this.Database.Name + "]");
	
				OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
				DataTable metaData = new DataTable();

				adapter.Fill(metaData);
				cn.Close();

				PopulateArray(metaData);
			}
			catch {}
		}

	public void run()
	{
		Exception exp = null;

		OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();

		//Currently not running on Oracle/DB2: .Net-Failed, GH:Pass
		//not runnig on postgres because "The 'current catalog' property is not supported by the 'Mainsoft.JDBC.OLEDB.1' provider."
		if (ConnectedDataProvider.GetDbType(con) != DataBaseServer.Oracle  &&
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.DB2 && 
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.Sybase &&
			ConnectedDataProvider.GetDbType(con) != DataBaseServer.PostgreSQL)
		{
			try
			{
				BeginCase("Change DataBase");
				con.ChangeDatabase("GHTDB_EX");
				Compare(con.Database , "GHTDB_EX");
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			try
			{
				BeginCase("Check DataBase Changed ");
				OleDbCommand cmd = new OleDbCommand("select count(*) from Customers", con);
				object obj = cmd.ExecuteScalar();
				Compare(obj != null, true);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}

	}

		private string LoadViewSource()
		{
			string text = string.Empty;
			OleDbConnection cn = null;
			OleDbDataReader reader = null;
			try
			{
				string select = string.Format(@"SELECT CASE WHEN encrypted = 1 THEN NULL ELSE com.text END as Source FROM sysobjects o, syscomments com 
WHERE o.id = object_id(N'[{0}].[{1}]')
and com.id=o.id 
and com.status=2 
order by colid;", this.Schema, this.Name);
				cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase(this.Database.Name);

				OleDbCommand cmd = cn.CreateCommand();
				cmd.CommandText = select;

                try
                {
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        text += reader[0].ToString();
                    }

                    reader.Close();
                }
                catch
                {
                    if (reader != null)
                        reader.Close();
                }

                if (text == string.Empty)
                {
					select = string.Format(@"SELECT CASE WHEN encrypted = 1 THEN NULL ELSE com.text END as Source FROM sysobjects o, syscomments com 
WHERE o.id = object_id(N'[{0}].[{1}]')
and com.id=o.id 
order by colid;", this.Schema, this.Name);

                    cmd = cn.CreateCommand();
                    cmd.CommandText = select;
                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        text += reader[0].ToString();
                    }

                    reader.Close();
                    cn.Close();
                }

                cn.Close();

				text = text.TrimStart(' ', '\r', '\n', '\t');
			}
			catch 
			{
				if (reader != null) 
				    reader.Close();
				if ((cn != null) && (cn.State != ConnectionState.Closed) && (cn.State != ConnectionState.Broken) )
					cn.Close();
			}

			return text;
		}

		private void LoadExtraData(string name, string type)
		{
			try
			{
				string dbName = ("T" == type) ? this.Table.Database.Name : this.View.Database.Name;
				string schema = ("T" == type) ? this.Table.Schema : this.View.Schema;
				string select = "EXEC [" + dbName + "].dbo.sp_columns '" + name + "', '" + schema + "'";

                using (OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString))
                {
                    cn.Open();
                    cn.ChangeDatabase("[" + dbName + "]");

                    OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
                    DataTable dataTable = new DataTable();

                    adapter.Fill(dataTable);

                    if (this._array.Count > 0)
                    {
                        Column col = this._array[0] as Column;

                        f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
                        col._row.Table.Columns.Add(f_TypeName);

                        string typeName = "";
                        DataRowCollection rows = dataTable.Rows;

                        int count = this._array.Count;
                        Column c = null;

                        for (int index = 0; index < count; index++)
                        {
                            c = (Column)this[index];

                            typeName = rows[index]["TYPE_NAME"] as string;

                            if (typeName.EndsWith(" identity"))
                            {
                                typeName = typeName.Replace(" identity", "");
                                typeName = typeName.Replace("()", "");
                                c._row["TYPE_NAME"] = typeName;
                            }
                            else
                            {
                                c._row["TYPE_NAME"] = typeName;
                            }
                        }
                    }

                    select = @"select COLUMN_NAME, DATA_TYPE from 
INFORMATION_SCHEMA.COLUMNS 
where table_schema = '" + schema + @"' 
and table_catalog='" + dbName + @"' and table_name='" + name + @"' 
and DATA_TYPE in ('nvarchar', 'varchar', 'varbinary') 
and character_maximum_length=-1 and character_octet_length=-1;";

                    adapter = new OleDbDataAdapter(select, cn);
                    dataTable = new DataTable();

                    adapter.Fill(dataTable);

                    Column colz = null;
                    foreach (DataRow row in dataTable.Rows)
                    {
                        colz = this[row["COLUMN_NAME"] as string] as Column;

                        if (null != colz)
                        {
                            colz._row["TYPE_NAME"] = row["DATA_TYPE"] as string;
                        }
                    }

                    cn.Close();
                }
			}
			catch {}
		}

		private void LoadAutoKeyInfo()
		{
			try
			{
				string select =
@"SELECT TABLE_NAME, COLUMN_NAME, IDENT_SEED('[" + this.Table.Name + "]') AS AUTO_KEY_SEED, IDENT_INCR('[" + this.Table.Name + "]') AS AUTO_KEY_INCREMENT " +
"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + this.Table.Name + "' AND TABLE_SCHEMA = '" + this.Table.Schema + "' AND " + 
"columnproperty(object_id('[" + this.Table.Schema + "].[" + this.Table.Name + "]'), COLUMN_NAME, 'IsIdentity') = 1";

				OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase("[" + this.Table.Database.Name + "]");

				OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
				DataTable dataTable = new DataTable();

				adapter.Fill(dataTable);
				cn.Close();

				if(dataTable.Rows.Count > 0)
				{
					f_AutoKeySeed		= new DataColumn("AUTO_KEY_SEED", typeof(System.Int32));
					f_AutoKeyIncrement  = new DataColumn("AUTO_KEY_INCREMENT", typeof(System.Int32));
					f_AutoKey           = new DataColumn("AUTO_INCREMENT", typeof(Boolean));

					Column col = this._array[0] as Column;
					col._row.Table.Columns.Add(f_AutoKeySeed);
					col._row.Table.Columns.Add(f_AutoKeyIncrement);
					col._row.Table.Columns.Add(f_AutoKey);

					DataRowCollection rows = dataTable.Rows;
					DataRow row;

					for(int i = 0; i < rows.Count; i++)
					{
						row = rows[i];

						col = this[row["COLUMN_NAME"]] as Column;

						col._row["AUTO_KEY_SEED"]	   = row["AUTO_KEY_SEED"];
						col._row["AUTO_KEY_INCREMENT"] = row["AUTO_KEY_INCREMENT"];
						col._row["AUTO_INCREMENT"]     = true;
					}
				}
			}
			catch(Exception ex)
			{
				string s = ex.Message;
			}
		}

		private void LoadDescriptions()
		{
			try
			{
				string select = @"SELECT objName, value 
FROM ::fn_listextendedproperty ('MS_Description', 'user', '" + this.Table.Schema + @"', 'table', '" + this.Table.Name + @"', 'column', null)
UNION
SELECT objName, value 
FROM ::fn_listextendedproperty ('MS_Description', 'schema', '" + this.Table.Schema + @"', 'table', '" + this.Table.Name + @"', 'column', null)";

				OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
				cn.ChangeDatabase("[" + this.Table.Database.Name + "]");

				OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn);
				DataTable dataTable = new DataTable();

				adapter.Fill(dataTable);
				cn.Close();

				Column c;

				foreach(DataRow row in dataTable.Rows)
				{
					c = this[row["objName"] as string] as Column;

					if(null != c)
					{
						c._row["DESCRIPTION"] = row["value"] as string;
					}
				}
			}
			catch(Exception ex)
			{
				string s = ex.Message;
			}
		}

		virtual public ADODB.Recordset ExecuteSql(string sql)
		{
			Recordset oRS = new Recordset();
			OleDbConnection cn = null;
			OleDbDataReader reader = null;

			try 
			{
				cn = new OleDbConnection(dbRoot.ConnectionString);
				cn.Open();
                try
                {
                    cn.ChangeDatabase(this.Name);
                }
                catch { } // some databases don't have the concept of catalogs. Catch this and throw it out
                
				OleDbCommand command = new OleDbCommand(sql, cn);
				command.CommandType = CommandType.Text;

				reader = command.ExecuteReader();

				DataTable schema;
				string dataType, fieldname;
				int length;
				bool firstTime = true;

				while (reader.Read()) 
				{
					if (firstTime) 
					{
						schema = reader.GetSchemaTable();

						foreach (DataRow row in schema.Rows) 
						{
							fieldname = row["ColumnName"].ToString();
							dataType = row["DataType"].ToString();
							length = Convert.ToInt32(row["ColumnSize"]);

							oRS.Fields.Append(fieldname, GetADOType(dataType), length, 
								FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value);
						}

						oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
							CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1);

						firstTime = false;
					}
					oRS.AddNew(System.Reflection.Missing.Value,	System.Reflection.Missing.Value);

					for(int i = 0; i < reader.FieldCount; i++)
					{

						if (reader[i] is System.Guid)
						{
							oRS.Fields[i].Value = "{" + reader[i].ToString() + "}";
						}
						else
						{
							oRS.Fields[i].Value = reader[i];
						}
					}
				}

				cn.Close();
				//Move to the first record
				if (!firstTime) 
				{
					oRS.MoveFirst();
				}
				else 
				{
					oRS = null;
				}
			}
			catch (Exception ex) 
			{
				if ((reader != null) && (!reader.IsClosed)) 
				{
					reader.Close();
					reader = null;
				}
				if ((cn != null) && (cn.State == ConnectionState.Open)) 
				{
					cn.Close();
					cn = null;
				}
				throw ex;
			}

			return oRS;
		}