ADO.Net读取数据转换为对应的数据类型

一、sqldatareader 转实体类 最简单

public static object DataReaderToEntity(IDataReader dr, object entity)
{
    try
    {
        PropertyInfo[] pilist = entity.GetType().GetProperties();
        foreach (PropertyInfo pi in pilist)
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                if (pi.Name.Equals(dr.GetName(i)))
                {
                    pi.SetValue(entity, Convert.ChangeType(dr[i], pi.PropertyType), null);
                }
            }
        }
        return entity;
    }
    catch
    {
        return null;
    }
}

用法:

Entity entity = (Entity)DataReaderToEntity(dr, new Entigy());



二、DataReader转泛型
 　　 /// <summary>
        /// DataReader转泛型
        /// </summary>
        /// <typeparam name="T">传入的实体类</typeparam>
        /// <param name="objReader">DataReader对象</param>
        /// <returns></returns>
        public static IList<T> ReaderToList<T>(this IDataReader objReader)
        {
            using (objReader)
            {
                List<T> list = new List<T>();

                //获取传入的数据类型
                Type modelType = typeof(T);

                //遍历DataReader对象
                while (objReader.Read())
                {
                    //使用与指定参数匹配最高的构造函数，来创建指定类型的实例
                    T model = Activator.CreateInstance<T>();
                    for (int i = 0; i < objReader.FieldCount; i++)
                    {
                        //判断字段值是否为空或不存在的值
                        if (!IsNullOrDBNull(objReader[i]))
                        {
                            //匹配字段名
                            PropertyInfo pi = modelType.GetProperty(objReader.GetName(i), BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                            if (pi != null)
                            {
                                //绑定实体对象中同名的字段  
                                pi.SetValue(model, CheckType(objReader[i], pi.PropertyType), null);
                            }
                        }
                    }
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 对可空类型进行判断转换(*要不然会报错)
        /// </summary>
        /// <param name="value">DataReader字段的值</param>
        /// <param name="conversionType">该字段的类型</param>
        /// <returns></returns>
        private static object CheckType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                    return null;
                System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }

        /// <summary>
        /// 判断指定对象是否是有效值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static bool IsNullOrDBNull(object obj)
        {
            return (obj == null || (obj is DBNull)) ? true : false;
        }


        /// <summary>
        /// DataReader转模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objReader"></param>
        /// <returns></returns>
        public static T ReaderToModel<T>(this IDataReader objReader)
        {

            using (objReader)
            {
                if (objReader.Read())
                {
                    Type modelType = typeof(T);
                    int count = objReader.FieldCount;
                    T model = Activator.CreateInstance<T>();
                    for (int i = 0; i < count; i++)
                    {
                        if (!IsNullOrDBNull(objReader[i]))
                        {
                            PropertyInfo pi = modelType.GetProperty(objReader.GetName(i), BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                            if (pi != null)
                            {
                                pi.SetValue(model, CheckType(objReader[i], pi.PropertyType), null);
                            }
                        }
                    }
                    return model;
                }
            }
            return default(T);
        }



三、SqlDataReader转实体类

sqldatareader 转实体类 方法1
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data;


/// <summary>
/// Summary description for ConvertEntity1
/// </summary>
public class ConvertEntity1
{
    public ConvertEntity1()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public static T ReaderToModel<T>(IDataReader dr)
    {
        try
        {
            using (dr)
            {
                if (dr.Read())
                {
                    Type modelType = typeof(T);
                    T model = Activator.CreateInstance<T>();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {//, BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase
                        PropertyInfo pi = modelType.GetProperty(GetPropertyName(dr.GetName(i)));
                        if (pi != null)
                        {
                            pi.SetValue(model, HackType(dr[i], pi.PropertyType), null);
                        }
                    }
                    return model;
                }
            }
            return default(T);
        }
        catch (Exception ex)
        {
            return default(T);
        }
    }

    public static List<T> ReaderToList<T>(IDataReader dr)
    {
        using (dr)
        {
            List<T> list = new List<T>();
            Type modelType = typeof(T);
            T model = Activator.CreateInstance<T>();
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    PropertyInfo pi = modelType.GetProperty(GetPropertyName(dr.GetName(i)), BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                    if (pi != null)
                    {
                        pi.SetValue(model, HackType(dr[i], pi.PropertyType), null);
                    }
                }
                list.Add(model);
            }
            return list;
        }
    }
    //这个类对可空类型进行判断转换，要不然会报错  
    private static object HackType(object value, Type conversionType)
    {
        if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
        {
            if (value == null)
                return null;

            System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
            conversionType = nullableConverter.UnderlyingType;
        }
        return Convert.ChangeType(value, conversionType);

    }

 

    private static bool IsNullOrDBNull(object obj)
    {

        return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;

    }

 

    //取得DB的列对应bean的属性名  

    private static string GetPropertyName(string column)
    {

        column = column.ToLower();

        string[] narr = column.Split('_');

        column = "";

        for (int i = 0; i < narr.Length; i++)
        {

            if (narr[i].Length > 1)
            {

                column += narr[i].Substring(0, 1).ToUpper() + narr[i].Substring(1);

            }

            else
            {

                column += narr[i].Substring(0, 1).ToUpper();

            }

        }

        return column;

    }


}


四、如何让SqlDataReader 映射到实体类


public BossClub GetBossClub(SqlParameter[] parameters)
{
BossClub bc = null;
SqlDataReader sdr = DbHelperSQL.RunProcedure("UP_BossClub_GetModel", parameters);
if (sdr.HasRows)
{
sdr.Read();
bc = new BossClub();
bc.BID= sdr.GetInt32(0);
bc.Title = sdr.GetString(1);
bc.Body = sdr.GetString(2);
bc.CreateDate = sdr.GetDateTime(3);
}
return bc;

DbHelperSQL.RunProcedure（）返回的是SqlDataReader 如何才能让它映射到实体类BossClub
而不去写if (sdr.HasRows)
{
sdr.Read();
bc = new BossClub();
bc.BID= sdr.GetInt32(0);
bc.Title = sdr.GetString(1);
bc.Body = sdr.GetString(2);
bc.CreateDate = sdr.GetDateTime(3);
}
一个个赋值！！！！


五、一个个赋值
        var sql = "SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = @Id";
        var conn = new SqlConnection(ConnectionString);
        var stopWatch = new Stopwatch();

        try
        {
            conn.Open();
            var sqlCmd = new SqlCommand(sql, conn);

            for (var i = 0; i < keys.GetLength(0); i++)
            {
                for (var r = 0; r < keys.GetLength(1); r++)
                {
                    stopWatch.Restart();
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@Id", keys[i, r]);
                    var reader = await sqlCmd.ExecuteReaderAsync();
                    SalesOrderHeaderSQLserver salesOrderHeader = null;

                    while (await reader.ReadAsync())
                    {
                        salesOrderHeader = new SalesOrderHeaderSQLserver();
                        salesOrderHeader.SalesOrderId = (int)reader["SalesOrderId"];
                        salesOrderHeader.SalesOrderNumber = reader["SalesOrderNumber"] as string;
                        salesOrderHeader.AccountNumber = reader["AccountNumber"] as string;
                        salesOrderHeader.BillToAddressID = (int)reader["BillToAddressID"];
                        salesOrderHeader.TotalDue = (decimal)reader["TotalDue"];
                        salesOrderHeader.Comment = reader["Comment"] as string;
                        salesOrderHeader.DueDate = (DateTime)reader["DueDate"];
                        salesOrderHeader.CurrencyRateID = reader["CurrencyRateID"] as int?;
                        salesOrderHeader.CustomerID = (int)reader["CustomerID"];
                        salesOrderHeader.SalesPersonID = reader["SalesPersonID"] as int?;
                        salesOrderHeader.CreditCardApprovalCode = reader["CreditCardApprovalCode"] as string;
                        salesOrderHeader.ShipDate = reader["ShipDate"] as DateTime?;
                        salesOrderHeader.Freight = (decimal)reader["Freight"];
                        salesOrderHeader.ModifiedDate = (DateTime)reader["ModifiedDate"];
                        salesOrderHeader.OrderDate = (DateTime)reader["OrderDate"];
                        salesOrderHeader.TerritoryID = reader["TerritoryID"] as int?;
                        salesOrderHeader.CreditCardID = reader["CreditCardID"] as int?;
                        salesOrderHeader.OnlineOrderFlag = (bool)reader["OnlineOrderFlag"];
                        salesOrderHeader.PurchaseOrderNumber = reader["PurchaseOrderNumber"] as string;
                        salesOrderHeader.RevisionNumber = (byte)reader["RevisionNumber"];
                        salesOrderHeader.Rowguid = (Guid)reader["Rowguid"];
                        salesOrderHeader.ShipMethodID = (int)reader["ShipMethodID"];
                        salesOrderHeader.ShipToAddressID = (int)reader["ShipToAddressID"];
                        salesOrderHeader.Status = (byte)reader["Status"];
                        salesOrderHeader.SubTotal = (decimal)reader["SubTotal"];
                        salesOrderHeader.TaxAmt = (decimal)reader["TaxAmt"];
                    }

                    stopWatch.Stop();
                    reader.Close();
                    await PrintTestFindByPKReport(stopWatch.ElapsedMilliseconds, salesOrderHeader.SalesOrderId.ToString());
                }