private FilterTargetInfo GetFilterTargetInfo(SqlConnection connection, string controller, string action) { FilterTargetInfo filterTargetInfo = null; string q = $"Select top (1) f.FilterTargetId, f.FilterTargetSchema, f.FilterTargetName from [dbo].[FilterTargets] as f where f.ControllerName = @controller And f.ActionName = @action order by f.FilterTargetId"; SqlParameter p1 = new SqlParameter("@controller", controller); SqlParameter p2 = new SqlParameter("@action", action); SqlCommand cmd = new SqlCommand(q, connection); cmd.Parameters.AddRange(new[] { p1, p2 }); using (SqlDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { dr.Read(); filterTargetInfo = new FilterTargetInfo { FilterTargetSchema = (string)dr["FilterTargetSchema"], FilterTargetName = (string)dr["FilterTargetName"], FilterTargetId = (int)dr["FilterTargetId"] }; } } return(filterTargetInfo); }
private string BuildWherePartOfQuery(NameValueCollection queryString, Dictionary <string, QueryColumn> columnData, FilterTargetInfo filterTargetInfo) { string keyField = "BookId"; if (columnData.Count == 0) { return(""); } StringBuilder sb = new StringBuilder(); sb.Append($"WHERE {keyField} IN (SELECT {keyField} FROM {filterTargetInfo.FilterTargetSchema}.{filterTargetInfo.FilterTargetName} WHERE "); int index = 0; foreach (KeyValuePair <string, QueryColumn> colData in columnData) { string operatorPart = CreateQueryOperatorPart(queryString, colData); if (String.IsNullOrEmpty(operatorPart)) { sb.Remove(sb.Length - 5, 5); //remove ' AND ' } sb.Append(operatorPart); if (index != columnData.Count - 1) // not last { sb.Append(" AND "); } index++; } sb.Append(")"); return(sb.ToString()); }
public IEnumerable <Book> UpdateBooksFromFilterUsingRawSql(NameValueCollection queryString, string controller, string action) { List <Book> result = new List <Book>(); try { string connectionstring = _context.Config[Globals.CONNECTION_NAME_CONFIG]; SqlConnection connection = new SqlConnection(connectionstring); connection.Open(); FilterTargetInfo filterTargetInfo = GetFilterTargetInfo(connection, controller, action); if (filterTargetInfo == null) { return(result); // empty } Dictionary <string, QueryColumn> dict = GetFilterInfo(filterTargetInfo.FilterTargetId, connection, queryString.AllKeys); //get main part on query string sqlMainPart = $"Select * from {filterTargetInfo.FilterTargetSchema}.{filterTargetInfo.FilterTargetName} "; string sqlWherePart = BuildWherePartOfQuery(queryString, dict, filterTargetInfo); if (string.IsNullOrEmpty(sqlWherePart)) { return(result); // not such filters } string fullQueryText = sqlMainPart + sqlWherePart; SqlCommand cmdResult = new SqlCommand(fullQueryText, connection); using (SqlDataReader dataReader = cmdResult.ExecuteReader()) { while (dataReader.Read()) { int id = (int)dataReader["BookId"]; Book book = result.Where(b => b.BookId == id).FirstOrDefault(); Author author; Tag tag; if (book != null) //book in list already exist { int authorId = (int)dataReader["AuthorId"]; if (book.AuthorsOfBooks.Select(ab => ab.Author).Where(a => a.AuthorId == authorId).FirstOrDefault() == null) { author = new Author { AuthorId = authorId, LastName = (string)dataReader["LastName"], FirstName = (string)dataReader["FirstName"] }; book.AuthorsOfBooks.Add(new AuthorsOfBooks { Author = author, Book = book }); } int tagId = (int)dataReader["TagId"]; if (book.BookTags.Select(bt => bt.Tag).Where(a => a.TagId == tagId).FirstOrDefault() == null) { tag = new Tag { TagId = tagId, NameForLabels = (string)dataReader["TagNameForLabels"], NameForLinks = (string)dataReader["TagNameForLinks"] }; book.BookTags.Add(new BookTags { Tag = tag, Book = book }); } continue; } else { book = new Book(); } book.BookId = id; book.Name = (string)dataReader["Name"]; book.LanguageId = (int)dataReader["LanguageId"]; book.Pages = (int)dataReader["Pages"]; book.Description = (string)dataReader["Description"]; book.CategoryId = (int)dataReader["CategoryId"]; book.Year = (int)dataReader["Year"]; book.ImageData = (byte[])dataReader["ImageData"]; book.ImageMimeType = (string)dataReader["ImageMimeType"]; author = new Author { AuthorId = (int)dataReader["AuthorId"], LastName = (string)dataReader["LastName"], FirstName = (string)dataReader["FirstName"] }; book.AuthorsOfBooks.Add(new AuthorsOfBooks { Author = author, Book = book }); if (!(dataReader["TagId"] is DBNull)) { tag = new Tag { TagId = (int)dataReader["TagId"], NameForLabels = (string)dataReader["TagNameForLabels"], NameForLinks = (string)dataReader["TagNameForLinks"] }; book.BookTags.Add(new BookTags { Tag = tag, Book = book }); } result.Add(book); } } } catch (Exception ex) { #if DEBUG throw ex; #endif return(result); // empty } _filterApplied = true; this.Books = result; return(result); }