Welcome

A database can be used to store and retrieve data that can make your bot even more robust. In this post, I will be going over how to create a database, and use it to expand our 8Ball command.

Prerequisites

  • .NET Core 3.x

  • A fundamental understanding of the Discord.Net library, or following along step-by-step with (if you’re working on a local bot, just omit the Raspberry Pi steps!):

Featured
C# Discord Bot on Raspberry Pi: Simple Bot With Config File
C# Discord Bot on Raspberry Pi: Simple Bot With Config File

Setting things up

This post will be building off of the code found in the logging post, found here: https://github.com/gngrninja/csharpi/tree/04-efdb

If you‘d like to go through the logging post first, check that out as well, here:

Featured
C# Discord Bot: Logging All The Things
C# Discord Bot: Logging All The Things

Let’s add the package we’ll need to get started using EF Core w/sqlite.

Drop down to a console, navigate to your project folder, and run:

dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet restore
add_package.PNG

Creating our database model

Now we’ll want to create a model for Entity Framework Core to use when generating the database. The model will be converted into a table.

To do this, we’ll:

  • Create a folder in our project root named Database

  • In the Database folder, create two files:

    • CsharpiContext.cs

    • EightBallAnswer.cs

db_folder.PNG

Let’s start defining our EightBallAnswer, by adding the following to the EightBallAnswer.cs file:

https://github.com/gngrninja/csharpi/blob/04-efdb/Database/EightBallAnswer.cs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
using System;
using System.ComponentModel.DataAnnotations;

namespace csharpi.Database
{
    public partial class EightBallAnswer
    {
        [Key]
        public long AnswerId { get; set; }
        public string AnswerText { get; set; }
        public string AnswerColor { get; set; }
    }
}

Now we can create our DbSet in CsharpEntities.cs, which will tell EF Core what we want our database to look like:

https://github.com/gngrninja/csharpi/blob/04-efdb/Database/CsharpiEntities.cs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

namespace csharpi.Database
{
    public partial class CsharpiEntities : DbContext
    {
        public virtual DbSet<EightBallAnswer> EightBallAnswer { get; set; }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "csharpi.db" };
            var connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);
            optionsBuilder.UseSqlite(connection);
        }        
    }
}

Now let’s add some pieces to our Program.cs file:

At the top:

using csharpi.Database;

And to inject the DB Context via Dependency Injection:

.AddDbContext<CsharpiEntities>() 

That will make Program.cs look like this:

https://github.com/gngrninja/csharpi/blob/intro/Program.cs

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
using System;
using Discord;
using Discord.Net;
using Discord.Commands;
using Discord.WebSocket;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.DependencyInjection;
using csharpi.Services;
using System.Linq;
using Serilog;
using csharpi.Database;

namespace csharpi
{
    class Program
    {
        // setup our fields we assign later
        private readonly IConfiguration _config;
        private DiscordSocketClient _client;
        private static string _logLevel;

        static void Main(string[] args = null)
        {
            if (args.Count() != 0)
            {
                _logLevel = args[0];
            } 
            Log.Logger = new LoggerConfiguration()
                .WriteTo.File("logs/csharpi.log", rollingInterval: RollingInterval.Day)
                .WriteTo.Console()
                .CreateLogger();

            new Program().MainAsync().GetAwaiter().GetResult();
        }

        public Program()
        {
            // create the configuration
            var _builder = new ConfigurationBuilder()
                .SetBasePath(AppContext.BaseDirectory)
                .AddJsonFile(path: "config.json");  

            // build the configuration and assign to _config          
            _config = _builder.Build();
        }

        public async Task MainAsync()
        {
            // call ConfigureServices to create the ServiceCollection/Provider for passing around the services
            using (var services = ConfigureServices())
            {
                // get the client and assign to client 
                // you get the services via GetRequiredService<T>
                var client = services.GetRequiredService<DiscordSocketClient>();
                _client = client;

                // setup logging and the ready event
                services.GetRequiredService<LoggingService>();

                // this is where we get the Token value from the configuration file, and start the bot
                await client.LoginAsync(TokenType.Bot, _config["Token"]);
                await client.StartAsync();

                // we get the CommandHandler class here and call the InitializeAsync method to start things up for the CommandHandler service
                await services.GetRequiredService<CommandHandler>().InitializeAsync();

                await Task.Delay(-1);
            }
        }

        private Task LogAsync(LogMessage log)
        {
            Console.WriteLine(log.ToString());
            return Task.CompletedTask;
        }

        private Task ReadyAsync()
        {
            Console.WriteLine($"Connected as -> [{_client.CurrentUser}] :)");
            return Task.CompletedTask;
        }

