Generate Script & Export Data From SQL CE (Compact Edition) 3.5

Several months ago I migrated my family financial and expense information from excel file to SQL CE 3.5.
SQL CE is indeed good enough for data entry purpose, but after a while, I felt that it is not good enough for reporting purpose.
So I decided that it's better to use the SQL Server Express instead.

Now the first problem that I had : How to migrate the tables along with the data from SQL CE 3.5 to SQL Server Express?

I have been googling around for a while, but haven't really found a good one for this, which make me realized that there are more ways to get the data from SQL Server to SQL CE but not the other way around duhh :|

So later, I decided to code it myself ;)

When browsing around the net, I found about Information Schema View that is available in SQL Server, and fortunately it is also available in the SQL CE 3.5 ;) (before I found out about the view, I was using Linq Attributes to generate the table creation ouccch)
So now by using the view, I generate the script for the SQL CE 3.5 database file (.sdf).

In summary, the code will :
1. Generate Table Creation Script
2. Generate Insert Statement for the data in all of the tables
3. Generate script to assign the Primary Keys for the tables
4. Generate script to assign the foreign keys / references for the tables

** Update 5. Generate script to assign Indexes for the tables

6. Save the script into the output file

So here's a little peek of how to use the tool, I'm using Northwind.sdf file in the example below :
There are 2 parameters to pass in ;
1. Connection String to the sdf file
2. Output File Name for the script

Note : If you get any access denied when trying to open the Northwind.sdf file, here is why and the solution.

After the Northwind.sql file has been generated :
1. Create the SQL Server database file (.mdf)
2. Open the Northwind.sql script into your SQL Server Management Studio or Visual Studio (Since I'm currently using SQL Epxress 2008 CTP, there is no management studio yet for now) and just execute them, if everything's ok (finger crossed), you will see this kind of message below and you're done :)

Please note :
1. The tool will not generate the database for you, it will only generate the scripts to execute
2. Currently I purposely convert nchar and nvarchar data type to varchar in the table creation script. I don't see any purpose of using them unless if storing unicode characters in the table.
3. I haven't handled Image / BLOB data type for generating data (Insert Statement).

Ok now, stop talking and just give me the download link :P

Here is the executable file :
**Removed: Please get the files from CodePlex site instead.

And here is the code, if you want to see what's running inside :
**Removed: Please get the files from CodePlex site instead.

**Updates: Erik EJ has provided the newer version of this at CodePlex website, I really recommend that you get the files from there. Huge thanks to Erik to make this utility tool better and to make it possible for others to contribute

Hope it helps ;)
and do leave a comment if you have any feedback or find any bugs, Thanks ;)

Great series of Separation of Concern by Jimmy Bogard

I just finished reading Jimmy Bogard's 5 series of Separation of Concern by example and they are just too awesome to be missed out.

The series :
# Separation of Concerns - how not to do it
# Separation of Concerns by example: Part 1 - Refactoring away from static class
# Separation of Concerns by example: Part 2 - Specialized interface for Cache
# Separation of Concerns by example: Part 3 - Creating the repository
# Separation of Concerns by example: Part 4 - Fixing a bug with unit test
# Separation of Concerns by example: Part 5 - Dependency Injection with StructureMap

The best thing that I like about them is that they are based on a real world example :D
Enuf said, go and see for your self ;)

C# 2.0 Generic Collection Sort By Value

I’m working on a web application prototype and I’m using some dummy data in xml form because it is simple and I don’t want to care about the database for a prototype ;)

Let say this is my sample data :

<root>     <member id="1" name="Jeff" />     <member id="2" name="Fred" />     <member id="3" name="Greg" />     <member id="4" name="Andy" /> </root>

I have some drop down lists or combo boxes to fill in with this kind of data in the form, and I want them to be sorted based on the description (name) not based on the key (id).

Since the data come from xml file, it may not be sorted that way, so I need to find a way to sort the data first and then bind the values to the drop down lists.

Firstly I thought it will be very simple, only need to create a class which implements IComparer for this and use it in the available generic collection types. However, it only true at the new IComparer class, but not on the generic collection part, I found out that SortedDictionary<TKey, TValue> and SortedList<TKey, TValue> will use the key to sort, even though we can specify the IComparer in the constructor.

