/// <summary>
/// Example for reading data and returning via a named value tuple
/// https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/builtin-types/value-tuples
/// </summary>
/// <param name="ct"></param>
/// <returns></returns>
public static async Task <(bool success, DataTable dataTable, Exception exception)> ReadProductsUsingNamedValueTuple(CancellationToken ct)
{
StopWatcher.Instance.Start();
DataTable dt = new ();
return(await Task.Run(async() =>
{
await using var cn = new SqlConnection(ConnectionString);
await using var cmd = new SqlCommand { Connection = cn, CommandText = SelectStatement() };
try
{
ConnectMonitor?.Invoke("Before open");
await cn.OpenAsync(ct);
AfterConnectMonitor?.Invoke($"Elapsed: {StopWatcher.Instance.Elapsed}");
dt.Load(await cmd.ExecuteReaderAsync(ct));
/*
* See comments in method above on accessing columns
*/
dt.Columns["ProductID"].ColumnMapping = MappingType.Hidden;
dt.Columns["SupplierID"].ColumnMapping = MappingType.Hidden;
dt.Columns["CategoryID"].ColumnMapping = MappingType.Hidden;
return (true, dt, null);
}
catch (Exception ex)
{
return (false, dt, ex);
}
}, ct));
}
/// <summary>
/// Read products into a DataTable with connection timeout where product is in a specific category
/// </summary>
/// <param name="ct"><seealso cref="CancellationToken"/></param>
/// <param name="categoryIdentifier">Existing category identifier</param>
/// <returns></returns>
public static async Task <DataTableResults> ReadProductsUsingContainerByCategory(CancellationToken ct, int categoryIdentifier)
{
StopWatcher.Instance.Start();
var result = new DataTableResults()
{
DataTable = new DataTable()
};
return(await Task.Run(async() =>
{
await using var cn = new SqlConnection(ConnectionString);
await using var cmd = new SqlCommand { Connection = cn, CommandText = SelectStatement(categoryIdentifier) };
try
{
ConnectMonitor?.Invoke("Before open");
await cn.OpenAsync(ct);
AfterConnectMonitor?.Invoke($"Elapsed: {StopWatcher.Instance.Elapsed}");
}
#pragma warning disable 168
catch (TaskCanceledException tce)
#pragma warning restore 168
{
/*
* For debug purposes we have a variable for TaskCanceledException
* although we can still see it w/o tce but this is easier for those
* not knowledgeable with no var for the exception
*/
result.ConnectionFailed = true;
result.ExceptionMessage = "Connection Failed";
return result;
}
catch (Exception ex)
{
result.GeneralException = ex;
return result;
}
result.DataTable.Load(await cmd.ExecuteReaderAsync(ct));
return result;
}, ct));
}
public static async Task <DataTableResults> ReadProductsUsingContainer(CancellationToken ct)
{
StopWatcher.Instance.Start();
var result = new DataTableResults()
{
DataTable = new DataTable()
};
return(await Task.Run(async() =>
{
await using var cn = new SqlConnection(ConnectionString);
Debug.WriteLine(cn.ConnectionString);
await using var cmd = new SqlCommand { Connection = cn, CommandText = SelectStatement() };
try
{
ConnectMonitor?.Invoke("Before open");
await cn.OpenAsync(ct);
AfterConnectMonitor?.Invoke($"Elapsed: {StopWatcher.Instance.Elapsed}");
}
#pragma warning disable 168
catch (TaskCanceledException tce)
#pragma warning restore 168
{
/*
* For debug purposes we have a variable for TaskCanceledException
* although we can still see it w/o tce but this is easier for those
* not knowledgeable with no var for the exception
*/
result.ConnectionFailed = true;
result.ExceptionMessage = "Connection Failed";
return result;
}
catch (Exception ex)
{
result.GeneralException = ex;
return result;
}
result.DataTable.Load(await cmd.ExecuteReaderAsync(ct));
/*
* When coding is done properly, these checks are not needed but
* there are times when a developer slips so let's perform assertion
*
* Otherwise (and even better) remove the assertion and allow a run
* time exception to be thrown while in development or test mode.
*
* Note:
* First check is a language extension does the same as the other
* checks which some may like
*/
if (result.DataTable.ColumnExists("ProductID"))
{
result.DataTable.Columns["ProductID"].ColumnMapping = MappingType.Hidden;
}
if (result.DataTable.Columns.Contains("SupplierID"))
{
result.DataTable.Columns["SupplierID"].ColumnMapping = MappingType.Hidden;
}
if (result.DataTable.Columns.Contains("CategoryID"))
{
result.DataTable.Columns["CategoryID"].ColumnMapping = MappingType.Hidden;
}
return result;
}, ct));
}
