ADO.NET interview questions and answers

What Is ADO.NET

ADO.NET provides a bridge between .NET applications and data sources such as SQL Server, Oracle, or MySQL. Unlike older technologies that relied on persistent connections, ADO.NET emphasizes disconnected data access via XML and DataSets, though connected streaming remains vital for high-performance scenarios. In modern .NET development, SQL Server interactions specifically use the Microsoft.Data.SqlClient namespace, which replaces the legacy System.Data.SqlClient found in .NET Framework. This shift ensures ongoing support and new features aligned with cross-platform .NET Core and .NET 5+ evolution.

Connected Components

These classes require an active connection to the database and are optimized for forward-only, read-only access or command execution. - SqlConnection: Establishes a session with a specific SQL Server instance using a connection string. - SqlCommand: Executes SQL statements or stored procedures against the connection. - SqlDataReader: Provides a high-performance, forward-only stream of rows from the data source. - SqlTransaction: Manages database transactions to ensure atomicity across multiple operations.

Disconnected Components

These classes allow data manipulation in memory without holding an open database connection, ideal for web applications or batch processing. - SqlDataAdapter: Acts as a bridge between the DataSet and the database, using SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. - DataSet: An in-memory cache of data that can include multiple DataTables, relations, and constraints. - DataTable: Represents a single table of in-memory data within a DataSet.

Connected vs Disconnected

Best Practices and Pitfalls

• Parameterization: Always use explicit parameter types (e.g., SqlDbType.Int) instead of AddWithValue, which can cause performance issues due to type inference and plan cache pollution.

• Resource Management: Wrap SqlConnection, SqlCommand, and SqlDataReader in using statements to ensure connections are closed and disposed promptly.

• Async Operations: Use OpenAsync, ExecuteReaderAsync, and ReadAsync in web applications to free up threads while waiting for I/O.

• Null Handling: Check for DBNull before reading values; DataRow.Field<int>() throws on null, so use Field<int?>() for nullable columns.

• Security: Never concatenate user input into SQL strings; rely on parameterized queries to prevent injection attacks.

• Transactions: For distributed transactions on .NET 7+, note they are supported only on Windows after enabling TransactionManager.ImplicitDistributedTransactions.

Architecture and Data Model

• ADO: a single Connection object produces Recordset objects that combine query execution, cursor management, scrolling and update logic in one COM object. Data is represented as rows and fields accessed through Recordset.Fields.

• ADO.NET: separates responsibilities into a connected layer (SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter, SqlTransaction) and a disconnected layer (DataSet, DataTable, DataRow, DataRelation). The DataSet is an in-memory relational store that can hold multiple related tables, constraints and views, independent of any specific data source.

Connected vs Disconnected Access

• ADO is primarily connected: a Recordset typically holds a server-side cursor, locks server resources while open, and uses UpdateBatch for optimistic updates.

• ADO.NET favours a disconnected pattern: a SqlDataAdapter fills a DataSet/DataTable, the connection is closed immediately, the data is edited in memory, and a later Update reconciles changes back to the database. This scales far better for web applications and service tiers because connections are held for the shortest possible time.

XML Integration

• ADO: XML support was bolted on later through Recordset.Save with adPersistXML; the schema and row format are specific to ADO.

• ADO.NET: the DataSet serialises natively to XML via WriteXml/ReadXml and WriteXmlSchema, with XmlWriteMode.WriteSchema, IgnoreSchema or DiffGram. This makes data exchange across platforms and through web services straightforward, without COM on either end.

Performance, Scalability and Interoperability

Practical Guidance

• For new .NET 8 development, use ADO.NET types from Microsoft.Data.SqlClient for SQL Server, or the corresponding managed provider for other databases; classic ADO should only be referenced through COM interop to maintain existing applications.

• Where a higher-level API fits, EF Core 8 builds on the same ADO.NET provider model (Microsoft.Data.SqlClient), so the connected classes remain relevant for raw SQL, bulk operations and performance-critical paths.

• If you must interoperate with classic ADO, do it behind an interface and migrate the data access layer to ADO.NET or EF Core rather than extending the COM dependency.

Core Purpose

Unlike connected readers such as SqlDataReader, a DataSet operates independently of the data source after loading. This disconnected architecture supports scenarios requiring offline data processing, multiple round-trip batching, or binding to UI controls that expect a relational structure. It holds data retrieved from one or multiple sources, preserving schema information including primary keys and data types.

Internal Structure

A DataSet contains a collection of DataTable objects, each representing a tabular result set. Relationships between tables are defined via DataRelation objects, enabling navigation similar to foreign keys. Constraints such as UniqueConstraint and ForeignKeyConstraint enforce integrity within the in-memory cache. Changes to rows are tracked via RowState (Added, Modified, Deleted, Unchanged), which determines how updates are sent back to the database.

Filling and Updating

The SqlDataAdapter bridges the database and the DataSet. Its Fill method executes a SELECT command and populates the tables. To persist changes, the adapter uses InsertCommand, UpdateCommand, and DeleteCommand. Calling Update on the adapter sends pending changes to the server. Note that DataAdapter.Update calls AcceptChanges on each saved row automatically; developers should not call AcceptChanges manually afterward.

Modern Context

While still supported in .NET 8, DataSet usage has declined in favor of Entity Framework Core or lightweight micro-ORMs like Dapper for typical CRUD operations. However, it remains relevant for legacy migration, complex in-memory analytics, or scenarios requiring XML serialization of relational schemas. Avoid using binary serialization (RemotingFormat = Binary) as it relies on BinaryFormatter, which is obsolete and removed in .NET 9.

Best Practices and Pitfalls

• Null Handling: Use nullable generics with Field<T?>() to avoid InvalidCastException when database columns contain NULL values; Field<int>() throws on DBNull.

• Connection Management: Keep connections open only during Fill or Update; the DataSet itself does not hold an open connection.

• Serialization: Prefer JSON or XML (WriteSchema) over binary serialization for data transfer across processes.

• Memory Usage: Large DataSet instances consume significant memory; consider streaming with SqlDataReader for read-only scenarios involving massive data volumes.

Key Differences

How Each Works

• DataSet: You create a DataSet (or a typed DataTable), pass it to a SqlDataAdapter together with a SelectCommand, and call adapter.Fill(dataSet). The adapter opens the connection, reads all rows, populates the schema and rows, and closes the connection again. Because the data lives in memory, you can iterate over it repeatedly, add DataRelation objects between tables, and serialize the contents to XML with WriteXml.

• DataReader: You call command.ExecuteReader() on an open SqlConnection and loop with while (reader.Read()), accessing columns by ordinal or name with methods such as GetInt32(0) or reader["Name"]. Only one row is held in memory at a time, but the connection remains busy until you dispose the reader. If the command returns multiple result sets, you can advance through them with NextResult().

Choosing Between Them

• Use a DataReader when you only need to read data once and render or stream it: populating a dropdown, filling a grid on a web page, writing a CSV export, or loading a large lookup. It minimizes both memory pressure and load time.

• Use a DataSet when you need offline access, such as moving data across tiers, caching between postbacks, combining and relating several result sets, or binding to controls that require design-time schema. It is also the right choice when users will edit rows in the UI and you later persist changes through SqlDataAdapter.Update.

• Prefer neither when an ORM fits: in EF Core 8, queries return strongly typed entities and AsNoTracking() gives read-only performance close to a reader without the manual mapping.

Advantages and Limitations

• DataSet advantages: Works without an open connection, supports relationships and constraints, integrates with DataView for sorting/filtering, and tracks row state so updates are straightforward.

• DataSet limitations: High memory cost for large results, slower first load, and it is easy to serve stale data because the snapshot is taken at fill time.

• DataReader advantages: Excellent performance and a tiny memory footprint, plus access to provider-specific types.

• DataReader limitations: Ties up the connection for the whole read, cannot be bound directly in some scenarios without wrapping it, and requires careful disposal so connections return to the pool.

Best Practices and Common Pitfalls

• Always wrap a DataReader in a using block so it closes even on exception; otherwise the connection stays occupied and the pool is exhausted.

• Open the connection just before ExecuteReader and close it as soon as reading finishes, so the reader holds the connection for the shortest possible time.

• Do not perform slow work (such as calling a web service) inside a reader.Read() loop while the connection is open.

• Avoid DataSet for very large queries; the memory cost scales with the entire result set and can cause avoidable garbage-collection pressure.

• Remember that a DataSet is a snapshot: changes made by other users after Fill are invisible, so use it only when eventual consistency is acceptable.

Connection and Command Classes

• SqlConnection: Establishes a physical session with a SQL Server instance using a connection string. It must be explicitly opened before executing commands and closed afterward to release resources.

• SqlCommand: Encapsulates a SQL statement, table name, or stored procedure to execute against the connection. It holds the Parameters collection used to pass values securely.

• SqlTransaction: Groups multiple commands into a single atomic unit, ensuring all changes commit or roll back together via Commit() or Rollback().

Data Retrieval Models

• SqlDataReader: Implements a connected, forward-only, read-only stream of rows. It offers high performance for scenarios where data is processed sequentially without modification.

• SqlDataAdapter: Acts as a bridge between the database and disconnected data structures. It uses SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand to fill a DataSet and propagate changes back.

• DataSet and DataTable: Represent disconnected in-memory caches. DataSet can hold multiple DataTable objects with relationships, allowing manipulation without an open connection.

Best Practices and Pitfalls

• Dispose Resources: Always wrap SqlConnection, SqlCommand, and SqlDataReader in using statements to prevent connection pool exhaustion.

• Parameterization: Never use AddWithValue as it infers types incorrectly and harms plan caching; specify SqlDbType explicitly.

• Null Handling: Check reader.IsDBNull before retrieving values to avoid InvalidCastException, and use DBNull.Value when inserting nulls.

• Connected vs Disconnected: Use SqlDataReader for read-only streaming and DataSet/DataAdapter when batch updates or offline manipulation are required.

Core Responsibility

The connection object represents the active session with the database. Without an open connection, commands cannot be executed against the server. It handles the handshake, authentication (SQL Server authentication or Windows Integrated Security), and selects the initial catalog (database) specified in the connection string. It also exposes metadata about the server via properties like ServerVersion and Database.

Key Operations

A SqlConnection enables several operations beyond simply opening a channel: - Command creation: CreateCommand() produces a SqlCommand pre-bound to the connection, avoiding manual constructor wiring. - Transaction initiation: BeginTransaction() returns a SqlTransaction for atomic batches. - Database switching: ChangeDatabase(string) changes the default database for the session without reopening the connection. - Schema discovery: GetSchema() returns metadata tables about tables, columns, indexes, and other database objects. - State monitoring: The StateChange event notifies when the connection transitions between Closed, Open, Connecting, Executing, or Fetching. - Async support: OpenAsync() and CloseAsync() allow non-blocking connection management on .NET 8.

Lifecycle and State Management

Connections should be opened as late as possible and closed as early as possible. The State property indicates the current status. Explicitly calling Close() or disposing the object returns the connection to the pool rather than tearing down the physical network socket, thanks to ADO.NET pooling mechanisms.

Transaction Support

Local transactions are initiated via BeginTransaction(), which returns a SqlTransaction object. This transaction must be associated with commands created on the same connection. Committing or rolling back changes is managed through the transaction object, ensuring atomicity for a batch of operations within a single database session. For distributed transactions across multiple resources, .NET 8 supports System.Transactions.TransactionScope on Windows after enabling TransactionManager.ImplicitDistributedTransactions; this is not supported on Linux/macOS.

Connection Pooling

By default, Microsoft.Data.SqlClient enables connection pooling. When Close() is called, the physical connection remains alive in a pool keyed by the exact connection string. Subsequent Open() calls reuse existing pooled connections, significantly reducing overhead. Pooling behavior can be configured via connection string keywords like Pooling, MaxPoolSize, and ConnectionLifetime.

Best Practices

• Always use using: Ensure SqlConnection and SqlCommand are wrapped in using statements to guarantee disposal and return to the pool.

• Handle Nulls Correctly: Per ADO.NET rules, a C# null parameter value may be treated as not supplied. Use DBNull.Value explicitly when inserting database NULLs.

• Avoid Long-Lived Connections: Do not hold connections open during user interaction or long-running computations; open them only for the duration of the data operation.

• Secure Connection Strings: Store connection strings in secure configuration (e.g., Azure Key Vault or user secrets), never hardcode credentials in source code.

• Check State Before Opening: Verify connection.State != ConnectionState.Open before calling Open() to avoid exceptions when reusing connection objects.

Local Transactions with SqlTransaction

• A local transaction starts by calling BeginTransaction() on an open SqlConnection, which returns a SqlTransaction. You must assign this object to the Transaction property of every SqlCommand that participates.

• The transaction ends with Commit() to persist changes or Rollback() to undo them. Always call Rollback() explicitly in a catch block; do not rely on disposing the transaction to roll it back, because disposal does not guarantee an immediate rollback—the pending transaction is rolled back only when the connection is closed.

• SqlTransaction supports partial rollback through savepoints: call Save(string savepointName) to mark a point you can roll back to without aborting the whole transaction.

• Wrap both the connection and the transaction in using statements, but keep the explicit commit/rollback logic inside a try block.

Ambient Transactions with TransactionScope

• When work touches multiple connections, databases, or other resource managers, wrap the logic in a TransactionScope. Commands automatically enlist in the ambient transaction.

• For asynchronous code, pass TransactionScopeAsyncFlowOption.Enabled to the constructor; otherwise the ambient flow is not captured across await boundaries.

• Escalation to a distributed transaction is platform-dependent: supported on .NET Framework; on .NET 7+ it works only on Windows after setting TransactionManager.ImplicitDistributedTransactions = true, and is not available on Linux or macOS.

Isolation Levels

• Pass a System.Data.IsolationLevel value to BeginTransaction to control locking and consistency. The default is ReadCommitted, which prevents dirty reads but allows non-repeatable reads.

• Do not confuse System.Data.IsolationLevel with System.Transactions.IsolationLevel; the latter is a separate enum used by TransactionOptions when configuring a TransactionScope.

Common Pitfalls and Best Practices

• Open the connection first: BeginTransaction() throws if the connection is not already open.

• Never use AddWithValue: always add parameters with Parameters.Add("@name", SqlDbType.<Type>) and set Size, Precision, and Scale where the type requires them.

• Handle NULLs correctly: assign DBNull.Value for database NULLs; a C# null value makes the parameter behave as if it were not supplied.

• Keep transactions short: long-running transactions hold locks and hurt concurrency; commit as soon as the unit of work is complete.

• Use savepoints for partial rollback: instead of assuming SqlTransaction cannot nest, use Save() and Rollback(string) to undo part of a transaction.

• Retry on transient failures: cloud databases such as Azure SQL may deadlock; wrap the transaction block in a retry policy with exponential backoff.

• Avoid distributed transactions when possible: they are heavier and platform-restricted; design aggregates to fit a single connection if you can.

How Connection Pooling Works

When a SqlConnection object requests a connection, the provider checks the pool for an unused connection associated with the identical connection string. If found, it is reused; otherwise, a new physical connection is created up to the Max Pool Size. Closing the connection does not terminate the session but returns it to the pool for reuse, significantly reducing latency associated with authentication and network handshake. Pools are maintained per process, and a separate pool exists for each distinct connection string (and, when integrated security is used, for each Windows identity).

Configuration via Connection String

Pooling behavior is primarily controlled through keywords in the connection string. The key settings include: - Pooling: Enables or disables pooling (true by default). - Max Pool Size: The maximum number of connections allowed in the pool (default 100). - Min Pool Size: The minimum number of connections maintained in the pool (default 0). - Connection Lifetime: The maximum lifetime of a connection in seconds before it is destroyed (default 0, meaning unlimited). - Connection Reset: Determines whether the connection state is reset when returned to the pool (true by default).

Managing Pools Programmatically

While connection strings define static limits, you can manage pools dynamically using static methods on SqlConnection. SqlConnection.ClearPool(connection) empties the pool associated with a specific connection, while SqlConnection.ClearAllPools() empties all pools in the current process. This is useful during database maintenance or when credentials change, forcing the creation of fresh connections.

Code Example

The following example demonstrates opening a connection with pooling enabled and executing a parameterized query. Note the explicit parameter type definition and proper disposal to ensure the connection returns to the pool.

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public class DataAccess
{
    public string GetUserName(int userId)
    {
        string connectionString = "Server=localhost;Database=AppDb;Trusted_Connection=True;Pooling=true;Max Pool Size=50;";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Users WHERE Id = @id", conn))
            {
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = userId;
                return cmd.ExecuteScalar()?.ToString() ?? string.Empty;
            }
        }
    }
}

Best Practices and Common Pitfalls

Always ensure connections are closed within a using block; leaving connections open exhausts the pool and causes timeout exceptions. Avoid constructing connection strings dynamically with varying whitespace or order, as pools are keyed on the exact string value. Set Min Pool Size cautiously, as pre-creating connections increases startup time and resource usage. Finally, prefer ClearAllPools only when necessary, as it negates the performance benefits of pooling until the cache warms up again.

Core Responsibilities

• Encapsulate the statement to run: the CommandText property holds the SQL text, the name of a stored procedure, or the path of a table, and CommandType tells the provider how to interpret it (CommandType.Text, CommandType.StoredProcedure, or CommandType.TableDirect).

• Bind to a connection: a command is tied to a single DbConnection (a SqlConnection for SQL Server), either through its constructor or via the Connection property. The connection must be open before the command executes.

• Carry parameters: the Parameters collection maps input values, output values and return codes to placeholders in the statement, which is what makes parameterized queries possible.

• Control execution: properties such as CommandTimeout (how long to wait before giving up) and Transaction (enlisting the command in an SqlTransaction) let you fine-tune how and where the statement runs.

The Four Execution Methods

The provider-specific command type (SqlCommand) inherits these from DbCommand, and each method corresponds to a different kind of result:

A DataTable or DataSet is filled by a DbDataAdapter, which itself holds a command object for its SelectCommand, InsertCommand, UpdateCommand and DeleteCommand, so commands are also the engine behind disconnected scenarios.

Parameters and Command Types

Parameters matter for both correctness and security. Passing values through the Parameters collection instead of concatenating strings prevents SQL injection, lets the provider handle type conversion, and improves plan reuse in the database engine. For SQL Server, parameters are added with an explicit SqlDbType, and a parameter whose Value is C# null is treated as not supplied, so DBNull.Value must be used to send an actual database NULL.

Relationship to Other ADO.NET Objects

The command is the middle of the three connected objects: the Connection talks to the data source, the Command defines and runs the statement, and the DataReader or DataAdapter consumes what the command produces. A command can also enlist in a Transaction started on the same connection, so that several statements commit or roll back as a unit.

Best Practices and Common Pitfalls

• Always parameterize: never build SQL by concatenating user input; use the Parameters collection for every value that comes from outside your code.

• Dispose of commands and readers: wrap them in using so unmanaged resources are released, and keep one open reader per connection unless you enable MARS.

• Pick the right execution method: ExecuteScalar for one value, ExecuteReader for streaming rows, ExecuteNonQuery for writes — using ExecuteReader to read a single scalar wastes resources.

• Set CommandType.StoredProcedure explicitly when calling a stored procedure, and use Direction = ParameterDirection.Output for output and return parameters.

• Prefer Microsoft.Data.SqlClient on .NET 8; System.Data.SqlClient is the legacy stack and receives only critical fixes.

• Check for DBNull when reading results: reader.IsDBNull(0) or nullable Field<int?>() avoid exceptions on database NULLs.

Core Role

The DbDataAdapter (commonly SqlDataAdapter for SQL Server) sits between the connected and disconnected layers of ADO.NET. Instead of keeping a connection open while your application processes records, the adapter opens the connection only long enough to copy data into a DataTable. Your code then works against that in-memory copy without holding an active database session, which improves scalability for web applications where holding connections open is expensive.

The Four Commands

A data adapter manages up to four command objects that define how it moves data in each direction:

• SelectCommand: Executes a query to read data; used by the Fill method.

• InsertCommand: Runs when a new DataRow is added to the table.

• UpdateCommand: Runs when an existing DataRow is modified.

• DeleteCommand: Runs when a DataRow is marked for deletion.

Only the SelectCommand is required if you are reading data. To persist changes, you must assign the other three commands manually or generate them with a SqlCommandBuilder.

Fill and Update Behavior

The Fill method opens the connection if it is closed, reads the results, and closes the connection automatically. If the connection was already open, Fill leaves it open. The Update method scans the DataTable for rows whose RowState is Added, Modified, or Deleted and executes the corresponding command for each one.

• Change tracking: The DataTable tracks original and current values, allowing commands to use both for concurrency checks.

• AcceptChanges: DataAdapter.Update calls AcceptChanges on each row it successfully saves, so you should not call AcceptChanges manually afterward.

• Errors: If one row fails during Update, the process stops and throws an exception, leaving remaining changes unsent (unless ContinueUpdateOnError is set).

DataAdapter vs DbDataReader

Best Practices

• Dispose resources: Wrap connections and adapters in using blocks to return connections to the pool promptly.

• Explicit types: Always add parameters with Parameters.Add and a specific SqlDbType rather than AddWithValue, which can cause plan cache bloat and type conversion issues.

• Minimal selects: Only select the columns you need; Fill copies everything returned by the SelectCommand into memory.

