Try and search for ASP.NET Core and SQLite and you’ll get a dozen guides to using it with Entity Framework.
First of all, we’ll need an ASP.NET Core website and install the Nuget packages Microsoft.Data.Sqlite and System.Data.Sqlite.
I prefer to use Dapper for my database access (hence not wanting to use Entity Framework), so go ahead and install Dapper too.
Opening any connection in SQLite by default automatically creates the database file. Run this code:
var connectionStringBuilder = new SqliteConnectionStringBuilder(); connectionStringBuilder.DataSource = "MyNewDatabase.db"; using (var connection = new SqliteConnection(connectionStringBuilder.ConnectionString)) { connection.Open(); var result = connection.Query<int>("SELECT @number;", new { number = 789 }); }
And you’ll see in the /bin/debug folder there is a new (empty) file called MyNewDatabase.db. Setting a breakpoint and inspecting the result variable will also reveal that the database is responding nicely to queries. It should have a list of a single item, and that item is the number 789.
Running basic queries for fun isn’t very exciting though. We need to be able to connect to the database, create tables and interrogate them. The way to do this with SQLite is through the command line. Since I’ve been playing with Linux, I loaded up Bash for Windows 10 and gave it a go.
To do it like that, run sudo apt-get install sqlite3 in your Bash shell to get the client application. Then navigate to the database file location (you can get to the main drives with cd /mnt/c, where c is your drive letter). Then hit sqlite3 MyNewDatabase.db, and you’re into command line SQL mode! All commands must be terminated with a semi-colon, but otherwise it’s easy to add tables, set them up, etc. I will be saving all the commands in a separate file so the scripts can be re-run if necessary, or if I choose to create a new database on the live environment and deploy to it separately.
A few hints from this site on using SQLite:
To list all tables, type .tables and to exit the command line type .quit.