CODEWITHSUNDEEP
c#sqlloopsparameters
user13657
user13657

Reputation: 765

Sql - set the same parameter value in loop

I need to declare parameter once and then use it several times with Insert query like:

SqlCommand mainCMD = new SqlCommand("", conn);
for (int i = 0; i < 5; i++)
{
    mainCMD.CommandText += "INSERT INTO Country (name) VALUES (@cntr)";
    mainCMD.Parameters.AddWithValue("@cntr", "Country" + i);
}
mainCMD.ExecuteNonQuery();

How can i do it?

Upvotes: 0

Views: 3081

Answers (3)

user3156699
user3156699

Reputation:

Just use mainCMD.Parameters.clear() at the end of the loop

SqlCommand mainCMD = new SqlCommand("", conn);
for (int i = 0; i < 5; i++)
{
    mainCMD.CommandText += "INSERT INTO Country (name) VALUES (@cntr)";
    mainCMD.Parameters.AddWithValue("@cntr", "Country" + i);
    mainCMD.ExecuteNonQuery();
    mainCMD.Parameters.clear();

}

Upvotes: 2

DRapp
DRapp

Reputation: 48139

Build your command and parameter ONCE, then just change the parameter value IN the loop

SqlCommand mainCMD = new SqlCommand( " INSERT INTO Country (name) VALUES (@parmCountry)", conn);
// just to prime the parameter with proper data type string expectation
mainCMD.Parameters.AddWithValue("@parmCountry", "test country");
for (int i = 0; i < 5; i++)
{
   // change ONLY the parameter, then execute it
   mainCMD.Parameters[0].Value = "Country" + i.ToString();
   mainCMD.ExecuteNonQuery();
}

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236228

You should change name of parameter 

SqlCommand mainCMD = new SqlCommand("", conn);
for (int i = 0; i < 5; i++)
{
   mainCMD.CommandText += 
       String.Format(" INSERT INTO Country (name) VALUES (@cntr{0})", i);
   mainCMD.Parameters.AddWithValue("@cntr" + i, "Country" + i);
}

Also it will be more efficient to build command text separately:

int countriesCount = 5;

StringBuilder builder = new StringBuilder();      
for(int i = 0; i < countriesCount; i++)
    builder.AppendFormat("INSERT INTO Country (name) VALUES (@cntr{0}) ", i);

var cmd = new SqlCommand(builder.ToString(), conn);
for (int i = 0; i < countriesCount; i++)
    cmd.Parameters.AddWithValue("@cntr" + i, "Country" + i);

Upvotes: 3

Related Questions