Asynchronous programming

Άρθρο
11/18/2022

Applies to: .NET Framework .NET .NET Standard

This article discusses support for asynchronous programming in the Microsoft SqlClient Data Provider for SQL Server (SqlClient).

Legacy asynchronous programming

The Microsoft SqlClient Data Provider for SQL Server includes methods from System.Data.SqlClient to maintain backwards compatibility for applications migrating to Microsoft.Data.SqlClient. It isn't recommended to use the following legacy asynchronous programming methods for new development:

Tip

In the Microsoft SqlClient Data Provider for SQL Server, these legacy methods no longer require Asynchronous Processing=true in the connection string.

Asynchronous programming features

These asynchronous programming features provide a simple technique to make code asynchronous.

For more information about asynchronous programming in .NET, see:

When your user interface is unresponsive or your server doesn't scale, it's likely you need your code to be more asynchronous. Writing asynchronous code has traditionally involved installing a callback (also called continuation) to express the logic that occurs after the asynchronous operation finishes. This style complicates the structure of asynchronous code as compared with synchronous code.

You can call into asynchronous methods without using callbacks, and without splitting your code across multiple methods or lambda expressions.

The async modifier specifies that a method is asynchronous. When calling an async method, a task is returned. When the await operator is applied to a task, the current method exits immediately. When the task finishes, execution resumes in the same method.

Tip

In the Microsoft SqlClient Data Provider for SQL Server, asynchronous calls are not required to set the Context Connection connection string keyword.

Calling an async method doesn't create extra threads. It may use the existing I/O completion thread briefly at the end.

The following methods in the Microsoft SqlClient Data Provider for SQL Server support asynchronous programming:

Other asynchronous members support SqlClient streaming support.

Tip

The asynchronous methods don't require Asynchronous Processing=true in the connection string. And this property is obsolete in the Microsoft SqlClient Data Provider for SQL Server.

Synchronous to asynchronous connection open

You can upgrade an existing application to use the asynchronous feature. For example, assume an application has a synchronous connection algorithm and blocks the UI thread every time it connects to the database. Once connected, the application calls a stored procedure that signals other users of the one who just signed in.

using System;
using System.Data;
using Microsoft.Data.SqlClient;

