DataGridView数据更新至数据库-逐条更新

C# 专栏收录该内容
21 篇文章 0 订阅

首先判断是否存在指定记录,存在则执行更新语句,不存在则执行插入语句。主要用到三个函数:

 public class PubVariant
    {
        public static string strUpdateSql = "update CorrespondFields set CadField = @CadField,FieldType = @FieldType,CADTYPE = @CADTYPE"
            + " where SdeLayerName = @SdeLayerName and CadLayerName = @CadLayerName and SdeField = @SdeField";
        public static string strInsertSql = "insert into CorrespondFields values(@SdeLayerName,@CadLayerName,@SdeField,@CadField,@FieldType,@CADTYPE)";
    }

        /// <summary>
        /// 判断数据库是否有指定键值的记录
        /// </summary>
        /// <param name="str">键值</param>
        /// <returns>是否存在记录的布尔值</returns>
        public static bool ExistsRecord(string str)
        {
            string strSql = "select * from CorrespondFields where SdeField = '" + str + "' and SdeLayerName = '" + PubVariant.sdeLayerName
            + "' and CadLayerName = '" + PubVariant.cadLayerName + "'";
            using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                SqlDataReader datareader = cmd.ExecuteReader();
                return datareader.HasRows;
            }
            
        }

        /// <summary>
        /// 执行带参数的Sql语句
        /// </summary>
        /// <param name="sqlParas">sql参数数组</param>
        /// <param name="strSql">要执行的sql语句</param>
        public static void ExecuteSql(SqlParameter[] sqlParas, string strSql)
        {
            using (SqlConnection connection = new SqlConnection(PubVariant.ConnectionString))
            {
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(strSql, connection))
                {
                    foreach (SqlParameter sp in sqlParas)
                    {
                        cmd.Parameters.Add(sp);
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 用datagridview的数据更新数据库
        /// </summary>
        /// <param name="dgv">datagridview</param>
        /// <returns>更新是否成功</returns>
        public static bool UpdataFromDGVtoDB(DataGridView dgv)
        {
            try
            {
                for (int i = 0; i < dgv.Rows.Count - 1; i++)
                {
                    string strCADTYPE;
                    if (dgv.Rows[i].Cells[1].Value.ToString().StartsWith("["))
                    {
                        strCADTYPE = "1";
                    }
                    else
                    {
                        strCADTYPE = "2";
                    }

                    SqlParameter[] sqlParas = new SqlParameter[]
                    {
                        new SqlParameter("@SdeLayerName", PubVariant.sdeLayerName),
                        new SqlParameter("@CadLayerName", PubVariant.cadLayerName),
                        new SqlParameter("@SdeField", dgv.Rows[i].Cells[0].Value.ToString()),
                        new SqlParameter("@CadField", dgv.Rows[i].Cells[1].Value.ToString()),
                        new SqlParameter("@FieldType", dgv.Rows[i].Cells[2].Value.ToString()),
                        new SqlParameter("@CADTYPE", strCADTYPE)                        
                    };

                    if (ExistsRecord(dgv.Rows[i].Cells[0].Value.ToString()))
                    {
                        ExecuteSql(sqlParas, PubVariant.strUpdateSql);
                    }
                    else
                    {
                        ExecuteSql(sqlParas, PubVariant.strInsertSql);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "系统提示");
                return false;
            }
        }


  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 猿与汪的秘密 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值