public async Task Query()
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
ModelResponse <List <ResultTable> > result = await command.Query(@"SELECT 1, 2, 3;");
Assert.True(result.Correct);
}
public async Task ResultTableHtmlTable()
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
ModelResponse <List <ResultTable> > result = await command.Query(@"SELECT Name = 'Israel', Age=27;");
string table = GridBuilder.HtmlTable(result.Model.ElementAt(0));
Assert.True(result.Correct && !table.IsNotValid());
}
/// <summary>
/// Retrieves the first entity that
/// compels to the supplied conditions and
/// includes the specified columns during
/// selection
/// </summary>
/// <param name="conditions">Query Conditions</param>
/// <param name="columns">Columns to include</param>
/// <returns>ComplexReponse T</returns>
public async Task <ModelResponse <T> > GetFirst(List <QueryCondition <T> > conditions, string[] columns = null)
{
//Verify select columns
if (columns.IsNotValid())
{
columns = Columns;
}
//Verify integrity Columns (All the supplied columns must exists inside the current entity)
if (!columns.ToList().TrueForAll(Columns.Contains))
{
return(new ModelResponse <T>(false, @"The supplied columns does not exist in the current entity."));
}
//Inicialize query conditions
if (conditions.IsNotValid())
{
conditions = new List <QueryCondition <T> >();
}
else
{
//Validate query conditions integrity (All the supplied property-column query condition reference must exists inside the current entity)
if (!conditions.Select(c => c.Property).ToList().TrueForAll(columns.Contains))
{
return(new ModelResponse <T>(false, @"The supplied columns does not exist in the current entity."));
}
}
ModelResponse <T> response;
try
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
//Get the user supplied conditions
string userSqlConditions = conditions.ToSqlQuery(out SqlParameter[] parameters);
string sql = $@"Select Top 1 [{string.Join(@"], [", columns)}] From [dbo].[{TableName}] Where {userSqlConditions} And ([Deleted] Is Null);";
ModelResponse <List <ResultTable> > commandResult = await command.Query(sql, parameters);
if (commandResult.Correct)
{
T model = commandResult.Model
.FirstOrDefault()
.Rows.ElementAt(0)
.ToEntity <T>();
response = new ModelResponse <T>(model);
}
else
{
response = new ModelResponse <T>(false, commandResult.Message);
}
}
catch (Exception ex)
{
response = new ModelResponse <T>(ex);
}
return(response);
}
/// <summary>
/// Retrieves the first entity that
/// belongs to the specified key and
/// includes the specified columns during
/// selection
/// </summary>
/// <param name="id">Primary key</param>
/// <param name="columns">Columns to include</param>
/// <returns>ComplexReponse T</returns>
public async Task <ModelResponse <T> > GetFirst(long id, string[] columns)
{
//Verify entity primary key
if (id <= 0)
{
return(new ModelResponse <T>(false, @"The specified primary key is not valid."));
}
//Verify select columns
if (columns.IsNotValid())
{
columns = Columns;
}
//Verify integrity Columns (All the supplied columns must exists inside the current entity)
if (!columns.ToList().TrueForAll(Columns.Contains))
{
return(new ModelResponse <T>(false, @"The supplied columns does not exist in the current entity."));
}
ModelResponse <T> response;
try
{
SqlServerCommand command = new SqlServerCommand(ConnectionString);
string sql = $@"Select Top 1 [{string.Join(@"], [", columns)}] From [dbo].[{TableName}] Where [Id] = @Id And [Deleted] Is Null;";
SqlParameter[] parameters = new SqlParameter[1]
{
new SqlParameter(@"@Id", id)
};
ModelResponse <List <ResultTable> > commandResult = await command.Query(sql, parameters);
if (commandResult.Correct)
{
T model = commandResult.Model
.FirstOrDefault()
.Rows.ElementAt(0)
.ToEntity <T>();
response = new ModelResponse <T>(model);
}
else
{
response = new ModelResponse <T>(false, commandResult.Message);
}
}
catch (Exception ex)
{
response = new ModelResponse <T>(ex);
}
return(response);
}
/// <summary>
/// Retrieves all the entities that match with the
/// page request and query conditions specified
/// and includes the column select collection
/// during the selection
/// </summary>
/// <param name="pagination">Page request</param>
/// <param name="columns">Column selection collection</param>
/// <param name="conditions">Query conditions to apply</param>
/// <returns>ComplexResponse of Paginated collection</returns>
public async Task <ModelResponse <PaginatedCollection <T> > > GetRecords(Pagination pagination, string[] columns = null, List <QueryCondition <T> > conditions = null)
{
//Verify pagination instance
if (pagination == null)
{
return(new ModelResponse <PaginatedCollection <T> >(false, @"The specified pagination instance is not valid."));
}
//Initialize select columns
if (columns.IsNotValid())
{
columns = Columns;
}
//Verify integrity Columns (All the supplied columns must exists inside the current entity)
if (!columns.ToList().TrueForAll(Columns.Contains))
{
return(new ModelResponse <PaginatedCollection <T> >(false, @"The supplied columns does not exist in the current entity."));
}
//Inicialize query conditions
if (conditions.IsNotValid())
{
conditions = new List <QueryCondition <T> >();
}
else
{
//Validate query conditions integrity (All the supplied property-column query condition reference must exists inside the current entity)
if (!conditions.Select(c => c.Property).ToList().TrueForAll(Columns.Contains))
{
return(new ModelResponse <PaginatedCollection <T> >(false, @"The supplied columns does not exist in the current entity."));
}
}
ModelResponse <PaginatedCollection <T> > response;
try
{
//Build count query using the given parameters
StringBuilder sqlCount = new StringBuilder($@"Select Cast(count(*) as BigInt) as Total From [dbo].[{TableName}] ");
//Conditions to apply to
List <string> defaultConditions = new List <string>();
//Parameter list to pass
List <SqlParameter> parameterList = new List <SqlParameter>();
//Include or not deleted records
if (!pagination.IncludeAll)
{
defaultConditions.Add(@"[Deleted] Is Null");
}
//Include keyword search inside searchable columns
if (!pagination.KeyWords.IsNotValid() && !SearchColumns.IsNotValid())
{
defaultConditions.Add($@"[{string.Join(@"] Like '%' + @SearchKeyWord + '%' Or [", SearchColumns)}] Like '%' + @SearchKeyWord + '%'");
parameterList.Add(new SqlParameter(@"@SearchKeyWord", pagination.KeyWords));
}
//Include date range start
if (!pagination.Start.IsNotValid())
{
defaultConditions.Add(@"[Created] >= @StartAt");
parameterList.Add(new SqlParameter(@"@StartAt", pagination.Start));
}
//Include date range end
if (!pagination.End.IsNotValid())
{
defaultConditions.Add(@"[Created] <= @EndAt");
parameterList.Add(new SqlParameter(@"@EntAt", pagination.End));
}
//Adds any conditions if applies
if (defaultConditions.Any())
{
sqlCount.Append($@"Where ({string.Join(@") And (", defaultConditions)}) ");
}
//Command instance
SqlServerCommand command = new SqlServerCommand(ConnectionString);
//Get the user supplied conditions
string userSqlConditions = conditions.ToSqlQuery(out SqlParameter[] userParameters);
//Add the supplied user conditions
if (!userSqlConditions.IsNotValid())
{
//Add 'where' clausule if there is not any default condition
if (!defaultConditions.Any())
{
sqlCount.Append(@"Where ");
}
sqlCount
.Append(@"And ")
.Append(userSqlConditions);
parameterList.AddRange(userParameters);
}
sqlCount.Append(@";");
//Params added
SqlParameter[] parameters = parameterList.ToArray();
//Count results
ModelResponse <List <ResultTable> > resultCount = await command.Query(sqlCount.ToString(), parameters);
if (resultCount.Correct)
{
//Get the total records
long total = resultCount.Model
.First()
.GetFirstResult <long>(@"Total");
//The sql select query result is succeded but it has not returned any record
if (total.Equals(0))
{
return(new ModelResponse <PaginatedCollection <T> >(false, @"The sql select query result is succeded but it has not returned any record."));
}
//Calculate the pagination size by the given total records
pagination.Calculate(total);
//Sql select instance
StringBuilder sqlSelect = new StringBuilder($@"Select [{string.Join(@"], [", columns)}] From [dbo].[{TableName}] ");
//Apply the same conditions as the count sql
if (defaultConditions.Any())
{
sqlSelect.Append($@"Where ({string.Join(@") And (", defaultConditions)})");
}
//Add the supplied user conditions
if (!userSqlConditions.IsNotValid())
{
//Add 'where' clausule if there is not any default condition
if (!defaultConditions.Any())
{
sqlSelect.Append(@"Where ");
}
sqlSelect
.Append(@" And ")
.Append(userSqlConditions);
}
//Skip and take records ordered by ascending id
sqlSelect
.Append($@"Order By [Id] Asc ")
.Append($@"Offset {pagination.RequestedIndex * pagination.PageSize} Rows ")
.Append($@"Fetch Next {pagination.PageSize} Rows Only;");
//Page result
ModelResponse <List <ResultTable> > resultPage = await command.Query(sqlSelect.ToString(), parameters);
if (resultPage.Correct)
{
//Create the page reference
PaginatedCollection <T> page = new PaginatedCollection <T>(pagination, resultPage.Model.First().ToEntities <T>());
//Create response
response = new ModelResponse <PaginatedCollection <T> >(page);
}
else
{
//The sql select query result is succeded but it has not returned any record
response = new ModelResponse <PaginatedCollection <T> >(false, resultPage.Message);
}
}
else
{
//The sql count query result is succeded but it has not returned any record
response = new ModelResponse <PaginatedCollection <T> >(false, resultCount.Message);
}
}
catch (Exception ex)
{
response = new ModelResponse <PaginatedCollection <T> >(ex);
}
return(response);
}