The .NET data access choice starts with who should own the SQL and what should happen when an object changes. ADO.NET gives the application direct command and transaction control. Micro-ORMs map results with less boilerplate. Full ORMs add a model and persistence behavior that can save repeated work across related entities.

An existing SQL Server application can use more than one approach. Its CRUD code may benefit from change tracking while a reporting query needs exact SQL or a bulk load needs a provider API. Transaction ownership, generated SQL, parameter types and the shape of existing stored procedures are better buying criteria than a single benchmark.

.NET data access tools for SQL Server at a glance

Compare a driver, a lightweight mapper or query library, and a full ORM using the same database contract. Check transaction and retry settings explicitly.

Nine .NET SQL Server data access choices
ToolPrimary roleControl to verifyTrial evidence
Microsoft.Data.SqlClientADO.NET SQL Server providerExplicit SQL, parameters and transaction APITyped result and matching rollback
Entity Framework CoreORM, LINQ and tracked changesProvider, generated SQL and transaction strategyCorrect model, two writes and one commit
DapperSQL-centric object mappingCommand text, typed parameters and shared transactionExpected data and transactional result
LINQ to DBLINQ SQL translation and mappingGenerated SQL and transaction lifecycleActual statements and exact data
Devart dotConnect for SQL ServerCommercial .NET SQL Server providerRuntime, licensing and supported data access modeTyped result and provider transaction behavior
DevExpress XPOORM with SQL Server connection supportSession or unit of work and supported providerVerified changes and rollback
LLBLGen ProCommercial model and data access toolingRuntime, generated mappings and transaction scopeActual SQL and both outcomes
ServiceStack OrmLiteTyped SQL access and object mappingConfigured SQL Server provider and transactionTyped parameter and transaction results
NHibernateORM and unit-of-work pathSession lifecycle, flush and SQL dialectObserved SQL and persisted result

Microsoft.Data.SqlClient

Microsoft.Data.SqlClient is Microsoft's ADO.NET provider for SQL Server. It gives the application connections, commands, parameter types, readers and transactions. It also exposes SQL Server-specific functionality such as SqlBulkCopy and supplies the underlying connection used by several higher-level libraries.

It fits code that needs to manage precise SQL types, stored procedures, bulk operations or transaction boundaries. The application owns object mapping and data access conventions. Since it is often below the ORM as well, provider version, connection settings and authentication still matter even if few features call it directly.

Use Microsoft.Data.SqlClient when you want direct command and parameter control. Verify the connection driver, encryption policy and explicitly assigned transaction for every write command in the operation.

Bind the expected decimal scale and nullable values with the actual data model. Compare database state from a fresh connection after rolling back the intentionally failed lab change.

Microsoft’s ADO.NET documentation shows starting an asynchronous SqlClient transaction
Microsoft’s ADO.NET documentation shows starting an asynchronous SqlClient transaction. Publisher documentation excerpt
Compatibility
Supported SQL Server and Azure SQL connections from supported .NET target frameworks.
License and trial
Open-source Microsoft provider with separate SQL Server licensing.

Entity Framework Core

Entity Framework Core's SQL Server provider translates LINQ queries through Microsoft.Data.SqlClient and builds commands from changes tracked by a DbContext. It provides an entity model and migration tooling. Applications can opt into mapped projections and database-specific behaviors.

EF Core fits an application with a substantial relational domain model and repeated entity updates. Tracking and query translation have costs that vary by query shape. A team benefits from owning the generated SQL and migration review, especially when another application shares the database or a change can block production tables.

Confirm provider and .NET version, then review generated SQL and how transactions are defined across tracked changes. Coordinate an explicit transaction with the configured execution strategy when the operation can retry.

Fail between two lab writes and examine the persisted state independently of the tracking context. Repeat a successful operation and check its row outcome and audit record.

Microsoft’s Visual Studio scaffolding example shows an Entity Framework data context and a database provider selection
Microsoft’s Visual Studio scaffolding example shows an Entity Framework data context and a database provider selection. Publisher screenshot
Compatibility
EF Core SQL Server provider with compatible .NET, EF Core and SQL Server versions.
License and trial
Open-source Microsoft packages with hosting and database costs separate.

Dapper

Dapper extends ADO.NET connections with methods that execute SQL and map results into .NET types. It handles mapping and parameter binding while the caller supplies the command, connection and transaction. That works for hand-written queries and stored procedures.

