.NET实现类似Dapper的查询扩展（MSSQL）

最新推荐文章于 2024-04-03 07:45:57 发布

落英芳华

最新推荐文章于 2024-04-03 07:45:57 发布

阅读量162

点赞数

分类专栏： C#.NET 文章标签： .net sqlserver 数据库

本文链接：https://blog.csdn.net/weixin_44792145/article/details/129047285

版权

C#.NET 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

.NET 利用反射来实现SQL Server 查询扩展

简单的造个轮子

简单的造个轮子

获取数据库中的字段名，实例化泛型T，利用反射中的GetProperty方法获取泛型T中的成员（Property）并使用SetValue方法设置值

public static class SqlExtension
{
     public static IEnumerable<T> QueryForList<T>(this SqlConnection conn, string sql, params SqlParameter[] parameters) where T : class
     {
         Type entity = typeof(T);
         if (conn.State != ConnectionState.Open)
         {
             conn.Open();
         }

         SqlCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         cmd.Parameters.AddRange(parameters);

         object instance = Activator.CreateInstance(entity);

         using (IDataReader reader = cmd.ExecuteReader())
         {
             while (reader.Read())
             {
                 for (int i = 0; i < reader.FieldCount; i++)
                 {
                     string name = reader.GetName(i);
                     PropertyInfo propertyInfo = entity.GetProperty(name, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                     if (propertyInfo != null)
                     {
                         if (reader.GetValue(i) == DBNull.Value)
                         {
                             if (propertyInfo.PropertyType == typeof(string) || propertyInfo.PropertyType == typeof(int?))
                                 propertyInfo.SetValue(instance, null);
                             else if (propertyInfo.PropertyType == typeof(int) || propertyInfo.PropertyType == typeof(decimal))
                                 propertyInfo.SetValue(instance, 0);
                         }
                         else
                             propertyInfo.SetValue(instance, reader.GetValue(i));
                     }
                 }
                 
                 yield return instance as T;
             }
         }

     }

     public static T QueryFirstOrDefault<T>(this SqlConnection conn,string sql, params SqlParameter[] parameters) where T : class
     {
         Type entity = typeof(T);
         if (conn.State != ConnectionState.Open)
         {
             conn.Open();
         }

         SqlCommand cmd = conn.CreateCommand();
         cmd.CommandText = sql;
         cmd.Parameters.AddRange(parameters);

         object instance = Activator.CreateInstance(entity);

         using (IDataReader reader = cmd.ExecuteReader())
         {
             if (reader.Read())
             {
                 for (int i = 0; i < reader.FieldCount; i++)
                 {
                     string name = reader.GetName(i);
                     PropertyInfo propertyInfo = entity.GetProperty(name, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                     if (propertyInfo != null)
                     {
                         if (reader.GetValue(i) == DBNull.Value)
                         {
                             if (propertyInfo.PropertyType == typeof(string) || propertyInfo.PropertyType == typeof(int?))
                                 propertyInfo.SetValue(instance, null);
                             else if (propertyInfo.PropertyType == typeof(int) || propertyInfo.PropertyType == typeof(decimal))
                                 propertyInfo.SetValue(instance, 0);
                         }
                         else
                             propertyInfo.SetValue(instance, reader.GetValue(i));
                     }
                 }
                 return instance as T;
             }

         }
         
         return null;
     }
 
 
 }