-- 創(chuàng)建用戶(hù)表
CREATE TABLE [dbo].[Users] (
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserName] NVARCHAR(50) NOT NULL,
    [Age] INT NOT NULL,
    [Email] NVARCHAR(100) NULL,
    [CreateTime] DATETIME NOT NULL DEFAULT GETDATE()
)

// 實(shí)體類(lèi)（無(wú)侵入，無(wú)需繼承任何接口、標(biāo)注特性）
public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }
    public string Email { get; set; }
    public DateTime CreateTime { get; set; }
}

using System.Data;
using System.Data.SqlClient;
using Dapper;
// 1. 連接字符串（建議放在配置文件中，此處簡(jiǎn)化）
string connectionString = "Data Source=.;Initial Catalog=DapperDemo;User ID=sa;Password=123456;";
// 2. 使用using語(yǔ)句創(chuàng)建連接（自動(dòng)打開(kāi)、關(guān)閉連接）
using (IDbConnection conn = new SqlConnection(connectionString))
{
    // 打開(kāi)連接（Dapper的部分方法會(huì)自動(dòng)打開(kāi)連接，建議手動(dòng)打開(kāi)，提升性能）
    conn.Open();
    // 執(zhí)行SQL操作（后續(xù)講解）
}

// 準(zhǔn)備新增數(shù)據(jù)
var newUser = new User
{
    UserName = "張三",
    Age = 25,
    Email = "zhangsan@163.com",
    CreateTime = DateTime.Now
};
// 插入SQL（參數(shù)化，@UserName對(duì)應(yīng)實(shí)體類(lèi)的屬性名）
string insertSql = @"INSERT INTO Users (UserName, Age, Email, CreateTime)
                    VALUES (@UserName, @Age, @Email, @CreateTime);
                    SELECT SCOPE_IDENTITY();"; // 返回自增ID
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 執(zhí)行插入，返回自增ID（Convert.ToInt32轉(zhuǎn)換，適配SQL Server）
    int newUserId = conn.ExecuteScalar<int>(insertSql, newUser);
    Console.WriteLine($"新增用戶(hù)成功，ID：{newUserId}");
}

// 示例1：查詢(xún)所有用戶(hù)（返回List<User>）
string selectAllSql = "SELECT * FROM Users";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // Query<T>返回IEnumerable<T>，可直接轉(zhuǎn)換為L(zhǎng)ist
    List<User> userList = conn.Query<User>(selectAllSql).ToList();
    foreach (var user in userList)
    {
        Console.WriteLine($"ID：{user.Id}，姓名：{user.UserName}");
    }
}
// 示例2：根據(jù)ID查詢(xún)單個(gè)用戶(hù)（查詢(xún)不到返回null）
int userId = 1;
string selectOneSql = "SELECT * FROM Users WHERE Id = @Id";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // QueryFirstOrDefault<T>：查詢(xún)第一個(gè)結(jié)果，無(wú)結(jié)果返回null
    User user = conn.QueryFirstOrDefault<User>(selectOneSql, new { Id = userId });
    if (user != null)
    {
        Console.WriteLine($"查詢(xún)到用戶(hù)：{user.UserName}");
    }
    else
    {
        Console.WriteLine("未查詢(xún)到用戶(hù)");
    }
}
// 示例3：條件查詢(xún)（多參數(shù)）
int minAge = 20;
string emailLike = "%163.com%";
string selectConditionSql = "SELECT * FROM Users WHERE Age >= @MinAge AND Email LIKE @EmailLike";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 多參數(shù)：使用匿名對(duì)象傳遞
    var users = conn.Query<User>(selectConditionSql, new { MinAge = minAge, EmailLike = emailLike }).ToList();
}

// 準(zhǔn)備修改數(shù)據(jù)
int updateId = 1;
var updateUser = new User
{
    UserName = "張三_修改",
    Age = 26,
    Email = "zhangsan_update@163.com"
};
string updateSql = @"UPDATE Users 
                    SET UserName = @UserName, Age = @Age, Email = @Email
                    WHERE Id = @Id";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 傳遞多個(gè)參數(shù)（匿名對(duì)象）
    int affectedRows = conn.Execute(updateSql, new { updateUser.UserName, updateUser.Age, updateUser.Email, Id = updateId });
    Console.WriteLine($"修改成功，受影響行數(shù)：{affectedRows}");
}

int deleteId = 1;
string deleteSql = "DELETE FROM Users WHERE Id = @Id";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    int affectedRows = conn.Execute(deleteSql, new { Id = deleteId });
    Console.WriteLine($"刪除成功，受影響行數(shù)：{affectedRows}");
}

