0

I am trying to query the MySQL database from a c# application. Below is the code , here I am using parameterized query

 public static void ValidateName(MySqlConnection conn,List<Employee> EmpList, string Group)
 {
   string selectQuery = "Select Name from Employee where Group = @Group  AND @Name in (FirstName, LastName);";
   using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
    {
     for (int i = 0; i < EmpList.Count; i++)
      {
        cmd.Parameters.Add("@Group", MySqlDbType.VarChar).Value = Group;
        cmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value = EmpList[i].Name;
        var reader = cmd.ExecuteReader();
        List<string> lineList = new List<string>();
        while (reader.Read())
        {
            lineList.Add(reader.GetString(0));
        }
        if (lineList.Count <=0)
        {
           WriteValidationFailure(EmpList[i], "Failed");
        }
}       
}

But the above code is throwing error in below line saying 

 cmd.Parameters.Add("@Group", MySqlDbType.VarChar).Value = Group;

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll' @Group has already been defined

M. Adeel Khalid
  • 1,786
  • 2
  • 21
  • 24
xyz
  • 531
  • 1
  • 10
  • 31

1 Answers1

2

This is happening because you are adding the same set of parameters in each iterations. You can either clear then in each iteration or else add them before starting the loop and change the value of existing parameter during each iteration. I think second option would be great. One more thing I have to specify here is about the reader, you have to use reader as an using variable so that each time it will get disposed at the end of the using block and your code works fine. Which means you can try something like this:

using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
{
    cmd.Parameters.Add(new MySqlParameter("@Group", MySqlDbType.VarChar));
    cmd.Parameters.Add(new MySqlParameter("@Name", MySqlDbType.VarChar));
    for (int i = 0; i < EmpList.Count; i++)
    {
        cmd.Parameters["Group"].Value = group;
        cmd.Parameters["Name"].Value = EmpList[i].Name;
        // rest of code here
       using (MySqlDataReader reader = cmd.ExecuteReader())
       {
           // Process reader operations 
       }

    }
}
sujith karivelil
  • 28,671
  • 6
  • 55
  • 88
  • Do I need to add cmd.Prepare(); before every cmd.Pramaters.value does it improve the performance of the code – xyz Mar 14 '17 at 04:58
  • Can you please suggest me if using prepare statements will enhance the performance – xyz Mar 14 '17 at 05:06
  • @xyz: Hope that [this thread](http://stackoverflow.com/questions/20972375/how-to-correctly-and-efficiently-reuse-a-prepared-statement-in-c-sharp-net-sql) can explain it well – sujith karivelil Mar 14 '17 at 05:09