The Archidev

Moved to Typepad

I have moved my posts here. While live spaces is great I thought it's time for a more professional look. My live spaces blog will still be up for old posts, images etc. omarbesiso.live.spaces.com

ADNUG User Group – Building Enterprise Applications

On Tuesday the 7th of July I had the privilege and pleasure to present at the Adelaide .NET User Group. It was such a fantastic experience considering it was my first time in Adelaide and I got to catch up with my good old friend and mentor Darren Neimke. I got to present for the first time on my adapted “Building Enterprise Applications” talk where I spoke about my experiences in my relatively new role in Datacom and some new devised guidelines on building Enterprise Software. You can download the slides of the talk here.

Looking forward to CodeCampSA next weekend where I will be presenting on the Sync Framework.

Parso’s World’s Greatest Shave

On Friday I had the pleasure of catching up with my very good friend Andrew Parsons at Microsoft for lunch. Just as it happens, it was the World’s Greatest Shave event and Parso had volunteered to get a shave. I even paid 20 bucks to shave a portion of Andrew’s hair myself. Here’s for Parso, http://tinyurl/VirtualShave .

View Full Album

SQL 2008 & Sync Framework Over WCF : With Simplicity

I am working on a project at the moment that needs to keep a local store of data in order to give it to ability to function when the WCF service that feeds is offline. Once the service is up and running again, the data needs to be synchronized back to the server and any data on the server needs to be synchronized down to the client. This is becoming an increasingly demanded functionality as we see new projects. Now out of the box the Sync Framework will work nicely if you got direct access to SQL. But as the requirement states we have a WCF service sitting between the client and the DB.

In this post I am hoping to show a step by step approach to create a bidirectional synchronization line between a products table on my client and one on my SQL 2008 server that site behind a WCF service. Directly from MSDN this image shows what we try to achieve http://msdn.microsoft.com/en-us/library/bb902831.aspx.

Note: This is not an entry post of the sync framework. It’s just an explanation how to use sync framework, WCF and SQL 2008. Source code available at the end of the post.

Install the Sync Framework

Please download and install the sync framework found here.

My DB

Nothing fancy to see here, just your good old product table with a couple of rows.

There is a key thing to mention here before I continue. If you are using an SQL Server older than 2008 you will need to be manual change tracking as explained in the link to the MSDN article above. Otherwise SQL 2008 has got built in change tracking features which provide sufficient change tracking information to our scenario. One option in your database you have to make sure is on, is that tracking of changes is enabled. Right Click your database and click properties.

I created a data dude project in my solution for simplicity of distributing the demo’s source code.

 

My Sync Service – Part 1

The first thing I need to do here is to create a WCF project and add the following dlls as references. This will be my sync service.

1. Microsoft.Synchronization.dll
2. Microsoft.Synchronization.Data.dll
3. Microsoft.Synchronization.Data.Server.dll

I then create the following service definition for my Sync Service. These methods (service operations) and their signatures are what my client is going to be expecting when attempting a sync back to the server.

using System.Collections.ObjectModel;

using System.Data;

using System.Net.Security;

using System.ServiceModel;

using Microsoft.Synchronization.Data;



namespace WcfSyncService

{

  [ServiceContract(Name = "SyncService", SessionMode = SessionMode.NotAllowed,

    ProtectionLevel = ProtectionLevel.EncryptAndSign)]

  public interface ISyncService

  {

    [OperationContract]

    SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession);



    [OperationContract]

    SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession);



    [OperationContract]

    SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession);



    [OperationContract]

    SyncServerInfo GetServerInfo(SyncSession syncSession);

  }

}

My Sync Client – Part 1

Now I come up with a WPF client that displays a list of items from the local cache at any given time. The idea will be to synchronise the local cache with my server’s database through the WCF service we defined earlier.

So I start off with a simple WPF App.

I now need to add my local cache database.

I now get presented with the following wizard. I choose the server’s connection string and a new client database option is given to me. I then change the project location of the server to my previously created WCF service project.

From there I choose the products table to create a local dataset. The steps are outlined as follows.

 

