苏飞论坛

 找回密码
 马上注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

分布式系统框架(V2.0) 轻松承载百亿数据,千万流量!讨论专区 - 源码下载 - 官方教程

HttpHelper万能框架(V2.3-含.netcore) HttpHelper官方出品,无敌框架讨论区 - 源码下载 - 在线测试和代码生成

HttpHelper爬虫类(V2.0) 最牛的爬虫类,没有爬不到只有想不到 源码 - 代码生成器 - 讨论区 - 教程- 例子

查看: 7435|回复: 5

[教程] 【分布式系统框架教程】-查询两个表之间链接表的数据

[复制链接]
发表于 2014-11-27 12:58:10 | 显示全部楼层 |阅读模式
本帖最后由 songwenqi 于 2014-11-27 13:15 编辑

【分布式系统框架教程】-查询两个表之间链接表的数据

导读部分



1.【分布式系统框架】教程目录帖http://www.sufeinet.com/thread-10624-1-1.html

教程部分


以往的两表之间的查询一般采用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);
}

如此就完美的解决了链表查询的问题。下一篇我们接着讲分页的具体步骤。
效果:
QQ截图20141127125756.png
发表于 2014-11-27 13:49:56 | 显示全部楼层
回复

使用道具 举报

发表于 2015-7-10 17:07:51 | 显示全部楼层
发表于 2015-9-22 14:00:56 | 显示全部楼层
发表于 2015-12-13 21:57:41 | 显示全部楼层
原来是用extdata解决链接的问题,关键还是链接多表是难道要用extdata的集合吗?
发表于 2016-2-2 15:00:46 | 显示全部楼层
您需要登录后才可以回帖 登录 | 马上注册

本版积分规则

QQ|手机版|小黑屋|手机版|联系我们|关于我们|广告合作|苏飞论坛 ( 豫ICP备17001017号-1)

GMT+8, 2019-12-7 08:07

© 2017-2018

快速回复 返回顶部 返回列表