{"id":225,"date":"2017-02-12T08:00:00","date_gmt":"2017-02-12T08:00:00","guid":{"rendered":"https:\/\/pagan-gerbil.net\/?p=225"},"modified":"2017-02-12T08:00:00","modified_gmt":"2017-02-12T08:00:00","slug":"sqlite-in-asp-net-core-without-entity-framework","status":"publish","type":"post","link":"https:\/\/www.pagan-gerbil.net\/?p=225","title":{"rendered":"SQLite in ASP.NET Core WITHOUT Entity Framework"},"content":{"rendered":"

Try and search for ASP.NET Core and SQLite<\/a> and you\u2019ll get a dozen guides to using it with Entity Framework.<\/p>\n

First of all, we\u2019ll need an ASP.NET Core website and install the Nuget packages Microsoft.Data.Sqlite<\/font> and System.Data.Sqlite<\/font>.<\/p>\n

I prefer to use Dapper for my database access (hence not wanting to use Entity Framework), so go ahead and install Dapper<\/font> too.<\/p>\n

Opening any connection in SQLite by default automatically creates the database file. Run this code:<\/p>\n

var<\/span> connectionStringBuilder = new<\/span> SqliteConnectionStringBuilder();\nconnectionStringBuilder.DataSource = \"MyNewDatabase.db\"<\/span>;\nusing<\/span> (var<\/span> connection = new<\/span> SqliteConnection(connectionStringBuilder.ConnectionString))\n{     connection.Open();     var<\/span> result = connection.Query<int<\/span>>(\"SELECT @number;\"<\/span>, new<\/span> { number = 789 });\n}<\/pre>\n
And you\u2019ll see in the \/bin\/debug folder there is a new (empty) file called MyNewDatabase.db<\/font>. 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.<\/p>\n
Running basic queries for fun isn\u2019t 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\u2019ve been playing with Linux, I loaded up Bash for Windows 10 and gave it a go.<\/p>\n
To do it like that, run sudo apt-get install sqlite3<\/font> 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<\/font>, where c is your drive letter). Then hit sqlite3 MyNewDatabase.db<\/font>, and you\u2019re into command line SQL mode! All commands must be terminated with a semi-colon, but otherwise it\u2019s 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.<\/p>\n
A few hints from this site<\/a> on using SQLite:<\/p>\n
To list all tables, type .tables<\/font> and to exit the command line type .quit<\/font>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Try and search for ASP.NET Core and SQLite and you\u2019ll get a dozen guides to using it with Entity Framework. First of all, we\u2019ll 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 … <\/p>\n
Continue reading “SQLite in ASP.NET Core WITHOUT Entity Framework”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[43,189,190,191],"series":[],"_links":{"self":[{"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=\/wp\/v2\/posts\/225"}],"collection":[{"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=225"}],"version-history":[{"count":0,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=\/wp\/v2\/posts\/225\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=225"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/www.pagan-gerbil.net\/index.php?rest_route=%2Fwp%2Fv2%2Fseries&post=225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}