C# SqlCommandBuilder封装DRUD命令，更新数据库

SqlCommandBuilder帮助我们的Adapter生成相关的CRUD 的SqlCommand。也可以手动为Adapter写增删查改的SqlCommand命令。

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 _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))
                {
                    //拿到修改完了之后的DataTable对象
                    DataTable dt = this.dgvUserInfoCRUD.DataSource as DataTable; 

                    //把修改完的内存表dt 变化映射到数据库中的表的变化。  DataGridView中的DataTable中数据的变化(增删查改)，直接映射保存到数据库中。
                    //SqlCommandBuilder帮助我们的Adapter生成相关的CRUD 的SqlCommand
                    using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter))
                    {
                        adapter.Update(dt);
                    } 
                }
            }//end  using Conn
            MessageBox.Show("保存成功");  //DataGridView中的DataTable中数据的变化(增删查改)，直接映射保存到数据库中。
        }
    }
}

为SqlDataAdapter手写 增删查改 的SqlCommand 命令，来实现对数据库的更新：

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);
                }
            }//end  using Conn
            MessageBox.Show("保存成功");
        }
    }
}