步步为营-45-一套增删查改

说明:比较完整的增删查改

1:新建窗体项目,(命名规范)

2:添加dataGridView控件

3:通过AppConfig创建连接字符串

4:创建SQLHelper类,

　　4.1 获得连接字符串

　　　　4.1.1 添加引用,导入命名空间 using System.Configuration;

　　　　4.1.2 编写方法

　　public static string GetConnStr()
        {
            return ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        }

5 展示数据

　　5.1 页面加载时调用LoadUserInfo方法　　

 #region 01初始化用户表格
        private void LoadUserInfo()
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

           //01-01获取连接字符串
            string connstr = SqlHelper.GetConnStr();
            //01-02 从数据库中获取数据
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,connstr))
            {
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

LoadUserInfo

　　5.2 使用强类型数据,添加UserInfo类

 public class UserInfo
    {
        public int EmpId { get; set; }
        public string StuName { get; set; }
        public string Pwd { get; set; }
        public int StuAge { get; set; }
        public char Delflag { get; set; }
        public int ClassNo { get; set; } 
    }

UserInfo

　　5.3 修改显示字段

6 删除

　　添加一个右击菜单contextMenuStrip,设置整行选中属性中SelectionMode == FullRowSelect

#region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            LoadUserInfo();
        } 
        #endregion

删除

7 弹出窗口更新

　　7.1 添加窗体EditForm

　　7.2 设置readonly属性,设置双击事件

　　7.3 设置信息加载

　　7.4 设置保存按钮单击事件

　　7.5 保存后应该关闭"编辑窗体",同时刷新"主窗体"

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

namespace 完整的增删查改
{
    public partial class EditForm : Form
    {
        public UserInfo UserInfo { set; get; }

        public EditForm(UserInfo userInfo)
        {
            InitializeComponent();
            UserInfo = userInfo;
        }

       
        #region 01-窗体加载事件
        private void EditForm_Load(object sender, EventArgs e)
        {
            LoadUserInfo();
        }

        #region 01初始化用户表格
        private void LoadUserInfo()
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "select  Pwd, StuName, StuAge from UserInfo where EmpId =@EmpId";
                    cmd.Parameters.AddWithValue("@EmpId", UserInfo.EmpId);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            this.txtName.Text = reader["StuName"].ToString();
                            this.txtAge.Text = reader["StuAge"].ToString();
                            this.txtPwd.Text = reader["Pwd"].ToString();
                        }
                    }

                }
            }
        }
        #endregion

        #region 02-保存按钮触发事件
        private void btnSave_Click(object sender, EventArgs e)
        {
            int stuAge;
            if (!int.TryParse(txtAge.Text, out stuAge))
            {
                return;
            }
            using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    
                    cmd.CommandText = " Update UserInfo set Pwd=@Pwd, StuName=@StuName, StuAge =@StuAge  where EmpId = @EmpId; ";
                    cmd.Parameters.AddWithValue("@Pwd", txtPwd.Text);
                    cmd.Parameters.AddWithValue("@StuName",txtName.Text);
                    cmd.Parameters.AddWithValue("@StuAge", stuAge);
                    cmd.Parameters.AddWithValue("@EmpId", UserInfo.EmpId);

                    if (cmd.ExecuteNonQuery()> 0)
                    {
                        MessageBox.Show("修改成功!");
                        //关闭窗体
                        this.Close();   
                    }
                }
            }
        }
        #endregion

        #endregion
    }
}

EditForm

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            LoadUserInfo();
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

        #region 01初始化用户表格
        private void LoadUserInfo()
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

           //01-01获取连接字符串
           
            //01-02 从数据库中获取数据
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,ConnStr))
            {
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            LoadUserInfo();
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            LoadUserInfo();
        } 
        #endregion

     
    }
}

MainForm

8 多条件查询

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            LoadUserInfo();
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

        #region 01初始化用户表格
        private void LoadUserInfo()
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

           //01-01获取连接字符串
           
            //01-02 从数据库中获取数据
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,ConnStr))
            {
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            LoadUserInfo();
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            LoadUserInfo();
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count>0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion

            List<UserInfo> userInfoList = new List<UserInfo>();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText,connStr))
            {
                //填充之前,先给SelectCommand赋参数
                adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

     
    }
}

View Code

9 代码优化

　　从功能分析可以得知,我们查询后的表格显示和页面加载时的表格显示,调用的方法类似,有大量的重复代码. 可以考虑优化一下.观察发现两个方法最大的不同点在于sql脚本不一样.

