C# 执行存储过程的一些经验

来源：互联网发布：aaa云php云主机编辑：程序博客网时间：2024/05/17 20:13

问题：

如果你的参数添加是在循环体内完成的，
第2次时就会重复添加，此时触发参数过多异常

解决方法1，参数在循环体外添加，for内部只赋值和取返回值
解决方法2，每次循环清空参数Parameters.Clear();

====================================================

在c#执行存储过程的步骤：

int procResult = 0;
SqlCommand command = new SqlCommand("proc_Cc", DBHelper.connection);//创建Command对象，调用存储过程proc_Cc
command.CommandType = CommandType.StoredProcedure;//指定Command命令执行类型
SqlParameter sp_1 = null;//存储过程的输入参数
SqlParameter sp_2 = null;
SqlParameter sp_3 = null;
SqlParameter sp_4 = null;
for (int i = 0; i < dic.Count; i++)
{
try
{
DBHelper.connection.Open();
sp_1 = new SqlParameter("@id", SqlDbType.VarChar, 10);//存储过程的输入参数
sp_2 = new SqlParameter("@name", SqlDbType.VarChar, 20);
sp_3 = new SqlParameter("@price", SqlDbType.Money);
sp_4 = new SqlParameter("@count", SqlDbType.Int);
sp_1.Value = id;//给存储过程的参数赋值
sp_2.Value = dic[i].CookName;
sp_3.Value = dic[i].Price;
sp_4.Value = dic[i].Count;
command.Parameters.AddRange(new SqlParameter[] { sp_1, sp_2, sp_3, sp_4 });
procResult = command.ExecuteNonQuery();//获得受影响行数
command.Parameters.Clear();//注意：如果不清空，则第2次循环就会重复添加，此时触发参数过多异常！
if (procResult == 0)
{
MessageBox.Show("存储过程失败！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { DBHelper.connection.Close(); }
}

C#接收存储过程返回值:

public static int User_Add(User us)
     {
         int iRet;
         SqlConnection conn = new SqlConnection(Conn_Str);
         SqlCommand cmd = new SqlCommand("User_Add", conn);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@UName", us.UName);
         cmd.Parameters.AddWithValue("@UPass", us.UPass);
         cmd.Parameters.AddWithValue("@PassQuestion", us.PassQuestion);
         cmd.Parameters.AddWithValue("@PassKey", us.PassKey);
         cmd.Parameters.AddWithValue("@Email", us.Email);
         cmd.Parameters.AddWithValue("@RName", us.RName);
         cmd.Parameters.AddWithValue("@Area", us.Area);
         cmd.Parameters.AddWithValue("@Address", us.Address);
         cmd.Parameters.AddWithValue("@ZipCodes", us.ZipCodes);
         cmd.Parameters.AddWithValue("@Phone", us.Phone);
         cmd.Parameters.AddWithValue("@QQ", us.QQ);
         cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
         try
         {
             conn.Open();
             cmd.ExecuteNonQuery();
             iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
         }
         catch (SqlException ex)
         {
             throw ex;
         }
         finally
         {
             conn.Close();
         }
         return iRet;
     }

C#接收存储过程输出参数:

public static decimal Cart_UserAmount(int UID)
    {
        decimal iRet;
        SqlConnection conn = new SqlConnection(Conn_Str);
        SqlCommand cmd = new SqlCommand("Cart_UserAmount", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UID", UID);
        cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Direction=ParameterDirection.Output;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            iRet = (decimal)cmd.Parameters["@Amount"].Value;
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
        return iRet;
    }