Paging with LinQ to SQL
Posted On Friday, January 16, 2009 at at 5:48 PM by Unknown- Nếu ai đã từng làm chức năng phân trang cho một website thì đều biết rằng có nhiều phong cách để thực hiện, nhưng ý tưởng thì tương đối giống nhau. Bạn cần lấy đúng số lượng record cần hiển thị từ database để thể hiện dữ liệu lên màn hình. Sẽ có một control để hiển thị các số thứ tự trang, vị trí trang hiện tại, các quick button để đi tới trang kế tiếp, trang trước đó, v.v. Vì vậy ngoài việc lấy đúng số lượng record mong muốn, ta cần lấy được tổng số record trong database để có thể tính được tổng số trang cho control pagging.
- Để thực hiện các trang report tương đối phức tạp, người ta thường sử dụng Stored Procedure. Khi kết hợp với function ROW_NUMBER() của SQL 2005, người ta có thể dễ dàng viết câu query để lấy đúng phần dữ liệu mong muốn. Trong LinQ2SQL, việc gọi 1 Stored Procedure khá dễ dàng. Kết quả sẽ được map vào một DTO (Data transfer object) cho chúng ta. Code của tất cả các thành phần cần thiết như các lớp data context, lớp của các lớp DTO cũng như file xml mapping có thể được sinh ra dễ dàng chỉ với một thao tác drag and drop. Trong bài viết này, tôi xin giới thiệu một tính năng khác khá hay của LinQ2SQL, chúng ta sẽ sử dụng tính năng này để vừa tính tổng số lượng record đồng thời lấy phần dữ liệu cho một trang mà chỉ cần một lần gọi Stored Procedure.
- Khi chúng ta viết một Stored Procedure như ở code số 1, rồi execute nó thì kết quả nhận được sẽ gồm hai phần, phần thứ nhất là số lượng record ứng với câu query đầu tiên trong Stored Procedure, và phần còn lại là các record thoả điều kiện.
CREATE PROCEDURE [dbo].[GetCustomers]
@FromIndex INT,
@NumberOfRecord INT
AS
BEGIN
SELECT COUNT(*) as TotalCustomer FROM [Customers]
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY U.CustomerID) AS RowNumber, *
FROM [Customers] U ) AS Customer
WHERE RowNumber > @FromIndex AND RowNumber <= @FromIndex + @NumberOfRecord
END
@FromIndex INT,
@NumberOfRecord INT
AS
BEGIN
SELECT COUNT(*) as TotalCustomer FROM [Customers]
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY U.CustomerID) AS RowNumber, *
FROM [Customers] U ) AS Customer
WHERE RowNumber > @FromIndex AND RowNumber <= @FromIndex + @NumberOfRecord
END
Code 1: Câu Stored Procedure để lấy dữ liệu phân trang
- Tui xin không đi sâu giải thích các query của Stored Procedure trong code 1, chỉ lưu ý là khi Stored Procedure này được gọi thì sẽ có 2 kết quả trả về như đã nói ở trên. LinQ2SQL cho phép ta nhận được cả hai kết quả này bằng cách sử dụng IMultipleResults. Tất cả các thứ ta cần để gọi một Stored Procedure bằng LinQ2SQL là một file xml mapping, các lớp C# mà ta hay gọi là DTO để map dữ liệu từ Stored Procedure, và một lớp DataContext. Tất cả những thứ này đều có thể được sinh ra tự động bằng cách drag and drop các database object vào màn hình designer của LinQ2SQL Classes. Xem hình 1:
Hình 1: Màn hình designer của LinQ2SQL Classes
- Tuy các thứ ta cần có thể được sinh ra tự động từ việc drag and drop, nhưng thường thì ta phải sửa lại một số chỗ để được kết quả cuối cùng. Để generate được code gọi Stored Procedure bằng LinQ2SQL, ta chỉ việc kéo và thả Stored Procedure mong muốn vào màn hình designer. Sau đó, ta xem file dbml bằng một XML Viewer sẽ thấy được nội dung xml mapping. Xem code C# của file dbml này ta sẽ thấy được code mình muốn, ta sẽ phải sửa lại code này để có thể nhận cả 2 kết quả trả về của Stored Procedure mà mình thiết kế. Tất cả code C# cho lớp DataContext, cho các lớp DTO đều được sinh ra bên trong một file C# của LinQ2SQL Classes. Chúng ta sẽ copy những nội dung mong muốn và tạo thành các lớp riêng như ý mình. Các code dưới đây là nội dung của file mapping và các lớp DTO.
<?xml version="1.0" encoding="utf-8"?>
<Database Name="NORTHWND" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Function Name="dbo.GetCustomers" Method="GetCustomers">
</Function>
</Database>
<Database Name="NORTHWND" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Function Name="dbo.GetCustomers" Method="GetCustomers">
<Parameter Name="FromIndex" Parameter="fromIndex" DbType="Int" />
<Parameter Name="NumberOfRecord" Parameter="numberOfRecord" DbType="Int" />
<ElementType Name="nthoai.blogspot.com.PaggingLTS.Entity.CustomerCount">
</ElementType>
<ElementType Name="nthoai.blogspot.com.PaggingLTS.Entity.Customer">
</ElementType>
<Parameter Name="NumberOfRecord" Parameter="numberOfRecord" DbType="Int" />
<ElementType Name="nthoai.blogspot.com.PaggingLTS.Entity.CustomerCount">
<Column Name="TotalCustomer" Member="TotalCustomer" Storage="_TotalCustomer" DbType="Int" CanBeNull="true" />
</ElementType>
<ElementType Name="nthoai.blogspot.com.PaggingLTS.Entity.Customer">
<Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5) NOT NULL" CanBeNull="false" />
<Column Name="CompanyName" Member="CompanyName" Storage="_CompanyName" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Member="ContactName" Storage="_ContactName" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="ContactTitle" Member="ContactTitle" Storage="_ContactTitle" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="Address" Member="Address" Storage="_Address" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="City" Member="City" Storage="_City" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Region" Member="Region" Storage="_Region" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="PostalCode" Member="PostalCode" Storage="_PostalCode" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="Country" Member="Country" Storage="_Country" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Phone" Member="Phone" Storage="_Phone" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="Fax" Member="Fax" Storage="_Fax" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="CompanyName" Member="CompanyName" Storage="_CompanyName" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Member="ContactName" Storage="_ContactName" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="ContactTitle" Member="ContactTitle" Storage="_ContactTitle" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="Address" Member="Address" Storage="_Address" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="City" Member="City" Storage="_City" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Region" Member="Region" Storage="_Region" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="PostalCode" Member="PostalCode" Storage="_PostalCode" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="Country" Member="Country" Storage="_Country" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Phone" Member="Phone" Storage="_Phone" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="Fax" Member="Fax" Storage="_Fax" DbType="NVarChar(24)" CanBeNull="true" />
</ElementType>
</Function>
</Database>
Code 2: Nội dung file mapping đã được sửa đổi
public class Customer
{
public Customer()
{ }
public string _CustomerID;
public string CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public string _CompanyName;
public string CompanyName
{
get { return _CompanyName; }
set { _CompanyName = value; }
}
public string _ContactName;
public string ContactName
{
get { return _ContactName; }
set { _ContactName = value; }
}
public string _ContactTitle;
public string ContactTitle
{
get { return _ContactTitle; }
set { _ContactTitle = value; }
}
public string _Address;
public string Address
{
get { return _Address; }
set { _Address = value; }
}
public string _City;
public string City
{
get { return _City; }
set { _City = value; }
}
public string _Region;
public string Region
{
get { return _Region; }
set { _Region = value; }
}
public string _PostalCode;
public string PostalCode
{
get { return _PostalCode; }
set { _PostalCode = value; }
}
public string _Country;
public string Country
{
get { return _Country; }
set { _Country = value; }
}
public string _Phone;
public string Phone
{
get { return _Phone; }
set { _Phone = value; }
}
public string _Fax;
public string Fax
{
get { return _Fax; }
set { _Fax = value; }
}
}
public class CustomerCount
{
public CustomerCount()
{
}
public System.Nullable<int> _TotalCustomer;
public System.Nullable<int> TotalCustomer
{
get
{
return this._TotalCustomer;
}
set
{
if (this._TotalCustomer != value)
{
this._TotalCustomer = value;
}
}
}
}
{
public Customer()
{ }
public string _CustomerID;
public string CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
public string _CompanyName;
public string CompanyName
{
get { return _CompanyName; }
set { _CompanyName = value; }
}
public string _ContactName;
public string ContactName
{
get { return _ContactName; }
set { _ContactName = value; }
}
public string _ContactTitle;
public string ContactTitle
{
get { return _ContactTitle; }
set { _ContactTitle = value; }
}
public string _Address;
public string Address
{
get { return _Address; }
set { _Address = value; }
}
public string _City;
public string City
{
get { return _City; }
set { _City = value; }
}
public string _Region;
public string Region
{
get { return _Region; }
set { _Region = value; }
}
public string _PostalCode;
public string PostalCode
{
get { return _PostalCode; }
set { _PostalCode = value; }
}
public string _Country;
public string Country
{
get { return _Country; }
set { _Country = value; }
}
public string _Phone;
public string Phone
{
get { return _Phone; }
set { _Phone = value; }
}
public string _Fax;
public string Fax
{
get { return _Fax; }
set { _Fax = value; }
}
}
public class CustomerCount
{
public CustomerCount()
{
}
public System.Nullable<int> _TotalCustomer;
public System.Nullable<int> TotalCustomer
{
get
{
return this._TotalCustomer;
}
set
{
if (this._TotalCustomer != value)
{
this._TotalCustomer = value;
}
}
}
}
Code 3: Nội dung các file DTO C# để chứa dữ liệu được map từ database
- Tương tự bài trước, ta sẽ tạo ra một lớp DataContext riêng của mình với method gọi Stored Procedure đã được sửa đổi để có thể nhận được cả hai kết quả khi gọi Stored Procedure. Chỗ tôi sửa đổi ở đây chính là thay ISingleResult thành IMultipleResults. Có một điểm lạ là code sinh ra lúc nào cũng là ISingleResult cho dù Stored Procedure có trả về bao nhiêu tập dữ liệu đi nữa, nêu bắt buộc ta phải tự sửa lại ở bước này.
public class NorthwindDataContext : System.Data.Linq.DataContext
{
public static string mappingFile = @"bin\Mapping.xml";
public static string connectionString = ConfigurationManager.ConnectionStrings["PaggingLTSDemo"].ToString();
public static string mappingPath = PathHelper.ResolePath(mappingFile);
static XmlMappingSource map = XmlMappingSource.FromXml(File.ReadAllText(mappingPath));
/// <summary>
/// Simple Constructor
/// </summary>
public NorthwindDataContext()
:base (connectionString, map)
{
}
/// <summary>
/// Create a LINQ to SQL data context
/// </summary>
/// <param name="connection"></param>
public NorthwindDataContext(string connection)
:base (connection, map)
{
}
[Function(Name = "dbo.GetBannerClickReportDetail")]
[ResultType( typeof(CustomerCount))]
[ResultType( typeof(Customer))]
public IMultipleResults GetCustomers([Parameter(Name = "FromIndex", DbType = "Int")] System.Nullable<int> fromIndex, [Parameter(Name = "NumberOfRecord", DbType = "Int")] System.Nullable<int> numberOfRecord)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), fromIndex, numberOfRecord);
return (IMultipleResults)result.ReturnValue;
}
}
{
public static string mappingFile = @"bin\Mapping.xml";
public static string connectionString = ConfigurationManager.ConnectionStrings["PaggingLTSDemo"].ToString();
public static string mappingPath = PathHelper.ResolePath(mappingFile);
static XmlMappingSource map = XmlMappingSource.FromXml(File.ReadAllText(mappingPath));
/// <summary>
/// Simple Constructor
/// </summary>
public NorthwindDataContext()
:base (connectionString, map)
{
}
/// <summary>
/// Create a LINQ to SQL data context
/// </summary>
/// <param name="connection"></param>
public NorthwindDataContext(string connection)
:base (connection, map)
{
}
[Function(Name = "dbo.GetBannerClickReportDetail")]
[ResultType( typeof(CustomerCount))]
[ResultType( typeof(Customer))]
public IMultipleResults GetCustomers([Parameter(Name = "FromIndex", DbType = "Int")] System.Nullable<int> fromIndex, [Parameter(Name = "NumberOfRecord", DbType = "Int")] System.Nullable<int> numberOfRecord)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), fromIndex, numberOfRecord);
return (IMultipleResults)result.ReturnValue;
}
}
Code 4: Lớp DataContext
- Chúng ta sẽ làm một lớp DAO để sử dụng, khi gọi hàm chúng ta sẽ nhận được một list các DTO và output tổng số lượng record để ta có thể tính toán số trang
public List<Customer> GetCustomers(int fromIndex, int numberOfRow, out int itemCount)
{
List<Customer> resultList = new List<Customer>();
itemCount = 0;
using (NorthwindDataContext db = new NorthwindDataContext())
{
IMultipleResults result = db.GetCustomers( fromIndex, numberOfRow);
itemCount = result.GetResult<CustomerCount>().First<CustomerCount>().TotalCustomer.Value;
resultList = result.GetResult<Customer>().ToList<Customer>();
}
return resultList;
}
{
List<Customer> resultList = new List<Customer>();
itemCount = 0;
using (NorthwindDataContext db = new NorthwindDataContext())
{
IMultipleResults result = db.GetCustomers( fromIndex, numberOfRow);
itemCount = result.GetResult<CustomerCount>().First<CustomerCount>().TotalCustomer.Value;
resultList = result.GetResult<Customer>().ToList<Customer>();
}
return resultList;
}
Code 5: Method GetCustomers trong Data Access Object
- Bài viết này sử dụng database NorthWind. Các bạn có thể download file mdf cho NorthWind trên trang của Microsoft ở đây:
Nếu máy bạn đã cài SQL Express thì có thể chạy code sample mà không cần download vì tui đã attach sẵn Database này trong source code.- Cuối cùng, code sample cho bài viết có thể được download ở đây.
Kết luận
Bài viết này giới thiệu một cách phân trang cơ bản và đơn giản với LinQ. Thực hiện theo cách này chúng ta sẽ phải sử dụng Stored Procedure và chỉ cần gọi Stored Procedure này một lần. Trong bài viết tôi không trực tiếp sử dụng các code sinh ra từ VS.NET 2008 mà lại copy và paste chúng thành các file C# ứng với các layer độc lập. Tuy code được sinh tự động nhưng chắc chắn các bạn phải nhúng tay vào sửa lại để map theo ý muốn. Tuy phải động tay động chân nhưng khi đã quen thì sẽ không mất nhiều thời gian. Đặc biệt khi file xml mapping có lỗi thì tất nhiên sẽ không connect và map dữ liệu từ database được, nên thường thì ta nên có 1 unit test project để test các lớp mapping.