　　优化二,用户修改后显示搜索后页面,修改138行代码

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";

            LoadUserInfoToGridView(sqlStr);
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

        #region 01初始化用户表格--已废
        //private void LoadUserInfo()
        //{
        //    //01-00 设置强类型数据源
        //    List<UserInfo> userInfoList = new List<UserInfo>();

        //   //01-01获取连接字符串
           
        //    //01-02 从数据库中获取数据
        //    string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
        //    //01-03 创建Adapter
        //    using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,ConnStr))
        //    {
        //        //01-04 把数据库中的数据填充到内存表中
        //        DataTable dt = new DataTable();
        //        adapter.Fill(dt);
        //        //01-05 应该使用强类型数据
        //        foreach (DataRow dr in dt.Rows)
        //        {
        //            //数据封装
        //            UserInfo userInfo = new UserInfo();
        //            userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
        //            userInfo.Pwd = dr["Pwd"].ToString();
        //            userInfo.StuName = dr["StuName"].ToString();
        //            userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
        //            userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
        //            userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
        //            //添加到列表中
        //            userInfoList.Add(userInfo);
        //        }
        //        //01-06 配置数据源
        //        this.dataGridView1.DataSource = userInfoList;
        //    }
        //} 
        #endregion

        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            MainForm_Load(this,null);
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            btnSearch_Click(this, null);
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count > 0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion
            LoadUserInfoToGridView(sqlText, parameters.ToArray());
            //List<UserInfo> userInfoList = new List<UserInfo>();
            //using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText,connStr))
            //{
            //    //填充之前,先给SelectCommand赋参数
            //    adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
            //    DataTable dt = new DataTable();
            //    adapter.Fill(dt);
            //    foreach (DataRow dr in dt.Rows)
            //    {
            //        //数据封装
            //        UserInfo userInfo = new UserInfo();
            //        userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
            //        userInfo.Pwd = dr["Pwd"].ToString();
            //        userInfo.StuName = dr["StuName"].ToString();
            //        userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
            //        userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
            //        userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
            //        //添加到列表中
            //        userInfoList.Add(userInfo);
            //    }
            //    //01-06 配置数据源
            //    this.dataGridView1.DataSource = userInfoList;
            //}
        } 
        #endregion

        #region 05-将01和04进行优化可得-----加载数据到GridView
        private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

            //01-01获取连接字符串

            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, ConnStr))
            {
                //填充之前,先给SelectCommand赋参数
                adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

     
    }
}

优化

10 进一步优化,SqlHelper封装.SQLConnection和SqlCommand大量重复　　

　　10.1 SqlHelper代码

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

namespace 完整的增删查改
{
   public  class SqlHelper
    {
        #region 01-返回连接字符串
        public static string GetConnStr()
        {
            return ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        } 
        #endregion

        #region 02-封装sql执行SQL, 返回受影响行数--ExcuteNonQuery

       /// <summary>
       /// 执行sql语句,返回受影响行数
       /// </summary>
       /// <param name="sqlText">SQL语句</param>
       /// <param name="parameters">参数</param>
       /// <returns>返回受影响行数</returns>
        public static int ExcuteNonQuery(string sqlText,params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection( GetConnStr()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
                
            }
        }

        #endregion

        #region 03-执行sql语句,返回查询结果的第一行第一列的值

        public static object ExcuteScalar(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr()))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }

            }
        }

        #endregion

        #region 04-执行sql语句,返回Datatable

        public static DataTable ExcuteDataTable(string sqlStr, params SqlParameter[] parameters)
        {
           
                using (SqlDataAdapter adapter=new SqlDataAdapter(sqlStr,SqlHelper.GetConnStr()))
                {
                    DataTable dt = new DataTable( );
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                    adapter.Fill(dt);
                    return dt;
                }
           
        }

        #endregion

        #region 05-执行SQL脚本,返回dataReader

        public static SqlDataReader ExcuteDataReader(string sqlStr,params  SqlParameter[] parameters)
        {
            //SqlDataReader要求读取数据是,独占sqlconnection对象
            SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr());//不要释放连接,因为后面要保持连接持续打开状态
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sqlStr;
            cmd.Parameters.AddRange(parameters);
            //CommandBehavior.CloseConnection 当sqldatareader释放的时候,顺便把sqlconnection也释放掉
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);


        }

        #endregion
    }
}

SqlHelper

　　10.2 编辑窗体代码修改前

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            LoadUserInfo();
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

        #region 01初始化用户表格
        private void LoadUserInfo()
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

           //01-01获取连接字符串
           
            //01-02 从数据库中获取数据
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,ConnStr))
            {
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            LoadUserInfo();
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            LoadUserInfo();
        } 
        #endregion

     
    }
}

MainForm

编辑窗体-修改前

　　10.3 编辑窗体代码修改后

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

namespace 完整的增删查改
{
    public partial class EditForm : Form
    {
        public UserInfo UserInfo { set; get; }

        public EditForm(UserInfo userInfo)
        {
            InitializeComponent();
            UserInfo = userInfo;
        }

       
        #region 01-窗体加载事件
        private void EditForm_Load(object sender, EventArgs e)
        {
            LoadUserInfo();
        }

