Saving Multiple Lists to Database - C# Forms

Question

I am trying to store multiple lists to a database but I'm struggling on finding a better way of doing it, as the way I am doing it now, seems to be incorrect.

On my form, I have 6 different legs. Each leg consists of Cargo Weight, Bag Weight, Fuel Weight, and more.

Right now I am storing all the data to a new class called FlightDetails, where that class just has properties, no methods. For each leg, I am having to re-set all the property details, but for a different leg as seen below:

        private void InsertLeg1()
    {
        List<FlightDetails> leg1 = new List<FlightDetails>();
        leg1.Add(new FlightDetails
        {
            Date = dateTimeLoadPlanner.Value.Date,
            FlightNumber = cbFlightNumber.Text,
            LegNumber = 1,
            PassengerWeight = Convert.ToInt32(tbPax_L1.Text),
            BagWeight = Convert.ToInt32(tbBags_L1.Text),
            CargoWeight = Convert.ToInt32(tbCargo_L1.Text),
            Equipment = Convert.ToInt32(tbEquip_L1.Text),
            AWI = Convert.ToInt32(tbAWI_L1.Text),
            Fuel = Convert.ToInt32(tbFuel_L1.Text),
            Contigency = Convert.ToInt32(tbCont_L1.Text),
            TaxiBurn = Convert.ToInt32(tbTaxiBurn_L1.Text),
            TakeOffWeight = Convert.ToInt32(tbMTOW_L1.Text),
            LandingWeight = Convert.ToInt32(tbLandingWT_L1.Text),
            PassengerNumber = Convert.ToInt32(tbPaxNo_L1.Text),
            Seatpacks = Convert.ToInt32(tbSeatpacks_L1.Text),
            Aircraft = cbAircraft.Text,
            AircraftType = cbAircarftType.Text,
            Crew = cbCrew.Text,
            Notes = tbNotesALC.Text,
            AircraftWeight = Convert.ToInt32(tbAircraftWeight.Text),
            FuelBurn = Convert.ToInt32(tbFuelBurn_L1.Text),
            AircraftConfiguration = tbSeats_L1.Text
        });

        SaveDataALC.SaveLeg(leg1);
    }

    private void InsertLeg2()
    {
        List<FlightDetails> leg2 = new List<FlightDetails>();
        leg2.Add(new FlightDetails
        {
            Date = dateTimeLoadPlanner.Value.Date,
            FlightNumber = cbFlightNumber.Text,
            LegNumber = 2,
            PassengerWeight = Convert.ToInt32(tbPax_L2.Text),
            BagWeight = Convert.ToInt32(tbBags_L2.Text),
            CargoWeight = Convert.ToInt32(tbCargo_L2.Text),
            Equipment = Convert.ToInt32(tbEquip_L2.Text),
            AWI = Convert.ToInt32(tbAWI_L2.Text),
            Fuel = Convert.ToInt32(tbFuel_L2.Text),
            Contigency = Convert.ToInt32(tbCont_L2.Text),
            TaxiBurn = Convert.ToInt32(tbTaxiBurn_L2.Text),
            TakeOffWeight = Convert.ToInt32(tbMTOW_L2.Text),
            LandingWeight = Convert.ToInt32(tbLandingWT_L2.Text),
            PassengerNumber = Convert.ToInt32(tbPaxNo_L2.Text),
            Seatpacks = Convert.ToInt32(tbSeatpacks_L2.Text),
            Aircraft = cbAircraft.Text,
            AircraftType = cbAircarftType.Text,
            Crew = cbCrew.Text,
            Notes = tbNotesALC.Text,
            AircraftWeight = Convert.ToInt32(tbAircraftWeight.Text),
            FuelBurn = Convert.ToInt32(tbFuelBurn_L2.Text),
            AircraftConfiguration = tbSeats_L2.Text
        });

        SaveDataALC.SaveLeg(leg2);
    }


    public void InsertLeg3()
    {
        List<FlightDetails> leg3 = new List<FlightDetails>();
        leg3.Add(new FlightDetails
        {
            Date = dateTimeLoadPlanner.Value.Date,
            FlightNumber = cbFlightNumber.Text,
            LegNumber = 3,
            PassengerWeight = Convert.ToInt32(tbPax_L3.Text),
            BagWeight = Convert.ToInt32(tbBags_L3.Text),
            CargoWeight = Convert.ToInt32(tbCargo_L3.Text),
            Equipment = Convert.ToInt32(tbEquip_L3.Text),
            AWI = Convert.ToInt32(tbAWI_L3.Text),
            Fuel = Convert.ToInt32(tbFuel_L3.Text),
            Contigency = Convert.ToInt32(tbCont_L3.Text),
            TaxiBurn = Convert.ToInt32(tbTaxiBurn_L3.Text),
            TakeOffWeight = Convert.ToInt32(tbMTOW_L3.Text),
            LandingWeight = Convert.ToInt32(tbLandingWT_L3.Text),
            PassengerNumber = Convert.ToInt32(tbPaxNo_L3.Text),
            Seatpacks = Convert.ToInt32(tbSeatpacks_L3.Text),
            Aircraft = cbAircraft.Text,
            AircraftType = cbAircarftType.Text,
            Crew = cbCrew.Text,
            Notes = tbNotesALC.Text,
            AircraftWeight = Convert.ToInt32(tbAircraftWeight.Text),
            FuelBurn = Convert.ToInt32(tbFuelBurn_L3.Text),
            AircraftConfiguration = tbSeats_L3.Text
        });

        SaveDataALC.SaveLeg(leg3);
    }