// 適用于參數(shù)較少的場(chǎng)景
string sql = "SELECT * FROM Users WHERE Age >= @MinAge AND UserName LIKE @UserName";
var users = conn.Query<User>(sql, new { MinAge = 20, UserName = "%張%" }).ToList();

// 實(shí)體類(lèi)的屬性名與SQL中的@參數(shù)名一致即可
var param = new User { Age = 20, UserName = "張三" };
string sql = "SELECT * FROM Users WHERE Age = @Age AND UserName = @UserName";
var user = conn.QueryFirstOrDefault<User>(sql, param);

var param = new DynamicParameters();
param.Add("@MinAge", 20); // 添加參數(shù)
param.Add("@MaxAge", 30);
param.Add("@UserName", "%張%", DbType.String, ParameterDirection.Input, 50); // 指定參數(shù)類(lèi)型、方向、長(zhǎng)度
string sql = "SELECT * FROM Users WHERE Age BETWEEN @MinAge AND @MaxAge AND UserName LIKE @UserName";
var users = conn.Query<User>(sql, param).ToList();

// 一次性執(zhí)行兩個(gè)SQL語(yǔ)句，獲取兩個(gè)結(jié)果集
string sql = @"SELECT * FROM Users WHERE Id = @Id;
              SELECT * FROM Orders WHERE UserId = @Id;";
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 執(zhí)行多結(jié)果集查詢(xún)
    using (var multi = conn.QueryMultiple(sql, new { Id = 1 }))
    {
        // 讀取第一個(gè)結(jié)果集（用戶(hù)）
        User user = multi.Read<User>().FirstOrDefault();
        // 讀取第二個(gè)結(jié)果集（該用戶(hù)的訂單）
        List<Order> orders = multi.Read<Order>().ToList();
        // 關(guān)聯(lián)數(shù)據(jù)
        if (user != null)
        {
            user.Orders = orders;
        }
    }
}

-- 聯(lián)表查詢(xún)：用戶(hù)表 + 角色表
SELECT u.Id, u.UserName, u.Age, r.RoleName
FROM Users u
LEFT JOIN Roles r ON u.RoleId = r.Id
WHERE u.Id = @Id

// 定義包含聯(lián)表字段的實(shí)體類(lèi)（可新增DTO）
public class UserWithRole
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }
    public string RoleName { get; set; } // 角色表的字段
}
// 自動(dòng)映射聯(lián)表結(jié)果
string sql = @"SELECT u.Id, u.UserName, u.Age, r.RoleName
              FROM Users u
              LEFT JOIN Roles r ON u.RoleId = r.Id
              WHERE u.Id = @Id";
UserWithRole user = conn.QueryFirstOrDefault<UserWithRole>(sql, new { Id = 1 });

// 實(shí)體類(lèi)（用戶(hù)包含角色列表）
public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public List<Role> Roles { get; set; } = new List<Role>();
}
public class Role
{
    public int Id { get; set; }
    public string RoleName { get; set; }
}
// 聯(lián)表查詢(xún)SQL
string sql = @"SELECT u.Id, u.UserName, r.Id AS RoleId, r.RoleName
              FROM Users u
              LEFT JOIN UserRoles ur ON u.Id = ur.UserId
              LEFT JOIN Roles r ON ur.RoleId = r.Id
              WHERE u.Id = @Id";
// 手動(dòng)映射：將查詢(xún)結(jié)果映射到User和Role，關(guān)聯(lián)角色列表
var users = conn.Query<User, Role, User>(
    sql,
    (user, role) => // 映射委托：第一個(gè)參數(shù)是主實(shí)體，第二個(gè)是關(guān)聯(lián)實(shí)體
    {
        if (role != null)
        {
            user.Roles.Add(role);
        }
        return user;
    },
    param: new { Id = 1 },
    splitOn: "RoleId" // 拆分字段：指定從哪個(gè)字段開(kāi)始拆分第二個(gè)實(shí)體（Role）
).Distinct().ToList(); // Distinct去重（避免用戶(hù)被重復(fù)返回）

-- 創(chuàng)建存儲(chǔ)過(guò)程
CREATE PROCEDURE GetUserById
    @Id INT -- 輸入?yún)?shù)
AS
BEGIN
    SELECT * FROM Users WHERE Id = @Id
END

// 調(diào)用存儲(chǔ)過(guò)程（指定commandType為StoredProcedure）
int userId = 1;
var user = conn.QueryFirstOrDefault<User>(
    "GetUserById", // 存儲(chǔ)過(guò)程名稱(chēng)
    new { Id = userId }, // 輸入?yún)?shù)
    commandType: CommandType.StoredProcedure // 關(guān)鍵：指定為存儲(chǔ)過(guò)程
);

