Additional documentation on Asynchronous Programming can be found here: https://msdn.microsoft.com/en-us/library/hh211418%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396
Below are some async examples from Microsoft adapted for SQL Anywhere's .NET data provider. You can build this into a .NET project and run it. Hope this will help.
// [Program.cs]
// Copyright (c) 2016 SAP AG or an SAP affiliate company. All rights reserved.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
namespace AsyncTest
{
class Program
{
static void Main(string[] args)
{
Test();
}
static void Test()
{
AsyncTest[] tests = { new TestSA1(), new TestSA2(), new TestSA3(), new TestSA4(),
new TestSA5(), new TestSA6(), new TestSA7(), new TestSA8(),
new TestSA9() };
foreach (var test in tests)
{
test.Run();
while (!test.Completed)
{
Thread.Sleep(100);
}
Console.WriteLine();
}
if (AsyncTest.Failures > 0)
Console.WriteLine(string.Format("{0} test failures", AsyncTest.Failures));
else
Console.WriteLine("No test failures!");
Console.WriteLine("Press Enter to end");
Console.ReadLine();
}
}
}
// [AsyncTest.cs]
// Copyright (c) 2017 SAP AG or an SAP affiliate company. All rights reserved.
using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sap.Data.SQLAnywhere;
namespace AsyncTest
{
internal class Utility
{
public const string Provider = "Sap.Data.SQLAnywhere";
public const string ConnStrSA1 = "DSN=SQL Anywhere 17 Demo;UID=DBA;PWD=sql";
public const string ConnStrSA2 = "UID=DBA;PWD=sql";
}
internal abstract class AsyncTest
{
private static int _count = 0;
public static int Failures = 0;
public bool Completed { get; set; }
public async void Run()
{
_count++;
Completed = false;
Console.WriteLine(string.Format("Running test {0}......", _count));
try
{
await Execute();
if (!UseCallback())
{
Passed();
}
}
catch (Exception ex)
{
Failures++;
Failed(ex);
}
Completed = true;
}
protected void Passed()
{
Console.WriteLine(string.Format("Test {0} passed...", _count));
}
protected void Failed(Exception ex)
{
Console.WriteLine(string.Format("Test {0} failed - {1}", _count, ex.Message));
}
protected virtual bool UseCallback()
{
return false;
}
internal abstract Task Execute();
}
class TestSA1 : AsyncTest
{
internal override async Task Execute()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
SACommand command = new SACommand("select 1", conn);
int result = MethodAsync(conn, command).Result;
command = new SACommand("select top 10 * from SalesOrders", conn);
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
Console.WriteLine(String.Format("{0}", reader[0]));
}
}
}
private async Task<int> MethodAsync(SAConnection conn, SACommand cmd)
{
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
return 1;
}
}
class TestSA2 : AsyncTest
{
internal override async Task Execute()
{
AsyncCallback productList = new AsyncCallback(Callback);
SAConnection conn = new SAConnection(Utility.ConnStrSA1);
await conn.OpenAsync().ContinueWith((task) =>
{
SACommand cmd = new SACommand("SELECT TOP 2 * FROM SalesOrders", conn);
IAsyncResult ia = cmd.BeginExecuteReader(Callback, cmd);
}, TaskContinuationOptions.OnlyOnRanToCompletion);
}
protected override bool UseCallback()
{
return true;
}
void Callback(IAsyncResult result)
{
if (result.IsCompleted)
{
SACommand cmd = result.AsyncState as SACommand;
var reader = cmd.EndExecuteReader(result);
while (reader.Read())
Console.WriteLine(String.Format("{0}", reader[0]));
Completed = true;
Passed();
}
}
}
class TestSA3 : AsyncTest
{
internal override async Task Execute()
{
await PerformDBOperationsUsingProviderModel(Utility.ConnStrSA1, Utility.Provider);
}
private async Task PerformDBOperationsUsingProviderModel(string connectionString, string providerName)
{
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
await connection.OpenAsync();
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT TOP 2 * FROM Customers";
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
for (int i = 0; i < reader.FieldCount; i++)
{
object obj = await reader.GetFieldValueAsync<object>(i);
Console.WriteLine(obj);
}
}
}
}
}
}
class TestSA4 : AsyncTest
{
internal override async Task Execute()
{
await ExecuteSqlTransaction();
}
private async Task ExecuteSqlTransaction()
{
using (SAConnection connection = new SAConnection(Utility.ConnStrSA1))
{
await connection.OpenAsync();
SACommand command = connection.CreateCommand();
SATransaction transaction = null;
transaction = await Task.Run<SATransaction>(() => connection.BeginTransaction());
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "DELETE FROM Departments WHERE (DepartmentID = 777 OR DepartmentID = 888)";
await command.ExecuteNonQueryAsync();
command.CommandText = "INSERT INTO Departments(DepartmentID, DepartmentName) VALUES (777, 'HR')";
await command.ExecuteNonQueryAsync();
command.CommandText = "INSERT INTO Departments(DepartmentID, DepartmentName) VALUES (888, 'Supporting')";
await command.ExecuteNonQueryAsync();
await Task.Run(() => transaction.Commit());
Console.WriteLine("Both records are written to database.");
}
}
}
class TestSA5 : AsyncTest
{
internal override async Task Execute()
{
await ExecuteDistributedTransaction();
}
private async Task ExecuteDistributedTransaction()
{
using (SAConnection connection = new SAConnection(Utility.ConnStrSA1))
{
await connection.OpenAsync();
SACommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM Departments WHERE (DepartmentID = 777 OR DepartmentID = 888)";
await command.ExecuteNonQueryAsync();
}
using (SAConnection connection1 = new SAConnection(Utility.ConnStrSA1))
using (SAConnection connection2 = new SAConnection(Utility.ConnStrSA2))
{
using (CommittableTransaction transaction = new CommittableTransaction())
{
await connection1.OpenAsync();
connection1.EnlistTransaction(transaction);
await connection2.OpenAsync();
connection2.EnlistTransaction(transaction);
SACommand command1 = connection1.CreateCommand();
command1.CommandText = "INSERT INTO Departments(DepartmentID, DepartmentName) VALUES (777, 'HR')";
await command1.ExecuteNonQueryAsync();
SACommand command2 = connection2.CreateCommand();
command2.CommandText = "INSERT INTO Departments(DepartmentID, DepartmentName) VALUES (888, 'Supporting')";
await command2.ExecuteNonQueryAsync();
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
}
}
}
class TestSA6 : AsyncTest
{
internal override async Task Execute()
{
CancellationTokenSource source = new CancellationTokenSource();
source.CancelAfter(2000);
// give up after 2 seconds
try
{
await CancellingAsynchronousOperations( source.Token );
}
catch (AggregateException exAggr)
{
CheckException(exAggr.InnerException);
}
catch (Exception ex)
{
CheckException(ex);
}
}
private bool CheckException(Exception ex)
{
if (ex.Message.Equals("Statement interrupted by user"))
{
Console.WriteLine("Operation canceled");
return true;
}
throw ex;
}
private async Task CancellingAsynchronousOperations(CancellationToken cancellationToken)
{
using (SAConnection connection = new SAConnection(Utility.ConnStrSA1))
{
await connection.OpenAsync(cancellationToken);
SACommand command = new SACommand("WAITFOR DELAY '00:10:00'", connection);
await command.ExecuteNonQueryAsync(cancellationToken);
}
}
}
class TestSA7 : AsyncTest
{
internal override async Task Execute()
{
await MultipleCommands();
}
private async Task MultipleCommands()
{
string sqlDept = "SELECT * FROM Departments";
string sqlEmployee = "SELECT * FROM Employees WHERE DepartmentID = {0}";
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
SACommand cmdDept = new SACommand(sqlDept, conn);
SACommand cmdEmployee = new SACommand(sqlEmployee, conn);
using (SADataReader readerDept = await cmdDept.ExecuteReaderAsync())
{
while (await readerDept.ReadAsync() && !await readerDept.IsDBNullAsync(0))
{
Console.WriteLine("Department {0} employees", readerDept["DepartmentName"]);
cmdEmployee.CommandText = string.Format(sqlEmployee, readerDept[0]);
using (SADataReader readerEmployee = await cmdEmployee.ExecuteReaderAsync())
{
while (await readerEmployee.ReadAsync())
{
Console.WriteLine(" {0} {1}", readerEmployee["GivenName"], readerEmployee["Surname"]);
}
}
}
}
}
}
}
class TestSA8 : AsyncTest
{
internal override async Task Execute()
{
await ReadingAndUpdatingData();
}
private async Task ReadingAndUpdatingData()
{
string sqlDept = "SELECT * FROM Departments WHERE DepartmentHeadID IS NULL";
string sqlEmployee = "SELECT TOP 1 * FROM Employees";
string sqlUpdate = "UPDATE Departments SET DepartmentHeadID = {0} WHERE DepartmentID = {1}";
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
int totalRecordsUpdated = 0;
SATransaction trans = await Task.Run(() => conn.BeginTransaction());
SACommand cmdDept = new SACommand(sqlDept, conn, trans);
SACommand cmdEmployee = new SACommand(sqlEmployee, conn, trans);
SACommand cmdUpdate = new SACommand(sqlUpdate, conn, trans);
using (SADataReader readerDept = await cmdDept.ExecuteReaderAsync())
{
while (await readerDept.ReadAsync())
{
Console.WriteLine(readerDept["DepartmentName"]);
using (SADataReader readerEmployee = await cmdEmployee.ExecuteReaderAsync())
{
await readerEmployee.ReadAsync();
cmdUpdate.CommandText = string.Format(sqlUpdate, readerEmployee["EmployeeID"], readerDept["DepartmentID"]);
totalRecordsUpdated += await cmdUpdate.ExecuteNonQueryAsync();
}
}
}
Console.WriteLine("Total Records Updated: " + totalRecordsUpdated);
await Task.Run(() => trans.Rollback());
Console.WriteLine("Transaction Rolled Back");
}
}
}
class TestSA9 : AsyncTest
{
static string destTable = "DepartmentsDest";
static string selStmt = "SELECT * FROM Departments";
static string dropDestTableStmt = "DROP TABLE IF EXISTS DepartmentsDest";
static string createDestTableStmt = "CREATE TABLE DepartmentsDest ( DepartmentID int NOT NULL, DepartmentName char(80) NOT NULL, DepartmentHeadID int )";
internal override async Task Execute()
{
CreateDestTable();
SynchronousSqlBulkCopy();
CreateDestTable();
await AsyncSqlBulkCopy();
CreateDestTable();
await MixSyncAsyncSqlBulkCopy();
CreateDestTable();
await AsyncSqlBulkCopyNotifyAfter();
CreateDestTable();
await AsyncSqlBulkCopyDataRows();
//CreateDestTable();
// await AsyncSqlBulkCopyCancel();
CreateDestTable();
await AsyncSqlBulkCopyMARS();
}
private async void CreateDestTable()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
SACommand cmd = new SACommand(dropDestTableStmt, conn);
await cmd.ExecuteNonQueryAsync();
cmd.CommandText = createDestTableStmt;
await cmd.ExecuteNonQueryAsync();
}
}
// Synchronous bulk copy in .NET 4.5 private
private void SynchronousSqlBulkCopy()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
conn.Open();
DataTable dt = new DataTable();
using (SACommand cmd = new SACommand(selStmt, conn))
{
SADataAdapter adapter = new SADataAdapter(cmd);
adapter.Fill(dt);
using (SABulkCopy bcp = new SABulkCopy(conn))
{
bcp.DestinationTableName = destTable;
bcp.WriteToServer(dt);
}
}
}
}
// Asynchrounous bulk copy in .NET 4.5
private async Task AsyncSqlBulkCopy()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
DataTable dt = new DataTable();
using (SACommand cmd = new SACommand(selStmt, conn))
{
SADataAdapter adapter = new SADataAdapter(cmd);
adapter.Fill(dt);
using (SABulkCopy bcp = new SABulkCopy(conn))
{
bcp.DestinationTableName = destTable;
await bcp.WriteToServerAsync(dt);
}
}
}
}
// Add new Async.NET capabilities in an existing application (Mixing synchronous and asynchornous calls)
private async Task MixSyncAsyncSqlBulkCopy()
{
using (SAConnection conn1 = new SAConnection(Utility.ConnStrSA1))
{
conn1.Open();
using (SACommand cmd = new SACommand(selStmt, conn1))
{
using (SADataReader reader = cmd.ExecuteReader())
{
using (SAConnection conn2 = new SAConnection(Utility.ConnStrSA2))
{
await conn2.OpenAsync();
using (SABulkCopy bcp = new SABulkCopy(conn2))
{
bcp.DestinationTableName = destTable;
await bcp.WriteToServerAsync(reader);
}
}
}
}
}
}
// Using the NotifyAfter property
private async Task AsyncSqlBulkCopyNotifyAfter()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
DataTable dt = new DataTable();
using (SACommand cmd = new SACommand(selStmt, conn))
{
SADataAdapter adapter = new SADataAdapter(cmd);
adapter.Fill(dt);
using (SABulkCopy bcp = new SABulkCopy(conn))
{
bcp.DestinationTableName = destTable;
bcp.NotifyAfter = 1;
bcp.SARowsCopied += new SARowsCopiedEventHandler(OnSqlRowsCopied);
await bcp.WriteToServerAsync(dt);
}
}
}
}
private static void OnSqlRowsCopied(object sender, SARowsCopiedEventArgs e)
{
Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
// Using the new SABulkCopy Async.NET capabilities with DataRow[]
private async Task AsyncSqlBulkCopyDataRows()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
DataTable dt = new DataTable();
using (SACommand cmd = new SACommand(selStmt, conn))
{
SADataAdapter adapter = new SADataAdapter(cmd);
adapter.Fill(dt);
DataRow[] rows = dt.Select();
using (SABulkCopy bcp = new SABulkCopy(conn))
{
bcp.DestinationTableName = destTable;
await bcp.WriteToServerAsync(rows);
}
}
}
}
// Using Async.Net and MARS
private async Task AsyncSqlBulkCopyMARS()
{
using (SAConnection conn = new SAConnection(Utility.ConnStrSA1))
{
await conn.OpenAsync();
SACommand cmdCustomer = new SACommand("SELECT * FROM Customers", conn);
SACommand cmdDept = new SACommand(selStmt, conn);
using (SADataReader readerCustomer = await cmdCustomer.ExecuteReaderAsync())
using (SADataReader readerDept = await cmdDept.ExecuteReaderAsync())
{
await readerDept.ReadAsync();
using (SAConnection destConn = new SAConnection(Utility.ConnStrSA2))
{
await destConn.OpenAsync();
using (SABulkCopy bcp = new SABulkCopy(destConn))
{
bcp.DestinationTableName = destTable;
await bcp.WriteToServerAsync(readerDept);
}
}
}
}
}
}
}
answered
28 Jul '16, 12:00
JBSchueler
3.2k●3●15●62
accept rate:
20%