Select the field I want to include in my typed data table.

Viola! Not only did the client create the appropriate cache on the client but it also created the necessary sync providers and adapters in the service project as well.

The thing to note here is that my client is still not configured to sync with the server. For that I will need to do some minor code changes.

My Sync Service – Part 2

Jumping back to the server I need to now implement my service definition. I do that by passing all of the operation on to the Sync provider that has now been created for me by the wizard.

using System.Collections.ObjectModel;

using System.Data;

using Microsoft.Synchronization.Data;



namespace WcfSyncService

{

  public class SyncService : ISyncService

  {

    private readonly ProductCacheServerSyncProvider syncProvider;



    /// <summary>

    /// Initializes a new instance of the <see cref="SyncService"/> class.

    /// </summary>

    public SyncService()

    {

      syncProvider = new ProductCacheServerSyncProvider();

    }



    public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

    {

      return syncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

    }



    public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

    {

      return syncProvider.GetChanges(groupMetadata, syncSession);

    }



    public SyncSchema GetSchema(Collection<string> tableNames, SyncSession syncSession)

    {

      return syncProvider.GetSchema(tableNames, syncSession);

    }



    public SyncServerInfo GetServerInfo(SyncSession syncSession)

    {

      return syncProvider.GetServerInfo(syncSession);

    }

  }

}

My Sync service is now ready to roll and I load in the wcf test client just to make sure everything is alright.

OK so the mental note here is the service does load and it does run but the parameters are not supported by the WCF test client.

My Sync Client – Part 2

Ok now my WCF service is ready. The next step is to configure the client to use the WCF service for the synchronization. I now add a service reference to my service from the client.

One more thing to note here. The wizard will always create SyncTables with a default SyncDirection of DownloadOnly. But in this scenario we need the table updates to be Bidirectional.  The trick is to right click the ProductCache and click on View Code. A partial class is created and you need to modify it as follows.

using Microsoft.Synchronization.Data;



namespace WcfSyncClient {

  

  

  public partial class ProductCacheSyncAgent 
{

    

    partial void OnInitialized()

    {

      this.Product.SyncDirection = SyncDirection.Bidirectional;

    }

  }

}

Ok so now all my infrastructure is ready so time for a semi pretty UI for the display of data and operations.

It’s pretty simple what happens behind these buttons. The main thing to note is that I am instantiating the agent using my WCF client proxy. This basically tells the sync agent to use my WCF service for any synchronisations.

using System;

using System.Windows;

using Microsoft.Synchronization.Data;

using WcfSyncClient.SyncDBDataSetTableAdapters;

using WcfSyncClient.SyncServiceProxy;



namespace WcfSyncClient

{

  /// <summary>

  /// Interaction logic for Shell.xaml

  /// </summary>

  public partial class Shell : Window

  {

    public Shell()

    {

      InitializeComponent();

    }



    private void btnRefresh_Click(object sender, RoutedEventArgs e)

    {

      var ds = new SyncDBDataSet();

      var adapter = new ProductTableAdapter();

      adapter.Fill(ds.Product);

      listBox.DataContext = ds;

    }



    private void btnSynchronise_Click(object sender, RoutedEventArgs e)

    {

      var syncAgent = new ProductCacheSyncAgent(new SyncServiceClient());

      SyncStatistics syncStats = syncAgent.Synchronize();

    }



    private void btnAddRow_Click(object sender, RoutedEventArgs e)

    {

      var ds = (SyncDBDataSet) listBox.DataContext;

      var dr = ds.Product.NewRow() as SyncDBDataSet.ProductRow;

      if (dr != null)

      {

        dr.Name = "New Product " + (new Random()).Next();

        dr.ProductId = Guid.NewGuid();

        dr.Price = 800;

        ds.Product.AddProductRow(dr);



        var adapter = new ProductTableAdapter();

        adapter.Update(ds);

      }

    }

  }

}

I can see by running the application the SQL DB is also getting that changes as I add new records on the client and vice versa.

One Problem You Might Run Into

