string BuildSqlQueryCmd(ExpandedNavigationSelectItem expanded, string condition) { string table = string.Format("[{0}]", expanded.NavigationSource.Name); string cmdSql = "select {0} {1} from {2} {3} {4} {5} {6}"; string top = string.Empty; string skip = string.Empty; string fetch = string.Empty; if (!expanded.CountOption.HasValue && expanded.TopOption.HasValue) { if (expanded.SkipOption.HasValue) { skip = string.Format("OFFSET {0} ROWS", expanded.SkipOption.Value); ; fetch = string.Format("FETCH NEXT {0} ROWS ONLY", expanded.TopOption.Value); top = string.Empty; } else top = "top " + expanded.TopOption.Value; } var cmdtxt = string.Format(cmdSql , top , expanded.ParseSelect() , table , expanded.ParseWhere(condition, this.Model) , expanded.ParseOrderBy() , skip , fetch); return cmdtxt; }
string BuildSqlQueryCmd(ExpandedNavigationSelectItem expanded, string condition) { string table = string.Format("[{0}]", expanded.NavigationSource.Name); string cmdTxt = string.Empty; if (!expanded.CountOption.HasValue && expanded.TopOption.HasValue) { if (expanded.SkipOption.HasValue) { cmdTxt = string.Format( @"select t.* from( select ROW_NUMBER() over ({0}) as rowIndex,{1} from {2} {3} ) as t where t.rowIndex between {4} and {5}" , expanded.ParseOrderBy() , expanded.ParseSelect() , table , expanded.ParseWhere(condition, this.Model) , expanded.SkipOption.Value + 1 , expanded.SkipOption.Value + expanded.TopOption.Value); } else cmdTxt = string.Format("select top {0} {1} from {2} {3} {4}" , expanded.TopOption.Value , expanded.ParseSelect() , table , expanded.ParseWhere(condition, this.Model) , expanded.ParseOrderBy()); } else { cmdTxt = string.Format("select {0} from {1} {2} {3} " , expanded.ParseSelect() , table , expanded.ParseWhere(condition, this.Model) , expanded.ParseOrderBy()); } return cmdTxt; }