C# SqlDataAdapter.FillSchema方法代码示例

        private void connectToServer()
        {
            SqlConnection conn = Connection.getConnection();
            
            try
            {
                conn.Open();

                tradeDataAdapter = new SqlDataAdapter("SELECT * FROM tb_trade", conn);
                saleDataAdapter = new SqlDataAdapter("SELECT * FROM tb_sale", conn);
                vipDataAdapter = new SqlDataAdapter("SELECT * FROM tb_vip", conn);

                //先获取所有商品信息填充到dataset去
                SqlCommand cmd = new SqlCommand("SELECT * FROM view_commodity_cashier", conn);
                SqlDataReader reader = cmd.ExecuteReader();

                DataTable tb_commodity = new DataTable("commodity");
                tb_commodity.Load(reader);

                dataset.Tables.Add(tb_commodity);
                reader.Close();

                //获取tb_trade表
                tradeDataAdapter.Fill(dataset, "trade");

                //获取tb_sale表
                saleDataAdapter.Fill(dataset, "sale");

                //获取tb_vip表
                vipDataAdapter.Fill(dataset, "vip");

                StringBuilder builder = new StringBuilder();
                //获取当前的购物表模式
                //拼接select语句
                builder.Append("SELECT ");
                builder.Append("tb_commodity.s_code 条形码, ");
                builder.Append("tb_commodity.s_name 商品名, ");
                builder.Append("tb_commodity.s_price 商品单价, ");
                builder.Append("tb_sale.sale_count 数量, ");
                builder.Append("tb_sale.sale_amount 合计  ");
                builder.Append("FROM tb_commodity,tb_sale");

                SqlDataAdapter shoplistAdapter = new SqlDataAdapter(builder.ToString(), conn);
                shoplistAdapter.FillSchema(dt, SchemaType.Mapped);

                SqlCommandBuilder cmdBuilder1 = new SqlCommandBuilder(tradeDataAdapter);
                SqlCommandBuilder cmdBuilder2 = new SqlCommandBuilder(saleDataAdapter);
                SqlCommandBuilder cmdBuilder3 = new SqlCommandBuilder(vipDataAdapter);
                SqlCommandBuilder cmdBuilder4 = new SqlCommandBuilder(shoplistAdapter);

            }
            catch (Exception e)
            {
                MessageBox.Show("连接至服务器失败，请检查连接！错误："+e.ToString(), "错误");
            }
            finally
            {
                conn.Close();
            }
        }

    /// <summary>
    /// Gets all customers in the database
    /// </summary>
    /// <returns>DataTable with all customers in database</returns>
    private DataTable getAllCountries(string startsWith)
    {
        // Create and instantiate connection
        using (customerManagementConnection = new SqlConnection())
        {
            // Initialize connection string from web.config
            customerManagementConnection.ConnectionString =
                ConfigurationManager.ConnectionStrings[
                "CustomerManagementConnectionString"].ConnectionString;

            // Open connection
            customerManagementConnection.Open();

            // Declare and instantiate data adapter
            using (SqlDataAdapter customerManagementDataAdapter = new SqlDataAdapter())
            {
                // Declare and instantiate command objects
                SqlCommand selectCommand = new SqlCommand(
                    "SELECT * FROM Countries WHERE Name LIKE '" + startsWith + "%'", customerManagementConnection);
                // Assign command objects
                customerManagementDataAdapter.SelectCommand = selectCommand;

                // Declare and instantiate DataTable
                countriesDataTable = new DataTable("Countries");
                // Apply the full schema from the data source
                customerManagementDataAdapter.FillSchema(countriesDataTable, SchemaType.Source);
                customerManagementDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                customerManagementDataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
                // Populate Customers DataTable
                customerManagementDataAdapter.Fill(countriesDataTable);
            }
        }

        return countriesDataTable;
    }

        static DataSet FillDataSet(string SQLquery)
        {
            string sConnectionString = common.getConnectionString();

            SqlConnection objConn
                = new SqlConnection(sConnectionString);
            objConn.Open();

            SqlDataAdapter daAuthors
                = new SqlDataAdapter(SQLquery, objConn);
            DataSet ds = new DataSet("Area");
            daAuthors.FillSchema(ds, SchemaType.Source, "AreaTable");
            daAuthors.Fill(ds, "AreaTable");

            /*DataTable tblAuthors;
            tblAuthors = ds.Tables["AreaTable"];

            foreach (DataRow drCurrent in tblAuthors.Rows)
            {
                Console.WriteLine("{0} {1}",
                    drCurrent["ID"].ToString(),
                    drCurrent["Name"].ToString());
            }
            Console.ReadLine();*/
            return ds;
        }

        public static List<OrderDetail> OrderDetailList(int OrderID)
        {
            //Copied code from Nwind.cs VVVV
            List<OrderDetail> OrdDetList = new List<OrderDetail>();

            sqlCon = new SqlConnection(connectionString);
            sqlCon.Open();

            SqlDataAdapter da;
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(PROC_ORD_DETAILS, sqlCon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@OrderID", OrderID));

            da = new SqlDataAdapter(cmd);
            da.FillSchema(dt, SchemaType.Source);
            da.Fill(dt);

            sqlCon.Close();
            //Copied code^^^^
            foreach (DataRow row in dt.Rows)
            {
                //int OrderID = (int)row["OrderID"];
                //string ProductName = (string)row["ProductName"];
                int ProductID = (int)row["ProductID"];
                decimal UnitPrice = (decimal)row["UnitPrice"];
                short Quantity = (short)row["Quantity"];
                float Discount = (float)row["Discount"];

                OrderDetail OrD = new OrderDetail(OrderID,ProductID,UnitPrice,Quantity,Discount);
                OrdDetList.Add(OrD);
            }
      /*put code here */
            return OrdDetList;
        }

        public DataTable Consultar(string query)
        {
            DataTable dt = new DataTable();

            cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = query;
            cmd.CommandTimeout = 10;

            try
            {
                cmd.Connection = getConnection();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.FillSchema(dt, SchemaType.Source);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnn.Close();
            }

            return dt;
        }

 public DataTable getAllStudents()
 {
     SqlConnection conn = null;
     DataTable table = null;
     SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
     using (conn = new SqlConnection(connStr))
     {
         try
         {
             conn.Open();
             String query = "SelectProcedure1";
             SqlCommand com = new SqlCommand(query, conn);
             com.CommandType = CommandType.StoredProcedure;
             sqlDataAdapter.SelectCommand = com;
             DataSet dataSet = new DataSet();
             sqlDataAdapter.FillSchema(dataSet, SchemaType.Source, "Registration");
             sqlDataAdapter.Fill(dataSet, "Registration");
             table = dataSet.Tables["Registration"];
             return table;
         }
         catch (SqlException ex)
         {
           Console.WriteLine(ex.ToString(), "Sql Exception");
             return null;
         }
         finally
         {
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }

        /// <summary>
        ///    /************************************************************************************************
        ///    Populate CUSTOMER table
        ///    By Wesley D. Brown
        ///    Date 11/24/2007
        ///    Mod
        ///    **Description**
        ///    Functions:
        ///    **End Discription**
        ///    **Change Log**
        ///    **End Change Log**
        ///    ************************************************************************************************/
        /// </summary>
        /// <returns></returns>
        public void BuildCustomerTable()
        {
            var currdate = new DateTime();
            var dt = new DataTable();

            var sqlConnect = new SqlConnection {ConnectionString = Sqlconn};
            sqlConnect.Open();

            var dc = new SqlCommand("select top 0 * from dbo.CUSTOMER", sqlConnect) {CommandType = CommandType.Text};
            var da = new SqlDataAdapter(dc);
            da.FillSchema(dt, SchemaType.Mapped);

            sqlConnect.Close();
            sqlConnect.Dispose();

            var bulkCopy = new SqlBulkCopy(Sqlconn,SqlBulkCopyOptions.TableLock) {DestinationTableName = "dbo.CUSTOMER", BatchSize = 10000};

            int i = NumWh;
            int tid = (NumWh*10*3000) + 1;

            while (i < MaxNumWh + 1)
            {
                for (int id = 1; id < 11; id++)
                {
                    for (int cid = 1; cid < 3001; cid++)
                    {
                        DataRow dr  = dt.NewRow();
                        dr["C_ID"] = "C_W" + i + "_D" + id + "_" + cid;
                        dr["C_D_ID"] = "D_W" + i + "_" + id;
                        dr["C_W_ID"] = "W_" + i;
                        dr["C_FIRST"] = RandomString(5, 16);
                        dr["C_MIDDLE"] = "oe";
                        dr["C_LAST"] = RandomString(8, 16);
                        dr["C_STREET_1"] = RandomString(10, 20);
                        dr["C_STREET_2"] = RandomString(10, 20);
                        dr["C_CITY"] = RandomString(10, 20);
                        dr["C_STATE"] = RandomString(2, 2);
                        dr["C_ZIP"] = RandZip();
                        dr["C_PHONE"] = RandomString(12, 12);
                        dr["C_SINCE"] = currdate.ToLongTimeString();
                        dr["C_CREDIT"] = "GC";
                        dr["C_CREDIT_LIM"] = 5000;
                        dr["C_DISCOUNT"] = .5;
                        dr["C_BALANCE"] = -10.00;
                        dr["C_YTD_PAYMENT"] = 10.00;
                        dr["C_PAYMENT_CNT"] = 1;
                        dr["C_DELIVERY_CNT"] = 0;
                        dr["C_DATA"] = RandomString(300, 500);
                        dr["SEQ_ID"] = tid;
                        dt.Rows.Add(dr);
                    }
                }
                bulkCopy.WriteToServer(dt);
                dt.Clear();

                i++;
            }
            return;
        }

        protected DataSet GetAndFillDataSet(SqlDataAdapter adapter)
        {
            DataSet dts = new DataSet(NewTableName);
            adapter.FillSchema(dts, SchemaType.Source, NewTableName);
            adapter.Fill(dts);

            return dts;
        }

 private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
 {
     dsPubs.Clear();
     gros = new SqlDataAdapter("SELECT [Gross] FROM Payments Where [ID] LIKE '%" + id[i].id.ToString() + "%' AND [Year] LIKE '%" + DateTime.Now.Year.ToString() + "%'", Form1.Conn);
     dsPubs = new DataSet("Pubs");
     gros.FillSchema(dsPubs, SchemaType.Source, "Payments");
     gros.Fill(dsPubs, "Payments");
     dsPubs.Dispose();
 }

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

 private void log_Load(object sender, EventArgs e)
 {
     SqlDataAdapter daAuthors = new SqlDataAdapter("Select * From log", Form1.Conn);
     DataSet dsPubs = new DataSet("Pubs");
     //dsPubs.Tables["LTD"].Columns.Add(no);
     daAuthors.FillSchema(dsPubs, SchemaType.Source, "log");
     daAuthors.Fill(dsPubs, "log");
     //dsPubs.Tables["LTD"].Columns.Add(no);
     bindingSource1.DataSource = dsPubs.Tables["log"];
     dgv_log.DataSource = bindingSource1;
 }

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

        private void cargarTabla(string tabla)
        {
            SqlConnection conexion = conectarBD();

            conexion.Open();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(String.Format("SELECT * FROM {0}", tabla), conexion);
            dataAdapter.FillSchema(Datos, SchemaType.Source, tabla);
            dataAdapter.Fill(Datos, tabla);
            dataAdapters.Add(tabla, dataAdapter);
            conexion.Close();
        }

        private DataTable Products()
        {
            var connection = ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString;
            using (var dataAdapter = new SqlDataAdapter("SELECT * from Products", connection))
            {
                var dataTable = new DataTable();

                dataAdapter.Fill(dataTable);
                dataAdapter.FillSchema(dataTable, SchemaType.Mapped);
                return dataTable;
            }
        }

        public MyDBTable(SqlConnection conn, string sql)
        {
            this._conn = conn;
            if (_conn.State != ConnectionState.Open) _conn.Open();
            this._tableName = "_tft_";// tableName;
            //string sql = "select * from [" + _tableName + "] ";
            this._adapter = new SqlDataAdapter(sql, _conn);

            _adapter.FillSchema(_ds, SchemaType.Source, _tableName);
            _adapter.Fill(_ds, _tableName);
            _dt = _ds.Tables[_tableName];
        
        }