October 30, 2014

Using Linq To Sql

To use LinqToSql in a project:
First add "System.Data.Linq" reference to the project
Map the Entity Classes to Tables. Need a "[Table]XxxTable" class per table with appropriate properties for each column. Note that the columns names have to match the property names. Although this table class maps to the Db table an instance of it represents a row in the table. For example:
// Table mapping entity for the MyTableRow table row
[Table(Name = "MyTable")]
internal class MyTableRow
{
    // Default constructor is Required for Linq to Sql
    public MyTableRow()
    {
    }

    /////////////////////////////////
    // Database columns defined here

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column(CanBeNull = false)]
    public string Name { get; set; }

    [Column(DbType = "Bit NOT NULL")]
    public bool IsMandatory { get; set; }
}
This maps to a row in the SQL table "MyTable". Need a "DataContext" derived class to access these tables, call it xxxDataContext. Mark it with the "[Database]" attribute. For example:
// Linq to SQL data context for accessing the DB 
[Database]
internal class MyDataContext : DataContext
{
    // Constructor
    public MyDataContext(string connectionString)
        : base(connectionString)
    {
    }
}
Found that making the correct key definitions (including foreign keys) on the SQL tables was critical to getting the Linq to Sql working. With those definitions we can start to query the DB using Linq.
Querying SQL with LINQ
LINQ to SQL: .NET Language-Integrated Query for Relational Data
private void CreatePattern(
    MyDataContext dbAccess, 
    MyTableRow[] toCreate)
{
    var table = dbAccess.GetTable<MyTableRow>();
    table.InsertAllOnSubmit(toCreate);
    dbAccess.SubmitChanges();
}

private MyTableRow[] ReadPattern()
{
    var allRows = new MyTableRow[0];
    using (var dbAccess = new MyDataContext(GetDatabaseConnectionString()))
    {
        var table = dbAccess.GetTable<MyTableRow>();
        allRows = table.ToArray();
    }
    return allRows.ToArray();
}

private void UpdatePattern(
    MyAdminContext dbAccess, 
    MyTableRow[] changed)
{
    var table = dbAccess.GetTable<MyTableRow>();
    foreach (var target in changed)
    {
        // Find row to update in the table
        var id = target.Id;
        var row = table.FirstOrDefault(rowx => rowx.Id == id);
        if (row != null) // IF it was found
        {
            // Copy the changes from target into the row
            UpdateRow(row, target); 
        }
    }
    dbAccess.SubmitChanges(); 
}

private void DeletePattern(
    MyDataContext dbAccess, 
    MyTableRow[] deleted)
{
    var table = dbAccess.GetTable<MyTableRow>();

    foreach (var target in deleted)
    {
        // Find row to delete in the table
        var id = target.Id;
        var row = table.FirstOrDefault(ipdx => ipdx.Id == id);
        if (row != null) // Was it found?
        {
            table.DeleteOnSubmit(row);
        }
    }
    dbAccess.SubmitChanges();
}
There are 2 ways to create transactions; using the TransactionScope class and using the standard DbTransaction class.
Using new TransactionScope() Considered Harmful
All About TransactionScope
Using Transaction Scope (need to add the System.Transactions assembly in the references)
using System.Transactions;
...
using (var scope = TransactionScopeFactory.CreateTransactionScope()) // Asscociate all the changes with 1 transaction
{
    // Use 1 data context for all operations, in this case MS DTC will not be used
    // See http://weblog.west-wind.com/posts/2009/Jul/14/LINQ-to-SQL-and-Transactions 
    // paragraph 'TransactionScope DTC Requirements'
    using (var dbAccess = new MyDataContext (GetDatabaseConnectionString()))
    {
        MakeDbChangesUsingLinqToSql(dbAccess);

        scope.Complete();
        log.WriteInfo("Transaction completed, the database changes are committed.");
    }
}
Using a DbTransaction
using (var dbAccess = new MyDataContext(GetDatabaseConnectionString()))
{
    dbAccess.Connection.Open();
    // To absolutely guarantee that the MS DTC will not be used (which can occur when using TransactionScope) 
    // we will use a standard DB transaction here
    dbAccess.Transaction = dbAccess.Connection.BeginTransaction();
    
    try
    {
        MakeDbChangesUsingLinqToSql(dbAccess);
    
        dbAccess.Transaction.Commit(); // No exceptions so commit the changes
        log.WriteInfo("Transaction completed, the database changes are committed.");
    }
    catch (Exception) // Rollback if any exception is encountered
    {
        dbAccess.Transaction.Rollback();
        throw;
    }
}

October 20, 2014

Async/Await

Best explanation I have found
Looking underneath the hood
More complicated but diagrammed example here

The “async” keyword tells compiler that the method may return asynchronously, it enables the use of the await keyword. The beginning of an async method is executed just like any other method, it runs synchronously until it hits an “await” (or throws an exception).

The “await” keyword is where things can get asynchronous. Await is like a unary operator: it takes a single argument, an awaitable (an “awaitable” is an asynchronous operation). Await examines that awaitable to see if it has already completed; if the awaitable has already completed, then the method just continues running synchronously just like a regular method.

If “await” sees that the awaitable has NOT completed, then it acts asynchronously. It tells the awaitable to run the remainder of the method when it completes, and then returns from the async method.

Careful with a method that leaves a lot of data on the stack. This data will stay around until the async method is complete, ie all "async" tasks have completed.
private static async void DoSomethingAsync()
{
    var result = await SomeTask(args);
    DoSomethingWithResult(result);    
}