Snowflake Connector for .NET

You can cast Snowflake structured objects (e.g., OBJECT(city VARCHAR, state VARCHAR)) directly into C# customer classes using the T SnowflakeDbReader.GetObject<T>(int ordinal) method.

To control how the driver instantiates your class, use the [SnowflakeObject] attribute with the ConstructionMethod property. There are three supported methods:

1. PROPERTIES_ORDER (Default): Uses the non-parameterized constructor and maps Snowflake fields to properties based on their ordinal position. Use [SnowflakeColumn(IgnoreForPropertyOrder = true)] to skip a property in the sequence.
2. PROPERTIES_NAMES: Uses the non-parameterized constructor and maps Snowflake fields to properties with matching names. Use [SnowflakeColumn(Name = "...")] to map a database field name to a different property name.
3. CONSTRUCTOR: Uses a parameterized constructor. The constructor must have the exact number of parameters as the database object fields, and parameters are mapped based on their order.

// Example of reading an object into a class
var reader = (SnowflakeDbDataReader)command.ExecuteReader();
if (reader.Read())
{
    var address = reader.GetObject<Address>(0);
}

// Example class using Property Names mapping
[SnowflakeObject(ConstructionMethod = SnowflakeObjectConstructionMethod.PROPERTIES_NAMES)]
public class Address
{
    [SnowflakeColumn(Name = "nearestCity")]
    public string city { get; set; }
    public string state { get; set; }
    public Zip zip { get; set; }
}

The Snowflake connector supports two levels of asynchrony: C# async/await and server-side asynchronous execution.

When using server-side asynchronous execution, the server responds immediately with a queryId. You can then use this ID to check the status or wait for results. This allows you to start a query in one session and retrieve results in another.

Note: GET/PUT operations are not supported in asynchronous mode.

Key methods:

• ExecuteAsyncInAsyncMode: Starts the query on the server and returns a queryId.
• GetQueryStatusAsync: Checks the status of a query using its queryId.
• GetResultsFromQueryIdAsync: Repeatedly checks the status until the query completes and returns the results.

using var conn = new SnowflakeDbConnection("account=testaccount;username=testusername;password=testpassword")
await conn.OpenAsync(CancellationToken.None);
SnowflakeDbCommand cmd = (SnowflakeDbCommand)conn.CreateCommand();
cmd.CommandText = "SELECT ...";
var queryId = await cmd.ExecuteAsyncInAsyncMode(CancellationToken.None);

// Check status
var queryStatus = await cmd.GetQueryStatusAsync(queryId, CancellationToken.None);

// Get results
var reader = await cmd.GetResultsFromQueryIdAsync(queryId, CancellationToken.None).ConfigureAwait(false);

The Snowflake .NET connector targets netstandard2.0 but provides optimized multi-targeted builds for specific platforms starting from version 5.2.0.

Supported Frameworks

• .NET Framework 4.6.2, 4.7.1, 4.7.2, 4.8, 4.8.1
• .NET 6.0, 7.0, 8.0, 9.0, 10.0

Platform-Specific Builds

NuGet automatically selects the appropriate build based on your target framework and OS:

• Windows (.NET Framework 4.8.1): Uses net481 (optimized, no Mono.Unix).
• Windows (.NET 8+): Uses net8.0-windows or net10.0-windows (optimized, no Mono.Unix).
• Linux/macOS (.NET 8+): Uses net8.0 or net10.0 (includes Mono.Unix for full file system support).
• Legacy/Other: Uses netstandard2.0 for backward compatibility.

Important Linux Note: Only glibc-based distributions (e.g., Ubuntu, Debian, RHEL, CentOS) are supported. Musl-based (e.g., Alpine) and Android-based runtimes are not supported.

The Snowflake .NET Driver uses connection pools to manage and reuse connections, improving performance.

Multiple Connection Pools

Starting with v4.0.0, the driver supports multiple independent pools. A pool is uniquely identified by its exact connection string. Because the order of parameters in the connection string matters, changing the order will result in a different pool being created.

Pool Lifecycle

A pool is instantiated when a connection is first opened with a specific connection string or when accessed via SnowflakeDbConnectionPool.GetPool(connectionString). The pool manages the lifecycle of connections, ensuring the count stays within the MinPoolSize and MaxPoolSize limits and handling the destruction of expired connections based on the ExpirationTimeout.

