Microsoft ADO.NET driver for Microsoft Fabric Data Engineering (preview)

Important

This feature is in preview.

ADO.NET is a widely adopted data access technology in the .NET ecosystem that enables applications to connect to and work with data from databases and big data platforms.

The Microsoft ADO.NET Driver for Fabric Data Engineering lets you connect, query, and manage Spark workloads in Microsoft Fabric with the reliability and simplicity of standard ADO.NET patterns. Built on Microsoft Fabric's Livy APIs, the driver provides secure and flexible Spark SQL connectivity to your .NET applications using familiar DbConnection, DbCommand, and DbDataReader abstractions.

Key features

ADO.NET Compliant: Full implementation of ADO.NET abstractions (DbConnection, DbCommand, DbDataReader, DbParameter, DbProviderFactory)
Microsoft Entra ID Authentication: Multiple authentication flows including Azure CLI, interactive browser, client credentials, certificate-based, and access token authentication
Spark SQL Native Query Support: Direct execution of Spark SQL statements with parameterized queries
Comprehensive Data Type Support: Support for all Spark SQL data types including complex types (ARRAY, MAP, STRUCT)
Connection Pooling: Built-in connection pool management for improved performance
Session Reuse: Efficient Spark session management to reduce startup latency
Async Prefetch: Background data loading for improved performance with large result sets
Auto-Reconnect: Automatic session recovery after connection failures

Note

In open-source Apache Spark, database and schema are used synonymously. For example, running SHOW SCHEMAS or SHOW DATABASES in a Fabric Notebook returns the same result — a list of all schemas in the Lakehouse.

Prerequisites

Before using the Microsoft ADO.NET Driver for Microsoft Fabric Data Engineering, ensure you have:

.NET Runtime: .NET 8.0 or later
Microsoft Fabric Access: Access to a Microsoft Fabric workspace with Data Engineering capabilities
Azure Entra ID Credentials: Appropriate credentials for authentication
Workspace and Lakehouse IDs: GUID identifiers for your Fabric workspace and lakehouse
Azure CLI (optional): Required for Azure CLI authentication method

Download, include, reference, and verify

Download NuGet package

Microsoft ADO.NET Driver for Microsoft Fabric Data Engineering version 1.0.0 is in public preview which you can download from these download center links.

Reference NuGet package in your project

Include the downloaded NuGet package in your project and add a reference of the package to your project file:

<ItemGroup>
    <PackageReference Include="Microsoft.Spark.Livy.AdoNet" Version="1.0.0" />
</ItemGroup>

Verify installation

After inclusion and reference, verify the package is available in your project:

using Microsoft.Spark.Livy.AdoNet;

// Verify the provider is registered
var factory = LivyProviderFactory.Instance;
Console.WriteLine($"Provider: {factory.GetType().Name}");

Quick start example

using Microsoft.Spark.Livy.AdoNet;

// Connection string with required parameters
string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=AzureCli;";

// Create and open connection
using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Console.WriteLine("Connected successfully!");

// Execute a query
using var command = connection.CreateCommand();
command.CommandText = "SELECT 'Hello from Fabric!' as message";

using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
    Console.WriteLine(reader.GetString(0));
}

Connection string format

Basic format

The Microsoft ADO.NET Driver uses standard ADO.NET connection string format:

Parameter1=Value1;Parameter2=Value2;...

Required parameters

Parameter	Description	Example
`Server`	Microsoft Fabric API endpoint	`https://api.fabric.microsoft.com/v1`
`SparkServerType`	Server type identifier	`Fabric`
`FabricWorkspaceID`	Microsoft Fabric workspace identifier (GUID)	`<workspace-id>`
`FabricLakehouseID`	Microsoft Fabric lakehouse identifier (GUID)	`<lakehouse-id>`
`AuthFlow`	Authentication method	`AzureCli`, `BrowserBased`, `ClientSecretCredential`, `ClientCertificateCredential`, `AuthAccessToken`, `FileToken`

Optional parameters

Connection settings

Parameter	Type	Default	Description
`LivySessionTimeoutSeconds`	Integer	`60`	Time in seconds to wait for session creation
`LivyStatementTimeoutSeconds`	Integer	`600`	Time in seconds to wait for statement execution
`SessionName`	String	(auto)	Custom name for the Spark session
`AutoReconnect`	Boolean	`false`	Enable automatic session recovery

Connection pool settings