• Concurrency: Use original values in WHERE clauses to avoid overwriting changes made by other users since the data was loaded.

How It Works

A DataRelation is created with a name, a reference to a DataColumn (or array of columns) in the parent table, and a matching DataColumn in the child table. When you add it to the DataSet.Relations collection, ADO.NET also creates two supporting objects automatically:

• A UniqueConstraint on the parent column, ensuring every parent value is unique.

• A ForeignKeyConstraint on the child column, which can cascade updates and deletes according to its UpdateRule and DeleteRule (both default to Rule.Cascade).

The relationship is logical metadata held by the DataSet; the data itself stays in the two independent tables, so you can build several relations over the same tables.

Key Features

• Bidirectional navigation: from any DataRow you can call GetChildRows(relation) to reach the child rows, or GetParentRow(relation) / GetParentRows(relation) to reach the parent row(s). The DataRow overloads that take a DataRelation object are the strongly typed, safest form.

• Constraint enforcement: ChildKeyConstraint and ParentKeyConstraint expose the auto-created constraints, so you can inspect or reconfigure cascading behaviour.

• Nested XML output: setting Nested = true on a DataRelation makes DataSet.WriteXml emit child rows nested inside their parent rows instead of as separate flat tables.

• Designer and typed DataSet support: in typed DataSet classes, relations surface as properties and as generated methods such as GetChildRows helpers, which makes navigation read more like business objects.

• Works with disconnected data: because it lives in the DataSet, relation navigation works while the application is offline from the database.

Typical Use Cases

• Master-detail UIs, such as a customers grid whose selected row drives an orders grid, using the relation for filtering and navigation rather than writing a second query.

• Pre-loading a small, read-mostly reference set once and then joining it in memory, which avoids repeated round trips to SQL Server.

• Exporting hierarchical XML from relational data via WriteXml with Nested = true.

Advantages and Limitations

Best Practices and Common Pitfalls

• Let ADO.NET create the constraints by adding the relation through DataSet.Relations.Add, instead of building the UniqueConstraint and ForeignKeyConstraint by hand, unless you need non-default cascade rules.

• Match data types exactly between the two columns; a relation between an int key and a long foreign key will fail.

• For read-only lookups, consider DataRelation plus in-memory navigation; for large or frequently changing data, a single SQL JOIN query with Microsoft.Data.SqlClient and explicit SqlDbType parameters is usually cheaper than loading both tables.

• Prefer EF Core 8 for new application code, since it models relationships and navigation properties at a higher level. Keep DataSet and DataRelation for reporting, bulk-loading scenarios, or maintenance of existing .NET Framework code.

• Do not rely on DataSet binary serialization (RemotingFormat = Binary); it depends on BinaryFormatter, which is obsolete and removed in .NET 9.

How DataView Filtering and Sorting Works

A DataTable holds the actual rows; a DataView is a customizable, bindable projection over those rows. Each DataTable exposes a DefaultView property, or you can construct a new DataView targeting the table.

• RowFilter: a string expression, similar to a SQL WHERE clause, evaluated in memory against each row. Only rows matching the expression appear in the view.

• Sort: a comma-separated list of column names, each optionally followed by ASC or DESC.

• Live view: the DataView reflects changes made to the underlying DataTable (edits, adds, deletes) automatically, which is why it is ideal for data binding in Windows Forms and WPF.

RowFilter Expression Syntax

The filter expression supports a limited set of operators and functions:

• Comparisons: =, <>, <, >, <=, >=

• Logical: AND, OR, NOT, parentheses for grouping

• Patterns: LIKE '%text%' using % (any run of characters) and _ (one character); * and ? are accepted as equivalents

• Sets: IN (1, 2, 3)

• NULLs: ColumnName IS NULL / ColumnName IS NOT NULL

• Literals: strings in single quotes 'London', dates in #1/1/2024#, numbers unquoted

• Relations: Parent().ColumnName and Child(TableName).ColumnName to navigate DataRelation objects

Escape a single quote inside a string literal by doubling it: 'O''Brien'.

Sorting with the Sort Property

The Sort string mirrors an SQL ORDER BY clause but operates on column names only:

• "UnitPrice DESC" sorts one column descending.

• "CategoryID ASC, ProductName ASC" sorts by multiple columns.

• Sorting is applied after filtering, so the view shows only matching rows in the requested order.

DataTable.Select() as an Alternative

DataTable.Select() returns a DataRow[] snapshot rather than a live view:

• Select() returns all rows.

• Select(filterExpression) filters only.

• Select(filterExpression, sort) filters and sorts.

• Select(filterExpression, sort, recordStates) also filters by DataViewRowState (e.g. DataViewRowState.ModifiedCurrent, DataViewRowState.Added), useful for inspecting pending changes before Update().

Use Select() when you need a one-time array for processing; use a DataView when you need binding, currency management, or a view that stays synchronized with the table.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

var dataSet = new DataSet();

// Fill the DataSet from SQL Server
using var connection = new SqlConnection(
    "Server=localhost;Database=Northwind;Trusted_Connection=True;TrustServerCertificate=True;");
using var command = new SqlCommand(
    "SELECT ProductID, ProductName, UnitPrice, UnitsInStock, CategoryID FROM Products", connection);
using var adapter = new SqlDataAdapter(command);
adapter.Fill(dataSet, "Products");

DataTable products = dataSet.Tables["Products"];

// 1. Filter and sort with a DataView (live, bindable view)
DataView view = products.DefaultView;
view.RowFilter = "UnitPrice > 20 AND CategoryID = 1 AND ProductName LIKE '%cheese%'";
view.Sort = "UnitPrice DESC, ProductName ASC";

foreach (DataRowView rowView in view)
{
    Console.WriteLine($"{rowView["ProductName"]}: {rowView["UnitPrice"]}");
}

// 2. Filter and sort with Select() (returns a DataRow[] snapshot)
DataRow[] rows = products.Select("UnitsInStock > 0", "ProductName ASC");

// 3. Filter by row state, e.g. rows modified since last AcceptChanges
DataRow[] modified = products.Select(null, null, DataViewRowState.ModifiedCurrent);

// 4. Handle NULLs safely when reading typed values
foreach (DataRow row in rows)
{
    int? stock = row.Field<int?>("UnitsInStock"); // nullable: does not throw on DBNull
    if (stock > 0)
    {
        // process row
    }
}

Best Practices and Common Pitfalls

• Filter in the query when possible: RowFilter runs in memory over the full result set. For large tables, push filtering and sorting into the SQL WHERE and ORDER BY clauses to reduce memory and network traffic.

• Never build RowFilter from untrusted input: the expression is not parameterized. Validate or escape user input before concatenating it, or better, filter in SQL with parameters.

• Quote literals correctly: strings need single quotes, dates need #, and numbers must not be quoted. '10' compared to a numeric column can cause type-mismatch errors.

• DBNull and typed access: DataRow.Field<T>() throws if the column contains DBNull. Use the nullable form Field<int?>() for columns that can be null.

• DataView does not copy data: edits through a DataRowView modify the underlying DataTable. Clone the table first if you need an independent working copy.

• String comparisons respect CaseSensitive and Locale: filtering and sorting of string columns follow the table’s CaseSensitive property (default false, so comparisons are case-insensitive) and the table’s Locale; set DataTable.CaseSensitive = true if you need case-sensitive matching. DataView.ApplyDefaultSort only takes effect when Sort is empty and the table has a primary key.

What a DataProvider Contains

• Connection class: opens and manages a session to the data source (e.g. SqlConnection for SQL Server).

• Command class: executes SQL text, stored procedures, or parameterized statements and returns readers, scalar values, or affected row counts.

• DataReader class: streams result sets forward-only, read-only, with IEnumerable support so it works with LINQ while streaming.

• DataAdapter and CommandBuilder: fill DataSet/DataTable and push disconnected changes back to the database in batched, disconnected scenarios.

• Parameter class: binds values with an explicit database type, which is the mechanism behind parameterized queries and SQL injection protection.

• Transaction class: groups commands in a transaction with a chosen isolation level.

• Provider factory: a DbProviderFactory implementation lets you write code that is not bound to a specific vendor’s types, useful for multi-database products.

Common Providers

How to Choose

• Match the engine first: the provider must support your exact database and version, including cloud variants such as Azure SQL Database or Amazon RDS/Aurora.

• Prefer managed assemblies: fully managed packages such as Microsoft.Data.SqlClient, Npgsql, and Oracle.ManagedDataAccess.Core avoid native dependencies and simplify cross-platform and container deployment.

• Check platform support: some providers are Windows-only or have partial Linux/macOS support; verify the package’s support matrix for your runtime and OS.

• Feature requirements: confirm support for async (OpenAsync, ExecuteReaderAsync), connection pooling, multiple active result sets, bulk copy, distributed transactions, TLS configuration, and authentication modes such as managed identity or OAuth.

• Distributed transactions are constrained: they are supported on .NET Framework, and on .NET 7+ only on Windows after setting TransactionManager.ImplicitDistributedTransactions = true; they are not supported on Linux/macOS.

• ORM compatibility: if you use EF Core 8, pick a provider with a mature EF Core package (for example Microsoft.EntityFrameworkCore.SqlServer, Npgsql.EntityFrameworkCore.PostgreSQL) — the EF Core provider wraps the underlying ADO.NET provider.

• Performance and scale: compare streaming reader behavior, batching, prepared statements, and pooling defaults; for high-throughput inserts, check whether bulk copy or a native fast-load path exists.

• Licensing, support, and maintenance: prefer packages with active maintainers, security patch history, and a license compatible with your product; community forks may lack long-term support.

• Consistency across environments: choose one provider family for the whole solution so connection strings, parameter handling, and type mapping behave identically in development, tests, and production.

Best Practices and Common Pitfalls

• Use parameterized commands with explicit types for every value, and never concatenate user input into SQL text.

• Send DBNull.Value when a parameter must be a database NULL; a C# null in Value is treated as “not supplied”.

• Dispose connections, commands, readers, and transactions with using blocks so pooled connections return promptly.

• Do not mix providers casually: System.Data.SqlClient and Microsoft.Data.SqlClient have similar names but different namespaces, assembly versions, and feature sets.

• Write provider-agnostic code only when justified: DbProviderFactory adds flexibility but hides vendor-specific features and can make debugging harder, so prefer a direct, well-understood provider when you target a single database.

How It Works

1. You write SQL with placeholders, for example SELECT * FROM Customers WHERE Country = @country.

2. You create a SqlCommand (or another provider’s DbCommand) with that text.

3. For each placeholder you add a parameter to command.Parameters, giving an explicit SqlDbType, and set its Value.

4. The provider transmits the statement as an RPC call (sp_executesql for SQL Server) with the parameters bound separately, rather than concatenating values into the string.

Because the database engine never sees the values inside the statement text, it cannot confuse data with code, and the same prepared plan can be reused for different values.

Key Features

• SQL injection protection: the classic defence for dynamic SQL. Even if a user types ' OR 1=1 --, it is passed as a literal string value, not executed as SQL.

• Plan reuse: SQL Server caches one execution plan for the parameterized statement instead of one plan per distinct literal value, which improves performance and reduces cache pressure.

• Type safety: an explicit SqlDbType (with Size for strings and binary, Precision/Scale for decimals) makes the value’s database type unambiguous and avoids implicit conversions that can hurt performance.

• Readable, maintainable code: no string concatenation, quoting rules or date-format pitfalls.

• Provider-agnostic pattern: every ADO.NET provider implements it through DbCommand.Parameters; SQL Server uses @name, Oracle uses :name (and ODP.NET binds by position unless you set OracleCommand.BindByName = true), OLE DB and ODBC use ? positional markers.

Advantages and Limitations

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=.;Database=Northwind;Integrated Security=true;Encrypt=true;TrustServerCertificate=false;";

// The values come from an untrusted source (e.g. a web request).
string country = "USA";
DateTime registeredFrom = new DateTime(2020, 1, 1);

const string sql = """
    SELECT CustomerId, CompanyName, Country, RegisteredAt
    FROM Customers
    WHERE Country = @country
      AND RegisteredAt >= @registeredFrom
    ORDER BY CompanyName;
    """;

using var connection = new SqlConnection(connectionString);
using var command = new SqlCommand(sql, connection);

// Explicit type, size and precision for every parameter.
command.Parameters.Add("@country", SqlDbType.NVarChar, 50).Value = country;
command.Parameters.Add("@registeredFrom", SqlDbType.DateTime2).Value = registeredFrom;

connection.Open();
using var reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader["CustomerId"]}: {reader["CompanyName"]}");
}

Common Pitfalls

• Never use AddWithValue: it infers the type from the value, which can produce wrong lengths or implicit conversions. Use command.Parameters.Add("@name", SqlDbType.NVarChar, 50) and then set .Value.

• null vs DBNull.Value: a parameter whose Value is C# null is treated as not supplied. Send DBNull.Value when you mean database NULL.

• Parameter direction: the default is Input; for output values from stored procedures use Direction = ParameterDirection.Output and read the value after execution.

• Identifier names cannot be parameters: SELECT * FROM @table is invalid. Validate against a whitelist or use QUOTENAME in T-SQL.

Best Practices

• Prefer parameterized queries or stored procedures over any string-built SQL, including inside SqlCommand.CommandText.

• Keep parameter names and SQL placeholders in sync; a missing or extra parameter raises an error at execution time.

• Reuse the same SqlCommand object with different parameter values when executing repeatedly, rather than rebuilding the string each time.

Core Concept

Optimistic concurrency assumes conflicts are rare. Instead of locking rows (pessimistic), the application reads data along with a version token. When saving changes, the token is included in the WHERE clause. If another transaction modified the row, the token no longer matches, and the update fails safely.

Database Schema Requirement

The table must track changes automatically. A rowversion (formerly timestamp) column is ideal because SQL Server increments it automatically on every modification. - Column Type: rowversion (8 bytes binary). - Behavior: Non-nullable, unique within the database, increments per write. - Alternative: Check all original column values in the WHERE clause, but this is verbose and less reliable for large rows.

Update Logic and Conflict Detection

The UPDATE statement must filter by the primary key AND the original version. The return value of ExecuteNonQuery() indicates success. - Rows Affected = 1: Update succeeded; refresh the version token. - Rows Affected = 0: Conflict detected; the row was changed by another process. - Exception Handling: Network errors or deadlocks throw exceptions; distinguish these from logical conflicts.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public bool UpdateProductName(int id, string name, byte[] currentVersion)
{
    // Use Windows authentication; avoid TrustServerCertificate in production.
    var connectionString = "Server=.;Database=Inventory;Trusted_Connection=True;";
    var sql = "UPDATE Products SET Name = @name WHERE Id = @id AND RowVersion = @version";

    using var connection = new SqlConnection(connectionString);
    using var command = new SqlCommand(sql, connection);

    // Explicit types and sizes prevent type inference and plan cache pollution.
    command.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = name;
    command.Parameters.Add("@id", SqlDbType.Int).Value = id;
    // rowversion is binary(8); pass the original 8-byte token.
    command.Parameters.Add("@version", SqlDbType.RowVersion, 8).Value = currentVersion;

    try
    {
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
        return rowsAffected == 1;
    }
    catch (SqlException ex) when (ex.Number == -2 || ex.Number == 1205)
    {
        // Timeout (-2) or deadlock (1205): treat as a transient conflict and retry.
        throw;
    }
}

Best Practices and Pitfalls

• Refresh Version: On success, retrieve the new rowversion (via OUTPUT clause or SELECT) to keep the client state current.

• Null Handling: Never pass C# null for version parameters; use DBNull.Value if the column allows nulls, though rowversion typically does not.

• Transaction Scope: Wrap the read and update in a transaction if intermediate state consistency is required, but keep transactions short to avoid blocking.

• User Experience: On conflict, reload the data and prompt the user to merge changes rather than silently overwriting.

• Avoid AddWithValue: Always specify SqlDbType explicitly to prevent type inference errors and plan cache pollution.

Overview and Core Mechanism

The class operates by streaming data from a source implementing IDataReader, a DataTable, or an array of DataRow objects directly to the server. You instantiate SqlBulkCopy with an open SqlConnection, configure the destination table via DestinationTableName, and invoke WriteToServer. By default it uses row-level locks; specifying SqlBulkCopyOptions.TableLock acquires a bulk update lock and can enable minimal logging when the database recovery model allows, significantly improving throughput for datasets exceeding thousands of rows.

Configuration Options and Behavior

Key properties control performance and integrity. BatchSize determines how many rows are sent in a single round-trip; setting this too high may cause memory pressure, while too low reduces efficiency. BulkCopyTimeout sets the time limit before the operation aborts. Crucially, SqlBulkCopyOptions alters default behavior: by default, constraints are not checked and triggers are not fired. To enforce these, pass SqlBulkCopyOptions.CheckConstraints or SqlBulkCopyOptions.FireTriggers during construction. Column mapping is handled via ColumnMappings if source column names or indices do not match the destination schema.

Transaction Management

Transaction behavior is a common source of confusion. If you set SqlBulkCopyOptions.UseInternalTransaction, each batch commits independently. This means a failure in a later batch does not roll back earlier batches, violating atomicity. For true all-or-nothing semantics, create an explicit SqlTransaction on the connection and pass it to the SqlBulkCopy constructor. This ensures that if any part of the bulk operation fails, the entire transaction rolls back. Note that distributed transactions require specific OS and framework support (Windows, .NET 7+ with implicit distributed transactions enabled), but local transactions work across platforms.

Code Example

The following example demonstrates a safe bulk insert using an external transaction and explicit column mapping.

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public void BulkInsertData(string connectionString, DataTable dataTable)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                using (var bulkCopy = new SqlBulkCopy(
                    connection,
                    SqlBulkCopyOptions.Default,
                    transaction))
                {
                    bulkCopy.DestinationTableName = "dbo.TargetTable";
                    bulkCopy.BatchSize = 1000;
                    bulkCopy.ColumnMappings.Add("SourceCol", "DestCol");
                    bulkCopy.WriteToServer(dataTable);
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

Best Practices and Common Pitfalls

Always validate data types before calling WriteToServer; mismatches cause runtime exceptions that may leave partial data if transactions are misconfigured. Avoid using SqlBulkCopy for small datasets (under 1000 rows) where standard parameterized commands suffice, as the setup overhead outweighs benefits. Remember that DBNull.Value is required for database NULLs; C# null in a DataRow may be interpreted differently depending on the source type. Finally, never rely on UseInternalTransaction for critical data loads requiring atomicity, and ensure the destination table identity settings (like IDENTITY_INSERT) are configured if you are inserting explicit identity values.

Schema Definition and Generation

• Untyped DataSets are created dynamically at runtime by adding DataTable and DataColumn objects, or by filling a DataSet with a DataAdapter when no schema is known in advance.

• Typed DataSets are generated at design time from an XML Schema (XSD) file or a database schema using Visual Studio or xsd.exe. The generator produces a class derived from DataSet, plus typed DataTable and typed DataRow classes.

• Both share the same underlying disconnected data graph, change tracking, XML schema inference, and DataAdapter update behavior.

Data Access and Type Safety

• Untyped access uses string indexers: row["Name"] returns object, requiring casts such as (string)row["Name"] or row.Field<string>("Name"). A missing column throws IndexOutOfRangeException; a database NULL returns DBNull.Value.

• Typed access uses native properties: row.Name returns string directly. If the underlying value is DBNull, the generated accessor throws StrongTypingException; use the generated IsNameNull() method to test first.

• Typed tables expose typed collection methods, for example users.AddUsersRow("Bob") and users.Users[0].Name, while untyped tables require Rows.Add and string-based indexing.

Compile-Time Safety and Error Behavior

• Renaming a column in the XSD regenerates the typed class, so stale references fail at compile time. Untyped column-name typos compile successfully and fail only at runtime.

• Typed DataSets reduce boilerplate and make data-access code easier to refactor, but they add a generation step and can produce large generated files.

• Untyped DataSets are more flexible for dynamic or ad-hoc schemas whose shape is not known until runtime.

Comparison Table

Best Practices and Modern Context

• DataSets are supported in .NET 8 but are legacy compared with EF Core, Dapper, or POCO/JSON models. Use them mainly for maintaining existing disconnected-data scenarios or when XML schema/diffgram serialization is required.

• Avoid DataSet.RemotingFormat = Binary; it depends on BinaryFormatter, which is obsolete and removed in .NET 9. Prefer XML serialization or modern formats.

• Reference System.Data for DataSet, DataTable, SqlDbType, and DataAdapter. System.Data.Common is the base-provider namespace and is not the primary namespace for typed DataSets.

• For new development, prefer EF Core 8 with Microsoft.Data.SqlClient for SQL Server; reserve typed DataSets for interoperability with legacy components or reporting APIs that require DataTable.

How It Works

1. You fill the DataSet with a SqlDataAdapter, usually inside a using block.

2. The user or business logic edits rows in memory: adding, changing or removing DataRow objects.

3. Each row’s RowState becomes Added, Modified or Deleted; the row also keeps its original and current values.

4. adapter.Update(table) walks the rows and runs the matching command for each state.

5. Each saved row is committed in-memory via AcceptChanges. Do not call table.AcceptChanges() yourself before Update — that marks every row Unchanged so nothing is saved, and after Update it is redundant because the adapter has already done it.

Key Components

• SelectCommand: reads the data; its schema can be used to generate the other commands.

• InsertCommand / UpdateCommand / DeleteCommand: the explicit SqlCommand objects that write changes. Their parameters must map to the source column and, for updates and deletes, to the original values used in the WHERE clause.

• SqlCommandBuilder: generates the three write commands from the SelectCommand at runtime. Convenient for simple single-table scenarios, but it does not handle joins. Its default ConflictOption.CompareKeySearchableValues compares the primary key plus other searchable columns; choose CompareAllValues to compare every column or CompareRowVersion with a rowversion column.

• UpdateBatchSize: batches statements to reduce round trips when saving many rows.

• ContinueUpdateOnError: when true, failed rows are skipped and their errors are recorded in RowError instead of throwing; otherwise the first error stops the update.

Explicit Commands vs. CommandBuilder

Optimistic Concurrency

Because a DataSet is disconnected, the safest pattern is optimistic concurrency: the WHERE clause of UpdateCommand and DeleteCommand compares against the original column values fetched from the server. If another user changed the row first, the command affects zero rows and DBConcurrencyException is thrown for that row. Alternatives are “last writer wins” (match only the primary key) or adding a row version column such as rowversion.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true;";

using var connection = new SqlConnection(connectionString);
connection.Open();

// 1. Fill the DataSet
var adapter = new SqlDataAdapter
{
    SelectCommand = new SqlCommand("SELECT Id, Name, Price FROM Products", connection)
};

var table = new DataTable();
adapter.Fill(table);

// 2. Edit in memory
table.Rows[0]["Price"] = 19.99m;          // Modified
table.Rows.Add(101, "Widget", 5.50m);     // Added
table.Rows[2].Delete();                   // Deleted

// 3. Configure write commands with explicit parameter types
adapter.InsertCommand = new SqlCommand(
    "INSERT INTO Products (Id, Name, Price) VALUES (@id, @name, @price)", connection);
adapter.InsertCommand.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";
var insertName = adapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar);
insertName.Size = 100;
insertName.SourceColumn = "Name";
adapter.InsertCommand.Parameters.Add("@price", SqlDbType.Money).SourceColumn = "Price";

