In this post we continue with the building of our project that we started in the previous post. The previous post showed how to install SQLite and Dapper. We continue to cover all of the details.

If you haven’t already done so, please install SQLite and Dapper. Alternatively you could do the next two steps and then install these.

Now we can start setting up our connections to our database. We need a connection string. The time in the video by Tim Corey is 15:30. You can have a look at the website connectionstrings.com. Note that we have named our connection string “Default”. We can name it whatever we want. Note that we are using a relative location with the dot slash just before DemoDb.db.

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="Default" connectionString="Data Source=.\DemoDb.db;Version=3;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
</configuration>

Add a Reference to ConfigurationManager

The Configuration Manager is part of C#, but it is not added to our project by default. We need to add it. How do we do that? Right-click the References folder in the Solution Explorer of Visual Studio. Add a Reference. Search for Configuration Manager by typing “config” into the search box in the upper left corner. When you find SystemConfiguration, check the checkbox to the left of it and click the OK button.

Installation

We can postpone our installation of SQLite and Dapper into our project up to this point. If you’ve already installed those then that’s great. If not, install those now.

Class: SQLiteDataAccess

Let’s add a new class to our project and call it SQLiteDataAccess. You can call it something else if you wish. This class will have some methods that perform two of the CRUD (create, read, update, and delete) operations: Read and Create (Add).

using System.Collections.Generic;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.SQLite;
using Dapper;
namespace PeopleListAddSqlite
{
    public class SqliteDataAccess
    {
        // 22:35 to 31:50 in video by IAmTimCorey
        public static List<PersonModel> LoadPeople()
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {   // using Dapper
                var output = conn.Query<PersonModel>("select * from Person order by Id desc", new DynamicParameters());
                return output.ToList();
            }
        }
        public static void SavePerson(PersonModel person)
        {
            using (IDbConnection conn = new SQLiteConnection(LoadConnectionString()))
            {
                conn.Execute("insert into Person (FirstName, LastName) values (@FirstName, @LastName)", person);
            }
        }
        private static string LoadConnectionString(string id = "Default")
        {
            // go to App.config and return the string called "Default"
            // need to add Reference: ConfigurationManager and using System.Configuration
            return ConfigurationManager.ConnectionStrings[id].ConnectionString;
        }
    }
}