        // this method handles the ServiceCollection creation/configuration, and builds out the service provider we can call on later
        private ServiceProvider ConfigureServices()
        {
            // this returns a ServiceProvider that is used later to call for those services
            // we can add types we have access to here, hence adding the new using statement:
            // using csharpi.Services;
            // the config we build is also added, which comes in handy for setting the command prefix!
            var services = new ServiceCollection()
                .AddSingleton(_config)
                .AddSingleton<DiscordSocketClient>()
                .AddSingleton<CommandService>()
                .AddSingleton<CommandHandler>()
                .AddSingleton<LoggingService>()
                .AddDbContext<CsharpiEntities>() 
                .AddLogging(configure => configure.AddSerilog());

            if (!string.IsNullOrEmpty(_logLevel)) 
            {
                switch (_logLevel.ToLower())
                {
                    case "info":
                    {
                        services.Configure<LoggerFilterOptions>(options => options.MinLevel = LogLevel.Information);
                        break;
                    }
                    case "error":
                    {
                        services.Configure<LoggerFilterOptions>(options => options.MinLevel = LogLevel.Error);
                        break;
                    } 
                    case "debug":
                    {
                        services.Configure<LoggerFilterOptions>(options => options.MinLevel = LogLevel.Debug);
                        break;
                    } 
                    default: 
                    {
                        services.Configure<LoggerFilterOptions>(options => options.MinLevel = LogLevel.Error);
                        break;
                    }
                }
            }
            else
            {
                services.Configure<LoggerFilterOptions>(options => options.MinLevel = LogLevel.Information);
            }

            var serviceProvider = services.BuildServiceProvider();
            return serviceProvider;
        }
        
    }
}

Create the Database File

Now that we’ve defined what we want our database to look like, we can run some commands to get it created. Run these commands while in the root of your project folder:

  1. Install tooling for EF in .NET Core

    dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design

  2. Create the database using our model

    dotnet ef migrations add InitialCreate
dotnet ef database update

If all went well, you should see a new file named csharpi.db:

new_file.PNG

Taking a peek at the DB

To take a look and verify the database was created as per our defined model, you can use this tool: https://sqlitebrowser.org/

Here is what the file I created looks like:

Add Eight Ball Commands

Now we will want to add some commands to the bot that will allow us to:

  • Add an answer w/associated color to the database

  • List out answers currently in the database

  • Remove an answer from the database

  • Ask the 8Ball a question

The first thing we will want to do is remove the 8Ball command from the Modules/ExampleCommands.cs file. After removing it, ExampleCommands.cs should look like this:

https://github.com/gngrninja/csharpi/blob/04-efdb/Modules/ExampleCommands.cs

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
using Discord;
using Discord.Net;
using Discord.WebSocket;
using Discord.Commands;
using System;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Configuration;

namespace csharpi.Modules
{
    // for commands to be available, and have the Context passed to them, we must inherit ModuleBase
    public class ExampleCommands : ModuleBase
    {
        [Command("hello")]
        public async Task HelloCommand()
        {
            // initialize empty string builder for reply
            var sb = new StringBuilder();

            // get user info from the Context
            var user = Context.User;
            
            // build out the reply
            sb.AppendLine($"You are -> [{user.Username}]");
            sb.AppendLine("I must now say, World!");

            // send simple string reply
            await ReplyAsync(sb.ToString());
        }        
    }
}

The next thing we will do is add all of our eight ball handling commands to a new file in the Modules folder named EightBallCommands.cs

commands_file.PNG

The contents for EightBallCommands.cs should be as follows:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
using Discord;
using Discord.Net;
using Discord.WebSocket;
using Discord.Commands;
using System;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;
using Microsoft.Extensions.Configuration;
using csharpi.Database;
using Microsoft.Extensions.DependencyInjection;

namespace csharpi.Modules
{
    // for commands to be available, and have the Context passed to them, we must inherit ModuleBase
    public class EightBallCommands : ModuleBase
    {
        private readonly CsharpiEntities _db;
        private List<String> _validColors = new List<String>();
        private readonly IConfiguration _config;

        public EightBallCommands(IServiceProvider services)
        {
            // we can pass in the db context via depedency injection
            _db = services.GetRequiredService<CsharpiEntities>();
            _config = services.GetRequiredService<IConfiguration>();

            _validColors.Add("green");
            _validColors.Add("red");
            _validColors.Add("blue");
        }

