CODEWITHSUNDEEP
c#sqlstored-proceduresreturnparameter-passing
don
don

Reputation: 597

C#: Add Parameters to stored procedure inside a loop

I have been trying to add parameters to a stored procedure inside a loop. Below given is the code where I declared my variables.

 SqlConnection con = new SqlConnection();
        Connect conn = new Connect();
        SqlDataReader readerCourseID = null;
        con = conn.getConnected();
        con.Open();
        SqlCommand cmdAssignCourse;
        cmdAssignCourse = new SqlCommand("assignCourse", con);
        cmdAssignCourse.CommandType = CommandType.StoredProcedure;
        cmdAssignCourse.Parameters.Add("@sID", System.Data.SqlDbType.VarChar);
        cmdAssignCourse.Parameters.Add("@cID", System.Data.SqlDbType.VarChar);
        SqlParameter retValue = cmdAssignCourse.Parameters.Add("return", System.Data.SqlDbType.Int);

And below is the code where I inserted values into the variables that were declared earlier.

foreach (DataRow row in dt.Rows)
            {
                //get course id from course name. Pass  row["Course Name"].ToString()
                int i = getCourseID(row["Course Name"].ToString());



                //assignment of the course to student
                cmdAssignCourse.Parameters["@sID"].Value = studentCurrID.Value.ToString();
                cmdAssignCourse.Parameters["@cID"].Value = i;
                retValue.Direction = ParameterDirection.ReturnValue;
                cmdAssignCourse.ExecuteNonQuery();
                if (retValue.Value.ToString() == "0")
                {
                    MessageBox.Show("Added Course Successfully!");
                    //return 0;
                }
                else
                {
                    MessageBox.Show("An error occured! Possibly a duplication of data!");
                    //return -1;
                }

            }

However this code runs successfully and displays the message "Added Course Successfully!" once. But after the first successful run every other run it gives me "An error occured! Possibly a duplication of data!" message. The possible error is not clearing out the variables. How to clear the below variables. Please help me with this. Thanks!

Upvotes: 0

Views: 2395

Answers (1)

GarethD
GarethD

Reputation: 69829

You are not gaining anything by re-using the same SqlCommand and SqlConnection. Connection pooling will do all the hard work for you, there is no need to reinvent the wheel. It would be more legible and more robust to separate out your code, so create a new method to execute the procedure:

private int GenerateReturnValue(int courseID, int studentID)
{
    using (var connection = new SqlConnection("Your Connection String"))
    using (var command = new SqlCommand("assingCourse", connection)
    {
        connection.Open();
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@sID", System.Data.SqlDbType.VarChar).Value = studentID.ToString();
        command.Parameters.Add("@cID", System.Data.SqlDbType.VarChar).Value = courseID.ToString();
        command.Parameters.Add("@Return", System.Data.SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
        command.ExecuteNonQuery();

        return (int)command.Parameters["@Return"].Value;
    }
}

Then just call the method in your loop.

foreach (DataRow row in dt.Rows)
{
    int i = GenerateReturnValue(getCourseID(row["Course Name"].ToString()), studentCurrID.Value);
    if (i = 0)
    {
        MessageBox.Show("Added Course Successfully!");
        //return 0;
    }
    else
    {
        MessageBox.Show("An error occured! Possibly a duplication of data!");
        //return -1;
    }
}

In addition I think James was correct in saying that the problem lies in the fact that you never re-pull the return value from the query, you are missing that line after execution:

Upvotes: 1

Related Questions