Once that information is passed, I am storing it in the database here:

public static void SaveLeg(List<FlightDetails> legDetails)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionLoader.ConnectionString("Threshold")))
        {
            foreach (var legDetail in legDetails)
            {
                conn.Open();
                SqlCommand saveData = new SqlCommand("Save_Leg_Loadplanner", conn);
                saveData.CommandType = CommandType.StoredProcedure;
                saveData.Parameters.AddWithValue("@DateID", legDetail.Date);
                saveData.Parameters.AddWithValue("@FlightNumber", legDetail.FlightNumber);
                saveData.Parameters.AddWithValue("@LegNumber", legDetail.LegNumber);
                saveData.Parameters.AddWithValue("@PassengerWeight", legDetail.PassengerWeight);
                saveData.Parameters.AddWithValue("@BagWeight", legDetail.BagWeight);
                saveData.Parameters.AddWithValue("@CargoWeight", legDetail.CargoWeight);
                saveData.Parameters.AddWithValue("@Equipment", legDetail.Equipment);
                saveData.Parameters.AddWithValue("@AWI", legDetail.AWI);
                saveData.Parameters.AddWithValue("@Fuel", legDetail.Fuel);
                saveData.Parameters.AddWithValue("@TaxiBurn", legDetail.TaxiBurn);
                saveData.Parameters.AddWithValue("@Contigency", legDetail.Contigency);
                saveData.Parameters.AddWithValue("@TakeOffWeight", legDetail.TakeOffWeight);
                saveData.Parameters.AddWithValue("@LandingWeight", legDetail.LandingWeight);
                saveData.Parameters.AddWithValue("@PassengerNumber", legDetail.PassengerNumber);
                saveData.Parameters.AddWithValue("@Seatpacks", legDetail.Seatpacks);
                saveData.Parameters.AddWithValue("@Aircraft", legDetail.Aircraft);
                saveData.Parameters.AddWithValue("@Notes", legDetail.Notes);
                saveData.Parameters.AddWithValue("@Crew", legDetail.Crew);
                saveData.Parameters.AddWithValue("@AircraftType", legDetail.AircraftType);
                saveData.Parameters.AddWithValue("@AircraftWeight", legDetail.AircraftWeight);
                saveData.Parameters.AddWithValue("@FuelBurn", legDetail.FuelBurn);
                saveData.Parameters.AddWithValue("@AircraftConfiguration", legDetail.AircraftConfiguration);
                saveData.ExecuteNonQuery();
            }
        }
    }

