private static void AdoNetSamples()
{
if (File.Exists("file.db"))
{
File.Delete("file.db");
}
using (var duckDBConnection = new DuckDBConnection("Data Source=file.db"))
{
duckDBConnection.Open();
var command = duckDBConnection.CreateCommand();
command.CommandText = "CREATE TABLE integers(foo INTEGER, bar INTEGER);";
var executeNonQuery = command.ExecuteNonQuery();
command.CommandText = "INSERT INTO integers VALUES (3, 4), (5, 6), (7, NULL);";
executeNonQuery = command.ExecuteNonQuery();
command.CommandText = "Select count(*) from integers";
var executeScalar = command.ExecuteScalar();
command.CommandText = "SELECT foo, bar FROM integers";
var reader = command.ExecuteReader();
PrintQueryResults(reader);
}
}
public void ShouldOpenAndCloseInMemoryConnection()
{
using (var duckDBConnection = new DuckDBConnection())
{
duckDBConnection.Open();
duckDBConnection.Close();
}
}
public async Task ExceptionOnDisposeThenClose()
{
using var dbInfo = DisposableFile.GenerateInTemp("db");
await using var duckDBConnection = new DuckDBConnection(dbInfo.ConnectionString);
await duckDBConnection.OpenAsync();
await duckDBConnection.DisposeAsync();
await duckDBConnection.CloseAsync();
}
private static void AdoNetSamples()
{
if (File.Exists("file.db"))
{
File.Delete("file.db");
}
var duckDb = new DuckDb("Data Source=file.db");
using var duckDBConnection = new DuckDBConnection(duckDb);
duckDBConnection.Open();
using var conn2 = new DuckDBConnection(duckDb);
conn2.Open();
var command = duckDBConnection.CreateCommand();
command.CommandText = "CREATE TABLE integers(foo INTEGER, bar INTEGER);";
var executeNonQuery = command.ExecuteNonQuery();
command.CommandText = "INSERT INTO integers VALUES (3, 4), (5, 6), (7, NULL);";
executeNonQuery = command.ExecuteNonQuery();
command.CommandText = "Select count(*) from integers";
var executeScalar = command.ExecuteScalar();
var command2 = duckDBConnection.CreateCommand();
command2.CommandText = "SELECT foo, bar FROM integers";
var reader = command2.ExecuteReader();
PrintQueryResults(reader);
try
{
command.CommandText = "Not a valid Sql statement";
var causesError = command.ExecuteNonQuery();
}
catch (DuckDBException e)
{
Console.WriteLine(e.Message);
}
//sending a dapper queury
duckDBConnection.Execute("CREATE TABLE integers2(foo INTEGER, bar INTEGER);");
}
public async Task ConnectionSpeed()
{
const int taskCount = 5;
const int fileCount = taskCount * 5;
const int operationCount = 300;
const int totalOperations = taskCount * operationCount;
using var files = new DisposableFileList(fileCount, "db");
//open and close files with some overlap
var openAndClose = new Func <int, Task>(async ti =>
{
var rnd = new Random(ti);
for (int i = 0; i < operationCount; i++)
{
var cs = files[rnd.Next(fileCount)].ConnectionString;
await using var duckDBConnection = new DuckDBConnection(cs);
await duckDBConnection.OpenAsync();
}
});
var tasks = new Task[taskCount];
var stopwatch = Stopwatch.StartNew();
//it's hammer time baby!
for (int i = 0; i < taskCount; i++)
{
var index = i;
tasks[i] = Task.Run(async() => await openAndClose(index));
}
await Task.WhenAll(tasks);
var elapsed = stopwatch.Elapsed.TotalSeconds;
var operationsPerSec = totalOperations / elapsed;
Console.WriteLine($"Operations Per Second:{operationsPerSec:0.0}");
//dispose here to make sure there isn't a connection still attached
files.Dispose();
}
public void ShouldReadSimpleTable()
{
using (var duckDBConnection = new DuckDBConnection())
{
duckDBConnection.Open();
var createTableCommand = duckDBConnection.CreateCommand();
createTableCommand.CommandText = "CREATE TABLE integers(foo INTEGER, bar INTEGER);";
var executeNonQuery = createTableCommand.ExecuteNonQuery();
var insertValuesCommand = duckDBConnection.CreateCommand();
insertValuesCommand.CommandText = "INSERT INTO integers VALUES (3, 4), (5, 6), (7, NULL);";
executeNonQuery = insertValuesCommand.ExecuteNonQuery();
var readValuesCommand = duckDBConnection.CreateCommand();
readValuesCommand.CommandText = "SELECT foo, bar FROM integers;";
var reader = readValuesCommand.ExecuteReader();
reader.HasRows.Should().BeTrue();
var results = new List <Tuple <int?, int?> >();
while (reader.Read())
{
var foo = reader.GetInt32(0);
var bar = reader.GetInt32(1);
results.Add(new Tuple <int?, int?>(foo, bar));
}
results.Count.Should().Be(3);
results[0].Item1.Should().Be(3);
results[0].Item2.Should().Be(4);
results[1].Item1.Should().Be(5);
results[1].Item2.Should().Be(6);
results[2].Item1.Should().Be(7);
results[2].Item2.Should().Be(null);
}
}
public async Task SingleThreadedOpenAndCloseOfSameFile()
{
using var db1 = DisposableFile.GenerateInTemp("db", 1);
var cs = db1.ConnectionString;
await using var duckDBConnection = new DuckDBConnection(cs);
await duckDBConnection.OpenAsync();
var createTable = "CREATE TABLE INSERTIONS(TASK_ID INTEGER, INSERTION_INDEX INTEGER);";
await duckDBConnection.ExecuteAsync(createTable);
await using var dd1 = new DuckDBConnection(cs);
await using var dd2 = new DuckDBConnection(cs);
const int reps = 10;
for (int i = 0; i < reps; i++)
{
Console.WriteLine(i);
await dd1.OpenAsync();
var insertAValue = $"INSERT INTO INSERTIONS VALUES ({1}, {i});";
await dd1.ExecuteAsync(insertAValue);
await dd2.OpenAsync();
insertAValue = $"INSERT INTO INSERTIONS VALUES ({2}, {i});";
await dd2.ExecuteAsync(insertAValue);
await dd1.CloseAsync();
await dd2.CloseAsync();
}
var expectedInsertions = 2 * reps;
var insertions = await duckDBConnection.QuerySingleAsync <int>("SELECT COUNT(*) FROM INSERTIONS;");
insertions.Should().Be(expectedInsertions);
}
public async Task MultiThreadedStress()
{
//with 1 task per file, should be good mix of reusing connections
//and disposing of them
const int fileCount = 20;
const int taskCount = 20;
const int insertionCount = 1000;
const int totalInsertions = taskCount * insertionCount;
var files = new DisposableFileList(fileCount, "db");
foreach (var f in files)
{
await using var duckDBConnection = new DuckDBConnection(f.ConnectionString);
await duckDBConnection.OpenAsync();
var createTable = "CREATE TABLE INSERTIONS(TASK_ID INTEGER, INSERTION_INDEX INTEGER);";
await duckDBConnection.ExecuteAsync(createTable);
}
var insertionsWithRandomDelay = new Func <int, Task>(async ti =>
{
var rnd = new Random(ti);
for (int i = 0; i < insertionCount; i++)
{
//pick a random connection string for each test and jitter delays
await Task.Delay(TimeSpan.FromMilliseconds(rnd.Next(0, 10)));
var cs = files[rnd.Next(files.Count)].ConnectionString;
await using var duckDBConnection = new DuckDBConnection(cs);
await duckDBConnection.OpenAsync();
var insertAValue = $"INSERT INTO INSERTIONS VALUES ({ti}, {i});";
await duckDBConnection.ExecuteAsync(insertAValue);
}
});
var tasks = new Task[taskCount];
//it's hammer time baby!
for (int i = 0; i < taskCount; i++)
{
var index = i;
tasks[i] = Task.Run(async() => await insertionsWithRandomDelay(index));
}
await Task.WhenAll(tasks);
//sanity check of insertions
int insertionCountPostRun = 0;
foreach (var f in files)
{
var cs = f.ConnectionString;
await using var duckDBConnection = new DuckDBConnection(cs);
await duckDBConnection.OpenAsync();
var insertions = await duckDBConnection.QuerySingleAsync <int>("SELECT COUNT(*) FROM INSERTIONS;");
insertions.Should().BeGreaterThan(0);
insertionCountPostRun += insertions;
Console.WriteLine($"{insertions:0} Insertions for {cs}");
}
insertionCountPostRun.Should().Be(totalInsertions, $"Insertions don't add up?");
//dispose here to make sure there isn't a connection still attached
files.Dispose();
}
