CODEWITHSUNDEEP
c#mysql
R0ede
R0ede

Reputation: 53

Best way to insert multiple rows in mysql with c#?

I need to input mutiple rows of data into this MySQL using C#. enter image description here

I have this code below for inserting one row, but I am not sure how to approach multiple rows at once. I need to input a maximum of 8 rows into the table. Should i make multiple constructors or is there a better way?

public class Vo2phases
{
    //properties for vo2max phases
    public int VO2TestID { get; set; }
    public int Phase { get; set; }
    public DateTime Time { get; set; }
    public int Intensity { get; set; }
    public double RER { get; set; }
    public int Borgscale { get; set; }
    public int Pulse { get; set; }

    public Vo2phases(int _vo2testid, int _phase, 
                     DateTime _time, int _intensity, 
                     double _rer, int _borgscale, int _pulse)
    {
        //set parameters for phases
        VO2TestID = _vo2testid;
        Phase = _phase;
        Time = _time;
        Intensity = _intensity;
        RER = _rer;
        Borgscale = _borgscale;
        Pulse = _pulse;
    }

    public void addToDB()
    {
        try
        {
            //prepare for query
            var cmd = MySQL.readyQuery();

            //insert testdata to Vo2test
            cmd.CommandText = "INSERT INTO vo2phases_tb (Vo2_TestID, Phase, Time, Intensity, RER, Borgscale, Pulse) VALUES (@Vo2testid, @phase, @time, @intensity, @rer, @borgscale, @pulse);";

            cmd.Prepare();
            cmd.Parameters.AddWithValue("@vo2testid", VO2TestID);
            cmd.Parameters.AddWithValue("@phase", Phase);
            string TimeString = "00:" + Convert.ToString(Time.TimeOfDay.Hours) + ":" + Convert.ToString(Time.TimeOfDay.Minutes);
            cmd.Parameters.AddWithValue("@time", TimeString);
            cmd.Parameters.AddWithValue("@intensity", Intensity);
            cmd.Parameters.AddWithValue("@rer", RER);
            cmd.Parameters.AddWithValue("@borgscale", Borgscale);
            cmd.Parameters.AddWithValue("@pulse", Pulse);

            cmd.ExecuteNonQuery();

            cmd.Connection.Close();
        }
        catch (MySqlException ex)
        {
            Console.WriteLine(ex.Message);
        }

    }

Upvotes: 0

Views: 1269

Answers (2)

Ortund
Ortund

Reputation: 8255

Your problem is that you only seem to be expecting 1 Vo2phase in your code.

While this isn't a problem in concept or even in execution, it is lacking because it doesn't account for a collection being submitted.

I would overload addToDB so that it accepts a List as a parameter.

public void addToDB(List<Vo2phase> phases)
{
    try
    {
        //prepare for query
        var cmd = MySQL.readyQuery();

        //insert testdata to Vo2test
        cmd.CommandText = "INSERT INTO vo2phases_tb (Vo2_TestID, Phase, Time, Intensity, RER, Borgscale, Pulse) VALUES (@Vo2testid, @phase, @time, @intensity, @rer, @borgscale, @pulse);";

        cmd.Prepare();
        foreach (Vo2phase phase in phases)
        {
            cmd.Parameters.AddWithValue("@vo2testid", VO2TestID);
            cmd.Parameters.AddWithValue("@phase", Phase);
            string TimeString = "00:" + Convert.ToString(Time.TimeOfDay.Hours) + ":" + Convert.ToString(Time.TimeOfDay.Minutes);
            cmd.Parameters.AddWithValue("@time", TimeString);
            cmd.Parameters.AddWithValue("@intensity", Intensity);
            cmd.Parameters.AddWithValue("@rer", RER);
            cmd.Parameters.AddWithValue("@borgscale", Borgscale);
            cmd.Parameters.AddWithValue("@pulse", Pulse);

            cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
        }
        cmd.Connection.Close();
    }
    catch (MySqlException ex)
    {
        Console.WriteLine(ex.Message);
    }

}

Now to build the list, you'd simply add in a whole bunch of Vo2phases:

List<Vo2phase> Phases = new List<Vo2phase>
{
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...),
    new Vo2phase(VO2TestID = testId, Phase = i ...)

    // add the parameters as you normally would.
};

Hope this helps!

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

My approach, if I was expecting a high volume of inserts, would be to make the addToDB a static method that takes a connection and a collection of Vo2phases; you pretty much lose any benefit to, and actually incur some cost from, prepare when you only use it for single command executions.

Barring that, the prepared command could be maintained as a static member variable so that it is not recreated constantly; however, then you'd probably devote some code to verifying that command has not "gone bad" since it was last used.

Upvotes: 0

Related Questions