        #region 01初始化用户表格
        private void LoadUserInfo()
        {

            string sqlStr = "select  Pwd, StuName, StuAge from UserInfo where EmpId =@EmpId";
            ;
            using (SqlDataReader reader = SqlHelper.ExcuteDataReader(sqlStr,new SqlParameter("@EmpId",(object)UserInfo.EmpId)))
            {
                if (reader.Read())
                {
                    this.txtName.Text = reader["StuName"].ToString();
                    this.txtAge.Text = reader["StuAge"].ToString();
                    this.txtPwd.Text = reader["Pwd"].ToString();
                }
            }
        }
        #endregion

        #region 02-保存按钮触发事件
        private void btnSave_Click(object sender, EventArgs e)
        {
            int stuAge;
            if (!int.TryParse(txtAge.Text, out stuAge))
            {
                return;
            }
         
            #region 02-02通过SQLHelper修改后

            string sqlStr = " Update UserInfo set Pwd=@Pwd, StuName=@StuName, StuAge =@StuAge  where EmpId = @EmpId; ";
            List<SqlParameter> listParameters = new List<SqlParameter>();
            #region 01-参数
            SqlParameter pwd = new SqlParameter("@Pwd", SqlDbType.NVarChar, 32);
            pwd.Value = txtPwd.Text;
            listParameters.Add(pwd);

            SqlParameter stuName = new SqlParameter("@StuName", SqlDbType.NVarChar, 32);
            stuName.Value = txtName.Text;
            listParameters.Add(stuName);

            SqlParameter stuPAge = new SqlParameter("@StuAge", SqlDbType.Int, 4);
            stuPAge.Value = stuAge;
            listParameters.Add(stuPAge);

            SqlParameter empId = new SqlParameter("@EmpId", SqlDbType.Int, 4);
            empId.Value = UserInfo.EmpId;
            listParameters.Add(empId);
            #endregion

            int resultNum = SqlHelper.ExcuteNonQuery(sqlStr, listParameters.ToArray());
            if (resultNum > 0)
            {
                MessageBox.Show("修改成功!");
                // 关闭窗体
                this.Close();
            } 
            #endregion
        }
        #endregion

        #endregion
    }
}

修改后代码

　　10.4 Main窗体修改代码修改前

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";

            LoadUserInfoToGridView(sqlStr);
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

        #region 01初始化用户表格--已废
        //private void LoadUserInfo()
        //{
        //    //01-00 设置强类型数据源
        //    List<UserInfo> userInfoList = new List<UserInfo>();

        //   //01-01获取连接字符串
           
        //    //01-02 从数据库中获取数据
        //    string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
        //    //01-03 创建Adapter
        //    using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr,ConnStr))
        //    {
        //        //01-04 把数据库中的数据填充到内存表中
        //        DataTable dt = new DataTable();
        //        adapter.Fill(dt);
        //        //01-05 应该使用强类型数据
        //        foreach (DataRow dr in dt.Rows)
        //        {
        //            //数据封装
        //            UserInfo userInfo = new UserInfo();
        //            userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
        //            userInfo.Pwd = dr["Pwd"].ToString();
        //            userInfo.StuName = dr["StuName"].ToString();
        //            userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
        //            userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
        //            userInfo.ClassNo = int.Parse(dr["ClassNo"]==DBNull.Value?"-1":dr["ClassNo"].ToString());
        //            //添加到列表中
        //            userInfoList.Add(userInfo);
        //        }
        //        //01-06 配置数据源
        //        this.dataGridView1.DataSource = userInfoList;
        //    }
        //} 
        #endregion

        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
            #region 方法一-----缺点--没有参数化
            StringBuilder sbEmpIds = new StringBuilder();
            foreach (DataGridViewRow row in rows)
            {
                sbEmpIds.Append(row.Cells["EmpId"].Value.ToString());
                sbEmpIds.Append(",");
            }
            string EmpIds = sbEmpIds.ToString().Substring(0, sbEmpIds.Length - 1);
            string sqlStr = string.Format("Update UserInfo set DelFlag = 1 where EmpId in ({0})", EmpIds);
            #endregion

            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr2;
                  
                    cmd.Parameters.AddRange(parameters.ToArray());//方法二,使用
                   
                    if (cmd.ExecuteNonQuery()>0)
                    {
                        MessageBox.Show("删除成功!一共删除" + cmd.ExecuteNonQuery() + "条");
                    }
                }
            }
            MainForm_Load(this,null);
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            btnSearch_Click(this, null);
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count > 0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion
            LoadUserInfoToGridView(sqlText, parameters.ToArray());

        } 
        #endregion

        #region 05-将01和04进行优化可得-----加载数据到GridView
        private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();

            //01-01获取连接字符串

            //01-03 创建Adapter
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, ConnStr))
            {
                //填充之前,先给SelectCommand赋参数
                adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
                //01-04 把数据库中的数据填充到内存表中
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                //01-05 应该使用强类型数据
                foreach (DataRow dr in dt.Rows)
                {
                    //数据封装
                    UserInfo userInfo = new UserInfo();
                    userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                    userInfo.Pwd = dr["Pwd"].ToString();
                    userInfo.StuName = dr["StuName"].ToString();
                    userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                    userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                    userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                    //添加到列表中
                    userInfoList.Add(userInfo);
                }
                //01-06 配置数据源
                this.dataGridView1.DataSource = userInfoList;
            }
        } 
        #endregion

     
    }
}

