CollectDiscordData.cs

        [Command("Stats")]
        [RequireOwner]
        public async Task StatsCMD(CommandContext ctx, [Description("How many Messages may be Recorded per Channel, or '*' for limitless")]string maxMessages)
        {
            var useMax = maxMessages == "*";
            int maxBacklog;
            if (!useMax)
                maxBacklog = Int32.Parse(maxMessages);
            else
                maxBacklog = Int32.MaxValue;

            await ctx.RespondAsync($"Ok, lets try.... {maxBacklog} is the max amount of Messages / Channel");
            await DoStats(ctx, maxBacklog, useMax);
        }

        async Task DoStats(CommandContext ctx, int maxBacklog, bool useMax)
        {
            if (File.Exists("data.db"))
                File.Delete("data.db");
            using (var connection = new SqliteConnection("Data Source=data.db;"))
            {
                connection.Open();

                Action<string> SQLAction = (sql) =>
                {
                    var command = new SqliteCommand(sql, connection);
                    command.ExecuteNonQuery();
                };

                SQLAction(@"CREATE TABLE messages (
id INTEGER PRIMARY KEY,
channelName TEXT NOT NULL,
ownerId INTEGER NOT NULL,
ownerName TEXT NOT NULL,
messageId INTEGER NOT NULL,
messageLength INTEGER NOT NULL,
creationTime TEXT NOT NULL
);
");
                var totalMessages = new List<DiscordMessage>();
                foreach (var channel in ctx.Guild.Channels)
                {
                    try
                    {
                        if (channel.Type != ChannelType.Text)
                            continue;

                        var msgs = new List<DiscordMessage>();
                        var c = ((await channel.GetMessagesAsync()).FirstOrDefault()?.Id);
                        if (c == null)
                        {
                            Console.WriteLine($"{channel.Name} is empty");
                            continue;
                        }
                        ulong LastMessage = c.Value;
                        do
                        {
                            var v = await channel.GetMessagesBeforeAsync(LastMessage);
                            if (v.Count == 0)
                                break;
                            LastMessage = v.Last().Id;
                            msgs.AddRange(v);

                            if (!useMax && msgs.Count >= maxBacklog)
                                break;

                            using (SqliteTransaction oTransaction = connection.BeginTransaction())
                            {
                                using (SqliteCommand oCommand = connection.CreateCommand())
                                {
                                    oCommand.Transaction = oTransaction;
                                    oCommand.CommandType = CommandType.Text;
                                    oCommand.CommandText = "INSERT INTO messages (channelName, ownerId, ownerName, messageId, messageLength, creationTime) VALUES (@channelName, @ownerId, @ownerName, @messageId, @messageLength, @creationTime);";
                                    oCommand.Parameters.Add(new SqliteParameter("@channelName", SqliteType.Text));
                                    oCommand.Parameters.Add(new SqliteParameter("@ownerId", SqliteType.Integer));
                                    oCommand.Parameters.Add(new SqliteParameter("@ownerName", SqliteType.Text));
                                    oCommand.Parameters.Add(new SqliteParameter("@messageId", SqliteType.Integer));
                                    oCommand.Parameters.Add(new SqliteParameter("@messageLength", SqliteType.Integer));
                                    oCommand.Parameters.Add(new SqliteParameter("@creationTime", SqliteType.Text));
                                    try
                                    {
                                        foreach (var message in v)
                                        {
                                            oCommand.Parameters[0].Value = channel.Name;
                                            oCommand.Parameters[1].Value = message.Author.Id;
                                            oCommand.Parameters[2].Value = message.Author.Username;
                                            oCommand.Parameters[3].Value = message.Id;
                                            oCommand.Parameters[4].Value = message.Content.Length;
                                            oCommand.Parameters[5].Value = message.Timestamp.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss");
                                            if (oCommand.ExecuteNonQuery() != 1)
                                            {
                                                //'handled as needed, 
                                                //' but this snippet will throw an exception to force a rollback
                                                throw new InvalidProgramException();
                                            }
                                        }
                                        oTransaction.Commit();
                                    }
                                    catch (Exception)
                                    {
                                        oTransaction.Rollback();
                                        throw;
                                    }
                                }
                            }
                        } while (true);
                        totalMessages.AddRange(msgs);
                        Console.WriteLine($"Finished {channel.Name}");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Caught Error:");
                        Console.WriteLine(ex);
                        Console.WriteLine($"Channel {channel.Name} Finished with an Error");
                    }
                }

                SQLAction(@"
CREATE TABLE words (
word TEXT PRIMARY KEY,
amount INTEGER NOT NULL
);");

                ConcurrentDictionary<string, int> words = new ConcurrentDictionary<string, int>();

                Parallel.ForEach(totalMessages.ToArray(), msg =>
                {
                    Parallel.ForEach(Regex.Replace(msg.Content, @"\W", " ").Split(' '), part =>
                    {
                        words.AddOrUpdate(part, 1, (part2, val) => val + 1);
                    });
                });

                using (SqliteTransaction oTransaction = connection.BeginTransaction())
                {
                    using (SqliteCommand oCommand = connection.CreateCommand())
                    {
                        oCommand.Transaction = oTransaction;
                        oCommand.CommandType = CommandType.Text;
                        oCommand.CommandText = "INSERT INTO words (word, amount) VALUES (@key, @value);";
                        oCommand.Parameters.Add(new SqliteParameter("@key", SqliteType.Text));
                        oCommand.Parameters.Add(new SqliteParameter("@value", SqliteType.Integer));
                        try
                        {
                            foreach (var word in words.ToArray())
                            {
                                oCommand.Parameters[0].Value = word.Key;
                                oCommand.Parameters[1].Value = word.Value;
                                if (oCommand.ExecuteNonQuery() != 1)
                                {
                                    //'handled as needed, 
                                    //' but this snippet will throw an exception to force a rollback
                                    throw new InvalidProgramException();
                                }
                            }
                            oTransaction.Commit();
                        }
                        catch (Exception)
                        {
                            oTransaction.Rollback();
                            throw;
                        }
                    }
                }

                connection.Close();
            }
            Console.WriteLine("Done");
            await ctx.RespondAsync("Done");
        }