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 = ; 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 = ; i < ; i++)

                            dataTable.Rows.Add(null, "张8", );

                        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 = ; 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进行的增，删，改操作，可以合在一起批量执行。

巴特西