dam dam - 11 months ago 33
C# Question

How to execute stored procedure multiple times in C#

I have a time sheet app where users enter their time in/out for different days of the week. The form processes the in/out from each day, stuff them as parameters into a stored procedure and add them to the database. How would I accomplish this most efficiently? I don't have access to the DB, just the stored procedures.

This is the bare code behind, I've stripped out 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's only 1 day of in/out. If the users fill out multiple days, I'll get this error: Parameter '@In' was supplied multiple times.

Thanks for your help.

Answer Source
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 have to clear the parameters each iteration.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download