CRUD operations

Create a C# program that allows you to perform CRUD operations from SQLite, the program must have the following menu:

• 1. Add
• 2. Show
• 3. Edit
• 4. Delete
• 5. Exit

First prepare a SQLite version 3 database of people with a person table using the input's SQL query. Then create the CRUD operations (add, view, edit, and delete).

Add

Ask the user for a name and age and insert the data into the database.

Show

Show all the people saved in the database.

Edit

Request the person's code from the user and, if it exists, request the new name and age to modify the data in the database.

Delete

Request the person's code from the user and if it exists, delete the person from the database.

Exit

End the program.

You don't have to worry about the person's code as it is auto-incremental.

Input

create table if not exists person
(
   cod integer primary key autoincrement,
   name varchar(20),
   age int
)

Output

Solution

using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
 
public class CRUDSQLite
{
    public static string DatabaseFileName = "persons.sqlite";
    public static int OptionMenu = -1;
 
    public static void Main(string[] args)
    {
        CreateDatabaseIfNotExists();
        CreateTablesIfNotExists();
 
        do
        {
            ShowMenu();
 
            switch (OptionMenu)
            {
                case 1: Add();    break;
                case 2: Show();   break;
                case 3: Edit();   break;
                case 4: Delete(); break;
            }
        } while (OptionMenu != 5);
    }
 
    public static void ShowMenu()
    {
        Console.WriteLine();
        Console.WriteLine("1. Add");
        Console.WriteLine("2. Show");
        Console.WriteLine("3. Edit");
        Console.WriteLine("4. Delete");
        Console.WriteLine("5. Exit");
 
        Console.WriteLine();
        Console.Write("Enter a option: ");
        OptionMenu = int.Parse(Console.ReadLine());
 
        Console.Clear();
    }
 
    public static void CreateDatabaseIfNotExists()
    {
        if (!File.Exists(DatabaseFileName))
        {
            SQLiteConnection.CreateFile(DatabaseFileName);
        }
    }
 
    public static void CreateTablesIfNotExists()
    {
        using (SQLiteConnection cnx =
            new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
        {
            cnx.Open();
 
            string sql = "create table if not exists person("+
                "cod integer primary key autoincrement,"+
                "name varchar(20),"+
                "age int)";
            using (SQLiteCommand cmd = new SQLiteCommand(sql, cnx))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
 
    public static void Add()
    {
        Console.Clear();
        Console.WriteLine("Add");
        Console.WriteLine();
 
        Console.Write("Name: ");
        string name = Console.ReadLine();
 
        Console.Write("Age: ");
        int age = int.Parse(Console.ReadLine());
 
        using (SQLiteConnection cnx =
            new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
        {
            cnx.Open();
 
            string sql = "insert into person (name, age) values ('" + name + "'," + age + ")";
            using (SQLiteCommand cmd = new SQLiteCommand(sql, cnx))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
 
    public static void Show()
    {
        Console.Clear();
        Console.WriteLine("Show");
        Console.WriteLine();
 
        using (SQLiteConnection cnx =
            new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
        {
            cnx.Open();
            using (SQLiteCommand cmd = cnx.CreateCommand())
            {
                cmd.CommandText = "select * from person";
                cmd.CommandType = CommandType.Text;
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("Cod: " + reader["cod"].ToString());
                    Console.WriteLine("Name: " + reader["name"].ToString());
                    Console.WriteLine("Age: " + reader["age"].ToString());
                    Console.WriteLine();
                }
            }
        }
    }
 
    public static void Edit()
    {
        Console.Clear();
        Console.WriteLine("Edit");
        Console.WriteLine();
 
        Console.Write("Cod: ");
        int codPerson = int.Parse(Console.ReadLine());
 
        if (existPerson(codPerson))
        {
            Console.Write("Name: ");
            string newName = Console.ReadLine();
 
            Console.Write("Age: ");
            int newAge = int.Parse(Console.ReadLine());
 
            using (SQLiteConnection cnx =
                new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
            {
                cnx.Open();
 
                string sql = "update person set name='" + newName + "',"+
                                               "age=" + newAge + " where cod=" + codPerson;
                using (SQLiteCommand cmd = new SQLiteCommand(sql, cnx))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        else
        {
            Console.WriteLine("Not found");
        }
    }
 
    public static void Delete()
    {
        Console.Clear();
        Console.WriteLine("Delete");
        Console.WriteLine();
 
        Console.Write("Cod: ");
        int codPerson = int.Parse(Console.ReadLine());
 
        if (existPerson(codPerson))
        {
            using (SQLiteConnection cnx =
                new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
            {
                cnx.Open();
 
                string sql = "delete from person where cod=" + codPerson;
                using (SQLiteCommand cmd = new SQLiteCommand(sql, cnx))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        else
        {
            Console.WriteLine("Not found");
        }
    }
 
    private static bool existPerson(int codPerson)
    {
        bool exist = false;
 
        using (SQLiteConnection cnx =
            new SQLiteConnection("Data Source=" + DatabaseFileName + ";Version=3;"))
        {
            cnx.Open();
            using (SQLiteCommand cmd = cnx.CreateCommand())
            {
                cmd.CommandText = "select 1 as total from person where cod=" + codPerson;
                cmd.CommandType = CommandType.Text;
                SQLiteDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    exist = reader["total"].ToString() == "1" ? true : false;
                }
            }
        }
 
        return exist;
    }
}