C# MySqlDataAdapter.FillSchema方法代码示例

 /// <summary>
 /// 执行sql返回dataable
 /// </summary>
 /// <param name="sql"></param>
 /// <returns></returns>
 private DataTable ExecuteDataTable(string sql)
 {
     DataSet ds = new DataSet();
     try
     {
         switch (sqltype)
         {
             case 1:
                 using (SqlConnection conn = new SqlConnection(cononstr))
                 {
                     conn.Open();
                     using (SqlCommand cmd = conn.CreateCommand())
                     {
                         cmd.CommandText = sql;
                         using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                         {
                             adapter.FillSchema(ds, SchemaType.Source);
                             adapter.Fill(ds);
                         }
                     }
                 }
                 break;
             case 2:
                 using (MySqlConnection conn = new MySqlConnection(cononstr))
                 {
                     conn.Open();
                     using (MySqlCommand cmd = conn.CreateCommand())
                     {
                         cmd.CommandText = sql;
                         using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                         {
                             adapter.FillSchema(ds, SchemaType.Source);
                             adapter.Fill(ds);
                         }
                     }
                 }
                 break;
             default: break;
         }
         return ds.Tables[0];
     }
     catch (Exception ex)
     {
         this.Dispatcher.Invoke(delegate
         {
             tbModel.Text = "连接字符串填写错误，请检查。\n错误信息：" + ex.Message + "\n" + ex.ToString();
             tbDAL.Text = ex.Message + "\n" + ex.ToString();
         });
         ConnectSqlFailed();
         return null;
     }
     finally
     {
         this.Dispatcher.Invoke(delegate { btnConnect.IsEnabled = true; tbConnStr.IsEnabled = true; });
     }
 }

 public Table GetTableMetadata(string tableName)
 {
     string selectCmdText = string.Format("SELECT * FROM {0}", tableName); ;
     MySqlCommand command = new MySqlCommand(selectCmdText, conn);
     MySqlDataAdapter ad = new MySqlDataAdapter(command);
     System.Data.DataSet ds = new DataSet();
     ad.FillSchema(ds, SchemaType.Mapped, tableName);
     Table table = new Table(ds.Tables[0]);
     return table;
 }

        public void GetSchema()
        {
            if (Version < new Version(5, 0)) return;

            execSQL("CREATE PROCEDURE spTest() BEGIN SELECT * FROM Test; END");
            execSQL(@"CREATE TABLE Test(id INT AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id)) ");

            MySqlCommand cmd = new MySqlCommand("spTest", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
            reader.Read();
            reader.Close();

            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            DataTable schema = new DataTable();
            da.FillSchema(schema, SchemaType.Source);
            Assert.AreEqual(2, schema.Columns.Count);
        }

        public void GetSubCategoryMasterDetail()
        {
            try
            {
                //DataSet oDS = new DataSet();
                if (oDS_Main.Tables.Contains("SubCategory_Table"))
                {
                    oDS_Main.Tables["SubCategory_Table"].Rows.Clear();
                }
                MySqlConnection conn = new MySqlConnection(BG_Db_Class.getConnectionString());
                conn.Open();


                //***************For Sub Category Table **************************************************
                MySqlCommand cmd3 = new MySqlCommand("SELECT * FROM SubCategory_Table Order By Site_Id", conn);
                MySqlDataAdapter oSiteDataAdapter3 = new MySqlDataAdapter(cmd3);
                MySqlCommandBuilder oSiteTableCmdBuilder3 = new MySqlCommandBuilder(oSiteDataAdapter3);
                oSiteDataAdapter3.FillSchema(oDS_Main, SchemaType.Source);
                oSiteDataAdapter3.Fill(oDS_Main, "SubCategory_Table");
                DataTable SubCatTable = oDS_Main.Tables["SubCategory_Table"];
                SubCatTable.TableName = "SubCategory_Table";
                Log("SubCategory Loading Please wait");
            }
            catch (Exception ex)
            {
                //return null;
            }
            finally
            {
                //conn.Close();
                Log("SubCategory Master Loaded");
            }

        }

        private void GetData()
        {
            table_names = new String[] {
                "client_balance",
                "client_cashflow",
                "client_cashflow_type",
                "client_info",
                "commodity_category",
                "futures_account_balance",
                "futures_account_info",
                "futures_cashflow",
                "futures_cashflow_type",
                "futures_contracts",
                "futures_transactions",
                "futures_verbose_positions",
                "options_contracts",
                "options_direction_type",
                "options_transactions",
                "options_types",
                "options_verbose_positions"
            };

            view_names = new String[]
            {
                "client_balance_join",
                "client_cashflow_view",
                "commodity_category_view",
                "futures_account_balance_view",
                "futures_cashflow_view",
                "futures_contracts_view",
                "futures_positions_summary",
                "futures_transactions_view",
                "futures_verbose_positions_view",
                "options_contracts_view",
                "options_direction_type_view",
                "options_positions_summary",
                "options_transactions_view",
                "options_types_view",
                "options_verbose_positions_view",
                "non_trade_dates",
                "business_state_view",
                "option_settle_info_view",
                "future_settle_info_view",
                "business_current_state",
                "option_position_settle_info",
                "future_position_settle_info",
                "business_overview"
            };
            String selectString = "";
            foreach (String t in table_names)
            {
                selectString = String.Format("select * from {0};", t);
                MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                command.CommandType = System.Data.CommandType.Text;
                adapter.SelectCommand = command;
                MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                adapter.FillSchema(this, System.Data.SchemaType.Source, t);
                adapter.FillSchema(display_ds, SchemaType.Source, t);
                adapter.Fill(this, t);
                adapter.Fill(display_ds, t);
                adapterDict.Add(t, adapter);
            }
            foreach (String t in view_names)
            {
                selectString = String.Format("select * from {0};", t);
                if (t == "futures_verbose_positions_view")
                    selectString = "SELECT * FROM futures_verbose_positions;";
                else if (t == "business_current_state")
                    selectString = "SELECT * FROM accum_business_pnl ORDER BY settle_day DESC LIMIT 1";
                MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                command.CommandType = System.Data.CommandType.Text;
                adapter.SelectCommand = command;
                if (t == "non_trade_dates")
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
                    adapter.InsertCommand = builder.GetInsertCommand();
                    adapter.UpdateCommand = builder.GetUpdateCommand();
                    adapter.DeleteCommand = builder.GetDeleteCommand();
                }
                adapter.FillSchema(this, System.Data.SchemaType.Source, t);
                adapter.FillSchema(display_ds, SchemaType.Source, t);
                adapter.Fill(this, t);
                adapter.Fill(display_ds, t);
                adapterDict.Add(t, adapter);
            }
            foreach (System.Data.DataTable table in this.Tables)
            {
                foreach (System.Data.DataColumn col in table.Columns)
                {
                    col.AllowDBNull = true;
                }
            }
            foreach (System.Data.DataTable table in this.display_ds.Tables)
            {
                foreach (System.Data.DataColumn col in table.Columns)
//.........这里部分代码省略.........

        public void GetSchema()
        {
            if (version < new Version(5, 0)) return;

            try
            {
                execSQL("CREATE PROCEDURE spTest() BEGIN SELECT * FROM Test; END");

                MySqlCommand cmd = new MySqlCommand("spTest", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                reader.Read();
                reader.Close();

                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                DataTable schema = new DataTable();
                da.FillSchema(schema, SchemaType.Source);
                Assert.AreEqual(2, schema.Columns.Count);

                //Bug #27668  	FillSchema and Stored Proc with an out parameter
                execSQL("DROP TABLE IF EXISTS Test");
                execSQL(@"CREATE TABLE Test(id INT AUTO_INCREMENT, PRIMARY KEY (id)) ");
                execSQL("DROP PROCEDURE IF EXISTS spTest");
                execSQL(@"CREATE PROCEDURE spTest (OUT id INT)
                    BEGIN INSERT INTO Test VALUES (NULL); SET id=520; END");

                cmd = new MySqlCommand("spTest", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("?id", MySqlDbType.Int32);
                cmd.Parameters[0].Direction = ParameterDirection.Output;
                da = new MySqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                cmd.ExecuteNonQuery();
                da.Fill(dt);
                da.FillSchema(dt, SchemaType.Mapped);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
        }

        /// <summary>
        ///Searches the table adressen for a given Searchstring
        ///and inserts a "tblSugarSearchHuman" into the DataSet.</summary>
        /// <param name="searchstring">the search string</param>
        public void search_ds_human_persons(string searchstring)
        {
            StringBuilder m_strCommand = new StringBuilder();
            //SELECT `id`, `date_entered`, `date_modified`, `modified_user_id`, `created_by`, LEFT(`description`, 256), `deleted`, `assigned_user_id`, `salutation`, `first_name`, `last_name`, `title`, `department`, `do_not_call`, `phone_home`, `phone_mobile`, `phone_work`, `phone_other`, `phone_fax`, `primary_address_street`, `primary_address_city`, `primary_address_state`, `primary_address_postalcode`, `primary_address_country`, `alt_address_street`, `alt_address_city`, `alt_address_state`, `alt_address_postalcode`, `alt_address_country`, `assistant`, `assistant_phone`, `lead_source`, `reports_to_id`, `birthdate`, `campaign_id` FROM `sugarcrm`.`contacts` LIMIT 0, 1000;
            m_strCommand.Append("select salutation as Anrede, first_name as Vorname, last_name as Nachname, primary_address_street as Strasse, primary_address_postalcode as PLZ, primary_address_city as Ort, phone_home as Telefon, phone_mobile as Mobile from contacts where last_name like ");
            m_strCommand.Append("'%");
            m_strCommand.Append(searchstring);
            m_strCommand.Append("%' and deleted = 0;");

            MySqlCommand m_cmdSearchCommand = new MySqlCommand(m_strCommand.ToString());
            m_daSugar = new MySqlDataAdapter(m_cmdSearchCommand.CommandText, m_cnSugar);
            m_daSugar.FillSchema(m_dsSugar, SchemaType.Source, m_const_strSugarTableSearchHuman);
            m_daSugar.Fill(m_dsSugar, m_const_strSugarTableSearchHuman);

            //return m_dsSugar;
        }

        /// <summary>
        /// Searches for Contacts by Street.
        /// Table: tblSugarSearchAllCompanies</summary>
        /// <param name="searchstring">the search string</param>
        public void SearchContactsByStreet(string searchstring)
        {
            StringBuilder m_strCommand = new StringBuilder();
            m_strCommand.Append("select * from contacts where primary_address_street like ");
            m_strCommand.Append("'%");
            m_strCommand.Append(searchstring);
            m_strCommand.Append("%' and deleted = 0;");

            MySqlCommand m_cmdSearchCommand = new MySqlCommand(m_strCommand.ToString());
            m_daSugar = new MySqlDataAdapter(m_cmdSearchCommand.CommandText, m_cnSugar);
            m_daSugar.FillSchema(m_dsSugar, SchemaType.Source, m_const_strSugarTableSearchAllPrivate);
            m_daSugar.Fill(m_dsSugar, m_const_strSugarTableSearchAllPrivate);
        }

        /// <summary>
        /// Get Table Schema Definition from MySQL
        /// </summary>
        /// <param name="ConnString"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        private static DataTable GetMySQLSchemaTypeTable(string ConnString, string TableName)
        {
            DataTable ReturnTable = null;
            MySqlConnection MySqlConn = null;

            try
            {
                using (MySqlConn = new MySqlConnection(ConnString))
                {                    
                    MySqlCommand MySqlCmd = new MySqlCommand(TableName, MySqlConn);
                    MySqlCmd.CommandType = CommandType.TableDirect;

                    MySqlDataAdapter MySqlDataAdapt = new MySqlDataAdapter(MySqlCmd);

                    DataSet MyDataSet = new DataSet();
                    MySqlDataAdapt.FillSchema(MyDataSet, SchemaType.Source, TableName);

                    if (MyDataSet.Tables != null && MyDataSet.Tables.Count > 0)
                    {
                        ReturnTable = MyDataSet.Tables[0];
                    }
                }
            }
            catch (Exception ex)
            {
                string ErrorMessage = string.Format("MemcachedLoaderService. Error Retrieving Table Specs from MySQL Connection. TableName is [{0}]. Error Message is [{1}].", TableName, ex.Message);
                Utils.GetEventLog().WriteEntry(ErrorMessage);
            }
            finally
            {
                if (MySqlConn != null)
                    MySqlConn.Close();
            }

            return ReturnTable;
        }

 /// <summary>
 /// 执行返回DataSet
 /// </summary>
 /// <param name="con"></param>
 /// <param name="cmdText"></param>
 /// <param name="parameters"></param>
 /// <returns></returns>
 public static DataSet ExecuteDataSet(MySqlConnection con, string cmdText, params MySqlParameter[] parameters)
 {
     using (MySqlCommand cmd = con.CreateCommand())
     {
         cmd.CommandText = cmdText;
         cmd.Parameters.AddRange(parameters);
         using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             adapter.FillSchema(ds, SchemaType.Source);
             adapter.Fill(ds);
             return ds;
         }
     }
 }

        /// <summary>
        /// Execute a MySqlCommand (that returns a resultset) against the specified MySqlConnection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">A valid MySqlConnection</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public static DataSet ExecuteDataset(IDbConnection connection, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            MySqlCommand setformat = new MySqlCommand("SET NAMES latin1;set character_set_database = big5;", connection as MySqlConnection);
            setformat.ExecuteNonQuery();
            setformat.Dispose();

            // Create a command and prepare it for execution
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection as MySqlConnection, (MySqlTransaction)null, commandType, commandText, commandParameters as MySqlParameter[], out mustCloseConnection);

            // Create the DataAdapter & DataSet
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet("DataSet");
                da.FillSchema(ds, SchemaType.Source);

                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    if (col.DataType.Name.Equals("MySqlDateTime"))
                    {
                        col.DataType = Type.GetType("System.DateTime");
                    }
                }
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds);

                // Detach the MySqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                // Return the dataset
                return ds;
            }
        }

        // 创建据有 Table-Schema 的 DataTable 实例
        public DataTable NewTable()
        {
            MySqlDataAdapter adpt = new MySqlDataAdapter("SELECT * FROM " + this.TableName, this.sqlConn);
            DataTable dt = new DataTable();
            adpt.FillSchema(dt, SchemaType.Mapped);

            return dt;
        }

 public void GetSchema(DataTable dt)
 {
     MySqlConnection connection = new MySqlConnection(_sqlconn);
     MySqlDataAdapter adpter = new MySqlDataAdapter(_sqlcomm, connection);
     adpter.FillSchema(dt, SchemaType.Source);
 }

        public DataTable ToDataTable(System.Data.Entity.DbContext ctx, string sql)
        {
            IDbCommand cmd = ctx.Database.Connection.CreateCommand();
            MySqlDataAdapter adapter = new MySqlDataAdapter(sql,ctx.Database.Connection.ConnectionString);
            DataTable dt = new DataTable("sd");

            try
            {
                cmd.Connection.Open();
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt);
            }
            finally
            {
                cmd.Connection.Close();
            }
            return dt;
        }

 public override DataTable ObtenerEsquema(string tabla)
 {
     MySqlDataAdapter da = new MySqlDataAdapter("SELECT * from " + tabla, _connection);
     DataTable dt = new DataTable();
     return da.FillSchema(dt, SchemaType.Source);
 }