One exception that you might get during debugging your application on 64-bit machines is:

Unable to load DLL 'sqlceme35.dll': The specified module could not be found.

This is related to the fact that the original release SqlCe was not supported on 64-bit machines. You have two options:

1. Download Microsoft SQL Server Compact 3.5 Service Pack 1 and Synchronization Services for ADO.NET version 1.0 Service Pack 1 for Windows Desktop
2. Compile your project for x86 CPU configuration.

Clearly the first option is the recommended one.

Source Code

Conclusion

The sync framework is a nice tool and toy that simplifies a lot of the hassles we had to go through in the past for offline data caching. Bundling it with WCF just gives it a fantastic edge. It should not be missed in your future projects.

PostSharp & The Policy Injection Application Block

By now we all have heard of postsharp. By now we would have all used it once or twice or we would be using it on daily basis now.  I am not going to go into what postsharp does since you can read all about here. But what’s more important to me was understanding what it facilitates.

Scenario

So let’s say you get so far down a project and decide you want global exception handling and logging applied to your application. Kind of what the exception handling & logging application blocks do together with policy injection. So now you want to supply that functionality with the least impact possible to your code, deadlines and other constraints.

Policy Injection – Enterprise Library

So what impact would we have with policy injection. Well first of all we have to have the following assumptions:

Our classes need to implement an interface OR Our classes need to inherit from MarshalByRefObject

Now that in itself is very limiting because we cannot guarantee such a requirement but let’s take an optimistic step and say that the requirement is sufficed. We now have to go into every class’s instantiation and use the policy injection application block’s wrap or create method. But that’s a lot of impact on the code if we are working on an application with a decently sized object model or too many operations. So how’s postsharp better.

PostSharp – Injection with power

So with postsharp we create these things called aspects. So for the scenario above we would create two aspects one for exception handling and one for logging. To make this interesting let’s say we want to you use the exception handling application block to handle the exceptions and log any exceptions to a text file called trace.log. For the logging we want to just write to the console.

I start of by creating an aspects project in my solution (optional).

I then reference the necessary PostSharp assemblies, PostSharp.Public & PostSharp.Laos.

 

My Exception aspect would look something like this after inheriting from the base OnExceptionAspect base class from the PostSharp library.

using System;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using PostSharp.Laos;



namespace PostSharpDemo.Aspects

{

  /// <summary>

  /// This is a test exception aspect that will be injected into

  /// the business project. The aspect catches any unhandled

  /// exceptions and uses the exception handling application block

  /// to handle the exception.

  /// </summary>

  [Serializable]

  public sealed class MyExceptionAspect : OnExceptionAspect

  {

    public override void OnException(MethodExecutionEventArgs eventArgs)

    {

      bool rethrow = ExceptionPolicy.HandleException(eventArgs.Exception, "Demo Exception Policy");



      if (rethrow)

      {

        Console.WriteLine(eventArgs.Exception.Message);

        Console.WriteLine("Exception being handled by aspect!");

      }

    }

  }

}

and my logging aspect would looks as follows

using System;

using PostSharp.Laos;



namespace PostSharpDemo.Aspects

{

  /// <summary>

  /// This aspct is defined to demo tracing capability 

  /// with postsharp usage in AOP.

  /// </summary>

  [Serializable]

  public class MyOnMethodInvocationAspect : OnMethodInvocationAspect

  {

    public override void OnInvocation(MethodInvocationEventArgs context)

    {

      Console.WriteLine("Calling {0}", context.Delegate.Method);

      context.Proceed();

    }

  }

}

That’s it for our project. It’s as simple as that. Now how do we use this with our target assembly i.e. how do we inject those aspects? We first of all reference the assembly and the PostSharp assemblies again.

and for the final piece of the puzzle we place the following couple of lines in our assembly.info (or any other file in the project really).

// by using the '*' as shown below:

// [assembly: AssemblyVersion("1.0.*")]



[assembly: AssemblyVersion("1.0.0.0")]

[assembly: AssemblyFileVersion("1.0.0.0")]





