dapper-repository

Inject IDbConnectionFactory and ITenantContext, open the connection with using var conn, then call ExecuteAsync or QueryAsync with a CommandDefinition whose commandType is CommandType.StoredProcedure. Pass parameters as anonymous objects, never via string concatenation.

Create a stored procedure using COUNT(*) OVER() AS TotalCount with OFFSET and FETCH NEXT for paging, then call it via QueryAsync with TenantId, Search, Page, and PageSize parameters. Read TotalCount from the first row to build the PagedResult.

Yes, Dapper accepts an IEnumerable of anonymous parameter objects in ExecuteAsync and executes the stored procedure once per item. This works well for child tables with BIGINT IDENTITY keys generated by SQL Server.

Inline SQL scatters data logic across the codebase, complicates review, and risks SQL injection through string concatenation. Centralizing SQL in stored procedures with CREATE OR ALTER scripts keeps data access versioned, parameterized, and auditable.

Pass TenantId from ITenantContext as a parameter on every call and include tenant_id = @TenantId in every WHERE clause of the procedure. This applies to SELECT, UPDATE, and DELETE statements alike.