运行效果.png
一、准备数据库Student，数据表TableStudent
CREATE TABLE [dbo].[TableStudent] (
[stuId] INT IDENTITY (1, 1) NOT NULL,
[stuName] NVARCHAR (32) NULL,
[stuSex] NCHAR (2) NULL,
[stuBirthdate] NVARCHAR (32) NULL,
[stuPhone] NVARCHAR (32) NULL,
PRIMARY KEY CLUSTERED ([stuId] ASC)
);
二、获取数据库连接字符串
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace DataAdapterExample
{
class sqlHelper
{
//获取数据库连接字符串
public static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["strConnect"].ConnectionString;
}
#region 封装一个执行SQL返回受影响的行数
public static int ExecuteNoQuery(string sqlText,params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteNonQuery();
}
}
}
#endregion
#region 封装一个执行SQL返回查询结果中第一行第一列的值
public static object ExecuteScalar(string sqlText,params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters.ToArray());
return cmd.ExecuteScalar();
}
}
}
#endregion
#region 封装一个执行SQL返回一个DataTable
public static DataTable ExecuteDataTable(string sqlText,params SqlParameter[] parameters)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText,GetConnectionString()))
{
DataTable dt = new DataTable();
adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
adapter.Fill(dt);
return dt;
}
}
#endregion
#region 封装一个执行SQL返回一个SqlDataReader
public static SqlDataReader ExecutedReader(string sqlText,params SqlParameter[] parameters)
{
//SqlDataReader要求独占SqlConnection对象，并且SqlConnection必须是Open状态
SqlConnection con = new SqlConnection(GetConnectionString());
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlText;
cmd.Parameters.AddRange(parameters.ToArray());
//SqlDataReader执行完成后顺便关闭数据库连接
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
}
}
三、数据表映射StudentInfo帮助类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataAdapterExample
{
public class StudentInfo
{
public int stuId { get; set; }
public string stuName { get; set; }
public string stuSex { get; set; }
public string stuBirthdate { get; set; }
public string stuPhone { get; set; }
}
}
四、主窗体对数据表进行CURD
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace DataAdapterExample
{
public partial class MainFrm : Form
{
//标识正在修改的数据行的主键
private int updateStudentId = 0;
public MainFrm()
{
InitializeComponent();
}
//主窗体加载数据
private void MainFrm_Load(object sender, EventArgs e)
{
LoadStudentInfo();
}
//增加数据
private void ButtonAdd_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "insert into TableStudent (stuName,stuPhone,stuBirthdate,stuSex) values (@stuName,@stuPhone,@stuBirthdate,@stuSex)";
cmd.Parameters.AddWithValue("@stuName", this.textBoxstuName.Text.Trim());
cmd.Parameters.AddWithValue("@stuPhone", this.textBoxstuPhone.Text.Trim());
cmd.Parameters.AddWithValue("@stuSex", this.textBoxstuSex.Text.Trim());
cmd.Parameters.AddWithValue("@stuBirthdate", this.textBoxstuBirthdate.Text.Trim());
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("增加数据成功！");
}
}
}
LoadStudentInfo();
}
//删除数据
private void ButtonDelete_Click(object sender, EventArgs e)
{
if (this.dataGridViewFromAdapter.SelectedRows.Count <= 0)
{
MessageBox.Show("请选择要删除的数据");
}
if (MessageBox.Show("确认要删除吗？", "提醒消息", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) != DialogResult.Yes)
{
return;
}
int deleteStuId = int.Parse(this.dataGridViewFromAdapter.SelectedRows[0].Cells["stuId"].Value.ToString());
#region 原生手写
//using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
//{
// con.Open();
// string sql = "delete from TableStudent where stuId=@stuId";
// using (SqlCommand cmd = con.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.Parameters.AddWithValue("stuId", deleteStuId);
// //cmd.Parameters.Add("@stuId", SqlDbType.Int);
// //cmd.Parameters["stuId"].Value = deleteStuId;
// //SqlParameter parameter = new SqlParameter();
// //parameter.ParameterName = "@stuId";
// //parameter.Value = deleteStuId;
// //cmd.Parameters.Add(parameter);
// if (cmd.ExecuteNonQuery() > 0)
// {
// MessageBox.Show("删除成功");
// }
// }
//}
#endregion
#region sqlHelper封装方法ExecuteNoQuery
string strSQL = "delete from TableStudent where stuId=@stuId";
int num = sqlHelper.ExecuteNoQuery(strSQL, new SqlParameter("@stuId", (object)deleteStuId));
if (num>0)
{
MessageBox.Show("删除成功");
}
#endregion
LoadStudentInfo();
}
//修改数据
private void buttonUpdate_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
{
using (SqlCommand cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = "update TableStudent set stuName=@stuName,stuSex=@stuSex,stuBirthdate=@stuBirthdate,stuPhone=@stuPhone where stuId=@stuId";
cmd.Parameters.AddWithValue("@stuName", this.textBoxstuName.Text);
cmd.Parameters.AddWithValue("@stuBirthdate", this.textBoxstuBirthdate.Text);
cmd.Parameters.AddWithValue("@stuSex", this.textBoxstuSex.Text);
cmd.Parameters.AddWithValue("@stuPhone", this.textBoxstuPhone.Text);
cmd.Parameters.AddWithValue("@stuId", this.updateStudentId);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("更新数据成功");
}
}
}
//刷新数据
ButtonSearch_Click(this,null);
}
//选中数据赋值到对应文本框
private void dataGridViewFromAdapter_SelectionChanged(object sender, EventArgs e)
{
if (this.dataGridViewFromAdapter.SelectedRows.Count <= 0)
{
//MessageBox.Show("请先选中数据");
return;
}
//选中行的Id
int selectedId = int.Parse(this.dataGridViewFromAdapter.SelectedRows[0].Cells["stuId"].Value.ToString());
//把要修改的数据行的ID放到当前窗体的updateStudentId字段中保存
updateStudentId = selectedId;
using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "select stuId,stuName,stuSex,stuBirthdate,stuPhone from TableStudent where stuId=@stuId";
cmd.Parameters.AddWithValue("@stuId", selectedId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
this.textBoxstuSex.Text = reader["stuSex"].ToString().Trim();
this.textBoxstuName.Text = reader["stuName"].ToString().Trim();
this.textBoxstuBirthdate.Text = reader["stuBirthdate"].ToString().Trim();
this.textBoxstuPhone.Text = reader["stuPhone"].ToString().Trim();
}
}
}
}
}
//双击弹出修改窗体
private void dataGridViewFromAdapter_DoubleClick(object sender, EventArgs e)
{
if (this.dataGridViewFromAdapter.SelectedRows.Count <= 0)
{
return;
}
int editStuId = int.Parse(this.dataGridViewFromAdapter.SelectedRows[0].Cells["stuId"].Value.ToString());
EditStudentForm editStuFrm = new EditStudentForm(new StudentInfo() { stuId = editStuId });
//注册弹出窗体的关闭事件
editStuFrm.FormClosing += EditStuFrm_FormClosing;
editStuFrm.Show();
}
//弹出窗体修改完成关闭的时候执行的操作
private void EditStuFrm_FormClosing(object sender, FormClosingEventArgs e)
{
LoadStudentInfo();
}
//多条件查询
private void ButtonSearch_Click(object sender, EventArgs e)
{
//拼接whereSqlText脚本
string whereSqlText = "select stuId,stuName,stuPhone,stuBirthdate,stuSex from TableStudent";
List<string> whereList = new List<string>();
List<SqlParameter> parameters = new List<SqlParameter>();
if (!string.IsNullOrEmpty(this.textBoxSearchName.Text.Trim()))
{
//把where条件添加到whereList集合中
whereList.Add("stuName like @stuName");
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@stuName";
parameter.Value = "%" + this.textBoxSearchName.Text.Trim() + "%";
parameters.Add(parameter);
}
if (!string.IsNullOrEmpty(this.textBoxSearchSex.Text.Trim()))
{
//把where条件添加到whereList集合中
whereList.Add("stuSex like @stuSex");
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@stuSex";
parameter.Value = "%" + this.textBoxSearchSex.Text.Trim() + "%";
parameters.Add(parameter);
}
if (whereList.Count > 0)
{
whereSqlText += " where " + string.Join(" and ", whereList);
//MessageBox.Show(whereSqlText);
}
//加载数据方法的调用
LoadStudentInfo2DataGridView(whereSqlText, parameters.ToArray());
}
//加载数据
private void LoadStudentInfo()
{
List<StudentInfo> studentInfoList = new List<StudentInfo>();
string sqlText = "select stuId,stuName,stuSex,stuBirthdate,stuPhone from TableStudent";
LoadStudentInfo2DataGridView(sqlText);
}
//加载数据方法的封装
public void LoadStudentInfo2DataGridView(string sqlText,params SqlParameter[] parameters)
{
List<StudentInfo> studentInfoList = new List<StudentInfo>();
#region 原生写法
//using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
//{
// using (SqlDataAdapter adapter = new SqlDataAdapter(sqlText, con))
// {
// //填充之前，给SelectCommand赋参数
// adapter.SelectCommand.Parameters.AddRange(parameters.ToArray());
// DataTable dt = new DataTable();
// adapter.Fill(dt);
// foreach (DataRow row in dt.Rows)
// {
// StudentInfo studentInfo = new StudentInfo();
// studentInfo.stuId = int.Parse(row["stuId"].ToString().Trim());
// studentInfo.stuName = row["stuName"].ToString().Trim();
// studentInfo.stuSex = row["stuSex"].ToString().Trim();
// studentInfo.stuBirthdate = row["stuBirthdate"].ToString().Trim();
// studentInfo.stuPhone = row["stuPhone"].ToString().Trim();
// studentInfoList.Add(studentInfo);
// }
// this.dataGridViewFromAdapter.DataSource = studentInfoList;
// }
//}
#endregion
#region sqlHelper类的ExecuteDataTable方法
DataTable dt = sqlHelper.ExecuteDataTable(sqlText, parameters);
foreach (DataRow row in dt.Rows)
{
StudentInfo studentInfo = new StudentInfo();
studentInfo.stuId = int.Parse(row["stuId"].ToString().Trim());
studentInfo.stuName = row["stuName"].ToString().Trim();
studentInfo.stuSex = row["stuSex"].ToString().Trim();
studentInfo.stuBirthdate = row["stuBirthdate"].ToString().Trim();
studentInfo.stuPhone = row["stuPhone"].ToString().Trim();
studentInfoList.Add(studentInfo);
}
this.dataGridViewFromAdapter.DataSource = studentInfoList;
#endregion
}
}
}
五、双击DataGridView选中行弹出子窗体EditStudentForm对数据可以进行修改
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataAdapterExample
{
public partial class EditStudentForm : Form
{
//窗体属性
public StudentInfo StuInfo { get; set; }
//构造函数接收StudentInfo对象
public EditStudentForm(StudentInfo stuInfo)
{
InitializeComponent();
//将传递的对象赋值给当前对象的属性
StuInfo = stuInfo;
}
//弹出窗体的时候加载传递行stuId对应的数据到对应文本框
private void EditStudentForm_Load(object sender, EventArgs e)
{
#region 原生写法
//using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
//{
// using (SqlCommand cmd = con.CreateCommand())
// {
// con.Open();
// cmd.CommandText = "select stuSex,stuName,stuBirthdate,stuPhone from TableStudent where stuId=@stuId";
// cmd.Parameters.AddWithValue("@stuId", StuInfo.stuId);
// using (SqlDataReader reader = cmd.ExecuteReader())
// {
// if (reader.Read())
// {
// this.textBoxstuBirthdate.Text = reader["stuBirthdate"].ToString().Trim();
// this.textBoxstuName.Text = reader["stuName"].ToString().Trim();
// this.textBoxstuPhone.Text = reader["stuPhone"].ToString().Trim();
// this.textBoxstuSex.Text = reader["stuSex"].ToString().Trim();
// }
// }
// }
//}
#endregion
#region sqlHelper类的ExecuteReader方法
string strSQL = "select stuSex,stuName,stuBirthdate,stuPhone from TableStudent where stuId=@stuId";
using (SqlDataReader reader = sqlHelper.ExecutedReader(strSQL, new SqlParameter("@stuId", (object)StuInfo.stuId)))
{
if (reader.Read())
{
this.textBoxstuBirthdate.Text = reader["stuBirthdate"].ToString().Trim();
this.textBoxstuName.Text = reader["stuName"].ToString().Trim();
this.textBoxstuPhone.Text = reader["stuPhone"].ToString().Trim();
this.textBoxstuSex.Text = reader["stuSex"].ToString().Trim();
}
}
#endregion
}
//保存更新
private void ButtonSave_Click(object sender, EventArgs e)
{
#region 原生写法
//using (SqlConnection con = new SqlConnection(sqlHelper.GetConnectionString()))
//{
// using (SqlCommand cmd = con.CreateCommand())
// {
// con.Open();
// cmd.CommandText = "update TableStudent set stuPhone=@stuPhone,stuName=@stuName,stuBirthdate=@stuBirthdate,stuSex=@stuSex where stuId=@stuId";
// cmd.Parameters.AddWithValue("@stuId", StuInfo.stuId);
// cmd.Parameters.AddWithValue("@stuName", this.textBoxstuName.Text.Trim());
// cmd.Parameters.AddWithValue("@stuBirthdate", this.textBoxstuBirthdate.Text.Trim());
// cmd.Parameters.AddWithValue("@stuPhone", this.textBoxstuPhone.Text.Trim());
// cmd.Parameters.AddWithValue("@stuSex", this.textBoxstuSex.Text.Trim());
// if (cmd.ExecuteNonQuery() > 0)
// {
// MessageBox.Show("更新成功");
// }
// //关闭窗体
// this.Close();
// }
//}
#endregion
#region 使用sqlHelper类的ExecuteNoQuery静态方法
string strSQL = "update TableStudent set stuPhone=@stuPhone,stuName=@stuName,stuBirthdate=@stuBirthdate,stuSex=@stuSex where stuId=@stuId";
List<SqlParameter> parameterList = new List<SqlParameter>();
SqlParameter paraStuName = new SqlParameter("@stuName", SqlDbType.NVarChar, 32);
paraStuName.Value = this.textBoxstuName.Text.Trim();
parameterList.Add(paraStuName);
SqlParameter paraStuSex = new SqlParameter("@stuSex", SqlDbType.NVarChar, 32);
paraStuSex.Value = this.textBoxstuSex.Text.Trim();
parameterList.Add(paraStuSex);
SqlParameter paraStuBirthdate = new SqlParameter("@stuBirthdate", SqlDbType.NVarChar, 32);
paraStuBirthdate.Value = this.textBoxstuBirthdate.Text.Trim();
parameterList.Add(paraStuBirthdate);
SqlParameter paraStuPhone = new SqlParameter("@stuPhone", SqlDbType.NVarChar, 32);
paraStuPhone.Value = this.textBoxstuPhone.Text.Trim();
parameterList.Add(paraStuPhone);
SqlParameter paraStuId = new SqlParameter("@stuId", SqlDbType.NVarChar, 32);
paraStuId.Value = StuInfo.stuId;
parameterList.Add(paraStuId);
int num = sqlHelper.ExecuteNoQuery(strSQL, parameterList.ToArray());
if (num > 0)
{
MessageBox.Show("保存更新成功！");
}
//关闭窗口
this.Close();
#endregion
}
}
}
主窗体最下面的文本框功能：
（1）增加新的数据行
（2）显示选中行数据
（3）修改选中行数据
优化：sqlHelper类中封装方法
（1）执行SQL返回受影响的行数的ExecuteNoQuery方法
（2）执行SQL返回查询结果中第一行第一列的值的ExecuteScalar方法
（3）执行SQL返回一个DataTable的ExecuteDataTable方法
（4）执行SQL返回一个SqlDataReader的ExecutedReader方法