Parameter	Type	Default	Description
`ConnectionPoolEnabled`	Boolean	`true`	Enable connection pooling
`MinPoolSize`	Integer	`1`	Minimum connections in the pool
`MaxPoolSize`	Integer	`20`	Maximum connections in the pool
`ConnectionMaxIdleTimeMs`	Integer	`1800000`	Maximum idle time before connection is recycled (30 min)
`MaxLifetimeMs`	Integer	`3600000`	Maximum lifetime of a pooled connection (60 min)
`ValidateConnections`	Boolean	`true`	Validate connections before use
`ValidationTimeoutMs`	Integer	`5000`	Timeout for connection validation

Logging settings

Parameter	Type	Default	Description
`LogLevel`	String	`Information`	Log level: `Trace`, `Debug`, `Information`, `Warning`, `Error`
`LogFilePath`	String	(none)	Path for file-based logging

Note

Cross-driver aliases: The driver accepts JDBC and ODBC property names in addition to native ADO.NET names (e.g., WorkspaceId maps to FabricWorkspaceID, LakehouseId maps to FabricLakehouseID). All property names are case-insensitive.

Example connection strings

Basic connection (Azure CLI authentication)

Server=https://api.fabric.microsoft.com/v1;SparkServerType=Fabric;FabricWorkspaceID=<workspace-id>;FabricLakehouseID=<lakehouse-id>;AuthFlow=AzureCli

With connection pooling options

Server=https://api.fabric.microsoft.com/v1;SparkServerType=Fabric;FabricWorkspaceID=<workspace-id>;FabricLakehouseID=<lakehouse-id>;AuthFlow=AzureCli;ConnectionPoolEnabled=true;MinPoolSize=2;MaxPoolSize=10

With auto-reconnect and logging

Server=https://api.fabric.microsoft.com/v1;SparkServerType=Fabric;FabricWorkspaceID=<workspace-id>;FabricLakehouseID=<lakehouse-id>;AuthFlow=AzureCli;AutoReconnect=true;LogLevel=Debug

Authentication

The Microsoft ADO.NET Driver supports multiple authentication methods through Microsoft Entra ID (formerly Azure Active Directory). Authentication is configured using the AuthFlow parameter in the connection string.

Authentication methods

AuthFlow Value	Description	Best For
`AzureCli`	Uses Azure CLI cached credentials	Development and testing
`BrowserBased`	Interactive browser-based authentication	User-facing applications
`ClientSecretCredential`	Service principal with client secret	Automated services, background jobs
`ClientCertificateCredential`	Service principal with certificate	Enterprise applications
`AuthAccessToken`	Pre-acquired bearer access token	Custom authentication scenarios

Azure CLI authentication

Best for: Development and testing

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=AzureCli;";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Prerequisites:

Azure CLI installed: az --version
Logged in: az login

Interactive browser authentication

Best for: User-facing applications

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=BrowserBased;" +
    "AuthTenantID=<tenant-id>;";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync(); // Opens browser for authentication

Behavior:

Opens a browser window for user authentication
Credentials are cached for subsequent connections

Client credentials (service principal) authentication

Best for: Automated services and background jobs

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=ClientSecretCredential;" +
    "AuthTenantID=<tenant-id>;" +
    "AuthClientID=<client-id>;" +
    "AuthClientSecret=<client-secret>;";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Required parameters:

AuthTenantID: Azure tenant ID
AuthClientID: Application (client) ID from Microsoft Entra ID
AuthClientSecret: Client secret from Microsoft Entra ID

Certificate-based authentication

Best for: Enterprise applications requiring certificate-based authentication

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=ClientCertificateCredential;" +
    "AuthTenantID=<tenant-id>;" +
    "AuthClientID=<client-id>;" +
    "AuthCertificatePath=C:\\certs\\mycert.pfx;" +
    "AuthCertificatePassword=<password>;";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Required parameters:

AuthTenantID: Azure tenant ID
AuthClientID: Application (client) ID
AuthCertificatePath: Path to PFX/PKCS12 certificate file
AuthCertificatePassword: Certificate password

Access token authentication

Best for: Custom authentication scenarios

// Acquire token through your custom mechanism
string accessToken = await AcquireTokenFromCustomSourceAsync();

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=AuthAccessToken;" +
    $"AuthAccessToken={accessToken};";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Note

We strongly recommend avoiding hard‑coding credentials such as passwords, keys, secrets, tokens, or certificates in your code. Instead, use Azure Key Vault to securely store these values and retrieve them at runtime.

Usage examples

Basic connection and query

using Microsoft.Spark.Livy.AdoNet;

string connectionString =
    "Server=https://api.fabric.microsoft.com/v1;" +
    "SparkServerType=Fabric;" +
    "FabricWorkspaceID=<workspace-id>;" +
    "FabricLakehouseID=<lakehouse-id>;" +
    "AuthFlow=AzureCli;";

