Parameterized query - multi objects

Question

I found that question that is exactly what I am trying to do. The correct answer seems to do exactly what I want, but I would like to make something not to type all parameters again.

I have a <Repere>, with a function I already implemented for a simple parameterized query :

public MySqlParameter[] GetListMySqlParams()
{
    return this.getListMySqlParams();
}
private MySqlParameter[] getListMySqlParams()
{
    MySqlParameter[] listParams = new MySqlParameter[]
    {
        new MySqlParameter("idContract", this.contrat.ID),
        new MySqlParameter("contractCode", this.Contrat.Code),
        new MySqlParameter("phaseName", this.fase.Name),
        new MySqlParameter("assemblyName", this.assembly.Name),
        new MySqlParameter("idPhase", this.fase.ID),
        new MySqlParameter("idAss", this.assembly.ID),
        new MySqlParameter("name", this.name),
        new MySqlParameter("priority", this.priority),
        new MySqlParameter("quantity", this.quantity),
        new MySqlParameter("totalQuantity", this.totalQuantity),
        new MySqlParameter("listOperations", this.listOperations.ConvertToString()),
        new MySqlParameter("material", this.geometry.Material),
        new MySqlParameter("drawing", this.geometry.Drawing),
        new MySqlParameter("profile", this.geometry.Profile.Name),
        new MySqlParameter("groupeProfil", this.geometry.GroupeProfil),
        new MySqlParameter("length", this.geometry.Length),
        new MySqlParameter("weightNet", this.geometry.WeightNet),
        new MySqlParameter("revision", this.geometry.Revision),
        new MySqlParameter("principal", this.principal),
        new MySqlParameter("unloadingZone", this.unloadingZone),
        new MySqlParameter("executionClass", this.executionClass),
        new MySqlParameter("category", this.category.ID),
        new MySqlParameter("description", this.description),
        new MySqlParameter("workingOrder", this.workingOrder),
        new MySqlParameter("isMountingPart", this.isMountingPart),
        new MySqlParameter("isPRS", this.isPRS),
        new MySqlParameter("idPRS", this.idPRS),
        new MySqlParameter("importOk",this.geometry.ImportOk),
    };
    return listParams;
}

What I would like is using the solution there, but not having to type again all parameters (as I would like to do it on several objects, and lot of parameters).

Here is what I have for now :

private void insertListReperes(List<Repere> listReperes)
{
    if (this.OpenConnection() == true)
    {
        using (this.connection)
        {
            string query = "INSERT INTO [vsteel].detail (ID_CONTRACT,NAME_CONTRACT,NAME_PHASE,NAME_ASS,ID_PHASE,ID_ASS,NAME,NAME_ORI,PRIORITY,QTE,QTE_TOT,OP," +
            "MATERIAL,DRAWING,PROFILE,GROUP_PROFILE,LENGTH,WEIGHT,REVISION,PRINCIPAL,UNLOADING_ZONE,EXECUTION_CLASS,ID_CATEGORY,DESCRIPTION,WORKING_ORDER," +
            "IS_MOUNTING_PART,IS_PRS,ID_PRS,IMPORT_OK) " +
            "VALUES (@idContract,@contractCode,@phaseName,@assemblyName,@idPhase,@idAss,@name,@name,@priority,@quantity,@totalQuantity,@listOperations," +
            "@material,@drawing,@profile,@groupeProfil,@length,@weightNet,@revision,@principal,@unloadingZone,@executionClass,@category,@description,@workingOrder," +
            "@isMountingPart,@isPRS,@idPRS,@importOk)";
            using (MySqlCommand cmd = new MySqlCommand(query.Replace("[vsteel].", ""), connection))
            {
                for(int i=0;i<listReperes.Count();i++)
                {
                    Repere repere = listReperes[i];
                    if(i==0)
                    {
                        MySqlParameter[] listParams = repere.GetListMySqlParams();
                        for (int j = 0; j < listParams.Count(); j++)
                        {
                            cmd.Parameters.Add(listParams[i]);
                        }
                        cmd.Prepare();
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        MySqlParameter[] listParams = repere.GetListMySqlParams();
                        for (int j = 0; j < listParams.Count(); j++)
                        {
                            cmd.Parameters[listParams[i].ParameterName].Value = listParams[i].Value;
                        }
                        cmd.ExecuteNonQuery();
                    }
                    repere.ID = cmd.LastInsertedId;
                }
            }
        }
    }
}

But when I try to set parameter at line cmd.Parameters[listParams[i].ParameterName].Value = listParams[i].Value; it gives me the error that parameter is already defined. Is there a way to do it without copying all parameters names?

Answer 1

So, I found the problem. The error isn't being thrown on the line you said it was; it's on the cmd.Parameters.Add line, which makes more sense (the source code only ever throws a a"already existing" during an Add)

You've written:

cmd.Parameters.Add(listParams[i]);

You should have written:

cmd.Parameters.Add(listParams[j]);

By using [i] you repeatedly add the parameter for idContact because i is always 0 on every pass of the j loop

---

Note that you've made the same mistake on the lower line too, so you repeatedly set the value for idContact 21 times

---

By the way, I mentioned a library called Dapper (stackoverflow uses it for all its data access) that can make life simpler for you. If you write a code like:

using var connection = ... //get your connection here
var sql = "INSERT ... VALUES(@parameter, @names, @identical, @to, @your, @object, @properties); SELECT LAST_INSERT_IDENTITY()";
foreach(var v in itemstoinsert)
  v.id = connection.ExecuteScalar<int>(sql, v);

It should be all you need..