ASP.NET通过反射生成sql语句

最近对接一个接口,需要通过xml序列化成实体后添加额外信息后批量插入数据库,需要手动拼sql。因为涉及多张表,拼凑很麻烦而且容易出错,所以写了两个工具方法来生成sql,先写到博客里面,以便以后不时之需。

直接上工具方法,是通过反射机制来拼凑的sql语句,EF底层实现原理差不多。

1.实体生成Insert语句

 /// <summary>
        /// 通过反射生成sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="tableName">表名称</param>
        /// <returns></returns>
        public static string InsertSql<T>(T entity,string tableName="")where T:class
        {
            return entity == null || "".Equals(entity) ? string.Empty : $" INSERT INTO {tableName??entity.GetType().Name} ({string.Join(",", entity.GetType().GetProperties().Select(t => $"[{t.Name}]"))}) Values({string.Join(",",entity.GetType().GetProperties().Select(t=>$"'{t.GetValue(entity)}'"))})";
        }

 

2.实体生成Update语句,主键字段需要自己指定

   /// <summary>
        /// 通过反射生成sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">实体</param>
        /// <param name="value"></param>
        /// <param name="key">条件字段</param>
        /// <param name="tableName">表名称</param>

        public static string UpdateSql<T>(T entity, object value,string key, string tableName = "") where T:class
        {
            return entity==null || "".Equals(entity) || value==null || "".Equals(value) ? string.Empty: $" UPDATE {tableName??entity.GetType().Name} SET {string.Join(",", entity.GetType().GetProperties().Select(t => $"[{t.Name}]='{t.GetValue(entity)}'"))} WHERE {key}='{value}'";
        }

 

随便造个实体:

 public class UserEntity
    {
        /// <summary>
        /// 主键
        /// </summary>
        public string Id { get; set; }
        public string Name { get; set; }
        public string Adress { get; set; }
        public int Age { get; set; }
        public string Sex { get; set; }
        public string Phone { get; set; }
        public bool? Enabled { get; set; }
        public DateTime? CreateTime { get; set; }
    }

测试代码如下:

UserEntity user = new UserEntity()
{
    Name = "张三",
    Adress = "中国",
    Age = 22,
    Sex = "",
    Phone = "12345678910",
    Id = "552555",
    Enabled = true,
    CreateTime = DateTime.Now
};
Console.WriteLine("***********************实体生成Update语句****************************");
Console.WriteLine(UpdateSql(user, 1,"Id", "TestUser"));
Console.WriteLine("***********************实体生成Insert语句****************************");
Console.WriteLine(InsertSql(user, "TestUser"));
Console.ReadKey();

执行结果:

 

 如有不到之处,敬请指出。

posted @ 2019-07-15 09:57  *小嘻嘻*  阅读(652)  评论(0编辑  收藏  举报