Archive for the 'Programming' Category

SkyDrive: Why aren’t thou nicer to me? or “How to crawl pages and download all images using C#”

Ok, SkyDrive is still a baby, and personally I’ve used other services providing file space in the cloud and enjoyed them a little better. But this post isn’t about if SkyDrive is good or bad, it’s just about a missing feature that is very painful. Someone wanted to share some fotos, uploaded them to SkyDrive and all I wanted was to download them all to my PC. Tough look, you can click on each and every image to get to the preview page, where you click on the preview picture to then finally get at the actual picture. Multiply that by about 100. I have better things to do than waste my time on that.

So a Dev does what he does best, fires up Visual Studio 2008 and hacks away (did I just say I had something better to do - well I lied partially, but before I go off to do that, there is always time for some good ol’ C#).

I’ve posted it here not as a finished utility (there are no binaries) but as a small sample. Using WebClients, RegEx and some other stuff it downloads the list page of the SkyDrive folder, fetches the preview page and then downloads the actual image to a folder on the hard disk. Not really rocket science and of course there are a few quirks (no real error handling for example), but it’s just a sample. Feel free to extend as you wish, don’t blame me if it starts downloading Gigabytes of files overnight, because you accidentally crawled a HoneyPot. (And yes, it only downloads jpgs at the moment. I didn’t need any other types.)

May those SkyDrive bytes be with you…


/**********************************************************************************
 *
 * Example Application for crawling web pages and downloading images.
 *
 * This code works if you pass in a SkyDrive Folder Url (http://.... /browse.aspx/...)
 * and will download any jpg images it finds in there.
 *
 * Permission to use, copy, modify, distribute and sell this software and its
 * documentation for any purpose is hereby granted without fee.
 * I make no representations about the suitability of this software for any purpose.
 * It is provided "as is" without express or implied warranty.
 *
 * Alex Duggleby - 24.05.08 - V0.9 - http://alexduggleby.com
 *
 **********************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Text.RegularExpressions;
using System.IO;
using System.Web;
using System.ComponentModel;

namespace Tools.SkyDrive.DownloadAll
{
    class Program
    {
        // Used for tracking how many items we have left
        private static int _wcInnerCount = 0;
        private static int _wcInnerCompleted = 0;

        // We have to start somewhere
        private static Uri _uriStart;

        // Work we have already done
        private readonly static List<string> _urisCrawled = new List<string>();
        private readonly static List<string> _imagesDownloaded = new List<string>();

        // Download images to?
        private readonly static DirectoryInfo _diDownloadTo = new DirectoryInfo(Path.Combine(Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Personal), "Downloads"),"Images"));

        // This finds urls in the page
        private readonly static Regex _regexUrl = new Regex("href\\s*=\\s*(?:(?:\\\"(?<url>[^\\\"]*)\\\")|(?<url>[^\\s]* ))");

        // This finds the open url in the image page
        private readonly static Regex _regexUrlOpen = new Regex("href\\s*=\\s*(?:(?:\\\"(?<url>[^\\\"]*)\\\")|(?<url>[^\\s]*)) title=\\\"Open\\\""); 

        /// <summary>
        /// Takes the url to a skydrive folder page and downloads all jpg images.
        /// </summary>
        static void Main(string[] args)
        {
            // Usage check
            if (args.Length != 1)
            {
                Console.WriteLine("Usage: App.exe http://theUrlToThe/SkyDrive/FolderPage");
                return;
            }

            try
            {
                // First parameter is url
                _uriStart = new Uri(args[0]);
            }
            catch (Exception _ex)
            {
                Console.WriteLine("Invalid Url. " + _ex.Message);
                return;
            }

            // Make sure download directory exists
            if (!_diDownloadTo.Exists) _diDownloadTo.Create();

            using (WebClient _wc = new WebClient())
            {
                // This is the index with all the images
                string _pageContents = _wc.DownloadString(_uriStart);

                // Each image has a preview page, so we get the url to that, before we get the url to the actual image
                foreach (Match _matchUrlToImagePage
                    in _regexUrl.Matches(_pageContents))
                {
                    Uri _uriToImagePage =
                        new Uri(_uriStart, HttpUtility.HtmlDecode(_matchUrlToImagePage.Groups["url"].Value));

                    CrawlPreviewPage(_uriToImagePage);
                }
            }

            // Wait for the async web clients to complete...
            while (_wcInnerCompleted < _wcInnerCount)
            {
                Console.WriteLine("Wait for images to complete...");
                Console.ReadLine();
            }

            Console.WriteLine("Should be finished!");
            Console.ReadLine();
        }

        /// <summary>
        /// Parses the preview page and finds the actual image link
        /// </summary>
        /// <param name="uriToImagePage">The url to the preview page</param>
        /// <returns></returns>
        private static void CrawlPreviewPage(Uri uriToImagePage)
        {
            using (WebClient _wc = new WebClient())
            {
                if (!_urisCrawled.Contains(uriToImagePage.ToString()))
                {
                    _urisCrawled.Add(uriToImagePage.ToString());

                    if (uriToImagePage.ToString().ToLower().EndsWith(".jpg"))
                    {
                        string _pageContents = _wc.DownloadString(uriToImagePage);

                        // Find the image we want to download... There should be
                        // only one link with title="Open" in it.
                        foreach (Match _matchImage in _regexUrlOpen.Matches(_pageContents))
                        {
                            Uri _uriToImage = new Uri(_matchImage.Groups["url"].Value);

                            DownloadImage(_uriToImage);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Downloads async'ly an image from a Uri
        /// </summary>
        /// <param name="uriToImage">The uri to download</param>
        private static void DownloadImage(Uri uriToImage)
        {
            // Output the url
            Console.WriteLine("{0}{1}", uriToImage.ToString(), Environment.NewLine);

            if (!_imagesDownloaded.Contains(uriToImage.ToString()))
            {
                _imagesDownloaded.Add(uriToImage.ToString());
                string _lowerUrl = uriToImage.ToString().ToLower();

                // Simple checking
                if (_lowerUrl.EndsWith(".jpg") &&
                   (!_lowerUrl.Contains("browse")) &&
                   (!_lowerUrl.Contains("self")))
                {
                    // HtmlDecode here because some urls have encoded characters
                    string _localFilename = HttpUtility.HtmlDecode(
                        uriToImage.Segments[uriToImage.Segments.Length - 1]);

                    // Create a valid local filename
                    Path.GetInvalidPathChars().ToList().ForEach(
                        c => _localFilename = _localFilename.Replace(c, '_'));

                    Console.Write("Downloading {0}...{1}", _localFilename, Environment.NewLine);

                    // Create a seperate web client for each image (uses async, and you can't
                    // issue two downloads at the same time for the same client). Of course
                    // here we should be using some kind of pooling but this is the quickest
                    // way to do it.
                    using (WebClient _wcInner = new WebClient())
                    {
                        _wcInnerCount++;
                        _wcInner.DownloadFileAsync(uriToImage, Path.Combine(_diDownloadTo.ToString(), _localFilename));
                        _wcInner.DownloadFileCompleted += new AsyncCompletedEventHandler(_wcInner_DownloadFileCompleted);
                    }
                }
            }
        }

        // Is fired when a download complete. We output status and check if we are finished!
        private static void _wcInner_DownloadFileCompleted(object sender, AsyncCompletedEventArgs e)
        {
            // Increase the completed counter
            _wcInnerCompleted++;

            // Ok, we could do some more extensive checking, this could trigger
            // even if there are still items to download... but hey, it's just a
            // quick utility!
            if (_wcInnerCompleted == _wcInnerCount)
            {
                Console.WriteLine("{0}{1}{2}", Environment.NewLine, "Finished all files!", Environment.NewLine);
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("File {0} of {1} completed!", _wcInnerCompleted, _wcInnerCount);
            }
        }
    }
}

Off-Topic: T-SQL: Replace all occurrences in all columns in all tables?

Update: You can download the sql file here: http://in.solit.us/archives/download/149837 (saves you cleaning up the copy-pasted code from below… oh and hello experts-exchange-users)

Have you ever been asked to write something where you knew from the start that it could possibly wreak havok? Well I did today and since I haven’t posted in a while I thought I’d quickly share. Don’t worry I’ll be back blogging Sync, MVC and more soon (and hopefully tell you why I was gone).

Without talking too much about the real scenario that this was needed for, let me just say there is a large database that stores configuration data including a set of connection strings in multiple places spread across numerous tables. And our admins moved the database but didn’t know where exactly to change these config strings. I was the only dev left in the office at the time and they turned to me asking me if I could write a script that “replaced all occurrences of stringA with stringB throughout the whole database”.

Yikes. It sounds like trouble, you know it’s gonna hurt and I don’t want to be anywhere nearby when they press F5 to run the script. Ok, nevertheless I put together a little script that did just that, sent it to them and let’s see what happens next week. They have backups so even if everything goes wrong we can quickly restore things.

Here’s what I came up with. It’s not rocket science, but does the job. Use the script at your own risk (I didn’t find anything comparable on the web so maybe it will come in handy to someone). It shows some interesting usage of the system tables that I use now and again for things like this (when I don’t have my beloved SQLSMO). Have fun and use with caution (I can’t say that enough!)


------------------------------------------------------------
-- Name: STRING REPLACER
-- Author: ADUGGLEBY
-- Version: 20.05.2008 (1.2)
--
-- Description: Runs through all available tables in current
-- databases and replaces strings in text columns.
------------------------------------------------------------

-- PREPARE
SET NOCOUNT ON

-- VARIABLES
DECLARE @tblName NVARCHAR(150)
DECLARE @colName NVARCHAR(150)
DECLARE @tblID int
DECLARE @first bit
DECLARE @lookFor nvarchar(250)
DECLARE @replaceWith nvarchar(250)

-- CHANGE PARAMETERS
SET @lookFor = 'virtual2'
SET @replaceWith = 'virtual3'

-- TEXT VALUE DATA TYPES
DECLARE @supportedTypes TABLE ( xtype NVARCHAR(20) )
INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('varchar','char','nvarchar','nchar','xml')

-- ALL USER TABLES
DECLARE cur_tables CURSOR FOR
SELECT SO.name, SO.id FROM SYSOBJECTS SO WHERE XTYPE='U'
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @tblName, @tblID

WHILE @@FETCH_STATUS = 0
BEGIN
	-------------------------------------------------------------------------------------------
	-- START INNER LOOP - All text columns, generate statement
	-------------------------------------------------------------------------------------------
	DECLARE @temp NVARCHAR(4000)
	DECLARE @count INT
	SELECT @count = COUNT(name) FROM SYSCOLUMNS WHERE ID = @tblID AND
		XTYPE IN (SELECT xtype FROM @supportedTypes)

	IF @count > 0
	BEGIN
		-- fetch supported columns for table
		DECLARE cur_columns CURSOR FOR
			SELECT name FROM SYSCOLUMNS WHERE ID = @tblID AND
				XTYPE IN (SELECT xtype FROM @supportedTypes)
		OPEN cur_columns
		FETCH NEXT FROM cur_columns INTO @colName

		-- generate opening UPDATE cmd
		SET @temp = '
	PRINT ''Replacing ' + @tblName + '''

	UPDATE ' + @tblName + ' SET
		'
		SET @first = 1

		-- loop through columns and create replaces
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF (@first=0) SET @temp = @temp  + ',
		'
			SET @temp = @temp  + @colName
			SET @temp  = @temp  + ' = REPLACE(' +  @colName + ','''
			SET @temp  = @temp  + @lookFor
			SET @temp  = @temp  + ''','''
			SET @temp  = @temp  + @replaceWith
			SET @temp  = @temp  +  ''')'

			SET @first = 0
			FETCH NEXT FROM cur_columns INTO @colName
		END

		PRINT @temp

		CLOSE cur_columns
		DEALLOCATE cur_columns
	END
	-------------------------------------------------------------------------------------------
	-- END INNER
	-------------------------------------------------------------------------------------------

	FETCH NEXT FROM cur_tables INTO @tblName, @tblID
END

CLOSE cur_tables
DEALLOCATE cur_tables

dot.net Magazin 5.08 Sync Framework Article and File Sync with VB.net

This morning I finally got my authors copy of the dot.net Magazin that I wrote an article on Microsoft Sync Framework. It’s in german, 7 pages long and you can buy it tomorrow.

Anyhow, I got an email this morning asking about one of the code listings and asking if there was anything different to do in VB.net. It’s been a while since I last did VB.net (I actually did more VB6 than VB.net) but I converted the sample from the magazine to VB.net. Just remember to reference Microsoft.Synchronization and Microsoft.Synchronization.Files.

(By the way I still haven’t got round to looking at CTP2 of the Sync Framework. All this stuff (and the article) applies to CTP1. Maybe there was a breaking change in CTP2 with regard to the FileSyncProvider. I must get round to looking at CTP2 a.s.a.p.)

Here is the code:


Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Files
Imports System.IO

Module Module1

    Sub Main()

        'Define directories
        Dim _sourceDir As String = "c:\temp\1"
        Dim _destDir As String = "c:\temp\2"

        'Define system ids
        Dim _sourceId As SyncId = New SyncId(New Guid("A4715EAF-341E-4ebf-90C3-71E8644CA6E8"))
        Dim _destId As SyncId = New SyncId(New Guid("FF766014-0DA4-401d-BDA1-0679326ECF96"))

        'Exclude some patterns
        Dim _syncFilter As FileSyncScopeFilter = New FileSyncScopeFilter()
        _syncFilter.AttributeExcludeMask = FileAttributes.Hidden Or FileAttributes.System
        _syncFilter.FileNameExcludes.Add("DoNotCopy.txt")
        _syncFilter.SubdirectoryExcludes.Add("DoNotCopy")

        'Setup options (makes sure files aren't deleted for ever)
        Dim _syncOptions As FileSyncOptions = FileSyncOptions.RecycleDeletes Or FileSyncOptions.RecycleOverwrites

        'Do the sync
        Using _sourceProvider As FileSyncProvider = New FileSyncProvider(_sourceId, _sourceDir, _syncFilter, _syncOptions)
            Using _destinationProvider As FileSyncProvider = New FileSyncProvider(_destId, _destDir, _syncFilter, _syncOptions)

                Dim _syncAgent As SyncAgent = New SyncAgent()
                _syncAgent.LocalProvider = _sourceProvider
                _syncAgent.RemoteProvider = _destinationProvider
                _syncAgent.Direction = SyncDirection.UploadAndDownload
                _syncAgent.Synchronize()

            End Using
        End Using

    End Sub

End Module

TriggerCollection does not contain an Add method.

This is the kind of the thing you don’t want to see when you check out a project from SVN that should be compiling. It took a moment of thinking and a bit of reflectoring to find that the method was internal in the dll I was using:

SQL SMO TriggerCollection in reflector

The TriggerCollection is part of the table object in SQL Management Objects. I had the identical project compiling on a different PC. The funny thing was that the versions of the dll were identical. Only in reflector did I see a different version Guid:

// Assembly Microsoft.SqlServer.Smo, Version 9.0.242.0
Location: c:\Programme\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Name: Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Type: Library

// Module Microsoft.SqlServer.Smo.dll
Version: b74f0ed4-1269-420b-b67e-8bbae0c9c035
Location: c:\Programme\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
Size: 1555232 Bytes

This led me to think about the service pack level installed and I noticed you need Service Pack 2 to be able to use the Add method publically. (At least it wasn’t the other way round, they could had made it internal in the newer service pack *G*. Thankfully not.) Case solved, back to getting through this pile of work…

A Reusable Secure Login Form

For the impatient, source and binaries and quick explanation available at: http://code.msdn.microsoft.com/UtilsCommonViews/

How many of the last ten projects you implemented had a login form of some kind? Thanks to all those wonderful apis that you coded applications against, probably the majority of them did. Now try to remember how many of those login forms used strings for storing passwords in memory and how many saved the password to a settings file in plain text. I know most of my small utilities did and I’m not proud of it, but it happened and I’m almost sure you have some of those lying around in your src folder.

Anyway, today I had a couple of hours of spare time and decided to implement a reusable secure login form. My goals were to use SecureString for storing the password in memory and encrypt the username and password in a settings file for storing them between the sessions. I started off with a little research about the components I’d need to use.

SecureString is a class that was introduced in .NET Framework 2.0. Why not simply use a string for storing a password? Well let’s have the MSDN do the explaining:

An instance of the System.String class is both immutable and [...] cannot be programmatically [...] deleted from computer memory. Consequently [...] there is a risk the information [stored in it] could be revealed after it is used [...].

A SecureString object is similar to a String object in that it has a text value. However, the value of a SecureString object is automatically encrypted, can be modified until your application marks it as read-only, and can be deleted from computer memory by either your application or the .NET Framework garbage collector.

That’s a great scenario for our password. Next up I found this great secure textbox control that handles the user input into a SecureString object. The original control and source can de downloaded at: http://www.theglavs.com/DownloadItem.aspx?FileID=46. (Thanks to Glav and Dominik Zemp who created or extended the control.)

Then I wrote this simple extension method to get the characters of the contents of the SecureString because it can get quite dirty to do it inline. It uses requires execution of unmanaged code and some marshalling.


public static Char[] GetCharacters(this SecureString secureString)
{
	if (secureString == null)
		throw new ArgumentNullException("secureString");

	lock (secureString)
	{
		char[] _chars = new char[secureString.Length];
		IntPtr _ptrToChars = IntPtr.Zero
		try
		{
			_ptrToChars = Marshal.SecureStringToBSTR(secureString);
			Marshal.Copy(_ptrToChars, _chars, 0, secureString.Length);
		}
		finally
		{
			if (_ptrToChars != IntPtr.Zero)
				Marshal.ZeroFreeBSTR(_ptrToChars);
		}

		return _chars;
	}
}

The second feature was to encrypt the username and password in a file. I looked at encrypting the configuration file but decided against that path and went with encrypting the data using the ProtectedData API (in System.Security.Cryptography). It’s a simple call to the static method:


ProtectedData.Protect(byte[] data, byte[] entropy, DataProtectionScope);

The entropy specifies a kind of salt for your application and the DataProtectionScope can be set so that only the current user can decrypt the data or to local machine. The library will save a file UserData.bin to the local application directory (including the calling assemblies name). Additionally I decided to encrypt the file using the FileInfo.Encrypt method. This works only on NTFS, so the library will currently only work on NTFS systems. Now that I’ve written that sentence I think I’ll make that optional in the next release.Which brings me to the release and the source code. I want to try out MSDN code gallery so I’ve published the project here: http://code.msdn.microsoft.com/UtilsCommonViews/

Using the library is very simple:


LoginController _loginController = new LoginController();
_loginController.GetCredentials();

string _username = _loginController.Username;
char[] _password = _loginController.Password;
// _password needs to be zero'ed a.s.a.p. after usage

That last comment is important. If you don’t zero out that char array, the password will be floating around in memory.

Comments, suggestions are of course welcome! Here or in the MSDN code gallery.

Note: The project uses another utility library (Utils.Extensions) of mine that I’ve built-up over the last weeks that contains a few extension methods for common stuff I was missing in the BCL. The source is not clean enough yet for publishing but it’s in the pipe. For now it’s only available in this project as a release dll.

LINQ to SQL for SQL Compact Edition - Did you know?

You can’t actually drag and drop a SQL Compact Edition 3.5 database to the LINQ to SQL designer. But it is very easy to create the classes and work beautifully. Fire up a VS08 prompt and find the SDF file that your database is stored in, then run the following command:

Prompt: SqlMetal /code:DotNetBooksDB.cs /pluralize /namespace:Books.Client.Library.Store.SqlCe dotNetBooks.sdf
Microsoft (R) Database Mapping Generator 2008 version 1.00.21022
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.

The parameters are simple.

/code = the path where you would like to save the generated code.

/pluralize = changes the table book into books so you can write code like this: var x = from b in _ctx.Books (just looks a bit nicer)

/namespace = which namespace would you like your classes to have

and finally the path to the sdf file.

There is no output, but if you run the command your csharp should have been generated and just include that file into your project (and switch the project to .net Framework 3.5).

Turning a class library project into a MSTest project (or using MBUnit, MSTest and other frameworks in one project)

I know it’s been a while. I’ve been busy talking about SyncFramework at the VSOne conference, helping a bit of at the Sync Framework forums and lot’s of studying going on. I promise I have some new longer posts coming soon.

Yesterday I wrote a unit test that I wanted other members on my team to execute this week to check for some problems. Where’s the problem you may ask? Well I personally like TestDriven.net and MBUnit whereas my colleagues rely on the testing suite in Team System. Fair enough, usually we don’t exchange that many tests (and no - I’m sorry about that myself actually - we don’t have continuous integration. But even if we did that probably wouldn’t be a major problem).

Anyhow I created my class library project and wrote my test. Worked great on my computer (don’t hit me for saying that). I then remembered that it needed to run under MSTest on the other dev machines. Fair enough, can’t be that hard. I referenced

Microsoft.VisualStudio.QualityTools.UnitTestFramework

and gave my methods additional attributes. (The top ones are MSTest, the bottom ones are MBUnit.)


[Microsoft.VisualStudio.TestTools.UnitTesting.TestClass]
[TestFixture]
public class EvaluatorTestSuite 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestInitialize()]
[TestFixtureSetUp()]
public void SetUp() 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestCleanup()]
[TestFixtureTearDown()]
public void TearDown() 

[Microsoft.VisualStudio.TestTools.UnitTesting.TestMethod]
[Test]
public void Evaluate_ValidEvaluationModel_ReturnsValidEvaluationResult()

Then I went to the Test menu in Team System and wanted to execute all tests in this solution. But VS told me there aren’t any. Basically I figured out the test projects must have some special attribute that make them “testable”. They are basically just class libraries but they have a special Guid in their project file. Not quite sure why Microsoft would do that, seeing as they could just check for the attributes on the classes like TestDriven.net does, but ok. This is a quick solution to turning a class library into a MSTest testable project. Open the CSproj file of the class library project and just before the first propertygroup closes insert the following line:

<ProjectTypeGuids>

{3AC096D0-A1C2-E12C-1390-A8335801FDAB};

{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}

</ProjectTypeGuids>

Those two guids make it MSTest testable. And no, I didn’t have time to figure out why there are two and what they do exactly. If anybody cares to explain, I would love to hear about it. The file should look something like this:


<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProductVersion>9.0.21022</ProductVersion>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectGuid>{5273D187-1B0D-40DC-A415-36761976438B}</ProjectGuid>
    <OutputType>Library</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>TheGrandestProjectOfThemAll</RootNamespace>
    <AssemblyName>TheGrandestProjectOfThemAll</AssemblyName>
    <TargetFrameworkVersion>v3.5</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
    <ProjectTypeGuids>{3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
  </PropertyGroup>
Technorati Tags: ,,

Learn VB.net - German Screencasts Series

A bit out of the ordinary (I am in between writing up some more Sync Framework articles) but a fellow student partner Mario from Linz has put together a series of screen casts to get you started on Visual Basic .NET. He covers the basics but also some of the new VB.net features. If you are interested and understand german, take a look at: http://www.vb-magazin.de/casts/video_index.aspx

Sync Framework Tutorial - Part #5 - Some client provider schema details

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

In the last part (part 1, part 2, part 3, part 4) we saw how to setup the synchronization between a local SQLce database and a server database.

The SyncAgent was setup with two providers, one for the server which had SyncAdapters to specify which tables on the server side are used, which columns hold the metadata. Another for the client that used a local compact edition database and a number of SyncTable objects to setup the sync.

Upon Synchronization the SyncAgent downloads the schema from the server provider, creates the tables on the client if required and transfers changes between the databases since the last synchronization.

And it works, but you will soon notice something is missing.

The client schema

The server provides a schema so that the client can create an identical set of tables to use on the client. But if you attempt to insert a row on the client you will notice that the default values are missing. The primary keys are not specified and foreign key constraints on the client are not available.

This is not required but it would be nice to have them anyway. But there is only one way to do it. You have to manually alter the tables after they have been created by the client sync provider.

Adapting the client schema after it has been automatically created

The SqlCeClientSyncProvider provides a number of events, two of which are: CreatingSchema and SchemaCreated.

In the CreatingSchema handler you should set the primary key for each table using the CreatingSchemaEventArgs:

e.Schema.Tables[_mySchema.TableName].Columns[_mySchema.PK].RowGuid = true;

And you will also want to add foreign key constraints:

e.Schema.Tables[_mySchema.TableName].ForeignKeys.
                        Add(
                            _fkName,
                            _fkSchema.ParentTable,
                            _fkSchema.ParentColumn,
                            _fkSchema.ChildTable,
                            _fkSchema.ChildColumn);

The second event handler for SchemaCreated is used to run custom sql commands inside the same transaction used by the sync provider for creating the tables ensuring data integrity:

SqlCeCommand _cmdAlterTableDef = new SqlCeCommand();
_cmdAlterTableDef.Connection = (SqlCeConnection)e.Connection;
_cmdAlterTableDef.Transaction = (SqlCeTransaction)e.Transaction;
_cmdAlterTableDef.CommandText = _alterCmdString;
_cmdAlterTableDef.ExecuteNonQuery();

Summary

So far we have created an application that synchronizes data between a client database and a server database and done taken a look at some nice-to-have schema changes on the client.

Next-up is the distributed scenario where the server provider is called via a WCF proxy. Hopefully I can post that before Christmas.

Sync Framework Tutorial - Part #4 - Thou hast to sync thy data (the client application)

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

In this part of the tutorial (part 1, part 2, part 3) we will take a look at a fully functional client application that uses the sync framework to synchronize to the server database. It requires the client application to directly connect to the database. In the next part we will see how to connect via a service layer.

The solution

SF_Solution

Any sync framework solution will need at least a sync agent and two sync providers. The sync agent takes care of setting up the providers with the sync agent and configures the options. The providers are database providers. The client is a special SQLce version and the Server provider is the standard DbProvider. When the application wishes to synchronize its data, it calls the Synchronize method on the SyncAgent and it will take care of the rest.

Our goal is to synchronize data changes between server and client regardless of who makes the changes. This functionality is invoked manually from the client.

ActionSyncAgent.Setup()

// Providers
ActionClientSyncProvider _clientSyncProv = ActionClientSyncProvider.Setup();
ActionServerSyncProvider _serverSyncProv = ActionServerSyncProvider.Setup();
 
// Sync Agent
this.LocalProvider = _clientSyncProv;
this.RemoteProvider = _serverSyncProv;
 
// Tables
SyncTable _syncTableAction;
SyncTable _syncTableProject;
SyncTable _syncTableUser;
 
SyncGroup _syncGroupMain = new SyncGroup("AllSync");
 
_syncTableAction = CreateStandardSyncTable("Action", _syncGroupMain);
_syncTableProject = CreateStandardSyncTable("Project", _syncGroupMain);
_syncTableUser = CreateStandardSyncTable("User",_syncGroupMain);
 
_clientSyncProv.Configuration.SyncTables.Add(_syncTableAction);
_clientSyncProv.Configuration.SyncTables.Add(_syncTableProject);
_clientSyncProv.Configuration.SyncTables.Add(_syncTableUser);
 
_serverSyncProv.SetupSyncTableAdapters(
  new[] { _syncTableAction, _syncTableProject, _syncTableUser }); 
 

Start by creating an instance on ActionClientSyncProvider and ActionServerSyncProvider which have both been setup (details on those later). Both are attached to the respective provider properties on the sync agent.

Next up we specify which tables we want to synchronize. We create a sync table for each table and attach them to the main sync group (to ensure foreign key constraint are handled correctly) using the CreateStandardSyncTable method:

SyncTable _syncTable;
_syncTable = new SyncTable(tableName);
 
_syncTable.CreationOption = 
   TableCreationOption.DropExistingOrCreateNewTable;
   
_syncTable.SyncTableTransferOption = 
   SyncTableTransferOption.Bidirectional;
   
_syncTable.SyncGroup = _syncGroupMain;
return _syncTable;

We specify the CreationOption property as DropExistingOrCreateNewTable from the TableCreationOption enumeration.

The available options are:

CreateNewTableOrFail: creates the table on the client and throws an exception if it exists.

DropExistingOrCreateNewTable: creates the table but drops any existing tables with the same name.

TruncateExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists is removes all rows on the client table.

UploadExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists it will upload all rows from the table.

UseExistingTableOrFail: Throws an exception it the client table does not exist.

The SyncTableTransferOption specifies in which direction data can flow. There are four options: UploadOnly, DownloadOnly and Bidirectional are similar. They download a schema and then submit changed data in one or both directions. The fourth option is Snapshot which downloads the set from the server on every synchronization and replaces the client data.

The last call in the SyncAgent’s Setup method is a call to the server provider to set up it’s SyncAdapters using the SyncTables. SyncTable objects are not directly related to SyncAdapter objects but in this case the SyncAdapters use tables that share the same name as the sync tables. For this reason they are passed along:

Setting up SyncAdapters

public void SetupSyncTableAdapters(SyncTable[] syncTables)
{
    foreach (SyncTable _syncTable in syncTables)
    {
        this.SyncAdapters.Add(
              SyncUtil.CreateStandardSyncAdatper(this, _syncTable));
    }
}

The SyncUtil.CreateStandardSyncAdapter method handles the details. They are similar for all my tables therefore extracted into a utility method.

 

.

public static SyncAdapter CreateStandardSyncAdatper
  (ActionServerSyncProvider prov, SyncTable syncTable)
{
    SyncAdapter _syncAdapterAction = 
      SyncUtil.CreateStandardBuilder(prov, syncTable.TableName).ToSyncAdapter();
      
    _syncAdapterAction.TableName = syncTable.TableName;
    return _syncAdapterAction;
}
 
public static SqlSyncAdapterBuilder CreateStandardBuilder
  (ActionServerSyncProvider prov, string tableName)
{
    SqlSyncAdapterBuilder _builder = 
       new SqlSyncAdapterBuilder(prov.Connection as SqlConnection); 
 
    _builder.TableName = tableName;
    _builder.TombstoneTableName = _builder.TableName + "_Archive";
    _builder.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;
    _builder.CreationTrackingColumn = "InsertTimestamp";
    _builder.UpdateTrackingColumn = "UpdateTimestamp";
    _builder.DeletionTrackingColumn = "DeleteTimestamp";
    _builder.CreationOriginatorIdColumn = "InsertID";
    _builder.UpdateOriginatorIdColumn = "UpdateID";
    _builder.DeletionOriginatorIdColumn = "DeleteID";
 
    return _builder;
}
 

The SyncAdapter is created using a builder class. The class (SqlSyncAdapterBuilder) requires the connection to the database.

Be aware: The connection property on the provider is inherited from the base class (DbServerSyncProvider) and is of type IDbConnection. But the SqlSyncAdapterBuilder expects a SqlConnection. We are inferring that the property will return a SqlConnection but I consider this bad style and usually this would be a warning light for a design problem. But since this is only an example I’ll just continue.

The builder properties specify the names of the columns, of the table and the option for transferring which needs to correspond to the SyncTables.

The client provider

The client provider basically just inherits from the SqlCeClientSyncProvider and provides a connection string to the local SQLce database file. Well not quite, but that’s a part of the next part of the tutorial.

Next Page »


Subscribe / Search

Imagine Cup 2009 - Egypt
msplogo_small.jpg
mcprgb.png

 

July 2008
M T W T F S S
« Jun    
 123456
78910111213
14151617181920
21222324252627
28293031  

Blog Stats

  • 10,824 hits