CODEWITHSUNDEEP
sqlstored-procedurestransactionsc#-3.0
nimaSadeghpour
nimaSadeghpour

Reputation: 39

Sending parameters to a stored procedure

Where is the problem in my code?

I use a stored procedure and transaction.

For one parameter to be working properly, but when the number of parameters is more than one error occurs.

Where is my problem?

This is my code in C#

internal static bool ExecuteNonQueryTransaction(string CommandName, CommandType cmdType, SqlParameter[][] pars)
    {
        int result = 0;
        SqlTransaction tr = null;
        int h = pars.GetLength(0);


        using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }


            try
            {
                tr = con.BeginTransaction();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.Transaction = tr;
                    cmd.CommandText = CommandName;

                    // cmd.Parameters.AddRange(pars);
                    for (int i = 0; i < pars.GetLength(0); i++)
                    {
                        cmd.Parameters.AddRange(pars[i]);
          cmd.ExecuteNonQuery();
                    }

                    tr.Commit();
                }
            }
            catch
            {
                if (tr != null)
                {
                    tr.Rollback();
                }
                //return false;
            }

        }
        return (result > 0);
    }

and this my stored procedure

ALTER PROCEDURE dbo.AddNewUserTypePageAccess
(@id_user_type int,
 @id_page_access int)
as
    insert into user_type_page_access(id_user_type, id_page_access)
    values(@id_user_type, @id_page_access) 
    return

Thank you for your help.....

Upvotes: 1

Views: 139

Answers (1)

marc_s
marc_s

Reputation: 754278

You shouldn't call ExecuteNonQuery(); inside the loop that adds the parameters! Add all parameters, and then call ExecuteNonQuery(); once, with all the parameters in place.

Use this code:

using (SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandType = cmdType;
    cmd.Transaction = tr;
    cmd.CommandText = CommandName;

    // cmd.Parameters.AddRange(pars);
    for (int i = 0; i < pars.GetLength(0); i++)
    {
        cmd.Parameters.AddRange(pars[i]);
    }

    // call ExecuteNonQuery only AFTER you've added all the parameters!
    cmd.ExecuteNonQuery();

    tr.Commit();
}

Upvotes: 1

Related Questions