创建事务批量删除DataGridView数据(含关联表中的数据)

新建一个CommandInfo.cs 类

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinStuSystem
{
    public class CommandInfo
    {
        public string CommandText { get; set; }
        public DbParameter[] Parameters { get; set; }
        public bool IsProc { get; set; }
        public CommandInfo() { }

        public CommandInfo(string comText, bool isProc)
        {
            this.CommandText = comText;
            this.IsProc = isProc;
        }
        public CommandInfo(string commandText, DbParameter[] parameters, bool isProc)
        {
            CommandText = commandText;
            Parameters = parameters;
            IsProc = isProc;
        }
    }
}

新建SqlHelper.cs 类,封装事务处理函数

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Windows.Forms;

namespace WinStuSystem
{
    public class SqlHelper
    {
        public static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="comList"></param>
        /// <exception cref="Exception"></exception>
        public static bool ExecuteTrans(List<CommandInfo> comList)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = trans;
                try
                {
                    int count = 0;
                    for (int i=0; i<comList.Count;i++)
                    {
                        cmd.CommandText = comList[i].CommandText;
                        if (comList[i].IsProc)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            cmd.CommandType = CommandType.Text;
                        }
                        if (comList[i].Parameters.Length > 0)
                        {
                            cmd.Parameters.Clear ();
                            cmd.Parameters.AddRange(comList[i].Parameters);
                        }
                        count += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                    return true;
                }catch (Exception ex)
                {
                    trans.Rollback(); //回滚
                    throw new Exception("执行sql错误:"+ex.Message, ex);
                }
            }
        }
    }
}

点击批量删除执行的方法

private void button1_Click(object sender, EventArgs e)
{
    //获取勾选的数据的id
    List<int> ids = new List<int>();
    int count = classTableData.Rows.Count;
    for (int i = 0; i < count; i++)
    {
        if ((bool)classTableData.Rows[i].Cells["checks"].EditedFormattedValue == true)
        {
            int n = int.Parse(classTableData.Rows[i].Cells["ClassId"].Value.ToString());
            ids.Add(n);
        }
    }
    if (ids.Count > 0)
    {
        DialogResult dilr = MessageBox.Show("确定要删除班级及其相关的信息吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.None);
        if (dilr == DialogResult.Yes)
        {
            //先删学生,在删班级,
            string sqlstu = "delete from StuInfo where ClassId=@ClassId";
            string sqlclass = "delete from ClassInfo where ClassId=@ClassId";
            List<CommandInfo> comlists = new List<CommandInfo>();

            foreach (int id in ids)
            {
                SqlParameter[] pars =
                {
                    new SqlParameter("@ClassId",id)
                };
                CommandInfo coms = new CommandInfo()
                {
                    CommandText = sqlstu,
                    IsProc = false,
                    Parameters = pars
                };
                comlists.Add(coms);

                CommandInfo comc = new CommandInfo()
                {
                    CommandText = sqlclass,
                    IsProc = false,
                    Parameters = pars
                };
                comlists.Add(comc);
            }

            bool bl = SqlHelper.ExecuteTrans(comlists);
            if (bl)
            {
                DataTable dt = classTableData.DataSource as DataTable;
                string idStr = string.Join(",", ids);
                DataRow[] rows = dt.Select("ClassId in (" + idStr + ")");
                foreach (DataRow row in rows)
                {
                    dt.Rows.Remove(row);
                }
                classTableData.DataSource = dt;
                MessageBox.Show("批量删除成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
                return;
            }
            else
            {
                MessageBox.Show("删除失败", "提示", MessageBoxButtons.OK);
            }
        }
    }
    else
    {
        MessageBox.Show("请选择要删除的班级信息", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }
}

界面效果