private void PostSearch(int?page, PostsSearchModel searchModel)
{
var pageNumber = (page ?? 1);
const int pageSize = 10;
var postSearchModel = new SearchPostsBy
{
Body = searchModel.Body,
PostTypeId = 1,
Tags = searchModel.Tags
, PageNumberForSimpleData = pageNumber
};
//dynamic results = _simpleDataPostsServices.GetFewPosts();
int total;
dynamic results = _simpleDataPostsServices.Search(postSearchModel, out total);
var posts = new List <PostModel>();
foreach (dynamic result in results)
{
posts.Add(GetPostModel(result));
}
if (posts.Any())
{
var staticlist = new StaticPagedList <PostModel>(posts, pageNumber, pageSize, total);
searchModel.Posts = staticlist;
}
}
private void PostSearch(int?page, PostsSearchModel searchModel)
{
var pageNumber = (page ?? 1);
const int pageSize = 10;
var startRowNum = (pageNumber - 1) * pageSize;
var endRowNum = startRowNum + pageSize;
var postSearchModel = new SearchPostsBy
{
StartRowNum = startRowNum,
EndRowNum = endRowNum,
Body = searchModel.Body,
PostTypeId = 1,
Tags = searchModel.Tags
};
var results = _dapperPostsServices.Search(postSearchModel).ToList();
var firstResult = results.Any() ? results.FirstOrDefault() : null;
if (firstResult != null)
{
var total = firstResult.Total;
var posts = results.Select(GetPostModel).ToList();
var staticlist = new StaticPagedList <PostModel>(posts, pageNumber, pageSize, total);
searchModel.Posts = staticlist;
}
}
private static Sql GenerateSql(SearchPostsBy searchPostsBy)
{
var sql = Sql.Builder.Append(@"
SELECT tbl.*,ROW_NUMBER() OVER (ORDER BY ID ASC) rownum
,Count(*) OVER () Total
FROM Posts as tbl
where 1=1 " );
if (searchPostsBy.PostTypeId > 0)
{
sql.Append(@"AND PostTypeId = @postTypeId", new { @postTypeId = searchPostsBy.PostTypeId });
}
if (!string.IsNullOrEmpty(searchPostsBy.Body))
{
sql.Append(@"AND FREETEXT(Body,@body)", new{ @body = searchPostsBy.Body });
}
if (!string.IsNullOrEmpty(searchPostsBy.Tags))
{
sql.Append(@"AND FREETEXT(Tags,@tags)", new{ @tags = searchPostsBy.Tags });
}
return(sql);
}
//QueryMultiple
private static string GetSearchSql(SearchPostsBy searchPostsBy, out DynamicParameters param)
{
var postQuery = new PostsQuery();
param = new DynamicParameters();
if (!string.IsNullOrEmpty(searchPostsBy.Body))
{
//param.AddDynamicParams(new {@body = "%" + SearchPostsBy.Body + "%"});
param.AddDynamicParams(new { @body = searchPostsBy.Body });
postQuery = postQuery.ByBody();
}
if (!string.IsNullOrEmpty(searchPostsBy.Tags))
{
// param.AddDynamicParams(new {@tags = "%" + SearchPostsBy.Tags + "%"});
param.AddDynamicParams(new { @tags = searchPostsBy.Tags });
postQuery = postQuery.ByTags();
}
// ReSharper disable RedundantAnonymousTypePropertyName
param.AddDynamicParams(new { @startRow = searchPostsBy.StartRowNum });
// ReSharper restore RedundantAnonymousTypePropertyName
// ReSharper disable RedundantAnonymousTypePropertyName
param.AddDynamicParams(new { @endRow = searchPostsBy.EndRowNum });
// ReSharper restore RedundantAnonymousTypePropertyName
return(postQuery.Bind());
}
public dynamic Search(SearchPostsBy searchPostsBy)
{
dynamic posts = new Posts(_connectionStringName);
var generatedSql = GenerateSql(searchPostsBy);
var objects = GetArgs(searchPostsBy);
var result = posts.Paged(where : generatedSql, currentPage: searchPostsBy.PageNumberForSimpleData, pageSize: 10, args: objects);
return(result);
}
public Page <Post> Search(SearchPostsBy searchPostsBy)
{
var sql = GenerateSql(searchPostsBy);
const int itemsPerPage = 10;
using (var db = _databaseContext.StackOverflowDB)
{
return(db.Page <Post>(searchPostsBy.PageNumberForPetaPoco, itemsPerPage, sql));
}
}
public IEnumerable <PostSearResults> Search(SearchPostsBy searchPostsBy)
{
DynamicParameters param;
var sql = GetSearchSql(searchPostsBy, out param);
using (var profiledConnection = _databaseContext.ProfiledConnection)
{
var posts = profiledConnection.Query <PostSearResults>(sql, param);
return(posts.ToList());
}
}
private void PostSearch(int?page, PostsSearchModel searchModel)
{
var pageNumber = (page ?? 1);
const int pageSize = 10;
var postSearchModel = new SearchPostsBy
{
Body = searchModel.Body,
PostTypeId = 1,
Tags = searchModel.Tags,
PageNumberForPetaPoco = pageNumber
};
var result = _petaPocoPostsServices.Search(postSearchModel);
var total = result.TotalItems;
var posts = result.Items.Select(GetPostModel).ToList();
var staticlist = new StaticPagedList <PostModel>(posts, pageNumber, pageSize, (int)total);// hopefully the number does not get too big
searchModel.Posts = staticlist;
}
private static string[] GetArgs(SearchPostsBy searchPostsBy)
{
var args = new List <string>();
if (searchPostsBy.PostTypeId > 0)
{
args.Add(searchPostsBy.PostTypeId.ToString(CultureInfo.InvariantCulture));//, new { @postTypeId = searchPostsBy.PostTypeId });
}
if (!string.IsNullOrEmpty(searchPostsBy.Body))
{
args.Add(searchPostsBy.Body);
}
if (!string.IsNullOrEmpty(searchPostsBy.Tags))
{
args.Add(searchPostsBy.Tags);
}
return(args.ToArray());
//return string.Join(",", args.ToArray());
}
public dynamic Search(SearchPostsBy searchPostsBy, out int totalRecords)
{
dynamic expression1 = _databaseContext.StackOverflowDb.Posts.Id == _databaseContext.StackOverflowDb.Posts.Id;
dynamic expression2 = _databaseContext.StackOverflowDb.Posts.Id == _databaseContext.StackOverflowDb.Posts.Id;
dynamic expression3 = _databaseContext.StackOverflowDb.Posts.Id == _databaseContext.StackOverflowDb.Posts.Id;
const int pageSize = 10;
if (searchPostsBy.PostTypeId > 0)
{
expression1 = _databaseContext.StackOverflowDb.Posts.PostTypeId == searchPostsBy.PostTypeId;
}
if (!string.IsNullOrEmpty(searchPostsBy.Tags))
{
//seems like full text search is not supported
//http://stackoverflow.com/questions/16416934/fulltext-search-with-simple-data
//expression2 = _databaseContext.StackOverflowDb.Posts.Tags.Freetext(searchPostsBy.Tags);
expression2 = _databaseContext.StackOverflowDb.Posts.Tags.Like(searchPostsBy.Tags);
}
if (!string.IsNullOrEmpty(searchPostsBy.Body))
{
//seems like not supported
//http://stackoverflow.com/questions/16416934/fulltext-search-with-simple-data
//expression3 = _databaseContext.StackOverflowDb.Posts.Body.Freetext(searchPostsBy.Body);
expression3 = _databaseContext.StackOverflowDb.Posts.Body.Like(searchPostsBy.Body);
}
// dynamic searchExpression = expression1;// && expression2 && expression3;
var recordsToSkip = searchPostsBy.PageNumberForSimpleData > 0 ? pageSize * (searchPostsBy.PageNumberForSimpleData - 1) : 0;
Future <int> Total;
dynamic results = _databaseContext.StackOverflowDb
.Posts
.All()
.Where(expression1 && expression2 && expression3)
.OrderById()
.WithTotalCount(out Total)
.Skip(recordsToSkip).Take(pageSize).ToList();
totalRecords = Total.HasValue ? Total.Value : 0;
return(results);
}
private static string GenerateSql(SearchPostsBy searchPostsBy)
{
var sql = new StringBuilder();
//sql.Append(@" Where 1=1");
// sql.Append(@"SELECT tbl.*
// FROM Posts as tbl
// where 1=1 ");
if (searchPostsBy.PostTypeId > 0)
{
sql.Append(@" PostTypeId = @0");//, new { @postTypeId = searchPostsBy.PostTypeId });
}
if (!string.IsNullOrEmpty(searchPostsBy.Body))
{
sql.Append(@" AND FREETEXT(Body,@1)");//, new { @body = searchPostsBy.Body });
}
if (!string.IsNullOrEmpty(searchPostsBy.Tags))
{
sql.Append(@" AND FREETEXT(Tags,@2)"); //, new { @tags = searchPostsBy.Tags });
}
return(sql.ToString());
}
