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 _01SqlDataAdapterDemo
{
public partial class SqlCommandBuilderCRUD : Form
{
public SqlCommandBuilderCRUD()
{
InitializeComponent();
}
private void SqlCommandBuilderCRUD_Load(object sender, EventArgs e)
{
string connSql = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
string sql =
@"Select UserId, UserName, UserAge, DelFlag, CreateDate, UserPwd, LastErrorDateTime, ErrorTimes from userInfo";
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql))
{
DataTable dt =new DataTable();
adapter.Fill(dt);
this.dgvUserInfoCRUD.DataSource = dt;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
//把DataGridView的修改的数据保存到数据库中去。
string connSql = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
//修改的sql一定要跟 查询的sql脚本一致。
string sql =
@"Select UserId, UserName, UserAge, DelFlag, CreateDate, UserPwd, LastErrorDateTime, ErrorTimes from userInfo";
using (SqlConnection conn = new SqlConnection(connSql))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
{
//adapter.in
//拿到修改完了之后的DataTable对象
DataTable dt = this.dgvUserInfoCRUD.DataSource as DataTable; //
#region 手写 删除 SqlCommand
//删除的Command
adapter.DeleteCommand = conn.CreateCommand();
adapter.DeleteCommand.CommandText = "delete from UserInfo where UserId=@UserId";
//执行删除操作,把 UserId列的值 给@UserId参数用。
adapter.DeleteCommand.Parameters.Add("@UserId",SqlDbType.Int,4,"UserId");
#endregion
#region 手写 修改的 SqlCommand
adapter.UpdateCommand = conn.CreateCommand();
adapter.UpdateCommand.CommandText = "update UserInfo set UserName=@UserName,UserAge=@UserAge where UserId=@UserId";
//执行删除操作,把 UserId列的值 给@UserId参数用。
adapter.UpdateCommand.Parameters.Add("@UserId", SqlDbType.Int, 4, "UserId");
adapter.UpdateCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 32, "UserName");
adapter.UpdateCommand.Parameters.Add("@UserAge", SqlDbType.Int, 4, "UserAge");
#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("保存成功");
}
}
}