用SqlDataAdapter适配器进行增删改查

最新推荐文章于 2022-03-30 16:37:05 发布

mqy1212

最新推荐文章于 2022-03-30 16:37:05 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

分类专栏： C# 问题集锦文章标签： date exception object string insert delete

本文链接：https://blog.youkuaiyun.com/mqy1212/article/details/4711892

C# 问题集锦专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种利用SqlDataAdapter简化datagridview或gridcontrol中数据处理的方法，通过创建适配器并定义SQL命令来实现增删改查等功能，提供了一个实际应用场景的代码示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在工作上碰到经常要在datagridview或者gridcontrol上直接进行数据处理，以前要写很多繁琐的代码，现在利用SqlDataAdapter适配器可以很快可以完成，而且在代码量上少写尽一半。但是在SqlDataAdapter适配器操作问题上各有各的方式，我曾比较测试过几种方式，现将个人认为比较方便简单的方式总结如下：

1、创建SqlDataAdapter适配器

        /// <summary>
        /// 创建SqlDataAdapter适配器
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static SqlDataAdapter CreateSqlDataAdapter(SqlConnection connection)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            // Create the commands.
            adapter.SelectCommand = new SqlCommand(
                "select id, ProjectID ,EmployeeID, [Date],StartTime,EndTime,[Hour],[WeekDay] from [Hour] order by [Date] desc", connection);
            adapter.InsertCommand = new SqlCommand(
                "INSERT INTO Hour([EmployeeID], [ProjectID], [Hour], [Date], [StartTime], [EndTime], [WeekDay]) Values " +
                    " (@EmployeeID,@ProjectID,@Hour,@Date,@StartTime,@EndTime,@WeekDay)", connection);
            adapter.UpdateCommand = new SqlCommand(
                " UPDATE [Hour] SET [EmployeeID]=@EmployeeID, [ProjectID]=@ProjectID, [Hour]=@Hour, [Date]=@Date, [StartTime]=@StartTime, " +
                "[EndTime]=@EndTime, [WeekDay]= @WeekDay WHERE id = @Id", connection);
            adapter.DeleteCommand = new SqlCommand(
                "DELETE FROM Hour WHERE id = @id", connection);

            // Create the parameters.
            adapter.InsertCommand.Parameters.Add("@EmployeeID",
               SqlDbType.Int, 4, "EmployeeID");
            adapter.InsertCommand.Parameters.Add("@ProjectID",
               SqlDbType.NVarChar, 50, "ProjectID");
            adapter.InsertCommand.Parameters.Add("@Hour",
               SqlDbType.Decimal, 12, "Hour");
            adapter.InsertCommand.Parameters.Add("@Date",
               SqlDbType.DateTime, 8, "Date");
            adapter.InsertCommand.Parameters.Add("@StartTime",
               SqlDbType.DateTime, 8, "StartTime");
            adapter.InsertCommand.Parameters.Add("@EndTime",
               SqlDbType.DateTime, 8, "EndTime");
            adapter.InsertCommand.Parameters.Add("@WeekDay",
               SqlDbType.NVarChar, 50, "WeekDay");

            adapter.UpdateCommand.Parameters.Add("@Id",
                 SqlDbType.Int, 4, "Id");
            adapter.UpdateCommand.Parameters.Add("@EmployeeID",
               SqlDbType.Int, 4, "EmployeeID");
            adapter.UpdateCommand.Parameters.Add("@ProjectID",
               SqlDbType.NVarChar, 50, "ProjectID");
            adapter.UpdateCommand.Parameters.Add("@Hour",
               SqlDbType.Decimal, 12, "Hour");
            adapter.UpdateCommand.Parameters.Add("@Date",
               SqlDbType.DateTime, 8, "Date");
            adapter.UpdateCommand.Parameters.Add("@StartTime",
               SqlDbType.DateTime, 8, "StartTime");
            adapter.UpdateCommand.Parameters.Add("@EndTime",
               SqlDbType.DateTime, 8, "EndTime");
            adapter.UpdateCommand.Parameters.Add("@WeekDay",
               SqlDbType.NVarChar, 50, "WeekDay");
            adapter.UpdateCommand.Parameters.Add("@oldId",
                SqlDbType.Int, 4, "Id").SourceVersion =
                DataRowVersion.Original;

            adapter.DeleteCommand.Parameters.Add("@Id",
                 SqlDbType.Int, 4, "Id").SourceVersion =
                DataRowVersion.Original;

return adapter;
}

2、定义一个全局的dt，如EmpDT，在做所有的增删改查前必须注意的是必须要填充dAdapter.Fill(EmpDT);
在做除查询之外的其它操作时必须前把EmpDT绑定到网格控件，即建立关联

            SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
            dAdapter.Fill(EmpDT);
            gridControl1.DataSource = EmpDT;