using var connection = new LivyConnection(connectionString);
await connection.OpenAsync();

Console.WriteLine($"Connected! Server version: {connection.ServerVersion}");

// Execute a query
using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM employees LIMIT 10";

using var reader = await command.ExecuteReaderAsync();

// Print column names
for (int i = 0; i < reader.FieldCount; i++)
{
    Console.Write($"{reader.GetName(i)}\t");
}
Console.WriteLine();

// Print rows
while (await reader.ReadAsync())
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
        Console.Write($"{reader.GetValue(i)}\t");
    }
    Console.WriteLine();
}

Parameterized queries

using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM orders WHERE order_date >= @startDate AND status = @status";

// Add parameters
command.Parameters.AddWithValue("@startDate", new DateTime(2024, 1, 1));
command.Parameters.AddWithValue("@status", "completed");

using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"Order: {reader["order_id"]}, Total: {reader["total"]:C}");
}

ExecuteScalar for single values

using var command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM customers";

var count = await command.ExecuteScalarAsync();
Console.WriteLine($"Total customers: {count}");

ExecuteNonQuery for DML operations

// INSERT
using var insertCommand = connection.CreateCommand();
insertCommand.CommandText = @"
    INSERT INTO employees (id, name, department, salary)
    VALUES (100, 'John Doe', 'Engineering', 85000)";

int rowsAffected = await insertCommand.ExecuteNonQueryAsync();
Console.WriteLine($"Inserted {rowsAffected} row(s)");

// UPDATE
using var updateCommand = connection.CreateCommand();
updateCommand.CommandText = "UPDATE employees SET salary = 90000 WHERE id = 100";

rowsAffected = await updateCommand.ExecuteNonQueryAsync();
Console.WriteLine($"Updated {rowsAffected} row(s)");

// DELETE
using var deleteCommand = connection.CreateCommand();
deleteCommand.CommandText = "DELETE FROM employees WHERE id = 100";

rowsAffected = await deleteCommand.ExecuteNonQueryAsync();
Console.WriteLine($"Deleted {rowsAffected} row(s)");

Working with large result sets

using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM large_table";

using var reader = await command.ExecuteReaderAsync();

int rowCount = 0;
while (await reader.ReadAsync())
{
    // Process each row
    ProcessRow(reader);
    rowCount++;

    if (rowCount % 10000 == 0)
    {
        Console.WriteLine($"Processed {rowCount} rows...");
    }
}

Console.WriteLine($"Total rows processed: {rowCount}");

Schema discovery

// List all tables
using var showTablesCommand = connection.CreateCommand();
showTablesCommand.CommandText = "SHOW TABLES";

using var tablesReader = await showTablesCommand.ExecuteReaderAsync();
Console.WriteLine("Available tables:");
while (await tablesReader.ReadAsync())
{
    Console.WriteLine($"  {tablesReader.GetString(0)}");
}

// Describe table structure
using var describeCommand = connection.CreateCommand();
describeCommand.CommandText = "DESCRIBE employees";

using var schemaReader = await describeCommand.ExecuteReaderAsync();
Console.WriteLine("\nTable structure for 'employees':");
while (await schemaReader.ReadAsync())
{
    Console.WriteLine($"  {schemaReader["col_name"]}: {schemaReader["data_type"]}");
}

Using LivyConnectionStringBuilder

using Microsoft.Spark.Livy.AdoNet;

var builder = new LivyConnectionStringBuilder
{
    Server = "https://api.fabric.microsoft.com/v1",
    SparkServerType = "Fabric",
    FabricWorkspaceID = "<workspace-id>",
    FabricLakehouseID = "<lakehouse-id>",
    AuthFlow = "AzureCli",
    ConnectionPoolingEnabled = true,
    MinPoolSize = 2,
    MaxPoolSize = 10,
    ConnectionTimeout = 60
};

using var connection = new LivyConnection(builder.ConnectionString);
await connection.OpenAsync();

Using DbProviderFactory

using System.Data.Common;
using Microsoft.Spark.Livy.AdoNet;

// Register the provider factory (typically done at application startup)
DbProviderFactories.RegisterFactory("Microsoft.Spark.Livy.AdoNet", LivyProviderFactory.Instance);

// Create connection using factory
var factory = DbProviderFactories.GetFactory("Microsoft.Spark.Livy.AdoNet");

using var connection = factory.CreateConnection();
connection.ConnectionString = connectionString;

await connection.OpenAsync();

using var command = factory.CreateCommand();
command.Connection = connection;
command.CommandText = "SELECT * FROM employees LIMIT 5";