It fits SQL-first applications where query shape is part of the performance or operational contract. The reduced abstraction makes the executed SQL easier to identify, while changes to joined projections and transaction conventions remain the team's responsibility. It does not supply a tracked domain model.

Pass values as parameters and select a target type matching SQL Server precision and nullability. Use the same connection and documented transaction argument for every command in the operation.

Run the intentionally failed lab transaction and verify no partial state. Inspect the returned row and emitted SQL for the successful retry under real application settings.

Dapper’s README shows a typed query with named parameters
Dapper’s README shows a typed query with named parameters. Publisher documentation excerpt
Compatibility
Compatible ADO.NET SQL Server provider and supported .NET runtime.
License and trial
Open-source Dapper library with separate database licensing.

LINQ to DB

LINQ to DB translates typed LINQ expressions into SQL and supports provider-specific operations, including bulk copy on suitable SQL Server configurations. Its table mappings and query extensions support a SQL-oriented data layer with type checking at compile time.

It fits a team that wants expression composition and set-based operations with an explicit persistence model. The application supplies transaction and lifecycle conventions for its data access. Provider selection and the generated statement still determine which queries and bulk operations behave as expected.

Use the SQL Server provider and choose how the mapping and LINQ translation handle the fixture's values. Capture generated SQL, bind the parameter types and review the deployed transaction configuration.

Test a two-write operation and verify state with an independent connection after failure. Compare query behavior and the final recreated unit of work for a safe retry.

The LINQ to DB project guide shows starting a transaction on its data connection
The LINQ to DB project guide shows starting a transaction on its data connection. Publisher documentation excerpt
Compatibility
LINQ to DB with a supported SQL Server provider and .NET target.
License and trial
Open-source library with separate commercial database costs.

Devart dotConnect for SQL Server

Devart dotConnect for SQL Server is a commercial ADO.NET data provider. It has its own connection and command types and vendor tooling. A team can use it as the low-level provider for code that needs an ADO.NET interface to SQL Server.

It is relevant when the team has a specific need for vendor support or a component already using the provider. Its feature set and licensing should be evaluated on the chosen .NET target. Migration from another provider can affect connection strings, SQL type behavior and deployment packages even when the API remains familiar.

Verify the platform, provider and plan for the intended dotConnect application. Record how the driver or integration binds decimal values, transactions and cancellation.

Compare the application result and persisted SQL rows after rollback and success. Keep reproducible connection and transaction settings with the observed behavior.

Devart’s dbMonitor example shows database events and details for a dotConnect application
Devart’s dbMonitor example shows database events and details for a dotConnect application. Publisher screenshot
Compatibility
Supported SQL Server and .NET combinations in Devart's provider documentation.
License and trial
Commercial product with vendor edition and evaluation terms.

DevExpress XPO

DevExpress XPO maps persistent .NET classes to database tables and uses sessions or a UnitOfWork to load and save changes. It includes SQL Server connectivity through supported provider options and integrates with parts of the broader DevExpress application stack.

It fits projects that already depend on XPO mappings or need its particular persistence conventions. DevExpress maintains XPO and recommends EF Core for new development. A team with triggers, computed expressions or database-generated keys can use a full save workflow to expose assumptions in an existing mapping.

Choose the supported XPO SQL Server provider and session or unit-of-work approach. Check how it maps data types and when it sends SQL to the database.

Run both lab paths and compare stored state outside the active session. Record any configuration need for tracking, batching or transaction boundaries.

DevExpress’s XPO Data Model Wizard example shows SQL Server connection settings
DevExpress’s XPO Data Model Wizard example shows SQL Server connection settings. Publisher screenshot
Compatibility
DevExpress XPO runtime with a supported SQL Server connection provider.
License and trial
Free proprietary ORM under the DevExpress license. Source access is included in qualifying paid subscriptions.

LLBLGen Pro

LLBLGen Pro is a model designer and code generator with SQL Server support. A team can model an existing database or domain and generate mappings for supported runtime targets. The Designer also works with the LLBLGen Pro Runtime Framework.

It fits teams that manage a large schema and want repeatable generation with explicit mapping choices. Designer support for a backend does not make all runtime targets behave the same. Generated changes need source review, while provider support and deployment requirements follow the selected runtime.

Verify the supported SQL Server design and runtime configuration. Review generated mapping for decimal fields and the exact transaction sharing mechanism documented for your data access option.

