说明:通过DataAdapter或者dataset连接数据库,实现对数据增删改查操作.
以前写过一篇步步为营-23-通过GridView实现增删改
1:SqlDataAdapter DataTable实现
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 DataView
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
} private void MainForm_Load(object sender, EventArgs e)
{
//01-创建配置文件 添加引用
//02-设置连接字符串]
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//03 创建连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
//04-创建数据库操作脚本
string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr,conn))
{
//05-隆重介绍一下Adapter-适配器
//05-01判断sqlConnection是否初始化
//05-02初始化Select Command对象
//05-03 通过cmd对象执行返回SQLdataReader对象
//05-04 读取数据,填充到datatreader上
DataTable dt = new DataTable();
sda.Fill(dt);
//05-创建实体模型,对数据进行封装
List<UserINfo> userList = new List<UserINfo>();
foreach (DataRow dataRow in dt.Rows)
{
//封装数据
userList.Add(new UserINfo() { EmpId = int.Parse(dataRow["EmpId"].ToString()),
StuName = dataRow["StuName"].ToString(),
StuAge = int.Parse(dataRow["StuAge"].ToString())
}); }
//06-设置数据源
this.dataGridView1.DataSource = userList; }
} }
}
}
2:运行效果
3SqlDataAdapter DataSet实现
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 DataView
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
} private void MainForm_Load(object sender, EventArgs e)
{
//01-创建配置文件 添加引用
//02-设置连接字符串]
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//03 创建连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
//04-创建数据库操作脚本
string sqlStr = @"
select ClassId, ClassName from ClassInfo;
select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr,conn))
{
//05-隆重介绍一下Adapter-适配器
//05-01判断sqlConnection是否初始化
//05-02初始化Select Command对象
//05-03 通过cmd对象执行返回SQLdataReader对象
//05-04 读取数据,填充到datatreader上 DataSet ds = new DataSet();
sda.Fill(ds);
List<UserINfo> userList = new List<UserINfo>();
foreach (DataRow dataRow in ds.Tables[].Rows)
{
//封装数据
userList.Add(new UserINfo()
{
EmpId = int.Parse(dataRow["EmpId"].ToString()),
StuName = dataRow["StuName"].ToString(),
StuAge = int.Parse(dataRow["StuAge"].ToString())
}); }
this.dataGridView1.DataSource = userList;
}
} }
}
}
4 实现增删查改--通过SqlCommandBuilder
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 DataView
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
} private void Form2_Load(object sender, EventArgs e)
{
//01-创建配置文件 添加引用
//02-设置连接字符串]
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//03 创建连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
//04-创建数据库操作脚本
string sqlStr = @"select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr, conn))
{ DataTable dt =new DataTable();
sda.Fill(dt); this.dataGridView1.DataSource = dt;
}
} } private void btnSave_Click(object sender, EventArgs e)
{
//把DataGridView的修改的数据保存到数据库中去。
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //修改的sql一定要跟 查询的sql脚本一致。
string sqlStr = @"select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo"; using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, connStr))
{
//01-拿到修改之后的datatable对象
DataTable dt = this.dataGridView1.DataSource as DataTable;
//把修改完的内存表 映射到数据库中对应的表
//SQLCommandBuilder帮助我们Adapter生成相关的command
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter))
{
adapter.Update(dt);
}
}
}
}
}
5 实现增删查改--手动写
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
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 DataView
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
} private void Form2_Load(object sender, EventArgs e)
{
//01-创建配置文件 添加引用
//02-设置连接字符串]
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//03 创建连接字符串
using (SqlConnection conn = new SqlConnection(connStr))
{
//04-创建数据库操作脚本
string sqlStr = @"select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo";
using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr, conn))
{ DataTable dt =new DataTable();
sda.Fill(dt); this.dataGridView1.DataSource = dt;
}
} } private void btnSave_Click(object sender, EventArgs e)
{
//把DataGridView的修改的数据保存到数据库中去。
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //修改的sql一定要跟 查询的sql脚本一致。
string sqlStr = @"select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo"; using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlStr, conn))
{
//adapter.in
//拿到修改完了之后的DataTable对象
DataTable dt = this.dataGridView1.DataSource as DataTable; // #region 手写 删除 SqlCommand
//删除的Command
adapter.DeleteCommand = conn.CreateCommand();
adapter.DeleteCommand.CommandText = "delete from UserInfo where EmpId=@EmpId"; //执行删除操作，把 EmpId列的值 给@EmpId参数用。
adapter.DeleteCommand.Parameters.Add("@EmpId", SqlDbType.Int, , "EmpId"); #endregion #region 手写 修改的 SqlCommand
adapter.UpdateCommand = conn.CreateCommand();
adapter.UpdateCommand.CommandText = "update UserInfo set Pwd=@Pwd, StuName=@StuName,StuAge=@StuAge ,Delflag=@Delflag, ClassNo=@ClassNo where EmpId=@EmpId"; //执行更新操作
adapter.UpdateCommand.Parameters.Add("@EmpId", SqlDbType.Int, , "EmpId");
adapter.UpdateCommand.Parameters.Add("@Pwd", SqlDbType.NVarChar, , "Pwd");
adapter.UpdateCommand.Parameters.Add("@StuName", SqlDbType.NVarChar, , "StuName");
adapter.UpdateCommand.Parameters.Add("@StuAge", SqlDbType.Int, , "StuAge");
adapter.UpdateCommand.Parameters.Add("@Delflag", SqlDbType.Int, , "Delflag");
adapter.UpdateCommand.Parameters.Add("@ClassNo", SqlDbType.Int, , "ClassNo");
#endregion #region 手写 新增 SqlCommand
adapter.InsertCommand = conn.CreateCommand();
adapter.InsertCommand.CommandText = "INSERT INTO UserInfo (Pwd,StuName,StuAge,Delflag,ClassNo) VALUES (@Pwd,@StuName, @stuAge, @Delflag,@ClassNo)"; //执行更新操作
adapter.InsertCommand.Parameters.Add("@Pwd", SqlDbType.NVarChar, , "Pwd");
adapter.InsertCommand.Parameters.Add("@StuName", SqlDbType.NVarChar, , "StuName");
adapter.InsertCommand.Parameters.Add("@StuAge", SqlDbType.Int, , "StuAge");
adapter.InsertCommand.Parameters.Add("@Delflag", SqlDbType.Int, , "Delflag");
adapter.InsertCommand.Parameters.Add("@ClassNo", SqlDbType.Int, , "ClassNo");
#endregion adapter.Update(dt); #region 使用命令生成器 //找到表中，添加数据，生成一条insert ，Insert Command ExcuteNonQuery到数据库中。
//如果修改的：update
//delet... ////把修改完的内存表dt 变化映射到数据库中的表的变化。
////SqlCommandBuilder帮助我们的Adapter生成相关的CRUD SqlCommand
//using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter))
//{
// adapter.Update(dt);
//} #endregion
}
}//end using Conn
MessageBox.Show("保存成功");
}
}
}
手写的
增
删
改
