C#•11mo ago

✅ Trying To Get Better At Database Command Execution

code : https://pastebin.com/hH3nGTMe how database is created: https://pastebin.com/wHLmbu9w In my code above (link 1), I have a function and a model. The model is used to hold the totals that are going to be displayed on the home screen of my console based application. My goal is to get a much better understanding of sql commands with obtaining sums from items within a database. In link 2, is how the database is created. When I call the GetTotals() function from the home screen of my application, I want it to look like

Total Gas: $123,344
Total Mileage: 122,222
Total Maintenance: $123,123

Total Gas: $123,344
Total Mileage: 122,222
Total Maintenance: $123,123

just as an example. I know that there are sql commands that, when executed, can pull all the desired information from the table and sum up the numbers for me, however, I don't know how to do that. All I know to do is manually pull the information itself, pass that information off to a helper function, and sum the totals that way. Again, my goal here is to get better at the sql language and be able to write something like somethingHere.CommandText = @"SELECT x FROM table && SUM ALL x"; or however its written so that the sql execution does the summations for me and I can just write a one-line if statement to return the summation or 0 if no summation exists. Thanks in advance

18 Replies

Jimmacle•11mo ago

the key term you're looking for is "aggregate functions" https://www.sqlite.org/lang_aggfunc.html

Mekasu0124OP•11mo ago

ty ok so with my shifts table.

createShiftsTable.CommandText = @"CREATE TABLE IF NOT EXISTS
                                    [shifts] (
                                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                    [Date] VARCHAR(2048),
                                    [StartTime] VARCHAR(2048),
                                    [EndTime] VARCHAR(2048),
                                    [StartMileage] INTEGER,
                                    [EndMileage] INTEGER,
                                    [Earnings] REAL)";

