can i reuse a C# SqlParameter

Question

i am doing a continuously select every 10seconds, so i thought i would do some premature optimsing and save creating a cmd and paramater objects in everyloop

if i do this in one method

public void FirstSelect() {

    // select data
    this.cmdSelectData = new SqlCommand(SQL_SELECT_DATA, conn);

    this.paramBranchId = new SqlParameter("@branch_id", 1);
    this.cmdSelectData.Parameters.Add(paramBranchId);

    // fetch data blah, blah, blah...
}

and then this in another method

public void SecondSelect() {

    this.paramBranchId.Value = 2;
   // fetch data
}

would that work as expected, one select using branch 1, one select using branch 2 or do i need to

this.cmdSelectData.Parameters.Clear();
ths.cmdSelectData.Parameters.Add(new SqlParameter( // for branch 2)

}

Answer 1

You need to create a new Parameter for each one you add.

Answer 2

Yes, setting the Value on an existing parameter does affect subsequent executions of the command. It's quite handy if you want to execute the same command a few times with different values for one or more parameters, without having to rebuild the whole thing each time.