foreach loop does loop, then get error Procedure or function spTestSp has too many arguments specified

Question

I cannot work out what I'm doing wrong on this foreach loop, it insert 1st set of records, then return error; Procedure or function spTestSp has too many arguments specified.

Can anyone point me in the right direction

List<Jobs> jobDetails = new List<Jobs>
{
    new Jobs {JobDate = "Job3",JobRrf = "ref3"},
    new Jobs {JobDate = "Job4",JobRrf = "REf4"},
    new Jobs {JobDate = "Job5",JobRrf = "ref5"},
    new Jobs {JobDate = "Job6",JobRrf = "REf6"},
};

const string spName = "dbo.spTestSp";

using (var cn = new SqlConnection(_dbConn))
{
    using (var cmd = new SqlCommand(spName, cn))
    {
        //cn.Open();
        foreach (var data in jobDetails)
        {

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@param1", data.JobDate);
            cmd.Parameters.AddWithValue("@param2", data.JobRrf);
            //cmd.Parameters.AddWithValue("@param2", data.JobRrf);
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //cn.Close();
    }
}

alter PROCEDURE spTestSp
    -- Add the parameters for the stored procedure here
    @param1 nvarchar(50),
    @param2 nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    insert into [dbo].[tblTest]([test1],[test2])values(@param1,@param2)

END

Answer 1

Personally I can't see any advantages of creating only one SqlCommand object, and reuse it in foreach block. As you can clear it's parameters, it's easier to recreate the whole command, like this:

cn.Open();
foreach (var data in jobDetails)
{
    using (var cmd = new SqlCommand(spName, cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@param1", data.JobDate);
        cmd.Parameters.AddWithValue("@param2", data.JobRrf);
        //cmd.Parameters.AddWithValue("@param2", data.JobRrf);
        cmd.ExecuteNonQuery();
    }
}
//cn.Close();

In your code you are signalling the server to close connection and move it to the connection pool after each command, which isn't very efficient. Open it once and send your data to the server. Also make note that using block for the SqlConnection object will close it automatically, so you don't have to make it manually.

As for your error, it occurs simply because of the too many parameters you have in your command after one loop cycle.