C# ado.net stored procedure with recompile hint-CodePudding

Is there a way to add OPTION (RECOMPILE) in C# while executing stored procedure by System.Data.SqlClient?

What I'm looking for in my imagination would be something like

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("usp_xyz OPTION (RECOMPILE)", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("x", x);
        cmd.ExecuteNonQuery();
    }
}

CodePudding user response：

Yes, you can use the EXEC... WITH RECOMPILE syntax, but you must do it as an ad-hoc batch, and therefore specify all parameters. You cannot use this with CommandType.StoredProcedure.

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("EXEC usp_xyz @x = @x WITH RECOMPILE;", sqlConn))
    {
        cmd.Parameters.Add("@x", SqlDbType.SomeType, SomeLength).Value = x;
        cmd.ExecuteNonQuery();
    }
}

If you want, you could use sp_recompile, but this has different semantics: it does not just generate a new plan for this run, it discards the old plan for all future runs of the procedure.

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("sp_recompile", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@objname", SqlDbType.NVarChar, 776).Value = "dbo.usp_xyz";
        cmd.ExecuteNonQuery();
    }

    using (SqlCommand cmd = new SqlCommand("usp_xyz", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@x", SqlDbType.SomeType, SomeLength).Value = x;
        cmd.ExecuteNonQuery();
    }
}