Connection Lifecycle

• Opening: If an idle connection is available, it is returned immediately. If not, and MaxPoolSize hasn't been reached, a new connection is opened. If MaxPoolSize is reached, the request waits up to the WaitingForIdleSessionTimeout before throwing an exception.
• Busy: A connection is considered 'busy' while it is being used by the application. It must be closed to be returned to the pool. Failure to close connections can lead to pool exhaustion.
• Closing: When a connection is closed, pending transactions are rolled back. If the session properties (database, schema, warehouse, role) have changed, the connection's fate depends on the ChangedSession setting (see Changed Session Behavior).

using var connection = new SnowflakeDbConnection($"{ConnectionString};application=App1");
await connection.OpenAsync(cancellationToken); // Pool 1 is created

using var connection2 = new SnowflakeDbConnection($"{ConnectionString};application=App2");
await connection2.OpenAsync(cancellationToken); // Pool 2 is created

When an application executes SQL commands that alter the session context (e.g., USE SCHEMA, USE ROLE, USE WAREHOUSE), the connection no longer matches the original state defined in the connection string. You can control how these connections are handled using the ChangedSession parameter:

1. Destroy (Default)

Set ChangedSession=Destroy or omit the parameter. Altered connections are destroyed when closed and are not returned to the pool. The pool will replenish connections to meet the MinPoolSize.

2. OriginalPool

Set ChangedSession=OriginalPool. Altered connections are returned to the pool.

Warning: This is not recommended and is provided for backward compatibility. Connections retrieved from the pool may have a different database, schema, warehouse, or role than the connection string specifies, which can lead to unexpected SQL errors.

// Example: Destroy behavior (default)
var connectionString = ConnectionString + ";ChangedSession=Destroy";
var connection = new SnowflakeDbConnection(connectionString);
connection.Open();
connection.CreateCommand($"create schema \"{Guid.NewGuid()}\"").ExecuteNonQuery();
connection.Close(); // connection is destroyed

The GET command allows downloading stage directories or files to a local directory. It works with named stages, table internal stages, or user stages.

Behavior:

• If a technical or syntax error occurs, a SnowflakeDbException is thrown.
• If no technical errors occur but the DbDataReader has no data to process, ReadAsync() returns false without throwing an exception.

try
{
    conn.ConnectionString = "<connection parameters>";
    await conn.OpenAsync(cancellationToken);
    var cmd = (SnowflakeDbCommand)conn.CreateCommand(); // cast allows get QueryId from the command

    cmd.CommandText = "GET @my_schema.my_stage/stage_file.csv file://local_file.csv AUTO_COMPRESS=TRUE";
    await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
    Assert.IsTrue(await reader.ReadAsync(cancellationToken)); // True on success, False if failure
    Assert.DoesNotThrow(() => Guid.Parse(cmd.GetQueryId()));
}
catch (SnowflakeDbException e)
{
    Assert.DoesNotThrow(() => Guid.Parse(e.QueryId)); // on failure
}

Use the PUT command to upload a single local file or an entire local directory to a Snowflake stage (named, internal table, or internal user stage).

To access the QueryId from the command, you must cast the result of conn.CreateCommand() to SnowflakeDbCommand.

If the operation fails, a SnowflakeDbException is thrown. This exception may contain the QueryId if the failure occurred after the query was executed. The InnerException may provide specific details such as FileNotFoundException or DirectoryNotFoundException.

using var conn = new SnowflakeDbConnection();
try
{
    conn.ConnectionString = "<connection parameters>";
    await conn.OpenAsync(cancellationToken);
    var cmd = (SnowflakeDbCommand)conn.CreateCommand(); // cast allows get QueryId from the command

    cmd.CommandText = "PUT file://some_data.csv @my_schema.my_stage AUTO_COMPRESS=TRUE";
    await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
    Assert.IsTrue(await reader.ReadAsync(cancellationToken));
    Assert.DoesNotThrow(() => Guid.Parse(cmd.GetQueryId()));
}
catch (SnowflakeDbException e)
{
    Assert.DoesNotThrow(() => Guid.Parse(e.QueryId)); // when failed
    Assert.That(e.InnerException.GetType(), Is.EqualTo(typeof(FileNotFoundException)));
}