How can I create MySql parameters for a list of objects with unknown count?

Question

I want to insert a row for each object I have in a list. The count of this list is unknown but always 10 or less.

"insert into achievement(Rank,Event,UserId) values(@rank,@event,@userid);" Knowing this, there can be up to 10 collections of parameters for values.

So basically my question is: How can I do the following code in short and with a variable count of objects (not always 10)?

"insert into achievement(Rank,Event,UserId) values(@rank1,@event1,@userid) (@rank2,@event2,@userid) (@rank3,@event3,@userid) (@rank4,@event4,@userid) (@rank5,@event5,@userid) (@rank6,@event6,@userid) (@rank7,@event7,@userid) (@rank8,@event8,@userid) (@rank9,@event9,@userid) (@rank10,@event10,@userid)" 

List<Achievement> achievementDTOs;

command.Parameters.AddWithValue("rank1", dto1.Rank);
command.Parameters.AddWithValue("event1", dto1.Event);
command.Parameters.AddWithValue("rank2", dto2.Rank);
command.Parameters.AddWithValue("event2", dto2.Event);
command.Parameters.AddWithValue("rank3", dto3.Rank);
command.Parameters.AddWithValue("event3", dto3.Event);
command.Parameters.AddWithValue("rank4", dto4.Rank);
command.Parameters.AddWithValue("event4", dto4.Event);
command.Parameters.AddWithValue("rank5", dto5.Rank);
command.Parameters.AddWithValue("event5", dto5.Event);
command.Parameters.AddWithValue("rank6", dto6.Rank);
command.Parameters.AddWithValue("event6", dto6.Event);
command.Parameters.AddWithValue("rank7", dto7.Rank);
command.Parameters.AddWithValue("event7", dto7.Event);
command.Parameters.AddWithValue("rank8", dto8.Rank);
command.Parameters.AddWithValue("event8", dto8.Event);
command.Parameters.AddWithValue("rank9", dto9.Rank);
command.Parameters.AddWithValue("event9", dto9.Event);
command.Parameters.AddWithValue("rank10", dto10.Rank);
command.Parameters.AddWithValue("event10", dto10.Event);

ADyson · Accepted Answer · 2019-11-07 11:27:38Z

2

You can loop through your list of achievements and add a new set of parameters, and some new text for the query each time it loops. Something like this:

List<Achievement> achievementDTOs; //we'll assume this is actually populated
string sql = "insert into achievement(Rank,Event,UserId) values ";
string valueSQL = "";

for (int i = 0; i < achievementDTOs.Count; i++) // Loop through List with for
{
  valueSQL += (valueSQL == "" ? "", ",") + "(@event"+i+", @rank"+i+", @user"+i+")";
  command.Parameters.AddWithValue("event"+i, achievementDTOs[i].Event);
  command.Parameters.AddWithValue("rank"+i, achievementDTOs[i].Rank);
  command.Parameters.AddWithValue("user"+i, achievementDTOs[i].UserID);  
}

sql += valueSQL;
//...and now you can execute the SQL

edited Nov 7, 2019 at 11:27

answered Nov 7, 2019 at 9:44

ADyson

62.9k16 gold badges88 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iAgonyii Over a year ago

Thanks for your answer!

probin anand · Accepted Answer · 2019-11-07 10:21:05Z

-1

You can also try creating dynamic parameters as below

MySqlParameter[] myparam = new MySqlParameter[]{
    new MySqlParameter("columnname1",columnvalue1),
    new MySqlParameter("columnname2",columnvalue2),
};

then use the param as a parameter for insert ,update or select

answered Nov 7, 2019 at 10:21

probin anand

1347 bronze badges

1 Comment

ADyson Over a year ago

this example is not dynamic - you're still hard-coding each parameter being added to the array. And you haven't addressed how to deal with the structure of the SQL string as well.

Collectives™ on Stack Overflow

How can I create MySql parameters for a list of objects with unknown count?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related