adapter.UpdateCommand = new SqlCommand(
    "UPDATE Products SET Name = @name, Price = @price WHERE Id = @id", connection);
var updateName = adapter.UpdateCommand.Parameters.Add("@name", SqlDbType.NVarChar);
updateName.Size = 100;
updateName.SourceColumn = "Name";
adapter.UpdateCommand.Parameters.Add("@price", SqlDbType.Money).SourceColumn = "Price";
adapter.UpdateCommand.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";

adapter.DeleteCommand = new SqlCommand("DELETE FROM Products WHERE Id = @id", connection);
adapter.DeleteCommand.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";

// 4. Persist changes
adapter.Update(table);   // AcceptChanges is called per saved row

Best Practices and Common Pitfalls

• Never use AddWithValue; always add parameters with an explicit SqlDbType plus Size, Precision or Scale when the type needs them.

• A parameter whose Value is C# null is treated as not supplied; assign DBNull.Value when you mean a database NULL.

• Do not call table.AcceptChanges() before Update — that marks every row Unchanged and nothing is saved.

• Wrap Update in a SqlTransaction when all changes must succeed or fail together; without it, each statement runs in its own implicit transaction.

• For bulk inserts, SqlBulkCopy is far faster than row-by-row DataAdapter updates; note that by default it does not check constraints or fire triggers.

• In .NET Framework-only code, SqlDataAdapter supports distributed transactions through TransactionScope; on .NET 7+ that requires Windows and TransactionManager.ImplicitDistributedTransactions = true, and it is not supported on Linux or macOS.

Understanding DBNull vs C# null

ADO.NET uses System.DBNull to represent missing data in a database context. While C# null indicates a reference type has no object, DBNull.Value indicates a column value does not exist in the row. When passing parameters to SQL Server, assigning C# null to a parameter’s Value property often results in the parameter being treated as unset rather than NULL, unless handled explicitly. This distinction is critical because the database engine expects a specific token for missing data.

Reading NULL Values

When retrieving data, always check for nulls before casting. With SqlDataReader, use IsDBNull or GetFieldValue<T?> where supported. When working with DataTable rows, the Field<T> extension method is convenient. Note that DataRow.Field<int>() throws on DBNull; use Field<int?>() for nullable columns to return a nullable C# type instead of throwing an exception. This ensures robust data retrieval without try-catch blocks around every column access.

Writing NULL Values

When inserting or updating, C# nulls must be converted to DBNull.Value before assignment. A common pattern is using the null-coalescing operator cast to object. Parameters must declare their SqlDbType explicitly to avoid type inference errors that AddWithValue might introduce, especially for nullable types where the provider cannot infer the SQL type from a null reference. This guarantees the database receives the correct metadata for the parameter.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public class NullHandler
{
    public void InsertData(string connectionString, string name, int? age)
    {
        using var connection = new SqlConnection(connectionString);
        using var command = new SqlCommand("INSERT INTO T (Name, Age) VALUES (@Name, @Age)", connection);
        // Explicit types required; convert C# null to DBNull.Value
        command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = name ?? (object)DBNull.Value;
        command.Parameters.Add("@Age", SqlDbType.Int).Value = age.HasValue ? (object)age.Value : DBNull.Value;
        connection.Open();
        command.ExecuteNonQuery();
    }
}

Common Pitfalls

Avoid AddWithValue because it infers types from the value, which fails to distinguish between an empty string and NULL or misinfers nullable integers. Do not call AcceptChanges manually after DataAdapter.Update, as the method handles state tracking automatically. Remember that distributed transactions require explicit configuration on .NET 7+ Windows and are unsupported on Linux, though this rarely impacts simple NULL handling. Always validate input before constructing parameters to prevent injection.

Architectural Relationship

ADO.NET serves as the foundational data access layer in .NET, providing core classes like DbConnection, DbCommand, and DbDataReader within System.Data and provider namespaces such as Microsoft.Data.SqlClient. EF Core exists as a separate library (Microsoft.EntityFrameworkCore) that relies on these ADO.NET classes to communicate with the database. When EF Core processes a query or saves changes, it internally opens an ADO.NET connection, constructs commands, and reads results, shielding the developer from this boilerplate.

Levels of Abstraction

• ADO.NET: Requires explicit SQL writing, manual parameter typing, and result set mapping. It offers granular control over performance and query logic.

• EF Core: Translates LINQ queries into SQL and manages change tracking, migrations, and relationships automatically. It reduces boilerplate but adds a translation layer.

• Interoperability: EF Core exposes the underlying ADO.NET connection via Database.GetDbConnection(), permitting raw ADO.NET commands to run within an EF-managed context when necessary.

Under the Hood Mechanics

When context.SaveChanges() is invoked, EF Core generates SQL based on tracked entity changes and executes them using the configured provider. Parameters are added internally using parameterized commands with explicit types, mirroring secure ADO.NET practices. This abstraction reduces development time but requires developers to understand the generated SQL to avoid performance issues, such as inefficient joins or N+1 query patterns.

Comparison Table

Best Practices

• Select the Right Tool: Use ADO.NET for high-throughput bulk operations or complex stored procedures where SQL tuning is critical. Prefer EF Core for standard CRUD operations and domain-driven design.

• Manage State Carefully: When mixing raw ADO.NET with EF Core on the same connection, ensure both share the same transaction context to maintain data integrity and avoid change tracking conflicts.

• Inspect Generated SQL: Enable EF Core logging to review translated SQL queries, ensuring abstraction does not hide performance bottlenecks.

• Avoid Obsolete Patterns: Do not use System.Data.SqlClient (deprecated in favor of Microsoft.Data.SqlClient) or AddWithValue for parameters, as these compromise type safety and performance.

How Transaction Association Works

The DataAdapter does not manage transactions itself. Instead, you create a transaction via SqlConnection.BeginTransaction() and explicitly assign it to the Transaction property of every SqlCommand object linked to the adapter. If any command lacks this assignment, it executes outside the transaction scope, breaking atomicity. The connection must remain open for the duration of the transaction.

Implementation Steps

1. Open a SqlConnection and call BeginTransaction() to obtain a SqlTransaction.

2. Create SqlCommand objects for insert, update, and delete operations, passing the connection and transaction to their constructors.

3. Map each parameter to a DataTable column using SourceColumn (and set SqlDbType/size explicitly) so the adapter supplies per-row values.

4. Attach the commands to the corresponding properties of the SqlDataAdapter.

5. Execute adapter.Update(dataTable) within a try block.

6. Call transaction.Commit() on success or transaction.Rollback() in the catch block.

Update Behavior and State Management

When Update is called, the adapter examines row states in the DataTable and executes the appropriate command for each changed row. If an exception occurs during this process, the transaction remains open, allowing you to roll back all changes made so far. Crucially, DataAdapter.Update automatically calls AcceptChanges on rows that are successfully saved; you should not invoke AcceptChanges manually after a successful update, as this is redundant and can interfere with change tracking.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public void UpdateWithTransaction(string connectionString, DataTable table)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction())
        {
            using (var adapter = new SqlDataAdapter())
            {
                try
                {
                    // Insert Command
                    using var insertCmd = new SqlCommand(
                        "INSERT INTO Users (Id, Name) VALUES (@id, @name)", connection, transaction);
                    insertCmd.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";
                    insertCmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).SourceColumn = "Name";
                    adapter.InsertCommand = insertCmd;

                    // Update Command
                    using var updateCmd = new SqlCommand(
                        "UPDATE Users SET Name = @name WHERE Id = @id", connection, transaction);
                    updateCmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).SourceColumn = "Name";
                    updateCmd.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";
                    adapter.UpdateCommand = updateCmd;

                    // Delete Command
                    using var deleteCmd = new SqlCommand(
                        "DELETE FROM Users WHERE Id = @id", connection, transaction);
                    deleteCmd.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";
                    adapter.DeleteCommand = deleteCmd;

                    adapter.Update(table);
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

Best Practices and Pitfalls

• Keep Transactions Short: Minimize the time between BeginTransaction and Commit to reduce locking and blocking on the database server.

• Explicit Parameter Types: Always specify SqlDbType and size (e.g., SqlDbType.NVarChar, 100) when adding parameters to avoid type inference issues and plan cache pollution. Never use AddWithValue.

• Map Parameters to Columns: Set SourceColumn on each parameter so the DataAdapter pulls the value from the current DataRow; hard-coding .Value applies the same literal to every row.

• Exception Handling: Always roll back the transaction in a catch block before rethrowing or handling the error; leaving a transaction open can exhaust connection pool resources.

• Connection Scope: Ensure the SqlConnection used for the transaction is the same one passed to the commands and remains open until Commit or Rollback is called.

• Dispose Resources: Wrap SqlConnection, SqlTransaction, SqlDataAdapter, and SqlCommand in using (or using var) statements to release handles promptly.

• Null Handling: If a column allows NULLs, assign DBNull.Value instead of C# null to parameter values to send a database NULL correctly.

The Core Steps

1. Create a connection object and pass it the connection string, which identifies the server, database, credentials, and options.

2. Call Open() before running any command or starting a transaction.

3. Run your commands, readers, or adapters against the open connection.

4. Close the connection with Close() or, better, let a using block dispose it. SqlConnection implements IDisposable, and Dispose() calls Close() internally, so a using block guarantees release even on exceptions.

Key Properties and Methods

• ConnectionString: set via the constructor or the property; must be set before Open().

• State: a ConnectionState value (Closed, Open, Connecting, etc.) you can inspect before calling Open().

• Open() / OpenAsync(): opens the underlying database session. Prefer the async overload in server-side code.

• Close() / CloseAsync(): closes the session and returns the connection to the pool. Calling Close() on an already closed connection is harmless.

• Dispose() / DisposeAsync(): releases resources; using calls this automatically.

Connection Pooling

The .NET data providers pool connections automatically. When you Close() or Dispose() a connection, the underlying session is returned to a pool keyed by the connection string, and a later Open() with an identical string reuses it instead of making a new server round trip. This is why you should open late, use the connection, and close it as soon as possible — pooling makes short-lived connections cheap. Adding Pooling=true (the default) or Max Pool Size=100 (the default) to the connection string tunes this behavior. Note that Close() returns the connection to the pool; it does not destroy it.

Code Example

using System.Data;
using Microsoft.Data.SqlClient;

// Connection string is normally read from IConfiguration, not hard-coded.
string connectionString = "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true;Pooling=true;Max Pool Size=100";

// The using block disposes the connection, which closes it, even on error.
using SqlConnection connection = new(connectionString);

await connection.OpenAsync(); // Open before any command or transaction

using SqlCommand command = new("SELECT Name FROM Customers WHERE Region = @region", connection)
{
    CommandType = CommandType.Text
};

command.Parameters.Add("@region", SqlDbType.NVarChar, 50).Value = "West";

await using SqlDataReader reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    string name = reader.GetString(0); // Column ordinal 0
    Console.WriteLine(name);
}

// No explicit Close() needed: using disposes the connection here.

Best Practices

• Always wrap the connection in a using block so it is disposed even when a command throws.

• Prefer OpenAsync() and ExecuteReaderAsync() in web applications to avoid blocking thread-pool threads.

• Store the connection string in configuration (appsettings.json with IConfiguration, or ConfigurationManager.ConnectionStrings) — never hard-code credentials.

• Open as late as possible and close as early as possible; rely on pooling rather than holding a connection open “for efficiency.”

• Do not share a single open connection across concurrent tasks — SqlConnection is not thread-safe. Create one per logical operation.

• In .NET Framework 4.8, System.Data.SqlClient is the original provider; new code should still use Microsoft.Data.SqlClient, which is the actively supported successor.

Common Pitfalls

• Forgetting to close a connection leaks pooled sessions until garbage collection, which can exhaust the pool and raise timeout errors.

• Calling Open() twice without closing throws an InvalidOperationException.

• Using System.Data.SqlClient in a .NET 8 project pulls in the legacy, unsupported provider; switch the package reference to Microsoft.Data.SqlClient.

Method Purposes and Return Types

• ExecuteNonQuery: Executes INSERT, UPDATE, DELETE, or DDL statements. It returns an int representing the number of rows affected. For statements like CREATE TABLE, the return value is typically -1.

• ExecuteScalar: Executes a query and returns the value of the first column of the first row in the result set as an object. All other rows and columns are ignored. It is ideal for aggregate functions like COUNT or SUM.

• ExecuteReader: Executes a query and returns a SqlDataReader. This provides a high-performance, forward-only, read-only stream of rows. It requires an open connection while reading.

When to Use Each Method

• Use ExecuteNonQuery when modifying data or schema where row counts matter, such as updating user status or deleting records.

• Use ExecuteScalar when you need exactly one value, such as checking if a username exists or retrieving an identity key after an insert.

• Use ExecuteReader when processing multiple rows, such as listing orders or generating a report, especially when memory efficiency is critical compared to loading a DataSet.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public class CommandExamples
{
    public void RunExamples(string connectionString)
    {
        using var connection = new SqlConnection(connectionString);
        connection.Open();

        // ExecuteNonQuery: Update data
        using (var updateCmd = new SqlCommand(
            "UPDATE Users SET IsActive = 0 WHERE LastLogin < @date", connection))
        {
            updateCmd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now.AddYears(-1);
            int rowsAffected = updateCmd.ExecuteNonQuery();
            Console.WriteLine($"Rows updated: {rowsAffected}");
        }

        // ExecuteScalar: Get count
        using (var countCmd = new SqlCommand(
            "SELECT COUNT(*) FROM Users WHERE IsActive = @active", connection))
        {
            countCmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;
            object result = countCmd.ExecuteScalar();
            int count = result != DBNull.Value ? (int)result : 0;
            Console.WriteLine($"Active users: {count}");
        }

        // ExecuteReader: Stream data
        using (var readCmd = new SqlCommand(
            "SELECT Id, Name FROM Users WHERE IsActive = @active", connection))
        {
            readCmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;
            using (var reader = readCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    Console.WriteLine($"{id}: {name}");
                }
            }
        }
    }
}

Best Practices and Common Pitfalls

• Dispose Resources: Always wrap SqlDataReader and SqlCommand in using statements to prevent connection leaks.

• Handle Nulls: ExecuteScalar returns C# null if the result set is empty, and DBNull.Value if the first column of the first row contains a database NULL; check before casting to avoid InvalidCastException.

• Parameter Types: Specify SqlDbType explicitly when adding parameters instead of using AddWithValue to prevent type inference issues and plan cache pollution.

• Connection State: Ensure the connection is open before calling any execute method; otherwise, an InvalidOperationException is thrown.

• Streaming: Prefer ExecuteReader over DataAdapter for large datasets to reduce memory pressure, as it streams rows rather than loading them all into memory.

• Async I/O: For scalable server code, prefer the async counterparts ExecuteNonQueryAsync, ExecuteScalarAsync, and ExecuteReaderAsync to avoid blocking threads.

Core Purpose and Abstraction

These interfaces reside in the System.Data namespace and define the minimum capabilities required for any ADO.NET provider implementation. IDbConnection manages the lifecycle of a database session, including opening and closing connections, creating transactions, and generating command objects. IDbCommand represents a SQL statement or stored procedure, handling command text, execution timeouts, parameter collections, and execution methods such as ExecuteReader and ExecuteScalar. This abstraction allows the same business logic to operate across SQL Server, PostgreSQL, Oracle, or SQLite without rewriting query execution code.

Interface Contracts and Members

• IDbConnection: Exposes properties like ConnectionString, State, and Database, alongside methods such as Open(), Close(), CreateCommand(), and BeginTransaction(). It ensures that any provider offers a consistent way to establish a secure session.

• IDbCommand: Defines CommandText, CommandType, Transaction, and Parameters. It standardizes how queries are prepared and executed, returning IDataReader for streaming results or scalar values for single-value queries.

• IDataParameter: Commands use this interface for parameters, ensuring values are passed safely to prevent SQL injection, though provider-specific types (like SqlDbType) often require casting to the concrete provider type.

Provider Implementations

Concrete classes implement these interfaces to bridge the gap between the generic contract and the specific database protocol. For example, Microsoft.Data.SqlClient.SqlConnection implements IDbConnection for SQL Server, while Npgsql.NpgsqlConnection does the same for PostgreSQL. When an application instantiates a connection via a factory pattern or dependency injection, it receives the interface reference. This polymorphism means swapping a database engine often requires only changing the configuration string and the concrete type instantiation, leaving the query logic untouched. Note that because IDbCommand exposes no asynchronous members and its Parameters collection is typed as IDataParameterCollection, provider-specific casting (for example to SqlCommand) is needed when explicit parameter types such as SqlDbType must be set.

Best Practices and Pitfalls

• Avoid Hardcoding Providers: Use DbProviderFactories or dependency injection to resolve concrete types, keeping the code truly provider-agnostic.

• Handle Nulls Correctly: A C# null assigned to a parameter value is treated as “not supplied” by ADO.NET. Use DBNull.Value to send a database NULL.

• Dispose Resources: Always wrap connections and commands in using statements to ensure network resources and handles are released promptly.

• Prefer DbCommand for New Code: In .NET 8, System.Data.Common.DbCommand is generally recommended over IDbCommand because it supports asynchronous methods like ExecuteReaderAsync, whereas IDbCommand only offers synchronous execution.

• Security: Never concatenate strings into CommandText. Always use parameters to prevent SQL injection, even when coding against interfaces.

Return Types and Behavior

ExecuteScalar ignores additional rows and columns. ExecuteReader allows access to metadata and multiple result sets if enabled. ExecuteXmlReader transforms the rowset into XML format on the server side before streaming.

Typical Use Cases

• ExecuteScalar: Best for aggregate queries such as COUNT(*), SUM(), or retrieving an identity value after an insert. It minimizes network traffic when only one value is needed.

• ExecuteReader: Suitable for processing large result sets row-by-row without loading the entire dataset into memory. It is preferred over DataAdapter for read-only streaming scenarios.

• ExecuteXmlReader: Used when legacy systems consume XML directly from the database. Modern architectures typically prefer retrieving standard rows and serializing to JSON or XML in the application layer.

Connection and Resource Management

All three methods require an open SqlConnection. ExecuteReader and ExecuteXmlReader hold the connection open for the duration of the reader’s lifecycle. Attempting to execute another command on the same connection while a reader is open will throw an exception unless Multiple Active Result Sets (MARS) is enabled in the connection string (MultipleActiveResultSets=true). Always dispose of readers and connections promptly to return connections to the pool.

Best Practices and Pitfalls

• Null Handling: ExecuteScalar returns null if the result set is empty, but returns DBNull.Value if the first column of the first row contains a SQL NULL. Cast carefully to avoid InvalidCastException.

• Parameter Types: Always declare parameters with explicit types, e.g., command.Parameters.Add("@id", SqlDbType.Int).Value = userId. Never use AddWithValue, as it infers types and can degrade query plan performance.

• Disposal: Wrap SqlDataReader and XmlReader in using statements. Failing to close them explicitly leaks connections and may exhaust the connection pool.

• XML Deprecation: While ExecuteXmlReader is functional, reliance on server-side XML generation is considered legacy. Prefer standard rowsets for better testability and flexibility in modern .NET development.

How It Works

• Command setup: create a SqlConnection, open it, then create a SqlCommand whose CommandText is the procedure name (e.g. dbo.GetOrdersByCustomer) and whose CommandType is CommandType.StoredProcedure. You do not write EXEC or CALL; the provider handles that.

