C#,特性(Attribute)创建Sql语句，处理Model字段与数据库列不同

2020-02-06 更新 修复参数值为空异常问题

namespace 根据Model生成Sql
{//转载请保留 http://www.luofenming.com/show.aspx?id=ART2018040700001
   public class TestModel
    {
        //KeyAttribute 这个Key
        //如果 TestAttribute则特性写[Test(IsIncrement = true)]
        [Key(IsIncrement = true)]
        public int ID { get; set; }
        [Column("Name")]
        public string NikeName { get; set; }
        public string QQ { get; set; }
        public string Email { get; set; }      
    }
}

namespace 根据Model生成Sql
{
    public class ColumnAttribute : Attribute
    {
        private string _name=string.Empty;
        public ColumnAttribute(string name)
        {
            this._name = name;
        }
        public string GetName()
        {
            return _name;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace 根据Model生成Sql
{
    public class TestDal
    {
        //特性创建Sql语句，处理Model字段与数据库列不同
        public void CreateSql3<T>(T model)
        {
            Type type = typeof(T);
            PropertyInfo[] propertyArray = type.GetProperties().ToArray();

            string[] strSqlNames = propertyArray.Select(p => $"[{GetName(p)}]").ToArray();
            string strSqlName = string.Join(",", strSqlNames);
            string[] strSqlValues = propertyArray.Select(p => $"@{GetName(p)}").ToArray();
            string strSqlValue = string.Join(",", strSqlValues);
            //strSql是创建的Sql语句
            string strSql = "insert into testModel ( " + strSqlName + " ) values (" + strSqlValue + ")";
            //para Sql是参数
            SqlParameter[] para = propertyArray.Select(p => new SqlParameter($"@{p.Name}", p.GetValue(model, null)??DBNull.Value)).ToArray();
        }
        private string GetName(PropertyInfo name)
        {
            if (name.IsDefined(typeof(ColumnAttribute), true))
            {
                ColumnAttribute attribute = (ColumnAttribute)name.GetCustomAttributes(typeof(ColumnAttribute), true)[0];
                return attribute.GetName();
            }
            return name.Name;
        }
    }
}

      //以下是方法调用 
       private void button1_Click(object sender, EventArgs e)
        {
            TestDal dal = new TestDal();
            TestModel model = new TestModel()
            {
                ID = 10001,
                NikeName = "罗分明",
                QQ = "78630559",
                Email = "78630559@qq.com"
            };
            dal.CreateSql3(model);
        }