2

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?

Improve this question
17
  • How about just Clear()ing the parameter collection and then adding all your new ones with AddRange(listParams) ? Commented Dec 15, 2021 at 18:36
  • Incidentally, if you used Dapper your entire insertListReperes code would reduce to connection.Execute(query, repere) plus the lines that set up the query and the connection - you wouldnt even need the getListMySqlParams method; Dapper will do that bit - just feed it an SQL full of @parameterNames that match your property names (you seem to have this) and pass it a Repere instance in the parameters argument Commented Dec 15, 2021 at 18:37
  • I recommend not using case alone to distinguish method names. Also recommend not using camelCase for properties; use PascalCase please Commented Dec 15, 2021 at 18:39
  • 1
    I would need to prepare everytime - I don't imagine so - dev.mysql.com/doc/connector-net/en/… creates parameters after prepare is called Commented Dec 15, 2021 at 19:10
  • 1
    PascalCaseStartsWithAnUpper, whereas camelCaseStartsWithALower - in general we name public things in C# with PascalCase, and local or private things (class fields) with camelCase.. As to case differentiation alone; it's not very obvious, and some languages in the CLR don't support case sens. Consuming a C# DLL with two pub methods differing on case alone would cause issues for VB, for example.. I appreciate that your methods are private, so it's only you who will be confused by them, but a "case alone" method might well be a stack overflow bug waiting to happen. Commented Dec 15, 2021 at 19:14

1 Answer 1

Reset to default
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..

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

2 Comments

yeah, you're right, sorry for mistake. Then I finally decided to do as you suggested (clear(),AddRange() everytime. the code is clearer than mine, so except if it would make earn time, I think it is better. Thanks
No problem. I also just added a bit about Dapper and how it can make life a bit easier!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.