//PostSharp assembly injection.



[assembly:

  MyExceptionAspect(AttributeTargetAssemblies = "PostSharpDemo.Business",

    AttributeTargetTypes = "PostSharpDemo.Business.*")]

[assembly:

  MyOnMethodInvocationAspect(AttributeTargetAssemblies = "PostSharpDemo.Business",

    AttributeTargetTypes = "PostSharpDemo.Business.*")]

Build, run and voila every single method executed within the assembly is traced to the console and any unhandled exception will be directed at the Exception Handling Application block for policy processing.

The demo solution can be downloaded here.

I hope at this stage you are already happy with the possibilities. But wait there is more.

PostSharp bundled with PIAB

So is the policy injection application block useless now. Absolutely not. It still has it’s place and I see even the bigger potential is to treat both tools as power tools to be used in the correct scenarios. My wondering brain made me think of the possibility of using PostSharp to apply policy injection on all the classes in an assembly just to find out that this has already been thought of. You can find the project PostSharp4Entlib at http://www.codeplex.com/entlibcontrib/Wiki/View.aspx?title=PostSharp4EntLib&referringTitle=Home

Now how is this so special? Well two things:

1. PolicyInjection now will happen at compile time not at run time, so there is a significant gain in performance including the fact that you wouldn’t need to have the PIAB configuration section in the configuration file at runtime anymore. Note: you still need the configuration sections for the other application blocks.
2. The limitation of having the classes implement an interface or inherit from MarshalByRefObject is now gone, since we don’t need any runtime injection to the objects.

There was one issue though. The project on codeplex had a lot of build errors regarding assembly versions etc. I took the liberty of combining the newest pieces, PostSharp 1.0 Sp1, Entlib 4.0 & PostSharp4Entlib and building my own little solution while fixing all the build errors. I also came up with a little sample demo to demonstrate the usage of the library. So now one line of code in AssemblyInfo.cs

[assembly: InjectPoliciesFromConfiguration("Entlib.config")]

results in the same effect that would have had to cost me instantiating or wrapping every class using policy injection or having the limitation on the objects having to inherit or implement interfaces or other classes.

A PostSharpForEntlib updated project and a demo can be downloaded here.

I hope this has been useful. Happy Injection! :)

ADO.NET and DataAcess: What every developer should know?

In the world of ever increasing ORMs, it seems that developers tend to forget that in order for you to utilise a technology to its full potential you need to understand how frameworks work internally. While I complain about how many people blog and talk about Data Access, I felt the need to clarify some of the annoyances I saw in various codebases dealing with ADO.NET over the past couple of years.

For the sake of this post I created a simple database as follows to help me better explain my points in some of the samples below.

Encryption

When working with N-tier public applications you usually deploy your application on an App\Web server and a database server (most common scenario). While most of the time your database server is not exposed to the outside world of your application consumers, you assume that an SSL certificate will suffice on the web server to handle secure connections between the client and your application inside the browser or rich client. Recently I worked on a personal project where things were a bit different. My product was actually my SQL Server database which was exposed over the web.

I am not going to go into inner details of how we secured the server from hackers, attacks etc. I am more interested in how easy it is to encrypt information over the wire travelling to and from my db server. What was surprising is that most people I spoke to about this didn’t realise how easy it is to do that. So just like a web app I had a security certificate installed on the database server. Now that is all good until I realised the underestimation of the connection string power among devs. While this is a mild example but really it is easy, the main thing you have to do after making sure the certificate is properly configured is adding an Enrypt=True value pair to the connection string along with some supporting properties.

"DataSource=MyDbServer;Initial Catlogue=MySchool;Integrated Security=SSPI; Network Library=dbmssocn; Connect Timeout=10; TrustServerCertificate=True;Encrypt=True”

That is really it so please when passing credit card numbers to your stored procedures next time please do consider encrypting the traffic to your DB.

Connection Pooling – When I don’t want it

