如何在Ｃ＃中实现带多个参数的存储过程

Question

private string Execute_Test0()
{
    string rtn = "";    SqlConnection db = null;
    SqlCommand cmd = null;    try
    {
        string sql = "Test0";
        db = getConnection();        cmd = new SqlCommand(sql,db);
        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.Clear();        //返回值
        //cmd.Parameters.Add("@RETURN_VALUE",SqlDbType.Int);
        //cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;        cmd.Parameters.Add("@v1",SqlDbType.Int);
        cmd.Parameters["@v1"].Direction = ParameterDirection.Input;
        cmd.Parameters["@v1"].Value = ;//你的值        cmd.Parameters.Add("@v2",SqlDbType.VarChar,50);
        cmd.Parameters["@v2"].Direction = ParameterDirection.Input;
        cmd.Parameters["@v2"].Value = ;//你的值
        db.Open();        cmd.ExecuteNonQuery();//非SELECT        rtn = "OK";
    }
    catch(Exception ex)
    {
        rtn = ex.Message;
    }
    finally
    {
        cmd.Dispose();
        if (db.State == ConnectionState.Open) db.Close();
        db.Dispose();
    }    return rtn;
}

Answer 1

或者你直接当一个语句来执行就好了
System.Data.SqlClient.SqlConnection conn = new SqlConnection(datpath);
conn.Open();
string sql = "存储过程名 参数";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.close();

Answer 2

这是我最近学写的一个C#调用存储过程的例子，也是传多个参数进去的：  定义要传进去的参数列表
{
        SqlParameter[] parms ={ new SqlParameter("@dtDate0", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate1", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate2", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate3", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate4", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate5", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate6", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate7", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate8", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate9", SqlDbType.DateTime),
                                 new SqlParameter("@dtDate10", SqlDbType.DateTime),
                                 new SqlParameter("@iMonth",SqlDbType.Int)};
        parms[0].Value = dTime;
        parms[1].Value = FirstDayOfMonth;
        parms[2].Value = LastDayOfMonth;
        parms[3].Value = FirstDayOfLMonth;
        parms[4].Value = LastDayOfLMonth;
        parms[5].Value = LFirstDayOfMonth;
        parms[6].Value = LLastDayOfMonth;
        parms[7].Value = FirstDayOfTYear;
        parms[8].Value = LastDayOfTYear;
        parms[9].Value = FirstDayOfLYear;
        parms[10].Value = LastDayOfLYear;
        parms[11].Value = dTime.Month;
        //执行存储过程
        if(parameter == 1)
          DbHelperSQL.RunProcedure("qry_CustMonBusHz", parms);
} 调用存储过程的原函数
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            Connection.Close();     
            return returnReader;}

Answer 3

this.connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.connection;//自己定义con
cmd.CommandText = "Proc_WebsiteData_Insert";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para = new SqlParameter[2];
para[0] = new SqlParameter("loginuser", SqlDbType.VarChar);
para[0].Value = "独孤飘云";
para[1] = new SqlParameter("password",SqlDbType.VarChar);
para[1].Value = "dugupiaoyun";
cmd.Parameters.AddRange(para);
cmd.ExecuteNonQuery();
this.connection.Close();

Answer 4

SqlCommand .Parameters方法