C#中一次 SQL 請求返回分頁數據和總條數
在開發過程中，經常需要處理分頁數據和總條數的獲取。傳統的做法是通過執行兩次SQL請求來實現：一次用于分頁數據，另一次用于獲取總條數。然而，這種方式會增加客戶端與服務器之間的網絡往返次數，影響性能。本文將探討如何在C#中通過一次SQL請求同時獲取分頁數據和總條數，并給出具體示例代碼。
背景知識
在MySQL中，client_multi_statements選項允許在一個SQL請求中執行多條語句。然而，出于安全考慮，該選項默認設置為false，以防止SQL注入等安全風險。盡管如此，我們可以通過其他方法，如存儲過程或臨時表，來實現在一次請求中獲取分頁數據和總條數的目的。
在C#中，我們可以使用ADO.NET來執行SQL語句，包括調用存儲過程。
解決方案
1.使用存儲過程
在數據庫中創建一個存儲過程，該存儲過程接受分頁參數（如頁碼和每頁顯示的記錄數），然后返回分頁數據和總條數。這通常通過兩個輸出參數（或結果集）實現：一個用于分頁數據，另一個用于總條數。
2.示例
假設我們有一個dict_plugin表，我們需要從中獲取分頁數據和總條數。
(1) 創建存儲過程
在MySQL數據庫中，可以創建一個類似以下的存儲過程：
DELIMITER $$
CREATE PROCEDURE `GetDictPluginPaged`(
IN pageSize INT,
IN pageIndex INT,
OUT totalCount INT
)
BEGIN
SELECT COUNT(*) INTO totalCount FROM `dict_plugin`;
SET @offset = (pageIndex - 1) * pageSize;
SELECT * FROM `dict_plugin`
LIMIT pageSize OFFSET @offset;
END$$
DELIMITER ;注意：這里為了簡單起見，將總條數和分頁數據作為兩個獨立的查詢來執行。實際應用中，可以通過其他方式優化（如使用臨時表或變量存儲中間結果）。
(2) C#中調用存儲過程
在C#中，我們可以使用SqlCommand對象來調用這個存儲過程，并處理返回的結果。
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = "你的數據庫連接字符串";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("GetDictPluginPaged", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pageSize", 10); // 每頁10條
command.Parameters.AddWithValue("@pageIndex", 1); // 第一頁
SqlParameter totalCountParam = new SqlParameter
{
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
};
command.Parameters.Add(totalCountParam);
using (SqlDataReader reader = command.ExecuteReader())
{
// 首先讀取分頁數據
while (reader.Read())
{
// 假設表中有id和name字段
Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}");
}
// 獲取總條數
int totalCount = (int)totalCountParam.Value;
Console.WriteLine($"Total Count: {totalCount}");
}
}
}
}注意：由于SQL Server和MySQL在存儲過程和參數處理上有所不同，上面的示例是基于SQL Server的。如果你的數據庫是MySQL，你需要使用MySql.Data包中的MySqlConnection和MySqlCommand類，并相應地調整連接字符串。
總結
通過存儲過程，我們可以在一次數據庫調用中同時獲取分頁數據和總條數，從而減少網絡往返次數，提高應用性能。此外，存儲過程還可以提高數據庫操作的安全性和可維護性。盡管直接在SQL請求中執行多條語句（client_multi_statements=true）可以實現類似的功能，但出于安全考慮，通常不建議這么做。相反，使用存儲過程是一個更安全、更可控的選擇。
