C#自定义特性-SQL

语法

原则

        自定义特性必须继承自System.Attribute类；
        AttributeUsage属性来指定特性的使用范围和是否允许重复等；
        在特性类中定义属性，这些属性将用于存储特性值。

示例

using System;

// 定义一个自定义特性类
[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = true)]
public class CustomAttribute : Attribute
{
    // 特性属性
    public string Description { get; set; }
    public int Version { get; set; }

    // 特性构造函数
    public CustomAttribute(string description, int version)
    {
        Description = description;
        Version = version;
    }
}

// 使用自定义特性
[Custom("This is a sample class", 1)]
public class SampleClass
{
    [Custom("This is a sample method", 1)]
    public void SampleMethod()
    {
        // 方法实现
    }
}

class Program
{
    static void Main()
    {
        // 获取SampleClass类的特性信息
        var classAttributes = typeof(SampleClass).GetCustomAttributes(typeof(CustomAttribute), false);
        foreach (CustomAttribute attr in classAttributes)
        {
            Console.WriteLine($"Class Description: {attr.Description}, Version: {attr.Version}");
        }

        // 获取SampleMethod方法的特性信息
        var methodAttributes = typeof(SampleClass).GetMethod("SampleMethod").GetCustomAttributes(typeof(CustomAttribute), false);
        foreach (CustomAttribute attr in methodAttributes)
        {
            Console.WriteLine($"Method Description: {attr.Description}, Version: {attr.Version}");
        }
    }
}

AttributeUsage中的AllowMultiple属性默认值为false，它决定同种特性类型的实例能否在同一个目标上多次使用，比如在类中的同一个属性上。 

特性声明代码

using System;
using System.Reflection;

namespace Model.Common
{
    /// <summary>
    /// 用于生成SQLServer数据库查询的like条件
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class SqlLikeAttribute : Attribute
    {
        /// <summary>
        /// 数据库字段名
        /// </summary>
        public string FieldName { get; set; }
        public SqlLikeAttribute(string fidleName)
        {
            FieldName = fidleName;
        }
    }

    /// <summary>
    /// 用于生成SQLServer数据库查询的>、>=、<、<==、=条件
    /// </summary>
    [AttributeUsage(AttributeTargets.Property,AllowMultiple = false)]
    public class SqlRangeAttribute: Attribute
    {
        /// <summary>
        /// 数据库字段名
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 取值范围：>、>=、<、<==、=
        /// </summary>
        public string Range { get; set; }
        public SqlRangeAttribute(string fidleName, string range)
        {
            FieldName = fidleName;
            Range= range;
        }
    }

    /// <summary>
    /// 用于生成SQLServer数据库查询的between条件
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class SqlBetweenAttribute : Attribute
    {
        /// <summary>
        /// 数据库字段名
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 查询条件实体中的另一个字段名
        /// </summary>
        public string AnotherFieldName { get; set; }
        /// <summary>
        /// 是否是开始
        /// </summary>
        public bool IsStart { get; set; }
        public Object Value { get; set; }
        public SqlBetweenAttribute(string fidleName, string anotherFieldName, bool start = true)
        {
            FieldName = fidleName;
            AnotherFieldName = anotherFieldName;
            IsStart = start;
        }
    }

    /// <summary>
    /// 用于生成SQLServer数据库查询的条件，有SqlIgnoreAttribute修饰的属性直接忽略掉
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class SqlIgnoreAttribute : Attribute
    {
    }

    /// <summary>
    /// 测试
    /// </summary>
    public class AttributeTest
    {
        public static void Test()
        {
            TestModel testModel = new TestModel()
            {
                ID = 1,
                Name = "test",
                Begin = DateTime.Now,
                End = DateTime.Now.AddDays(1),
            };
            Type type = testModel.GetType();
            PropertyInfo[] infos = type.GetProperties();
            foreach (PropertyInfo info in infos)
            {
                SqlBetweenAttribute attr = (SqlBetweenAttribute)info.GetCustomAttribute(typeof(SqlBetweenAttribute), false);
                if (attr != null)
                {
                    string field = attr.FieldName;
                }
            }
        }
    }

    public class TestModel
    {
        public int ID { get; set; }
        public string Name { get; set; }
        [SqlBetween("field", "End")]
        public DateTime Begin { get; set; }
        [SqlBetween("field", "Begin", false)]
        public DateTime End { get; set; }
    }
}

