泛型学习第三天——C#读取数据库返回泛型集合 把DataSet类型转换为List泛型集合

时间:2021-09-21 00:07:43

定义一个类:

public class UserInfo
    {
        public System.Guid ID { get; set; }

public string LoginName { get; set; }

public string LoginPwd { get; set; }
    }

/// <summary>
/// 获取UserInfo泛型集合
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="sqlStr">要查询的T-SQL</param>
/// <returns></returns>
public IList<UserInfo> GetUserInfoAll(string connStr, string sqlStr)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
{
SqlDataReader sdr = cmd.ExecuteReader();

IList<UserInfo> list = new List<UserInfo>();

while (sdr.Read())
{

UserInfo userInfo = new UserInfo();

userInfo.ID = (Guid)sdr["ID"];

userInfo.LoginName = sdr["LoginName"].ToString();

userInfo.LoginPwd = sdr["LoginPwd"].ToString();

list.Add(userInfo);

}
return list;
}
}
}

/// <summary>
/// 获取泛型集合
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="sqlStr">要查询的T-SQL</param>
/// <returns></returns>
public IList<T> GetList<T>(string connStr, string sqlStr)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter sda = new SqlDataAdapter(sqlStr, conn))
{
DataSet ds = new DataSet();
sda.Fill(ds);
return DataSetToList<T>(ds, 0);
}
}
}

/// <summary>
/// DataSetToList
/// </summary>
/// <typeparam name="T">转换类型</typeparam>
/// <param name="dataSet">数据源</param>
/// <param name="tableIndex">需要转换表的索引</param>
/// <returns></returns>
public IList<T> DataSetToList<T>(DataSet dataSet, int tableIndex)
{
//确认参数有效
if (dataSet == null || dataSet.Tables.Count <= 0 || tableIndex < 0)
return null;

DataTable dt = dataSet.Tables[tableIndex];

IList<T> list = new List<T>();

for (int i = 0; i < dt.Rows.Count; i++)
{
//创建泛型对象
T _t = Activator.CreateInstance<T>();
//获取对象所有属性
PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
foreach (PropertyInfo info in propertyInfo)
{
//属性名称和列名相同时赋值
if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
{
if (dt.Rows[i][j] != DBNull.Value)
{
info.SetValue(_t, dt.Rows[i][j], null);
}
else
{
info.SetValue(_t, null, null);
}
break;
}
}
}
list.Add(_t);
}
return list;
}