3、增删改操作都用如下语句：

SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
int count = dAdapter.Update(EmpDT);

4、具体代码仅供参考：

/// <summary>
        /// 数据绑定查询
        /// </summary>
        /// <param name="num"></param>
        private void InitData()
        {
            //调整第一列的宽度以显示行号
            this.gridView1.IndicatorWidth = 50;

EmpDT.Rows.Clear();
EmpDT.AcceptChanges();

            SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
            dAdapter.Fill(EmpDT);
            gridControl1.DataSource = EmpDT;

            SetColumnIsReadOnly(true);

        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            SetColumnIsReadOnly(false);
            a(false);

DataRow dr = EmpDT.NewRow();

int dtRowCount = EmpDT.Rows.Count;

            if (dtRowCount > 1)
            {
                string proID = EmpDT.Rows[dtRowCount - 1]["ProjectID"].ToString();
                int eID = Convert.ToInt32(EmpDT.Rows[dtRowCount - 1]["EmployeeID"].ToString());

                dr["ProjectID"] = proID;
                dr["EmployeeID"] = eID;
            }

            EmpDT.Rows.Add(dr);
            this.gridView1.FocusedRowHandle = dtRowCount;
            gridControl1.DataSource = EmpDT;

            //默认值
            DateTime now = DateTime.Now;
            string sTime = "8:30";
            string eTime = "17:30";

            this.gridView1.SetFocusedRowCellValue(this.gridView1.Columns["Date"], now);
            this.gridView1.SetFocusedRowCellValue(this.gridView1.Columns["StartTime"], Convert.ToDateTime(sTime));
            this.gridView1.SetFocusedRowCellValue(this.gridView1.Columns["EndTime"], Convert.ToDateTime(eTime));

            //计算星期几
            this.gridView1.SetFocusedRowCellValue(this.gridView1.Columns["WeekDay"], GetWeekDayByDate(now));
            //计算工时
            this.gridView1.SetFocusedRowCellValue(this.gridView1.Columns["Hour"], GetHourByTime(sTime, eTime).ToString("0.00"));   //hour.ToString("0.00")设置显示格式

IsAdd = true;
}

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            if (!IsMod)
            {
                MyMessageBox.MessageBoxByInformation("请先启用修改状态之后再修改！");
                return;
            }
            else
            {
                this.gridView1.FocusedRowHandle--;
                this.gridView1.FocusedRowHandle++;

                try
                {
                    SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
                    int count = dAdapter.Update(EmpDT);
                    if (count > 0)
                    {
                        MyMessageBox.MessageBoxByInformation("成功修改了 " + count + " 条数据！");
                        return;
                    }
                }
                catch (Exception ex)
                {
                    MyMessageBox.MessageBoxByError("修改失败： "+ex.Message);
                    return;
                }
                finally
                {
                    SetColumnIsReadOnly(true);
                    a(true);
                    IsMod = false;
                }
            }

}

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            this.gridView1.FocusedRowHandle--;
            this.gridView1.FocusedRowHandle++;

            if (IsAdd || IsImport)
            {
                try
                {
                    SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
                    int count = dAdapter.Update(EmpDT);
                    if (count > 0)
                    {
                        MyMessageBox.MessageBoxByInformation("成功添加了 " + count + " 条数据！");
                        return;
                    }
                }
                catch (Exception ex)
                {
                    MyMessageBox.MessageBoxByError("添加失败： " + ex.Message);
                    return;
                }
                finally
                {
                    SetColumnIsReadOnly(true);
                    a(true);
                    IsAdd = false;
                }
            }
            else
            {
                MyMessageBox.MessageBoxByInformation("请在执行保存前先点击“新增”录入数据或点击“导入”导入数据！");
                return;
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripButton4_Click(object sender, EventArgs e)
        {

if (this.gridControl1 == null || this.gridView1.DataRowCount == 0)
return;

            if (this.gridView1.IsDataRow(this.gridView1.FocusedRowHandle))
            {
                DialogResult dr = MyMessageBox.MessageBoxByOKCancel("你确定要删除吗？如果确定，请点击“确定”，否则点击“取消”。");

                if (dr == DialogResult.OK)
                {
                    try
                    {
                        EmpDT.Rows[this.gridView1.FocusedRowHandle].Delete();
                        SqlDataAdapter dAdapter = CreateSqlDataAdapter(DBCommon.Connection);
                        int count = dAdapter.Update(EmpDT);
                        if (count > 0)
                        {
                            MyMessageBox.MessageBoxByInformation("成功删除了 " + count + " 条数据！");
                            return;
                        }
                    }
                    catch (Exception ex)
                    {
                        MyMessageBox.MessageBoxByError("删除失败： " + ex.Message);
                        return;
                    }
                }
            }

        }