• Parameters: each parameter is added with command.Parameters.Add("@name", SqlDbType.Type). Set .Size for variable-length types such as NVarChar and VarChar, and .Precision/.Scale for Decimal, so the provider sends a correct definition. Then assign .Value. Sending C# null means “not supplied”; send DBNull.Value when you want a database NULL.

• Direction: the default is ParameterDirection.Input. Use Output or InputOutput for values the procedure returns through parameters, and read .Value after execution (cast from object).

• Execution method: ExecuteNonQuery for procedures that do not return a result set (it returns rows affected), ExecuteReader for a result set streamed through a SqlDataReader, and ExecuteScalar for a single scalar such as a count or identity value.

Key Parameters and Behavior

Code Example

First, the procedure in SQL Server:

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT,
    @FromDate   DATETIME,
    @Status     NVARCHAR(20) = NULL
AS
    SELECT  OrderId, OrderDate, TotalAmount
    FROM    dbo.Orders
    WHERE   CustomerId = @CustomerId
      AND   OrderDate >= @FromDate
      AND   (@Status IS NULL OR Status = @Status)
    ORDER BY OrderDate;

Then the C# call:

using System;
using System.Collections.Generic;
using System.Data;              // SqlDbType, CommandType
using System.Threading.Tasks;   // Task
using Microsoft.Data.SqlClient; // SqlConnection, SqlCommand, SqlDataReader

public static async Task<List<Order>> GetOrdersAsync(
    string connectionString, int customerId, DateTime fromDate, string? status)
{
    var orders = new List<Order>();

    // Connection is opened before the command executes and disposed on exit
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand("dbo.GetOrdersByCustomer", connection)
    {
        CommandType = CommandType.StoredProcedure
    };

    // Explicit types and sizes; never use AddWithValue
    command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
    command.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = fromDate;

    // C# null means "not supplied"; DBNull.Value sends an explicit SQL NULL
    var statusParam = command.Parameters.Add("@Status", SqlDbType.NVarChar, 20);
    statusParam.Value = string.IsNullOrEmpty(status) ? DBNull.Value : status;

    try
    {
        // One open reader per connection; stream the rows while reading
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            orders.Add(new Order
            {
                // IsDBNull avoids InvalidCastException on NULL columns
                OrderId = reader.GetInt32(0),
                OrderDate = reader.GetDateTime(1),
                TotalAmount = reader.IsDBNull(2) ? 0m : reader.GetDecimal(2)
            });
        }
    }
    catch (SqlException ex)
    {
        // Log the error; include procedure name and state for diagnostics
        Console.WriteLine($"SQL error {ex.Number}: {ex.Message}");
        throw;
    }

    return orders;
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
}

Best Practices and Common Pitfalls

• Always parameterize: never build the procedure call or its arguments with string concatenation; that defeats the injection protection the provider gives you for free.

• Prefer the async methods (OpenAsync, ExecuteReaderAsync, ReadAsync) in web or UI code so blocked database calls do not tie up threads.

• Keep the connection short-lived: open it last, close it first, and rely on using rather than manual Close.

• Match types exactly: an AddWithValue-free, explicitly typed parameter avoids implicit conversions that can defeat indexes (for example NVarChar compared against a VarChar column).

• Handle NULLs in both directions: send DBNull.Value for SQL NULL, and check reader.IsDBNull(i) before reading a nullable column.

• Do not leave connections open across readers: nest a second command inside the loop only if you enabled MARS, or open a second connection.

Core Concept

A prepared statement defines the SQL structure once, allowing the database engine to compile an execution plan before specific values are bound. When parameters are used, the server caches the plan and reuses it for subsequent executions with different values. This contrasts with string concatenation, where every variation generates a new plan and exposes the application to injection risks.

Implementation in ADO.NET

To use prepared statements, create a SqlCommand with parameter placeholders (e.g., @Id) in the command text. Define each parameter explicitly via the Parameters collection, specifying the SqlDbType and size where applicable. ADO.NET does not prepare statements automatically; you must call SqlCommand.Prepare() explicitly to force the server to compile the plan before execution, which can optimize performance for loops or batch operations. Note that Prepare() consumes an extra network round-trip, so it is only beneficial when the same command is executed many times.

Security and Performance Benefits

• SQL Injection Prevention: Parameters are escaped automatically by the provider, preventing malicious input from altering query structure.

• Plan Reuse: The database caches the execution plan, reducing compilation overhead for frequently run queries.

• Type Safety: Explicit typing ensures data matches column definitions, reducing conversion errors and implicit casts.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

public void GetUser(int userId, string connectionString)
{
    const string sql = "SELECT Name, Email FROM Users WHERE Id = @Id AND Status = @Status";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(sql, connection))
        {
            // Explicit type and size for parameters
            command.Parameters.Add("@Id", SqlDbType.Int).Value = userId;
            command.Parameters.Add("@Status", SqlDbType.NVarChar, 10).Value = "Active";

            // Optional: Explicitly prepare for repeated execution
            // command.Prepare();

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader["Name"]);
                }
            }
        }
    }
}

Best Practices and Pitfalls

• Avoid AddWithValue: Never use Parameters.AddWithValue as it infers types incorrectly (e.g., mapping strings to NVarChar(MAX)), hurting performance and preventing plan reuse.

• Specify String Size: Always define the size for VarChar or NVarChar parameters to match the column schema, avoiding unnecessary memory allocation.

• Handle Nulls: Use DBNull.Value instead of C# null when a parameter represents a database NULL, since a null value is treated as an unset parameter.

• Dispose Resources: Wrap SqlConnection, SqlCommand, and SqlDataReader in using statements to prevent connection leaks and ensure timely cleanup.

• Explicit Prepare: Use Prepare() only when executing the same command many times in a single session; otherwise the extra round-trip is not worth it, and SQL Server can still reuse plans for parameterized queries without explicit preparation.

How It Works

The DataAdapter never holds a connection open between operations. Its behaviour follows a simple rule:

1. You assign command objects to its four command properties: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand.

2. You assign a connection to each of those commands (the DataAdapter reads the connection from the commands, and all commands should share one connection).

3. When you call Fill, the adapter checks the connection state:

4. If it is closed, it opens it, fetches the data, and closes it when done.

5. If it is open, it uses it as-is and leaves it open afterwards.

6. When you call Update, the same open/close logic applies while it walks the changed rows and executes the matching command for each DataRowState (Added, Modified, Deleted).

Because the adapter inspects state rather than blindly closing, you can safely wrap several adapter calls inside one open connection or inside one transaction when you need them to run as a unit.

Key Features

• Connection ownership belongs to the command, not the adapter. The adapter has no Connection property of its own; it uses SelectCommand.Connection and the others.

• Automatic open/close on Fill. This is why Fill works even if you never call connection.Open().

• Automatic open/close on Update. The adapter opens the connection, submits the per-row changes, and closes it again.

• Transaction awareness. If you set SelectCommand.Transaction (and the same transaction on the other commands), the adapter enlists in it instead of starting an implicit one.

• AcceptChanges after each row is saved. DataAdapter.Update calls AcceptChanges on each row it successfully writes, so those rows are no longer marked as changed. Do not call AcceptChanges yourself after Update — that would discard any remaining changes and is unnecessary.

• Table and schema mapping. TableMappings and MissingSchemaAction control how result sets land in DataSet tables, but they do not affect connection handling.

Explicitly Managing the Connection vs. Letting the Adapter Do It

The practical rule: let the adapter manage the connection for simple one-shot scenarios, and open it yourself (inside a using block) when you need a transaction, several commands, or multiple adapter calls to share the same connection.

Best Practices and Common Pitfalls

• Always wrap the connection in a using even when the adapter closes it; the using guarantees disposal if an exception occurs before Fill or Update runs.

• Share one connection object across all four commands. Using different connections for SelectCommand and UpdateCommand is legal but defeats connection pooling efficiency and breaks transaction enlistment.

• Enlist commands in a transaction explicitly when Update must be all-or-nothing: assign the same SqlTransaction to InsertCommand, UpdateCommand and DeleteCommand, and open the connection before creating the transaction.

• Do not call AcceptChanges after Update. The adapter already did it per saved row; calling it again silently discards any rows that failed to save.

• Remember that Fill leaves an already-open connection open. If you open it, you own closing it.

How It Works

• Connection Management: If the connection is closed when Fill is called, the method opens it, reads the data, and closes it. If the connection is already open, Fill leaves it open when finished so your surrounding code retains control.

• Data Retrieval: Internally, it executes the command and uses an IDataReader to stream rows into the target object, mapping each result row to a DataRow without holding the entire raw result set in memory separately.

• Return Value: It returns an int indicating the number of rows successfully added to or refreshed in the target object.

Schema Inference and Table Mapping

• Automatic Schema: If the target DataTable does not exist in the DataSet, Fill creates it based on the column names and data types present in the result set.

• Existing Schema: If the table already exists, incoming data is merged into existing rows that match the primary key, or appended as new rows if no key is defined.

• Primary Keys: By default, Fill does not retrieve primary key or constraint information. Use MissingSchemaAction.AddWithKey or call FillSchema beforehand if you need constraint metadata for later updates.

Handling Multiple Result Sets

• DataSet: If the SelectCommand returns multiple result sets, Fill creates multiple tables in the DataSet, naming them Table, Table1, Table2, and so on.

• DataTable: When filling a single DataTable, only the first result set is consumed; any subsequent results returned by the command are ignored.

Best Practices and Common Pitfalls

• Connection State: Do not assume the connection is closed after Fill returns; verify the state if you opened it manually before calling the method.

• NULL Values: Accessing a database NULL with DataRow.Field<int>() throws an exception; use Field<int?>() for nullable columns to handle DBNull safely.

• Schema Overhead: Calling FillSchema adds an extra round trip to the database; prefer passing MissingSchemaAction.AddWithKey directly to Fill when key information is required.

• Resource Disposal: Wrap SqlConnection, SqlCommand, and SqlDataAdapter in using statements to ensure network resources and handles are released promptly.

How It Works

1. DataAdapter.Fill loads rows into a DataTable; each row starts with RowState = Unchanged.

2. Your code edits the rows: adding, modifying or deleting them changes RowState accordingly.

3. DataAdapter.Update(table) walks the rows in the order they appear in the table’s row collection, matches each row’s RowState to one of the three action commands, fills the command parameters from the row, and executes the command.

4. For each row it successfully saves, Update calls AcceptChanges on that row, resetting RowState to Unchanged. You should not call AcceptChanges yourself after Update, because that would erase the change tracking that Update has already processed.

5. Rows whose state is Unchanged are skipped, and DataRow values are sent as parameters, which prevents SQL injection.

Key Components

• SelectCommand: the query used by Fill; its result set defines the table schema and, when the source column names match, the default SourceColumn mappings for the other commands.

• InsertCommand / UpdateCommand / DeleteCommand: one command per kind of change. Any command you leave null means that kind of change cannot be saved.

• SqlParameter.SourceColumn: tells the adapter which DataTable column supplies the parameter’s value. Without it, the parameter gets no value from the row.

• SqlParameter.SourceVersion: defaults to Current, which is right for INSERT/UPDATE values. For an optimistic-concurrency WHERE clause that compares against the original values, set SourceVersion = DataRowVersion.Original.

• SqlCommandBuilder: a convenience that generates the three action commands from a single-table SelectCommand at runtime. It is fine for quick, single-table scenarios, but hand-written commands give you control over concurrency, stored procedures and performance, and are preferred in production code.

• DataTable.GetChanges(): returns a copy containing only changed rows, useful for validating or sending a subset to a server before calling Update on the original table.

• DataAdapter.ContinueUpdateOnError: when true, a failing row is skipped (its error is recorded in RowError) and the remaining rows are still saved; when false (default), the first error throws and the update stops.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        var connectionString = "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true;";
        using var connection = new SqlConnection(connectionString);

        using var adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand("SELECT Id, Name, Price FROM Products", connection);

        adapter.InsertCommand = new SqlCommand(
            "INSERT INTO Products (Name, Price) VALUES (@name, @price)", connection);
        adapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100).SourceColumn = "Name";
        var insPrice = adapter.InsertCommand.Parameters.Add("@price", SqlDbType.Decimal);
        insPrice.Precision = 10; insPrice.Scale = 2; insPrice.SourceColumn = "Price";

        adapter.UpdateCommand = new SqlCommand(
            "UPDATE Products SET Name = @name, Price = @price WHERE Id = @id", connection);
        adapter.UpdateCommand.Parameters.Add("@name", SqlDbType.NVarChar, 100).SourceColumn = "Name";
        var updPrice = adapter.UpdateCommand.Parameters.Add("@price", SqlDbType.Decimal);
        updPrice.Precision = 10; updPrice.Scale = 2; updPrice.SourceColumn = "Price";
        adapter.UpdateCommand.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";

        adapter.DeleteCommand = new SqlCommand("DELETE FROM Products WHERE Id = @id", connection);
        adapter.DeleteCommand.Parameters.Add("@id", SqlDbType.Int).SourceColumn = "Id";

        var table = new DataTable();
        adapter.Fill(table);

        // Modified: change an existing row
        if (table.Rows.Count > 0)
        {
            table.Rows[0]["Price"] = 24.95m;
        }

        // Added: insert a new row (Id 0 is a placeholder; the INSERT handles identity)
        table.Rows.Add(0, "New Gadget", 19.95m);

        // Deleted: mark an existing row for removal
        if (table.Rows.Count > 1)
        {
            table.Rows[1].Delete();
        }

        adapter.Update(table); // saves each row and calls AcceptChanges on the saved rows
    }
}

Best Practices and Common Pitfalls

• Always use parameters with explicit types and sizes, never string concatenation or AddWithValue, which infers types from values and can cause bad query plans or conversion errors.

• Set SourceColumn (and SourceVersion for original values) on every parameter of the action commands; a missing mapping is a common reason an Update silently writes nothing or fails.

• Do not call AcceptChanges before Update: that marks every row Unchanged, so nothing is saved. Call RejectChanges only to discard local edits.

• Guard against lost updates: in a multi-user system, add the original column values to the WHERE clause of UpdateCommand and DeleteCommand and check the returned row count; a DBConcurrencyException means the row changed since you loaded it.

• Wrap related updates in a SqlTransaction when a DataSet spans several tables, so the whole batch commits or rolls back together.

• Remember the disconnected trade-off: this pattern is ideal for two-tier, occasionally connected clients; for server-side or web work, prefer EF Core 8 or direct SqlCommand calls, which avoid holding a full result set in memory.

What Is a DataSet Schema

The schema is the blueprint held within a DataSet object. It consists of a collection of DataTable objects, each containing DataColumn definitions and Constraint objects such as UniqueConstraint and ForeignKeyConstraint. Unlike a database schema, it exists entirely in memory, which allows applications to manipulate data offline and later reconcile changes. Hierarchical data is supported through DataRelation objects that link parent and child tables on matching columns, enabling navigation such as GetChildRows.

Approaches to Defining a Schema

There are three main ways to establish a DataSet schema:

Programmatic definition gives full control over the structure. XML interoperability lets structure survive transmission, which made DataSet a common choice in older web services. Database-derived schemas reduce duplication but couple the in-memory shape to the current database design.

How the Schema Is Used During Data Operations

Once defined, the schema governs how data can be changed:

• Constraint enforcement: With EnforceConstraints set to true (the default), foreign-key violations, unique-constraint breaches, and nulls in non-nullable columns throw exceptions as changes are made, not when they are sent to the database.

• Relationship navigation: DataRelation objects let you traverse parent and child rows without writing joins.

• Change tracking: The schema underpins DataRow.RowState tracking, so DataAdapter.Update can apply the correct INSERT, UPDATE, and DELETE operations.

• Typed access: A typed DataSet generated from the schema exposes strongly typed properties such as row.ColumnName with compile-time checking; untyped DataSets use row["ColumnName"] and suit dynamic scenarios where the structure is not known at compile time.

Best Practices and Pitfalls

• Avoid binary serialization: Do not set DataSet.RemotingFormat = Binary; it relies on BinaryFormatter, which is obsolete and removed in .NET 9. Use XML or JSON instead.

• Nullable handling: DataRow.Field<int>() throws on DBNull; use Field<int?>() for columns that allow database NULLs.

• Memory overhead: A DataSet carries significantly more memory overhead than a DbDataReader. Use it only when disconnected manipulation, multiple related tables, or change tracking is required.

• Bulk loads: Temporarily set EnforceConstraints to false when bulk-loading interdependent tables to avoid ordering issues, then re-enable it.

• Untrusted schemas: When loading a schema from an external source via ReadXmlSchema, validate the XSD first to reduce the risk of denial-of-service attacks via complex type definitions.

Change Tracking and Constraints

A DataSet maintains state for every row to support updates and rollbacks, which consumes memory and CPU. When loading large volumes of data where immediate validation is unnecessary, disable constraint checking temporarily. Set dataSet.EnforceConstraints = false before calling DataAdapter.Fill and re-enable it afterward. This prevents the engine from validating foreign key relationships and unique constraints on every inserted row. Note that DataAdapter.Update automatically calls AcceptChanges on each saved row, so manual calls afterward are redundant and should be avoided.

Schema Handling and Fill Options

Inferring schema dynamically during a fill operation adds significant latency. If the schema is known, define the DataTable structure beforehand and set adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey or Add appropriately. Avoid using FillSchema unless the structure is truly unknown, as it executes an additional query or derives metadata unnecessarily. When passing data across tiers, prefer XmlWriteMode.IgnoreSchema if the receiver already knows the contract, reducing payload size. Remember that DataRow.Field<int>() throws on database NULLs; use Field<int?>() for nullable columns to prevent runtime exceptions during processing.

Serialization and Remoting

Historically, DataSet supported binary serialization via RemotingFormat = Binary, but this relies on BinaryFormatter. BinaryFormatter is obsolete and removed in .NET 9, making this approach insecure and unsupported for modern applications. Instead, serialize data using JSON or explicit XML schemas (XmlWriteMode.WriteSchema) only when necessary. For high-performance internal communication, consider passing lightweight DTOs (Data Transfer Objects) rather than full DataSet graphs, which carry substantial metadata overhead.

Architectural Alternatives

Often the best optimization is avoiding DataSet entirely. The table below compares the common disconnected and connected options:

Use DataTable when relational features like multiple tables and relations are not required, as it reduces object graph complexity. For forward-only read scenarios, DbDataReader provides the lowest memory footprint and fastest access since it streams data without caching. If you need disconnected data but want modern performance, map results to POCOs using EF Core or a micro-ORM like Dapper. Reserve DataSet for scenarios requiring complex offline manipulation, multiple related tables, or legacy integration where its specific change-tracking semantics are required.

Common Pitfalls

Do not treat a DataSet as a generic collection for simple lists; the overhead is unjustified. Never assume DataRow values are non-null without checking or using nullable generics. Avoid holding DataSet instances in long-lived static caches, as they prevent garbage collection of large object heaps. Finally, ensure distributed transaction settings are configured correctly if using DataSet updates within a TransactionScope, noting that implicit distributed transactions require explicit enablement on .NET 7+ Windows environments.

Connection Pooling

• Pooling is enabled by default in Microsoft.Data.SqlClient; connections with identical connection strings share one pool.

• The default Max Pool Size is 100 per connection string. If concurrent demand exceeds this, requests queue waiting for a free connection, which looks like a hang.

• Tune pooling in the connection string: Min Pool Size=5; Max Pool Size=200; Connection Lifetime=0. Set Min Pool Size only after measuring steady-state load, since it pre-allocates connections.

• On Linux/macOS, pooling behaves the same as on Windows for Microsoft.Data.SqlClient; the legacy System.Data.SqlClient is in maintenance mode and should not be used for new development in .NET 8.

Async I/O and Streaming Reads

• Use OpenAsync, ExecuteReaderAsync, and ReadAsync so threads return to the pool while waiting on the database. Never block with .Result or .Wait(), which causes thread-pool starvation under load.

• SqlDataReader streams rows one at a time instead of buffering the whole result set. Prefer it over filling a DataSet or DataTable when you process rows sequentially.

• Set CommandTimeout explicitly (default is 30 seconds) so runaway queries fail fast instead of holding connections indefinitely.

Efficient Batch and Bulk Operations

• For bulk inserts, use SqlBulkCopy instead of executing one INSERT per row; it moves data in batches with far fewer round trips.

• By default SqlBulkCopy does not check constraints or fire triggers; pass SqlBulkCopyOptions.CheckConstraints or FireTriggers when you need them.

• SqlBulkCopy with UseInternalTransaction commits each batch separately. For all-or-nothing atomicity, create an external SqlTransaction and pass it to the constructor.

Common Pitfalls

• AddWithValue: never use it; it forces type inference and can cause plan-cache bloat. Add parameters with an explicit SqlDbType, for example command.Parameters.Add("@id", SqlDbType.Int).Value = id;.

• Leaked connections: always wrap SqlConnection, SqlCommand, and SqlDataReader in using (or await using) so pooling can reuse them.

• Connection string drift: even a trailing space creates a separate pool. Store the string in one configuration location.

• Long transactions: keep transactions short; an open transaction holds its connection and blocks cleanup until committed.

• DataTable overuse: loading large result sets into memory per request increases GC pressure; stream with SqlDataReader when possible.

What It Controls

