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(); }