1. 使用ADO.NET查询数据库数据,使用SqlDataReader逐行读取,返回泛型集合。
此方法将所需的数据返回了泛型集合,但是此种方式不具有通用性,当数据库有多张表需要查询时,需要做大量的重复工作,效率太低。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace test3._0
{
class Userinfo
{
public string Name { get; set; }
public string Pwd { get; set; }
}
class SQLHelp
{
public static SqlDataReader ExecuteReader(string cmdStr)
{
string conStr = "Data Source=VICTORY;Initial Catalog = test; Persist Security Info = True; User ID = sa; Password = 123456";
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(cmdStr, con);
con.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
class ToList
{
public IList<Userinfo> GetUserinfos(string cmd)
{
SqlDataReader da = SQLHelp.ExecuteReader(cmd);
IList<Userinfo> list = new List<Userinfo>();
while(da.Read())
{
Userinfo userinfo = new Userinfo();
userinfo.Name = da["name"].ToString();
userinfo.Pwd = da["Pwd"].ToString();
list.Add(userinfo);
}
return list;
}
}
class Program
{
static void Main(string[] args)
{
string cmd = "select * from users3_0";
ToList a = new ToList();
IList<Userinfo> b = a.GetUserinfos(cmd);
foreach(Userinfo e in b)
{
Console.WriteLine(e.Name);
Console.WriteLine(e.Pwd);
Console.WriteLine();
}
}
}
}
2. 利用DataSet、SqlDataAdapter与构造的通用DataSetToList
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
namespace Test
{
public class Userinfo
{
public string Name { get; set; }
public string Pwd { get; set; }
}
public class DataSettoList
{
public IList<T> GetList<T>(string cmd)
{
string conStr = "Data Source=VICTORY;Initial Catalog = test; Persist Security Info = True; User ID = sa; Password = 123456";
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd, con);
DataSet ds = new DataSet();
da.Fill(ds);
return DataSetToList<T>(ds, 0);
}
public List<T> DataSetToList<T>(DataSet ds, int tableIndex)
{
//确认具有参数
if (ds == null || ds.Tables.Count <= 0 || tableIndex < 0)
{
return null;
}
DataTable dt = ds.Tables[tableIndex];//取得DataSet中下标为tableIndex的表
IList<T> list = new List<T>();
PropertyInfo[] Memberall = typeof(T).GetProperties();//获取T类型中的Attribute
for (int i = 0; i < dt.Rows.Count; i++)
{
T t = Activator.CreateInstance<T>();//使用无参数构造函数,创建指定泛型类型参数所指定类型的实例
PropertyInfo[] tMemberall = t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
foreach (PropertyInfo tMember in tMemberall)
{
if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper())) //dt表中j列和属性名称相同时赋值
{
if (dt.Rows[i][j] != DBNull.Value)
{
tMember.SetValue(t, dt.Rows[i][j], null);//设置指定对象的属性值,即给t成员赋值
}
else
{
tMember.SetValue(t, null, null);
}
break;
}
}
}
list.Add(t);
}
return list.ToList();
}
}
class Program
{
static void Main(string[] args)
{
string cmdtext = "select * from users3_0";
DataSettoList a = new DataSettoList();
IList<Userinfo> lis = a.GetList<Userinfo>(cmdtext);
foreach (var i in lis)
{
Console.WriteLine(i.Name);
Console.WriteLine(i.Pwd);
Console.WriteLine();
}
}
}
}
附:数据库数据