SQLSERVER结合两个数据库的查询

 public static DataTable Admin_usersPaging(int StartRowIndex,int EndRowIndex,params string[] condition)
    {
        string str = "";
        if (condition != null && condition.Length > 0)
        {

            //str = "select * from (select u.userId,u.loginname,tu.teamId,t.teamName,dt.DeptId,d.deptName,ROW_NUMBER() over(order by u.userId)as rowNum  from Users u "
            //     + "left join TeamUser tu on u.userId=tu.userId and tu.isDefault=1 "
            //      + "left join Team t on tu.teamId=t.teamId "
            //      + "left join DeptTeam dt on tu.teamId=dt.TeamId and dt.DeptTeamId in (select min(DeptTeamId) from DeptTeam group by teamId) "
            //      + "left join Dept d on dt.DeptId=d.DeptId "
            //      + "where enabled=1 and isExternal=1 and " + condition[0] + " ) us where rowNum between " + StartRowIndex + " and " + EndRowIndex + " ";

            str = "select * from (select u.userId,aspu.UserId as aspUserId,ur.RoleId,r.RoleName,m.Email,m.Password,isnull(m.IsLockedOut,0) as IsLockedOut,ROW_NUMBER() over(order by u.userId)as rowNum  from EmiteWF.dbo.Users u "
                  + "left join aspnet_Users aspu on u.loginname=aspu.UserName "
                  + "left join aspnet_UsersInRoles ur on ur.UserId=aspu.UserId "
                  + "left join aspnet_Roles r on r.RoleId=ur.RoleId "
                  + "left join aspnet_Membership m on m.UserId=aspu.UserId "
                  + "left join EmiteWF.dbo.TeamUser tu on u.userId=tu.userId and tu.isDefault=1 "
                  + "left join EmiteWF.dbo.Team t on tu.teamId=t.teamId "
                  + "left join EmiteWF.dbo.DeptTeam dt on tu.teamId=dt.TeamId and dt.DeptTeamId in (select min(DeptTeamId) from EmiteWF.dbo.DeptTeam group by teamId) "
                  + "left join EmiteWF.dbo.Dept d on dt.DeptId=d.DeptId "
                  + "where enabled=1 and isExternal=1 and " + condition[0] + " ) us where rowNum between " + StartRowIndex + " and " + EndRowIndex + " ";

        }
        else
        {
            str = "select u.userId,0) as IsLockedOut  from (select *,ROW_NUMBER() over(order by userId) as rowNum from EmiteWF.dbo.Users where enabled=1 and isExternal=1 ) u "
                  + "left join aspnet_Users aspu on u.loginname=aspu.UserName "
                  + "left join aspnet_UsersInRoles ur on ur.UserId=aspu.UserId "
                  + "left join aspnet_Roles r on r.RoleId=ur.RoleId "
                  + "left join aspnet_Membership m on m.UserId=aspu.UserId "
                  + "left join EmiteWF.dbo.TeamUser tu on u.userId=tu.userId and tu.isDefault=1 "
                  + "left join EmiteWF.dbo.Team t on tu.teamId=t.teamId "
                  + "left join EmiteWF.dbo.DeptTeam dt on tu.teamId=dt.TeamId and dt.DeptTeamId in (select min(DeptTeamId) from EmiteWF.dbo.DeptTeam group by teamId)  "
                  + "left join EmiteWF.dbo.Dept d on dt.DeptId=d.DeptId "
                  + "where rowNum between " + StartRowIndex + " and " + EndRowIndex + " ";
        }
        DataTable dt = AspnetDbConnector.GenerateDataTable(str);
        return dt;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!