ADO.NET

The ADO.NET provider is the layer where you write the SQL yourself. Everything is where you expect it to be: DbConnection, DbCommand, DbDataReader, DbTransaction, DbParameter, a data adapter, a command builder and a provider factory.

The basics

using OutWit.Database.AdoNet;

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "SELECT Id, Name FROM Users WHERE IsActive = @active";
command.Parameters.AddWithValue("@active", true);

using var reader = command.ExecuteReader();

while (reader.Read())
    Console.WriteLine($"{reader.GetInt64(0)}: {reader.GetString(1)}");

The async equivalents are all there: OpenAsync, ExecuteReaderAsync, ExecuteNonQueryAsync, ExecuteScalarAsync, ReadAsync.

Parameters

Named parameters, prefixed with @. Two ways to add one, both fine:

command.Parameters.AddWithValue("@name", "Alice");
command.Parameters.Add(new WitDbParameter("@name", DbType.String, 100));

Types are inferred from the value when you do not say. Being explicit is worth it where the inference could go either way, such as a null whose column type matters.

Use parameters rather than building the statement by concatenation. Beyond the injection question, a parameterised statement can be prepared once and reused.

Prepared statements

Prepare() parses and plans the statement and keeps the result on the command. Run the same statement many times with different values and you pay the parse once.

command.CommandText = "INSERT INTO Events (Name, At) VALUES (@name, @at)";
command.Parameters.Add(new WitDbParameter("@name", DbType.String));
command.Parameters.Add(new WitDbParameter("@at", DbType.DateTime));
command.Prepare();

foreach (var e in events)
{
    command.Parameters["@name"].Value = e.Name;
    command.Parameters["@at"].Value = e.At;
    command.ExecuteNonQuery();
}

Changing CommandText discards the prepared statement, so a command reused for different SQL behaves correctly rather than quietly running the old plan.

Stored procedures

CommandType.StoredProcedure works the way it does everywhere else: the routine name goes in CommandText, the parameters go in the collection.

using var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "ArchiveOrdersBefore";
command.Parameters.AddWithValue("@cutoff", DateTime.UtcNow.AddYears(-1));

command.ExecuteNonQuery();

CommandType.TableDirect is refused. It means "the command text is a table name, return all of it", which this provider has no translation for, and refusing it beats answering something approximate.

See WitSQL for writing the routines themselves.

Transactions

using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

using var command = connection.CreateCommand();
command.Transaction = transaction;

// ... statements

transaction.Commit();

Assign the transaction to the command. A command without one runs in autocommit, which for a large write is worth avoiding; see transactions.

Schema metadata

GetSchema answers the standard collections, so tooling that inspects a database generically works against this one.

var tables = connection.GetSchema("Tables");
var columns = connection.GetSchema("Columns", new[] { null, null, "Users" });

For the same information in SQL, see INFORMATION_SCHEMA.

ChangeDatabase throws. One connection is one database, and switching would have to mean something this engine has no concept of.

DataAdapter and CommandBuilder

For code built around DataTable, the adapter fills and updates as usual, and the command builder generates the insert, update and delete from your select:

using var adapter = new WitDbDataAdapter("SELECT Id, Name, Email FROM Users", connection);
using var builder = new WitDbCommandBuilder(adapter);

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

table.Rows[0]["Name"] = "Updated";

adapter.Update(table);

The command builder needs a single-table select that includes the primary key, and a table that has one. Anything more complicated means writing the three commands yourself, which the adapter accepts directly.

Provider factory

For code that picks its database at runtime:

DbProviderFactories.RegisterFactory(
    WitDbProviderFactory.PROVIDER_INVARIANT_NAME,
    WitDbProviderFactory.Instance);

var factory = DbProviderFactories.GetFactory(WitDbProviderFactory.PROVIDER_INVARIANT_NAME);

using var connection = factory.CreateConnection()!;
connection.ConnectionString = configuration.GetConnectionString("Default");
connection.Open();

Use the constant rather than typing the name. It is OutWit.Database.AdoNet, and registering under one string while asking for another is an easy afternoon to lose.

Where to go next

• Connection strings, every parameter
• WitSQL, the language
• EF Core provider, for the parts you would rather not write in SQL
• Transactions, isolation levels and locking