I'm not sure if there is a better way of doing this. This does work, but I feel there may be a more cleaner way of doing this.

Answer 1

Quick remarks:

• Why do you use a stored proc? Why not use an ORM like EntityFramework, or even Dapper?

• Use descriptive names. leg1 doesn't gain you anything.

• Don't copy-paste, only to change a few names. InsertLeg1, InsertLeg2 and InsertLeg3 should really be a single method, but I fear your design makes this too hard. It's been far too long since I've used WinForms, but it should be possible to have a reusable "sub form" that contains all those controls, that way you could have a single method that receives this "sub form" to convert it into a FlightDetails.

• I don't like the class name FlightDetails (not in the least because it is a plural), but I cannot think of a better one.

Answer 2

InsertLeg{X}

• I would suggest to extract the common parts into dedicated methods
• First let's start with the Collection initilization and Save method call

private void InsertLeg(FlightDetails leg)
    => SaveDataALC.SaveLeg(new List<FlightDetails> { leg });

• Then let's continue with those properties where the data source is the same for all three cases

FlightDetails CreateDetails()
    => new FlightDetails
    {
        Date = dateTimeLoadPlanner.Value.Date,
        FlightNumber = cbFlightNumber.Text,
        Aircraft = cbAircraft.Text,
        AircraftType = cbAircarftType.Text,
        Crew = cbCrew.Text,
        Notes = tbNotesALC.Text,
    };

• And finally introduce a helper method to retrieve Text property of a TextBox as an integer

int GetTextAsInteger(TextBox tb)
    => Convert.ToInt32(tb.Text);

• Please bear in mind that this code is error-prone
  • Please prefer int.TryParse

---

After all these modifications the InsertLeg{x} could be rewritten like this

void InsertLeg1()
{
    var details = CreateDetails();
    details.LegNumber = 1;
    details.PassengerWeight = GetTextAsInteger(tbPax_L1);
    details.BagWeight = GetTextAsInteger(tbBags_L1);
    details.CargoWeight = GetTextAsInteger(tbCargo_L1);
    details.Equipment = GetTextAsInteger(tbEquip_L1);
    details.AWI = GetTextAsInteger(tbAWI_L1);
    details.Fuel = GetTextAsInteger(tbFuel_L1);
    details.Contigency = GetTextAsInteger(tbCont_L1);
    details.TaxiBurn = GetTextAsInteger(tbTaxiBurn_L1);
    details.TakeOffWeight = GetTextAsInteger(tbMTOW_L1);
    details.LandingWeight = GetTextAsInteger(tbLandingWT_L1);
    details.PassengerNumber = GetTextAsInteger(tbPaxNo_L1);
    details.Seatpacks = GetTextAsInteger(tbSeatpacks_L1);
    details.AircraftWeight = GetTextAsInteger(tbAircraftWeight);
    details.FuelBurn = GetTextAsInteger(tbFuelBurn_L1);
    details.AircraftConfiguration = tbSeats_L1.Text;
    InsertLeg(details);
}

SaveLeg

• I think it is enough to call only once the conn.Open
  • You do not have to do that for each element in your collection
• Rather than using AddWithValue you can use the AddRange command

