SQL Data Adapter – Quick Start

This quick start shows how to use POCO DataSet with SQL Server via SqlDataAdapter. It covers both single-table loading and multi-table workflows where several queries populate the same dataset, with relations automatically inferred from the database schema.

When to use this workflow

Use the SQL Data Adapter when SQL Server is your persistence layer and you want a schema-aware, metadata-driven dataset that supports disconnected editing, explicit save/merge workflows, and relation-aware persistence.

Prerequisites

Core workflow

  1. Load schema and data from SQL Server into a POCO DataSet
  2. Edit data in memory (row state is tracked automatically)
  3. Create a delta changeset
  4. Save changes to SQL Server
  5. Merge post-save values back into the original dataset

1. Load schema and data from SQL Server into a POCO DataSet

Single-table example

string connectionString = "Server=.;Database=HumanResources;Trusted_Connection=True;";
SqlDataAdapter adapter = new SqlDataAdapter(connectionString);

IDataSet dataSet = await adapter.FillAsync("SELECT * FROM Department", false, null, null, null, null);

In real applications, related tables are often loaded using multiple independent queries. The SQL Data Adapter supports this via FillIntoExistingDataSet and FillIntoExistingDataSetAsync.

These methods append tables into an existing dataset. Relations are populated from the database schema once the related tables are present.

Example: multiple independent queries

IDataSet dataSet = DataSetFactory.CreateDataSet();
SqlDataAdapter adapter = new SqlDataAdapter(connectionString);

// You can disable automatic relation population if you do not need them,
// but by default it's on and will populate as soon as related tables are loaded
// adapter.PopulateRelationsFromSchema = false;

// Load Department table
await adapter.FillIntoExistingDataSetAsync(dataSet, "SELECT * FROM Department", false, null, null, null);

// Load Employee table into the SAME dataset
await adapter.FillIntoExistingDataSetAsync(dataSet, "SELECT * FROM Employee", false, null, null, null);

2. Working with the data

Once both tables are loaded, you can work with the data to suit your business needs. Rows state is tracked automatically.

3. Create changeset

// After making changes to the dataset (adding, modifying, deleting rows), create a changeset
IDataSet changeset = dataSet.CreateChangeset();

4. Saving changes

// Save changes to SQL Server using the default row-by-row save mode
await adapter.SaveChangesAndMergePostSaveAsync(changeset);

By default, SqlDataAdapter uses SqlSaveMode.RowByRow. This preserves the existing relation-aware save behavior.

To opt into generated Save stored procedures that accept SQL Server table-valued parameters, pass explicit adapter options.

SqlDataAdapterOptions options = new SqlDataAdapterOptions();
options.SaveMode = SqlSaveMode.BatchStoredProcedure;

await adapter.SaveChangesAndMergePostSaveAsync(changeset, options);

Batch stored procedure mode uses table-valued parameters that include the __ChangeState metadata column. Generated Save procedures should use this column to classify rows as insert, update, or delete instead of relying on negative primary-key values.

As a result, all rows return to the Unchanged state, and server-generated values (identity, rowversion, etc.) are reflected in the dataset.

Advanced usage

The examples above use SqlDataAdapter in its default, simple workflow, where connection and transaction lifetime are managed automatically.

For advanced scenarios such as executing multiple commands under a single transaction or explicitly controlling commit and rollback behavior, see Transactions and Advanced Usage.

Table of Content POCO DataSet Quick Start Next: Transactions and Advanced Usage

 

Business Process Programming in .Net
© 2004–2026 Laskarzhevsky Software Inc.
Unless otherwise noted, the content of this website is licensed under the Creative Commons Attribution 4.0 International License (CC BY 4.0).
Code examples are provided under the MIT License.
You are free to share and adapt the material provided that appropriate credit is given and any modifications are clearly indicated.
The information provided on this website is for educational purposes only.
The author and publisher make no warranties regarding the completeness or suitability of the information and are not responsible for any damages resulting from its use.