In this third post on this project we’ll look at the back-end, meaning the database. The database technology is SQLite. The name of the database in this project is quotes.db. The name of the table is ThoughtsTable. The Id is the primary key and is therefore always unique. It is also auto incremented. This means for each row (record) added, the number goes up by one. The date is not stored as a date because SQLite does not have a date class for storing dates. We have to choose another type. I’ve chosen REAL for this project. The last two columns are text. On the front-end interface, the user never sees the value of Id because they don’t need to. The user never sees the real number stored in the Date column. This is because that real number is converted over to another number and then we use C# to convert that number over to a DateTime type and then display that as a “long date” format. For an article on SQLite date and times, have a look at this article called SQLite Date & Time.

CREATE TABLE "ThoughtsTable" (
	"Id"	INTEGER NOT NULL UNIQUE,
	"Date"	REAL NOT NULL,
	"Thought"	TEXT NOT NULL,
	"Comments"	TEXT NOT NULL,
	PRIMARY KEY("Id" AUTOINCREMENT)
)

Once the database has been created, along with at least one table, we are ready to populate it. One way to do this is through SQL INSERT commands similar to the following.

INSERT INTO ThoughtsTable (date, Thought, Comments) VALUES(2459174,"Spread love everywhere you go. Let no one ever come to you without leaving happier." -Mother Teresa","This quote of Mother Teresa is...");

C# Model

Each time we get a row/record from the database we’ll need to save it in memory somewhere. We can use an object called DailyThought. In C#, here below is the code. Notice that the data types in C# are long, double, string, and string. In SQLite the data types are integer, real, text, and text. The Visual Studio IDE will give you an error if the types are not compatible. In your C# program, you will need to adjust the C# type to fit the database type.

    class DailyThought
    {
        public long Id { get; set; }
        public Double Date { get; set; }
        public string Thought { get; set; }
        public string Comments { get; set; }
    }