Run the same two writes and observe the intentional failure. Check the persisted outcome in a new reader, then verify the expected amount and audit record after success.

The LLBLGen Pro Designer shows model entities and associated project details
The LLBLGen Pro Designer shows model entities and associated project details. Publisher screenshot
Compatibility
SQL Server through a supported LLBLGen Pro Designer and runtime target combination.
License and trial
Commercial Designer licensing with provider and runtime terms determined separately.

ServiceStack OrmLite

ServiceStack OrmLite is a typed data access library that maps .NET objects to SQL operations. Its SQL Server dialect builds statements for common queries and persistence. It also allows explicit SQL for operations that are easier to express directly.

It fits projects that want typed CRUD with a relatively small API, especially when the rest of the application already uses ServiceStack. Relationships and transaction scope remain visible responsibilities in application code. License limits and the behavior of an existing schema should be understood before broad adoption.

Select the approved SQL Server dialect or provider for the actual runtime. Verify the parameter mapping and transaction scope across all writes needed by the operation.

Compare read and commit outcomes in the lab before and after a failure. Capture which rows were updated and the actual amount and audit state.

ServiceStack’s OrmLite documentation shows opening a database transaction
ServiceStack’s OrmLite documentation shows opening a database transaction. Publisher documentation excerpt
Compatibility
Supported OrmLite SQL Server dialect and ADO.NET driver for the chosen runtime.
License and trial
ServiceStack license and usage limits or commercial subscription apply.

NHibernate

NHibernate maps .NET classes and relationships to relational tables. A session maintains loaded objects and can persist changes in a transaction. SQL Server support comes through configured dialects and drivers, with mappings controlling how database objects are represented.

It is a reasonable choice for a long-lived application already built around its mapping or session semantics. Lazy loading and session scope can change when queries run, especially across service boundaries. Existing mapping behavior and generated SQL deserve attention when changing the driver or target SQL Server release.

Configure the supported SQL Server dialect and NHibernate session strategy. Decide when the session flushes and which operation spans a database transaction.

Force the controlled failure and verify the database independently of NHibernate's session. Check results after a new session repeats the allowed change.

The NHibernate reference shows starting a transaction on a session
The NHibernate reference shows starting a transaction on a session. Publisher documentation excerpt
Compatibility
NHibernate with a documented SQL Server dialect, ADO.NET driver and .NET target.
License and trial
Open-source NHibernate licensing and community support.

Which tool fits your data layer?

What you needTools to shortlistWhat to verify
Keep SQL and transaction control in the applicationVerify typed parameters, mapped rows and that both writes use the same connection and transaction.
Use LINQ or an established mapping frameworkInspect generated SQL, execution order and when the application flushes or saves changes.
Generate mappings from an existing SQL Server schemaCheck designer and runtime requirements, then test the generated data layer against the same transaction.

Choose how much SQL and transaction logic the .NET app owns

SqlClient exposes direct connection, command and transaction controls. SQL mappers reduce object conversion work while allowing explicit queries. An ORM adds query translation and tracked changes. Verify generated SQL, types and transaction boundaries for your actual service.

Check automatic retry and explicit transactions together in the chosen provider or ORM. Use the documented transaction strategy and design a replay that does not duplicate committed business changes.

Which parts of data access does the team need to control?
  • Exact commands and parameter binding

    Trial SqlClient and a SQL-oriented mapper

    Verify connection, transaction and typed results.

  • Model-level queries and tracked changes

    Trial an ORM with the supported SQL Server provider

    Inspect generated SQL, flush or save behavior and rollback.

  • The service retries after a failed write

    Test real committed state with an independent connection

    Check a failed transaction and successful safe retry.

Verify that a failed .NET write leaves both SQL tables unchanged

The fixture starts item 1 at amount 20.00. The Save step changes it to 23.45 and records an audit row in one transaction. The default intentionally fails after the first write. After rollback the amount should remain 20.00 and no audit row should exist.

Repeat with @SimulateFailure set to 0 and verify amount 23.45 and one audit row. Implement equivalent behavior in the actual app for the comparison, open a separate verification connection and inspect exact bound data and outcome.

SQL Server .NET transaction acceptance
CaseAmount in databaseAudit rows
Setup20.000
Simulated failure halfway through20.000
Successful rerun23.451
Replay after successRemains 23.45Remains 1

How to compare these tools

Run the same read and two-step write in each candidate. Force the second statement to fail and check from a fresh connection that rolling back also undid the first statement. Sources checked .

