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);
}