判断记录是否存在的通用方法

在数据写入到数据库时，通常要根据某些条件检测记录是否存在，然后决定是否要写入，数据写入分两种情形：新添加数据和更新数据，在通用权限底层中，对这类判断进行了封装。

位置截图

主要核心代码部分

        /// <summary>
        /// 判断存在
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual bool Exists(List<KeyValuePair<string, object>> parameters, object id = null)
        {
            bool result = false;
            if (id != null)
            {
                result = DbLogic.Exists(DbHelper, this.CurrentTableName, parameters, new KeyValuePair<string, object>(this.PrimaryKey, id));
            }
            else
            {
                result = DbLogic.Exists(DbHelper, this.CurrentTableName, parameters);
            }
            return result;
        }

        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="parameters">参数</param>
        /// <param name="targetField">获取字段</param>
        /// <param name="name">目标字段名</param>
        /// <param name="parameters">目标字段值</param>
        /// <returns>存在</returns>
        public static bool Exists(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, KeyValuePair<string, object> parameter = new KeyValuePair<string, object>())
        {
            return GetCount(dbHelper, tableName, parameters, parameter) > 0;
        }
        /// <summary>
        /// 获取个数
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="parameters">目标字段,值</param>
        /// <returns>行数</returns>
        public static int GetCount(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, KeyValuePair<string, object> parameter = new KeyValuePair<string, object>())
        {
            int result = 0;
            string sqlQuery = "SELECT COUNT(1) "
                + " FROM " + tableName
                + " WHERE " + GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);

            if (!string.IsNullOrEmpty(parameter.Key))
            {
                if (parameter.Value != null)
                {
                    sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " <> '" + parameter.Value + "' ) ";
                }
                else
                {
                    sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " IS NOT NULL) ";
                }
            }

            object returnObject = null;
            if (parameters != null)
            {
                returnObject = dbHelper.ExecuteScalar(sqlQuery, dbHelper.MakeParameters(parameters));
            }
            else
            {
                returnObject = dbHelper.ExecuteScalar(sqlQuery);
            }
            if (returnObject != null)
            {
                result = int.Parse(returnObject.ToString());
            }
            return result;
        }

该方法支持多种数据库(Oracle、MySQL、MSSQL等)，id为记录的主键，新增时是null。可以参照引入到自己项目中。

方法应用举例

如在新增公司时，要求公司编号和公司名称不可以重复，前端文本框值变化时使用了Ajax异步判断。

        /// <summary>
        /// 验证公司编号是否重复
        /// </summary>
        /// <param name="code"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private bool ValidateCode(string code, string id)
        {
            code = DbLogic.SqlSafe(code);
            id = DbLogic.SqlSafe(id);
            return !organizeManager.Exists(new KeyValuePair<string, object>(BaseOrganizeEntity.FieldCode, code), id);
        }
        /// <summary>
        /// 验证公司名称是否重复
        /// </summary>
        /// <param name="name"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        private bool ValidateName(string name, string id)
        {
            name = DbLogic.SqlSafe(name);
            id = DbLogic.SqlSafe(id);
            return !organizeManager.Exists(new KeyValuePair<string, object>(BaseOrganizeEntity.FieldFullName, name), id);
        }

在新增公司时，Id为null，更新时传入公司的主键Id。

有了这个方法，表中记录存在判断处理就方便多了，而且支持增加多种条件的限制判断（参数List<KeyValuePair<string, object>> parameters）。