-- 創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程
CREATE PROCEDURE GetUserCountByAge
    @MinAge INT, -- 輸入?yún)?shù)
    @Count INT OUTPUT -- 輸出參數(shù)
AS
BEGIN
    SELECT @Count = COUNT(*) FROM Users WHERE Age >= @MinAge
END

// 使用DynamicParameters傳遞輸入/輸出參數(shù)
var param = new DynamicParameters();
param.Add("@MinAge", 20); // 輸入?yún)?shù)
param.Add("@Count", 0, DbType.Int32, ParameterDirection.Output); // 輸出參數(shù)（初始值0）
// 調(diào)用存儲(chǔ)過(guò)程
conn.Execute(
    "GetUserCountByAge",
    param,
    commandType: CommandType.StoredProcedure
);
// 獲取輸出參數(shù)的值
int count = param.Get<int>("@Count");
Console.WriteLine($"年齡>=20的用戶(hù)數(shù)量：{count}");

using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 開(kāi)啟事務(wù)
    using (IDbTransaction tran = conn.BeginTransaction())
    {
        try
        {
            // 執(zhí)行第一個(gè)SQL（新增用戶(hù)）
            string insertUserSql = "INSERT INTO Users (UserName, Age) VALUES (@UserName, @Age); SELECT SCOPE_IDENTITY()";
            int userId = conn.ExecuteScalar<int>(insertUserSql, new { UserName = "李四", Age = 28 }, transaction: tran);
            // 執(zhí)行第二個(gè)SQL（新增用戶(hù)角色關(guān)聯(lián)）
            string insertUserRoleSql = "INSERT INTO UserRoles (UserId, RoleId) VALUES (@UserId, @RoleId)";
            conn.Execute(insertUserRoleSql, new { UserId = userId, RoleId = 1 }, transaction: tran);
            // 提交事務(wù)
            tran.Commit();
            Console.WriteLine("事務(wù)執(zhí)行成功");
        }
        catch (Exception ex)
        {
            // 回滾事務(wù)（出現(xiàn)異常時(shí)）
            tran.Rollback();
            Console.WriteLine($"事務(wù)執(zhí)行失?。簕ex.Message}");
        }
    }
}

// 需安裝NuGet包：System.Transactions
using (var scope = new TransactionScope())
{
    // 第一個(gè)數(shù)據(jù)庫(kù)連接
    using (IDbConnection conn1 = new SqlConnection(connectionString1))
    {
        conn1.Open();
        conn1.Execute("INSERT INTO Users (UserName) VALUES (@UserName)", new { UserName = "王五" });
    }
    // 第二個(gè)數(shù)據(jù)庫(kù)連接（跨庫(kù)事務(wù)）
    using (IDbConnection conn2 = new SqlConnection(connectionString2))
    {
        conn2.Open();
        conn2.Execute("INSERT INTO Orders (OrderNo) VALUES (@OrderNo)", new { OrderNo = "20240520001" });
    }
    // 提交事務(wù)（所有操作成功才提交）
    scope.Complete();
}

// 安裝擴(kuò)展包：Install-Package Dapper.Contrib
using Dapper.Contrib.Extensions;
// 實(shí)體類(lèi)需標(biāo)注[Table]特性（指定數(shù)據(jù)庫(kù)表名，可選）
[Table("Users")]
public class User
{
    [Key] // 標(biāo)注主鍵（自增主鍵需標(biāo)注）
    public int Id { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }
}
// 簡(jiǎn)化CRUD操作（無(wú)需寫(xiě)SQL）
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    // 新增
    int newId = conn.Insert<int>(new User { UserName = "趙六", Age = 30 });
    // 根據(jù)ID查詢(xún)
    User user = conn.Get<User>(newId);
    // 修改
    user.UserName = "趙六_修改";
    conn.Update(user);
    // 刪除
    conn.Delete(user);
    // 查詢(xún)所有
    List<User> users = conn.GetAll<User>().ToList();
}

// 安裝擴(kuò)展包：Install-Package Dapper.SqlBuilder
using Dapper.SqlBuilder;
// 構(gòu)建動(dòng)態(tài)SQL
var builder = new SqlBuilder();
// 基礎(chǔ)SQL
var template = builder.AddTemplate(@"SELECT * FROM Users /**where** /**orderby**");
// 動(dòng)態(tài)添加WHERE條件
int? minAge = 20;
string userName = "張";
if (minAge.HasValue)
{
    builder.Where("Age >= @MinAge", new { MinAge = minAge });
}
if (!string.IsNullOrEmpty(userName))
{
    builder.Where("UserName LIKE @UserName", new { UserName = $"%{userName}%" });
}
// 動(dòng)態(tài)添加排序
builder.OrderBy("CreateTime DESC");
// 執(zhí)行查詢(xún)
using (IDbConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    var users = conn.Query<User>(template.RawSql, template.Parameters).ToList();
}

