C# MySqlConnection.GetSchema方法代码示例

 public bool ConnectToDataBase(string strConnect)
 {
     sqlConnection = new MySqlConnection(strConnect.ToString());
     try
     {
         sqlConnection.Open();
         listTables = new List<string>();
         using (DataTable dt = sqlConnection.GetSchema("Tables"))
         {
             if (dt != null && dt.Rows.Count > 0)
             {
                 listTables.Capacity = dt.Rows.Count;
                 foreach (DataRow row in dt.Rows)
                     listTables.Add(row["table_name"].ToString());
             }
         }
         sqlConnection.Close();
         //string query = "select * from sys.tables where type_desc = 'USER_TABLE'";
     }
     catch (Exception ex)
     {
         System.Windows.Forms.MessageBox.Show(ex.Message);
         return false;
     }
     return true;
 }

 public void ListDatabases(bool showAll)
 {
     var connectionString = Settings.DefaultConnection.GetConnectionString();
     using (var connection = new MySqlConnection(connectionString))
     {
         Logger.WriteLine("Databases:");
         var databases = connection.GetSchema("Tables");
         foreach (DataRow database in databases.Rows)
         {
             Logger.WriteLine("Name: {0}", database["database_name"]);
         }
     }
 }

 public override IList<DataBaseSchema> GetDataBases(System.Data.Common.DbConnectionStringBuilder connectionstr)
 {
     IList<DataBaseSchema> list = null;
     using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString))
     {
         connection.Open();
         DataTable databases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases, connectionstr.DataBase());
         if (databases != null && databases.Rows.Count > 0)
         {
             list = new List<DataBaseSchema>();
             foreach (DataRow database in databases.Rows)
             {
                 string name = (string)database["database_name"];
                 DataBaseSchema db = new DataBaseSchema(name, name);
                 list.Add(db);
             }
         }
     }
     return list;
 }

        protected override void LoadChildren( )
        {
            string myConnectionString = "";

            using ( MySqlConnection connection = new MySqlConnection(myConnectionString) )
            {
                connection.Open( );
                DataTable schema = connection.GetSchema("Columns");

                List<string> TableNames = new List<string>( );

                //TODO use linq here

                foreach ( DataRow row in schema.Rows )
                {
                    if( row[2].ToString() == table_.TableName)
                        base.Children.Add(new FieldViewModel(new Field(row[3].ToString( )), this));
                }
            }
        }

        protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            MySqlConnection conn = connection as MySqlConnection;
            if (conn == null)
                throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection");

            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.ConnectionString = conn.ConnectionString;
            string dbName = builder.Database;
            builder.Database = "mysql";

            using (MySqlConnection c = new MySqlConnection(builder.ConnectionString))
            {
                c.Open();
                DataTable table = c.GetSchema("Databases", new string[] { dbName });
                if (table != null && table.Rows.Count == 1) return true;
                return false;
            }
        }

 public override IList<ColumnSchema> GetColumns(System.Data.Common.DbConnectionStringBuilder connectionstr, string tablename)
 {
     IList<ColumnSchema> list = null;
     using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString))
     {
         connection.Open();
         DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, tablename, null });
         if (columns != null && columns.Rows.Count > 0)
         {
             list = new List<ColumnSchema>();
             DataView dv = columns.DefaultView;
             dv.Sort = "ORDINAL_POSITION asc";
             foreach (DataRowView table in dv)
             {
                 string name = string.Format("{0}({1})", table["COLUMN_NAME"], table["DATA_TYPE"]);
                 ColumnSchema column = new ColumnSchema(name);
                 list.Add(column);
             }
         }
     }
     return list;
 }

        public static bool DatabaseHelperTableExists(string tableName)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString.GetWriteConnectionString()))
            {
                string[] restrictions = new string[4];
                restrictions[2] = tableName;
                connection.Open();
                DataTable table = connection.GetSchema("Tables", restrictions);
                connection.Close();
                if (table != null)
                {
                    return (table.Rows.Count > 0);
                }
            }

            return false;
        }

        private void comMySqlData_DropDown(object sender, EventArgs e)
        {
            if (this.comMySqlData.Items.Count != 0)
                return;

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = GetMySql();

            try
            {
                conn.Open();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(rm.GetString("Info75") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            DataTable tb = conn.GetSchema("Databases");

            foreach (DataRow r in tb.Rows)
            {

                this.comMySqlData.Items.Add(r[1].ToString());

            }
        }

        private void FillMySql()
        {
            if (this.comTableName.Items.Count != 0)
                return;

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = this.txtDataSource.Text;

            try
            {
                conn.Open();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(rm.GetString("Error12") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            DataTable tb = conn.GetSchema("Tables");

            foreach (DataRow r in tb.Rows)
            {

                this.comTableName.Items.Add(r[2].ToString());

            }
        }

        /// <summary>
        /// Returns the current version of the membership schema
        /// </summary>
        private static int GetSchemaVersion(MySqlConnection connection)
        {
            string[] restrictions = new string[4];
            restrictions[2] = "mysql_Membership";
            DataTable dt = connection.GetSchema("Tables", restrictions);
            if (dt.Rows.Count == 0)
                return 0;

            return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]);
        }

        private static DataSet GetProcData(MySqlConnection connection, string spName)
        {
            string schema = String.Empty;
            string name = spName;

            int dotIndex = spName.IndexOf(".");
            if (dotIndex != -1)
            {
                schema = spName.Substring(0, dotIndex);
                name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1);
            }

            string[] restrictions = new string[4];
            restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase();
            restrictions[2] = name;
            DataTable procTable = connection.GetSchema("procedures", restrictions);
            if (procTable.Rows.Count > 1)
                throw new MySqlException(Resources.ProcAndFuncSameName);
            if (procTable.Rows.Count == 0)
                throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema));

            DataSet ds = new DataSet();
            ds.Tables.Add(procTable);

            // we don't use GetSchema here because that would cause another
            // query of procedures and we don't need that since we already
            // know the procedure we care about.
            ISSchemaProvider isp = new ISSchemaProvider(connection);
            string[] rest = isp.CleanRestrictions(restrictions);
            try
            {
                DataTable parametersTable = isp.GetProcedureParameters(rest, procTable);
                ds.Tables.Add(parametersTable);
            }
            catch (Exception) { }

            return ds;
        }

        /// <summary>
        /// Executes the service
        /// </summary>
        /// <param name="values">The values.</param>
        /// <param name="theWorkspace">The workspace.</param>
        /// <returns></returns>
        public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace)
        {
            Dev2JsonSerializer serializer = new Dev2JsonSerializer();

            if(values == null)
            {
                throw new InvalidDataContractException("No parameter values provided.");
            }
            string database = null;
            StringBuilder tmp;
            values.TryGetValue("Database", out tmp);
            if(tmp != null)
            {
                database = tmp.ToString();
            }

            if(string.IsNullOrEmpty(database))
            {
                var res = new DbTableList("No database set.");
                Dev2Logger.Log.Debug("No database set.");
                return serializer.SerializeToBuilder(res);
            }

            DbSource dbSource;
            DbSource runtimeDbSource = null;
            try
            {
                dbSource = serializer.Deserialize<DbSource>(database);

                if(dbSource.ResourceID != Guid.Empty)
                {
                    runtimeDbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID);
                }
            }
            catch(Exception e)
            {
                Dev2Logger.Log.Error(e);
                var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message);
                return serializer.SerializeToBuilder(res);
            }
            if(runtimeDbSource == null)
            {
                var res = new DbTableList("Invalid Database source");
                Dev2Logger.Log.Debug("Invalid Database source");
                return serializer.SerializeToBuilder(res);
            }
            if(string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server))
            {
                var res = new DbTableList("Invalid database sent {0}.", database);
                Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database));
                return serializer.SerializeToBuilder(res);
            }

            try
            {
                Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName);
                var tables = new DbTableList();
                DataTable columnInfo;
                switch(dbSource.ServerType)
                {

                     case enSourceType.SqlDatabase:
                    {
                        using (var connection = new SqlConnection(dbSource.ConnectionString))
                        {
                            connection.Open();
                            columnInfo = connection.GetSchema("Tables");
                        }
                        break;
                    }
                    default:
                    {
                        using (var connection = new MySqlConnection(dbSource.ConnectionString))
                        {
                            connection.Open();
                            columnInfo = connection.GetSchema("Tables");
                        }
                        break;
                    }
                }
       
                if(columnInfo != null)
                {
                    foreach(DataRow row in columnInfo.Rows)
                    {
                        var tableName = row["TABLE_NAME"] as string;
                        var schema = row["TABLE_SCHEMA"] as string;
                        tableName = '[' + tableName + ']';
                        var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema);
                        if(dbTable == null)
                        {
                            dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List<IDbColumn>() };
                            tables.Items.Add(dbTable);
                        }
