本帖最后由 songwenqi 于 2014-11-27 13:15 编辑
【分布式系统框架教程】-查询两个表之间链接表的数据
以往的两表之间的查询一般采用sql的内连接等sql语句来查询，这种查询方式不不便于分布和扩展的，而分布式框架的精髓就体现在这，不用内连查询，可分布，可扩展，而且读取方便、快捷。下面是两个表对应的model：
UserInfo类
[C#] 纯文本查看 复制代码 namespace DistributedModel.User
{
/// <summary>
/// 用户基类
/// </summary>
public class UserInfo
{
/// <summary>
/// 用户ID
/// </summary>
public int ID { get; set; }
/// <summary>
/// 用户姓名
/// </summary>
public string Name { get; set; }
/// <summary>
/// 用户性别
/// </summary>
public int Sex { get; set; }
/// <summary>
/// 获取用户性别汉字
/// </summary>
/// <returns>汉字男或者女</returns>
public string GetSex()
{
return Gender.GetItem(Sex).Name;
}
/// <summary>
/// 用户手机号
/// </summary>
public string Phone { get; set; }
/// <summary>
/// 所在省
/// </summary>
public int ProvinceId { get; set; }
/// <summary>
/// 获取省汉字
/// </summary>
/// <returns></returns>
public string GetProvince()
{
return Area.GetProvince(ProvinceId).Name;
}
/// <summary>
/// 所在市
/// </summary>
public int CityId { get; set; }
/// <summary>
/// 获取城市汉字
/// </summary>
/// <returns></returns>
public string GetCity()
{
return Area.GetCity(CityId).Name;
}
/// <summary>
/// 12星座ID
/// </summary>
public int ConstellationId { get; set; }
/// <summary>
/// 获取星座汉字
/// </summary>
/// <returns></returns>
public string GetConstellation()
{
return Constellation.GetItem(ConstellationId).Name;
}
/// <summary>
/// 学历ID
/// </summary>
public int DegreeId { get; set; }
/// <summary>
/// 获取学历汉字
/// </summary>
/// <returns></returns>
public string GetDegree()
{
return Degree.GetItem(DegreeId).Name;
}
/// <summary>
/// 民族ID
/// </summary>
public int NationId { get; set; }
/// <summary>
/// 获取民族汉字
/// </summary>
/// <returns></returns>
public string GetNation()
{
return Nation.GetItem(NationId).Name;
}
/// <summary>
/// 生肖ID
/// </summary>
public int ZodiacId { get; set; }
/// <summary>
/// 获取生肖汉字
/// </summary>
/// <returns></returns>
public string GetZodiac()
{
return Zodiac.GetItem(ZodiacId).Name;
}
/// <summary>
/// 添加日期
/// </summary>
public DateTime AddTime { get; set; }
}
}
LoginUserInfo类，查询时主要查询这个表，然后关联user表，所以此时LoginUserInfo类需要继承一个基类ModelBase
[C#] 纯文本查看 复制代码 namespace DistributedModel.User
{
/// <summary>
/// 登录用户
/// </summary>
public class LoginUserInfo : ModelBase
{
/// <summary>
/// 用户ID
/// </summary>
public int ID { get; set; }
/// <summary>
/// 登录用户名
/// </summary>
public string UserName { get; set; }
/// <summary>
/// 登录密码
/// </summary>
public string UserPwd { get; set; }
/// <summary>
/// 用户状态
/// </summary>
public UserStatus UserStatus { get; set; }
/// <summary>
/// 注册IP
/// </summary>
public string LoginIp { get; set; }
/// <summary>
/// 登录类型
/// </summary>
public LoginType LoginType { get; set; }
}
}
好了现在两个表对应的model已经贴完，下面是dal，首先需要配置数据库连接字符串
[C#] 纯文本查看 复制代码 namespace DistributedDAL
{
/// <summary>
/// 所有表的连接字符串都写在这里，方法统一修改
/// </summary>
public static class DbConfig
{
/// <summary>
/// 默认的连接字符串
/// </summary>
public readonly static string DefaultConnection ="DefaultConnection";
/// <summary>
/// User表的连接字符串
/// </summary>
public static readonly string UserConnection ="UserConnection";
/// <summary>
/// LoginUser表的连接字符串
/// </summary>
public static readonly string LoginUserConnection = "LoginUserConnection";
}
}
UserDAL类
[C#] 纯文本查看 复制代码 public class UserDAL:BaseDAL<UserInfo>
{
/// <summary>
/// 数据库连接字符串名称
/// </summary>
protected override string ConnName
{
get { return DbConfig.UserConnection; }
}
/// <summary>
/// 组织对象
/// </summary>
/// <param name="reader">Reader对象</param>
/// <param name="fields">字段集合</param>
/// <returns></returns>
protected override UserInfo FillModelFromReader(DbDataReader reader, params string[] fields)
{
return ModelFromReader(reader, fields);
}
/// <summary>
/// 组织对象
/// </summary>
/// <param name="reader">Reader对象</param>
/// <param name="fields">字段集合</param>
/// <returns></returns>
protected UserInfo ModelFromReader(DbDataReader reader, params string[] fields)
{
var info = new UserInfo();
if (UtilDAL.HasFields("ID", fields)) info.ID = (int)reader["ID"];
if (UtilDAL.HasFields("ZodiacId", fields)) info.ZodiacId = (int)reader["ZodiacId"];
if (UtilDAL.HasFields("Name", fields)) info.Name = reader["Name"].ToString();
if (UtilDAL.HasFields("Sex", fields)) info.Sex = (int)reader["Sex"];
if (UtilDAL.HasFields("Phone", fields)) info.Phone = reader["Phone"].ToString();
if (UtilDAL.HasFields("ProvinceId", fields)) info.ProvinceId = (int)reader["ProvinceId"];
if (UtilDAL.HasFields("CityId", fields)) info.CityId = (int)reader["CityId"];
if (UtilDAL.HasFields("ConstellationId", fields)) info.ConstellationId = (int)reader["ConstellationId"];
if (UtilDAL.HasFields("DegreeId", fields)) info.DegreeId = (int)reader["DegreeId"];
if (UtilDAL.HasFields("NationId", fields)) info.NationId = (int)reader["NationId"];
if (UtilDAL.HasFields("AddTime", fields)) info.AddTime = (DateTime)reader["AddTime"];
return info;
}
/// <summary>
/// 查询
/// </summary>
/// <param name="userIdlist">Id列表</param>
/// <returns></returns>
public List<UserInfo> FindList(List<int> userIdlist)
{
var sql = string.Format("SELECT * FROM [User] WHERE ID in({0})", string.Join(",", userIdlist));
return FindList(sql);
}
}
LoginUserDAL类
[C#] 纯文本查看 复制代码 public class LoginUserDAL : BaseDAL<LoginUserInfo>
{
/// <summary>
/// 数据库连接字符串名称
/// </summary>
protected override string ConnName
{
get { return DbConfig.LoginUserConnection; }
}
/// <summary>
/// 组织对象
/// </summary>
/// <param name="reader">Reader对象</param>
/// <param name="fields">字段集合</param>
/// <returns></returns>
protected override LoginUserInfo FillModelFromReader(DbDataReader reader, params string[] fields)
{
return ModelFromReader(reader, fields);
}
/// <summary>
/// 组织对象
/// </summary>
/// <param name="reader">Reader对象</param>
/// <param name="fields">字段集合</param>
/// <returns></returns>
protected LoginUserInfo ModelFromReader(DbDataReader reader, params string[] fields)
{
var info = new LoginUserInfo();
if (UtilDAL.HasFields("ID", fields)) info.ID = (int)reader["ID"];
if (UtilDAL.HasFields("UserName", fields)) info.UserName = reader["UserName"].ToString();
if (UtilDAL.HasFields("UserPwd", fields)) info.UserPwd = reader["UserPwd"].ToString();
if (UtilDAL.HasFields("UserStatus", fields)) info.UserStatus = (UserStatus)(int)reader["UserStatus"];
if (UtilDAL.HasFields("LoginIp", fields)) info.LoginIp = reader["LoginIp"].ToString();
if (UtilDAL.HasFields("LoginType", fields)) info.LoginType = (LoginType)(int)reader["LoginType"];
return info;
}
public List<LoginUserInfo> FindListPage(string strWhere, string field, int pageIndex, int pageSize, out int totalCount)
{
return FindPage("LoginUser", field, strWhere, "ID desc", pageIndex, pageSize, out totalCount);
}
}
好了 下面就是BLL层了，关键就在这里，
先贴UserBLL类，很简单就是实现了一个通过userIdlist来查找对象的方法。
[C#] 纯文本查看 复制代码 public class UserBLL
{
UserDAL dal=new UserDAL();
/// <summary>
/// 获取列表 根据userIdlist
/// </summary>
/// <param name="userIdlist"></param>
/// <returns></returns>
public List<UserInfo> FindList(List<int> userIdlist)
{
return dal.FindList(userIdlist);
}
}
好了下面重要的部分来了：LoginUserBLL类
[C#] 纯文本查看 复制代码 public class LoginUserBLL
{
LoginUserDAL dal = new LoginUserDAL();
public List<LoginUserInfo> FindListPage(string strWhere, int pageIndex, int pageSize, out int totalCount)
{
List<LoginUserInfo> ulist = dal.FindListPage(strWhere, "", pageIndex, pageSize, out totalCount);
if (ulist.Count <= 0)
{
return null;
}
//关联LoginUserInfo 实现两表之间的关系查询
var userIdlist = ulist.Select(u => u.ID);
//从这里解决链表和子查询
UserBLL userbll = new UserBLL();
//根据主表关联的ID去数据库查询子表数据，因为是ID，所以速度最快
List<UserInfo> userList = userbll.FindList(userIdlist.ToList());
//使用Foreach将数据的关键链接起来
foreach (LoginUserInfo loginuser in ulist)
{
//这是就是数据关联的条件，满足这个条件的就说明是我们链接或者是要子查询的数据
if (userList.Any(u => u.ID == loginuser.ID))
{
UserInfo loginuserinfo = userList.FirstOrDefault(u => u.ID == loginuser.ID);
//将得到的子表数据直接添加的主表。完美解决子查询和链表问题
loginuser.AddExData("UserInfo", loginuserinfo);
}
}
return ulist;
}
}
前端代码：当然不要忘记配置web.config的数据库连接字符串。
[C#] 纯文本查看 复制代码 <connectionStrings>
<!--默认的连接字符串-->
<add name="DefaultConnection" connectionString="server=192.168.18.100;uid=sa;password=1234;database=TestDB;" providerName="System.Data.SqlClient"/>
<!--User表的连接字符串-->
<add name="UserConnection" connectionString="server=192.168.18.100;uid=sa;password=1234;database=TestDB;" providerName="System.Data.SqlClient"/>
<!--LoginUser表的连接字符串-->
<add name="LoginUserConnection" connectionString="server=192.168.18.100;uid=sa;password=1234;database=TestDB;" providerName="System.Data.SqlClient"/>
</connectionStrings>
[C#] 纯文本查看 复制代码 LoginUserBLL loginbll = new LoginUserBLL();
protected void Page_Load(object sender, EventArgs e)
{
public List<LoginUserInfo> ulist = loginbll.FindListPage(strWhere, pgindex, pageSize, out totalcounts);
}
如此就完美的解决了链表查询的问题。下一篇我们接着讲分页的具体步骤。
效果：
| 
窥视卡
雷达卡
发表于 2014-11-27 12:58:10
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