So based on the forum discussion, I found that we can use a generic List<T> to perform sorting and we can use KeyValuePair<TKey, TValue>as the type of T.

Here is the SortByValueComparer class which implements IComparer :

public class SortByValueComparer<TKey, TValue> : IComparer<KeyValuePair<TKey, TValue>> {     #region IComparer<KeyValuePair<TKey, TValue>> Members     public int Compare(KeyValuePair<TKey, TValue> x, KeyValuePair<TKey, TValue> y)     {         return Comparer<TValue>.Default.Compare(x.Value, y.Value);     }     #endregion }

Then we can use List<T> such as :

[Fact] public void Test_ListSort() {     List<KeyValuePair<string, string>> l = new List<KeyValuePair<string, string>>();     l.Add(new KeyValuePair<string, string>("1", "Jeff"));     l.Add(new KeyValuePair<string, string>("2", "Fred"));     l.Add(new KeyValuePair<string, string>("3", "Greg"));     l.Add(new KeyValuePair<string, string>("4", "Andy"));     Assert.Equal(l[0].Value, "Jeff");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Andy");     // Sort the collection based on the value     l.Sort(new SortByValueComparer<string, string>());     Assert.Equal(l[0].Value, "Andy");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Jeff"); }

However, I felt that the code is a bit cluttered, so I add a new Custom List class which implements List<T> :

public class CustomList<TKey, TValue> : List<KeyValuePair<TKey, TValue>> {     public CustomList() : base() { }     public CustomList(int capacity) : base(capacity) { }     public void AddEntry(TKey key, TValue value)     {         this.Add(new KeyValuePair<TKey, TValue>(key, value));     }     public void SortByValue()     {         this.Sort(new SortByValueComparer<TKey, TValue>());     } }

So I can use it like this :

[Fact] public void Test_CustomList() {     CustomList<string, string> l = new CustomList<string, string>();     l.AddEntry("1", "Jeff");     l.AddEntry("2", "Fred");     l.AddEntry("3", "Greg");     l.AddEntry("4", "Andy");     Assert.Equal(l[0].Value, "Jeff");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Andy");     // Sort the collection based on the value     l.SortByValue();     Assert.Equal(l[0].Value, "Andy");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Jeff"); }

Hope this helps ;)

Testing Concurrency using ThreadPool (Multithread)

There are times when we need to test our code in concurrent manner where there are some numbers of instances running simultaneously.

This is different with the invocation such as below code where the method will be executed in sequential manner, this kind of test may be useful to test how long it may take to execute the method.

for (int iTest = 0; iTest < 100; iTest++) {     TestingMethodA(); }

 

For this testing concurrency, I’m using Joseph Albahari’s Threading in C# articles in ThreadPooling section as references to the MultiThreadRunner class that I created.

Code below in plain English : Submit 5 invocations of TestingMethodA with 10 maximum concurrent threads.

MultiThreadRunner runner = new MultiThreadRunner();            runner.Run(new MultiThreadRunner.ParameterlessMethodDelegate(TestingMethodA), 5, 10);

 

The MultiThreadRunner class :

