C#ADO.NET基础二

DataAdapter的使用，批量增删改

1.使用DataAdapter查询

 private void Select2()
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select name,age from test";
                        DataTable dataTable = new DataTable();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dataTable);

                        if (dataTable != null)
                        {
                            for (int i = 0; i < dataTable.Rows.Count; i++)
                            {
                                DataRow row = dataTable.Rows[i];
                                string name = row["name"].ToString();
                                int age = int.Parse(row["age"].ToString());

                                textBox1.Text += $"name:{name} age:{age}\r\n";
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

2.批量增

private void btnBatchAdd_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select id,name,age from test";
                        DataTable dataTable = new DataTable();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dataTable);

                        for (int i = 0; i < 10; i++)
                            dataTable.Rows.Add(null, "张8", 20);

                        SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                        adapter.Update(dataTable.GetChanges());
                        dataTable.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

3.批量改

private void btnBatchUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select id,name,age from test";
                        DataTable dataTable = new DataTable();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dataTable);

                        foreach (DataRow row in dataTable.Rows)
                        {
                            if (row["name"].ToString() == "张8")
                                row["name"] = "张一";
                        }

                        SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                        adapter.Update(dataTable.GetChanges());
                        dataTable.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

4.批量删

注意：DataTable的删除只能用 dataTable.Rows[i].Delete()不能用DataRow.RemoveAt和DataRow.Remove，否则更新数据库不成功

参考：https://www.cnblogs.com/zouhao/p/9977767.html

 

private void btnBatchDelete_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select id,name,age from test";
                        DataTable dataTable = new DataTable();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                        adapter.Fill(dataTable);

                        for (int i = 0; i < dataTable.Rows.Count; i++)
                            dataTable.Rows[i].Delete();

                        SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                        adapter.Update(dataTable.GetChanges());
                        dataTable.AcceptChanges();
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

 

5.总结

　　针对DataSet或者DataTable进行的增，删，改操作，可以合在一起批量执行。