Every isolation level is defined by which of the following concurrency side effects it permits or prevents:

• Dirty read: a transaction reads a row that another transaction has modified but not yet committed; if that transaction rolls back, the reader has data that never existed.

• Non-repeatable read: a transaction reads the same row twice and gets different values because another transaction updated or deleted the row and committed in between.

• Phantom read: a transaction re-runs a query with the same predicate and finds extra or missing rows because another transaction inserted or deleted matching rows and committed in between.

• Lost update and write skew: rarer anomalies that stricter levels or explicit locking also guard against.

The Isolation Levels

The System.Data.IsolationLevel enum exposes these members. SQL Server maps them to its own locking and versioning behaviour as shown.

How It Is Applied in ADO.NET

The level is chosen when the transaction starts, and it applies to every command enlisted in that transaction:

• SqlConnection.BeginTransaction(IsolationLevel) starts a transaction with the requested level on a single connection.

• TransactionScope uses the ambient transaction’s level; the default for a SQL Server ambient transaction is Read Committed unless you pass a TransactionOptions structure with an explicit System.Transactions.IsolationLevel.

• The level is a request, not a command: the provider and database engine translate it to their own semantics, and some backends silently coerce an unsupported level to the closest supported one.

Choosing a Level

• Default to Read Committed for typical OLTP work; it is the SQL Server default and a sound balance of correctness and throughput.

• Use Snapshot when read contention is hurting performance and your workload can tolerate readers seeing a slightly older, committed view of the data; watch for the tempdb version-store overhead and update conflicts.

• Use Repeatable Read or Serializable only when the business logic genuinely requires those guarantees, because the extra locking scales poorly under high concurrency.

• Avoid Read Uncommitted except for deliberate, throwaway diagnostic reads; reading uncommitted data can produce results that are impossible under any correct schedule.

Core Difference

• Pessimistic: assumes conflicts are likely, so it acquires a lock as soon as data is read and holds it until the transaction commits or rolls back. Other sessions that try to read or write the locked rows must wait.

• Optimistic: assumes conflicts are rare, so it reads data without taking locks. Before writing, it verifies that the data has not changed since it was read, typically using a version number, timestamp, or full row comparison.

Pessimistic Concurrency Control

• Implemented with explicit transactions and locking, such as SELECT ... WITH (UPDLOCK, HOLDLOCK) in SQL Server or a transaction with an appropriate IsolationLevel.

• In ADO.NET you would open a SqlConnection, begin a SqlTransaction, read the row within that transaction, and keep the connection open until the update completes.

• Advantages: conflicts are impossible while the lock is held; simple mental model.

• Limitations: reduces concurrency, can cause blocking and deadlocks, and holding locks across a round trip (read, user edits, save) is usually impractical in disconnected web applications.

Optimistic Concurrency Control

• EF Core 8 implements optimistic checking by default: UPDATE and DELETE statements include all original property values in the WHERE clause, so a row changed by another session yields zero rows affected and throws a DbUpdateConcurrencyException. For efficient conflict detection, configure an explicit concurrency token such as a SQL Server rowversion/timestamp column with .IsRowVersion().

• If no row matches (because another session changed it), EF Core throws a DbUpdateConcurrencyException, which your code can catch and resolve by retrying, refreshing the values, or notifying the user.

• In raw ADO.NET you do the same manually: include the original RowVersion in the WHERE clause and check RowsAffected to detect a conflict.

• Advantages: no locks held during user think time, scales well for web workloads, no deadlocks.

• Limitations: conflicts do happen and must be handled; requires a version column or equivalent.

Comparison

Choosing Between Them

• Use optimistic by default in ASP.NET and most line-of-business applications, because requests are short-lived and holding database locks across HTTP requests would exhaust connections.

• Use pessimistic only when contention is genuinely high, when a lost update would be catastrophic, or when a multi-step operation must be serialized end to end within a single database transaction.

• In EF Core, mark a column as a concurrency token with .IsConcurrencyToken() or .IsRowVersion(); without a token, EF Core uses all original values in the WHERE clause, which is less efficient.

Common Pitfalls

• Not handling DbUpdateConcurrencyException: catching it and blindly retrying can overwrite another user’s changes; decide on a resolution strategy (client wins, store wins, or merge).

• Using AddWithValue or omitting the version parameter type: always add parameters with an explicit SqlDbType, and for rowversion use SqlDbType.Timestamp with the correct size.

• Assuming optimistic means no conflicts: you must still design the UI and API to surface and resolve them.

• Holding pessimistic locks across user interaction: this blocks other users and can exhaust the connection pool; keep such transactions as short as possible.

How Batch Updates Work

When UpdateBatchSize is set to a value greater than 1, the SqlDataAdapter groups that many INSERT, UPDATE, or DELETE commands before sending them to the server in one round-trip, which reduces network latency compared to executing each row change individually. Setting it to 1 disables batching; setting it to 0 lets the provider create the largest batch it can. If an error occurs within a batch, the outcome depends on the transaction context: without an explicit transaction, previously successful batches stay committed while the failing batch is rolled back, so the operation is not all-or-nothing.

Manual Batching with Transactions

For large datasets, loading everything into a DataSet may consume too much memory. A leaner strategy is to read data in chunks with a SqlDataReader, build SqlCommand objects explicitly with parameterized SQL, and execute them inside a single SqlTransaction, committing only after every chunk succeeds. This avoids DataSet change-tracking overhead, keeps the working set small, and lets you apply custom validation or retry logic between batches. It is also the fallback when a provider’s data adapter does not support batching at all.

Bulk Operations vs. Batch Updates

SqlBulkCopy is optimized for high-volume inserts, not for updating existing rows. The usual pattern for bulk updates is to bulk copy the new data into a staging table and then run a single set-based UPDATE or MERGE statement that joins the staging table to the target. Note that by default SqlBulkCopy does not check constraints or fire triggers; use SqlBulkCopyOptions.CheckConstraints or SqlBulkCopyOptions.FireTriggers when those behaviors are required.

Best Practices and Pitfalls

• Explicit parameters: always add parameters with an explicit SqlDbType and size, e.g. command.Parameters.Add("@id", SqlDbType.Int).Value = id; never use AddWithValue, whose type inference can cause conversion errors and plan-cache pollution.

• Null handling: a parameter whose Value is C# null is treated as not supplied; send DBNull.Value when you mean a database NULL.

• Transactions: wrap batch operations in a local SqlTransaction for atomicity. Distributed transactions are supported on .NET Framework, and on .NET 7+ only on Windows after setting TransactionManager.ImplicitDistributedTransactions = true; they are not supported on Linux or macOS.

• AcceptChanges: do not call AcceptChanges manually after DataAdapter.Update — Update calls it automatically on each row it saves.

• Batch size tuning: start with a moderate UpdateBatchSize (for example 10–100) and measure; larger batches improve throughput only up to the point where lock escalation and memory pressure dominate.

Core XML Methods on DataSet and DataTable

• WriteXml: writes the current data, and optionally the schema, to a file, Stream, TextWriter, or XmlWriter.

• ReadXml: reads XML data into the DataSet, optionally inferring the schema or reading it from an embedded XSD.

• WriteXmlSchema: writes only the XSD schema definition for the tables, columns, and relations.

• ReadXmlSchema: reads only the schema, preparing the DataSet structure before the data payload arrives.

• GetXml and GetXmlSchema: return the data and the schema as XML strings without writing to a file or stream.

Each method has overloads accepting a file path, Stream, TextReader/TextWriter, or XmlReader/XmlWriter, so you can work with files, memory streams, or network streams interchangeably. DataTable exposes the same WriteXml, ReadXml, WriteXmlSchema, and ReadXmlSchema methods when you only have a single table.

XmlWriteMode Options

DiffGram is the mode that preserves both original and current row versions, which is what ADO.NET uses when you need to send changes back to a server and apply updates with concurrency awareness.

How Relations Map to XML

When a DataSet contains DataRelation objects, WriteXml nests child rows inside their parent rows as hierarchical elements. Column names become element or attribute names, and the generated XSD captures data types, nullability, and constraints. Reading that XML back with ReadXmlSchema followed by ReadXml reconstructs the tables and relations automatically.

Legacy XML Integration

• XmlDataDocument: a .NET Framework-only class that kept a DataSet and an XML DOM synchronized. It is not available in .NET 8 and is not recommended for new work; use the DataSet XML methods or LINQ to XML instead.

• DataSet binary serialization (RemotingFormat = SerializationFormat.Binary) relies on BinaryFormatter, which is obsolete and removed in .NET 9. Do not use it; prefer XML or JSON serialization.

Code Example

using System;
using System.Data;

// Build a DataSet with a parent-child relation
DataSet ds = new DataSet("Orders");
DataTable customers = ds.Tables.Add("Customers");
customers.Columns.Add("CustomerId", typeof(int));
customers.Columns.Add("Name", typeof(string));

DataTable orders = ds.Tables.Add("Orders");
orders.Columns.Add("OrderId", typeof(int));
orders.Columns.Add("CustomerId", typeof(int));
orders.Columns.Add("Amount", typeof(decimal));

ds.Relations.Add("CustomerOrders",
    customers.Columns["CustomerId"],
    orders.Columns["CustomerId"]);

customers.Rows.Add(1, "Acme Corp");
orders.Rows.Add(101, 1, 250.00m);

// Write data plus inline schema to a file
ds.WriteXml("orders.xml", XmlWriteMode.WriteSchema);

// Write only the schema so another tier can prepare its DataSet
ds.WriteXmlSchema("orders.xsd");

// Read the schema first, then the data (preserves types and relations)
DataSet loaded = new DataSet();
loaded.ReadXmlSchema("orders.xsd");
loaded.ReadXml("orders.xml");

Best Practices and Pitfalls

• Read the schema before the data when the DataSet is empty; otherwise ReadXml infers the schema and the inferred column types may not match the original types.

• Use XmlWriteMode.DiffGram when you need to transmit original and current values for concurrency-aware updates.

• Validate XML against the XSD before ReadXml if the source is untrusted; ReadXml does not validate by default.

• Prefer XmlReader/XmlWriter overloads for large payloads to avoid buffering the entire document in memory.

• Do not rely on DataSet binary serialization; it uses BinaryFormatter, which is obsolete and removed in .NET 9.

What XSD Describes for a DataSet

When ADO.NET serializes a DataSet to XSD, the schema captures:

• Tables and columns: each DataTable becomes an <xs:element> with nested column elements typed as XML Schema data types (string, int, decimal, dateTime).

• Primary keys and unique constraints: mapped to <xs:key> and <xs:unique> elements.

• Relations: parent-child relationships between tables become <xs:keyref> elements, preserving referential links.

• Nullability: nullable columns are represented so DBNull values round-trip correctly.

This means the XSD is a complete structural blueprint of the in-memory relational data, independent of the actual row data.

How to Read and Write XSD

ADO.NET provides dedicated methods on DataSet and DataTable:

• WriteXmlSchema: writes only the schema (no data) to a file, TextWriter, XmlWriter or Stream.

• ReadXmlSchema: reads an XSD and rebuilds the tables, columns and relations in an empty DataSet.

• WriteXml with XmlWriteMode.WriteSchema: writes both data and schema together in one document.

• GetXmlSchema: returns the schema as a string without writing to disk.

Generating Strongly Typed DataSets

The xsd.exe tool (shipped with the .NET Framework SDK and Visual Studio / Windows SDK Build Tools; it is not part of the .NET 8 SDK) can compile an XSD into a C# class that derives from DataSet. This gives you compile-time access to tables and columns instead of string-based DataRow["Column"] lookups. Strongly typed DataSets are a legacy pattern; for new development prefer EF Core or Dapper, but XSD generation remains useful for interoperability with services that exchange DataSet XML.

Best Practices and Pitfalls

• Validate before reading: an XSD that does not match the expected structure can create unexpected tables; validate incoming schemas in untrusted scenarios.

• XmlWriteMode limits: XmlWriteMode supports only WriteSchema, IgnoreSchema and DiffGram - do not rely on other modes.

• Avoid binary serialization: DataSet.RemotingFormat = Binary relies on BinaryFormatter, which is obsolete and removed in .NET 9; use XSD/XML for persistence instead.

• Schema drift: when a producer changes column types or names, consumers reading the XSD may fail at runtime; version your schemas and test compatibility.

• Data types are approximate: SQL types map to XML Schema types, so precision and scale information for decimals may be lost in the XSD representation.

Core XML Integration Methods

The DataSet and DataTable classes expose WriteXml to serialize data to XML and ReadXml to populate the object from XML. These methods handle the mapping between relational rows and XML elements automatically.

• WriteXml: Outputs data to a file path, Stream, TextReader, or XmlWriter. On a DataSet, all contained tables are written; on a DataTable, only that table is written.

• ReadXml: Loads XML from the same source types and returns an XmlReadMode value indicating how schema was interpreted.

• ReadXmlSchema / WriteXmlSchema: Load or save only the XSD schema structure, without data, giving finer control over initialization.

• Mapping behavior: By default, each row becomes an XML element and each column becomes a nested element; schema-aware writes can express data types, constraints, and relations.

XmlWriteMode Enumeration

WriteXml accepts an XmlWriteMode parameter to control whether schema is included.

• IgnoreSchema: Writes only the data rows. Useful when the consumer already knows the schema.

• WriteSchema: Includes an inline XSD schema definition along with the data, allowing a recipient to reconstruct table structure, types, and constraints.

• DiffGram: Writes the current state alongside the original state, preserving change tracking information. This is useful for synchronization and disconnected update scenarios.

Reading XML and Schema

ReadXml uses XmlReadMode to determine how to handle schema information found in the XML.

• Auto: Chooses behavior based on whether an inline schema is present.

• ReadSchema: Reads an inline schema if present; otherwise may infer or fail depending on the source.

• IgnoreSchema: Ignores inline schema and loads data into existing tables only; if no matching schema exists, no data is loaded.

• Inferschema: Generates a schema from the XML structure when none exists.

• DiffGram: Reads a DiffGram and preserves original/current row states.

• InferTypedSchema: Infers schema and attempts to assign .NET types.

If the schema is missing and the target structure is not predefined, the result depends on the mode: InferSchema creates a schema from the data, IgnoreSchema generally loads nothing without matching columns, and Auto selects inference when needed. Using ReadXmlSchema separately allows loading only the structure before data arrives.

Best Practices and Limitations

• Avoid DataSet.RemotingFormat = Binary because it relies on BinaryFormatter, which is obsolete and removed in .NET 9.

• Always validate XML sources before calling ReadXml to prevent injection or schema poisoning attacks.

• For large datasets, prefer XmlWriter and XmlReader overloads instead of file paths to manage memory efficiently.

• Do not rely on XML serialization for security-sensitive data without encryption, because the output is plain text by default.

• Use DiffGram only when change tracking across tiers is needed; otherwise WriteSchema or IgnoreSchema produces simpler, smaller documents.

Common Exception Types

• SqlException: The primary exception thrown by SQL Server, containing error Number, Severity, State, and an Errors collection with detailed error information. Common numbers include 1205 (deadlock victim), 2627 (unique constraint violation), 547 (foreign key conflict), and -2 (command timeout). It derives from DbException.

• InvalidOperationException: Occurs when an operation is invalid for the current object state, such as opening an already open SqlConnection, executing a command on a closed connection, or reading from a closed or exhausted SqlDataReader.

• ArgumentException / ArgumentNullException: Thrown when invalid arguments are passed, for example a null connection string, an empty parameter name, or a mismatched parameter type.

• ObjectDisposedException: Raised when accessing a disposed SqlConnection, SqlCommand, or SqlDataReader.

• Timeout Errors: Command timeouts manifest as SqlException with Number equal to -2; there is no separate TimeoutException in ADO.NET.

• TransactionException: May be thrown when a SqlTransaction is rolled back or committed incorrectly, or when a distributed transaction cannot be promoted (platform-dependent).

Handling Strategies

• Specific Catch Blocks: Catch SqlException before general Exception to access SQL-specific properties like Number and Errors. Catch InvalidOperationException separately for state issues.

• Resource Management: Wrap SqlConnection, SqlCommand, SqlDataReader, and SqlTransaction in using statements (or await using for async) to guarantee disposal even when an exception occurs.

• Transient Error Detection: Inspect SqlException.Number to identify retry-worthy errors (1205 deadlock, 1222 lock timeout, -2 command timeout) versus fatal errors (2627 constraint violation, syntax errors). Use a retry policy with exponential backoff for transient faults.

• Logging and Security: Log exception details server-side (including Number, Message, and stack trace) but expose only generic messages to end users to prevent information disclosure. Never log connection strings or credentials.

• Connection State Management: Check connection.State != ConnectionState.Open before calling Open() to avoid InvalidOperationException. Use ConnectionState to track state in long-running operations.

• Transaction Rollback: In a catch block, roll back an active SqlTransaction if the connection is still open, then rethrow or handle the error.

• Async Exception Handling: When using ExecuteReaderAsync, ExecuteNonQueryAsync, etc., ensure await is inside a try-catch block; otherwise exceptions may be captured in the task and observed later.

Best Practices and Pitfalls

• Avoid Swallowing Exceptions: Empty catch blocks hide failures; always log or rethrow. If you catch and handle, document why.

• Parameter Safety: Always use explicit SqlDbType definitions with Parameters.Add; AddWithValue can infer incorrect types (e.g., treating a string as NVarChar instead of VarChar) and hurt plan reuse.

• Command Timeout: Set CommandTimeout explicitly for long-running queries; the default is 30 seconds. A timeout throws SqlException with number -2.

• Distributed Transactions: On .NET 8, distributed transactions via TransactionScope are supported only on Windows after setting TransactionManager.ImplicitDistributedTransactions = true; they are not supported on Linux or macOS.

• Null Values: Pass DBNull.Value for database NULLs; a C# null parameter value is treated as “parameter not supplied”.

• Reader Usage: A SqlConnection can have only one open SqlDataReader at a time unless MultipleActiveResultSets=True is in the connection string.

• Configuration: Store connection strings in configuration files or secret manager, never hardcode them in source code.

How ADO.NET Surfaces Database Errors

• Provider-specific exceptions: SQL Server failures throw SqlException; other providers throw their own types derived from the common System.Data.Common.DbException. Catch SqlException when you need SQL Server details, and DbException when you write provider-agnostic code.

• SqlException.Errors: a SqlErrorCollection containing one SqlError per server message. A single statement can raise multiple errors, so iterate the collection when you need all messages.

• Key properties: Number (server error number), Class (severity), State, LineNumber, Procedure, Server, ClientConnectionId, and Message.

• Common error numbers: -2 indicates a command timeout, 1205 means the transaction was chosen as a deadlock victim, and 18456 is a login failure. These are useful for distinguishing transient failures from permanent configuration or permission problems.

What to Log

• Structured details, not plain strings: log the error number, class, procedure, line number and the operation name so failures can be grouped and searched.

• The exception object itself: pass the exception to ILogger.LogError so the stack trace and inner exceptions are captured by the logging provider.

• Correlation identifiers: include an activity or request ID so a database failure can be traced across services.

• Avoid sensitive data: do not log connection strings, passwords, tokens, full SQL with interpolated user input, or parameter values that may contain personal data. Log the command text only in controlled diagnostics, never user input inline.

• Classify severity: use LogError for failures that break the operation, LogWarning for retried transient errors, and reserve LogCritical for unavailable databases or lost connectivity.

Implementing Logging Correctly

• Wrap connections, commands, readers and transactions in using blocks so resources are released even when an exception is thrown.

• Catch SqlException explicitly, log it, and rethrow unless you can genuinely recover. Do not swallow exceptions with an empty catch.

• Use when filters to handle specific cases such as timeouts or deadlocks separately from general SQL errors.

• Let transient-error handling and logging stay separate concerns: log first, then apply retry logic if the error is transient.

• Prefer logging through ILogger rather than writing to files or the database from data-access code, so the application controls sinks, filtering and redaction.

Code Example

using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;

public sealed class CustomerRepository
{
    private readonly string _connectionString;
    private readonly ILogger<CustomerRepository> _logger;

    public CustomerRepository(string connectionString, ILogger<CustomerRepository> logger)
    {
        _connectionString = connectionString;
        _logger = logger;
    }

    public string? GetCustomerName(int customerId)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            using var command = new SqlCommand(
                "SELECT Name FROM dbo.Customers WHERE CustomerId = @id", connection);

            // Explicit type; never use AddWithValue.
            command.Parameters.Add("@id", SqlDbType.Int).Value = customerId;

            connection.Open();
            return (string?)command.ExecuteScalar();
        }
        catch (SqlException ex) when (ex.Number == -2)
        {
            _logger.LogError(ex,
                "Command timeout while reading customer {CustomerId}", customerId);
            throw;
        }
        catch (SqlException ex)
        {
            _logger.LogError(ex,
                "SQL error {ErrorNumber} (class {ErrorClass}) in {Procedure} " +
                "while reading customer {CustomerId}",
                ex.Number, ex.Class, ex.Procedure, customerId);
            throw;
        }
    }
}

Best Practices and Common Pitfalls

• Do not log the full connection string: it may contain credentials or tokens.

• Do not log user input or parameter values by default: identifiers such as CustomerId are usually safe; free-text values may not be.

• Do not catch Exception and continue: catch SqlException or DbException and let unexpected exceptions propagate.