View Code

　　10.5 Main窗体修改代码修改后

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";

            LoadUserInfoToGridView(sqlStr);
        }
        //获取连接字符串
       public string ConnStr = SqlHelper.GetConnStr();

    
        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
         
            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion

            int resultNum = SqlHelper.ExcuteNonQuery(sqlStr2, parameters.ToArray());
            if (resultNum > 0)
            {
                MessageBox.Show("删除成功!一共删除" + resultNum + "条");
            }
           
            MainForm_Load(this,null);
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            btnSearch_Click(this, null);
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count > 0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion
            LoadUserInfoToGridView(sqlText, parameters.ToArray());

        } 
        #endregion

        #region 05-将01和04进行优化可得-----加载数据到GridView
        private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();
            DataTable dt = SqlHelper.ExcuteDataTable(sqlStr, parameters);
            //01-05 应该使用强类型数据
            foreach (DataRow dr in dt.Rows)
            {
                //数据封装
                UserInfo userInfo = new UserInfo();
                userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                userInfo.Pwd = dr["Pwd"].ToString();
                userInfo.StuName = dr["StuName"].ToString();
                userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                //添加到列表中
                userInfoList.Add(userInfo);
            }
            //01-06 配置数据源
            this.dataGridView1.DataSource = userInfoList;


        } 
        #endregion

     
    }
}

View Code

11 分页

　　11.1 判断是否有行被选中

　　if (dataGridView1.SelectedRows.Count == 0 )

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }
        //获取连接字符串
        public string ConnStr = SqlHelper.GetConnStr();
        #region 01-窗体加载
        private void MainForm_Load(object sender, EventArgs e)
        {
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";

            LoadUserInfoToGridView(sqlStr);
        } 
        #endregion
    
        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
         
            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion

            int resultNum = SqlHelper.ExcuteNonQuery(sqlStr2, parameters.ToArray());
            if (resultNum > 0)
            {
                MessageBox.Show("删除成功!一共删除" + resultNum + "条");
            }
           
            MainForm_Load(this,null);
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            btnSearch_Click(this, null);
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count > 0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion
            LoadUserInfoToGridView(sqlText, parameters.ToArray());

        } 
        #endregion

        #region 05-将01和04进行优化可得-----加载数据到GridView
        private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();
            DataTable dt = SqlHelper.ExcuteDataTable(sqlStr, parameters);
            //01-05 应该使用强类型数据
            foreach (DataRow dr in dt.Rows)
            {
                //数据封装
                UserInfo userInfo = new UserInfo();
                userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                userInfo.Pwd = dr["Pwd"].ToString();
                userInfo.StuName = dr["StuName"].ToString();
                userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                //添加到列表中
                userInfoList.Add(userInfo);
            }
            //01-06 配置数据源
            this.dataGridView1.DataSource = userInfoList;


        } 
        #endregion
        #region 06-分页显示
        //06-01 首页
        private void btnFirst_Click(object sender, EventArgs e)
        {
            string sqlStr = "select top(2) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";

            LoadUserInfoToGridView(sqlStr);
        }
        //06-02 上一页
        private void btnPri_Click(object sender, EventArgs e)
        {
            int pageNum = GetNowPage();
           //06-02-04 根据当前页计算上一页
            int prvPage = pageNum - 1;
            if (prvPage == 0)
            {
                MessageBox.Show("已经是首页了");
                return;
                
            }
            string sqlStr = "select top(2) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where  Empid not in (select top((@prvPage-1)*2) EmpId from UserInfo) ";
            //06-02-05
            LoadUserInfoToGridView(sqlStr,new SqlParameter( "@prvPage",(object)prvPage));
        }
        //06-03 下一页
        private void btnNext_Click(object sender, EventArgs e)
        {
            int pageNum = GetNowPage();
            //06-03-02 根据当前页计算下一页
            int prvPage = pageNum + 1;
            string sqlStr = "select top(2) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where  Empid not in (select top((@prvPage-1)*2) EmpId from UserInfo where Delflag = 0 ) and Delflag = 0 ";
            //06-03-03
            LoadUserInfoToGridView(sqlStr, new SqlParameter("@prvPage", (object)prvPage));
        }
        //06-04 最后一页
        private void btnLast_Click(object sender, EventArgs e)
        {
            //06-04-01 获取最大的RowNumber
            string sqlTestGetRowNumber = @"select Max(Temp.rowNumber) from 
                (select EmpId,ROW_NUMBER() over (Order By EmpId ) as rowNumber from UserInfo where Delflag = 0 ) as Temp";
            int rowNumber =int.Parse(SqlHelper.ExcuteScalar(sqlTestGetRowNumber).ToString());//如果是最后页 rowNumber = 9或10
            string sqlStr;
            if (rowNumber % 2 == 0)
            {
                sqlStr = @"select * from(select * ,ROW_NUMBER() over (order by EmpId) as num from UserInfo where Delflag = 0) as T
                where T.num between @rowNumber-1 and @rowNumber ;";
            
            }
            else
            {
                sqlStr = @"select * from(select * ,ROW_NUMBER() over (order by EmpId) as num from UserInfo where Delflag = 0) as T
                where T.num = @rowNumber ;";
            }
            LoadUserInfoToGridView(sqlStr, new SqlParameter("@rowNumber", (object)rowNumber));
        }
        //06-05 获取当前页
        public int GetNowPage()
        {
            //06-02-01 获得页面选中行的EmpId;
            int empId;
            if (dataGridView1.SelectedRows.Count == 0 )
            {
               
                    MessageBox.Show("已经是尾页了,自动跳到首页");
                  
                return 1;
            }
            if (int.TryParse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString(),out empId))
            {
                //06-02-02 获取当前选中列的rowNumber,放心页面上必有一行被选中
                string sqlTestGetRowNumber = @"select Temp.rowNumber  from 
                (select EmpId,ROW_NUMBER() over (Order By EmpId ) as rowNumber from UserInfo where Delflag = 0 ) as Temp
                Where Temp.EmpId  = @empId;";
                int rowNumber = int.Parse(SqlHelper.ExcuteScalar(sqlTestGetRowNumber, new SqlParameter("@empId", (object)empId)).ToString());//如果是第二页 rowNumber = 3或4
                //06-02-03 根据rowNumber算出当前页数
                int pageNum = (rowNumber + 1) / 2;
                return pageNum;
            }
            return 1;
        }

        #endregion

     
    }
}

