Introduction

This is a very simple app that will teach you how to perform CRUD operations against a real database. These operations are the base of web-development and you’ll be using them throughout your career in any most applications. We think it’s very important to do it from the start of your journey, since everything that will happen from here is just adding complexity to CRUD operations. No matter how complex and fancy the app you’re building is, in the end it all comes down to executing CRUD calls to a database.

For that you’ll have to learn very simple SQL commands. I know it sounds scary, but you’ll be amazed about how little SQL knowledge you need to build a full-stack app. Don’t worry, we will take you by the hand and by the end you’ll have completed your first fully functioning CRUD app. The most common ways of calling a SQL database with C# are through ADO.NET, Dapper and Entity Framework. We will start by using ADO.NET, because it’s the closest to raw SQL.

If you think this project is too hard for you and you have no idea where to even start, you’re probably right. You might need an extra hand to build a real application on your own. If that’s the case, watch the video tutorial for this project and then come back and try it again on your own. It’s perfectly ok to feel lost, since most beginner courses don’t actually teach you how to build something.

So let’s go!

Requirements

This is an application where you’ll log occurrences of a habit.
This habit can't be tracked by time (ex. hours of sleep), only by quantity (ex. number of water glasses a day)
Users need to be able to input the date of the occurrence of the habit
The application should store and retrieve data from a real database
When the application starts, it should create a sqlite database, if one isn’t present.
It should also create a table in the database, where the habit will be logged.
The users should be able to insert, delete, update and view their logged habit.
You should handle all possible errors so that the application never crashes.
You can only interact with the database using ADO.NET. You can’t use mappers such as Entity Framework or Dapper.
Follow the DRY Principle, and avoid code repetition.
Your project needs to contain a Read Me file where you'll explain how your app works. Here's a nice example:

Github project with an example of a tidy Read Me file.
Don't panic! I'll help! 😁

Tips

Read this article about the KISS principle and try to apply it to this project.
Test your SQL commands on DB Browser before using them in your program.
To improve the user's experience, when asking for a date input, give the option to type a simple command to add today's date
You can keep all of the code in one single class if you wish. We'll deal with Object Oriented Programming in the next project
Use a switch statement for the user input menus.
Don't forget the user input's validation: Check for incorrect dates. What happens if a menu option is chosen that's not available? What happens if the users input a string instead of a number?

Challenges

If you haven't, try using parameterized queries to make your application more secure.
Let the users create their own habits to track. That will require that you let them choose the unit of measurement of each habit.
Seed Data into the database automatically when the database gets created for the first time, generating a few habits and inserting a hundred records with randomly generated values. This is specially helpful during development so you don't have to reinsert data every time you create the database.
Create a report functionality where the users can view specific information (i.e. how many times the user ran in a year? how many kms?) SQL allows you to ask very interesting things from your database.

AI Challenge

Since this is a slightly more advanced challenge you'll be rewarded with 20 extra points. It taps into the future of programming: Artificial intelligence. Can you let the users add records using their voice? For this you'll use Azure's Language Services. You can find a step-by-step tutorial below:

Changing Your Working Directory

This way .NET will build your project in your main folder. By default it builds your project in a bin folder and just to keep things simple we want to avoid that. That will create a Properties folder with a launchsettings.json file containing your configuration information. This is an important step only for applications that use Sqlite because you want the database to be created in the same folder of the application to avoid confusion.

For that, click on the chevron next to the name of your app on the top menu, click on {nameoftheapp} Debug Properties and copy the path of your directory to the 'Working Directory' field. To find out what your path is, you can right click on your project in the Solution Explorer and on “Copy Full Path” or look it up in your Files Explorer. If you’re using Mac/Visual Studio Code, reach out and I’ll tell you how to do it.

Start Coding!

Every time you start your app, it should check if there’s a database. If there isn’t, it will create one along with a table where you’ll store your data. If you delete your database externally, it will always create one. If the database exists, it will move to the next step: take the user input about what you want to do. Something like this:

In ASP.NET C# development, the use of SQL Server is very common. But we won’t be using it here. SQLite is a super lightweight database system and it’s important to get familiar with it before jumping into SQL server.

You’ll need to tell your program to create a sqlite file. You’ll be able to visualise that file externally with the help of a small application. Here's a link to a basic sqlite tutorial.

That’s where your actual code begins! You’re on your own for a while. If you get stuck, keep trying, remember the steps to debug your app/get unstuck. And if you ultimately can’t do it, reach out to our Discord Community or myself and we will help!

Video Tutorial

If you’re feeling totally lost, it’s perfectly ok to watch a video tutorial to get you going. Here you’ll learn to connect all the pieces to build a real application. Once you finish it, make sure you try it again on your own without the help of the video so you internalise the newly acquired knowledge.

Code Review

If you want your code to be reviewed by a member of the C# Academy, instead of creating your own repository, follow this article, where you’ll learn how to create a fork from our base review repository.

Review Repository

https://github.com/TheCSharpAcademy/CodeReviews.Console.HabitTracker

Habit Logger