public ActionResult GetSelect(string searchTerm, int pageSize, int pageNum)
{
IQueryable <RoleViewModel> queryRoleList = RoleService.GetAll(true).AsQueryable();
var roles = new List <RoleViewModel>();
int roleCount = 0;
if (searchTerm != null)
{
searchTerm = searchTerm.ToLower();
}
queryRoleList = queryRoleList.Where(x => x.Name.Like(searchTerm));
roleCount = queryRoleList.Count();
roles = queryRoleList.Skip(pageSize * (pageNum - 1))
.Take(pageSize)
.ToList();
SelectPageResult pagedUsers = SelectGenerator.RolesToSelectPageResult(roles, roleCount);
return(new JsonResult
{
Data = pagedUsers,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public ActionResult GetSelect(string searchTerm, int pageSize, int pageNum)
{
IQueryable <BookViewModel> queryBookList = BookService.GetAll(true).AsQueryable();
var books = new List <BookViewModel>();
int booksCount = 0;
if (searchTerm != null)
{
searchTerm = searchTerm.ToLower();
}
queryBookList = queryBookList.Where(x => x.AuthorAndTitle.Like(searchTerm));
booksCount = queryBookList.Count();
books = queryBookList.Skip(pageSize * (pageNum - 1))
.Take(pageSize)
.ToList();
SelectPageResult pagedUsers = SelectGenerator.BooksToSelectPageResult(books, booksCount);
return(new JsonResult
{
Data = pagedUsers,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public ActionResult GetSelect(string searchTerm, int pageSize, int pageNum)
{
IQueryable <UserViewModel> queryLanguageList = UserService.GetAll(true).AsQueryable();
var languages = new List <UserViewModel>();
int languagesCount = 0;
if (searchTerm != null)
{
searchTerm = searchTerm.ToLower();
}
queryLanguageList = queryLanguageList.Where(x => x.Username.Like(searchTerm) ||
x.Id.Equals(searchTerm));
languagesCount = queryLanguageList.Count();
languages = queryLanguageList.Skip(pageSize * (pageNum - 1))
.Take(pageSize)
.ToList();
SelectPageResult pagedUsers = SelectGenerator.UsersToSelectPageResult(languages, languagesCount);
return(new JsonResult
{
Data = pagedUsers,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
public void GenerateSelectWithNullQueryThrowsException()
{
var sut = new SelectGenerator(null);
Func <string> buildSut = () => sut.ToString();
var ex = Assert.Throws <ArgumentException>(buildSut);
Assert.Equal("The provided query specification is invalid.", ex.Message);
}
protected override DbCommandDefinition CreateDbCommandDefinition(
DbProviderManifest providerManifest, DbCommandTree commandTree)
{
if (commandTree == null)
throw new ArgumentNullException("commandTree");
SqlGenerator generator = null;
if (commandTree is DbQueryCommandTree)
generator = new SelectGenerator();
else if (commandTree is DbInsertCommandTree)
generator = new InsertGenerator();
else if (commandTree is DbUpdateCommandTree)
generator = new UpdateGenerator();
else if (commandTree is DbDeleteCommandTree)
generator = new DeleteGenerator();
else if (commandTree is DbFunctionCommandTree)
generator = new FunctionGenerator();
string sql = generator.GenerateSQL(commandTree);
EFMySqlCommand cmd = new EFMySqlCommand();
cmd.CommandText = sql;
if (generator is FunctionGenerator)
cmd.CommandType = (generator as FunctionGenerator).CommandType;
SetExpectedTypes(commandTree, cmd);
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
function = (commandTree as DbFunctionCommandTree).EdmFunction;
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair<string, TypeUsage> queryParameter in commandTree.Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = queryParameter.Key;
parameter.Direction = ParameterDirection.Input;
parameter.DbType = Metadata.GetDbType(queryParameter.Value);
FunctionParameter funcParam;
if (function != null &&
function.Parameters.TryGetValue(queryParameter.Key, false, out funcParam))
{
parameter.ParameterName = funcParam.Name;
parameter.Direction = Metadata.ModeToDirection(funcParam.Mode);
parameter.DbType = Metadata.GetDbType(funcParam.TypeUsage);
}
cmd.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen
foreach (DbParameter p in generator.Parameters)
cmd.Parameters.Add(p);
return CreateCommandDefinition(cmd);
}
public void GenerateSelectOfRelevantIso()
{
var queryData = Builder.BuildQueryData(Builder.Pack("Style"));
var sut = new SelectGenerator(queryData);
var response = sut.ToString();
var formattedResponse = SqlFormattingManager.DefaultFormat(response);
var expectedResponse = SqlFormattingManager.DefaultFormat("SELECT [Style_Cleaned] FROM [MySchema].[MyTable]");
Assert.Equal(expectedResponse, formattedResponse);
}
public void GenerateSelectWithDistinctValuesOptionNullDefaultsToFalse()
{
var queryData = Builder.BuildQueryData(Builder.Pack("All_Values_Column"));
var sut = new SelectGenerator(queryData);
var response = sut.ToString();
var formattedResponse = SqlFormattingManager.DefaultFormat(response);
var expectedResponse = SqlFormattingManager.DefaultFormat("SELECT [All_Values_Column_Cleaned] FROM [MySchema].[MyTable]");
Assert.Equal(expectedResponse, formattedResponse);
}
public void GenerateSelectWithNotAcceptableFieldThrowsException()
{
var fakeValidColumns = Builder.BuildValidColumns(_ => true);
var queryData = Builder.BuildQueryData(Builder.Pack("Bad_Field"), validColumns: fakeValidColumns);
var sut = new SelectGenerator(queryData);
Func <string> buildSut = () => sut.ToString();
var ex = Assert.Throws <ArgumentException>(buildSut);
Assert.Equal("The provided query specification is invalid.", ex.Message);
}
public void GenerateSelectOfProductId()
{
const string field = "ProductId";
var queryData = Builder.BuildQueryData(Builder.Pack(field));
var sut = new SelectGenerator(queryData);
var response = sut.ToString();
var formattedResponse = SqlFormattingManager.DefaultFormat(response);
var expectedResponse = SqlFormattingManager.DefaultFormat("SELECT [ProductId_Cleaned] FROM [MySchema].[MyTable]");
Assert.Equal(expectedResponse, formattedResponse);
}
public async Task <IEnumerable <T> > Select <T>(QueryConditions queryConditions = null,
FilterRequest filter = null, SqlTransaction transaction = null)
{
IEnumerable <T> result = null;
var type = typeof(T);
var queryGenerator = new SelectGenerator();
var query = queryGenerator.GetSelectViewModel(type, queryConditions, filter);
var mapper = new SelectMapper();
await Task.Run(() =>
{
mapper.ReflectionTableViewModelsMapping(_connectionService,
query, type, transaction);
result = mapper.GetSelectObject <T>(query);
});
return(result);
}
public void GenerateDistinctSelectOfProductIdAndRelevantIsoRespectsFieldOrder()
{
var fields = new[] {
"ProductId_ID",
"Some_Item"
};
var queryData = Builder.BuildQueryData(fields);
var sut = new SelectGenerator(queryData);
var response = sut.ToString();
var formattedResponse = SqlFormattingManager.DefaultFormat(response);
var expectedResponse = SqlFormattingManager.DefaultFormat("SELECT [ProductId_ID_Cleaned], [Some_Item_Cleaned] FROM [MySchema].[MyTable]");
Assert.Equal(expectedResponse, formattedResponse);
}
public void GenerateSelectOfRelevantIsoAndProductId()
{
var fields = new[] {
"Style",
"ProductId"
};
var queryData = Builder.BuildQueryData(fields);
var sut = new SelectGenerator(queryData);
var response = sut.ToString();
var formattedResponse = SqlFormattingManager.DefaultFormat(response);
var expectedResponse = SqlFormattingManager.DefaultFormat("SELECT [Style_Cleaned], [ProductId_Cleaned] FROM [MySchema].[MyTable]");
Assert.Equal(expectedResponse, formattedResponse);
}
public async Task <int> Count <T>(CancellationToken cancellationToken,
string condition = "", SqlTransaction transaction = null)
{
var type = typeof(T);
var selectGenerator = new SelectGenerator();
var query = selectGenerator.GetCountSelect(type, condition);
int result;
using (var connection = _connectionService.Create())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = query.Main;
cmd.Transaction = transaction;
result = (Int32)await cmd.ExecuteScalarAsync(cancellationToken);
}
}
return(result);
}
public void GenerateDistinctSelectUsingGroupByWithColumnNotInSelectExpectsException()
{
var fields = new[]
{
"Distinct_Values_Column",
"Another_Values_Column"
};
var groupBy = new[]
{
"Another_Values_Column",
"Some_Other_Column"
};
var queryData = Builder.BuildQueryData(fields, groupBy: groupBy, distinct: true);
var sut = new SelectGenerator(queryData);
Func <string> buildSut = () => sut.ToString();
var ex = Assert.Throws <ArgumentException>(buildSut);
Assert.Equal("The provided query specification is invalid.", ex.Message);
}
protected override System.Data.Entity.Core.Common.DbCommandDefinition CreateDbCommandDefinition(
System.Data.Entity.Core.Common.DbProviderManifest providerManifest, DbCommandTree commandTree)
{
if (commandTree == null)
{
throw new ArgumentNullException("commandTree");
}
SqlGenerator generator = null;
if (commandTree is DbQueryCommandTree)
{
generator = new SelectGenerator();
}
else if (commandTree is DbInsertCommandTree)
{
generator = new InsertGenerator();
}
else if (commandTree is DbUpdateCommandTree)
{
generator = new UpdateGenerator();
}
else if (commandTree is DbDeleteCommandTree)
{
generator = new DeleteGenerator();
}
else if (commandTree is DbFunctionCommandTree)
{
generator = new FunctionGenerator();
}
string sql = generator.GenerateSQL(commandTree);
EFMySqlCommand cmd = new EFMySqlCommand();
cmd.CommandText = sql;
if (generator is FunctionGenerator)
{
cmd.CommandType = (generator as FunctionGenerator).CommandType;
}
SetExpectedTypes(commandTree, cmd);
EdmFunction function = null;
if (commandTree is DbFunctionCommandTree)
{
function = (commandTree as DbFunctionCommandTree).EdmFunction;
}
// Now make sure we populate the command's parameters from the CQT's parameters:
foreach (KeyValuePair <string, TypeUsage> queryParameter in commandTree.Parameters)
{
DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = queryParameter.Key;
parameter.Direction = ParameterDirection.Input;
parameter.DbType = Metadata.GetDbType(queryParameter.Value);
#if NET_45_OR_GREATER
if (queryParameter.Value.EdmType is PrimitiveType &&
((PrimitiveType)queryParameter.Value.EdmType).PrimitiveTypeKind == PrimitiveTypeKind.Geometry)
{
((MySqlParameter)parameter).MySqlDbType = MySqlDbType.Geometry;
}
#endif
FunctionParameter funcParam;
if (function != null &&
function.Parameters.TryGetValue(queryParameter.Key, false, out funcParam))
{
parameter.ParameterName = funcParam.Name;
parameter.Direction = Metadata.ModeToDirection(funcParam.Mode);
parameter.DbType = Metadata.GetDbType(funcParam.TypeUsage);
}
cmd.Parameters.Add(parameter);
}
// Now add parameters added as part of SQL gen
foreach (DbParameter p in generator.Parameters)
{
cmd.Parameters.Add(p);
}
return(CreateCommandDefinition(cmd));
}
