步步为营-45-一套增删查改
说明:比较完整的增删查改
1:新建窗体项目,(命名规范)
2:添加dataGridView控件
3:通过AppConfig创建连接字符串
<connectionStrings>
<add name="connStr" connectionString="server=.;uid=sa;pwd=sa;database=DemoDB"/>
</connectionStrings>
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方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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
5.2 使用强类型数据,添加UserInfo类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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; }
}
5.3 修改显示字段
6 删除
添加一个右击菜单contextMenuStrip,设置整行选中属性中SelectionMode == FullRowSelect
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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 保存后应该关闭"编辑窗体",同时刷新"主窗体"
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
8 多条件查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
9 代码优化
从功能分析可以得知,我们查询后的表格显示和页面加载时的表格显示,调用的方法类似,有大量的重复代码. 可以考虑优化一下.观察发现两个方法最大的不同点在于sql脚本不一样.
优化二,用户修改后显示搜索后页面,修改138行代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
10.2 编辑窗体代码修改前
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 编辑窗体代码修改后
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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窗体修改代码修改前
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
10.5 Main窗体修改代码修改后
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
11 分页
11.1 判断是否有行被选中
if (dataGridView1.SelectedRows.Count == 0 )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
12 跳转到某一页(通过存储过程)
12.1 储存过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
12.3 Main代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
12.4 进一步改进代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#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
12.5 运行效果