View Code

12 跳转到某一页(通过存储过程)

　　12.1 储存过程　　

ALTER procedure [dbo].[usp_UserInfo_GetPageData]
    @PageSize int,
    @PageIndex int
    as 
BEGIN
        select * from 
        (select * ,ROW_NUMBER() over (Order by EmpId) as rowNumber from UserInfo where Delflag = 0) as temp 
        where temp.rowNumber between (@PageSize*(@PageIndex-1)+1) and (@PageSize*@PageIndex)
END

存储过程

　　12.2 SqlHelper代码

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

namespace 完整的增删查改
{
   public  class SqlHelper
    {
        #region 01-返回连接字符串
        public static string GetConnStr()
        {
            return ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        } 
        #endregion

        #region 02-封装sql执行SQL, 返回受影响行数--ExcuteNonQuery

       /// <summary>
       /// 执行sql语句,返回受影响行数
       /// </summary>
       /// <param name="sqlText">SQL语句</param>
       /// <param name="parameters">参数</param>
       /// <returns>返回受影响行数</returns>
        public static int ExcuteNonQuery(string sqlText,params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection( GetConnStr()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
                
            }
        }

        #endregion

        #region 03-执行sql语句,返回查询结果的第一行第一列的值
       /// <summary>
        /// 执行sql语句,返回查询结果的第一行第一列的值
       /// </summary>
       /// <param name="sqlStr"></param>
       /// <param name="parameters"></param>
       /// <returns></returns>
        public static object ExcuteScalar(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr()))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }

            }
        }

        #endregion

        #region 04-执行sql语句,返回Datatable

        public static DataTable ExcuteDataTable(string sqlStr, params SqlParameter[] parameters)
        {
           
                using (SqlDataAdapter adapter=new SqlDataAdapter(sqlStr,SqlHelper.GetConnStr()))
                {
                    DataTable dt = new DataTable( );
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                    adapter.Fill(dt);
                    return dt;
                }
           
        }

        #endregion

        #region 05-执行SQL脚本,返回dataReader
        public static SqlDataReader ExcuteDataReader(string sqlStr,params  SqlParameter[] parameters)
        {
            //SqlDataReader要求读取数据是,独占sqlconnection对象
            SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr());//不要释放连接,因为后面要保持连接持续打开状态
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sqlStr;
            cmd.Parameters.AddRange(parameters);
            //CommandBehavior.CloseConnection 当sqldatareader释放的时候,顺便把sqlconnection也释放掉
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region 06-执行存储过程脚本,返回dataReader
        public static SqlDataReader ExcuteDataReaderByProcedure(string sqlStr, params  SqlParameter[] parameters)
        {
            //SqlDataReader要求读取数据是,独占sqlconnection对象
            SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr());//不要释放连接,因为后面要保持连接持续打开状态
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sqlStr;
            cmd.Parameters.AddRange(parameters);
            //CommandBehavior.CloseConnection 当sqldatareader释放的时候,顺便把sqlconnection也释放掉
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion
    }
}