public static class DapperHelper
{
    // 連接字符串（從配置文件讀取，推薦使用IConfiguration）
    private static readonly string _connectionString = "Data Source=.;Initial Catalog=DapperDemo;User ID=sa;Password=123456;";
    // 執(zhí)行增刪改操作
    public static int Execute(string sql, object param = null, IDbTransaction transaction = null)
    {
        using (IDbConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            return conn.Execute(sql, param, transaction);
        }
    }
    // 執(zhí)行查詢(xún)操作（返回多個(gè)結(jié)果）
    public static List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null)
    {
        using (IDbConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            return conn.Query<T>(sql, param, transaction).ToList();
        }
    }
    // 執(zhí)行查詢(xún)操作（返回單個(gè)結(jié)果）
    public static T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null)
    {
        using (IDbConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            return conn.QueryFirstOrDefault<T>(sql, param, transaction);
        }
    }
    // 其他方法...
}

// DAL層（數(shù)據(jù)訪問(wèn)層）
public class UserDal
{
    public User GetById(int id)
    {
        string sql = "SELECT * FROM Users WHERE Id = @Id";
        return DapperHelper.QueryFirstOrDefault<User>(sql, new { Id = id });
    }
    public int Add(User user)
    {
        string sql = "INSERT INTO Users (UserName, Age, Email) VALUES (@UserName, @Age, @Email); SELECT SCOPE_IDENTITY()";
        return DapperHelper.ExecuteScalar<int>(sql, user);
    }
    // 其他方法...
}
// BLL層（業(yè)務(wù)邏輯層）
public class UserBll
{
    private readonly UserDal _userDal = new UserDal();
    public User GetUserById(int id)
    {
        // 業(yè)務(wù)邏輯處理
        if (id <= 0)
        {
            throw new ArgumentException("用戶(hù)ID無(wú)效");
        }
        return _userDal.GetById(id);
    }
    // 其他方法...
}

// 優(yōu)化前：查詢(xún)?nèi)孔侄?，無(wú)分頁(yè)
string sql = "SELECT * FROM Users WHERE Age >= 20";
var users = DapperHelper.Query<User>(sql);
// 優(yōu)化后：只查需要的字段，分頁(yè)查詢(xún)，使用索引
string sql = "SELECT Id, UserName, Age FROM Users WHERE Age >= @MinAge ORDER BY CreateTime DESC OFFSET @PageSize * (@PageIndex - 1) ROWS FETCH NEXT @PageSize ROWS ONLY";
var users = DapperHelper.Query<User>(sql, new { MinAge = 20, PageIndex = 1, PageSize = 10 });

// 方案1：修改SQL參數(shù)名，與實(shí)體類(lèi)屬性名一致
string sql = "SELECT * FROM Users WHERE UserName = @UserName"; // 與實(shí)體類(lèi)的UserName一致
// 方案2：使用別名，讓查詢(xún)結(jié)果字段與實(shí)體類(lèi)屬性名一致
string sql = "SELECT UserName AS Name FROM Users"; // 實(shí)體類(lèi)屬性名為Name
// 方案3：手動(dòng)映射（復(fù)雜場(chǎng)景）
var user = conn.QueryFirstOrDefault<User>(
    "SELECT UserName AS Name FROM Users WHERE Id = @Id",
    new { Id = 1 }
);

// 正確寫(xiě)法：插入后返回自增ID，使用ExecuteScalar
string sql = "INSERT INTO Users (UserName) VALUES (@UserName); SELECT SCOPE_IDENTITY()";
int newId = conn.ExecuteScalar<int>(sql, new { UserName = "張三" });

// SQL順序：先查Users，再查Orders
string sql = "SELECT * FROM Users; SELECT * FROM Orders;";
using (var multi = conn.QueryMultiple(sql))
{
    var users = multi.Read<User>().ToList(); // 先讀Users（與SQL順序一致）
    var orders = multi.Read<Order>().ToList(); // 再讀Orders
}

using (var tran = conn.BeginTransaction())
{
    // 關(guān)鍵：指定transaction參數(shù)
    conn.Execute(insertSql, param, transaction: tran);
    tran.Commit();
}