• Remember multiple errors per command: for batch or multi-statement work, inspect ex.Errors rather than only ex.Message.

• Do not confuse client timeouts with server failures: Number == -2 comes from the command timing out, not from the server rejecting the query.

• Use parameterized queries always: correct error logging is not a substitute for preventing SQL injection in the first place.

Keep Secrets Out of Source Code

• Development: use the .NET Secret Manager tool (dotnet user-secrets), which stores values outside the project tree and is never committed to source control.

• Production: read from environment variables, Azure Key Vault (via the Azure.Extensions.AspNetCore.Configuration.Secrets package), AWS Secrets Manager, or HashiCorp Vault through the configuration system.

• Local files: appsettings.json should contain only non-sensitive placeholders; real credentials belong in appsettings.*.local.json (gitignored) or User Secrets.

Prefer Managed Identity and Integrated Security

• Azure SQL: use Authentication=Active Directory Default or a managed identity, which removes the password from the string entirely and relies on Azure RBAC.

• On-premises Windows: use Integrated Security=true (Windows Authentication), which leverages Kerberos and requires no stored credential.

• Linux containers: managed identity or certificate-based authentication is preferred over password-based SQL login.

Encrypt Configuration When Necessary

• .NET Framework (legacy): the ProtectedConfiguration API with RsaProtectedConfigurationProvider can encrypt the <connectionStrings> section via aspnet_regiis -pe; this legacy API is not available in .NET 8.

• .NET 8: there is no built-in configuration encryption provider; rely on external secret stores and operating-system file ACLs instead.

• DPAPI: DpapiProtectedConfigurationProvider works only on Windows and should not be assumed available on Linux or macOS.

Harden the Connection String Itself

• Persist Security Info=false: set explicitly so the password cannot be read back from an open SqlConnection.ConnectionString.

• Encrypt=True;Trust Server Certificate=false: enforces TLS and prevents man-in-the-middle attacks; validate the server certificate.

• Least privilege: the database user in the string should have only the permissions the application actually needs, and credentials should be rotated on a schedule.

Code Example

using System;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

public class DataAccess
{
    private readonly string _connectionString;

    public DataAccess(IConfiguration configuration)
    {
        // Loaded from env vars, Key Vault, or User Secrets - never hardcoded
        _connectionString = configuration.GetConnectionString("DefaultConnection")
            ?? throw new InvalidOperationException("Connection string not configured.");
    }

    public async Task<int> GetProductCountAsync(int categoryId)
    {
        await using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        await using var command = connection.CreateCommand();
        command.CommandText = "SELECT COUNT(*) FROM Products WHERE CategoryId = @id";
        // Explicit parameter type, no AddWithValue
        command.Parameters.Add("@id", SqlDbType.Int).Value = categoryId;
        return (int?)await command.ExecuteScalarAsync() ?? 0;
    }
}

Common Pitfalls

• Hardcoding: embedding a connection string literal in code guarantees it ends up in source control and build artifacts.

• Logging: never write the connection string to logs, telemetry, or exception messages; the password may be included.

• Cross-platform assumptions: do not rely on DPAPI or aspnet_regiis encryption when the app runs on Linux containers.

• Over-permissioned accounts: a connection string tied to a sa-level login turns any leak into a full database compromise.

Connection Management

• Connection pooling: enabled by default in Microsoft.Data.SqlClient; reuse the exact same connection string so requests share a pool instead of creating new physical connections — any difference between two strings, even extra whitespace, creates a separate pool.

• Open late, close early: wrap SqlConnection, SqlCommand and SqlDataReader in using (or await using) so they dispose promptly and the connection returns to the pool immediately.

• Async I/O: use OpenAsync, ExecuteReaderAsync and ReadAsync so threads are freed to serve other requests while waiting on the database, which matters most in ASP.NET Core on both Windows and Linux.

• Avoid long-lived connections: never hold a connection open across user think time or between HTTP requests; take one from the pool, do the work, and return it.

Commands and Parameters

• Never use AddWithValue: it infers the SQL type from the CLR value, which causes implicit conversions, prevents index use, and bloats the plan cache with duplicate plans.

• Set lengths explicitly: for variable-length types set Size (and Precision/Scale for decimals) so the engine does not treat every value as max length.

• Send NULLs correctly: assign DBNull.Value for a database NULL; a C# null Value means the parameter is treated as not supplied.

• Prepare selectively: command.Prepare() can help a command executed many times, though with sp_executesql-based plan reuse in .NET 8 the benefit is often small; measure before adopting it widely.

Reading and Materialization

• Prefer SqlDataReader: it streams rows forward-only with minimal allocation; avoid DataSet/DataAdapter when you read data once and do not need disconnected change tracking.

• LINQ over a streaming reader: DbDataReader implements IEnumerable, so reader.Cast<IDataRecord>() lets you apply LINQ while rows are still being fetched instead of buffering them first.

• Handle NULLs safely: DataRow.Field<int>() and reader.GetInt32 throw on DBNull; use Field<int?>() or check reader.IsDBNull for nullable columns.

• Select only needed columns: avoid SELECT * to reduce network traffic, memory pressure and unnecessary serialization.

Bulk Operations

• Use SqlBulkCopy for large inserts: it is far faster than row-by-row INSERT. By default it does not check constraints or fire triggers; pass SqlBulkCopyOptions.CheckConstraints or FireTriggers when you need them.

• All-or-nothing batches: SqlBulkCopyOptions.UseInternalTransaction commits each batch separately, so wrap the copy in your own SqlTransaction when the whole load must succeed or fail together.

Common Pitfalls

• AddWithValue: the single most common performance mistake; always declare SqlDbType explicitly.

• Sync-over-async: calling .Result or .Wait() on async ADO.NET methods can deadlock under load; use await end to end.

• Undisposed readers: an open reader blocks its connection from returning to the pool; await using prevents this.

• DataSet binary serialization: DataSet.RemotingFormat = Binary relies on BinaryFormatter, which is obsolete and removed in .NET 9; do not rely on it.

• Connection string drift: keep connection strings in one configuration source so minor edits do not silently create extra pools.

Why It Matters

• Unmanaged resources: Under the hood, a SqlConnection holds a native handle to a SQL Server session. The garbage collector reclaims managed memory, but it cannot promptly release these native handles.

• Connection pool exhaustion: If connections are not closed, the pool fills up and new Open calls block or fail with a timeout, which is a common cause of production outages under load.

• Exception safety: Without using, an exception between Open and Close leaves the connection open. using translates to a try/finally where Dispose runs in the finally block.

How It Works

• When the compiler sees using (var conn = new SqlConnection(...)), it generates a try block around your code and a finally block that calls conn.Dispose().

• SqlConnection.Dispose closes the connection if it is still open and returns it to the connection pool (when pooling is enabled, which is the default).

• SqlCommand.Dispose releases any associated resources, including reader references and prepared statement handles.

• In C# 8 and later, including C# 12, you can use a using declaration (using var conn = ...;) which disposes the object at the end of the enclosing scope, reducing nesting.

Code Example

using System;
using System.Data;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=AppDb;Trusted_Connection=True;TrustServerCertificate=True;";

// Classic using block style
using (var connection = new SqlConnection(connectionString))
{
    connection.Open(); // Open before executing any command
    using (var command = new SqlCommand("SELECT Name FROM Users WHERE Id = @id", connection))
    {
        command.Parameters.Add("@id", SqlDbType.Int).Value = 42;
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            }
        } // reader.Dispose closes the reader
    } // command.Dispose
} // connection.Dispose returns it to the pool

// Equivalent C# 12 using-declaration style, less nesting
using var connection2 = new SqlConnection(connectionString);
connection2.Open();
using var command2 = new SqlCommand("SELECT COUNT(*) FROM Users", connection2);
int count = (int)command2.ExecuteScalar();
// Both dispose automatically at the end of the enclosing scope

Connection Pooling and Dispose

• Dispose does not necessarily close the physical TCP connection; with pooling enabled it returns the session to the pool for reuse by a later Open with the same connection string.

• This is why using is both safe and performant: you get prompt resource release without the cost of repeatedly establishing new physical connections.

• If you need to disable pooling for a specific scenario, add Pooling=false to the connection string, but this is rarely advisable for web applications.

Best Practices and Common Pitfalls

• Open as late as possible: Call Open immediately before the command, not at the start of a method, so the connection spends minimal time checked out of the pool.

• Dispose readers too: A SqlDataReader is also IDisposable. Leaving it open keeps the connection busy and can block subsequent commands on the same connection.

• One active reader per connection: Unless you enable MultipleActiveResultSets=true in the connection string, a second command on an open connection with a pending reader will fail.

• Never rely on finalizers: The finalizer may eventually close the connection, but timing is non-deterministic and finalization can be delayed indefinitely under memory pressure.

• Transactions need care: If you create a SqlTransaction, dispose it in its own using as well, and remember that an uncommitted transaction is rolled back when the connection closes or the transaction is disposed.

• Async code: using works identically with await; the disposal happens after the awaited task completes within the block.

Connection Pooling

SqlConnection is pooled by default, so “opening” a connection is usually just leasing one from the pool and closing it returns it. This only works when you follow the pooling rules:

• Exact connection-string match: pools are keyed on the connection string, so any variation (a different ApplicationName, a changed Max Pool Size, different credentials) creates a separate pool.

• Always close/return connections: wrap every connection in a using block so Dispose returns it to the pool even on exceptions. Leaving connections open exhausts the pool.

• Pool size: Max Pool Size defaults to 100. Under heavy load, raising it can help, but a higher value also increases SQL Server connection pressure; often the real fix is shorter-lived connections.

• Connection Reset (default true) makes the pool run sp_reset_connection when a connection is reused, which clears temporary state. Keep it enabled unless you have measured the overhead.

• Connection Lifetime / Load Balance Timeout: connections older than this are destroyed instead of reused, which helps with load-balanced clusters.

Commands and Parameters

• Parameterize every query: parameters are not only a security requirement, they let SQL Server cache and reuse the execution plan. String-concatenated SQL produces a new plan per distinct literal, which wastes CPU and memory.

• Avoid AddWithValue: it infers the SQL type from the .NET value, which produces NVARCHAR for strings with no regard for the actual column size and can silently truncate or widen parameters, defeating plan reuse. Use Parameters.Add(name, SqlDbType) and set Size, Precision and Scale explicitly.

• CommandTimeout defaults to 30 seconds. Long-running reports or bulk loads need a higher value, but a timeout is usually a symptom of a missing index or a bad query — fix the query first.

• CommandType.StoredProcedure avoids the extra parsing/permission steps of ad-hoc text and gives you a stable plan.

Reading Data Efficiently

• Prefer SqlDataReader over DataSet/DataTable for read-only results: it streams rows one at a time instead of buffering the whole result set in memory.

• CommandBehavior.CloseConnection ensures the connection is closed when the reader is disposed, which is the safe pattern for returning a reader from a data-access method.

• MARS (MultipleActiveResultSets=true) lets you run several readers on one connection, but it adds server-side overhead and can hide serialization problems. Enable it only when you genuinely need interleaved reads.

• DbDataReader implements IEnumerable, so reader.Cast<IDataRecord>() lets you project rows with LINQ while still streaming — do not call ToList() on it if the caller can consume rows lazily.

• For DataTable, Load(reader) is fine, but remember DataRow.Field<int>() throws on DBNull; use the nullable overload Field<int?>() for nullable columns.

Bulk Inserts

SqlBulkCopy is orders of magnitude faster than individual INSERT statements for large loads, with important defaults to know: it does not check constraints and does not fire triggers unless you pass SqlBulkCopyOptions.CheckConstraints / FireTriggers. Set BatchSize to avoid loading the whole source at once. UseInternalTransaction commits each batch separately, so for all-or-nothing semantics enlist the bulk copy in an explicit SqlTransaction instead.

Code Example

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

// Streams orders for one customer without buffering the whole result set.
async IAsyncEnumerable<(int OrderId, int CustomerId, DateTime? OrderDate)> GetOrdersAsync(
    int customerId, string connectionString)
{
    // Connection strings belong in configuration, never in code.
    // Leases a pooled connection; Dispose returns it, even on exceptions.
    using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    using var command = new SqlCommand(
        "SELECT OrderId, CustomerId, OrderDate FROM Sales.Orders WHERE CustomerId = @customerId",
        connection);
    command.CommandType = CommandType.Text;
    command.CommandTimeout = 60; // seconds; default is 30

    // Explicit type: avoids AddWithValue type inference and plan-reuse problems.
    command.Parameters.Add("@customerId", SqlDbType.Int).Value = customerId;

    // CloseConnection disposes the underlying connection with the reader.
    using var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);

    while (await reader.ReadAsync())
    {
        // A nullable column reads as DBNull; convert it to a nullable value.
        DateTime? orderDate = reader.IsDBNull(2) ? null : reader.GetDateTime(2);
        yield return (reader.GetInt32(0), reader.GetInt32(1), orderDate);
    }
}

Best Practices and Common Pitfalls

• Use the async methods (OpenAsync, ExecuteReaderAsync, ReadAsync) and never block on them with .Result or .Wait(), especially in web applications.

• Do not share one open connection across threads; open, use, and dispose per unit of work and let pooling handle the cost.

• Do not wrap every call in a transaction “for safety”: unnecessary transactions hold locks longer and reduce throughput.

• Fetch only what you need: SELECT explicit columns with a WHERE clause rather than SELECT *; network round trips and serialization dominate many workloads.

• Measure before tuning: check pool counters, SQL Server wait statistics, and execution plans before changing pool sizes or timeouts.

Core Difference

• DataTable: A single in-memory table made of DataRow objects and DataColumn objects. It can exist independently or as a member of a DataSet.

• DataSet: A container holding a collection of DataTable objects (Tables), plus DataRelation objects that define parent-child links between tables, and Constraint objects that enforce rules such as primary keys and foreign keys.

Structure Comparison

When to Use Each

• Use a DataTable when your operation involves a single result set, for example binding the result of one query to a grid, iterating rows for reporting, or passing tabular data between layers.

• Use a DataSet when you need several related tables together, such as master-detail data like orders and order details, or when you must track and submit changes across multiple tables as a unit.

How They Work with a DataAdapter

• A DataAdapter.Fill can populate either a DataTable directly or a DataSet; you do not need a DataSet just to read one table.

• Both keep a copy of data in memory after the connection closes, and changes are tracked per row (DataRowState).

• DataAdapter.Update sends inserted, modified, and deleted rows back to the database and calls AcceptChanges on each row it saves, so you should not call AcceptChanges again afterward.

Practical Guidance and Pitfalls

• Do not wrap a single table in a DataSet; the extra container overhead buys you nothing.

• When reading values, DataRow.Field<int>() throws if the column contains a database NULL; use Field<int?>() for nullable columns and check HasValue.

• For a parameter whose C# value is null, send DBNull.Value; a C# null parameter value is treated as not supplied.

• In new .NET 8 development, many data-access scenarios have moved to entity classes with EF Core or Dapper, but DataSet and DataTable remain appropriate for disconnected scenarios, reporting, and integration with legacy components.

Summary

Choose a DataTable for one table and a DataSet when you genuinely need multiple related tables, relationships, or cross-table constraints in memory.

How It Works

• DataTable.Columns returns a DataColumnCollection that holds the table’s column definitions in a defined order.

• Columns.Add(DataColumn) appends a fully configured DataColumn instance to that collection.

• Columns.Add(string name, Type type) is a shortcut that creates the column and sets its ColumnName and DataType in one call.

• Columns.Add(string name) creates a column of type string when you do not specify a type.

• A column’s DataType can be set any time before the column is added to a table; once the column belongs to the table, assigning DataType throws an ArgumentException.

• Adding a column whose ColumnName duplicates an existing (case-insensitive by default) name throws a DuplicateNameException.

Key Behaviors and Rules

• Column identity: ColumnName is the logical name; Caption is a display label (defaults to the column name). Lookups in Columns["name"] and in DataRow["name"] use ColumnName.

• Nullability: AllowDBNull defaults to true. Set it to false before adding rows if the field is required.

• Primary keys: to make the column part of the key, assign an array to DataTable.PrimaryKey after adding the column; a key column must have AllowDBNull = false and be unique.

• Auto-increment: set AutoIncrement = true, plus AutoIncrementSeed and AutoIncrementStep, for surrogate key columns. Such columns are typically marked ReadOnly = true.

• Computed values: set Expression to derive the column from other columns (for example "UnitPrice * Quantity"). The referenced columns must already exist, and the DataType must be compatible with the expression result.

• Existing rows: when a column is added to a table that already contains rows, every existing row gets a default value for the new column — the DefaultValue you set, otherwise DBNull.Value for nullable columns, which throws on non-nullable value-type columns without a default.

Code Example

using System.Data;

DataTable CreateOrderTable()
{
    var table = new DataTable("Order");

    // Explicitly configured column, then appended.
    var id = new DataColumn("OrderId", typeof(int))
    {
        AllowDBNull = false,
        AutoIncrement = true,
        AutoIncrementSeed = 1,
        AutoIncrementStep = 1,
        ReadOnly = true,
        Caption = "Order ID"
    };
    table.Columns.Add(id);

    // Shortcut overloads: name + type in one call.
    table.Columns.Add("CustomerName", typeof(string));
    table.Columns.Add("OrderDate", typeof(DateTime));
    table.Columns["CustomerName"]!.AllowDBNull = false;

    // Expression columns need the referenced columns to exist first.
    table.Columns.Add("UnitPrice", typeof(decimal));
    table.Columns.Add("Quantity", typeof(int));
    table.Columns.Add("LineTotal", typeof(decimal));
    table.Columns["LineTotal"]!.Expression = "UnitPrice * Quantity";

    // Primary key requires the column to be non-null and unique.
    table.PrimaryKey = new[] { table.Columns["OrderId"]! };

    return table;
}

void AddColumnToExistingTable(DataTable table)
{
    // Adding a column after rows exist fills existing rows with DefaultValue.
    if (!table.Columns.Contains("Notes"))
    {
        table.Columns.Add("Notes", typeof(string));
    }
}

Best Practices and Common Pitfalls

• Set schema before adding data: add all columns, constraints and the primary key before inserting rows, so validation applies from the first row onward.

• Prefer strongly typed access: DataRow.Field<T>("CustomerName") avoids casting, but use Field<T?> for nullable columns because Field<T> throws on DBNull.

• Do not rename or retype columns after data is loaded: retyping is not allowed once the column belongs to the table, and renaming can break code and bindings that reference the old name.

• Case-insensitive matching: Columns["orderid"] matches a column named OrderId by default, which is convenient but can hide typos.

• Use Contains before optional additions: call table.Columns.Contains("Notes") before adding a column that another routine may already have added, to avoid a DuplicateNameException.

• Remember DBNull.Value: reading a null cell returns DBNull, not C# null; compare with row.IsNull("Notes") or Convert.IsDBNull before casting.

How It Works

• A view, not a copy: A DataView references one DataTable (exposed through DataView.Table) and maintains a set of row references plus index structures. Creating a view does not duplicate the rows, so changes to the underlying rows are immediately visible through the view.

• Row state filtering: DataView.RowStateFilter restricts which versions of rows appear, using DataViewRowState values such as CurrentRows (the default: unchanged, added, and modified rows), ModifiedOriginal, ModifiedCurrent, Deleted, and OriginalRows. This makes it useful for inspecting pending edits before a DataAdapter.Update.

• Sorting: DataView.Sort takes a SQL-like expression such as "LastName ASC, HireDate DESC". The view builds an index so enumeration and lookups follow that order.

• Filtering: DataView.RowFilter accepts a boolean expression evaluated per row, for example "Age >= 18 AND Department = 'Sales'". For one-off finds without maintaining a filter, DataView.Find and FindRows use the current sort index and are faster than re-scanning.

• Editing: With DataView.AllowEdit, AllowNew, and AllowDelete set to true (defaults), you can edit, add, or delete rows through the view. Those changes are written back to the underlying DataTable with the appropriate DataRowState, so a later DataAdapter.Update persists them.

Key Features

• Multiple views of one table: You can create several DataView objects over the same DataTable, each with its own sort and filter, while the table is stored only once.

• Data binding: DataView implements IBindingList, ITypedList, and IList, so it binds directly to Windows Forms grids (DataGridView.DataSource) and other bindable UIs; DataTable.DefaultView is the view that controls typically bind to by default.

• Custom sorting and filtering logic: DataView exposes ApplyDefaultSort (uses the table’s primary key order when no Sort is set) and, for scenarios where expressions are not enough, you can derive from DataView or use LINQ to DataSet (table.AsDataView() after a LINQ query) to produce a view from a projection.

• Index maintenance: The view keeps its index up to date as rows change, so sorting and Find stay consistent at the cost of some overhead on every table modification.

DataView vs. Other ADO.NET Data Structures

Advantages and Limitations

• Advantages: No data duplication; immediate reflection of table changes; simple, expression-based sorting and filtering; first-class data-binding support; lets you inspect pending inserts, updates, and deletes through RowStateFilter before saving.

• Limitations: It is limited to one DataTable, so it cannot join related tables (use a DataRelation plus DataRow.GetChildRows or a LINQ join over DataSet instead). Expression syntax is restricted to the documented DataColumn.Expression operators and functions. Heavy use of RowFilter on large tables costs CPU and memory because indexes are rebuilt. It is an in-memory, disconnected construct: for large or frequently re-queried data, filtering and sorting in SQL Server is usually more efficient.

