How to insert a list of object to mysql stored procedure

Question

I have a controller that accepts a list of object then insert it to database I already done it but I think my code will have a problem if there will be a large of data.

My code

     public void SaveAnswers(List<questionModel.SAVEDANSWER> answers, int userid)
    {
        string query = "INSERT INTO answer (QUESTIONID,USERANSWERID, USERID) VALUES (@qid, @aid,@uid);";
        using (MySqlConnection myconn = new MySqlConnection(cmn.connstring))
        {
            myconn.Open();
            for (int i = 0; i <= answers.Count - 1; i++)
                using (MySqlCommand myCmd = new MySqlCommand(query, myconn))
                {
                    myCmd.CommandType = System.Data.CommandType.Text;
                    MySqlParameter questionid = myCmd.Parameters.AddWithValue("@qid", answers[i].QUESTIONID);
                    myCmd.Parameters.AddWithValue("@qid", answers[i].QUESTIONID);
                    myCmd.Parameters.AddWithValue("@aid", answers[i].ANSWERID);
                    myCmd.Parameters.AddWithValue("@uid", userid);
                    myCmd.ExecuteNonQuery();
                }
            myconn.Close();
            myconn.Dispose();
        }
    }

I did something similar in my SQL Server. I inserted the list of object to data table then pass the data table to the stored procedure.

In my stored procedure in SQL Server, I have a table parameter.

Can I do something like that in MySQL?

User defined table type are not available in mysql. So unfortunately you can handle this case same as you can do in sql server. — Chetan, Aug 20 '17 at 04:27
https://stackoverflow.com/questions/10929586/what-is-the-difference-between-for-and-foreach — Jeric Cruz, Aug 20 '17 at 05:19
it won't change anything. @Justcode Maybe you can use Parallel.ForEach — Caner, Aug 20 '17 at 09:51
Please check it. You may help you. https://stackoverflow.com/questions/25323560/most-efficient-way-to-insert-rows-into-mysql-database — Caner, Aug 20 '17 at 09:58

score 0 · Answer 1 · answered Aug 20 '17 at 06:05

you can use multiple insert rows approach with List builder(assume all are integers)

 public void SaveAnswers(List<questionModel.SAVEDANSWER> answers, int userid)
    {
        StringBuilder query = new StringBuilder("INSERT INTO answer (QUESTIONID,USERANSWERID, USERID) VALUES";

        using (MySqlConnection myconn = new MySqlConnection(cmn.connstring))
        {
            List<String> Rows = new List<String>();
            for (int i = 0; i <= answers.Count - 1; i++)
            {
              Rows.Add(string.Format("({0},{1},{2})", (answers[i].QUESTIONID), (answers[i].ANSWERID),(answers[i].userId)));
            }
        query.Append(string.Join(",", Rows));
        query.Append(";");
        myconn.Open();
        using (MySqlCommand myCmd = new MySqlCommand(query.ToString(), myconn))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.ExecuteNonQuery();
        }
        myconn.Close();
        }
    }

Jeric Cruz · Answer 2 · 2017-08-20T10:01:42.300

If you are using C#6, you can try string interpolation to your query inside of a foreach statement.

public void SaveAnswers(List<questionModel.SAVEDANSWER> answers, int userid)
{   
    using (MySqlConnection myconn = new MySqlConnection(cmn.connstring))
    {
        myconn.Open();
        foreach(var item in answers)
        {
            //do string interpolation of your query.
            var myCmd = new MySqlCommand($@"INSERT INTO answer (QUESTIONID,USERANSWERID, USERID) VALUES ({item.QUESTIONID}, {item.ANSWERID},{userid})", myconn))
            myCmd.CommandType = System.Data.CommandType.Text;
            myCmd.ExecuteNonQuery();
        }       
    }   
}

this is very wrong. in each loop you made the connection. – Caner Aug 20 '17 at 09:52 — Caner, Aug 20 '17 at 09:52

How to insert a list of object to mysql stored procedure

2 Answers2