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

来源：互联网发布：美国人口老龄化数据编辑：程序博客网时间：2024/06/16 08:51

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("保存成功");        }    }}

阅读全文

0 0