Best Practices

• Set RowFilter and Sort once where possible rather than changing them in tight loops, since each change can rebuild the index.

• Prefer DataView.FindRows over iterating the view when you need rows matching a key that is already covered by the Sort expression.

• Use RowStateFilter to show users only their pending changes, and remember that deleted rows are excluded from the default CurrentRows view.

• Do not call AcceptChanges on rows before a DataAdapter.Update: that would erase the inserted/modified/deleted state the adapter needs. DataAdapter.Update calls AcceptChanges on each row it saves.

• For read-only display, set AllowEdit, AllowNew, and AllowDelete to false to prevent unintended edits flowing back to the table.

• When a filter expression includes user input, validate and sanitize it; expression strings are concatenated, not parameterized, so values must be escaped properly.

Constraint Types

• UniqueConstraint: Requires values in one or more columns to be distinct. Assigning DataTable.PrimaryKey creates one automatically; you can also add a non-primary unique constraint on alternate keys.

• ForeignKeyConstraint: Enforces referential integrity between a parent column set and a child column set. It exposes DeleteRule, UpdateRule, and AcceptRejectRule, each taking a Rule value such as Rule.Cascade, Rule.SetNull, Rule.SetDefault, or Rule.None.

• ConstraintCollection: Constraints live in DataTable.Constraints, not in the DataSet directly. A DataRelation created with createConstraints = true also adds a ForeignKeyConstraint to the child table.

How Enforcement Works

• DataSet.EnforceConstraints is true by default. When enabled, a change that breaks a rule throws ConstraintException when the row edit ends—for example, at EndEdit, AcceptChanges, or when the row is added to the table.

• Setting EnforceConstraints = false suspends checking, which is useful for bulk loading temporarily inconsistent rows. Restoring true validates every existing row and throws if any violation remains.

• Validation occurs at row-commit time, not on every individual property assignment.

• Only DataSet exposes EnforceConstraints; DataTable does not have this property.

Retrieving Constraints from a Database

• A plain DataAdapter.Fill populates rows only; it does not create constraints.

• DataAdapter.FillSchema, or setting MissingSchemaAction.AddWithKey before Fill, can recover primary-key metadata from SQL Server.

• Foreign-key metadata is not retrieved automatically. To enforce referential integrity in memory, create ForeignKeyConstraint objects manually or add a DataRelation with constraints enabled.

• These in-memory constraints protect the disconnected cache only; they do not alter or replace server-side constraints.

Best Practices and Pitfalls

• Bulk loads: Turn EnforceConstraints off before inserting many rows, then turn it back on once; otherwise each row pays validation cost.

• Nulls: A UniqueConstraint permits multiple DBNull values unless the constrained column is also the primary key, because primary-key columns cannot be null.

• Server truth: In-memory constraints are a convenience, not a security boundary. Always define matching PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints in SQL Server.

• Serialization: Do not use DataSet.RemotingFormat = Binary; it depends on BinaryFormatter, which is obsolete and removed in .NET 9.

• Nullable reads: When reading constrained columns that allow nulls, use DataRow.Field<int?>() rather than Field<int>(), which throws on DBNull.

Supported .NET Types

• Primitive and numeric types: Boolean, Byte, SByte, Char, Decimal, Double, Single, Int16, Int32, Int64, UInt16, UInt32, UInt64 and String.

• Date and time types: DateTime for dates and timestamps, TimeSpan for time-of-day and interval values.

• Binary and identity types: Byte[] for BLOB or varbinary data, and Guid for uniqueidentifier values.

• Metadata and enumeration types: Type, so a cell can hold a System.Type value, and Enum types.

• Custom types: the documented rule is that a user-defined type must implement IList to be usable as a DataType; types outside the supported set throw an ArgumentException when the column is created.

How Type Enforcement Works

• Setting DataColumn.DataType, or passing a Type to DataTable.Columns.Add, records the column’s type.

• Assigning a value through DataRow[column] checks that the value is assignable to that column’s DataType; a mismatch throws ArgumentException.

• A database NULL is represented by DBNull.Value, never C# null. DataRow.Field<int>() throws on DBNull, so use Field<int?>() when the column may be null.

• Computed columns defined through DataColumn.Expression must produce results that are assignable to the column’s declared DataType.

Typical SQL Server to .NET Type Mapping

When a DataTable is filled from SQL Server with Microsoft.Data.SqlClient, the provider picks the column types:

Common Pitfalls

• DBNull handling: never compare a cell to null to detect a database null; use DBNull.Value, DataRow.IsNull(column), or the nullable Field<T?> accessor.

• Type mismatch: assigning "123" (a string) to an Int32 column throws; convert the value to the column’s declared type first.

• Nullable value types: Nullable<T> such as int? is not supported as a DataColumn.DataType; declare the column with the underlying value type and store DBNull.Value for missing values.

• Binary serialization: DataSet.RemotingFormat = Binary relies on BinaryFormatter, which is obsolete and removed in .NET 9; avoid it.

Best Practices

• Match each column’s DataType to the source database type, for example Decimal for SQL DECIMAL and DateTime for DATETIME2, so values round-trip without lossy conversions.

• Use typeof(byte[]) for VARBINARY columns instead of storing hex strings.

• Prefer the strongly typed Field<T> and SetField<T> accessors over the object indexer, so type errors surface at compile time.

• Keep columns to the common CLR types unless a custom IList type genuinely solves a specific domain problem, since unusual types are harder to map, serialize and query.

The DataRelation Class

The DataRelation class represents a link between two DataTable objects based on matching columns. It serves two main purposes: - Data Integrity: It can enforce referential integrity by automatically creating ForeignKeyConstraint and UniqueConstraint objects. - Navigation: It enables hierarchical traversal of data, allowing you to retrieve child rows from a parent row or vice versa without writing SQL joins.

Steps to Define a Relationship

1. Create Tables: Instantiate DataTable objects and add them to the DataSet.

2. Define Columns: Ensure the parent column (usually primary key) and child column (foreign key) have compatible data types (e.g., both typeof(int)).

3. Instantiate Relation: Create a DataRelation specifying a name, the parent DataColumn, and the child DataColumn.

4. Add to DataSet: Call DataSet.Relations.Add() to register the relationship.

Navigating Related Data

Once defined, you can traverse the relationship using DataRow methods: - GetChildRows: Called on a parent row to retrieve an array of matching child rows. - GetParentRow: Called on a child row to retrieve the single matching parent row. - Relation Name Overload: Use GetChildRows("RelationName") if multiple relations exist between the same tables.

Constraints and Enforcement

By default, constructing a DataRelation adds a ForeignKeyConstraint to the child table and a UniqueConstraint to the parent table. You can control this behavior using the constructor overload that accepts a bool createConstraints parameter. If set to false, you must manually manage integrity. Deleting a parent row with existing children will throw a ConstraintException unless the DeleteRule is set to Rule.Cascade or Rule.SetNull.

Code Example

using System;
using System.Data;

DataSet ds = new DataSet();
DataTable customers = new DataTable("Customers");
DataTable orders = new DataTable("Orders");

// Define columns with matching types for the relationship
customers.Columns.Add("CustomerID", typeof(int));
customers.Columns.Add("Name", typeof(string));
orders.Columns.Add("OrderID", typeof(int));
orders.Columns.Add("CustomerID", typeof(int));

ds.Tables.Add(customers);
ds.Tables.Add(orders);

customers.Rows.Add(1, "Alice");
orders.Rows.Add(101, 1);

// Create the relation on matching columns
DataRelation rel = new DataRelation("CustOrders",
    customers.Columns["CustomerID"],
    orders.Columns["CustomerID"]);

// Add the relation to the DataSet
ds.Relations.Add(rel);

// Navigate from parent to child
DataRow customerRow = customers.Rows[0];
DataRow[] childRows = customerRow.GetChildRows(rel);

Best Practices and Pitfalls

• Match Data Types Exactly: The parent and child columns must have identical data types; otherwise, an exception is thrown at runtime.

• Handle DBNull: When accessing values via DataRow.Field<T>(), remember it throws on DBNull. Use Field<int?>() for nullable foreign keys.

• Avoid Binary Serialization: Do not set DataSet.RemotingFormat = Binary; it relies on BinaryFormatter, which is obsolete and removed in .NET 9.

• Constraint Management: Explicitly configure DeleteRule and UpdateRule (e.g., Rule.Cascade) if you want automatic propagation of changes rather than exceptions.

• Memory Considerations: DataSet keeps data in memory; use relationships for moderate-sized data, not for large-scale reporting where SQL joins are more efficient.

How It Works

A ForeignKeyConstraint is typically associated with a DataRelation between two DataTable objects in the same DataSet, but it can also be created independently and added directly to the child table’s Constraints collection:

• The child columns (Columns) are the foreign-key columns on the child table.

• The related columns (RelatedColumns) are the primary- or unique-key columns on the parent table (RelatedTable).

• The constraint lives in the child table’s Constraints collection and is only enforced while DataSet.EnforceConstraints is true, which is the default.

When you add a DataRelation to DataSet.Relations, the DataRelation constructor creates and installs the ForeignKeyConstraint automatically unless you pass createConstraint: false. The relation provides navigation between tables; the constraint provides enforcement. They are distinct objects serving distinct purposes.

Key Properties and Rules

• ConstraintName: name used to look the constraint up in childTable.Constraints.

• DeleteRule / UpdateRule: members of the Rule enum that decide what happens to child rows when a parent row is deleted or its key is changed.

• AcceptRejectRule: AcceptRejectRule.Cascade or AcceptRejectRule.None; cascades AcceptChanges/RejectChanges calls from the parent to the child rows.

SetNull and SetDefault require the child foreign-key column to allow DBNull or have a meaningful default, otherwise the constraint will fail on the next integrity check.

Code Example

using System;
using System.Data;

var dataSet = new DataSet("Company");

var parent = dataSet.Tables.Add("Department");
parent.Columns.Add("DepartmentId", typeof(int));
parent.Columns.Add("DepartmentName", typeof(string));
parent.PrimaryKey = new[] { parent.Columns["DepartmentId"] };

var child = dataSet.Tables.Add("Employee");
child.Columns.Add("EmployeeId", typeof(int));
child.Columns.Add("DepartmentId", typeof(int));   // foreign key column
child.Columns.Add("EmployeeName", typeof(string));

// Create the constraint explicitly so it can be named and configured.
var fk = new ForeignKeyConstraint(
    "FK_Employee_Department",
    parent.Columns["DepartmentId"],
    child.Columns["DepartmentId"])
{
    DeleteRule = Rule.Cascade,
    UpdateRule = Rule.Cascade,
    AcceptRejectRule = AcceptRejectRule.Cascade
};
child.Constraints.Add(fk);

// Add the navigation relation without creating a second constraint.
dataSet.Relations.Add(new DataRelation(
    "Department_Employee",
    parent.Columns["DepartmentId"],
    child.Columns["DepartmentId"],
    createConstraint: false));

parent.Rows.Add(1, "Engineering");
parent.Rows.Add(2, "Sales");

// Valid: a parent row with DepartmentId 1 exists.
child.Rows.Add(101, 1, "Ada Lovelace");

try
{
    // No parent has DepartmentId 99, so the row is rejected.
    child.Rows.Add(102, 99, "Grace Hopper");
}
catch (ConstraintException ex)
{
    Console.WriteLine($"Row rejected: {ex.Message}");
}

// DeleteRule.Cascade removes the employee row along with the department.
parent.Rows.Find(1).Delete();

When the data comes from SQL Server, fill the parent table before the child table with a SqlDataAdapter from Microsoft.Data.SqlClient; otherwise the constraint rejects child rows whose parents have not been loaded yet.

Best Practices and Common Pitfalls

• The database is the source of truth. Keep the matching FOREIGN KEY constraint in SQL Server; the ForeignKeyConstraint only protects the in-memory DataSet and cannot replace server-side integrity.

• Wrap edits in try/catch ConstraintException so a single invalid row does not crash a whole editing session.

• Temporarily suspend enforcement for bulk loads: set dataSet.EnforceConstraints = false, fill the tables, then set it back to true — re-enabling enforcement revalidates all rows and throws ConstraintException if any still violate the rules, which you can catch and inspect.

• Do not call AcceptChanges to “fix” violations. AcceptChanges simply marks rows as unchanged; it does not make orphaned children valid, and with AcceptRejectRule.Cascade it can hide real errors.

• Check childTable.HasErrors and DataRow.RowError after enforcement failures to report which rows and columns caused the problem.

• Remember EnforceConstraints: if constraints appear to be doing nothing, this flag has usually been turned off somewhere in the call chain.

The DataRelation

A DataRelation is added to the DataSet.Relations collection and connects a parent column (usually a primary key) to a foreign-key column in the child table. By default, creating the relation also adds a ForeignKeyConstraint that enforces referential integrity on the in-memory data and cascades deletes or updates according to the rule you specify.

• Parent table: the “master” side, such as Customers.

• Child table: the “detail” side, such as Orders.

• Relation name: a unique string used to retrieve the relation later or to name nested XML.

Navigating from Parent to Detail

Call DataRow.GetChildRows(DataRelation) on a parent row to receive an array of matching child rows. You can also use the overloaded GetChildRows(string relationName) or iterate the table’s ChildRelations collection when a table participates in several relations. If a parent has no children, the returned array is empty rather than null.

Navigating from Detail to Parent

Call DataRow.GetParentRow(DataRelation) on a child row to get its single parent row for that relation. GetParentRows(DataRelation) returns the same parent wrapped in a one-element array (or an empty array if there is no parent), which is convenient for uniform array-style handling. A child row has at most one parent per relation; to walk several upward relations, iterate the table’s ParentRelations collection and call GetParentRow for each.

Code Example

using System;
using System.Data;

DataSet dataSet = new DataSet();
DataTable customers = dataSet.Tables.Add("Customers");
DataTable orders = dataSet.Tables.Add("Orders");
// add columns and fill rows here (omitted for brevity)

DataRelation relation = new DataRelation(
    "CustomerOrders",
    customers.Columns["CustomerId"],
    orders.Columns["CustomerId"]);
dataSet.Relations.Add(relation);

foreach (DataRow customer in customers.Rows)
{
    Console.WriteLine(customer.Field<string>("CompanyName"));
    DataRow[] orderRows = customer.GetChildRows(relation);
    foreach (DataRow order in orderRows)
    {
        // Use nullable access if the column can contain DBNull
        int? orderId = order.Field<int?>("OrderId");
        Console.WriteLine($"  Order {orderId}");
    }
}

// From detail back to master
DataRow firstOrder = orders.Rows[0];
DataRow parent = firstOrder.GetParentRow(relation);
string name = parent.Field<string>("CompanyName");

Best Practices and Pitfalls

• Name relations explicitly so GetChildRows("CustomerOrders") is self-documenting and stable if column order changes.

• Use Field<T?> for nullable columns: DataRow.Field<int>() throws InvalidCastException on DBNull; prefer Field<int?>() and check HasValue.

• Do not call AcceptChanges after DataAdapter.Update: Update already calls AcceptChanges on each saved row, so calling it again is redundant.

• Check relation existence before navigating if the DataSet may be built dynamically; accessing a missing relation name throws at runtime.

• Remember navigation is in-memory only: the DataSet does not lazy-load from the database, so ensure the relevant tables are filled before walking relations.

• Avoid DataSet binary serialization (RemotingFormat = Binary): it relies on BinaryFormatter, which is obsolete and removed in .NET 9; use XML or JSON instead.

What It Is

LINQ to DataSet allows you to query data that has already been fetched from a database and held in memory, rather than querying the database itself. It is part of the System.Data.DataSetExtensions assembly, which is included in the .NET 8 shared framework, so no extra NuGet package is required for most workloads. This is useful when you need to filter, sort, or project data locally after loading it, or when working with data from multiple sources merged into a single DataSet.

Key Extension Methods

The feature relies on extension methods defined in System.Data.DataRowExtensions and System.Data.DataTableExtensions:

• AsEnumerable(): Converts a DataTable into an IEnumerable<DataRow>, which is the entry point for LINQ queries.

• Field<T>(): Provides strongly-typed access to column values on a DataRow. It handles conversion and boxing more cleanly than the DataRow indexer.

• SetField<T>(): Assigns a value to a column, automatically converting null to DBNull.Value.

• CopyToDataTable(): Materializes the results of a LINQ query back into a new DataTable.

Code Example

The following example loads data securely using Microsoft.Data.SqlClient and then filters it with LINQ. Note the explicit parameter type and the nullable Field<int?> usage.

using System.Data;
using System.Linq;
using Microsoft.Data.SqlClient;

var dataSet = new DataSet();
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("SELECT Id, Name, Age FROM Users WHERE Age >= @minAge", connection))
{
    // Explicit type prevents SQL injection and type inference issues
    command.Parameters.Add("@minAge", SqlDbType.Int).Value = 18;
    connection.Open();
    using (var adapter = new SqlDataAdapter(command))
    {
        adapter.Fill(dataSet, "Users");
    }
}

DataTable? table = dataSet.Tables["Users"];
if (table is null) return;

// LINQ to DataSet query over disconnected data
var adults = from row in table.AsEnumerable()
             where row.Field<int?>("Age") >= 18
             select new { Id = row.Field<int>("Id"), Name = row.Field<string>("Name") };

Best Practices and Pitfalls

• DBNull handling: row.Field<int>("Age") throws an InvalidCastException if the database value is NULL. Use a nullable type like Field<int?> for columns that allow nulls, or check row.IsNull("Age") first.

• Filter early: LINQ to DataSet operates on data already in memory. For large datasets, filter in SQL first to reduce memory usage and network traffic, then use LINQ for final projection or in-memory joins.

• Deferred execution: The query is not executed until you enumerate adults (e.g., via ToList() or a foreach loop).

• No SQL injection risk: Since the data is disconnected, LINQ queries cannot be injected into the database, but you must still use parameterized queries when filling the DataSet, as shown above.

• Typed DataSets: While untyped DataSet objects are common, generated typed DataSets provide compile-time safety for column names, reducing runtime string errors.

Execution Model and Data Availability

A DataSet is disconnected; data is fully loaded into memory before LINQ executes. This allows multiple passes and random access. A DataReader is connected and forward-only. LINQ queries against it are deferred but must be enumerated while the connection remains open and the reader is active.

Key Differences

LINQ to DataSet Mechanics

To query a DataTable, call AsEnumerable() from System.Data.DataSetExtensions. This returns IEnumerable<DataRow>, enabling standard LINQ operators. Use row.Field<T>("Column") for type-safe access. This method handles conversion and throws if the value is DBNull unless a nullable type is used.

LINQ to DataReader Mechanics

DbDataReader implements the non-generic IEnumerable, enabling reader.Cast<IDataRecord>(). This projects each record into LINQ-compatible objects without loading everything into memory. However, column access uses indexers (r["Name"]), requiring explicit DBNull checks or IsDBNull calls to avoid runtime exceptions.

Performance and Memory Implications

DataSet consumes memory proportional to the entire result set but offers fast repeated querying. DataReader minimizes memory footprint and starts returning data immediately, ideal for large datasets. However, holding a connection open during LINQ enumeration increases database lock duration and connection pool pressure.

Best Practices and Pitfalls

• Dispose Resources: Ensure DataReader and SqlConnection are wrapped in using blocks; enumeration happens inside the block.

• Handle DBNull: Use Field<int?>() for DataSets and IsDBNull() for Readers to prevent invalid cast exceptions.

• Avoid AddWithValue: Always specify SqlDbType explicitly to prevent type inference issues and plan cache pollution.

• Connection Lifetime: Do not return IQueryable backed by a DataReader from a method; materialize results before closing the connection.

Core Role

DataContext acts as a combined unit of work and identity map. By default, every entity object you query through a DataContext is tracked by that instance, and the DataContext guarantees that a given database row is represented by exactly one object instance within its lifetime. It is the object through which you both read data (via LINQ queries over Table<T> properties) and write data (via InsertOnSubmit, DeleteOnSubmit, and SubmitChanges). Without it, you would have to write SQL manually, track object state yourself, and ensure that two queries for the same row do not produce conflicting copies.

Key Responsibilities

• Connection management: Holds the connection string or SqlConnection and opens/closes the underlying connection as needed for each query or submit operation.

• Query translation: Converts LINQ expression trees into parameterized T-SQL statements executed against SQL Server.

• Change tracking: Records the original values of every entity it materializes so it can generate correct INSERT, UPDATE, and DELETE commands.

• Identity mapping: Ensures that two queries returning the same primary key yield the same object reference within the same DataContext.

• Unit of work: Batches all pending changes and sends them in a single SubmitChanges call, wrapped in a transaction by default.

Lifecycle and Usage Pattern

The recommended pattern is short-lived DataContext instances, typically one per request or per unit of work:

1. Construct a DataContext with a connection string or SqlConnection.

2. Query data through Table<T> properties exposed on the DataContext.

3. Modify, add, or remove entities as needed.

4. Call SubmitChanges once to persist everything.

5. Dispose the DataContext (it implements IDisposable).

Keeping a DataContext alive for too long causes it to accumulate tracked objects, increasing memory usage and slowing down SubmitChanges because change detection must scan every tracked entity.

Deprecation and Replacement