        [Command("add")]
        public async Task AddResponse(string answer, string color)
        {            
            var sb = new StringBuilder();
            var embed = new EmbedBuilder();

            // get user info from the Context
            var user = Context.User;
            
            // check to see if the color is valid
            if (!_validColors.Contains(color.ToLower()))
            {
                sb.AppendLine($"**Sorry, [{user.Username}], you must specify a valid color.**");
                sb.AppendLine("Valid colors are:");
                sb.AppendLine();
                foreach (var validColor in _validColors)
                {
                    sb.AppendLine($"{validColor}");
                }       
                embed.Color = new Color(255, 0, 0);         
            }
            else 
            {
                // add answer/color to table
                await _db.AddAsync(new EightBallAnswer
                    {
                        AnswerText  = answer,
                        AnswerColor = color.ToLower()                     
                    }
                );
                // save changes to database
                await _db.SaveChangesAsync();                
                sb.AppendLine();
                sb.AppendLine("**Added answer:**");
                sb.AppendLine(answer);
                sb.AppendLine();
                sb.AppendLine("**With color:**");
                sb.AppendLine(color);
                embed.Color = new Color(0, 255, 0);  
            }

            // set embed
            embed.Title = "Eight Ball Answer Addition";
            embed.Description = sb.ToString();
            
            // send embed reply
            await ReplyAsync(null, false, embed.Build());
        }

        [Command("list")]
        public async Task ListAnswers()
        {            
            var sb = new StringBuilder();
            var embed = new EmbedBuilder();

            // get user info from the Context
            var user = Context.User;
            
            var answers = await _db.EightBallAnswer.ToListAsync();
            if (answers.Count > 0)
            {
                foreach (var answer in answers)
                {
                    sb.AppendLine($":small_blue_diamond: [{answer.AnswerId}] **{answer.AnswerText}**");
                }
            }
            else
            {
                sb.AppendLine("No answers found!");
            }

            // set embed
            embed.Title = "Eight Ball Answer List";
            embed.Description = sb.ToString();
            
            // send embed reply
            await ReplyAsync(null, false, embed.Build());
        }   

        [Command("remove")]
        public async Task RemoveAnswer(int id)
        {            
            var sb = new StringBuilder();
            var embed = new EmbedBuilder();

            // get user info from the Context
            var user = Context.User;
            
            var answers = await _db.EightBallAnswer.ToListAsync();
            var answerToRemove = answers.Where(a => a.AnswerId == id).FirstOrDefault();

            if (answerToRemove != null)
            {
                _db.Remove(answerToRemove);
                await _db.SaveChangesAsync();
                sb.AppendLine($"Removed answer -> [{answerToRemove.AnswerText}]");
            }
            else
            {
                sb.AppendLine($"Did not find answer with id [**{id}**] in the database");
                sb.AppendLine($"Perhaps use the {_config["prefix"]}list command to list out answers");
            }
            
            // set embed
            embed.Title = "Eight Ball Answer List";
            embed.Description = sb.ToString();
            
            // send embed reply
            await ReplyAsync(null, false, embed.Build());
        } 

        [Command("8ball")]
        [Alias("ask")]
        public async Task AskEightBall([Remainder]string args = null)
        {
            // I like using StringBuilder to build out the reply
            var sb = new StringBuilder();

            // let's use an embed for this one!
            var embed = new EmbedBuilder();
            
            // add our possible replies from the database
            var replies = await _db.EightBallAnswer.ToListAsync();

            // add a title                        
            embed.Title = "Welcome to the 8-ball!";
            
            // we can get lots of information from the Context that is passed into the commands
            // here I'm setting up the preface with the user's name and a comma
            sb.AppendLine($"{Context.User.Username},");
            sb.AppendLine();

            // let's make sure the supplied question isn't null 
            if (args == null)
            {
                // if no question is asked (args are null), reply with the below text
                sb.AppendLine("Sorry, can't answer a question you didn't ask!");
            }
            else 
            {
                // if we have a question, let's give an answer!
                // get a random number to index our list with 
                var answer = replies[new Random().Next(replies.Count)];
                
                // build out our reply with the handy StringBuilder
                sb.AppendLine($"You asked: [**{args}**]...");
                sb.AppendLine();
                sb.AppendLine($"...your answer is [**{answer.AnswerText}**]");

                switch (answer.AnswerColor)
                {
                    case "red":
                    {
                        embed.WithColor(255, 0, 0);
                        break;
                    }
                    case "blue":
                    {
                        embed.WithColor(0, 0, 255);
                        break;
                    }
                    case "green":
                    {
                        embed.WithColor(0, 255, 0);
                        break;
                    }                                        
                }                               
            }

            // now we can assign the description of the embed to the contents of the StringBuilder we created
            embed.Description = sb.ToString();

            // this will reply with the embed
            await ReplyAsync(null, false, embed.Build());
        }
    }
}

Testing it out

Let’s test it out!

Adding answers

+add "yes!" "green"
+add "that's a no, bob!" "red"
+add "I dono" "blue"
add_response.PNG
db_answers.PNG

Listing answers

+list
list_answers.PNG

Removing answers

+remove 3
remove_response.PNG
db_removed.PNG

Asking a question

+8ball is the sky blue?
asked.PNG

I asked it a couple times so I could see the different responses and color changes.

Conclusion

This is a very basic example of what you can do with a simple database, created via code, for your Discord Bot to use. If you have any questions or feedback, please leave a comment below!