Setting up the database

You are going to need a database. Relativity Server, the middle tier of Data Abstract for .NET, can use almost any database that has an ADO.NET driver (which is almost every popular SQL-based database).

To make things as simple as possible for this application, you will use SQLite. SQLite is famous for its great zero-configuration feature, which means no complex setup or administration is needed.

Installing SQLite on Windows

SQLite is not installed on Windows by default, so unless you have used it on your machine before, you are going to need to install it to follow this tutorial. It's a very simple process and will only take a few minutes.

Go to the SQLite download page and download the pre-compiled binaries from the Windows section. You will need to download the sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zip files.

Create a C:\>sqlite folder and unzip the two files into it. This should give you the files sqlite3.def, sqlite3.dll and sqlite3.exe. Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue the sqlite3 command, which should display a result similar to this:.

C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Pre-Configured Database

You can download a copy of the database with all the initial data needed here.

The Database Schema

The database consists of just three tables.

Users Table

CREATE TABLE "Users" (
  "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "Name" text(20,0) NOT NULL,
  "Password" text(20,0) NOT NULL);

Priorities Table

CREATE TABLE "Priorities" (
  "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "Name" text(20,0) NOT NULL);

Tasks Table

CREATE TABLE "Tasks" (
  "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "Done" integer NOT NULL DEFAULT 0,
  "DueDate" DATETIME,
  "Priority" integer NOT NULL DEFAULT 0,
  "Task" text(50,0),
  "Details" text(255,0),
  "User" integer NOT NULL);

Initial Data

The database will need some initial seed data.

Two users

INSERT INTO "Users" VALUES (1, 'Alex', '111');
INSERT INTO "Users" VALUES (2, 'Daniel', '222');

Three priorities#\

INSERT INTO "Priorities" VALUES (1, 'Low');
INSERT INTO "Priorities" VALUES (2, 'Normal');
INSERT INTO "Priorities" VALUES (3, 'High');

One Task

INSERT INTO "Tasks" VALUES (1, 0, '2014-08-01 00:00:00.0000', 1,
  'Learn DA', 'Learn Data Abstract', 2);