Pooling is a great mechanism for enhancing the performance of our DB connections. Do note that pooling works over the connection Lifetime, hence a connection will be kept alive until its creation time is compared with the current time and then destroyed if the difference exceeds the lifetime. In specific scenarios though I would like to create a “Persistent Connection: One that stays alive until explicitly closed”. For that the I have to disable pooling. Again power to the connection string for this one.

"DataSource=MyDbServer;Initial Catlogue=MySchool;Integrated Security=SSPI; Pooling=False”

Another tip is when your are debugging data access code and have pooling turned on (that is the default behaviour) and you want to make sure that pooling is not affecting your scenario make sure you use the SqlConnection.ClearAllPools or SqlConnection.ClearPool methods.

Load Balancing – Lending a hand to DBAs :)

Still working with connection strings again you may have a failover SQL cluster. While many of devs depend on DBAs to configure the environments properly, we all know how busy those guys are so why not give them a helping hand. One of the properties on connection strings again can do just that.

"DataSource=MyDbServer;Initial Catlogue=MySchool;Integrated Security=SSPI;     Failover Partner=MyRescuerDbServer”

MARS – Multiple Active Result Sets

While not often done I had a scenario where I needed to open multiple SqlDataReaders on the same connection. Only problem is when you try to do this you get an exception saying “There is already a datareader associated with the command that must be closed”. But hey, my scenario dictates that I have that flexibility. Well at this time it feels that my post is about connection strings but hey why not.

"DataSource=MyDbServer;Initial Catlogue=MySchool;Integrated Security=SSPI;     MultipleActiveResultSets=True”

From this point onwards you’re free to open as many data readers (I believe) as you want on the same SQL connection.

Command Behaviours

Now I am a big fan of defensive coding and policy injection. Command Behaviours for me are like policy injection in that I define my policies on how I want this command to behave in certain events. There is no one set of rules again on how all commands should behave or else we wouldn’t have been provided with this flexibility. So my tip here is do familiarise yourself with properties on the Command Behaviour class. http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

Command Builders

Ever wondered how typed datasets generated the insert, update, and delete statements for you. Generation Templates? String Builders? Internal Logic? Black Voodoo? Again one of the most missed classes among junior developers are command builders. Now a command builder is not a code generation tool as such but by right it can be used as one. Examine the following code and it’s output.

private static void Main()

{

  const string connectionString = "Data Source=localhost;Initial Catalog=School;Integrated Security=True";

  var connection = new SqlConnection(connectionString);



  using (connection)

  {

    connection.Open();



    SqlCommand command = connection.CreateCommand();

    command.CommandType = CommandType.Text;

    command.CommandText = "SELECT * FROM dbo.School";



    var adapter = new SqlDataAdapter(command);

    var commandBuilder = new SqlCommandBuilder(adapter);



    Console.WriteLine(commandBuilder.GetInsertCommand().CommandText + Environment.NewLine);

    Console.WriteLine(commandBuilder.GetUpdateCommand().CommandText + Environment.NewLine);

    Console.WriteLine(commandBuilder.GetDeleteCommand().CommandText + Environment.NewLine);



    Console.ReadKey();

  }

}

The output:

As shown the command builder generated the select, insert, update and delete commands for me. While the select statement passed in has to be simple and usually dealing with one table, but still the generated code can be used in many of code generation tools I see today plus it’s used by the typed dataset designer.

Asynchronous Data Operations

Now when working with transactions, developers always tend to keep the transactions on the lowest tier possible above their data repositories which is totally correct. On the other hand they tend to work with threading on the highest level away from their data repositories because asynchronous calls with WCF or the background worker are much easier. While the most common guideline for this scenario is IT DEPENDS I still think that when dealing with multiple threads doing various data operations, the most efficient way is to use your ADO.NET objects to handle those. Let’s take the following scenario:

1. I am reading some data from my school table.
2. I want a file to be created after all the data is read.
3. I don’t want the main\UI thread to be affected by the entire procedure and the whole thing is just a straight data dump with no business logic.

To me this is a data operation that I don’t even need to handle outside my repository. Remember repositories sit in your infrastructure tier where you persist and read from databases, file, web services etc..

