Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to execute stored procedure multiple times in C#

Tags:

c#

procedures

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.

like image 567
dam Avatar asked Sep 13 '11 00:09

dam


People also ask

Can a procedure be executed more than once?

A procedure cannot be executed more than once. 4. ALOGO procedure has two parts.

Can a stored procedure return multiple tables?

They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

Is it possible to send multiple parameters in procedures?

Setting up multiple parameters is very easy to do. You just need to list each parameter and the data type separated by a comma as shown below.

How to query a stored procedure?

Click on your database and expand “Programmability” and right click on “Stored Procedures” or press CTRL+N to get new query window. You can write the SELECT query in between BEGIN and END to get select records from the table.


2 Answers

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.)

like image 174
Kyle W Avatar answered Sep 28 '22 01:09

Kyle W


Another alternative, you could change the scope of the SqlCommand so that it is recreated each 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();

Seems a bit wasteful, but there are some libraries that work this way (the Enterprise Library DAAB comes to mind).

like image 22
mgnoonan Avatar answered Sep 28 '22 00:09

mgnoonan