Grab SQL variables that changes in query in ADO.NET

How do I get variable @Test that was changed in query?

        const string query = @"SET @Test = 2;";

        using (var connection = new SqlConnection(conStr))
        {
            connection.Open();
            var command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("@Test", 1);
            var r = command.ExecuteReader();
            // command.Parameters["@Test"].Value == 1
            // r hasn't any variables                
        }

ADDED: I've solve this problem withoute creating stored procedure

        const string query = @"SET @Test = 2;";

        using (var connection = new SqlConnection(conStr))
        {
            connection.Open();
            var command = new SqlCommand(query, connection);

            SqlParameter par = command.Parameters.Add("@Test", SqlDbType.NVarChar, 15);
            par.Direction = ParameterDirection.Out开发者_如何学Goput;

            command.ExecuteNonQuery();
            // par.Value now contain 2
        }

Both ansvers help!

Firstly, in your stored procedure the parameter needs to be marked as OUTPUT

CREATE PROC MyQuery
    @Test INT OUTPUT
AS
SET @Test = 2

Then, when constructing the c# code, instead of using AddWithValue, be more explicit in your creation of a SqlParameter, namely marking it as Input/Output.

var command = new SqlCommand("MyQuery", connection);
command.CommandType = CommandType.StoredProcedure;

var param = command.CreateParameter();
param.Name = "@Test";
param.Type = DbType.Int;
param.Direction = ParameterDirection.InputOutput;
param.Value = 1;

Now once you execute your command:

command.ExecuteNonQuery(); // Could also be ExecuteReader, if you have a resultset too!

You can read the value of param, which should have changed to 2

if(param.Value == 2)
{ Console.WriteLine("WooHoo"); }