sql+aspnetpager+查询功能

#region 通过SQL语句分页 连表查询  重载 【relevanceOne关联字段】

        public DataSet GetPageDataBySql(int pageIndex, int pageSize, string tbName, string tbID, string keyName, string keyword, string orderType, string tbNameTwo, string relevanceOne,string relevanceTwo)
        {
            string strConn = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

            SqlConnection conn = new SqlConnection(strConn);

            try
            {
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();

                string sql = "";

                cmd.CommandType = CommandType.Text;
                if (!string.IsNullOrEmpty(keyword) && !string.IsNullOrEmpty(keyName))
                {
                    sql = "select top " + pageSize + " * from [" + tbName + "] tb_1 inner join [" + tbNameTwo + "] tb_2 on  tb_1." + relevanceOne + "=tb_2." + relevanceTwo + " where tb_1." + relevanceOne + " not in(select top " + (pageIndex - 1) * pageSize + " tb_3." + relevanceOne + " from [" + tbName + "] tb_3 inner join [" + tbNameTwo + "] tb_4 on  tb_3." + relevanceOne + "=tb_4." + relevanceTwo + " where " + keyName + " like '%" + keyword + "%'  order by tb_3." + tbID + " " + orderType + ") and " + keyName + " like '%" + keyword + "%'  order by  tb_1." + tbID + " " + orderType;
                }
                else
                {
                    sql = "select top " + pageSize + " * from [" + tbName + "] tb_1 inner join [" + tbNameTwo + "] tb_2 on  tb_1." + relevanceOne + "=tb_2." + relevanceTwo + " where tb_1." + relevanceOne + " not in(select top " + (pageIndex - 1) * pageSize + " tb_3." + relevanceOne + " from [" + tbName + "] tb_3 inner join [" + tbNameTwo + "] tb_4 on  tb_3." + relevanceOne + "=tb_4." + relevanceTwo + "  order by tb_3." + tbID + " " + orderType + ")  order by  tb_1." + tbID + " " + orderType;
                }

                

                cmd.CommandText = sql;

                SqlDataAdapter ada = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();

                if (ada != null)
                {
                    ada.Fill(ds);
                    return ds;
                }
                return null;
            }
            finally
            {
                conn.Close();
            }

        }
        #endregion