Bài này sẽ hướng dẫn bạn sử dụng View trong SQL. View là một bảng thực dựa trên tập kết quả của một câu lệnh SQL, một View chứa các hàng và các cột như một bảng thực. Các trường trong View là các trường của một hoặc nhiều bảng trong database.
Để chúng ta hình dung View là gì : thì trong SQL một VIEW là một bảng ảo được tạo bằng cách kết hợp dữ liệu từ một hoặc nhiều bảng. VIEW không lưu trữ dữ liệu mà chỉ là một cách thuận tiện để truy cập và tổ chức dữ liệu từ các bảng với nhau.
- Sử dụng câu lệnh sau để tạo 1 View:
CREATE VIEW CategoryView AS
SELECT Id, Name, DisplayOrder
FROM Category
- Để truy vấn View vừa tạo thì sử dụng câu lệnh sau:
SELECT * FROM CategoryView;
ALTER VIEW CategoryView AS
SELECT Id, Name, DisplayOrder
FROM Category WHERE Id = 1
- Mệnh đề SELECT không được chứa từ khoá DISTINCT.
- Mệnh đề SELECT không được chứa các hàm tổng.
- Mệnh đề SELECT có thể không chứa các hàm tập hợp.
- Mệnh đề SELECT không được chứa các toán tử tập hợp.
- Mệnh đề SELECT không được chứa mệnh đề ORDER BY.
- Mệnh đề FROM không được chứa nhiều bảng.
- Mệnh đề WHERE không được chứa các truy vấn con.
- Truy vấn không chứa GROUP BY hoặc HAVING.
- Các cột được ứng lượng không thể được update.
- Tất cả các cột NOT NULL từ bảng cơ sở phải được select trong view để truy vấn INSERT hoạt động.
Ví dụ:
UPDATE CategoryView
SET Name = 'Le Hieu'
WHERE Id = 1;
DELETE FROM CategoryView
WHERE Id = 1;
- Xoá View:
DROP VIEW CategoryView;
- Các ý trên là những phần Thêm, Sửa, Xoá 1 View cơ bản. Tiếp đến thì ta sẽ tạo 1 ví dụ vào dự án và implement View vào .NET 6.
+ Bước 1 : Tạo 1 View dưới SQL
CREATE VIEW [dbo].[MemberView] AS
SELECT
m.Id AS Id,
m.AvatarUrl AS AvatarUrl,
m.Email AS Email,
m.FirstName AS FirstName,
m.IdentityCardNumber AS IdentityCardNumber,
m.Job AS Job,
m.LastName AS LastName,
m.PhoneNo AS PhoneNo,
m.UrlIdentityCardBackImage AS UrlIdentityCardBackImage,
m.UrlIdentityCardFrontImage AS UrlIdentityCardFrontImage,
m.UserName AS UserName,
m.CreatedOn AS CreatedOn,
m.MemberType AS MemberType,
m.IsRequested AS IsRequested,
m.QRCodeDriver AS QRCodeDriver,
m.TotalPoint AS TotalPoint,
u.IsActive AS IsActive,
m.ReferralCode AS IntroduceCode,
m.TotalInvited AS TotalInvite,
m.BalanceInvited AS BalanceInvited,
r.Name AS Roles,
laa.LicenseAdsAgentCode AS LicenseAdsAgent,
m.DateOfBirth
FROM
Member AS m WITH(NOLOCK)
INNER JOIN [Identity].Users AS u WITH(NOLOCK) ON m.UserId = u.Id
INNER JOIN [Identity].UserRoles AS ur WITH(NOLOCK) ON u.Id = ur.UserId
INNER JOIN [Identity].Roles AS r WITH(NOLOCK) ON ur.RoleId = r.Id AND r.Name NOT LIKE 'Superadmin%'
LEFT JOIN MemberVip AS mv WITH(NOLOCK) ON m.Id = mv.MemberId AND mv.IsDeleted = 0
LEFT JOIN
(SELECT DISTINCT MemberId, Status, IsDeleted, LastModifiedOn
FROM AffiliateRegister WITH(NOLOCK) )
AS affiliate
ON m.Id = affiliate.MemberId AND affiliate.Status = 2 AND affiliate.IsDeleted = 0
LEFT JOIN LicenseAdsAgent AS laa WITH(NOLOCK) ON LOWER(laa.UserName) = LOWER(m.UserName) AND laa.IsDeleted = 0 AND laa.Status = 2;
GO
Câu lệnh SQL trên là khởi tạo 1 View từ bảng MEMBER và join với các bảng phía dưới, và có sử dụng WITH(NOLOCK).
- WITH(NOLOCK) là gì? Và sử dụng nó có ý nghĩa gì?
Bạn đã từng gặp tình trạng bị Deadlock bao
giờ chưa? Cùng mình tìm hiểu nhé.
Deadlock được hiểu là sự xung đột giữa các câu lệnh trong cùng
thời điểm đọc hay ghi dữ liệu. Nó làm thay đổi tính nhất quán trong các tài
nguyên của database khiến cho các lệnh còn lại không có tài nguyên để sử dụng.
Sự xáo trộn này khiến cho các câu lệnh không thể kết thúc để giải phóng tài
nguyên. Khi đó, tiến trình đọc/ghi dữ liệu sẽ bị lỗi, không thể hoàn thành.
Để các bạn có thể hình dung dễ hơn, chúng tôi
giả sử: Transaction T1 đang cần tài nguyên để thực hiện lock
một số hàng trong bảng student. Nhưng transaction 2 lại nắm giữ các tài nguyên
đó. Khi đó, sẽ xảy ra các trường hợp:
- Transaction
1 phải dừng lại để chờ transaction 2 giải phóng tài
nguyên.
- Transaction
2 cũng dừng lại để chờ transaction 1 giải phóng tài
nguyên.
Tất cả điều này khiến cho hoạt động của database
đều dừng lại. Nó chỉ có thể được xử lý khi deadlock được phát hiện. Và một
trong 2 transaction trên sẽ bị hủy bỏ. Một quá trình hoạt động thông thường khi
các tài nguyên được sử dụng như sau: Yêu cầu >> Sử dụng >>
Giải phóng.
- Giảm
bớt số lượng bảng cần sử dụng trong mỗi câu lệnh. Số bảng tham gia càng ít
thì deadlock càng thấp. Tài nguyên sẽ được giải phóng một cách nhịp nhàng.
- Sử
dụng các câu lệnh dạng batch để tạo ra một script duy nhất thay vì gọi rời
rạc trong database để thực thi câu lệnh.
- Kiểm
soát và điều chỉnh thứ tự tác động các bảng giống nhau trong quá trình
thực thi câu lệnh để chỉ xảy ra lock, chứ không phải deadlock.
- Giảm
thời gian round trip qua đường mạng để giải phóng tài nguyên nhanh hơn.
Kết thúc giao dịch sớm hơn.
- Gửi Script dạng batch là cách giúp sap server phân tích được toàn bộ mã Script. Từ đó, tạo ra một execution plan hiệu quả hơn, tránh được hiện tượng deadlock tốt hơn.
Đó là những thông tin về Deadlock, mình sẽ tiếp tục với ví dụ nhé :
+ Bước 2 : Tạo file có tên MemberView trong Entities, nó giống như 1 DTOs (Data transfer object) bao gồm những property mình đã lấy ra dưới View và thêm property khác nếu cần:
namespace Domain.Entities.DatTable.Member { public class MemberView { public long Id { get; set; } = default!; public string UserName { get; set; } = default!; public string Email { get; set; } = default!; public string? FirstName { get; set; } public string? LastName { get; set; } public DateTime? DateOfBirth { get; set; } public string? PhoneNo { get; set; } public string? IdentityCardNumber { get; set; } public string? UrlIdentityCardFrontImage { get; set; } public string? UrlIdentityCardFrontImageLink { get; set; } public string? UrlIdentityCardBackImage { get; set; } public string? UrlIdentityCardBackImageLink { get; set; } public string? AvatarUrl { get; set; } public string? AvatarUrlLink { get; set; } public byte? Gender { get; set; } public string? Job { get; set; } public DateTime? CreatedOn { get; set; } public short MemberType { get; set; } public bool? IsRequested { get; set; } public bool IsActive { get; set; } public string? QRCodeDriver { get; set; } public int? TotalPoint { get; set; } public string IntroduceCode { get; set; } = default!; public int? TotalInvite { get; set; } public int? BalanceInvited { get; set; } public bool IsSendMail { get; set; } public short Status { get; set; } public string Roles { get; set; } = default!; public string? OrganizationName { get; set; } public string? OrganizationAddress { get; set; } public string? OrganizationPhone { get; set; } public string? OrganizationEmail { get; set; } public string? LicenseAdsAgent { get; set; } } }
+ Bước 3: Tiếp đến thì chúng ta vẫn đăng kí DB set trong DbContext như 1 Entities trong table:
using Domain.Entities.DatTable.Member;
using Microsoft.EntityFrameworkCore;
namespace Infrastructure.Contexts
{
public class ApplicationDbContext : AudtableContext
{
public ApplicationDbContext(DbContextOptions options) : base(options)
{
}
public virtual DbSet MemberViews { get; set; } = default!;
}
}
+ Bước 4 : Trong Repository ta sử dụng DbContext rồi gọi View đó ra rồi sử dụng :
/// <summary>
/// Get Member
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public IQueryable GetMember(GetAllMemberQuery request)
{
IQueryable query;
DateTime? endDate = request.EndDate.HasValue ? new DateTime(request.EndDate.Value.Year, request.EndDate.Value.Month, request.EndDate.Value.Day, 23, 59, 59) : null;
try
{
query = from m in _dbContext.MemberViews
where (string.IsNullOrWhiteSpace(request.Keyword) || (m.FirstName + " " + m.LastName).ToLower().Contains(request.Keyword.ToLower())
|| m.Email.ToLower().Contains(request.Keyword.ToLower())
|| m.UserName.ToLower().Contains(request.Keyword.ToLower())
|| (!string.IsNullOrWhiteSpace(m.PhoneNo) && m.PhoneNo.Contains(request.Keyword))
|| (!string.IsNullOrWhiteSpace(m.IdentityCardNumber) && m.IdentityCardNumber.Contains(request.Keyword))
|| (!string.IsNullOrWhiteSpace(m.Job) && m.Job.ToLower().Contains(request.Keyword.ToLower()))
|| (!string.IsNullOrWhiteSpace(m.IntroduceCode) && m.IntroduceCode.ToLower().Contains(request.Keyword.ToLower())))
&& (!request.Gender.HasValue || m.Gender == request.Gender)
&& (!request.MemberType.HasValue || m.MemberType == request.MemberType)
&& (!request.DateOfBirth.HasValue || (m.DateOfBirth.HasValue && m.DateOfBirth.Value.Date.Equals(request.DateOfBirth)))
&& (request.RoleName == null || request.RoleName.Any(x => x.Equals(m.Roles)))
&& (!request.FromDate.HasValue || m.CreatedOn >= request.FromDate.Value)
&& (!request.EndDate.HasValue || m.CreatedOn <= endDate)
select new GetAllMemberResponse()
{
Id = m.Id,
AvatarUrl = m.AvatarUrl,
Email = m.Email,
FirstName = m.FirstName,
IdentityCardNumber = m.IdentityCardNumber,
Job = m.Job,
LastName = m.LastName,
PhoneNo = m.PhoneNo,
UrlIdentityCardBackImage = m.UrlIdentityCardBackImage,
UrlIdentityCardFrontImage = m.UrlIdentityCardFrontImage,
UserName = m.UserName,
CreatedOn = m.CreatedOn,
MemberType = m.MemberType,
IsRequested = m.IsRequested,
QRCodeDriver = m.QRCodeDriver,
TotalPoint = m.TotalPoint,
IsActive = m.IsActive,
IntroduceCode = m.IntroduceCode,
TotalInvite = m.TotalInvite,
BalanceInvited = m.BalanceInvited,
Roles = new List { m.Roles },
LicenseAdsAgent = m.LicenseAdsAgent,
PromoteDate = m.PromoteDate
};
}
catch (Exception)
{
throw new ApiException(ValidatorMessage.SYS008I);
}
return query;
}
+ Bước 5: Ở tầng Handle thì gọi lại Query từ Repository để dùng and phân trang hoặc thêm các property khác nếu cần:
using Application.Interfaces.Aws;
using Application.Interfaces.Repositories.DatTable;
using Domain.Wrappers;
using MediatR;
using Microsoft.EntityFrameworkCore;
using System.Linq.Dynamic.Core;
namespace Application.Features.DatTable.Member.Queries.GetAllMember
{
public class GetAllMemberQuery : GetAllMemberParameter, IRequest>
{
}
internal class GetAllMemberQueryHandler : IRequestHandler>
{
private readonly IMemberRepository _memberRepository;
private readonly IAwsUploadService _awsUploadService;
public GetAllMemberQueryHandler(IMemberRepository memberRepository, IAwsUploadService awsUploadService)
{
_memberRepository = memberRepository;
_awsUploadService = awsUploadService;
}
public async Task> Handle(GetAllMemberQuery request, CancellationToken cancellationToken)
{
var query = _memberRepository.GetMemberIsUser(request);
var data = query.OrderBy(request.Order);
var totalRecord = data.Count();
var result = !request.IsExport ? await data.Skip((request.PageNumber - 1) * request.PageSize).Take(request.PageSize).ToListAsync(cancellationToken) : await data.ToListAsync(cancellationToken);
if (result.Any())
{
result = result.Select(p =>
{
p.AvatarUrlLink = !string.IsNullOrEmpty(p.AvatarUrl)
? _awsUploadService.GeneratePreSignedUrl(p.AvatarUrl!, false)
: "";
return p;
}).ToList();
}
return PaginatedResult.Success(result, totalRecord, request.PageNumber, request.PageSize);
}
}
}
Đoạn code trên mình dùng View từ Repository để phân trang cho API.
+ Bước 6: Ở tầng Controller :
/// <summary>
/// Get All Member
/// </summary>
/// <param name="parameter"></param>
/// <returns></returns>
[HttpGet]
[Authorize(Policy = Permissions.Members.View)]
public async Task>> GetAllMember([FromQuery] GetAllMemberParameter parameter)
{
return Ok(await _mediator.Send(new GetAllMemberQuery
{
IsExport = parameter.IsExport,
SortBy = parameter.SortBy,
PageNumber = parameter.PageNumber,
PageSize = parameter.PageSize,
Order = parameter.Order,
DateOfBirth = parameter.DateOfBirth,
Gender = parameter.Gender,
Keyword = parameter.Keyword,
MemberType = parameter.MemberType,
RoleName = parameter.RoleName,
}));
}
Lưu ý:Và lưu ý khi sử dụng Transaction:
Trong C# thì mình cũng có thể dùng :- Khi Table nào có nhiều record, khi truy xuất dữ liệu thì dùng
ConfigureAwait(false)
Ex: var otp = await _otpRepository.Entities.Where(x => x.Email.Equals(request.Email) && x.UserName.Equals(request.UserName) && !x.IsDeleted).FirstOrDefaultAsync().ConfigureAwait(false);- Khi sử dụng
ConfigureAwait(false), bạn thông báo hệ thống rằng không cần thiết phải trở về luồng gốc (UI thread trong ứng dụng đồ họa) sau khi mộtTaskhoàn thành. Điều này có thể giúp tăng hiệu suất và giảm khả năng xảy ra deadlock trong một số tình huống.
- Khi sử dụng transaction trong SQL, chúng ta cần hiểu được giá trị và không nên lạm dụng nó. Với mỗi giao dịch được thực hiện cần đánh giá tổng thể xem các câu lệnh/giao dịch có xung đột nhau hay không.
- Sử dụng transaction tràn lan là nguyên nhân dẫn tới deadlock nhiều hơn. Tốt nhất, bạn nên tránh sử dụng transaction tối đa. Transaction càng ngắn thì càng mang lại hiệu quả cao hơn.
Oke rồi nhoé !!
kĩ quá bạn kkk
Trả lờiXóaTuyệt dời kkk
Trả lờiXóa