This is an implementation of .NET 6 Core driver for Firebolt in a form of a DbConnection class. Supports all latest .NET frameworks and all platforms.
This project is developed under Visual Studio 2022. Earlier versions of Visual Studio are not supported.
Here is a FireboltNetSDK NuGet page.
- Install using .NET CLI
dotnet add package FireboltNetSDK - Install using Visual Studio UI
Tools>NuGet Package Manager>Manage NuGet Packages for Solutionand search forFirebolt
- Install using Package Manager Console:
PM> Install-Package FireboltNetSDK Following examples demonstrate how to connect and interact with Firebolt database using this driver:
// Name of your Firebolt account string account = "my_firebolt_account"; // Client credentials, that you want to use to connect string clientId = "my_client_id"; string clientSecret = "my_client_secret"; // Name of database and engine to connect to (Optional) string database = "my_database_name"; string engine = "my_engine_name"; // Construct a connection string using defined parameter string conn_string = $"account={account};clientid={clientId};clientsecret={clientSecret};database={database};engine={engine}";using FireboltDotNetSdk.Client; // Create a new connection using generated connection string using var conn = new FireboltConnection(conn_string); // Open a connection conn.Open(); // Execute SQL, fetch data, ... // Close the connection after all operations are done conn.Close();// First you would need to create a command var command = conn.CreateCommand(); // ... and set the SQL query command.CommandText = "CREATE DATABASE IF NOT EXISTS MY_DB"; // Execute a SQL query and get a DB reader command.ExecuteNonQuery(); // Close the connection after all operations are done conn.Close();// First you would need to create a command var command = conn.CreateCommand(); // ... and set the SQL query command.CommandText = "SELECT * FROM my_table"; // Execute a SQL query and get a DB reader DbDataReader reader = command.ExecuteReader(); // Optionally you can check whether the result set has rows Console.WriteLine($"Has rows: {reader.HasRows}"); // Discover the result metadata int n = reader.FieldCount(); for (int i = 0; i < n; i++) { Type type = reader.GetFieldType(); string name = reader.GetName(); } // Iterate over the rows and get values while (reader.Read()) { for (int i = 0; i < n; i++) { Console.WriteLine($"{reader.GetName(i)}:{reader.GetFieldType(i)}={reader.GetValue(i)}"); } }var tz = conn.CreateCommand(); tz.CommandText = "SET time_zone=America/New_York"; tz.ExecuteNonQuery(); tz.CommandText = "SELECT '2000-01-01 12:00:00.123456 Europe/Berlin'::timestamptz as t"; DbDataReader tzr = tz.ExecuteReader(); if (tzr.Read()) { // 2000-01-01 06:00:00.123456-05 Console.WriteLine(tzr.GetDateTime(0)); }Firebolt supports server-side asynchronous query execution, allowing queries to run in the background while you retrieve results later. This is particularly useful for long-running queries, as it eliminates the need to maintain a persistent connection to the server while waiting for execution to complete.
⚠ Note: This is different from .NET's asynchronous programming model. Firebolt's server-side async execution means that the query runs independently on the server, while .NET async/await handles non-blocking execution on the client side.
Executing a query asynchronously means the database will start processing it in the background. Instead of returning data immediately, the response contains a query token, which can be used later (even in a new connection) to check the query status or retrieve results.
FireboltCommand command = (FireboltCommand)conn.CreateCommand(); command.CommandText = "INSERT INTO large_table SELECT * FROM source_table"; // Execute the query asynchronously on the server command.ExecuteServerSideAsyncNonQuery(); // Alternatively, use .NET's async/await to avoid blocking the client thread await command.ExecuteServerSideAsyncNonQueryAsync(); // Store the async query token for later use string token = command.AsyncToken;You can check if the query is still running or if it has finished executing.
IsServerSideAsyncQueryRunning(token)returnstrueif the query is still in progress andfalseif it has finished.IsServerSideAsyncQuerySuccessful(token)returns:trueif the query completed successfullyfalseif the query failednullif the query is still running
using FireboltConnection conn = new FireboltConnection(conn_string); conn.Open(); // Check if the query is still running bool isRunning = conn.IsServerSideAsyncQueryRunning(token); // Check if the query completed successfully (returns null if it's still running) bool? isSuccessful = conn.IsServerSideAsyncQuerySuccessful(token);or use .NET asynchronous eqivalents
// Check if the query is still running bool isRunning = await conn.IsServerSideAsyncQueryRunningAsync(token); // Check if the query completed successfully (returns null if it's still running) bool? isSuccessful = await conn.IsServerSideAsyncQuerySuccessfulAsync(token);If an asynchronous query is no longer needed, you can cancel it before execution completes.
using FireboltConnection conn = new FireboltConnection(conn_string); conn.Open(); // Cancel the async query bool cancelled = conn.CancelServerSideAsyncQuery(token);or do so asynchronously
bool cancelled = await conn.CancelServerSideAsyncQueryAsync(token);This approach ensures that long-running queries do not block your application while allowing you to monitor, manage, and cancel them as needed.
Firebolt supports server-side prepared statement execution, allowing better safety when using client provided inputs, not constructing the query client-side.
To make use of server-side prepared statements, you need to provide the following parameter in your connection string:
preparedStatementParamStyle=FbNumeric This makes any prepared statement constructed from the connection to use the FbNumeric parameter style, which is required for server-side prepared statements.
⚠ Note: Using this parameter, normal prepared statements will not work, so you need to use server-side prepared statements only.
Other than this parameter, the API is the same, except for the command text.
var command = (FireboltCommand)conn.CreateCommand(); command.CommandText = "SELECT * FROM my_table WHERE id = $1"; command.Parameters.AddWithValue("$1", 123); command.Prepare(); // Execute the query as any other command using var reader = command.ExecuteReader();The preparedStatementParamStyle parameter in the connection string can take the following values:
Native(@paramName) - default: Uses the native parameter style, which is compatible with client-side prepared statements.FbNumeric($number): Uses Firebolt's numeric parameter style, which is required for server-side prepared statements.
Firebolt supports query result streaming, allowing you to retrieve large datasets in a memory-efficient manner. This is particularly useful for queries that return a significant amount of data, as it avoids loading the entire result set into memory at once.
To execute a query that returns a large result set, you can use the ExecuteStreamedQuery method or it's asynchronous equivalent ExecuteStreamedQueryAsync. This method allows you to stream the results directly from the server without loading them all into memory at once.
FireboltCommand command = (FireboltCommand)conn.CreateCommand(); command.CommandText = "SELECT * FROM large_table"; // Execute the query asynchronously on the server using var reader = command.ExecuteStreamedQuery(); // or use the asynchronous version using var reader = await command.ExecuteStreamedQueryAsync(); // Iterate over the streamed results in the same way as with a regular DbDataReader while (await reader.ReadAsync()) { for (int i = 0; i < reader.FieldCount; i++) { Console.WriteLine($"{reader.GetName(i)}: {reader.GetValue(i)}"); } }The Firebolt .NET SDK provides full support for database transactions, allowing you to group multiple operations into atomic units of work.
using var connection = new FireboltConnection(connectionString); await connection.OpenAsync(); // Begin a transaction using var transaction = connection.BeginTransaction(); try { // Execute multiple commands within the transaction using var command1 = connection.CreateCommand(); command1.CommandText = "INSERT INTO customers (name) VALUES ('John Doe')"; await command1.ExecuteNonQueryAsync(); using var command2 = connection.CreateCommand(); command2.CommandText = "UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123"; await command2.ExecuteNonQueryAsync(); // Commit the transaction if all operations succeed transaction.Commit(); } catch (Exception) { // Rollback the transaction if any operation fails transaction.Rollback(); throw; }The SDK supports both synchronous and asynchronous transaction operations:
// Asynchronous commit await transaction.CommitAsync(); // Asynchronous rollback await transaction.RollbackAsync();If a transaction is disposed without being explicitly committed or rolled back, it will automatically rollback:
using var connection = new FireboltConnection(connectionString); await connection.OpenAsync(); using (var transaction = connection.BeginTransaction()) { // Execute commands... // If an exception occurs here, the transaction will automatically rollback when disposed } // Transaction is automatically rolled back if not committedThe FireboltTransaction class provides properties to check the current state:
var transaction = connection.BeginTransaction(); // Check if transaction has been completed bool isCompleted = transaction.IsCompleted; // Check if transaction has been committed bool isCommitted = transaction.IsCommitted; // Check if transaction has been rolled back bool isRolledBack = transaction.IsRolledBack;If you prefer manual transaction management, you can use the FireboltConnection class to control transactions explicitly:
using var connection = new FireboltConnection(connectionString); await connection.OpenAsync(); // Start a transaction manually var command = connection.CreateCommand(); command.CommandText = "BEGIN TRANSACTION"; await command.ExecuteNonQueryAsync(); // Execute your SQL commands // Commit the transaction command.CommandText = "COMMIT"; await command.ExecuteNonQueryAsync(); // If you need to rollback, you can do so command.CommandText = "ROLLBACK"; await command.ExecuteNonQueryAsync();TransactionScope is not supported in the Firebolt .NET SDK. Instead, you should use the explicit transaction management provided by the SDK, as shown in the examples above.