public async Task InsertPosts(IEnumerable <Post> posts) { using (var rentedConnection = await ConnectionPool.RentConnectionAsync()) using (var clonedCommand = (MySqlCommand)InsertQuery.Clone()) using (var transaction = await rentedConnection.Object.BeginTransactionAsync()) using (var clonedSet = postDataTable.Clone()) using (var adapter = new MySqlDataAdapter($"SELECT * FROM {Board} WHERE 1 = 0", rentedConnection.Object)) { clonedCommand.Connection = rentedConnection; clonedCommand.Transaction = transaction; clonedCommand.UpdatedRowSource = UpdateRowSource.None; foreach (var post in posts) { var row = clonedSet.NewRow(); row["num"] = post.PostNumber; row["thread_num"] = post.ReplyPostNumber != 0 ? post.ReplyPostNumber : post.PostNumber; row["op"] = post.ReplyPostNumber == 0 ? 1 : 0; row["timestamp"] = post.UnixTimestamp; row["timestamp_expired"] = 0; row["preview_orig"] = post.TimestampedFilename.HasValue ? $"{post.TimestampedFilename}s.jpg" : null; row["preview_w"] = post.ThumbnailWidth ?? 0; row["preview_h"] = post.ThumbnailHeight ?? 0; row["media_filename"] = post.OriginalFilenameFull; row["media_w"] = post.ImageWidth ?? 0; row["media_h"] = post.ImageHeight ?? 0; row["media_size"] = post.FileSize ?? 0; row["media_hash"] = post.FileMd5; row["media_orig"] = post.TimestampedFilenameFull; row["spoiler"] = post.SpoilerImage == true ? 1 : 0; row["deleted"] = 0; row["capcode"] = post.Capcode?.Substring(0, 1).ToUpperInvariant() ?? "N"; row["email"] = null; // 4chan api doesn't supply this???? row["name"] = HttpUtility.HtmlDecode(post.Name); row["trip"] = post.Trip; row["title"] = HttpUtility.HtmlDecode(post.Subject); row["comment"] = post.Comment; row["sticky"] = post.Sticky == true ? 1 : 0; row["locked"] = post.Closed == true ? 1 : 0; row["poster_hash"] = post.PosterID == "Developer" ? "Dev" : post.PosterID; row["poster_country"] = post.CountryCode; clonedSet.Rows.Add(row); } adapter.InsertCommand = clonedCommand; adapter.UpdateBatchSize = 100; await adapter.UpdateAsync(clonedSet); transaction.Commit(); } }
/* Connect to the database specified in the connection string, build non- * select queries from the provided query, update the database, and return * the number of affected rows. Return null if any steps fail. */ private async Task <int?> SaveTableChangesAsync(string connect, string select, DataTable table) { if (connect == null) { return(null); } try { using (var connection = new MySqlConnection(connect)) using (var adapter = new MySqlDataAdapter(select, connection)) using (var builder = new MySqlCommandBuilder(adapter)) { /* This feels like a cludge, but it is unfortunately necessary. Update * commands called when the data adapter affects record insertions do * NOT return the newly created ID and do not update the added row * in the data table as a result. This results in added records that * cannot be deleted. Also, once a failed deletion occurs, accept changes * called after any other operation that works will still fail becuase the * deletion is queued to occur. This handler checks for an insertion after * a row update occurs, and if an insertion occured, it does what the * data adapter should be doing in the first place. */ MySqlRowUpdatedEventHandler handler = null; handler = (object sender, MySqlRowUpdatedEventArgs args) => { if (args.Command.CommandText.Contains("INSERT")) { table.Rows[table.Rows.Count - 1]["id"] = args.Command.LastInsertedId; } adapter.RowUpdated -= handler; }; adapter.RowUpdated += handler; return(await adapter.UpdateAsync(table)); } } catch // ( Exception e ) { // MessageBox.Show( e.Message ); return(null); } }