How to execute a stored procedure several times in C #

Question

I have a schedule application in which users enter their time in / out for different days of the week. The form processes the input / output from each day, fills them as parameters into the stored procedure and adds them to the database. How could I do this most efficiently? I do not have access to the database, but only stored procedures.

This is clean code, I deleted some unnecessary codes.

SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand("insertINOUT", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserName", user)); for (int j = 0; j < weekDays.Length; j++) { cmd.Parameters.Add(new SqlParameter("@In", in)); cmd.Parameters.Add(new SqlParameter("@Out", out)); cmd.ExecuteReader(); } conn.Close(); 

The code works if there is only one day of entry / exit. If users fill out several days, I will get this error: The parameter "@In" was sent several times.

Thank you for your help.

Answer 1

 SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand("insertINOUT", conn); cmd.CommandType = CommandType.StoredProcedure; for (int j = 0; j < weekDays.Length; j++) { **cmd.Parameters.Clear();** cmd.Parameters.Add(new SqlParameter("@UserName", user)); cmd.Parameters.Add(new SqlParameter("@In", in)); cmd.Parameters.Add(new SqlParameter("@Out", out)); cmd.ExecuteReader(); } conn.Close(); 

(You must clear the parameters of each iteration.)

Answer 2

Another alternative, you can change the scope of SqlCommand so that it is recreated every time.

 SqlConnection conn = new SqlConnection(connString); conn.Open(); for (int j = 0; j < weekDays.Length; j++) { SqlCommand cmd = new SqlCommand("insertINOUT", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserName", user)); cmd.Parameters.Add(new SqlParameter("@In", in)); cmd.Parameters.Add(new SqlParameter("@Out", out)); cmd.ExecuteReader(); } conn.Close(); 

It seems a bit wasteful, but there are some libraries that work this way (DAB Enterprise DAB comes to mind).

Answer 3

 using (SqlConnection conn ... ) { SqlCommand cmd = ... ... // Set up the parameter list. // You can use .AddWithValue here to add values that don't change in the loop. cmd.Parameters.Add("@Username", SqlDbType.VarChar); ... for (...) { // Load one set of loopy values. cmd.Parameters["@UserId"].Value = user; ... } } 

Answer 4

The reason you get this error is because the for loop re-adds the parameter several times:

 cmd.Parameters.Add(new SqlParameter("@In", in)); cmd.Parameters.Add(new SqlParameter("@Out", out)); 

The right way to do this is either to clear the collection of parameters in the last line of the foor loop, or simply check if this parameter exists and set its value instead of doing Parameters.Add

Answer 5

 SqlConnection conn = new SqlConnection(connString); conn.Open(); SqlCommand cmd = new SqlCommand("insertINOUT", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserName", user)); for (int j = 0; j < weekDays.Length; j++) { cmd.Parameters.Add(new SqlParameter("@In"+j, in)); cmd.Parameters.Add(new SqlParameter("@Out"+j, out)); cmd.ExecuteReader(); } conn.Close();