C# ADO.NET 批量启用，停用账户

C# ADO.NET 批量启用，停用账户

数据库表：

UserInfos:

数据：

底层Model:
SqlHelper.cs:

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

namespace WINERP.Common
{
    public class SqlHelper
    {
        //数据库连接
        //private string connStr = "server=192.168.0.204;uid=sa;pwd=123456;database=StudentDB";
        public static readonly string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        public static object ExecuteScalar(string sql, params SqlParameter[] paras)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //创建SqlCommand对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paras);
                //打开连接，最晚打开，最早关闭
                conn.Open();
                //执行sql语句
                o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
            }
            return o;
        }
        /// <summary>
        /// 执行查询,返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paras);
                SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch (SqlException ex)
            {
                conn.Close();
                throw new Exception("执行查询出现异常", ex);
            }
        }

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);//创建SqlCommand对象
                if (paras != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter();//断开式连接
                sda.SelectCommand = cmd;
                sda.Fill(dt);//数据填充
            }
            return dt;
        }
        /// <summary>
        /// 增删改
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //创建SqlCommand对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paras);
                //打开连接，最晚打开，最早关闭
                conn.Open();
                //执行sql语句,返回受影响的行数
                count = cmd.ExecuteNonQuery();
            }
            return count;
        }

        /// <summary>
        /// 执行事物
        /// </summary>
        /// <param name="comList"></param>
        /// <returns></returns>
        public static bool ExecuteTrans(List<CommandInfo> comList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                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("执行事物出现异常", ex);
                }
            }
        }


        public static DataSet GetDataSet(string sql, params SqlParameter[] paras)
        {
            DataSet ds = null;
            using(SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);//创建SqlCommand对象
                if (paras != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter();//断开式连接
                sda.SelectCommand = cmd;
                sda.Fill(ds);//数据填充
            }
            return ds;
        }
    }
}

CommandInfo.cs:

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

namespace WINERP.Common
{
    public class CommandInfo
    {
        public string CommandText;//sql或存储过程名
        public DbParameter[] Parameters;//参数列表
        public bool IsProc;//是否是存储过程

        public CommandInfo()
        {
        }

        public CommandInfo(string commandText, bool isProc)
        {
            CommandText = commandText;
            IsProc = isProc;
        }

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

DAL 层
UserDAL.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WINERP.Common;
using WINERP.CustomAttributes;
using WINERP.Model.DMooel;

namespace WINERP.DAL
{
    public class UserDAL
    {
		public bool UpdateMultiUserState(List<int> userIds, int userState)
        {
            List<CommandInfo> comList = new List<CommandInfo>();
            foreach (int userId in userIds)
            {
                string sql = "update UserInfos set [email protected] where [email protected]";
                SqlParameter[] pms =
                {
                    new SqlParameter("@UserState",userState),
                    new SqlParameter("@UserId",userId)
                };
                comList.Add(new CommandInfo()
                {
                    CommandText = sql,
                    IsProc = false,
                    Parameters = pms
                });
            }
            bool flag = SqlHelper.ExecuteTrans(comList);
            return flag;
        }
	}
}

BLL
UserBLL.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WINERP.DAL;
using WINERP.Model.DMooel;
using WINERP.Model.VModel;

namespace WINERP.BLL
{
    public class UserBLL
    {
        #region 启用多账户
        public bool EnableMultiUser(List<int> userIds)
        {
            return udal.UpdateMultiUserState(userIds, 1);
        }
        #endregion

        #region 停用多账户
        public bool StopMultiUser(List<int> userIds)
        {
            return udal.UpdateMultiUserState(userIds, 0);
        }
        #endregion
    }
}

WinForm

#region 数据列表的单击单元格事件  修改/启用账户/停用账户
        private void dgvUsers_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //单击单元格
            DataGridViewCell cell = dgvUsers.Rows[e.RowIndex].Cells[e.ColumnIndex];
            //获取单元格的值
            string cellVal = cell.FormattedValue.ToString();
            //将datagridview当前行的数据转换成实体
            UserInfosModel user = dgvUsers.Rows[e.RowIndex].DataBoundItem as UserInfosModel;
            switch (cellVal)
            {
                case "修改":
                    UpdateUser(2,user.UserId);
                    break;
                case "停用":
                    UpdateIserState(0, user.UserId);
                    break;
                case "启用":
                    UpdateIserState(1, user.UserId);
                    break;
            }
        }
        #endregion

批量启用/停用

        #region 批量启用/停用 type:用户状态
        private void UpdateUsersState(int type)
        {
            //获取该单元格账户的状态
            string actMsg = type == 1 ? "启用" : "停用";
            //判断选择的账户数是否等于0
            if (dgvUsers.SelectedRows.Count == 0)
            {
                MessageBox.Show($"请选择要{actMsg}的账户", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            
            if (MessageBox.Show($"用户{actMsg}", $"您确定要{actMsg}该账号吗?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                List<int> userIds = new List<int>();
                foreach (DataGridViewRow dr in dgvUsers.SelectedRows)
                {
                    UserInfosModel user = dr.DataBoundItem as UserInfosModel;
                    userIds.Add(user.UserId);
                }
                bool bl = false;
                if (type == 1)
                {
                    bl = ubll.EnableMultiUser(userIds);
                }
                else
                {
                    bl = ubll.StopMultiUser(userIds);
                }

                if (bl)
                {
                    MessageBox.Show($"用户{actMsg}", $"该用户{actMsg}成功!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    LoadUserList();
                }
                else
                {
                    MessageBox.Show($"该用户{actMsg}失败!", "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
        }
        #endregion

        #region 批量启用
        private void btnEnable_Click(object sender, EventArgs e)
        {
            Action act = () =>
            {
                UpdateUsersState(1);
            };
            act.TryCatch("批量启用异常");
        }
        #endregion

        #region 批量停用
        private void btnStop_Click(object sender, EventArgs e)
        {
            Action act = () =>
            {
                UpdateUsersState(0);
            };
            act.TryCatch("批量停用异常");
        }
        #endregion