注意06

　　12.3 Main代码　　

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

namespace 完整的增删查改
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }
        //获取连接字符串
        public string ConnStr = SqlHelper.GetConnStr();
        //每页的行数;
        public int pageSize;

        #region 01-窗体加载
        private void MainForm_Load(object sender, EventArgs e)
        {
            #region 01-显示列表数据
            string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            LoadUserInfoToGridView(sqlStr); 
            #endregion

  

            #region 02-计算总页数和当前页数,行数
            GetTotalPageAndNowPage();
            #endregion
        }

       
        #endregion
    
        #region 02右击删除-多项删除
        private void contextMenuDelete_Opening(object sender, CancelEventArgs e)
        {
            //02-01给出提示,判断是否确定删除
            if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes)
            {
                return;
            }
            //02-02 拿到选中行的主键,并把主键拼接起来
            var rows = this.dataGridView1.SelectedRows;
            //02-03 编写执行SQL脚本
         
            #region 方法二-

            StringBuilder sbSql = new StringBuilder();
             List<SqlParameter> parameters = new List<SqlParameter>( );
            for (int i = 0; i < rows.Count; i++)
            {
                sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";");
                SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int);
                para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString());
                parameters.Add(para);
                
            }
            string sqlStr2 = sbSql.ToString(); 
            #endregion

            int resultNum = SqlHelper.ExcuteNonQuery(sqlStr2, parameters.ToArray());
            if (resultNum > 0)
            {
                MessageBox.Show("删除成功!一共删除" + resultNum + "条");
            }
           
            MainForm_Load(this,null);
        } 
        #endregion

        #region 03双击事件-弹出修改窗体
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            //03-01 拿到Id
            int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString());
           
            //03-02打开修改窗体
            EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id});
            //03-04让主窗体关注"编辑窗体"的关闭事件.
            frmEdit.FormClosing += EditUserInfoFrm_FormClosing;
            frmEdit.Show();
            //03-03 通过构造函数传递数据
        }
        //03-04 当修改窗体关闭时候执行下面的事件响应方法
        private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e)
        {
            btnSearch_Click(this, null);
        } 
        #endregion

        #region 04-多条件查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            //string connStr = SqlHelper.GetConnStr();
            #region 04-02拼接SQl语句
            string sqlText = "select  EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
            List<string> whereList = new List<string>();
            List<SqlParameter> parameters = new List<SqlParameter>();

            if (!string.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuName like @StuName ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuName";
                parameter.Value = "%" + txtName.Text + "%";
                parameters.Add(parameter);
            }

            if (!string.IsNullOrEmpty(this.txtAge.Text.Trim()))
            {
                //把Where条件添加到List集合中
                whereList.Add(" StuAge like @StuAge ");
                //把参数进行赋值
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@StuAge";
                parameter.Value = "%" + txtAge.Text + "%";
                parameters.Add(parameter);
            }

            if (whereList.Count > 0)
            {
                sqlText += " where " + string.Join(" and ", whereList);
            }
            #endregion
            LoadUserInfoToGridView(sqlText, parameters.ToArray());

        } 
        #endregion

        #region 05-将01和04进行优化可得-----加载数据到GridView
        private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();
            DataTable dt = SqlHelper.ExcuteDataTable(sqlStr, parameters);
            //01-05 应该使用强类型数据
            foreach (DataRow dr in dt.Rows)
            {
                //数据封装
                UserInfo userInfo = new UserInfo();
                userInfo.EmpId = int.Parse(dr["EmpId"].ToString());
                userInfo.Pwd = dr["Pwd"].ToString();
                userInfo.StuName = dr["StuName"].ToString();
                userInfo.StuAge = int.Parse(dr["StuAge"].ToString());
                userInfo.Delflag = Char.Parse(dr["Delflag"].ToString());
                userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString());
                //添加到列表中
                userInfoList.Add(userInfo);
            }
            //01-06 配置数据源
            this.dataGridView1.DataSource = userInfoList;


        } 
        #endregion
        #region 06-分页显示
        
        #region //06-01 首页
        private void btnFirst_Click(object sender, EventArgs e)
        {
            string sqlStr = "select top(@pageSize) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 ";
            LoadUserInfoToGridView(sqlStr, new SqlParameter("@pageSize",(Int32)pageSize));
        } 
        #endregion
        
        #region //06-02 上一页
        private void btnPri_Click(object sender, EventArgs e)
        {
            int pageNum = GetNowPage(pageSize);
            //06-02-04 根据当前页计算上一页
            int prvPage = pageNum - 1;
            if (prvPage == 0)
            {
                MessageBox.Show("已经是首页了");
                return;

            }
            string sqlStr = "select top(@pageSize) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where  Empid not in (select top((@prvPage-1)*@pageSize) EmpId from UserInfo) ";
            SqlParameter[] parameters =
            {
                new SqlParameter("@prvPage", (object)prvPage),
                new SqlParameter("@pageSize", pageSize)
            };
            //06-02-05
            LoadUserInfoToGridView(sqlStr, parameters);
        } 
        #endregion

        #region //06-03 下一页
        private void btnNext_Click(object sender, EventArgs e)
        {
            int pageNum = GetNowPage(pageSize);
            //06-03-02 根据当前页计算下一页
            int prvPage = pageNum + 1;
            string sqlStr = "select top(@pageSize) EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where  Empid not in (select top((@nextPage-1)*@pageSize) EmpId from UserInfo where Delflag = 0 ) and Delflag = 0 ";
            SqlParameter[] parameters =
            {
                new SqlParameter("@nextPage", (object)prvPage),
                new SqlParameter("@pageSize", pageSize)
            };
            //06-03-03
            LoadUserInfoToGridView(sqlStr, parameters);
        } 
        #endregion
        
        #region //06-04 最后一页
        private void btnLast_Click(object sender, EventArgs e)
        {
            //06-04-01 获取最大的RowNumber
            string sqlTestGetRowNumber = @"select Max(Temp.rowNumber) from 
                (select EmpId,ROW_NUMBER() over (Order By EmpId ) as rowNumber from UserInfo where Delflag = 0 ) as Temp";
            int rowNumber = int.Parse(SqlHelper.ExcuteScalar(sqlTestGetRowNumber).ToString());//如果是最后页 rowNumber = 9或10
            string sqlStr;
            sqlStr = @"select * from(select * ,ROW_NUMBER() over (order by EmpId) as num from UserInfo where Delflag = 0) as T
                where T.num between @rowNumber-@pageSize+1 and @rowNumber ;"; 
            SqlParameter[] parameters =
            {
                new SqlParameter("@rowNumber", (object)rowNumber),
                new SqlParameter("@pageSize", pageSize)
            };
            LoadUserInfoToGridView(sqlStr, parameters);
        } 
        #endregion

        #region //06-05 获取当前页
        public int GetNowPage(int pageSize)
        {
            //06-02-01 获得页面选中行的EmpId;
            int empId;
            if (dataGridView1.SelectedRows.Count == 0)
            {

                MessageBox.Show("已经是尾页了,自动跳到首页");

                return 1;
            }
            if (int.TryParse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString(), out empId))
            {
                //06-02-02 获取当前选中列的rowNumber,放心页面上必有一行被选中
                string sqlTestGetRowNumber = @"select Temp.rowNumber  from 
                (select EmpId,ROW_NUMBER() over (Order By EmpId ) as rowNumber from UserInfo where Delflag = 0 ) as Temp
                Where Temp.EmpId  = @empId;";
                int rowNumber = int.Parse(SqlHelper.ExcuteScalar(sqlTestGetRowNumber, new SqlParameter("@empId", (object)empId)).ToString());//如果是第二页 rowNumber = 3或4
                //06-02-03 根据rowNumber算出当前页数
                int pageNum = (int)((rowNumber + pageSize - 1) / pageSize);
                return pageNum;
            }
            return 1;
        } 
        #endregion

        #region //06-06 跳到某一页---存储过程
        private void btnSkip_Click(object sender, EventArgs e)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();
            int pageIndex = int.Parse(txtSkipPage.Text);
            string sqlStr = "usp_UserInfo_GetPageData";
            SqlParameter[] parameters =
            {
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            SqlDataReader reader = SqlHelper.ExcuteDataReaderByProcedure(sqlStr, parameters);
            while (reader.Read())
            {
                //数据封装
                UserInfo userInfo = new UserInfo();
                userInfo.EmpId = int.Parse(reader["EmpId"].ToString());
                userInfo.Pwd = reader["Pwd"].ToString();
                userInfo.StuName = reader["StuName"].ToString();
                userInfo.StuAge = int.Parse(reader["StuAge"].ToString());
                userInfo.Delflag = Char.Parse(reader["Delflag"].ToString());
                userInfo.ClassNo = int.Parse(reader["ClassNo"] == DBNull.Value ? "-1" : reader["ClassNo"].ToString());
                //添加到列表中
                userInfoList.Add(userInfo);
            }
            //01-06 配置数据源
            this.dataGridView1.DataSource = userInfoList;
         }
      
        #endregion

        #endregion

        #region 07计算总页数和当前页数
        private void GetTotalPageAndNowPage()
        {
            //01-获得每页显示的数据行数
            if (!int.TryParse(txtPageSize.Text.ToString(),out pageSize))
            {
                pageSize = 2;
            }
            if (pageSize ==0)
            {
                pageSize = 2;
            }
            //02-获取总的记录数
           string sqlStr = "select count(1) from UserInfo where Delflag = 0 ";
            string count = SqlHelper.ExcuteScalar(sqlStr).ToString();
            //03-根据总记录数计算总页数
            txtTotalPage.Text = (Math.Ceiling(double.Parse(count)/pageSize)).ToString();
            //04-获取当前页
            txtNowPage.Text = GetNowPage(pageSize).ToString();


        }
        
        #endregion

        #region 08-每当PageSize改变时,触发事件
        private void txtPageSize_TextChanged(object sender, EventArgs e)
        {
            GetTotalPageAndNowPage();
            btnFirst_Click(this,null);
        } 
        #endregion

     
    }
}

