C# MySqlDataAdapter.Fill方法代码示例

        public void AutoIncrementColumns()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
            execSQL("INSERT INTO Test VALUES(NULL)");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            DataRow row = ds.Tables[0].NewRow();
            ds.Tables[0].Rows.Add(row);

            try
            {
                da.Update(ds);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }

            ds.Clear();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
            cb.Dispose();
        }

        public static DataView affichgrid(DataGridView dgv)
        {
            string ConnectionString = "SERVER=localhost;" + "DATABASE=gestioncommerciale;" + "UID=root;" + "PASSWORD=freedomity;";
            MySqlConnection _Conn = DAL.Connexion.connect();
            _Conn.Open();

            DataTable dt = new DataTable();

            string strSql = "SELECT * FROM client";

            MySqlCommand cmd = new MySqlCommand(strSql, _Conn);

            //OleDbDataAdapter joue le rôle de pont entre DataSet et une source de données pour la récupération et l'enregistrement de données.
            MySqlDataAdapter da = new MySqlDataAdapter(strSql, ConnectionString);

            // un cache en mémoire des données récupérées d'une source de données,
            DataSet ds = new DataSet();

            da.Fill(dt);
            da.Fill(ds, "client");

            dgv.DataSource = dt.DefaultView;

            return (ds.Tables["client"].DefaultView);
        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="sql">sql 语句</param>
        /// <param name="tablename">指定DataSet中的表，不指定传null</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataSet(string sql, string tablename, params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = new MySqlConnection(Connection))
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                conn.Open();
                if ((parameters != null) && (parameters.Length > 0))
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(parameters);
                }

                DataSet ds = new DataSet();
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                if (tablename != null)
                {
                    adapter.Fill(ds, tablename);
                }
                else
                {
                    adapter.Fill(ds);
                }

                return ds;
            }
        }

        public void DataViewControl()
        {
            string myConn = "datasource=localhost;port=3306;username='root';password=''";
            string Querry = "select * from aus_work.user;";
            MySqlConnection conn = new MySqlConnection(myConn);
            //MySqlCommand cmddatabase = new MySqlCommand(Querry, conn);
            MySqlDataAdapter dataadap = new MySqlDataAdapter(Querry, conn);
            conn.Close();
            DataTable userdatatablecontol = new DataTable();

            dataadap.Fill(userdatatablecontol);
            dataadap.Fill(userdatatable);
            dataGridView1.DataSource = userdatatablecontol;
        }

        public void process(ServiceRequest request, ServiceResponse response)
        {
            List<Category> list = new List<Category>();

            string sqlStr = "select * from category";

            MySqlConnection conn = ConnectionManager.getInstance().getConnection();

            conn.Open();

            MySqlDataAdapter mda = new MySqlDataAdapter(sqlStr, conn);
            DataSet ds = new DataSet();
            mda.Fill(ds,"table1");

            conn.Close();

            int count = ds.Tables["table1"].Rows.Count;
            for (int i = 0; i < count; i++)
            {
                Category c = new Category();
                c.categoryId = (int)ds.Tables["table1"].Rows[i][0];
                c.categoryName = (string)ds.Tables["table1"].Rows[i][1];
                list.Add(c);
            }

            GetCategoryResponse serviceResponse = new GetCategoryResponse();
            serviceResponse.categories = list;
            response.responseObj = serviceResponse;
            response.returnCode = 0;
        }

        private void WypelnijGridView()
        {
            txtDruzyna.Text = "";
            txtImie.Text = "";
            txtNazwisko.Text = "";
            txtData.Text = "";
            txtPozycja.Text = "";
            txtWaga.Text = "";
            txtWzrost.Text = "";
            txtNumer.Text = "";

            string constr = ConfigurationManager.ConnectionStrings["pol"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM pilkarze"))
                {
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }

        private void fillgridDef()
        {
            id = -1;
            buttonDel.Visible = false;
            buttonEdit.Visible = false;
            buttonIns.Visible = false;
            buttonCancel.Visible = false;
            DBConnect NewcConnection = new DBConnect();
            NewcConnection.dbConnection();
            MySqlCommand querysql = new MySqlCommand("Select * From deficiencias", DBConnect.db);

            try
            {
                MySqlDataAdapter dados = new MySqlDataAdapter();
                dados.SelectCommand = querysql;
                DataTable tabela = new DataTable();
                dados.Fill(tabela);
                BindingSource fonte = new BindingSource();
                fonte.DataSource = tabela;
                dataGridView1.DataSource = fonte;
                this.dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].HeaderText = "Deficiencia";
                dados.Update(tabela);
                dataGridView1.AutoResizeColumns();
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            DBConnect.db.Close();
            textBoxDef.Clear();
        }

        public void fill_dg1()
        {
            try
            {
                _connection.Open();
                _mySqlCommand.CommandText = @"SELECT
                    operators.Id_Operator AS 'ID',
                    operators.Surname AS 'ФИО',
                    operators.LevelMD AS 'Уровень MD',
                    operators.LevelUSD AS 'Уровень USD'
                    FROM
                    operators
                    WHERE operators.active = 1
                ";
                _mySqlCommand.Connection = _connection.MySqlConnection;

                var dataAdapter = new MySqlDataAdapter(_mySqlCommand.CommandText, _connection.MySqlConnection);

                var dset = new DataSet();
                dataAdapter.Fill(dset);

                dg1.ItemsSource = dset.Tables[0].DefaultView;
                _connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (con.State == ConnectionState.Closed) con.Open();
        DataTable dt = new DataTable();

        if (ddlGuestType.Text == "De-Activate")
        {

            cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
                       " FROM  Hotels Where Status='De-Activate' ", con);
        }
        else if (ddlGuestType.Text == "Activate")
        {

            cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
                       " FROM  Hotels Where Status='Activate' ", con);

        }
        else
        {

            cmd = new MySqlCommand("SELECT HotelKey as HotelID,HotelName,ListingType,ShortDescription,HotelImage,HotelOverview,VideoLink,PricePerDay,GoogleMapLocation,EmailID,PhoneNumber,Address,City,State,Country,Website " +
                     " FROM  Hotels ", con);

        }

        MySqlDataAdapter da = new MySqlDataAdapter(cmd);

        da.Fill(dt);

        ExportToExcel(dt, "Hotels", ddlGuestType.Text + " Hotel" );
        if (con.State == ConnectionState.Open) con.Close();
    }

        // display all posted topics under the selected forum
        protected void Load_Topics()
        {
            try
            {
                con.Open();
                MySqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "SELECT * FROM forum_discussions WHERE forum_name = " + "'" + txtForumName.Text + "'";
                //cmd.CommandText = "SELECT * FROM forum_discussions where surname like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') or firstname like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') or customer_id like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') or group_status like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') or email like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') or institution like " + "'" + txtSearch.Text + "%' and  group_name in ('clients') ";

                adap = new MySqlDataAdapter(cmd);
                ds1 = new DataSet();
                adap.Fill(ds1, "forum");

                grdTopics.DataSource = ds1.Tables[0];
                grdTopics.DataBind();

                lblError.Visible = false;

            }
            catch (Exception err)
            {
                lblError.Visible = true;
                lblError.Text = "Error: " + err.Message;
            }

            con.Close();
        }

        // GET: Test
        public ActionResult Index()
        {
            MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=classicmodels; password=root");
            con.Open();

            MySqlCommand query = new MySqlCommand("SELECT contactFirstName, contactLastName FROM customers", con);
            MySqlDataAdapter adp = new MySqlDataAdapter(query);
            DataSet ds = new DataSet();
            adp.Fill(ds);

            List<String> list = ds.Tables[0].AsEnumerable()
                .Select(r => r.Field<String>("contactFirstName"))
                .ToList();

            ViewBag.List = list;

            //List<String> test = new List<String>();

            //test.Add("een");
            //test.Add("twee");
            //test.Add("drie");
            //ViewBag.List = test;

            return View();
        }

        public Form4()
        {
            InitializeComponent();

            try
            {
                MySqlConnection con = new MySqlConnection(ruta);
                con.Open();
                string consulta = "select * from pc_escritorio";
                MySqlDataAdapter da = new MySqlDataAdapter(consulta, con);

                //lleno
                DataSet ds = new DataSet();
                da.Fill(ds, "pc_escritorio");

                //datos al datagriew
                dataGridView1.DataSource = ds.Tables[0];
                con.Close();

            }
            catch (Exception ee)
            {
                MessageBox.Show("Se produjo un error debido a : " + ee.ToString());

            }
        }

        public void filldatagrid()
        {
            string str = sqlcon;
            MySqlConnection con = new MySqlConnection(str);

            string com = "Select id,name,surname,username,gold,military,political,diplomatic,trade,rev_sum,cost_sum from player where username='" + label2.Text + "'" ;
            MySqlDataAdapter adpt = new MySqlDataAdapter(com, con);
            DataSet myDataSet = new DataSet();
            adpt.Fill(myDataSet, "player");
            DataTable myDataTable = myDataSet.Tables[0];
            //DataRow tempRow = null;

            DataRow tempRow1 = null;

            foreach (DataRow tempRow1_Variable in myDataTable.Rows)
            {
                tempRow1 = tempRow1_Variable;
                label1.Text = Convert.ToString(tempRow1["name"] + " " + tempRow1["surname"]);
            }

            dataGridView1.DataSource = myDataSet;
            dataGridView1.DataMember = "player";
            dataGridView1.Columns[0].HeaderText = "Κωδικός Παίκτη";
            dataGridView1.Columns[1].HeaderText = "Όνομα Παίκτη";
            dataGridView1.Columns[2].HeaderText = "Επώνυμο Παίκτη";
            dataGridView1.Columns[3].HeaderText = "Username";
            dataGridView1.Columns[4].HeaderText = "Χρύσος";
            dataGridView1.Columns[5].HeaderText = "Στρατιωτική Ικανότητα";
            dataGridView1.Columns[6].HeaderText = "Πολιτική Ικανότητα";
            dataGridView1.Columns[7].HeaderText = "Διπλωματική Ικανότητα";
            dataGridView1.Columns[8].HeaderText = "Εμπορική Ικανότητα";
            dataGridView1.Columns[9].HeaderText = "Έσοδα";
            dataGridView1.Columns[10].HeaderText = "Έξοδα";
        }

 private void FatuSemana_Load(object sender, EventArgs e)
 {
     this.label4.DataBindings.Clear();
     dataSistema = DateTime.Now.ToShortDateString();
     string data="",sDataF="";
     data = dataSistema;
     DateTime d = Convert.ToDateTime(data);
     sDataF = d.ToString("yyyyMMdd");
     try
     {
         MySqlConnection con = new MySqlConnection("SERVER=localhost;" + " DATABASE=banco_rr_sacoles;" + " UID=root;" + "PASSWORD=12345;");
         MySqlDataAdapter sql = new MySqlDataAdapter("SELECT SUM( total ) AS total FROM faturamento WHERE data BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) and '" + sDataF + "'", con);
         //SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY);
         DataTable dt = new DataTable();
         sql.Fill(dt);
         BindingSource source = new BindingSource();
         source.DataSource = dt;
         this.label4.DataBindings.Add("Text", source, "total", true);
         string total = label4.Text;
         Decimal tt = Convert.ToDecimal(total);
         String tot = tt.ToString("N", new CultureInfo("pt-BR"));
         tbxFaturamento.Text = ("R$ " + (String.Format("{0:0.00}",tt))).ToString();
         con.Close();
     }
     catch (Exception ex)
     {
         ex.Message.ToString();
         //throw new Exception("Erro de comandos: " + ex.Message);
     }
 }

    public void SimpleJoinWithPredicate()
    {
      MySqlDataAdapter da = new MySqlDataAdapter(
          @"SELECT b.id,b.name,a.name as author_name from books b JOIN
                    authors a ON b.author_id=a.id WHERE b.pages > 300", conn);
      DataTable dt = new DataTable();
      da.Fill(dt);

      using (testEntities context = new testEntities())
      {
        var q = from b in context.Books
                join a in context.Authors
                on b.Author.Id equals a.Id
                where b.Pages > 300
                select new
                {
                  bookId = b.Id,
                  bookName = b.Name,
                  authorName = a.Name
                };

        string sql = q.ToTraceString();
        CheckSql(sql, SQLSyntax.SimpleJoinWithPredicate);

        int i = 0;
        foreach (var o in q)
          Assert.AreEqual(dt.Rows[i++][0], o.bookId);
        Assert.AreEqual(dt.Rows.Count, i);
      }
    }