createShiftsTable.CommandText = @"CREATE TABLE IF NOT EXISTS
                                    [shifts] (
                                    [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                    [Date] VARCHAR(2048),
                                    [StartTime] VARCHAR(2048),
                                    [EndTime] VARCHAR(2048),
                                    [StartMileage] INTEGER,
                                    [EndMileage] INTEGER,
                                    [Earnings] REAL)";

if I wanted to get the total for mileage, I would do

public List<string> GetTotals()
{
    using SQLiteConnection conn = new(_dbPath);
    using SQLiteCommand shiftInfo = conn.CreateCommand();
    SQLiteDataReader shiftReader;
    TotalsModel totals = new();

    conn.Open();

    shiftInfo.CommandText = @"SELECT sum(EndMileage - StartMileage) FROM shifts";

    shiftReader = shiftInfo.ExecuteReader();

    try
    {
        while (shiftReader.Read())
        {
            totals = new()
            {
                TotalMileage = Convert.ToInt32(shiftReader)
            }
        }
    }
}

public List<string> GetTotals()
{
    using SQLiteConnection conn = new(_dbPath);
    using SQLiteCommand shiftInfo = conn.CreateCommand();
    SQLiteDataReader shiftReader;
    TotalsModel totals = new();

    conn.Open();

    shiftInfo.CommandText = @"SELECT sum(EndMileage - StartMileage) FROM shifts";

    shiftReader = shiftInfo.ExecuteReader();

    try
    {
        while (shiftReader.Read())
        {
            totals = new()
            {
                TotalMileage = Convert.ToInt32(shiftReader)
            }
        }
    }
}

right? from the shifts table, I need to get the total time, total mileage, and total earnings.

Jimmacle•11mo ago

i'm pretty sure that'll work, best to try it though since sqlite doesn't have an actual date/time type you'll need extra functions to treat those as dates and times iirc

Mekasu0124OP•11mo ago

I can do that with helper functions, not a problem. I'm just trying to find the most efficient way to do the following: 1. From the shifts table -> Total Mileage, Total Time, Total Earnings 2. From the gas table -> Total Gas Money 3. From the maintenance table -> Total Maintenance Money using the model

public class TotalsModel
{
    public double TotalGas { get; set; } // total gas money from gas table
    public double TotalMaintenance { get; set; } // total maintenance money from the maintenance table
    public TimeSpan TotalTime { get; set; } // total time from the shifts table
    public int TotalMileage { get; set; } // total mileage from the shifts table
    public double TotalEarnings { get; set; } // total earnings from the shifts table
}

public class TotalsModel
{
    public double TotalGas { get; set; } // total gas money from gas table
    public double TotalMaintenance { get; set; } // total maintenance money from the maintenance table
    public TimeSpan TotalTime { get; set; } // total time from the shifts table
    public int TotalMileage { get; set; } // total mileage from the shifts table
    public double TotalEarnings { get; set; } // total earnings from the shifts table
}

so if it's more efficient to use helpers functions to calculate the sums, then I can do that. I just can't exactly remember how to read the information from the database to pass it off to that helper function

Jimmacle•11mo ago

it's less efficient to calculate client-side since you need to send all that data instead of just the result sqlite has date/time functions built in, i'm just not sure if they do what you need

Mekasu0124OP•11mo ago

I just need either a helper function, or aggregated function, to calculate the total time between StartTime and EndTime for each record in the database

Jimmacle•11mo ago

https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite?

Mekasu0124OP•11mo ago

although it's not efficient to do things client side, this application is being built to be ran and used locally. There are no online servers. The information isn't sent to a server when it's saved. Nothing. It's all stored in a local database that resides in the programs files and is created on run-time

Jimmacle•11mo ago

i mean, your question is about doing things in SQL instead of "client side" so i'm trying to stick to that 😄

Mekasu0124OP•11mo ago

this makes me think that I'll need to loop the reader continuously for each record in the database, and have a total variable that is instantiated outside of the loop to add the totals to and then use that total

Mekasu0124OP•11mo ago

that's 100% perfectly fine. I'm trying for that goal as well without things getting too complicated

Jimmacle•11mo ago

or use an aggregate function in the query

Mekasu0124OP•11mo ago

this new territory for me

Jimmacle•11mo ago

aggregate functions will calculate something over all the rows of whatever expression is inside it e.g. the sum of the differences between the start and end time for each record

Mekasu0124OP•11mo ago

ok what would that code look like for example? Like I've never used these before so I'm not sure, and it's been a minute since I've touched C# let alone SQLite and I'm somewhat struggling to remember how to read the information from the database as just a simple step one ok awesome

Jimmacle•11mo ago

just select sum(julianday(EndTime) - julianday(StartTime)) from shifts; i assume that will return 1 row with 1 column that contains the result i don't know how you're currently formatting those times, but it seems like they should be UTC and in ISO 8601 format for this to work correctly (or one of a couple other options) https://www.sqlite.org/lang_datefunc.html

Mekasu0124OP•11mo ago

the date's will be in the format "MM/DD/YYYY" and the times will be in military time like 15:34 for 3:34 PM I'm so confused.

public List<string> GetTotals()
{
    using SQLiteConnection conn = new(_dbPath);
    using SQLiteCommand shiftInfo = conn.CreateCommand();
    using SQLiteCommand gasInfo = conn.CreateCommand();
    using SQLiteCommand maintenanceInfo = conn.CreateCommand();

    SQLiteDataReader reader;
    SQLiteDataReader reader2;
    SQLiteDataReader reader3;

    TotalsModel totals = new();

    double totalGas = 0f;
    double totalMaintenance = 0f;
    TimeSpan totalTime = TimeSpan.Zero;
    int totalMileage = 0;
    double totalEarnings = 0f;

    conn.Open();

    shiftInfo.CommandText = @"SELECT sum(EndMileage - StartMileage) FROM shifts";
    gasInfo.CommandText = @"SELECT sum(Amount) FROM gas";
    maintenanceInfo.CommandText = @"SELECT sum(Amount) FROM maintenance";

    reader = shiftInfo.ExecuteReader();
    reader2 = gasInfo.ExecuteReader();
    reader3 = maintenanceInfo.ExecuteReader();

    try
    {
        while (reader.Read())
        {

        }

        while (reader2.Read())
        {
        }

        while (reader3.Read())
        {
        }
    }
    catch (Exception ex)
    {
        ColorPrint.WriteLine(ex.Message, ConsoleColor.Red);
    }
}

public List<string> GetTotals()
{
    using SQLiteConnection conn = new(_dbPath);
    using SQLiteCommand shiftInfo = conn.CreateCommand();
    using SQLiteCommand gasInfo = conn.CreateCommand();
    using SQLiteCommand maintenanceInfo = conn.CreateCommand();

    SQLiteDataReader reader;
    SQLiteDataReader reader2;
    SQLiteDataReader reader3;

    TotalsModel totals = new();

    double totalGas = 0f;
    double totalMaintenance = 0f;
    TimeSpan totalTime = TimeSpan.Zero;
    int totalMileage = 0;
    double totalEarnings = 0f;

    conn.Open();

    shiftInfo.CommandText = @"SELECT sum(EndMileage - StartMileage) FROM shifts";
    gasInfo.CommandText = @"SELECT sum(Amount) FROM gas";
    maintenanceInfo.CommandText = @"SELECT sum(Amount) FROM maintenance";

    reader = shiftInfo.ExecuteReader();
    reader2 = gasInfo.ExecuteReader();
    reader3 = maintenanceInfo.ExecuteReader();

    try
    {
        while (reader.Read())
        {

        }

        while (reader2.Read())
        {
        }

        while (reader3.Read())
        {
        }
    }
    catch (Exception ex)
    {
        ColorPrint.WriteLine(ex.Message, ConsoleColor.Red);
    }
}

here's here I'm at currently I'm trying to figure out how to get the information and what not in another project, I did something like

public static List<Habit> GetHabits()
{
    List<Habit> habits = new();

    using (SQLiteConnection sqlite = new SQLiteConnection(@"Data Source=habits.db"))
    {
        using (SQLiteCommand cmd = new SQLiteCommand(sqlite))
        {
            SQLiteDataReader reader;

            sqlite.Open();
            cmd.CommandText = "SELECT * FROM habits";
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                int id = int.Parse(reader["Id"].ToString());
                string name = reader["Name"].ToString();
                DateTime date = DateTime.Parse(reader["Date"].ToString());
                int count = int.Parse(reader["Count"].ToString());
                string description = reader["Description"].ToString();

                Habit habit = new() { Id = id, Name = name, Date = date, Count = count, Description = description };

                habits.Add(habit);
             }

            sqlite.Close();
        }
        sqlite.Close();
    }
    return habits;
}