using var reader = await command.ExecuteReaderAsync();
// Process results...

Data type mapping

The driver maps Spark SQL data types to .NET types:

Spark SQL Type	.NET Type	DbType
BOOLEAN	`bool`	Boolean
TINYINT	`sbyte`	SByte
SMALLINT	`short`	Int16
INT	`int`	Int32
BIGINT	`long`	Int64
FLOAT	`float`	Single
DOUBLE	`double`	Double
DECIMAL(p,s)	`decimal`	Decimal
STRING	`string`	String
VARCHAR(n)	`string`	String
CHAR(n)	`string`	String
BINARY	`byte[]`	Binary
DATE	`DateTime`	Date
TIMESTAMP	`DateTime`	DateTime
ARRAY<T>	`T[]` or `string` (JSON)	Object
MAP<K,V>	`Dictionary<K,V>` or `string` (JSON)	Object
STRUCT	`object` or `string` (JSON)	Object

Working with complex types

Complex types (ARRAY, MAP, STRUCT) are returned as JSON strings by default:

using System.Text.Json;
using System.Collections.Generic;

using var command = connection.CreateCommand();
command.CommandText = "SELECT array_column, map_column, struct_column FROM complex_table LIMIT 1";

using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
    // Complex types returned as JSON strings
    string arrayJson = reader.GetString(0);  // e.g., "[1, 2, 3]"
    string mapJson = reader.GetString(1);    // e.g., "{\"key\": \"value\"}"
    string structJson = reader.GetString(2); // e.g., "{\"field1\": 1, \"field2\": \"text\"}"

    // Parse with System.Text.Json
    var array = JsonSerializer.Deserialize<int[]>(arrayJson);
    var map = JsonSerializer.Deserialize<Dictionary<string, string>>(mapJson);
}

Troubleshooting

This section provides guidance for resolving common issues you might encounter when using the Microsoft ADO.NET Driver for Microsoft Fabric Data Engineering.

Common issues

The following sections describe common problems and their solutions:

Connection failures

Problem: Can't connect to Microsoft Fabric

Solutions:

Verify FabricWorkspaceID and FabricLakehouseID are correct GUIDs
Check Azure CLI authentication: az account show
Ensure you have appropriate Fabric workspace permissions
Verify network connectivity to api.fabric.microsoft.com

Authentication errors

Problem: Authentication fails with Azure CLI

Solutions:

Run az login to refresh credentials
Verify correct tenant: az account set --subscription <subscription-id>
Check token validity: az account get-access-token --resource https://api.fabric.microsoft.com

Query timeouts

Problem: Queries timing out on large tables

Solutions:

Increase statement timeout: LivyStatementTimeoutSeconds=300
Use LIMIT clause to restrict result size during development
Ensure Spark cluster has adequate resources

Session creation timeout

Problem: Connection times out during session creation

Solutions:

Increase session timeout: LivySessionTimeoutSeconds=120
Check Fabric capacity availability
Verify workspace hasn't reached session limits

Enable logging

When troubleshooting issues, enabling detailed logging can help you identify the root cause of problems. You can enable logging through the connection string or programmatically.

To enable detailed logging via connection string:

LogLevel=Debug

Or configure programmatically:

using Microsoft.Extensions.Logging;

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder.AddConsole();
    builder.SetMinimumLevel(LogLevel.Debug);
});

// Logging is automatically integrated with the connection

Log levels:

Trace: Most verbose, includes all API calls
Debug: Detailed debugging information
Information: General information (default)
Warning: Warnings only
Error: Errors only

Feedback

Was this page helpful?

Last updated on 2026-03-18

Share via

Microsoft ADO.NET driver for Microsoft Fabric Data Engineering (preview)

Key features

Prerequisites

Download, include, reference, and verify

Download NuGet package

Reference NuGet package in your project

Verify installation

Quick start example

Connection string format

Basic format

Required parameters

Optional parameters

Connection settings

Connection pool settings

Logging settings

Example connection strings

Basic connection (Azure CLI authentication)

With connection pooling options

With auto-reconnect and logging

Authentication

Authentication methods

Azure CLI authentication

Interactive browser authentication

Client credentials (service principal) authentication

Certificate-based authentication

Access token authentication

Usage examples

Basic connection and query

Parameterized queries

ExecuteScalar for single values

ExecuteNonQuery for DML operations

Working with large result sets

Schema discovery

Using LivyConnectionStringBuilder

Using DbProviderFactory

Data type mapping

Working with complex types

Troubleshooting

Common issues

Connection failures

Authentication errors

Query timeouts

Session creation timeout

Enable logging

Related content

Feedback

Additional resources