SQL Objects
Most of the time, the programmer resorts to other ways like Ado.net and Dapper to easily work with the SQL Stored Procedure , not knowing that he can simply manage the number of connections made or create a separate context for it. SQL Stored Procedure is called easily in the AUA Framework and has none of the above problems.
SQL Stored Procedure
Procedure call in the AUA framework is as follows
CREATE PROCEDURE [dbo].[uspGetUserRoles]
@userId bigint
AS
SELECT ROLE.Id as RoleId, ROLE.Title, ROLE.Description
FROM UserRole INNER JOIN ROLE
ON UserRole.RoleId = ROLE.Id
WHERE UserRole.AppUserId = @userId
We create a view model for the output of the procedure.
public class GetUserRolesSpResult
{
public int RoleId { get; set; }
public string Title { get; set; }
public string Description { get; set; }
}
To call a procedure in StoredProcContext , we will add the following code.
public IQueryable<GetUserRolesSpResult> GetUserRolesSp(long userId)
{
var cmd = LoadStoredProc(StoredProcedureConsts.GetUserRoles).WithSqlParam("userId", userId);
return cmd.ExecutStoredProc<GetUserRolesSpResult>();
}
To avoid the dispersal of the procedures' names, we place them in the class in a fixed order.
public class StoredProcedureConsts
{
public const string GetAppUsersCount = "uspGetAppUsersCount";
public const string GetUserRoles = "uspGetUserRoles";
}
The framework allows you to query the output of the procedure, but this is not correct, as it should write its own procedure for each task. The StoredProcService service, which includes all procedures, allows you to call your own processor.
public class StoredProcService : FuncBaseService, IStoredProcService
{
public StoredProcService(IUnitOfWork unitOfWork) : base(unitOfWork)
{
}
public IQueryable<GetUserRolesSpResult> GetUserRolesSp(long userId)
{
return UnitOfWork.StoredProc.GetUserRolesSp(userId);
}
}
SQL Function
One of the good features of the AUA framework is that it works with functions in the SQL Server to map their result to objects, and to write a LINQ query on their results, which can easily be done in AUA frameworks and it avoids generating a dirty code for this task. We call with the AUA framework a function that takes the user code and returns its name.
CREATE FUNCTION [dbo].[GetUserName] (@userId BIGINT)
RETURNS NVARCHAR(200) AS
BEGIN
RETURN (SELECT UserName FROM AppUser WHERE Id = @userId)
END
In SqlFunctionContext, you can add a new function call.
public class SqlFunctionContext
{
private readonly DbContext _dbContext;
public SqlFunctionContext(DbContext dbContext)
{
_dbContext = dbContext;
}
public string GetUserName(long userId)
{
var resultParameter = new SqlParameter("@result", SqlDbType.NVarChar, 200)
{
Direction = ParameterDirection.Output
};
ExecuteSqlCommand($"SET @result = dbo.GetUserName('{userId}')", resultParameter);
return resultParameter.Value as string;
}
private void ExecuteSqlCommand(string sqlCommand, IDbDataParameter resultParameter)
{
_dbContext?.Database?.ExecuteSqlCommand(sqlCommand, resultParameter);
}
}
In the SqlFunctionService service, the function can be accessed and called.
public class SqlFunctionService : FuncBaseService, ISqlFunctionService
{
public SqlFunctionService(IUnitOfWork unitOfWork) : base(unitOfWork)
{
}
public string GetUserName(long userId)
{
return UnitOfWork.SqlFunction
.GetUserName(userId);
}
}
SQL View
One of the concerns of .NET programmers is working with SQL Views so that they can map their results in objects and apply filters on the outputs of the view. The Entity Framework recognizes the views as a table, but the AUA framework makes it possible to map the view output to the objects and apply a filter to it.
CREATE VIEW [dbo].[UserRolesVw]
AS
SELECT AppUser.Id AS userId, AppUser.UserName, Role.Title
FROM AppUser
INNER JOIN UserRole ON AppUser.Id = UserRole.AppUserId
INNER JOIN Role ON UserRole.RoleId = Role.Id
GO
For the view output, we write a class that inherits from BaseView.
public class UserRolesVw : BaseView
{
public long UserId { get; set; }
public string UserName { get; set; }
public string Title { get; set; }
}
We also make a View DTO for the View output (if we want to create a change in the View output, we apply it to the DTO).
public class UserRolesVwDto : IMapFrom<UserRolesVw>
{
public long UserId { get; set; }
public string UserName { get; set; }
public string Title { get; set; }
}
We also create a service to work with the view, where filters will be written for the view. Interface
public interface IUserRolesVwService : IBaseGenericService<UserRolesVw, UserRolesVwDto>
{
}
ViewService
public class UserRolesVwService : BaseGenericService<UserRolesVw, UserRolesVwDto>, IUserRolesVwService
{
public UserRolesVwService(IUnitOfWork unitOfWork) : base(unitOfWork)
{
}
}
Functions that are added to the view service by default are as follows
| Function | Description |
|---|---|
| GetAll | This returns all entities and can be filtered. It also supports Async. |
| GetAllDto | This returns all entities in DTO format and can be filtered. It also supports Async. |
| GetCount | Number of entities - can be filtered. |
| GetFirst | This returns the first entity and can be filtered. |
| GetLast | This returns the last entity and can be filtered. |
| GetCountAsync | Number of entities – filterable; supporting Async. |
| GetFirstAsync | This returns the first entity and can be filtered; supporting Async. |
| GetLastAsync | This returns the last entity and can be filtered; supporting Async. |
| GetDtoById | Holding entity and mapping it in DTO format. |
| GetByIdAsync | Holding entity with the primary key; supporting Async. |
| GetDtoByIdAsync | Holding entity and mapping it in DTO format; supporting Async |
| ConvertTo | This converts a query result to another object based on configuration mapping |
| ProjectTo | This projects a query result to another object based on configuration mapping |
SQL Objects in youtube More videos
SQL Objects in youtube More videos