//.........这里部分代码省略.........

 public override IList<ViewSchema> GetViews(System.Data.Common.DbConnectionStringBuilder connectionstr)
 {
     IList<ViewSchema> list = null;
     using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString))
     {
         connection.Open();
         DataTable views = connection.GetSchema(SqlClientMetaDataCollectionNames.Views, new string[] { null, null, null,null});
         if (views != null && views.Rows.Count > 0)
         {
             list = new List<ViewSchema>();
             foreach (DataRow table in views.Rows)
             {
                 string name = string.Format("{0}", table["TABLE_NAME"]);
                 string des = string.Format("{0}.{1}", table["TABLE_SCHEMA"], table["TABLE_NAME"]);
                 ViewSchema view = new ViewSchema(name,des);
                 list.Add(view);
             }
         }
     }
     return list;
 }

        public void GetProcedureParametersDoesNotRequireSelectFromMySqlProceduresTable()
        {
            if (Version < new Version(5, 5, 3)) return;

              suExecSQL(String.Format("GRANT ALL ON `{0}`.* to 'simpleuser' identified by 'simpleuser'", database0));
              execSQL("DROP PROCEDURE IF EXISTS spTest");
              execSQL(@"CREATE  PROCEDURE spTest(id INT, name VARCHAR(20))
                    BEGIN SELECT name; END");

              string connStr = GetConnectionString("simpleuser", "simpleuser", true) + ";use procedure bodies=false";

              using (MySqlConnection c = new MySqlConnection(connStr))
              {
            c.Open();

            string[] restrictions = new string[4];
            restrictions[1] = c.Database;
            restrictions[2] = "spTest";
            DataTable procTable = c.GetSchema("procedures", restrictions);
            ISSchemaProvider isp = new ISSchemaProvider(c);
            string[] rest = isp.CleanRestrictions(restrictions);

            MySqlSchemaCollection parametersTable = isp.GetProcedureParameters(rest, new MySqlSchemaCollection( procTable ));

            Assert.IsNotNull(parametersTable);
              }
        }

 private bool GetMySQLDataBases(ref bool bInDataBase, string DataBaseToFind)
 {
     string conxString = m_SQL_Connection.ServerConnectionString;
     try
     {
         using (MySqlConnection sqlConx = new MySqlConnection(conxString))
         {
             sqlConx.Open();
             DataTable tblDatabases = sqlConx.GetSchema("Databases");
             sqlConx.Close();
             bInDataBase = false;
             this.listBox_DataBaseNames.Items.Clear();
             foreach (DataRow row in tblDatabases.Rows)
             {
                 string DataBaseName;
                 DataBaseName = row["database_name"].ToString();
                 this.listBox_DataBaseNames.Items.Add(DataBaseName);
                 if (DataBaseToFind != null)
                 {
                     if (DataBaseToFind.Length > 0)
                     {
                         if (DataBaseName.Equals(DataBaseToFind))
                         {
                             bInDataBase = true;
                         }
                     }
                 }
                 //Console.WriteLine("Database: " + row["database_name"]);
             }
             return true;
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(this, lngConn.s_Error_Can_not_get_Databases_on_Server.s + txt_ServerName.Text + "\n" + lngConn.s_Execption.s + " = " + ex.Message + "\"", lngConn.s_Error.s, MessageBoxButtons.OK, MessageBoxIcon.Error);
         return false;
     }
 }