注意06-06

　　12.4 进一步改进代码　　

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

namespace 完整的增删查改
{
   public  class SqlHelper
    {
        #region 01-返回连接字符串
        public static string GetConnStr()
        {
            return ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        } 
        #endregion

        #region 02-封装sql执行SQL, 返回受影响行数--ExcuteNonQuery

       /// <summary>
       /// 执行sql语句,返回受影响行数
       /// </summary>
       /// <param name="sqlText">SQL语句</param>
       /// <param name="parameters">参数</param>
       /// <returns>返回受影响行数</returns>
        public static int ExcuteNonQuery(string sqlText,params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection( GetConnStr()))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
                
            }
        }

        #endregion

        #region 03-执行sql语句,返回查询结果的第一行第一列的值
       /// <summary>
        /// 执行sql语句,返回查询结果的第一行第一列的值
       /// </summary>
       /// <param name="sqlStr"></param>
       /// <param name="parameters"></param>
       /// <returns></returns>
        public static object ExcuteScalar(string sqlStr, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr()))
            {

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = sqlStr;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }

            }
        }

        #endregion

        #region 04-执行sql语句,返回Datatable

        public static DataTable ExcuteDataTable(string sqlStr, params SqlParameter[] parameters)
        {
           
                using (SqlDataAdapter adapter=new SqlDataAdapter(sqlStr,SqlHelper.GetConnStr()))
                {
                    DataTable dt = new DataTable( );
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                    adapter.Fill(dt);
                    return dt;
                }
           
        }

        #endregion

        #region 05-执行SQL脚本,返回dataReader
        public static SqlDataReader ExcuteDataReader(string sqlStr,params  SqlParameter[] parameters)
        {
            return ExcuteDataReader(sqlStr,CommandType.Text, parameters);
        }

        #endregion

        #region 06-执行存储过程脚本,返回dataReader
        public static SqlDataReader ExcuteDataReader(string sqlStr, CommandType cmdType, params  SqlParameter[] parameters)
        {
            //SqlDataReader要求读取数据是,独占sqlconnection对象
            SqlConnection conn = new SqlConnection(SqlHelper.GetConnStr());//不要释放连接,因为后面要保持连接持续打开状态
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = cmdType;
            cmd.CommandText = sqlStr;
            cmd.Parameters.AddRange(parameters);
            //CommandBehavior.CloseConnection 当sqldatareader释放的时候,顺便把sqlconnection也释放掉
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion
    }
}

