0

I like to minimize the scheduling of tasks that wait on eachother but not show it's one How would I alter my TestOption1 to return a Task the the calling method?

Task continuations

    [TestClass()]
    public class SqlServerTests
    {
        public const string Membership = "Data Source=LocalHost;Initial Catalog=tempdb;Integrated Security=True;";

        [TestMethod()]
        public async Task ContinueWithTest()
        {
            using CancellationTokenSource cts = new CancellationTokenSource();
            //warm up so pooling is enabled on all 3 methods
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                con.Open();
                cmd.ExecuteNonQuery();
            }

            var sw2 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption2(cts.Token).ConfigureAwait(false);
            sw2.Stop();

            //allow the benefit of the doubt for the slower and give it cashed plans
            var sw3 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption3(cts.Token).ConfigureAwait(false);
            sw3.Stop();

            Assert.IsTrue(sw2.ElapsedTicks < sw3.ElapsedTicks, "Stopwatch 2 {0} Stopwatch 3 {1}", sw2, sw3);


            var sw1 = System.Diagnostics.Stopwatch.StartNew();
            await TestOption1(cts.Token).ConfigureAwait(false);
            sw1.Stop();
            Console.WriteLine($"TestOption1: No internal awaits {sw1.ElapsedTicks:N0} ticks");
            Console.WriteLine($"TestOption2: 1x internal await {sw2.ElapsedTicks:N0} ticks");
            Console.WriteLine($"TestOption3: 2x internal await {sw3.ElapsedTicks:N0} ticks");

            Assert.IsTrue(sw1.ElapsedTicks < sw2.ElapsedTicks, "Stopwatch 1 {0} Stopwatch 2 {1}", sw1, sw2);
            Assert.IsTrue(sw1.ElapsedTicks < sw3.ElapsedTicks, "Stopwatch 1 {0} Stopwatch 3 {1}", sw1, sw3);
        }

        private static Task TestOption1(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith((t) => cmd.ExecuteNonQuery()
                                        , cancellationToken
                                        , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                                        , scheduler: TaskScheduler.Default);
            }
        }

        private static async Task TestOption2(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                await con.OpenAsync(cancellationToken)
                      .ContinueWith((_) => cmd.ExecuteNonQuery(), cancellationToken).ConfigureAwait(false);
            }
        }

        private static async Task TestOption3(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "set nocount on";

                await con.OpenAsync(cancellationToken).ConfigureAwait(false);
                await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
            }
        }
    }

I would like to be able to do something like this

    [TestMethod]
    public async Task TestContinueWithDelegate()
    {
        var data = await TestOptionReturn().ConfigureAwait(false);
        Assert.IsNotNull(data);
    }

    private static Task<object> TestOptionReturn(CancellationToken cancellationToken = default)
    {
        using (var con = new SqlConnection(Membership))
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "Test1";

            return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                  .ContinueWith(delegate { return cmd.ExecuteScalar(); }
                                    , cancellationToken
                                    , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                                    , scheduler: TaskScheduler.Default);
        }
    }

The following tests fail as the database doesn't open enter image description here

[TestMethod] public async Task TestContinueWithDelegate() { using CancellationTokenSource cts = new CancellationTokenSource(); var data = await TestOptioDDL(cts.Token).ConfigureAwait(false); using var reader = await TestOptionOutput(cts.Token).ConfigureAwait(false); Assert.IsNotNull(data); Assert.IsTrue(reader.Read()); }

        private static Task<object> TestOptioDDL(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "TestOutput";
                cmd.Parameters.Add(new SqlParameter("Data", System.Data.SqlDbType.DateTime) { IsNullable = true, Direction = System.Data.ParameterDirection.Output });
                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith(delegate
                      {
                          cmd.ExecuteScalar();
                          return cmd.Parameters[0].Value;
                      }
                      , cancellationToken
                      , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                      , scheduler: TaskScheduler.Default);
            }
        }

        private static Task<SqlDataReader> TestOptionOutput(CancellationToken cancellationToken = default)
        {
            using (var con = new SqlConnection(Membership))
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select * from sys.databases";
                cmd.Parameters.Add(new SqlParameter("Data", System.Data.SqlDbType.DateTime) { IsNullable = true, Direction = System.Data.ParameterDirection.Output });
                return con.OpenAsync(cancellationToken)//fails as it does not wait for the db to open....
                      .ContinueWith(delegate
                      {
                          return cmd.ExecuteReader();
                      }
                      , cancellationToken
                      , continuationOptions: TaskContinuationOptions.ExecuteSynchronously
                      , scheduler: TaskScheduler.Default);
            }
        }
3
  • 1
    Can you describe more about what exactly do you want to do? Commented Apr 9, 2020 at 10:13
  • If you are already sure that your connection works in synchronous mode, try this: var t1=con.OpenAsync(cancellationToken); t1.ContinueWith(delegate { return cmd.ExecuteReader(); } , cancellationToken , continuationOptions: TaskContinuationOptions.ExecuteSynchronously , scheduler: TaskScheduler.Default); return t1; does this solve your problem? Commented Apr 9, 2020 at 12:00
  • @SinaHoseinkhani, nope, same error Commented Apr 9, 2020 at 12:45

1 Answer 1

3

TestOption3 is the best choice. Optimize for maintainability, not for saving a few milliseconds on what will be a highly I/O-bound task.

That said, your connection is closed because your connection is being disposed before the tasks complete (possibly before it's even opened!). If you're going to remove async and await, then you need to handle rewriting your method so that the disposal at the end of the using will be run after the tasks complete.

Sign up to request clarification or add additional context in comments.

3 Comments

@Stehen could you blog a sample, I'm sure there are quite a few developers looking for this. most of my tasks are quite fast and the async switching becomes an issue specially when the load on the sever get's larger
I'm curious to know how you concluded it was the async switching that was the problem? Note that your existing test timings aren't valid; TestOption1 isn't actually running the command since the connection is closed.
the test 1-3 are over simplified so that all could try to execute it, when running 1 production code we use option 1 when we do insert, update and deletes and do not need a transaction, lot's of logging of non- critical data, change this to something slower and it really hurts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.