public async Task DeleteUserDataAsync(string userId) { using (var connection = new SQLiteConnection(ConnectionString)) { await connection.OpenAsync(); var transaction = await connection.BeginTransactionAsync(); var param = new { Id = userId }; // Delete all notes that belong to the given user string sql = "SELECT NoteId FROM UserNotes WHERE UserId = @Id"; IEnumerable <string> noteIds = await connection.QueryAsync <string>(sql, param); foreach (string id in noteIds) { await DeleteNoteAsync(id, connection); } // Delete all labels that belong to the given user sql = "SELECT LabelId FROM UserLabels WHERE UserId = @Id"; IEnumerable <string> labelIds = await connection.QueryAsync <string>(sql, param); foreach (string id in labelIds) { await DeleteLabelAsync(id, connection); } await transaction.CommitAsync(); } }
/// <summary> /// 插入数据 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="database">数据库文件</param> /// <param name="entities">实体列表</param> public async Task <bool> InsertAsync <T>(string database, IEnumerable <T> entities) where T : class, new() { using (var connection = new SQLiteConnection(GetConnectionString(database))) { await connection.OpenAsync(); var dbTran = await connection.BeginTransactionAsync(); try { foreach (var entity in entities) { await connection.InsertAsync(entity, dbTran); } dbTran.Commit(); return(true); } catch (Exception ex) { dbTran.Rollback(); //Logger.Instance.Common.Error($"[ Dapper ] InsertAsync, Database = {database}, Message = {ex.Message}"); return(false); } } }
public async Task InsertCacheCmd(int count) { const string methodName = nameof(InsertCacheCmd); var file = $"db{count}{methodName}.db"; var connectionString = DbHelper.ConnectionString(file); await ResetDatabase(file); const int batchSize = 10000; var guid = Guid.NewGuid().ToString(); var(cmd, p) = CreateCommand(); var pageCount = count / batchSize; var sw = Stopwatch.StartNew(); for (var i = 0; i < pageCount; i++) { await using var sqLiteConnection = new SQLiteConnection(connectionString); cmd.Connection = sqLiteConnection; await sqLiteConnection.OpenAsync(); await using var beginTransactionAsync = await sqLiteConnection.BeginTransactionAsync(); for (var j = 0; j < batchSize; j++) { p.Value = guid; await cmd.ExecuteNonQueryAsync(); } await beginTransactionAsync.CommitAsync(); } sw.Stop(); Console.WriteLine($"cost: {sw.ElapsedMilliseconds}"); }
public async Task CreateDb() { var fileName = "data.db"; if (File.Exists(fileName)) { return; } var connectionString = GetConnectionString(fileName); await using var sqlConnection = new SQLiteConnection(connectionString); await sqlConnection.OpenAsync(); await using var transaction = await sqlConnection.BeginTransactionAsync(); await sqlConnection.ExecuteAsync(@" CREATE TABLE Product( ProductId int PRIMARY KEY, Color TEXT )", transaction); var dict = CreateDict(); foreach (var(_, p) in dict) { await sqlConnection.ExecuteAsync(@" INSERT INTO Product(ProductId,Color) VALUES(@ProductId,@Color)", p, transaction); } await transaction.CommitAsync(); }
public static async Task <KVTransaction> BeginTransactionAsync(SQLiteConnection connection, SemaphoreSlim trxSemaphore) { try { var transaction = await connection.BeginTransactionAsync(); return(new KVTransaction(transaction, trxSemaphore)); } catch (Exception ex) { Console.WriteLine($"{ex}"); trxSemaphore.Release(); throw; } }
public static async Task <System.Data.IDbTransaction> TryBeginTransaction(DbType type, string db) { DbConnection conn = null; try { switch (type) { case DbType.MySql: conn = new MySqlConnection(db); break; case DbType.SqlServer: conn = new SqlConnection(db); break; case DbType.PostgreSql: conn = new NpgsqlConnection(db); break; case DbType.Sqlite: conn = new SQLiteConnection(db); break; default: throw new NotImplementedException(); } await conn.OpenAsync(); return(await conn.BeginTransactionAsync()); } catch (Exception ex) { #if DEBUG Console.WriteLine(ex.Message); #endif if (conn != null && conn.State == System.Data.ConnectionState.Open) { await conn.CloseAsync(); } conn.Dispose(); return(null); } }
public async Task <string> CreateLabelAsync(string userId, Label label) { label.Id = Guid.NewGuid().ToString(); using (var connection = new SQLiteConnection(ConnectionString)) { connection.Open(); var transaction = await connection.BeginTransactionAsync(); string sql = "INSERT INTO Labels VALUES (@Id, @Name)"; await connection.ExecuteAsync(sql, label, transaction); sql = "INSERT INTO UserLabels VALUES (@UserId, @LabelId)"; var param = new { UserId = userId, LabelID = label.Id }; await connection.ExecuteAsync(sql, param, transaction); await transaction.CommitAsync(); return(label.Id); } }
public async Task <string> CreateNoteAsync(string userId, Note note) { note.Id = Guid.NewGuid().ToString(); using (var connection = new SQLiteConnection(ConnectionString)) { connection.Open(); var transaction = await connection.BeginTransactionAsync(); string sql = @"INSERT INTO Notes VALUES (@Id, @Title, @Content, @Pinned, @ModifiedDate)"; await connection.ExecuteAsync(sql, note, transaction); sql = "INSERT INTO UserNotes VALUES (@UserId, @NoteId)"; var param = new { UserId = userId, NoteId = note.Id }; await connection.ExecuteAsync(sql, param, transaction); await transaction.CommitAsync(); return(note.Id); } }
public async Task <int> DeleteLabelAsync(string id) { if (!await LabelExistsAsync(id)) { return(0); } using (var connection = new SQLiteConnection(ConnectionString)) { await connection.OpenAsync(); var transaction = await connection.BeginTransactionAsync(); int deleteCount = await DeleteLabelAsync(id, connection); await transaction.CommitAsync(); return(deleteCount); } }
private static async Task PopulateDatabase(SQLiteConnection connection) { await using var transaction = await connection.BeginTransactionAsync(); await using var command = connection.CreateCommand(); command.CommandText = @" CREATE TABLE characters (id INTEGER NOT NULL, name NVARCHAR(20) NOT NULL, type NVARCHAR(10) NOT NULL, homePlanet INTEGER NULL, primaryFunction NVARCHAR(20) NULL); CREATE TABLE friends (characterId INTEGER NOT NULL, friendId INTEGER NOT NULL); CREATE TABLE planets (id INTEGER NOT NULL, name NVARCHAR(20) NOT NULL); INSERT INTO planets (id, name) VALUES (1, 'Tatooine'); INSERT INTO characters (id, name, type, homePlanet) VALUES (1, 'Luke', 'Human', 1); INSERT INTO characters (id, name, type, homePlanet) VALUES (2, 'Vader', 'Human', 1); INSERT INTO characters (id, name, type, primaryFunction) VALUES (3, 'R2-D2', 'Droid', 'Astromech'); INSERT INTO characters (id, name, type, primaryFunction) VALUES (4, 'C-3PO', 'Droid', 'Protocol'); INSERT INTO friends (characterId, friendId) VALUES (1, 3); INSERT INTO friends (characterId, friendId) VALUES (1, 4); INSERT INTO friends (characterId, friendId) VALUES (3, 1); INSERT INTO friends (characterId, friendId) VALUES (3, 4); "; await command.ExecuteNonQueryAsync(); await transaction.CommitAsync(); }
public async Task Test() { // arrange var observer = new Observer(); using var subscription = DiagnosticListener.AllListeners.Subscribe(observer); var isolationLevel = IsolationLevel.ReadCommitted; // act await using var connection = new SQLiteConnection(_connectionString).AddDiagnosting(); await connection.OpenAsync(); await using var transaction = await connection.BeginTransactionAsync(isolationLevel); await connection.QueryAsync("SELECT id, name from test", transaction : transaction); await transaction.CommitAsync(); await connection.CloseAsync(); // assert Assert.Equal(8, observer.Events.Count); { var first = observer.Events[0]; Assert.Equal(DbDiagnosticListener.EventNames.ConnectionOpening, first.Key); var before = Assert.IsType <ConnectionPayload>(first.Value); Assert.Equal(connection, before.Connection); var second = observer.Events[1]; Assert.Equal(DbDiagnosticListener.EventNames.ConnectionOpened, second.Key); var after = Assert.IsType <ConnectionPayload>(second.Value); Assert.Equal(connection, after.Connection); Assert.Equal(before.OperationId, after.OperationId); Assert.True(before.Timestamp < after.Timestamp); } { var third = observer.Events[2]; Assert.Equal(DbDiagnosticListener.EventNames.CommandExecuting, third.Key); var before = Assert.IsType <CommandPayload>(third.Value); Assert.Equal(connection.GetGuid(), before.ConnectionId); Assert.Equal(transaction.GetId(), before.TransactionId); var fourth = observer.Events[3]; Assert.Equal(DbDiagnosticListener.EventNames.CommandExecuted, fourth.Key); var after = Assert.IsType <CommandPayload>(fourth.Value); Assert.Equal(connection.GetGuid(), before.ConnectionId); Assert.Equal(transaction.GetId(), before.TransactionId); Assert.Equal(before.Command, after.Command); Assert.Equal(before.OperationId, after.OperationId); Assert.True(before.Timestamp < after.Timestamp); } { var fifth = observer.Events[4]; Assert.Equal(DbDiagnosticListener.EventNames.TransactionCommitting, fifth.Key); var before = Assert.IsType <TransactionPayload>(fifth.Value); Assert.Equal(connection, before.Connection); Assert.Equal(transaction.GetId(), before.TransactionId); Assert.Equal(isolationLevel, before.IsolationLevel); var sixth = observer.Events[5]; Assert.Equal(DbDiagnosticListener.EventNames.TransactionCommitted, sixth.Key); var after = Assert.IsType <TransactionPayload>(sixth.Value); Assert.Equal(connection, after.Connection); Assert.Equal(transaction.GetId(), after.TransactionId); Assert.Equal(isolationLevel, after.IsolationLevel); Assert.Equal(before.OperationId, after.OperationId); Assert.True(before.Timestamp < after.Timestamp); } { var seventh = observer.Events[6]; Assert.Equal(DbDiagnosticListener.EventNames.ConnectionClosing, seventh.Key); var before = Assert.IsType <ConnectionPayload>(seventh.Value); Assert.Equal(connection, before.Connection); var eighth = observer.Events[7]; Assert.Equal(DbDiagnosticListener.EventNames.ConnectionClosed, eighth.Key); var after = Assert.IsType <ConnectionPayload>(eighth.Value); Assert.Equal(connection, after.Connection); Assert.Equal(before.OperationId, after.OperationId); Assert.True(before.Timestamp < after.Timestamp); } }
public async Task InsertChannelClassBatchItemSetResult(int count) { const string methodName = nameof(InsertChannelClassBatchItemSetResult); var file = $"db{count}{methodName}.db"; var connectionString = DbHelper.ConnectionString(file); await ResetDatabase(file); const int batchSize = 10000; var guid = Guid.NewGuid().ToString(); var(cmd, p) = CreateCommand(); await using var sqLiteConnection = new SQLiteConnection(connectionString); await sqLiteConnection.OpenAsync(); cmd.Connection = sqLiteConnection; var pool = ObjectPool.Create <ClassBatchValueItem>(); var arrayPool = ArrayPool <ClassBatchValueItem> .Create(); var resultPool = ObjectPool.Create <ClassBatchValueResultItem>(); var taskChannel = Channel.CreateUnbounded <ClassBatchValueItem>(); var resultChannel = Channel.CreateUnbounded <ClassBatchValueResultItem>(); #pragma warning disable 4014 Task.Factory.StartNew(async() => { while (await resultChannel.Reader.WaitToReadAsync()) { while (resultChannel.Reader.TryRead(out var item)) { try { if (item.IsSuccess) { var item1 = item; Parallel.For(0, item.ItemCount, i => { var taskCompletionSource = item1.Items[i]; taskCompletionSource.Tcs.SetResult(item1.Result); }); } else { var item1 = item; Parallel.For(0, item.ItemCount, i => { var taskCompletionSource = item1.Items[i]; taskCompletionSource.Tcs.SetException(item1.Exception); }); } } catch (Exception e) { Console.WriteLine(e); throw; } finally { resultPool.Return(item); foreach (var task in item.Items) { pool.Return(task); } arrayPool.Return(item.Items); } } } }, TaskCreationOptions.LongRunning).Unwrap(); #pragma warning restore 4014 #pragma warning disable 4014 Task.Factory.StartNew(async() => { var waiting = TimeSpan.FromMilliseconds(50); while (await taskChannel.Reader.WaitToReadAsync()) { var list = new List <ClassBatchValueItem>(); var time = DateTimeOffset.Now; while (list.Count < batchSize && DateTimeOffset.Now - time < waiting && taskChannel.Reader.TryRead(out var item)) { list.Add(item); } if (list.Any()) { await using var transaction = await sqLiteConnection.BeginTransactionAsync(); foreach (var item in list) { p.Value = item.Data; await cmd.ExecuteNonQueryAsync(); } try { await transaction.CommitAsync(); var batchResultItem = resultPool.Get(); batchResultItem.IsSuccess = true; batchResultItem.Result = 0; var classBatchValueItems = arrayPool.Rent(list.Count); list.CopyTo(classBatchValueItems); batchResultItem.Items = classBatchValueItems; batchResultItem.ItemCount = list.Count; var valueTask = resultChannel.Writer.WriteAsync(batchResultItem); if (!valueTask.IsCompleted) { await valueTask; } } catch (Exception e) { var classBatchResultItem = resultPool.Get(); classBatchResultItem.IsSuccess = false; classBatchResultItem.Exception = e; var classBatchValueItems = arrayPool.Rent(list.Count); list.CopyTo(classBatchValueItems); classBatchResultItem.Items = classBatchValueItems; classBatchResultItem.ItemCount = list.Count; var valueTask = resultChannel.Writer.WriteAsync(classBatchResultItem); if (!valueTask.IsCompleted) { await valueTask; } } finally { list.Clear(); } } } }).Unwrap(); #pragma warning restore 4014 var pageCount = count / batchSize; var sw = Stopwatch.StartNew(); for (var i = 0; i < pageCount; i++) { Parallel.For(0, batchSize, async j => { var classBatchItem = pool.Get(); if (classBatchItem.Tcs == null) { classBatchItem.Tcs = new ManualResetValueTaskSource <int>(); } else { classBatchItem.Tcs.Reset(); } classBatchItem.Data = guid; var valueTask = taskChannel.Writer.WriteAsync(classBatchItem); if (!valueTask.IsCompleted) { await valueTask; } var finalValueTask = new ValueTask(classBatchItem.Tcs, classBatchItem.Tcs.Version); if (!finalValueTask.IsCompleted) { await finalValueTask; } }); } taskChannel.Writer.Complete(); await taskChannel.Reader.Completion; sw.Stop(); Console.WriteLine($"cost: {sw.ElapsedMilliseconds}"); }
public async Task InsertChannelClassBatchItemWithPool(int count) { const string methodName = nameof(InsertChannelClassBatchItemWithPool); var file = $"db{count}{methodName}.db"; var connectionString = DbHelper.ConnectionString(file); await ResetDatabase(file); const int batchSize = 10000; var guid = Guid.NewGuid().ToString(); var(cmd, p) = CreateCommand(); await using var sqLiteConnection = new SQLiteConnection(connectionString); await sqLiteConnection.OpenAsync(); cmd.Connection = sqLiteConnection; var bounded = Channel.CreateUnbounded <ClassBatchItem>(); var pool = ObjectPool.Create <ClassBatchItem>(); #pragma warning disable 4014 Task.Factory.StartNew(async() => { var waiting = TimeSpan.FromMilliseconds(50); while (await bounded.Reader.WaitToReadAsync()) { var list = new List <ClassBatchItem>(); var time = DateTimeOffset.Now; while (list.Count < batchSize && DateTimeOffset.Now - time < waiting && bounded.Reader.TryRead(out var item)) { list.Add(item); } if (list.Any()) { await using var transaction = await sqLiteConnection.BeginTransactionAsync(); foreach (var item in list) { p.Value = item.Data; await cmd.ExecuteNonQueryAsync(); } try { await transaction.CommitAsync(); foreach (var item in list) { item.Tcs.SetResult(0); } } catch (Exception e) { foreach (var item in list) { item.Tcs.SetException(e); } } finally { list.Clear(); foreach (var classBatchItem in list) { pool.Return(classBatchItem); } } } } }).Unwrap(); #pragma warning restore 4014 var pageCount = count / batchSize; var sw = Stopwatch.StartNew(); for (var i = 0; i < pageCount; i++) { Parallel.For(0, batchSize, async j => { var tcs = new TaskCompletionSource <int>(); var classBatchItem = pool.Get(); classBatchItem.Tcs = tcs; classBatchItem.Data = guid; var valueTask = bounded.Writer.WriteAsync(classBatchItem); if (!valueTask.IsCompleted) { await valueTask; } await tcs.Task; }); } bounded.Writer.Complete(); await bounded.Reader.Completion; sw.Stop(); Console.WriteLine($"cost: {sw.ElapsedMilliseconds}"); }
public async Task InsertChannelStructBatchItemValueTask(int count) { const string methodName = nameof(InsertChannelStructBatchItemValueTask); var file = $"db{count}{methodName}.db"; var connectionString = DbHelper.ConnectionString(file); await ResetDatabase(file); const int batchSize = 10000; var guid = Guid.NewGuid().ToString(); var(cmd, p) = CreateCommand(); await using var sqLiteConnection = new SQLiteConnection(connectionString); await sqLiteConnection.OpenAsync(); cmd.Connection = sqLiteConnection; var bounded = Channel.CreateUnbounded <StructBatchItemValueTask>(); #pragma warning disable 4014 Task.Factory.StartNew(async() => { var waiting = TimeSpan.FromMilliseconds(50); while (await bounded.Reader.WaitToReadAsync()) { var list = new List <StructBatchItemValueTask>(); var time = DateTimeOffset.Now; while (list.Count < batchSize && DateTimeOffset.Now - time < waiting && bounded.Reader.TryRead(out var item)) { list.Add(item); } if (list.Any()) { await using var transaction = await sqLiteConnection.BeginTransactionAsync(); foreach (var item in list) { p.Value = item.Data; await cmd.ExecuteNonQueryAsync(); } try { await transaction.CommitAsync(); foreach (var item in list.AsParallel()) { item.Tcs.SetResult(0); } } catch (Exception e) { foreach (var item in list.AsParallel()) { item.Tcs.SetException(e); } } finally { list.Clear(); } } } }).Unwrap(); #pragma warning restore 4014 var pageCount = count / batchSize; var sw = Stopwatch.StartNew(); for (var i = 0; i < pageCount; i++) { await Enumerable.Range(0, batchSize) .ToObservable() .Select(j => Observable.FromAsync(async() => { var valueTaskSource = new ManualResetValueTaskSource <int>(); var valueTask = bounded.Writer.WriteAsync(new StructBatchItemValueTask { Tcs = valueTaskSource, Data = guid }); if (!valueTask.IsCompleted) { await valueTask; } var finalValueTask = new ValueTask(valueTaskSource, valueTaskSource.Version); if (!finalValueTask.IsCompleted) { await finalValueTask; } })) .Merge() .ToTask(); } bounded.Writer.Complete(); await bounded.Reader.Completion; sw.Stop(); Console.WriteLine($"cost: {sw.ElapsedMilliseconds} ms"); }
public static async Task <KVTransaction> BeginTransactionAsync(SQLiteConnection connection) { var transaction = await connection.BeginTransactionAsync(); return(new KVTransaction(transaction)); }