saveData.Parameters.AddRange(new[]
{
    new SqlParameter("@DateID", legDetail.Date),
    new SqlParameter("@FlightNumber", legDetail.FlightNumber),
    new SqlParameter("@LegNumber", legDetail.LegNumber),
    new SqlParameter("@PassengerWeight", legDetail.PassengerWeight),
    new SqlParameter("@BagWeight", legDetail.BagWeight),
    new SqlParameter("@CargoWeight", legDetail.CargoWeight),
    new SqlParameter("@Equipment", legDetail.Equipment),
    new SqlParameter("@AWI", legDetail.AWI),
    new SqlParameter("@Fuel", legDetail.Fuel),
    new SqlParameter("@TaxiBurn", legDetail.TaxiBurn),
    new SqlParameter("@Contigency", legDetail.Contigency),
    new SqlParameter("@TakeOffWeight", legDetail.TakeOffWeight),
    new SqlParameter("@LandingWeight", legDetail.LandingWeight),
    new SqlParameter("@PassengerNumber", legDetail.PassengerNumber),
    new SqlParameter("@Seatpacks", legDetail.Seatpacks),
    new SqlParameter("@Aircraft", legDetail.Aircraft),
    new SqlParameter("@Notes", legDetail.Notes),
    new SqlParameter("@Crew", legDetail.Crew),
    new SqlParameter("@AircraftType", legDetail.AircraftType),
    new SqlParameter("@AircraftWeight", legDetail.AircraftWeight),
    new SqlParameter("@FuelBurn", legDetail.FuelBurn),
    new SqlParameter("@AircraftConfiguration", legDetail.AircraftConfiguration)
});

Answer 3

InsertLeg1, InsertLeg2, InsertLeg3 ..etc. I don't see why they're saving a single instance of FlightDetails to a list? So SaveDataALC.SaveLeg will process a List<FlightDetails> with only one element inside it. if there are multiple instances of FlightDetails then you propabaly need to store them all in one List<FlightDetails> then pass that list to SaveDataALC.SaveLeg once.

So, to make the story short, you might need to declare a global List<FlightDetails> and use it accross the class, and when you need to insert the values, just call the insert method once.

example :

public class ExampleClass
{
    private List<FlightDetails> _flightDetails = new List<FlightDetails>(); 
    
    public void SomeMethod() {
        var flightLeg = new FlightDetails {
            Date = dateTimeLoadPlanner.Value.Date,
            FlightNumber = cbFlightNumber.Text,
            LegNumber = 1,
            PassengerWeight = Convert.ToInt32(tbPax_L1.Text),
            BagWeight = Convert.ToInt32(tbBags_L1.Text),
            CargoWeight = Convert.ToInt32(tbCargo_L1.Text),
            Equipment = Convert.ToInt32(tbEquip_L1.Text),
            AWI = Convert.ToInt32(tbAWI_L1.Text),
            Fuel = Convert.ToInt32(tbFuel_L1.Text),
            Contigency = Convert.ToInt32(tbCont_L1.Text),
            TaxiBurn = Convert.ToInt32(tbTaxiBurn_L1.Text),
            TakeOffWeight = Convert.ToInt32(tbMTOW_L1.Text),
            LandingWeight = Convert.ToInt32(tbLandingWT_L1.Text),
            PassengerNumber = Convert.ToInt32(tbPaxNo_L1.Text),
            Seatpacks = Convert.ToInt32(tbSeatpacks_L1.Text),
            Aircraft = cbAircraft.Text,
            AircraftType = cbAircarftType.Text,
            Crew = cbCrew.Text,
            Notes = tbNotesALC.Text,
            AircraftWeight = Convert.ToInt32(tbAircraftWeight.Text),
            FuelBurn = Convert.ToInt32(tbFuelBurn_L1.Text),
            AircraftConfiguration = tbSeats_L1.Text
        };
        
        _flightDetails.Add(flightLeg);
    }
        
    private void InsertLegs() {
        SaveDataALC.SaveLeg(_flightDetails);    
        // if you want to reuse it then you can clear the list
        // by uncommenting the following line.
        // _flightDetails.Clear();
    }
}

The SomeMethod is just shows that you need to use _flightDetails for adding new FlightDetails in all related methods within the class.