class MultiThreadRunner {     static object workerLocker = new object();     static int runningWorkers = 0;     /// <summary>     /// The delegate is to be used as a placeholder for a parameterless method to invoke     /// </summary>     public delegate void ParameterlessMethodDelegate();     /// <summary>     /// An overload to Run method, set the concurrentNoOfInstances to a default no     /// </summary>     /// <param name="myMethod">The method to be invoke, passed as a delegate</param>     /// <param name="noOfInvocations">Total no of invocations to execute</param>     public void Run(Delegate myMethod, int noOfInvocations)     {         this.Run(myMethod, noOfInvocations, 0);     }     /// <summary>     /// The method will submit the method into the ThreadPool     /// </summary>     /// <param name="myMethod">The method to be invoke, passed as a delegate</param>     /// <param name="noOfInvocations">Total no of invocations to execute</param>     /// <param name="concurrentNoOfInstances">Concurrent no of instances -> ThreadPool.MinThreads</param>     public void Run(Delegate myMethod, int noOfInvocations, int concurrentNoOfInstances)     {         int iRow = runningWorkers = noOfInvocations;         int iStartWorker, iEndWorker, iCompletion;         // Adjust the min threads         SetMinThreads(concurrentNoOfInstances);         ThreadPool.GetAvailableThreads(out iStartWorker, out iCompletion);         Debug.WriteLine(string.Format("Available Threads, Start : {0}", iStartWorker));         for (int i = 1; i <= iRow; i++)         {             ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(InvokeMethod), myMethod);             Debug.WriteLine("Submitted a queue : " + i.ToString());         }         lock (workerLocker)         {             while (runningWorkers > 0) Monitor.Wait(workerLocker);         }         ThreadPool.GetAvailableThreads(out iEndWorker, out iCompletion);         Debug.WriteLine(string.Format("All threads have been completed, Start : {0} - End : {1} - Completion : {2}", iStartWorker, iEndWorker, iCompletion));     }     /// <summary>     /// Set the ThreadPool Mininum Threads, this is to avoid a delay bottleneck, please refer to http://msdn.microsoft.com/en-us/library/system.threading.threadpool.aspx     /// </summary>     /// <param name="concurrentNoOfInstances"></param>     private void SetMinThreads(int concurrentNoOfInstances)     {         int iMaxWorker, iMinWorker, iCompletion;         ThreadPool.GetMaxThreads(out iMaxWorker, out iCompletion);         ThreadPool.GetMinThreads(out iMinWorker, out iCompletion);         if (concurrentNoOfInstances != 0)         {             if (concurrentNoOfInstances > iMaxWorker)             {                 ThreadPool.SetMinThreads(iMaxWorker, iCompletion);                 Debug.WriteLine("The requested concurrent no of instances is higher than max threads, set the min threads as max threads : " + iMaxWorker);             }             else             {                 ThreadPool.SetMinThreads(concurrentNoOfInstances, iCompletion);                 Debug.WriteLine("Set the min threads to " + concurrentNoOfInstances);             }         }         else         {             Debug.WriteLine("Using the default min threads : " + iMinWorker);         }     }     /// <summary>     /// To invoke the method     /// </summary>     /// <param name="myMethod"></param>     private void InvokeMethod(object myMethod)     {         Debug.WriteLine("Invoke the method in thread : " + Thread.CurrentThread.ManagedThreadId);         ((Delegate)myMethod).DynamicInvoke(null);         lock (workerLocker)         {             runningWorkers--; Monitor.Pulse(workerLocker);         }     } }

or get the complete project from here :

 

Hope this helps with your test as well :D

Passing method using delegate

I was doing some code refactoring today and this is one of the things that fits nicely to remove those repetitive codes.

I have lines of code below as an example where I process some data based on certain key.

private void ProcessData() {     string theKey;     Key key = new Key("A", "B");                theKey = key.KeyA;     Console.WriteLine("Processing data with key : {0}", theKey);     theKey = key.KeyB;     Console.WriteLine("Processing data with key : {0}", theKey);     theKey = key.KeyA + key.KeyB;     Console.WriteLine("Processing data with key : {0}", theKey); }

This looks simple enough, but imagine what would happen if i replace “the processing data with key…” line with 20 real codes, they suddenly become 60 lines which are repetitive, doing the same thing using the key ;)

 

So this is what I came out with, create a delegate which receives Key object then create 3 new methods which have the same signature as the delegate and will have the logic to construct the key.

private delegate string AssignKeyDelegate(Key key); private string AssignKeyA(Key key) {     return key.KeyA; } private string AssignKeyB(Key key) {     return key.KeyB; } private string AssignKeyAB(Key key) {     return key.KeyA + key.KeyB; }

 

By using this, I can process the data and construct the key based on the appropriate method which is specified in the parameter using the delegate.

private void Run() {     ProcessData(new AssignKeyDelegate(AssignKeyA));     ProcessData(new AssignKeyDelegate(AssignKeyB));     ProcessData(new AssignKeyDelegate(AssignKeyAB)); } private void ProcessData(AssignKeyDelegate del) {     Key key = new Key("A", "B");                string theKey = del(key);     Console.WriteLine("Processing data with key : {0}", theKey); }

 

I provided the sample code above as a basic example only, there are many solutions that may come out with this kind of code :) The purpose is only to describe another feature that we can do with delegate.

Hope this helps :)