使用特性

        /// <summary>
        /// 从实体中获取属性值不为空的属性和值，用于数据库Select的Where条件
        /// 暂时只支持，int、string、double、DateTime
        /// 要求数值类型默认为-1
        /// </summary>
        /// <param name="data"></param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public static List<string> GetPropertyValueNotNullForSelectWhere(this object data, string prefix)
        {
            string item = "";
            List<string> items = new List<string>();
            Dictionary<string, SqlBetweenAttribute> dic = new Dictionary<string, SqlBetweenAttribute>();
            DateTime dateTime = new DateTime(1, 1, 1, 0, 0, 0);

            PropertyInfo[] propertyInfos = data.GetType().GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                if (propertyInfo.Name.ToUpper() == "ID")
                    continue;

                item = "";
                object obj = propertyInfo.GetValue(data, null);
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                        if (Convert.ToInt32(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToInt32(obj)}";
                        break;
                    case "System.String":
                        if (Convert.ToString(obj) == "")
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}='{Convert.ToString(obj)}'";
                        break;
                    case "System.Double":
                        if (Convert.ToDouble(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToDouble(obj)}";
                        break;
                    case "System.Decimal":
                        if (Convert.ToDecimal(obj) == -1)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}={Convert.ToDecimal(obj)}";
                        break;
                    case "System.DateTime":
                        obj = propertyInfo.GetValue(data, null);
                        if (Convert.ToDateTime(obj) == dateTime)
                            continue;
                        item = $" and {prefix}{propertyInfo.Name}='{Convert.ToDateTime(obj)}'";
                        break;
                }

                //if(!CheckAttrSqlBetween(propertyInfo, obj, ref dic, ref item))
                //    continue;

                CheckAttrSqlRange(propertyInfo, obj, ref item, prefix);
                CheckAttrSqlLike(propertyInfo, obj, ref item, prefix);
                if (!CheckAttrSqlIgnore(propertyInfo, obj, ref item))
                    continue;

                items.Add(item);
            }
            return items;
        }

        /// <summary>
        /// 检查属性是否被SqlBetween特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo">实体的属性对象</param>
        /// <param name="obj">属性的值</param>
        /// <param name="dic">暂存特性的字典</param>
        /// <param name="item"></param>
        /// <returns>true 表示需要把item加到List<string>中</returns>
        static bool CheckAttrSqlBetween(PropertyInfo propertyInfo, object obj, ref Dictionary<string, SqlBetweenAttribute> dic, ref string item)
        {
            SqlBetweenAttribute attr = (SqlBetweenAttribute)propertyInfo.GetCustomAttribute(typeof(SqlBetweenAttribute), false);
            if (attr == null)
                return true;

            attr.Value = obj;
            if (!dic.ContainsKey(attr.AnotherFieldName))
            {   //缺少另外一个，先缓存
                dic.Add(attr.AnotherFieldName, attr);
                return false;
            }
            else
            {
                SqlBetweenAttribute _attr = dic[attr.AnotherFieldName];
                dic.Remove(attr.AnotherFieldName);

                SqlBetweenAttribute attrb = attr.IsStart ? attr : _attr;
                SqlBetweenAttribute attre = attr.IsStart ? _attr : attr;
                switch (propertyInfo.PropertyType.FullName)
                {
                    case "System.Int32":
                    case "System.Double":
                    case "System.Decimal":
                        item = $" and {attr.FieldName} between {attrb.Value} and {attre.Value}";
                        break;
                    case "System.String":
                    case "System.DateTime":
                        item = $" and {attr.FieldName} between '{attrb.Value}' and '{attre.Value}'";
                        break;
                }
                return true;
            }
        }

        /// <summary>
        /// 检查属性是否被SqlRange特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        static void CheckAttrSqlRange(PropertyInfo propertyInfo, object obj, ref string item, string prefix)
        {
            SqlRangeAttribute attr = (SqlRangeAttribute)propertyInfo.GetCustomAttribute(typeof(SqlRangeAttribute), false);
            if (attr == null)
                return;

            switch (propertyInfo.PropertyType.FullName)
            {
                case "System.Int32":
                case "System.Double":
                case "System.Decimal":
                    item = $" and {prefix}{attr.FieldName} {attr.Range} {obj} ";
                    break;
                case "System.String":
                case "System.DateTime":
                    item = $" and {prefix}{attr.FieldName} {attr.Range} '{obj}' ";
                    break;
            }
            return;
        }

        /// <summary>
        /// 检查属性是否被SqlLike特性修饰，并处理
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        static void CheckAttrSqlLike(PropertyInfo propertyInfo, object obj, ref string item, string prefix)
        {
            SqlLikeAttribute attr = (SqlLikeAttribute)propertyInfo.GetCustomAttribute(typeof(SqlLikeAttribute), false);
            if (attr == null)
                return;

            switch (propertyInfo.PropertyType.FullName)
            {
                case "System.String":
                    item = $" and ({prefix}{attr.FieldName} like '%{obj}%' or {prefix}{attr.FieldName}='{obj}') ";
                    break;
            }
            return;
        }

        /// <summary>
        /// 检查属性是否被SqlIgnoreAttribute特性修饰，如果修饰则不加入到Where条件中
        /// </summary>
        /// <param name="propertyInfo"></param>
        /// <param name="obj"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        static bool CheckAttrSqlIgnore(PropertyInfo propertyInfo, object obj, ref string item)
        {
            SqlIgnoreAttribute attr = (SqlIgnoreAttribute)propertyInfo.GetCustomAttribute(typeof(SqlIgnoreAttribute), false);
            if (attr == null)
                return true;
            else
                return false;
        }