Commit one reservation, then roll back a failed retry

Use an empty disposable DotNetAccessTrial database. Run Setup, then run Save with the intentional failure enabled. Use View in a fresh connection to confirm the rollback. Rerun Save with the failure disabled and verify both persisted results.

SQL

Compare transaction rollback and success in the SQL Server fixture

T-SQL · 37 lines

SET NOCOUNT ON;IF DB_NAME() <> N'DotNetAccessTrial' OR @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2    THROW 51000, 'Use the disposable DotNetAccessTrial database and autocommit.', 1; DECLARE @Step varchar(5) = 'View'; -- Setup, Save or ViewIF @Step NOT IN ('Setup','Save','View')    THROW 51001, 'Choose Setup, Save or View.', 1;SET XACT_ABORT ON; IF @Step = 'Setup'BEGIN    BEGIN TRAN;    CREATE TABLE dbo.DotNetTrialItems (ItemId int PRIMARY KEY, Amount decimal(10,2) NOT NULL);    CREATE TABLE dbo.DotNetTrialAudit (ItemId int PRIMARY KEY, ChangedUtc datetime2(3) NOT NULL);    INSERT dbo.DotNetTrialItems VALUES (1,20.00);    COMMIT;END; IF @Step = 'Save'BEGIN    DECLARE @SimulateFailure bit = 1, @NewAmount decimal(10,2) = 23.45;    BEGIN TRY        BEGIN TRAN;        UPDATE dbo.DotNetTrialItems SET Amount = @NewAmount WHERE ItemId = 1 AND Amount = 20.00;        IF @@ROWCOUNT <> 1 THROW 51002, 'Expected the initial trial row.', 1;        IF @SimulateFailure = 1 THROW 51003, 'Intentional failure before the audit insert.', 1;        INSERT dbo.DotNetTrialAudit VALUES (1,SYSUTCDATETIME());        COMMIT;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK;        THROW;    END CATCH;END; SELECT ItemId, Amount FROM dbo.DotNetTrialItems WHERE ItemId = 1;SELECT COUNT_BIG(*) AS AuditRows FROM dbo.DotNetTrialAudit WHERE ItemId = 1;
Review before runningT-SQLUTF-837 lines
  1. Pick intended .NET and SQL driver versions and record how the client opens the connection and binds amount. Run Setup in the isolated database.
  2. Use the candidate library to perform the failure and success cases. Check the resulting SQL data from a fresh independent connection. Verify amount types, stored values and audit outcome.
  3. Review actual SQL operations and retry decisions. Repeat the successful request and check whether the application preserves the single committed action.

Conclusion

Choose the data layer the team can maintain. Compare SQL control, mapping and deployment needs against the same production-shaped read and write. Verify rollback using database state and keep that check with the application.

Frequently asked questions

Should I use Dapper or EF Core for SQL Server?

Use Dapper if the application keeps SQL explicit and needs a typed mapper. Use EF Core if it benefits from a LINQ model, entity tracking and migrations. Compare the SQL generated or authored for the real workload and verify transaction behavior.

Does Dapper replace Microsoft.Data.SqlClient?

Dapper extends an ADO.NET connection with query and mapping helpers. For SQL Server, the application still uses a compatible provider such as Microsoft.Data.SqlClient.

How can I test rollback in a .NET SQL Server application?

Execute a successful first statement and a deliberately failing second statement in the same transaction. Roll back. Read the affected data from a new connection and confirm it matches the state before the failed operation.

Which .NET tools can generate models from SQL Server tables?

EF Core can scaffold a DbContext and entities from an existing database. DevExpress XPO and LLBLGen Pro also document database-first modeling. Check supported versions, tooling and how regenerated code is maintained.

Does one call to EF Core SaveChanges cover multiple SQL commands?

When the provider supports transactions, EF Core normally applies the changes in a single SaveChanges call atomically. For work across multiple saves or direct commands, configure the required transaction and check how retries and savepoints affect it.

Does an ORM automatically make a sequence of .NET commands one SQL Server transaction?

Check the library's actual transaction and save behavior for those commands. Verify multi-step operations with a failure in the middle and an independent read.

How do I compare performance between Dapper and EF Core for SQL Server?

Use the same app query shape, parameters, result size and transaction behavior. Capture generated SQL and real timings on the intended deployment.

Related guides

Guide~12 min readUpdated 16 Nov 2026
LinkedInXEmail