LINQ to SQL is deprecated; Microsoft stopped developing it after .NET Framework 4 and it is not available on .NET Core, .NET 5+, or .NET 8. For new development on .NET 8, use EF Core 8 with DbContext.

Best Practices and Common Pitfalls

• Treat it as short-lived: Do not store a DataContext in a static field or reuse it across many requests; create a new instance per unit of work.

• Call SubmitChanges once: Batching all modifications into a single call is more efficient than calling it after each change.

• Disable tracking for read-only queries when possible: Setting ObjectTrackingEnabled = false improves performance for scenarios that never submit changes; in EF Core the equivalent is AsNoTracking().

• Do not start new projects with LINQ to SQL: It is not actively developed and receives no feature updates. Migrate existing code to EF Core 8, whose DbContext provides the same unit-of-work and change-tracking role with active support.

Core Async Methods

The DbConnection, DbCommand, and DbDataReader base classes define the asynchronous API, which SqlConnection, SqlCommand, and SqlDataReader implement in the Microsoft.Data.SqlClient provider.

• OpenAsync: Opens the database connection without blocking the thread.

• ExecuteReaderAsync: Executes a query and returns a reader as a Task<DbDataReader>.

• ExecuteNonQueryAsync: Runs INSERT, UPDATE, or DELETE statements asynchronously.

• ExecuteScalarAsync: Retrieves the first column of the first row asynchronously.

• ReadAsync: Advances the reader to the next row without blocking.

Most of these methods accept a CancellationToken, enabling you to cancel long-running queries or respond to request timeouts in web applications.

Why Use Asynchronous I/O

Async I/O does not make a single query execute faster; the database still takes the same amount of time to process the request. The benefit is scalability: while the I/O is in progress, no thread is blocked waiting for it. In ASP.NET Core applications, this frees up thread pool threads to handle other incoming requests, reducing the risk of thread pool starvation under high load. This is particularly important for web servers where thread count is limited.

Code Example

The following method opens a connection, runs a parameterized SELECT statement, and streams the results asynchronously. It uses await using to dispose of resources asynchronously and binds parameters with explicit types.

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;

public class UserRepository
{
    public async Task<IEnumerable<(int Id, string Name)>> GetUsersAsync(
        int minId, CancellationToken cancellationToken = default)
    {
        var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION")
            ?? throw new InvalidOperationException("Connection string missing");
        var results = new List<(int Id, string Name)>();

        await using var connection = new SqlConnection(connectionString);
        await using var command = new SqlCommand(
            "SELECT Id, Name FROM Users WHERE Id >= @minId", connection);

        // Bind with explicit type; never use AddWithValue
        command.Parameters.Add("@minId", SqlDbType.Int).Value = minId;

        await connection.OpenAsync(cancellationToken);
        await using var reader = await command.ExecuteReaderAsync(cancellationToken);

        while (await reader.ReadAsync(cancellationToken))
        {
            results.Add((reader.GetInt32(0), reader.GetString(1)));
        }

        return results;
    }
}

Best Practices and Common Pitfalls

• Async all the way: Once you call an async method, propagate async and await up the call stack. Never block on a Task with .Result or .Wait(), as this can cause deadlocks in contexts with a synchronization context and defeats the scalability benefit.

• Pass cancellation tokens: Always forward the CancellationToken from your controller or API endpoint so operations can be aborted if the client disconnects.

• Connection pooling: Async opens still use the standard connection pool, so keep connection strings identical to maximize pool reuse.

• Avoid fire-and-forget: Do not start a database Task without awaiting it; unobserved exceptions can crash the process in some hosting configurations.

• Explicit parameter types: Always add parameters with Parameters.Add and a SqlDbType, as shown above, rather than AddWithValue, to ensure correct query plans and type safety.

How Asynchronous I/O Works

When a synchronous method like ExecuteReader is called, the calling thread blocks until the SQL Server responds. In contrast, async methods utilize operating system I/O completion ports on Windows or epoll on Linux. The thread returns to the ThreadPool immediately after initiating the request. Once the database sends data, the runtime schedules the continuation on an available thread, allowing fewer threads to handle more concurrent requests.

Core Async Methods

Microsoft.Data.SqlClient provides async counterparts for most synchronous operations. Key methods include OpenAsync, ExecuteReaderAsync, ExecuteNonQueryAsync, and ExecuteScalarAsync. Data readers also support ReadAsync, NextResultAsync, and GetFieldValueAsync<T>. These methods accept an optional CancellationToken to allow external cancellation, such as when a web request is abandoned by the client.

Scalability and Responsiveness

In ASP.NET Core, using async database calls allows the web server to handle thousands of concurrent connections with a limited thread pool. Synchronous blocking under load leads to thread pool starvation, causing request queuing and timeouts. For desktop apps like WPF or .NET MAUI, async operations keep the UI thread free, preventing the interface from freezing during long-running queries.

Best Practices and Pitfalls

• Always await async calls: Never use .Result or .Wait() as this can cause deadlocks, especially in UI contexts.

• Use ConfigureAwait(false) in library code: Avoids unnecessary context capture, though this is less critical in modern ASP.NET Core.

• Propagate CancellationToken: Pass tokens from the caller to support timeouts and cooperative cancellation.

• Avoid mixing sync and async on the same connection: Doing so without proper handling can lead to race conditions or connection state errors.

• Async transactions: Use BeginTransactionAsync, CommitAsync, and RollbackAsync when working with transactions in an async flow.

Asynchronous Methods

The Task-based methods are defined on DbCommand and overridden in SqlCommand; they are the ones to use with async/await:

• ExecuteNonQueryAsync: runs a command that returns no result set (INSERT, UPDATE, DELETE, DDL, stored procedures without a result), returning the number of rows affected.

• ExecuteScalarAsync: runs a query and returns the first column of the first row as an object, discarding the rest; useful for aggregate lookups such as COUNT(*).

• ExecuteReaderAsync: returns a SqlDataReader for streaming result sets. Overloads accept a CommandBehavior and/or a CancellationToken.

• ExecuteXmlReaderAsync: returns an XmlReader when the command produces XML, for example a query using FOR XML.

• BeginExecuteNonQuery / EndExecuteNonQuery, BeginExecuteReader / EndExecuteReader, BeginExecuteXmlReader / EndExecuteXmlReader: the older Asynchronous Programming Model. They still compile, but they are legacy; use the *Async methods in new code.

How It Works

Each *Async method yields the calling thread back to the thread pool until SQL Server responds, which is what makes them valuable in server applications: a blocked thread costs memory and reduces throughput, while an awaited operation holds almost no managed resources. The methods are true async I/O operations against the connection, not work queued to the thread pool.

Cancellation is supported through a CancellationToken passed to the overloads that accept one. SQL Server honors the cancellation for most running commands, and the operation surfaces as an OperationCanceledException (or SqlException for some server-side aborts) when the token fires.

Comparing the Execution Methods

Code Example

using System.Data;
using Microsoft.Data.SqlClient;

// Connection strings should come from configuration, never hard-coded.
string connectionString = "Server=.;Database=Shop;Trusted_Connection=True;TrustServerCertificate=True;";

await using SqlConnection connection = new(connectionString);
await connection.OpenAsync(); // OpenAsync is the async counterpart of Open

await using SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Name FROM Customers WHERE Region = @region";
command.Parameters.Add("@region", SqlDbType.NVarChar, 50).Value = "West";

// ExecuteReaderAsync streams the rows; CommandBehavior.SingleRow is a hint for one-row queries.
await using SqlDataReader reader =
    await command.ExecuteReaderAsync(CommandBehavior.SingleRow, CancellationToken.None);

while (await reader.ReadAsync()) // ReadAsync avoids blocking per row
{
    string name = reader.GetString(0);
    Console.WriteLine(name);
}

For a command that modifies data, replace the reader block with:

command.CommandText = "UPDATE Customers SET Name = @name WHERE Id = @id";
command.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = "Contoso";
command.Parameters.Add("@id", SqlDbType.Int).Value = 42;

int rows = await command.ExecuteNonQueryAsync();

Best Practices and Pitfalls

• Prefer OpenAsync with await using: connections, commands and readers are all IAsyncDisposable in .NET 8, so await using disposes them asynchronously.

• Never block on async code: calling .Result or .Wait() on these tasks can deadlock in UI or ASP.NET classic synchronization contexts and defeats the purpose of the async call.

• Pass a real CancellationToken from the request or page lifetime rather than CancellationToken.None, so long-running queries can be cancelled cleanly.

• Do not mix one connection with concurrent commands: a single SqlConnection handles one active command at a time unless MARS is enabled; run parallel commands on separate connections.

• SqlDataAdapter has async methods too: Microsoft.Data.SqlClient includes SqlDataAdapter.FillAsync and UpdateAsync; remember that Update calls AcceptChanges on each saved row automatically, so don’t call AcceptChanges yourself.

• Remember DBNull.Value: a column containing NULL reads as DBNull, not C# null, so check reader.IsDBNull(0) before calling typed accessors.

Provider-Agnostic Base Classes

System.Data.Common defines abstract base classes that every ADO.NET provider implements:

• DbConnection: represents the database connection; created per provider via the factory.

• DbCommand: holds the SQL text and parameters; created via connection.CreateCommand().

• DbParameter: represents a parameter; created via command.CreateParameter(), with DbType, Size, Precision and Scale set explicitly.

• DbDataReader: forward-only result stream; returned by ExecuteReader().

• DbTransaction and DbDataAdapter: for transactions and disconnected DataSet fills.

Because your code references only these base types, swapping providers requires no code changes — only a different provider package and connection string.

Resolving the Provider with DbProviderFactory

DbProviderFactories.GetFactory(string invariantName) returns the factory registered for a provider invariant name. Critical for .NET 8: unlike .NET Framework, .NET Core and later do not read machine.config, so providers from NuGet packages are not auto-registered merely by referencing the assembly. You must register each factory explicitly at startup with DbProviderFactories.RegisterFactory(invariantName, factory) (for example SqlClientFactory.Instance for SQL Server or NpgsqlFactory.Instance for PostgreSQL), or resolve the provider through dependency injection instead. GetFactory throws ArgumentException if the name is not registered.

In modern .NET 8 applications, many teams register the DbConnection (or a repository interface) directly in the DI container, which avoids DbProviderFactory registration entirely and makes the provider choice explicit at the composition root.

Configuration and Connection Strings

Store the provider name and connection string in configuration (e.g. appsettings.json), and keep secrets out of source code using user secrets, environment variables, or Azure Key Vault. Note that SQL dialects are not fully portable: TOP, identity columns, and square-bracket quoting are SQL Server-specific, while PostgreSQL uses LIMIT, SERIAL, and double quotes. Keep SQL provider-specific or use a query-per-provider strategy if you must support divergent dialects.

Limitations and Best Practices

• Provider-specific features are not portable: SqlBulkCopy, table-valued parameters, and provider-specific types require concrete classes and break the abstraction.

• Data type mappings differ: DbType.DateTime2, precision, and scale behave differently across providers; test each provider explicitly.

• Parameter naming varies: SQL Server and PostgreSQL use named parameters (@name), while Oracle binds by position unless BindByName = true is set.

• Never use AddWithValue: always set DbType, Size, Precision and Scale explicitly via CreateParameter().

• Validate the invariant name before calling GetFactory, and wrap provider resolution so an unsupported provider fails fast with a clear message.

• Prefer DI in new .NET 8 code: DbProviderFactory still works but requires manual registration; DI gives compile-time safety and easier testing.

Provider Selection

ADO.NET is a set of interfaces and base classes in System.Data, but database-specific logic lives in provider libraries. For MySQL on .NET 8, MySqlConnector is the preferred high-performance provider over the legacy Oracle MySql.Data package because it fully supports async/await without blocking threads. You install it via NuGet (MySqlConnector) and reference using MySqlConnector; in your data access layer.

Connection and Command Structure

You establish a connection using MySqlConnection with a standard connection string containing server, database, and credentials. Commands are created via MySqlCommand, linked to the connection, and configured with a SQL statement. Always open the connection explicitly before executing commands and ensure it closes afterward, ideally through using statements which guarantee disposal even if exceptions occur.

Parameterized Queries

Security is critical; never concatenate user input into SQL strings. Use parameters to prevent SQL injection and ensure correct type mapping. Following strict typing rules, every parameter is added with an explicit MySqlDbType rather than relying on type inference helpers like AddWithValue, which can cause performance issues or type mismatches. For nullable columns, pass DBNull.Value instead of C# null to correctly represent database NULLs.

Data Retrieval

Execute queries using ExecuteReader to obtain a MySqlDataReader, which streams rows efficiently. The reader implements IEnumerable, allowing LINQ integration via Cast<IDataRecord>() if needed, though manual iteration is often clearer for mapping objects. Read columns by ordinal, or by name by first calling GetOrdinal, and check for DBNull before converting values to avoid invalid cast exceptions during data processing.

Code Example

using System.Threading.Tasks;
using MySqlConnector;

public sealed class UserRepository
{
    public async Task<User?> GetUserAsync(int userId, string connectionString)
    {
        await using var connection = new MySqlConnection(connectionString);
        await connection.OpenAsync();

        await using var command = new MySqlCommand(
            "SELECT id, name, email FROM users WHERE id = @id", connection);

        // Explicit type declaration instead of AddWithValue, which infers the type
        command.Parameters.Add("@id", MySqlDbType.Int32).Value = userId;

        await using var reader = await command.ExecuteReaderAsync();
        if (await reader.ReadAsync())
        {
            // MySqlDataReader has no GetXxx(string) overloads: resolve ordinals first
            int idOrdinal = reader.GetOrdinal("id");
            int nameOrdinal = reader.GetOrdinal("name");
            int emailOrdinal = reader.GetOrdinal("email");

            return new User
            {
                Id = reader.GetInt32(idOrdinal),
                Name = reader.GetString(nameOrdinal),
                Email = reader.IsDBNull(emailOrdinal) ? null : reader.GetString(emailOrdinal)
            };
        }

        return null;
    }
}

public sealed class User
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public string? Email { get; set; }
}

Common Pitfalls

Avoid leaving connections open by relying on garbage collection; always use using blocks. Do not mix MySqlConnector types with Microsoft.Data.SqlClient types, as they are incompatible despite sharing base classes. Remember that MySqlCommand parameters are bound by name by default, unlike ODP.NET which binds by position. Finally, ensure your connection string uses SslMode=Required in production to encrypt traffic between the application and the database server.

What Each Technology Is

• ADO.NET: the foundation of all .NET data access. You open a SqlConnection, create a SqlCommand, bind parameters with an explicit SqlDbType, execute the command, and read results with a SqlDataReader or load them into a DataTable/DataSet. You write the SQL, you map the rows to objects yourself, and you control connection lifetime, transactions and batching explicitly.

• Entity Framework: an ORM that models the database as .NET entity classes plus a DbContext. You query with LINQ (context.Products.Where(p => p.Price > 10)), and EF Core translates that to SQL, executes it through an ADO.NET provider, and materialises the results as objects. Changes to entities are tracked and saved with SaveChanges, which generates the INSERT/UPDATE/DELETE statements.

• The legacy “ADO.NET Entity Framework” branding refers to EF6, which is in maintenance mode and runs only on .NET Framework. New development should use EF Core 8 unless an EF6-only feature is required.

Key Differences

How EF Core Relies on ADO.NET

EF Core is not a replacement for ADO.NET; it is a consumer of it. Every EF Core provider wraps an ADO.NET provider — for SQL Server, Microsoft.EntityFrameworkCore.SqlServer uses Microsoft.Data.SqlClient internally. When you call SaveChanges, EF Core opens a SqlConnection, enlists it in a DbTransaction, and issues parameterised commands through the same ADO.NET classes you would use by hand. This is why provider-specific behaviour (connection string settings, distributed transaction limits, bulk copy semantics) still applies underneath EF Core.

When to Choose Which

• Prefer EF Core 8 for line-of-business applications with many tables, relationships and CRUD screens, where developer productivity, compile-time queries and migrations outweigh micro-optimisation.

• Prefer plain ADO.NET for high-throughput read paths, reporting, bulk loads (SqlBulkCopy), complex stored-procedure logic, or when you need exact control over the SQL and round trips.

• Combine them: EF Core 8 supports dropping down to ADO.NET for specific hotspots — execute raw SQL via context.Database.SqlQueryRaw<T>() for mapped shapes, or FromSqlRaw/FromSqlInterpolated on a DbSet, while keeping Microsoft.Data.SqlClient available for a raw SqlConnection when a query cannot be expressed through the model.

Best Practices and Common Pitfalls

• Always parameterise queries in both stacks; in raw ADO.NET use command.Parameters.Add("@id", SqlDbType.Int).Value = id and never AddWithValue, which infers types and can cause bad query plans.

• Watch for EF Core performance traps: lazy-loading proxies cause N+1 queries, and SaveChanges sends one command per changed row. Use eager loading (Include) or split queries for graphs, and ExecuteUpdate/ExecuteDelete (bulk updates) in EF Core 7+ when you do not need to load entities first.

• Dispose everything: wrap SqlConnection, SqlCommand and SqlDataReader in using blocks; in EF Core the DbContext should be scoped or explicitly disposed so connections are returned to the pool.

• Do not treat EF Core as a reason to skip SQL knowledge — reading the generated SQL with LogTo (configured on the DbContextOptionsBuilder) or ToQueryString() is essential for diagnosing slow queries.

What Code First Is

• Model as code: You write entity classes with navigation properties that represent tables and relationships.

• Schema generation: EF Core creates the database and tables automatically, either at first use (for development) or through migrations.

• Migrations: Schema changes are captured as incremental migration files that can be reviewed, scripted and applied to different environments.

• Convention over configuration: Naming conventions infer primary keys, foreign keys and column names, though you can override them with data annotations or the fluent API in OnModelCreating.

How It Builds on ADO.NET

EF Core does not replace ADO.NET; it consumes it. Each database provider (for example the SQL Server provider) is implemented on top of the ADO.NET provider model: - DbConnection: EF Core opens and closes connections through the provider’s connection object. - DbCommand: Generated SQL is sent to the server via commands, with parameters added using explicit types. - DbDataReader: Query results are materialized from a streaming reader into tracked entity objects. - DbTransaction: SaveChanges wraps commands in a transaction when multiple writes occur.

You never see these objects in typical Code First code, but they are the transport layer EF Core relies on.

Core Components

When Raw ADO.NET Is Useful

Code First covers most scenarios, but dropping to ADO.NET is appropriate for bulk operations, database-specific features EF Core does not model, or performance-critical queries where you want full control over SQL and parameters. You can reach the underlying connection through DbContext.Database.GetDbConnection() and create commands from it. Note that if you open the connection yourself, EF Core will not close it for you.

Best Practices and Pitfalls

• Never use AddWithValue: always add parameters with an explicit SqlDbType and Size where relevant.

• Migrations in production: generate SQL scripts (Script-Migration) and review them before applying to production databases.

• Connection ownership: when you manually open the connection for raw ADO.NET, dispose or close it yourself; EF Core only manages connections it opens.

• Change tracking cost: for read-only reporting queries, use AsNoTracking() to avoid the overhead of tracking entities.

• Know the abstraction: Code First is convenient, but understanding the ADO.NET layer helps you diagnose performance issues, parameter sniffing and transaction behaviour.

How It Works

Database First in EF Core is implemented through scaffolding (reverse engineering). In practice you run dotnet ef dbcontext scaffold with a connection string and the provider package; the tooling reads system catalog views to discover tables, columns, primary keys, foreign keys, and constraints, then emits C# code that represents that structure. This is the natural fit when the database already exists and is managed independently of the application—for example, in legacy modernization projects or in environments where database administrators own the schema.

Note that the visual EDMX-based workflows (Model First / Database First) available in EF6 are not supported in EF Core; scaffolding is the supported equivalent for existing databases.

Generated Artifacts

Scaffolding produces three main artifacts that compose the data access layer:

• Entity Classes: Plain C# objects (POCOs) whose properties match table columns. By default they are mapped via the Fluent API; data annotations are generated only if you pass the --data-annotations flag.

• DbContext: A derived context class with a DbSet<T> property for each table and an OnConfiguring method that wires up the connection string (unless you suppress it).

• Fluent API Mapping: The OnModelCreating method holds the detailed configuration—keys, relationships, column types, nullability—that cannot be expressed (or is not expressed) through data annotations.

Database First vs Code First

When It Is the Right Choice

Choose Database First when: - The database already exists and is the authoritative source of truth. - The schema is maintained by a DBA or another team and changes are applied outside your codebase. - You are modernizing a legacy application and want to avoid rewriting the schema. - You need to integrate with a third-party or vendor-managed database.

Avoid it for greenfield projects where you want the model to drive schema evolution, because Code First with Migrations gives you version-controlled, repeatable schema changes.

Best Practices and Pitfalls

• Use partial classes: Generated files are overwritten on rescaffold. Extend entities or the context with partial class definitions in separate files to keep custom logic safe.

• Treat generated code as read-only: Rescaffolding regenerates files and discards manual edits inside them.

• Control naming: Plural table names or reserved keywords can produce awkward C# identifiers; use --namespace, --table, and schema options to shape the output.

• Beware migration drift: You can enable Migrations after scaffolding in EF Core 8, but applying them to a database managed externally can cause the schema and model to diverge.

• Protect secrets: Never commit connection strings with credentials to source control. Use integrated security, user secrets, or environment variables, and keep the connection string out of the command history.