注意05-06

   #region //06-06 跳到某一页---存储过程
        private void btnSkip_Click(object sender, EventArgs e)
        {
            //01-00 设置强类型数据源
            List<UserInfo> userInfoList = new List<UserInfo>();
            int pageIndex = int.Parse(txtSkipPage.Text);
            string sqlStr = "usp_UserInfo_GetPageData";
            SqlParameter[] parameters =
            {
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            SqlDataReader reader = SqlHelper.ExcuteDataReader(sqlStr,CommandType.StoredProcedure, parameters);
            while (reader.Read())
            {
                //数据封装
                UserInfo userInfo = new UserInfo();
                userInfo.EmpId = int.Parse(reader["EmpId"].ToString());
                userInfo.Pwd = reader["Pwd"].ToString();
                userInfo.StuName = reader["StuName"].ToString();
                userInfo.StuAge = int.Parse(reader["StuAge"].ToString());
                userInfo.Delflag = Char.Parse(reader["Delflag"].ToString());
                userInfo.ClassNo = int.Parse(reader["ClassNo"] == DBNull.Value ? "-1" : reader["ClassNo"].ToString());
                //添加到列表中
                userInfoList.Add(userInfo);
            }
            //01-06 配置数据源
            this.dataGridView1.DataSource = userInfoList;
         }
      
        #endregion

Main中06方法

　　12.5 运行效果