namespace SqlCommandCS
{
    class Program
    {
        static void Main()
        {
            string str = "Data Source=(local);Initial Catalog=Northwind;"
                + "Integrated Security=SSPI";
            string qs = "SELECT OrderID, CustomerID FROM dbo.Orders;";
            CreateCommand(qs, str);
        }
        private static void CreateCommand(string queryString,
            string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

When converted to use the asynchronous functionality, the program would look like:

using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

class A {
   public static void Main() 
   {
      using (SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=NorthWind; Integrated Security=SSPI"))
      {
         SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM dbo.Orders", conn);

         int result = A.Method(conn, command).Result;

         SqlDataReader reader = command.ExecuteReader();
         while (reader.Read())
            Console.WriteLine(reader[0]);
      }
   }

   static async Task<int> Method(SqlConnection conn, SqlCommand cmd) {
      await conn.OpenAsync();
      await cmd.ExecuteNonQueryAsync();
      return 1;
   }
}

Add the asynchronous feature in an existing application (mixing old and new patterns)

It's also possible to add asynchronous capability (SqlConnection::OpenAsync) without changing the existing asynchronous logic. For example, if an application currently uses:

AsyncCallback productList = new AsyncCallback(ProductList);
SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=NorthWind; Integrated Security=SSPI");
conn.Open();
SqlCommand cmd = new SqlCommand("select top 2 * from orders", conn);
IAsyncResult ia = cmd.BeginExecuteReader(productList, cmd);

You can begin to use the asynchronous pattern without substantially changing the existing algorithm.

using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

class A 
{
   static void ProductList(IAsyncResult result) { }

   public static void Main() 
   {
      // AsyncCallback productList = new AsyncCallback(ProductList);
      // SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=NorthWind; Integrated Security=SSPI");
      // conn.Open();
      // SqlCommand cmd = new SqlCommand("select top 2 * from orders", conn);
      // IAsyncResult ia = cmd.BeginExecuteReader(productList, cmd);

      AsyncCallback productList = new AsyncCallback(ProductList);
      SqlConnection conn = new SqlConnection("Data Source=(local); Initial Catalog=NorthWind; Integrated Security=SSPI");
      conn.OpenAsync().ContinueWith((task) => {
         SqlCommand cmd = new SqlCommand("select top 2 * from orders", conn);
         IAsyncResult ia = cmd.BeginExecuteReader(productList, cmd);
      }, TaskContinuationOptions.OnlyOnRanToCompletion);
   }
}

Use the base provider model and the asynchronous feature

You may need to create a tool that can connect to different databases and execute queries. You can use the base provider model and the asynchronous feature.

The Microsoft Distributed Transaction Controller (MSDTC) must be enabled on the server to use distributed transactions. For information on how to enable MSDTC, see How to Enable MSDTC on a Web Server.

using System.Data.Common;
using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

class program
{
   static async Task PerformDBOperationsUsingProviderModel(string connectionString)
   {
      using (DbConnection connection = SqlClientFactory.Instance.CreateConnection())
      {
            connection.ConnectionString = connectionString;
            await connection.OpenAsync();

            DbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM AUTHORS";

            using (DbDataReader reader = await command.ExecuteReaderAsync())
            {
               while (await reader.ReadAsync())
               {
                  for (int i = 0; i < reader.FieldCount; i++)
                  {
                        // Process each column as appropriate
                        object obj = await reader.GetFieldValueAsync<object>(i);
                        Console.WriteLine(obj);
                  }
               }
            }
      }
   }

   public static void Main()
   {
      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
      // replace these with your own values
      builder.DataSource = "localhost";
      builder.InitialCatalog = "pubs";
      builder.IntegratedSecurity = true;

      Task task = PerformDBOperationsUsingProviderModel(builder.ConnectionString);
      task.Wait();
   }
}

Use SQL transactions and the new asynchronous feature

using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

class Program 
{
   static void Main()
   {
      string connectionString =
          "Persist Security Info=False;Integrated Security=SSPI;database=Northwind;server=(local)";
      Task task = ExecuteSqlTransaction(connectionString);
      task.Wait();
   }

   static async Task ExecuteSqlTransaction(string connectionString)
   {
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
         await connection.OpenAsync();

         SqlCommand command = connection.CreateCommand();
         SqlTransaction transaction = null;

         // Start a local transaction.
         transaction = await Task.Run<SqlTransaction>(
             () => connection.BeginTransaction("SampleTransaction")
             );

         // Must assign both transaction object and connection
         // to Command object for a pending local transaction
         command.Connection = connection;
         command.Transaction = transaction;

         try {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (555, 'Description')";
            await command.ExecuteNonQueryAsync();

            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (556, 'Description')";
            await command.ExecuteNonQueryAsync();

            // Attempt to commit the transaction.
            await Task.Run(() => transaction.Commit());
            Console.WriteLine("Both records are written to database.");
         }
         catch (Exception ex)
         {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction.
            try
            {
               transaction.Rollback();
            }
            catch (Exception ex2)
            {
               // This catch block will handle any errors that may have occurred
               // on the server that would cause the rollback to fail, such as
               // a closed connection.
               Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
               Console.WriteLine("  Message: {0}", ex2.Message);
            }
         }
      }
   }
}

Use distributed transactions and the new asynchronous feature

In an enterprise application, you may need to add distributed transactions in some scenarios, to enable transactions between multiple database servers. You can use the System.Transactions namespace and enlist a distributed transaction, as follows:

using Microsoft.Data.SqlClient;
using System.Threading.Tasks;
using System.Transactions;

class Program
{
   public static void Main()
   {
      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
      // replace these with your own values
      // create two tables RegionTable1 and RegionTable2
      // and add a constraint in one of these tables 
      // to avoid duplicate RegionID
      builder.DataSource = "localhost";
      builder.InitialCatalog = "Northwind";
      builder.IntegratedSecurity = true;

      Task task = ExecuteDistributedTransaction(builder.ConnectionString, builder.ConnectionString);
      task.Wait();
   }

   static async Task ExecuteDistributedTransaction(string connectionString1, string connectionString2)
   {
      using (SqlConnection connection1 = new SqlConnection(connectionString1))
      using (SqlConnection connection2 = new SqlConnection(connectionString2))
      {
            using (CommittableTransaction transaction = new CommittableTransaction())
            {
               await connection1.OpenAsync();
               connection1.EnlistTransaction(transaction);

               await connection2.OpenAsync();
               connection2.EnlistTransaction(transaction);

               try
               {
                  SqlCommand command1 = connection1.CreateCommand();
                  command1.CommandText = "Insert into RegionTable1 (RegionID, RegionDescription) VALUES (100, 'Description')";
                  await command1.ExecuteNonQueryAsync();

                  SqlCommand command2 = connection2.CreateCommand();
                  command2.CommandText = "Insert into RegionTable2 (RegionID, RegionDescription) VALUES (100, 'Description')";
                  await command2.ExecuteNonQueryAsync();

                  transaction.Commit();
               }
               catch (Exception ex)
               {
                  Console.WriteLine("Exception Type: {0}", ex.GetType());
                  Console.WriteLine("  Message: {0}", ex.Message);

                  try
                  {
                        transaction.Rollback();
                  }
                  catch (Exception ex2)
                  {
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                  }
               }
            }
      }
   }
}

Cancel an asynchronous operation

You can cancel an asynchronous request by using the CancellationToken.

using Microsoft.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace Samples
{
   class CancellationSample
   {
      public static void Main(string[] args)
      {
            CancellationTokenSource source = new CancellationTokenSource();
            source.CancelAfter(2000); // give up after 2 seconds
            try
            {
               Task result = CancellingAsynchronousOperations(source.Token);
               result.Wait();
            }
            catch (AggregateException exception)
            {
               if (exception.InnerException is SqlException)
               {
                  Console.WriteLine("Operation canceled");
               }
               else
               {
                  throw;
               }
            }
      }

      static async Task CancellingAsynchronousOperations(CancellationToken cancellationToken)
      {
            using (SqlConnection connection = new SqlConnection("Server=(local);Integrated Security=true"))
            {
               await connection.OpenAsync(cancellationToken);

               SqlCommand command = new SqlCommand("WAITFOR DELAY '00:10:00'", connection);
               await command.ExecuteNonQueryAsync(cancellationToken);
            }
      }
   }
}

Asynchronous operations with SqlBulkCopy

Asynchronous capabilities are also in Microsoft.Data.SqlClient.SqlBulkCopy with SqlBulkCopy.WriteToServerAsync.

using System.Data;
using Microsoft.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace SqlBulkCopyAsyncCodeSample
{
    class Program
    {
        static string selectStatement = "SELECT * FROM [pubs].[dbo].[titles]";
        static string createDestTableStatement =
            @"CREATE TABLE {0} (
            [title_id] [varchar](6) NOT NULL,
            [title] [varchar](80) NOT NULL,
            [type] [char](12) NOT NULL,
            [pub_id] [char](4) NULL,
            [price] [money] NULL,
            [advance] [money] NULL,
            [royalty] [int] NULL,
            [ytd_sales] [int] NULL,
            [notes] [varchar](200) NULL,
            [pubdate] [datetime] NOT NULL)";

        // Replace the connection string if needed, for instance to connect to SQL Express: @"Server=(local)\SQLEXPRESS;Database=Demo;Integrated Security=true"
        // static string connectionString = @"Server=(localdb)\V11.0;Database=Demo";
        static string connectionString = @"Server=(local);Database=Demo;Integrated Security=true";

        // static string marsConnectionString = @"Server=(localdb)\V11.0;Database=Demo;MultipleActiveResultSets=true;";
        static string marsConnectionString = @"Server=(local);Database=Demo;MultipleActiveResultSets=true;Integrated Security=true";

        // Replace the Server name with your actual sql azure server name and User ID/Password
        static string azureConnectionString = @"Server=SqlAzure;User ID=<myUserID>;Password=<myPassword>;Database=Demo";

        static void Main(string[] args)
        {
            SynchronousSqlBulkCopy();
            AsyncSqlBulkCopy().Wait();
            MixSyncAsyncSqlBulkCopy().Wait();
            AsyncSqlBulkCopyNotifyAfter().Wait();
            AsyncSqlBulkCopyDataRows().Wait();
            AsyncSqlBulkCopySqlServerToSqlAzure().Wait();
            AsyncSqlBulkCopyCancel().Wait();
            AsyncSqlBulkCopyMARS().Wait();
        }

        // 3.1.1 Synchronous bulk copy in .NET 4.5
        private static void SynchronousSqlBulkCopy()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);

                    string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                    cmd.CommandText = string.Format(createDestTableStatement, temptable);
                    cmd.ExecuteNonQuery();

                    using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
                    {
                        bcp.DestinationTableName = temptable;
                        bcp.WriteToServer(dt);
                    }
                }
            }

        }

        // 3.1.2 Asynchronous bulk copy in .NET 4.5
        private static async Task AsyncSqlBulkCopy()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);

                    string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                    cmd.CommandText = string.Format(createDestTableStatement, temptable);
                    await cmd.ExecuteNonQueryAsync();

                    using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
                    {
                        bcp.DestinationTableName = temptable;
                        await bcp.WriteToServerAsync(dt);
                    }
                }
            }
        }

        // 3.2 Add new Async.NET capabilities in an existing application (Mixing synchronous and asynchronous calls)
        private static async Task MixSyncAsyncSqlBulkCopy()
        {
            using (SqlConnection conn1 = new SqlConnection(connectionString))
            {
                conn1.Open();
                using (SqlCommand cmd = new SqlCommand(selectStatement, conn1))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        using (SqlConnection conn2 = new SqlConnection(connectionString))
                        {
                            await conn2.OpenAsync();
                            string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                            SqlCommand createCmd = new SqlCommand(string.Format(createDestTableStatement, temptable), conn2);
                            await createCmd.ExecuteNonQueryAsync();
                            using (SqlBulkCopy bcp = new SqlBulkCopy(conn2))
                            {
                                bcp.DestinationTableName = temptable;
                                await bcp.WriteToServerAsync(reader);
                            }
                        }
                    }
                }
            }
        }

        // 3.3 Using the NotifyAfter property
        private static async Task AsyncSqlBulkCopyNotifyAfter()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);

                    string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                    cmd.CommandText = string.Format(createDestTableStatement, temptable);
                    await cmd.ExecuteNonQueryAsync();

                    using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
                    {
                        bcp.DestinationTableName = temptable;
                        bcp.NotifyAfter = 5;
                        bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                        await bcp.WriteToServerAsync(dt);
                    }
                }
            }
        }

        private static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("Copied {0} so far...", e.RowsCopied);
        }

        // 3.4 Using the new SqlBulkCopy Async.NET capabilities with DataRow[]
        private static async Task AsyncSqlBulkCopyDataRows()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand(selectStatement, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);
                    DataRow[] rows = dt.Select();

                    string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                    cmd.CommandText = string.Format(createDestTableStatement, temptable);
                    await cmd.ExecuteNonQueryAsync();

                    using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
                    {
                        bcp.DestinationTableName = temptable;
                        await bcp.WriteToServerAsync(rows);
                    }
                }
            }
        }

        // 3.5 Copying data from SQL Server to SQL Azure in .NET 4.5
        private static async Task AsyncSqlBulkCopySqlServerToSqlAzure()
        {
            using (SqlConnection srcConn = new SqlConnection(connectionString))
            using (SqlConnection destConn = new SqlConnection(azureConnectionString))
            {
                await srcConn.OpenAsync();
                await destConn.OpenAsync();
                using (SqlCommand srcCmd = new SqlCommand(selectStatement, srcConn))
                {
                    using (SqlDataReader reader = await srcCmd.ExecuteReaderAsync())
                    {
                        string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                        using (SqlCommand destCmd = new SqlCommand(string.Format(createDestTableStatement, temptable), destConn))
                        {
                            await destCmd.ExecuteNonQueryAsync();
                            using (SqlBulkCopy bcp = new SqlBulkCopy(destConn))
                            {
                                bcp.DestinationTableName = temptable;
                                await bcp.WriteToServerAsync(reader);
                            }
                        }
                    }
                }
            }
        }

        // 3.6 Cancelling an Asynchronous Operation to SQL Azure
        private static async Task AsyncSqlBulkCopyCancel()
        {
            CancellationTokenSource cts = new CancellationTokenSource();
            using (SqlConnection srcConn = new SqlConnection(connectionString))
            using (SqlConnection destConn = new SqlConnection(azureConnectionString))
            {
                await srcConn.OpenAsync(cts.Token);
                await destConn.OpenAsync(cts.Token);
                using (SqlCommand srcCmd = new SqlCommand(selectStatement, srcConn))
                {
                    using (SqlDataReader reader = await srcCmd.ExecuteReaderAsync(cts.Token))
                    {
                        string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                        using (SqlCommand destCmd = new SqlCommand(string.Format(createDestTableStatement, temptable), destConn))
                        {
                            await destCmd.ExecuteNonQueryAsync(cts.Token);
                            using (SqlBulkCopy bcp = new SqlBulkCopy(destConn))
                            {
                                bcp.DestinationTableName = temptable;
                                await bcp.WriteToServerAsync(reader, cts.Token);
                                //Cancel Async SqlBulCopy Operation after 200 ms
                                cts.CancelAfter(200);
                            }
                        }
                    }
                }
            }
        }

        // 3.7 Using Async.Net and MARS
        private static async Task AsyncSqlBulkCopyMARS()
        {
            using (SqlConnection marsConn = new SqlConnection(marsConnectionString))
            {
                await marsConn.OpenAsync();

                SqlCommand titlesCmd = new SqlCommand("SELECT * FROM [pubs].[dbo].[titles]", marsConn);
                SqlCommand authorsCmd = new SqlCommand("SELECT * FROM [pubs].[dbo].[authors]", marsConn);
                //With MARS we can have multiple active results sets on the same connection
                using (SqlDataReader titlesReader = await titlesCmd.ExecuteReaderAsync())
                using (SqlDataReader authorsReader = await authorsCmd.ExecuteReaderAsync())
                {
                    await authorsReader.ReadAsync();

                    string temptable = "[#" + Guid.NewGuid().ToString("N") + "]";
                    using (SqlConnection destConn = new SqlConnection(connectionString))
                    {
                        await destConn.OpenAsync();
                        using (SqlCommand destCmd = new SqlCommand(string.Format(createDestTableStatement, temptable), destConn))
                        {
                            await destCmd.ExecuteNonQueryAsync();
                            using (SqlBulkCopy bcp = new SqlBulkCopy(destConn))
                            {
                                bcp.DestinationTableName = temptable;
                                await bcp.WriteToServerAsync(titlesReader);
                            }
                        }
                    }
                }
            }
        }
    }
}

