Use the driver with ADO.NET and Visual Studio

.NET programs reach an OData service through the driver with System.Data.Odbc – reading, DataAdapter.Fill, CommandBuilder, parameters – and the Visual Studio wizards work with it as they do with a SQL Server data source. Three settings decide whether writing succeeds.

ADO.NET and the TableAdapter Configuration Wizard generate UPDATE and DELETE according to the rules of the CommandBuilder. The default (ConflictOption.CompareAllSearchableValues, in the wizard „Use optimistic concurrency“) compares every column with its old value. That can't be expressed in OData: with an address for custom statements, the statement goes to the database unchanged; without one, the driver rejects it before anything is written.

1. Connect as usual; the bitness of the process determines the driver (Visual Studio 2022 and later is 64-bit):

   using var connection = new OdbcConnection(
       "DRIVER={Wkl OData};URL=https://daten.example.com/odata/v4/fachdaten/dbo/;AUTH=WINDOWS");
   connection.Open();

2. Choose the concurrency rule:

   Situation	Setting
   Address for custom statements available	the default is sufficient; the database checks the value comparisons
   no address, table with row version or row stamp (gw_rowstamp)	ConflictOption.CompareRowVersion, include the column in the query; in the wizard, clear „Use optimistic concurrency“
   no address, conflicts don't matter	ConflictOption.OverwriteChanges

3. For names with special characters, set QuotePrefix = "[" and QuoteSuffix = "]" on the OdbcCommandBuilder; without them, it writes the names unquoted, with any driver.

Result: Adding, changing, and deleting through the CommandBuilder succeed; a change by someone else appears as a DBConcurrencyException. This applies on .NET 10, .NET 8, and .NET Framework 4.8, in 32-bit and 64-bit processes.

• Transactions only where the service offers them. With the data service Wkl.Data.Gateway, BeginTransaction, Commit, and Rollback work as they do through the SQL Server driver; another service without Store.Transactions answers BeginTransaction with HYC00 instead of an apparently opened transaction. See Transactions.
• DateTime only to the millisecond. ADO.NET transmits a bound DateTime only to the millisecond; the driver stores what arrives. The SQL Server driver rejects any fraction in this binding.
• Views appear as tables. $metadata doesn't distinguish views from tables; Server Explorer shows them under „Tables“. Reading and querying aren't affected.

• Driver connection string
• Supported SQL
• Set up pass-through queries – the address for custom statements