public static List<Habit> GetHabits()
{
    List<Habit> habits = new();

    using (SQLiteConnection sqlite = new SQLiteConnection(@"Data Source=habits.db"))
    {
        using (SQLiteCommand cmd = new SQLiteCommand(sqlite))
        {
            SQLiteDataReader reader;

            sqlite.Open();
            cmd.CommandText = "SELECT * FROM habits";
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                int id = int.Parse(reader["Id"].ToString());
                string name = reader["Name"].ToString();
                DateTime date = DateTime.Parse(reader["Date"].ToString());
                int count = int.Parse(reader["Count"].ToString());
                string description = reader["Description"].ToString();

                Habit habit = new() { Id = id, Name = name, Date = date, Count = count, Description = description };

                habits.Add(habit);
             }

            sqlite.Close();
        }
        sqlite.Close();
    }
    return habits;
}

but with doing shiftInfo.CommandText = @"SELECT sum(EndMileage - StartMileage) FROM shifts"; I have no idea how I would do

try
{
    while (reader.Read())
    {
        totalMileage += _______;
    }
}

try
{
    while (reader.Read())
    {
        totalMileage += _______;
    }
}

normall I believe I would do

try
{
  while (reader.Read())
  {
      totalMileage += Convert.ToInt32(Convert.ToInt32(reader["EndMileage"].ToString()) - Convert.ToInt32(reader["StartMileage"].ToString()))
  }
}

try
{
  while (reader.Read())
  {
      totalMileage += Convert.ToInt32(Convert.ToInt32(reader["EndMileage"].ToString()) - Convert.ToInt32(reader["StartMileage"].ToString()))
  }
}

the only thing I can think of is when the user creates a new entry, calculate the totals there and then save them under a totals column in the shifts table I'm just going to wait. I still have to build the rest of the program. I was trying to go ahead and get all the database functions that I'd need finished before I built the program, but I think I'm just skipping a whole lot of steps with doing that, so I'm going to go back to building the program and then when I'm ready to obtain totals I'll come back to this. Thanks for your help ❤️

Buddy•11mo ago

Just use GetX() X being the datatype

Gaming

Programming

✅ Trying To Get Better At Database Command Execution