Asynchronously use multiple commands with MARS

The example opens a single connection to the AdventureWorks database. Using a SqlCommand object, a SqlDataReader is created. As the reader is used, a second SqlDataReader is opened, using data from the first SqlDataReader as input to the WHERE clause for the second reader.

Note

The following example uses the sample AdventureWorks database. The connection string provided in the sample code assumes that the database is installed and available on the local computer. Modify the connection string as necessary for your environment.

using System.Data.Common;
using Microsoft.Data.SqlClient;
using System.Threading.Tasks;

class Class1
{
    static void Main()
    {
        Task task = MultipleCommands();
        task.Wait();
    }

    static async Task MultipleCommands()
    {
        // By default, MARS is disabled when connecting to a MARS-enabled.
        // It must be enabled in the connection string.
        string connectionString = GetConnectionString();

        int vendorID;
        SqlDataReader productReader = null;
        string vendorSQL =
            "SELECT BusinessEntityID, Name FROM Purchasing.Vendor";
        string productSQL =
            "SELECT Production.Product.Name FROM Production.Product " +
            "INNER JOIN Purchasing.ProductVendor " +
            "ON Production.Product.ProductID = " +
            "Purchasing.ProductVendor.ProductID " +
            "WHERE Purchasing.ProductVendor.BusinessEntityID = @VendorId";

        using (SqlConnection awConnection =
            new SqlConnection(connectionString))
        {
            SqlCommand vendorCmd = new SqlCommand(vendorSQL, awConnection);
            SqlCommand productCmd =
                new SqlCommand(productSQL, awConnection);

            productCmd.Parameters.Add("@VendorId", SqlDbType.Int);

            await awConnection.OpenAsync();
            using (SqlDataReader vendorReader = await vendorCmd.ExecuteReaderAsync())
            {
                while (await vendorReader.ReadAsync())
                {
                    Console.WriteLine(vendorReader["Name"]);

                    vendorID = (int)vendorReader["BusinessEntityID"];

                    productCmd.Parameters["@VendorId"].Value = vendorID;
                    // The following line of code requires a MARS-enabled connection.
                    productReader = await productCmd.ExecuteReaderAsync();
                    using (productReader)
                    {
                        while (await productReader.ReadAsync())
                        {
                            Console.WriteLine("  " +
                                productReader["Name"].ToString());
                        }
                    }
                }
            }
        }
    }

    private static string GetConnectionString()
    {
        // To avoid storing the connection string in your code, you can retrieve it from a configuration file.
        return "Data Source=(local);Integrated Security=SSPI;Initial Catalog=AdventureWorks;MultipleActiveResultSets=True";
    }
}

Asynchronously read and update data with MARS

MARS allows a connection to be used for both read operations and data manipulation language (DML) operations with more than one pending operation. This feature eliminates the need for an application to deal with connection-busy errors. Also, MARS can replace the use of server-side cursors, which generally consume more resources. Finally, because multiple operations can operate on a single connection, they can share the same transaction context, eliminating the need to use sp_getbindtoken and sp_bindsession system stored procedures.

The following Console application demonstrates how to use two SqlDataReader objects with three SqlCommand objects and a single SqlConnection object with MARS enabled. The first command object retrieves a list of vendors whose credit rating is 5. The second command object uses the vendor ID provided from a SqlDataReader to load the second SqlDataReader with all of the products for the particular vendor. Each product record is visited by the second SqlDataReader. A calculation is performed to determine what the new OnOrderQty should be. The third command object is then used to update the ProductVendor table with the new value. This entire process takes place within a single transaction, which is rolled back at the end.