[C#]C#高级篇Tip1：使用Attribute+反射实现与Sql的高效交互。

摘要

此篇将会带你优雅地实现与数据库的交互，当然，代码直接copy也可以，当然我也打算之后写入类库。

先修内容

熟练掌握与数据库的交互。包括建表，插值，查询，更新，删除等命令语句。
知道C#中的DataSet,DataTable,DataRow等知识。
会用标准库及第三方包(.NuGet)与数据库进行交互。
熟练掌握C#中的反射和特性。

笑话：Reflection和Attribute是一对好伙伴，如果拆散它们，你就没法写出高级的(装逼的)和优雅的功能。

准备工作：开始之前，如果你打算使用MySql数据库，那就到.NuGet中下载一个MySql.Data的包。

情景

小明最近闲的没事，想写一个 Asp. Net Core后端，首先得要一个账号系统，因此不可避免地需要创建表以及和数据库进行交互，他盘算着，他的代码中可能会出现这些代码：

void Set(DataRow row)
{
    id = (int)row[nameof(id)];
    username = (string)row[nameof(username)];
    usertype = (int)row[nameof(usertype)];
    password = (string)row[nameof(password)];
    credit = (string)row[nameof(credit)];
}

提示：nameof(XXX)是C#6.0的新特性，实际上他和"XXX"是等价的。

void GetAddcommand()
{
    return $"insert into user (username,usertype,password,credit) values ('{username}',{usertype},'{password}','{credit}')";
}

$""是插值字符串，有关详情请咨询百度或者等我以后的文章把。

这些代码量直观易懂，但是小明总是觉得自己在搬砖，干着重活累活，到头来都是重复代码，这可把小明给折腾了三天三夜，于是在一个雷电交加的也换，小明苦练出一套C#秘技:Reflection+Attribute大法。

好的，后面的故事就从这里开始。

故事一：接口化实现

小明这几天特别牛逼，他已经能够熟练使用命令行和Sql数据库进行交互了，但是为了模块化，写一个静态类是不优雅的，所以他炮制出了这么一套规则。

提示：模块化是一种比较好的思维模式，不要总是把一些本应该单独的东西搅和在一块。

用一个接口来标记某一个类将实现与数据库的交互。
基于1，我们知道这个接口总得要一个数据库交互的一个对象，当然还有它对应的数据表名。

所以小明很快就把基础设施搭好了，但是他觉得这只是基础操作。

using System;
using System.Data;

public abstract class SqlBaseProvider
{
    protected SqlBaseProvider(IDbConnection conn)
    {
        Conn = conn;
    }

    protected IDbConnection Conn { get; set; }

    protected abstract void Open();
    public abstract DataTable Query(string command);
    protected abstract void Close();
    public abstract void Execute(string command);

    public bool Exists(string command)
    {
        var table = Query(command);
        if (table.Rows.Count == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    public bool TryQuery(string command, out DataTable table)
    {
        table = Query(command);
        if (table.Rows.Count == 0)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
}

/// <summary>
/// 表示一个Sql对象，用反射的方式和数据库交互。
/// </summary>
public interface ISqlObject
{
    /// <summary>
    /// 提供Sql的相关信息及操作。
    /// </summary>
    SqlBaseProvider SqlProvider { get; }
    /// <summary>
    /// 数据表。
    /// </summary>
    string Table { get; }
}

小明左思又想，怎么将属性和数据库字段进行绑定呢？小红说把所有可读写属性都拿去和数据库交互，但是小明觉得这样容易把程序搞炸，而且灵活性不够，于是小明掏出了一把宝剑：Attribute。

提示：Attribute可不像某些程序员说的”这不是Bug，而是Attribute(特性)”。实际上，Attribute表示某个东西的特性，通常情况下，他并不影响它的主体。它就像一个黏人的东西一样，总是粘在一个东西上。

小明想了十来分钟，最终总结出三个特性，分别表示交互的元素，查询的主键，更新数据库绑定方法。

这是小刀

public static class Extensions
{
    /// <summary>
    /// 将原可枚举对象通过函数映射到一个新列表。
    /// </summary>
    /// <typeparam name="TIn"></typeparam>
    /// <typeparam name="TResult"></typeparam>
    /// <param name="obj"></param>
    /// <param name="func"></param>
    /// <returns></returns>
    public static List<TResult> Map<TIn,TResult>(this IEnumerable<TIn> obj,Func<TIn,TResult> func)
    {
        List<TResult> result = new List<TResult>();
        foreach (var item in obj)
        {
            result.Add(func(item));
        }
        return result;
    }

}

这是另一把宝剑，小明为了用这把剑，着实花了不少力气。

/// <summary>
/// 表示其为一个<see cref="ISqlObject"/>的一个元素，实现和MySql的交互。
/// </summary>
[AttributeUsage(AttributeTargets.Property, Inherited = true, AllowMultiple = false)]
public sealed class SqlElementAttribute : Attribute
{
    public SqlElementAttribute(string name = null)
    {
        Name = name;
    }
    public string Name { get; }
}

/// <summary>
/// 表示将一个属性绑定到一个命名Update方法。
/// </summary>
[AttributeUsage(AttributeTargets.Property, Inherited = true, AllowMultiple = true)]
public sealed class SqlBindingAttribute : Attribute
{
    public SqlBindingAttribute(string funcName)
    {
        FuncName = funcName;
    }

    public string FuncName { get; }
}

/// <summary>
/// 表示该属性是查询的主键。
/// </summary>
[AttributeUsage(AttributeTargets.Property,Inherited =true,AllowMultiple =true)]
public sealed class SqlSearchKeyAttribute:Attribute
{
    public SqlSearchKeyAttribute()
    {
    }
}

你可能会纳闷，这个特性似乎什么都没干，但是别急，往下看就能看到特性的魅力。

接着小明有拿出了另一把宝剑：Reflection，这是两把剑真正的实力就被激发了，当然，他也拿出了一把小刀：扩展方法。

小明以迅雷不及掩耳之势，码出了下列代码。

public class NameValue
{
    public NameValue(string name, string value)
    {
        Name = name;
        Value = value;
    }

    public string Name { get; }
    public string Value { get; }
}
public class NameValueList:IList<NameValue>
{
    List<NameValue> vs = new List<NameValue>();

    public NameValue this[int index] { get => ((IList<NameValue>)vs)[index]; set => ((IList<NameValue>)vs)[index] = value; }
    public int Count => ((IList<NameValue>)vs).Count;
    public bool IsReadOnly => ((IList<NameValue>)vs).IsReadOnly;
    public void Add(NameValue item)
    {
        ((IList<NameValue>)vs).Add(item);
    }

    public void Clear()
    {
        ((IList<NameValue>)vs).Clear();
    }
    public bool Contains(NameValue item)
    {
        return ((IList<NameValue>)vs).Contains(item);
    }
    public void CopyTo(NameValue[] array, int arrayIndex)
    {
        ((IList<NameValue>)vs).CopyTo(array, arrayIndex);
    }
    public IEnumerator<NameValue> GetEnumerator()
    {
        return ((IList<NameValue>)vs).GetEnumerator();
    }
    public int IndexOf(NameValue item)
    {
        return ((IList<NameValue>)vs).IndexOf(item);
    }
    public void Insert(int index, NameValue item)
    {
        ((IList<NameValue>)vs).Insert(index, item);
    }
    public bool Remove(NameValue item)
    {
        return ((IList<NameValue>)vs).Remove(item);
    }
    public void RemoveAt(int index)
    {
        ((IList<NameValue>)vs).RemoveAt(index);
    }
    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IList<NameValue>)vs).GetEnumerator();
    }

    public void Add(string name, string value) => Add(new NameValue(name, value));
    public List<string> Names
    {
        get
        {
            List<string> ar = new List<string>();
            foreach (var item in vs)
            {
                ar.Add(item.Name);
            }
            return ar;
        }
    }
    public List<string> Values
    {
        get
        {
            List<string> ar = new List<string>();
            foreach (var item in vs)
            {
                ar.Add(item.Value);
            }
            return ar;
        }
    }
}

/// <summary>
/// 提供<see cref="ISqlObject"/>的扩展方法，包括添加记录，查询并更新。
/// </summary>
public static class SqlExtension
{
    /// <summary>
    /// 添加到数据库。
    /// </summary>
    /// <param name="obj"></param>
    public static void Add(this ISqlObject obj)
    {
        //获取成员列表。
        NameValueList vs = new NameValueList();
        foreach (var property in obj.GetType().GetProperties())
        {
            if (property.CanWrite && property.CanRead && property.TryGetSqlElementName(out string name))
            {
                vs.Add(name,TranSql(property.GetValue(obj)));
            }
        }
        //生成命令语句。
        string cmd = $"insert into {obj.Table} set ({string.Join(',',vs.Names)}) values ({string.Join(',',vs.Values)})" ;

        obj.SqlProvider.Execute(cmd);
    }
    /// <summary>
    /// 根据<see cref="SqlSearchKeyAttribute"/>将数据库相应行中<see cref="SqlBindingAttribute"/>绑定的属性更新。
    /// </summary>
    /// <param name="obj"></param>
    /// <param name="binding"></param>
    public static void Update(this ISqlObject obj, string binding)
    {
        //获取成员列表。
        NameValueList vs = new NameValueList();
        foreach (var property in obj.GetType().GetProperties())
        {
            if (property.CanWrite && property.CanRead && property.TryGetSqlElementName(out string name) && property.SqlBindingExists(binding))
            {
                vs.Add(name, TranSql(property.GetValue(obj)));
            }
        }
        //生成命令语句。
        string cmd = $"update {obj.Table} set " + 
            string.Join(',', vs.Map((m) => $"{m.Name}={m.Value}")) + " " +
            obj.GetWhereExpression();
        obj.SqlProvider.Execute(cmd);
    }
    /// <summary>
    /// 尝试查询，如果成功，就将<see cref="SqlElementAttribute"/>标记的属性更新为数据库中的值。
    /// </summary>
    /// <param name="obj"></param>
    /// <returns></returns>
    public static bool TryQuery(this ISqlObject obj)
    {
        string cmd = $"select * from {obj.Table} {obj.GetWhereExpression()}";

        var table = obj.SqlProvider.Query(cmd);

        if (table.Rows.Count == 0)
        {
            return false;
        }
        else
        {
            obj.SetValue(table.Rows[0]);
            return true;
        }
    }
    /// <summary>
    /// 根据键值对查询。
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="name"></param>
    /// <param name="value"></param>
    /// <param name="result"></param>
    /// <returns></returns>
    public static bool TryQuery<T>(string name,object value, out List<T> result) where T : ISqlObject,new()
    {
        T obj = new T();
        string cmd = $"select * from {obj.Table} {GetWhereExpression(name,value)}";
        var table = obj.SqlProvider.Query(cmd);
        if (table.Rows.Count == 0)
        {
            result = null;
            return false;
        }
        else
        {
            List<T> result1 = new List<T>();
            foreach (DataRow item in table.Rows)
            {
                T example = new T();
                example.SetValue(item);
                result1.Add(example);
            }

            result = result1;
            return true;
        }
    }
    public static bool Exists(this ISqlObject obj)
    {
        string cmd = $"select * from {obj.Table} {obj.GetWhereExpression()}";

        var table = obj.SqlProvider.Query(cmd);

        return table.Rows !=null && table.Rows.Count != 0;
    }

    private static string GetWhereExpression(this ISqlObject obj)
    {
        string name = null; object value = null;
        foreach (var property in obj.GetType().GetProperties())
        {
            if (property.CanWrite && property.CanRead && property.GetCustomAttribute<SqlSearchKeyAttribute>() != null && property.TryGetSqlElementName(out string name1))
            {
                name = name1;
                value = property.GetValue(obj);
            }
        }

        if (name !=null)
        {
            if (value is string str)
            {
                return $"where {name} like '{str}'";
            }
            else
            {
                return $"where {name}={value}";
            }
        }
        else
        {
            throw new KeyNotFoundException();

        }
    }
    private static string GetWhereExpression(string name,object value)
    {
        if (value is string str)
        {
            return $"where {name} like '{str}'";
        }
        else
        {
            return $"where {name}={value}";
        }
    }
    private static bool TryGetSqlElementName(this PropertyInfo obj, out string name)
    {
        var attribute = obj.GetCustomAttribute<SqlElementAttribute>();
        if (obj!=null)
        {
            if (attribute.Name == null)
            {
                name = obj.Name;
            }
            else
            {
                name = attribute.Name;
            }
            return true;
        }
        else
        {
            name = null;
            return false;
        }
    }
    private static bool SqlBindingExists(this PropertyInfo obj, string binding)
    {
        var attributes = obj.GetCustomAttributes<SqlBindingAttribute>();
        foreach (var attribute in attributes)
        {
            if (attribute.FuncName == binding)
            {
                return true;
            }
        }
        return false;
    }
    private static void SetValue(this ISqlObject obj,DataRow row)
    {
        foreach (var property in obj.GetType().GetProperties())
        {
            if (property.CanRead && property.CanWrite && property.TryGetSqlElementName(out string name))
            {
                property.SetValue(obj, row[name]);
            }
        }

    }
    public static void SetValue<T>(this T obj, T other) where T : ISqlObject, new()
    {
        foreach (var property in obj.GetType().GetProperties())
        {
            if (property.CanRead && property.CanWrite && property.TryGetSqlElementName(out string name))
            {
                property.SetValue(obj,property.GetValue(other));
            }
        }
    }

    private static string TranSql(object obj)
    {
        if (obj is string str)
        {
            return $"'{str}'";
        }
        else
        {
            return obj.ToString();
        }
        
    }
}

快结束了，大家如果不懂请重修精通C#6.0，当然这些代码尽管拿去，哦，差点把MySqlProvider给忘了。

要实现这段代码，需要导入.NuGet包MySql.Data。

/// <summary>
/// 提供<see cref="ISqlObject"/>依赖的MySql交互实现。
/// </summary>
public class MySqlProvider : SqlBaseProvider
{
    public MySqlProvider(IDbConnection conn) : base(conn)
    {
    }

    protected override void Open()
    {
        if (Conn.State == ConnectionState.Closed)
        {
            Conn.Open();
        }
    }
    protected override void Close() => Conn.Close();
    public override void Execute(string command)
    {
        try
        {
            Open();
            var cmd = new MySqlCommand(command, (MySqlConnection)Conn);
            cmd.ExecuteNonQuery();
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Close();
        }
    }
    public override DataTable Query(string command)
    {
        try
        {
            Open();

            DataSet dataSet = new DataSet();
            MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(command, (MySqlConnection)Conn);
            sqlDataAdapter.Fill(dataSet);

            return dataSet.Tables[0];
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Close();
        }
    }
}

当然，因为小明能力不够，无法详细解释Reflection这把宝剑威力怎么会这么大，只能很浅显地给个例子了。造了轮子别人不会用也是白讲。

例子

public class MyUser:ISqlObject
{
    [SqlElement]
    public int id { get; set; }
    [SqlElement][SqlSearchKey]
    public string username { get; set; }
    [SqlElement]
    public int usertype { get; set; }
    [SqlElement][SqlBinding("password")]
    public string password { get; set; }
    [SqlElement]
    public string credit { get; set; }

    SqlBaseProvider ISqlObject.SqlProvider { get; }= Config.MySqlProvider;

    string ISqlObject.Table => "user";
}

提示：Config. MyProvider请读者自己修改，这个应该挺简单地。

小明向我们展示了他造出的大轮子到底怎么转起来。

当你需要插入一条数据时，只需要这么一句。

user.Add(); //user是MyUser的一个实例。

类似的，有。

if(user.Exists())
{
    user.Update("password"); //请回头看[SqlBinding]，它将其绑定到这个带参方法。
}

MyUser user = new MyUser();
user.username = "test1";
if(user.TryQuery()) //根据[SqlSearchKey]来查找，若查找成功，user自动更新为数据库内的数据。
{
    //Do Something.
}

credit = "babababa";
if(SqlExtension.TryQuery(nameof(credit),credit,out var result)) //静态查询方法,out是所有匹配的结果。
{
    MyUser user2 = result[0];
    //Do Something
}

结语

如果你觉得有用，不要忘了在我的相关项目上Star一下，当然，这个轮子你可以拿去用。