private static void Main()

{

  const string connectionString = "Data Source=XPSEXTREME;Initial Catalog=School;Integrated Security=True;Asynchronous Processing=True";

  var connection = new SqlConnection(connectionString);



  connection.Open();



  SqlCommand command = connection.CreateCommand();

  command.CommandType = CommandType.Text;

  command.CommandText = "SELECT * FROM dbo.School";



  Console.WriteLine("Starting Read:");

  command.BeginExecuteReader(new AsyncCallback(WriteToFile), command);



  Console.ReadKey();

}



private static void WriteToFile(IAsyncResult ar)

{

  var command = (SqlCommand) ar.AsyncState;

  SqlDataReader dataReader = command.EndExecuteReader(ar);

  command.Connection.Close();

  command.Connection.Dispose();



  //Write reader content to file



  Console.WriteLine("Data is ready to be written to file.");

}

Do note that you have to apply the Asynchronous Processing=True property in the connection string for successful asynchronous calls.

Batch Updates

One of the most annoying things in DataAdapters is that the default behaviour on executing multiple commands is:

1. Open Connection.

2. Execute a single command.

3. Close Connection.
4. Goto 1 :)

A very useful property that again many of us miss is the BatchUpdateSize property on the DataAdapter. To update records at once in the database with a single open connection you define BatchUpdateSize to 10 records at once. The property is set to 1 by default.

var adapter = new SqlDataAdapter(command) {UpdateBatchSize = 10};

Now that’s a neat solution but if you are using SQL 2008 why stop there. By creating the following stored procedure and utilising the new Table Value parameters I can do batch updates even more efficiently.

CREATE TYPE dbo.StudentInput AS TABLE

  ( StudentId uniqueidentifier, Name nvarchar(100), SchoolId uniqueidentifier )

GO



CREATE PROCEDURE UpdateStudents (@students dbo.StudentInput READONLY)

AS

BEGIN

  INSERT INTO dbo.Student(StudentId, Name, SchoolId)

  SELECT ns.StudentId, ns.Name, ns.SchoolId FROM @students AS ns;

END



GO

Then I execute my command passing in a datatable parameter with multiple values.

private static void Main()

{

  const string connectionString = "Data Source=localhost;Initial Catalog=School;Integrated Security=True";

  var connection = new SqlConnection(connectionString);



  var schoolGuid = new Guid("c7a92ea0-9fd5-42eb-9949-f16b3644cc6d");



  var dataTable = new DataTable();

  dataTable.Columns.Add("StudentId", typeof (Guid));

  dataTable.Columns.Add("Name", typeof (String));

  dataTable.Columns.Add("SchoolId", typeof (Guid));



  dataTable.Rows.Add(new object[] {Guid.NewGuid(), "Omar Besiso", schoolGuid});

  dataTable.Rows.Add(new object[] {Guid.NewGuid(), "Paul Stovell", schoolGuid});

  dataTable.Rows.Add(new object[] {Guid.NewGuid(), "Brenden Crowne", schoolGuid});



  using (connection)

  {

    connection.Open();



    SqlCommand command = connection.CreateCommand();

    command.CommandType = CommandType.StoredProcedure;

    command.CommandText = "dbo.UpdateStudents";



    var parameter = new SqlParameter("@students", SqlDbType.Structured) {Value = dataTable};

    command.Parameters.Add(parameter);



    command.ExecuteNonQuery();



    Console.ReadKey();

  }

}

 

DataViews

Ok by now the you must be bored so I will leave you with one last very simple clarification. A data view is a view built on a single datatable showing a subset or all of the tables columns. It’s not a view that you can use to join multiple datatables in the same dataset. This was a common joke at work recently when we were assigned to review some other mobs work so thought I’d share :)

Conclusion

Like anything make sure you know your frameworks internals and the simple\efficient things you can do. While in the world of ORMs most of this post doesn’t bother you much, trust me it will help when your manager comes screaming down your neck “Why is this not working? Why is it slow? etc.”. ADO.NET is a great framework so ride it till the wheels fall off :)