Adding parameters to stored procedure in C# with AddWithValue method

Question

I'm fixing SQL Injections in the code. One of the method executes stored procedure and append parameters violating SQL Injections rules:

sqlCmd.CommandText = "updateSalesReps @repNumber='" + RepNumber + "', 
@ISONumber='" + ISONumber + "', @type='" + strUpdtType + "'";

Now, I'm adding parameters:

sqlCmd.Parameters.AddWithValue("@rNumber", RepNumber);
sqlCmd.Parameters.AddWithValue("@isoNumber", ISONumber);
sqlCmd.Parameters.AddWithValue("@updateType",strUpdtType);

This is my final query:

 sqlCmd.CommandText = "updateSalesReps @repNumber=@repNumber, 
 @ISONumber=@isoNumber, @type=@updateType";

My question is if it is OK to have ...@repNumber=@repNumber... in my stored procedure or it will create naming ambiguity?

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Aug 12, 2015 at 20:35

Elliot Rodriguez · Accepted Answer · 2015-08-12 20:17:23Z

2

If you specify the commandType and commandText properties on the command object you dont need to do that. Assuming you're using SQL Server stored procs, try:

   SqlCommand sqlCmd = new SqlCommand();
   sqlCmd.CommandTimeout = 15;
   sqlCmd.CommandType = CommandType.StoredProcedure;
   sqlCmd.CommandText = "updateSalesReps"

Then add to your parameters collection using AddWithValue.

Reference: CommandType in MSDN

When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

answered Aug 12, 2015 at 20:17

Elliot Rodriguez

6181 gold badge7 silver badges26 bronze badges

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

2 Comments

gene Over a year ago

Thank you. When too many people have worked on the same file, I see different techniques of doing the same thing to the point of frustration :)

AndersJH Over a year ago

Don't use AddWithValue, as the comment on the question says.

Collectives™ on Stack Overflow

Adding parameters to stored procedure in C# with AddWithValue method

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related