Monday, December 27, 2010

Drop all stored procedures in Firebird database

In my last post, I gave the SQL script to delete all stored procedures from a SQL Server database. Following is the link to post -

http://www.sanjaysingh.net/2010/12/drop-all-stored-procedures-from-sql.html

Now, following SQL script can be used to delete all the stored procedures from a Firebird database.

SET TERM ^ ;
EXECUTE BLOCK
AS
DECLARE procName varchar(100);
begin

FOR SELECT rdb$Procedure_name FROM rdb$procedures WHERE rdb$system_flag IS NULL OR rdb$system_flag = 0 INTO :procName
DO
begin
EXECUTE STATEMENT 'DROP PROCEDURE ' || procName;
end
end^

SET TERM ; ^

Thursday, December 23, 2010

Drop all stored procedures from SQL Server database

Following SQL script drops all user stored procedures from a SQL Server database.

USE TestDatabase

DECLARE @CurrStoredProcedureName SYSNAME
DECLARE StoredProceduresCursor CURSOR FOR
SELECT name FROM sys.objects WHERE type = 'P'

OPEN StoredProceduresCursor
FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('DROP PROCEDURE ' + @CurrStoredProcedureName)
FETCH NEXT FROM StoredProceduresCursor INTO @CurrStoredProcedureName
END

CLOSE StoredProceduresCursor
DEALLOCATE StoredProceduresCursor

GO


Replace database name with your database name in the above script. Mainly we are doing following two things:


  1. Get all the user stored procedures in a CURSOR.

  2. Loop through the cursor, build a DROP PROCEDURE query for each stored procedure and execute it.

Monday, December 20, 2010

Check if given table or stored procedure exists in Firebird

Following SQL checks to see if table named Employee exists in the database and if it does, drops it.

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from rdb$relations where rdb$relation_name = 'EMPLOYEE')) then
execute statement 'drop table employee;';
END^
SET TERM ; ^

Note that conditional operator IF cannot be used outside of PSQL so we have to place this call inside BLOCK as shown above. Also we cannot put DDL statements inside PSQL, so above thing cannot even be placed inside stored procedure.In the same way, following SQL checks to see if stored procedure Sel_Employee exists and drops it if it does.

SET TERM ^ ;
EXECUTE BLOCK AS BEGIN
if (exists(select 1 from RDB$PROCEDURES where rdb$Procedure_name = 'SEL_EMPLOYEE')) then
execute statement 'drop procedure SEL_EMPLOYEE;';
END^
SET TERM ; ^

Sunday, December 19, 2010

Password Manager

Often times we store our passwords in one or other kind of files on our personal computers. When you have too many passwords it becomes increasingly difficult to manage them. At the same time if you write them to a txt file, you also have the risk of it being read by someone else.

I made a very simple Password Manager C# .NET windows app that stores the password in encrypted format on your local file system. Its really simple. Following is summary of what it does.

  • When you run the application first time, it will ask you to configure your master password.
  • All the passwords are stored in encrypted format (using Rijndael symmetric algorithm) using your master password as the key in a file Password.dat at the exe location. Note that this file can only decrypted with your master password and so is secure even if someone gets his hand on your password data file.
  • It has a a Form that shows your password in a grid and gives you a search text box which show real time search results as you type.

Source and executable can be downloaded from following locations:

http://sourceforge.net/projects/managepassword/files/

Tuesday, December 14, 2010

List all user tables and their columns names in a Firebird database

Following SQL query gives list of all the table names and their column names in a Firebird database.

SELECT Relations.RDB$RELATION_NAME "Table Name", Fields.RDB$FIELD_NAME "Column Name" FROM RDB$RELATION_FIELDS Fields
JOIN RDB$RELATIONS Relations ON
Fields.RDB$RELATION_NAME = Relations.RDB$RELATION_NAME
WHERE
(Relations.RDB$SYSTEM_FLAG IS NULL OR Relations.RDB$SYSTEM_FLAG = 0)
AND
(Relations.RDB$VIEW_BLR IS NULL)
ORDER BY 1;


As noted before, RDB$System_Flag is used to filter out user tables as against system tables and RDB$View_BLR is used to filter Views.

List all stored procedures in a Firebird database

Following SQL query gives you all the user define stored procedures in a Firebird database.

SELECT rdb$Procedure_name as "Procedure Name" FROM rdb$procedures
WHERE rdb$system_flag IS NULL OR rdb$system_flag = 0;

List all user table names in Firebird

Following SQL query will give you names of all the user defined tables in a Firebird database.

SELECT rdb$relation_name AS "Table Name"
FROM rdb$relations
WHERE rdb$view_blr IS NULL AND (rdb$system_flag IS NULL OR rdb$system_flag = 0);

rdb$system_flag of 0 or NULL identifies a user table or view and rdb$view_blr being NULL identifies that it is a TABLE.

Thursday, December 02, 2010

Build Visual Studio solution from command line or batch file

Following simple batch file can be used to build a solution in Visual Studio 2010. Here I use devenv.exe to build the solution from command line so that we get the same behavior as if building from IDE. Save below given batch script into a .bat file, update the solution file path and you are all set to build.

echo off

call "C:\Program Files\Microsoft Visual Studio 10.0\VC\bin\vcvars32.bat"
devenv "C:\SampleWinApplication\SampleWinApplication.sln" /build

pause

vcvars32.bat sets up environment for Visual Studio Tools. Note that this same batch file is called when you invoke Visual Studio command prompt (Start->Programs->Microsoft Visual Studio 2010->Visual Studio Tools—>Visual Studio Command Prompt (2010). This is important to correctly invoke devenv from command line.

Saturday, November 27, 2010

Set enum type property through reflection

When you are loading third party assemblies in your application through reflection and you want to set any enum type property of any class, the situation is trickier than it appears at first. As against setting property of primitive type or any normal class type, enum type properties are not straight forward because you cannot use Activator.CreateInstance or any such APIs to create an enum instance to be set.

Assuming that the property to be set is a static property of a class, following sample method will do the job for you. Method is mostly self-explanatory.

private void SetEnumValue(Assembly theirAssembly, string fullyQualifiedClassName, 
string propertyName, string fullyQualifiedEnumName,
int newValue)
{
//Get Type of the class which contains property to be set
Type externalType = theirAssembly.GetType(fullyQualifiedClassName);

// Get Type of the enum in their assembly
Type theirEnumType = theirAssembly.GetType(fullyQualifiedEnumName);

// Get PropertyInfo that is to be set.
PropertyInfo enumTypePropertyInfo = externalType.GetProperty(propertyName);

// Derive new value to be set, ensure that newValue is valid for their enum type
object newValueToSet = Enum.ToObject(theirEnumType, newValue);

// Assuming that Property to be set is an static property, following will update its value
enumTypePropertyInfo.SetValue(null, newValueToSet, null);
}

In case, the property to be set is an instance property you can update this method to pass the instance in the last line rather than null as the first argument of PropertyInfo.SetValue.

Monday, November 15, 2010

Error Copying File or Folder. Cannot Copy. There is not enough free disk space.

I got this error recently when i was trying to copy a large file (~8GB) to my external USB hard drive. This did not make any sense to me as i had 120+ GB available free space on my hard disk.

I spent some time and it turns out i am not able to copy file because my hard disk was formatted with FAT32 format. FAT32 has 4GB file size limit and this is why i was not able copy this large file. So the solution is to convert your FAT32 to NTFS format. I already had lot of data on my hard disk so formatting with NTFS was not an option. Luckily, i found that Windows gives us  a convert command to convert FAT32 drives to NTFS while keeping all your data intact. You can use following command on the DOS prompt.

convert <drive> /FS:NTFS /X

Here <drive> is the drive (for example, F:) that you want to convert to NTFS. In my case, i got another error that it found some error on the drive and suggested to run CHKDSK. I ran CHKDSK as follows and then re-ran Convert command as given above and everything worked like a charm.

chkdsk <drive> /F /X

Here <drive> is the drive (for example F:) that you want to CHKDSK.

Thursday, November 11, 2010

Thread Synchronization Techniques – MethodImplAttribute

  • This is one more thread synchronization technique where you decorate a method with a particular attribute. We use attribute MethodImplAttribute with option of MethodImplOptions.Synchronized to make the whole method thread safe.
  • MethodImplAttribute is in System.Runtime.CompilerServices namespace.
  • This attribute option ensures that the method can be executed by only one thread at a time.
  • Note that this class lies under CompilerServices namespace. So when you compile your app, compiler inserts lock statements around the body of the method to make the whole method thread safe. For the instance method it uses lock(this) and for the static methods it uses lock(typeof(classname)). Because of this, the use of this approach for thread synchronization is discouraged. Note that it is usually not a good practice to use lock on publicly visible objects/types. This is because somebody else might, in the future, use the same object/type to lock some other code segment, thus decreasing concurrency and increasing the chance of deadlock.

Following code snippet shows the use of MethodImplAttribute to synchronize two threads. We have a DataStore class which is accessed by two threads to store data. In the AddData method of DataStore, we simulate a delay in data processing by using Thread.Sleep. Here if we remove the MethodImplAttribute from the AddData method, you will randomly get System.ArgumentException. Following is the sample application:

using System;
using System.Threading;
using System.Reflection;
using System.Diagnostics;
using System.IO;
using System.Runtime.CompilerServices;
using System.Collections.Generic;

namespace ConsoleApplication1
{
class DataStore
{
private Dictionary<string, string> dataCollection = new Dictionary<string, string>();
private const int StoreLimit = 100000;

[MethodImplAttribute(MethodImplOptions.Synchronized)]
public void AddData(string data)
{
if (!dataCollection.ContainsKey(data) && dataCollection.Count < StoreLimit)
{
Thread.Sleep(1000); // simulate some delay for data processing
dataCollection.Add(data, data);

}
}
}
class Program
{
static void Main(string[] args)
{
(new Thread(ReceiveDataFromServer1)).Start();
(new Thread(ReceiveDataFromServer2)).Start();
}

private static Random rand = new Random();
private static DataStore store = new DataStore();
private static void ReceiveDataFromServer1()
{
while (true)
{
string data = rand.Next(10, 20).ToString();
store.AddData(data);
Console.WriteLine("First thread data - " + data);
}
}

private static void ReceiveDataFromServer2()
{
while (true)
{
string data = rand.Next(10, 20).ToString();
store.AddData(data);
Console.WriteLine("Second thread data - " + data);
}
}
}
}

Tuesday, November 09, 2010

Thread Synchronization Techniques – Semaphore

  • Semaphore is another class based (derives from) on WaitHandle. Other classes that are based on WaitHandle and i have discussed them in my previous posts are AutoResetEvent, ManualResetEvent and Mutex.
  • Semaphore is used to control number of threads that can access a shared resource concurrently.
  • Threads enter the Semaphore by calling WaitOne method and release the Semaphore by calling Release method.
  • Semaphore is initialized by specifying the number of concurrent threads that it should allow. The count on Semaphore is decremented each time a thread enters the Semaphore and incremented each time a thread releases the Semaphore. When the count is zero, subsequent calls block until other threads release the Semaphore. When all threads release the Semaphore, the count is maximum value specified when the Semaphore was created.
  • There is no guaranteed order in which blocked thread enter the Semaphore.
  • A thread can enter Semaphore multiple times by calling WaitOne method repeatedly. But each of the WaitOne calls must be matched with corresponding Release call. There is also an overload of Release that takes an integer specifying number of entries to be release.
  • If the count on the Semaphore is full (no thread is entered), and a thread calls Release on that Semaphore, SemaphoreFullException is thrown.
  • Semaphore are of two types: local Semaphores and named system Semaphores. Named system Semaphores are visible throughout the operating system and can be used to synchronize the activities of processes. You can create multiple semaphores that represent the same named system semaphore and you can use OpenExisting method to open an existing named system semaphore.

Now, below i am going to give an example for use of Semaphore. We use semaphore in the following console application to allow for only two simultaneous instances of this application. Following is the code snippet:

using System;
using System.Threading;
using System.Reflection;
using System.Diagnostics;
using System.IO;

namespace ConsoleApplication1
{
class Program
{
const string ApplicationID = "B45CFE7E-8E19-4a83-8782-859E006AD576";
static void Main(string[] args)
{
Semaphore semaphore = null;
string appName = Path.GetFileName(Process.GetCurrentProcess().MainModule.FileName);
try
{
semaphore = Semaphore.OpenExisting(ApplicationID);
}
catch(WaitHandleCannotBeOpenedException)
{
semaphore = new Semaphore(2, 2, ApplicationID);
}

if (semaphore.WaitOne(0))
{
Console.WriteLine(string.Format("{0} is running", appName));
Console.WriteLine("Press enter to exit.");
Console.ReadLine();
semaphore.Release();
}
else
{
Console.WriteLine(string.Format("Two Instances of {0} are already running.",appName));
Console.WriteLine("Press enter to exit.");
Console.ReadLine();
}
}
}
}

This might not be the best scenario in which Semaphore should be used, but by this example i just want to show the concepts behind Semaphore and its potential use.

Monday, November 08, 2010

Thread Synchronization Techniques - ReaderWriterLockSlim

  • If you haven’t read ReaderWriterLock, i would suggest read that first at http://www.sanjaysingh.net/2010/11/thread-synchronization-techniques_9866.html
  • This class was introduced in .NET framework 3.5. This is similar to ReaderWriterLock but it has simplified rules for recursive locks and for upgrading and downgrading lock state and this avoids many cases of potential deadlocks.
  • Microsoft recommends it for all future development.
  • By default, new instances of ReaderWriterLockSlim are created with LockRecursionPolicy.NoRecusrion and therefore do not allow recursive lock calls. Contrast this with ReaderWriterLock which defaults to allowing recursive locks making your code more prone to deadlocks. For example, if the current thread entered read mode and it makes another call to acquire read lock, LockRecursionException is thrown. But remember that ReaderWriterLockSlim can also be instantiated to allow for recursive locks, and in that case this call will be allowed.
  • A thread can enter lock in three modes -  read mode, write mode and upgradable read mode.
  • Regardless of the recursion policy, only one thread can be in write mode at any given time. When one thread is in write mode, no other thread can enter the lock in any mode.
  • Only one thread can be in upgradable mode at any given time.
  • Any number of threads can be in read mode.

Below, i will give the same BookStore example explained in the above link using ReaderWriterLockSlim class. Behavior of the class remains same. Note the difference of use.

public class Book
{
public string Title { get; private set; }
public string Publisher { get; private set; }
public string Author { get; private set; }
public uint PublishYear { get; private set; }

public Book(string title, string publisher, string author, uint publishYear)
{
this.Title = title;
this.Publisher = publisher;
this.Author = author;
this.PublishYear = publishYear;
}
}

public class BookStore
{
private List<Book> Books = new List<Book>();
private ReaderWriterLockSlim BooksLock = new ReaderWriterLockSlim();

public BookStore()
{
// Populate Books
}

public void AddBook(Book newBook)
{
if (this.BooksLock.TryEnterWriteLock(2000))
{
try
{
this.Books.Add(newBook);
Console.WriteLine("Book Added");
}
finally
{
this.BooksLock.ExitWriteLock();
}
}
}

public Book GetBook(string title, string author)
{
Book requiredBook = null;

if (this.BooksLock.TryEnterReadLock(2000))
{
try
{
foreach (Book currBook in this.Books)
{
if (currBook.Title.Equals(title) && currBook.Author.Equals(author))
{
requiredBook = currBook;
break;
}
}
}
finally
{
this.BooksLock.ExitReadLock();
}
}
return requiredBook;
}

public int GetBooksCount()
{
int count = 0;

if (this.BooksLock.TryEnterReadLock(2000))
{
try
{
count = this.Books.Count;
}
finally
{
this.BooksLock.ExitReadLock();
}
}
return count;
}
}

Sunday, November 07, 2010

Thread Synchronization Techniques - ReaderWriterLock

  • ReaderWriterLock class defines a lock that supports single writer and multiple readers. This is used to synchronize access to a resource. In situations where resource is changed infrequently, a ReaderWriterLock provides better throughput than normal locks like Monitor.
  • A thread can hold a reader lock or a writer lock but not both.
  • Every call to acquire a lock increases the lock count. So you have to call release lock as many times as you call acquire lock.
  • Readers and Writers are queued separately. When a thread releases a writer lock, all threads waiting in the reader queue are granted reader locks. When all of those reader locks have been released, the next thread waiting in the writer queue, if any, is granted the writer lock, and so on.
  • When a thread in the writer queue is waiting for active readers locks to be released, threads requesting new reader locks accumulate in the reader queue. This helps protect writers against indefinite blockage by readers.
  • Most methods for acquiring locks on ReaderWriterLock accept time-out values. Use time-out to avoid deadlocks in your application.

Below, i will show the use of ReaderWriterLock class. We have a BookStore class and a Book class. BookStore class maintains a collection of Books. ReaderWriterLock makes perfect sense here because in BookStore write operation is less frequent than read operation (You get new books less frequently than number of customers that you have to cater to).

public class Book
{
public string Title { get; private set; }
public string Publisher { get; private set; }
public string Author { get; private set; }
public uint PublishYear { get; private set; }

public Book(string title, string publisher, string author, uint publishYear)
{
this.Title = title;
this.Publisher = publisher;
this.Author = author;
this.PublishYear = publishYear;
}
}

public class BookStore
{
private List<Book> Books = new List<Book>();
private ReaderWriterLock BooksLock = new ReaderWriterLock();

public BookStore()
{
// Populate Books
}

public void AddBook(Book newBook)
{
this.BooksLock.AcquireWriterLock(2000);
try
{
this.Books.Add(newBook);
}
finally
{
this.BooksLock.ReleaseWriterLock();
}
}

public Book GetBook(string title, string author)
{
Book requiredBook = null;
this.BooksLock.AcquireReaderLock(2000);
try
{
foreach (Book currBook in this.Books)
{
if (currBook.Title.Equals(title) && currBook.Author.Equals(author))
{
requiredBook = currBook;
break;
}
}
}
finally
{
this.BooksLock.ReleaseReaderLock();
}
return requiredBook;
}

public int GetBooksCount()
{
int count = 0;
this.BooksLock.AcquireReaderLock(2000);
try
{
count = this.Books.Count;
}
finally
{
this.BooksLock.ReleaseReaderLock();
}
return count;
}
}


AddBook method acquires writer lock and GetBook and GetBookCount method acquires reader lock. Note that we release lock in the finally block to ensure that lock is released whatever the case be.

Thread Synchronization Techniques - Interlocked

  • Windows follows what is called preemtive multithreading where a thread can be suspended after loading a value from memory address, but before having a chance to alter it and store it. Thus calls like following is not guaranteed to be atomic:
x += 1;


  • Interlocked class provides methods that synchronize access to a variable that is shared by multiple threads.
  • Interlocked operations are atomic meaning the entire operation is a unit that cannot be interrupted by another interlocked operation on the same variable.
Following is example of some of the commonly used Interlocked methods -


// 5 is added to variable variableToBeAdded and value is stored in it, as an atomic operation.
Interlocked.Add(ref variableToBeAdded, 5);

// Increments a specified variable variableToBeIncremented by 1 and stores the result in it, as an atomic operation.
Interlocked.Increment(ref variableToBeIncremented);

// Decrements a specified variable valueToBeDecremented by 1 and stores the result in it, as an atomic operation.
Interlocked.Decrement(ref valueToBeDecremented);

// Assigned 10 to variableToBeAssigned as an atomic operation.
Interlocked.Exchange(ref variableToBeAssigned, 10);

// Checks to see if variableToBeCompared is 2 and if it is then assigns 3 to it.
Interlocked.CompareExchange(ref variableToBeCompared, 3, 2);


Also note that all of these methods have overloads for different types of variables like int, long, float, double etc.

Friday, November 05, 2010

Thread Synchronization Techniques - Mutex

  • Mutex is a synchronization technique that grants exclusive access to the shared resource to only one thread. If a thread acquires a mutex, second thread that wants to acquire that mutex is suspended until the first thread releases the mutex.
  • The thread that own a mutex can request the same mutex without blocking its execution.
  • You can use WaitOne method to request ownership of a mutex. ReleaseMutex should be called to release ownership of a mutex. If you acquire same mutex multiple times, you will have to call ReleaseMutex as many times to release the mutex.
  • If a thread terminates while owning a mutex, the state of the mutex is set to signaled and the next waiting thread gets ownership.
  • A mutex are of two types – local mutex, which are unnamed and named mutex. A local mutex exists only within your process. It can be used by any thread in your process that has reference to the Mutex object that represents the mutex. Named mutex objects are visible throughout the operating system and can be used to synchronize the activities of processes.

Following example uses mutex to make an application single-instance. As said above, named mutex can be used for cross process synchronization, we take advantage of this to achieve single instance behavior for our application. Here is the sample application.

using System;
using System.Threading;
using System.Reflection;

namespace ConsoleApplication1
{
class Program
{
const string ApplicationID = "B45CFE7E-8E19-4a83-8782-859E006AD576";
static void Main(string[] args)
{
string applicationName = Assembly.GetEntryAssembly().GetName().Name;
Mutex startupMutex = new Mutex(false, ApplicationID);
if (startupMutex.WaitOne(1))
{
Console.WriteLine(string.Format("{0} is running.",applicationName));

// do your stuff
Console.ReadLine();
}
else
{
Console.WriteLine(string.Format("{0} is already running.Exiting..",applicationName));
Console.ReadLine();
}

}
}
}

Wednesday, November 03, 2010

Thread Synchronization Techniques – Auto/ManualResetEvent

AutoResetEvent and ManualResetEvent are what is called Wait Handles that is used to signal the state of one thread to another thread. Threads can use wait handles to notify other threads that they need exclusive access to a resource. Other threads must then wait to use this resource until the Wait Handle is no longer in use. Wait Handles have two states signaled and nonsignaled. A Wait Handle that is not owned by any thread is in the signaled state. A Wait Handle that is owned by a thread is in the nonsignaled state.

  • Threads request ownership of a wait handle by calling one of the wait methods, such as WaitOne, WaitAny, or WaitAll. All of these methods are blocking calls. If no other thread owns the wait handle, the call immediately returns true, the wait handle’s status is changed to nonsignaled, and the thread that owns the wait handle continues to run.
  • Threads that own a wait handle call the Set method when they are done.
  • Other threads can reset the status of a wait handle to nonsignaled by calling the reset method.
  • Once it has been signaled ManualResetEvent remains signaled until it is manually reset. That is calls to WaitOne return immediately.
  • AutoResetEvent resets automatically after each release. For example, let’s assume that we have three threads waiting on a manual reset event wait handle. As soon as we signal (Set) this ManualResetEvent, all the three threads are released. In contrast, had it been AutoResetEvent, it would release only one thread, resetting the state automatically.

Following sample class simulates processing of network data. Methods ReceiveData and ProcessData run on two different threads. Note that here we are achieving synchronization between the thread using AutoResetEvent. The ReceiveData thread is continuously generating data and pushing it into buffer. It also signals the AutoResetEvent as soon as some data is received. The ProcessData thread waits on the AutoResetEvent and processes the data as soon as signaled.

Note that we do not need to reset the AutoResetEvent, as it is automatically done after every set. Look at the example now:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;

namespace ConsoleApplication1
{
public class NetworkDataSimulator
{
private AutoResetEvent autoResetEvent = new AutoResetEvent(false);
private List<string> buffer = new List<string>();
private Random rand = new Random(100);

public void ReceiveData()
{
while (true)
{
// Simulate arrival of some data
string data = rand.Next().ToString();

// let us put the data in buffer
lock (buffer)
{
buffer.Add(data);
Console.WriteLine(string.Format("Data Received - {0}", data));
autoResetEvent.Set();
}

// simualte some delay
Thread.Sleep(2000);
}
}

public void ProcessData()
{
while (true)
{
autoResetEvent.WaitOne();
lock (buffer)
{
// Process received data
foreach (string dataElement in buffer)
{
Console.WriteLine(string.Format("Data Processed - {0}",dataElement));
}
buffer.Clear();
}
Thread.Sleep(2000);
}
}
}
}

Call these two threads as follows and check the behavior of how they synchronize.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
NetworkDataSimulator nds = new NetworkDataSimulator();
(new Thread(nds.ProcessData)).Start();
(new Thread(nds.ReceiveData)).Start();

}
}
}

Tuesday, November 02, 2010

Thread Synchronization Techniques – Monitor

Monitor is the most fundamental thread synchronization technique in the .NET Framework. This can be used to synchronize access to what is called critical section by multiple threads. I am going to present an example first and then we will discuss that:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;

namespace WpfApplication1
{
public class Person
{
public string Name
{
get;
private set;
}

public string Age
{
get;
private set;
}

public void SetData(string name, string age)
{
try
{
Monitor.Enter(this);
this.Name = name;
this.Age = age;
}
finally
{
Monitor.Exit(this);
}
}
}
}

Look at the SetData method. Here, setting name and age of the person is an atomic operation for us. This is also a critical section for us that we want to make thread safe. Note that if we do not synchronize this section in a multithreaded application, then we may end up having corrupt name and age values for a person object.

Monitor.Enter method acquires an exclusive lock on “this” object if it is not already acquired. If the second thread tries to enter this method while first thread is still executing it, it will block at Monitor.Enter call. Note that the lock is identified by the Object that is passed to the enter method, so take extreme care in deciding what object to use to acquire lock -  particularly when your contains multiple critical sections. Otherwise, you may end up in deadlock.

Each call to Monitor.Enter should be complemented with a call to Monitor.Exit to release the lock. The idea is that only one thread can be inside the Monitor at any one time.

C# .NET also provides a language specific construct “lock” to be used in this scenario. Following is the implementation of the above method “SetData” using “lock” construct:

public void SetData(string name, string age)
{
lock (this)
{
this.Name = name;
this.Age = age;
}
}

This implementation of “SetData” is exactly same as the implemented using Monitor. “lock” is C# language construct which get translated to Monitor calls in the compiled code.

Thread Synchronization

In a multithreaded applications, you often need to synchronize individual threads with other parts of your program. Synchronization techniques are used to control following two things:

  • To control the order in which code runs whenever task must be performed in a specific sequence.
  • To prevent the problems that can occur when two threads share the same resources at the same time.

For example, in a networking application, one thread might continuously listen on the socket for incoming packets and adds whatever it receives to the Queue. Second thread on the other hand is waiting on the Queue and it process the message as soon as it gets something. We need synchronization here because both threads are using the same shared resource “the Queue”.

There are several thread synchronization techniques to address these issues. I will discussing those techniques one by one in later articles.

Monday, November 01, 2010

Understanding Dispatcher in WPF

  • Dispatcher is an instance of the class System.Windows.Threading.Dispatcher.
  • Dispatcher maintains a prioritized queue of work items for a specific thread.
  • When the Dispatcher is created on a thread, it becomes the only Dispatcher that can be associated with the thread, even if the Dispatcher is shut down.
  • If you attempt to get the CurrentDispatcher for the current thread and a Dispatcher is not associated, a Dispatcher will be created.
  • If a Dispatcher is shut down, it cannot be restarted.
  • In WPF, a DispatcherObject can only be accessed by the Dispatcher it is associated with. Note that every visual (Textbox, Combobox etc) inherits from DispatcherObject. For this reason, a background thread cannot update the content of a Button that is associated with the Dispatcher on the UI thread. This is accomplished using either Invoke or BeginInvoke. Invoke is synchronous and BeginInvoke is asynchronous. The operation is added to the queue of the Dispatcher at the specified DispatcherPriority.

Following code generates exception “The calling thread cannot access this object because a different thread owns it”.

public partial class Window1 : Window
{
public Window1()
{
InitializeComponent();
Thread mythread = new Thread(() => this.MyButton.Content = "New Value");
mythread.Start();
}
}


Here MyButton is a Button added to window1 and we are trying to set its content from a different thread. As mentioned above, this is illegal.

Following is the correct way of doing this. Note the use of Dispatcher.

public partial class Window1 : Window
{
public Window1()
{

InitializeComponent();
Thread mythread = new Thread(() => this.MyButton.Dispatcher.Invoke((Action)(() => this.MyButton.Content = "New Value"),DispatcherPriority.Normal));
mythread.Start();
}
}

Sunday, October 31, 2010

Update different rows on different conditions in SQL Server

Here I will show you how to write an UPDATE statement in SQL Server to update different rows on different conditions.

Following UPDATE sql statement updates salaries of the Employee table, giving 20% raise to the employees having salary less than or equal to 5000, 10% raise to the employees having salary between 5000 and 10000, 5% raise to the employees having salary between 10000 and 20000, and no raise for the employees with salary greater then or equal to 30000.

UPDATE EMPLOYEE
SET Salary = CASE
WHEN Salary <= 5000 THEN Salary* 1.20
WHEN Salary <= 10000 THEN Salary * 1.10
WHEN Salary <= 20000 THEN Salary * 1.05
ELSE Salary
END

Saturday, October 30, 2010

Generate database scripts in SQL Server

SQL Server gives a very useful tool ‘Database Publish Wizard’ that can be used to script and publish SQL Server database. For SQL Server 2005 installation, this tool is usually located at [Program Files]\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe. This tool has both command line as well as GUI interface.

Here I am going to show some sample command line queries that can be used to generate database scripts. For the sake of example, let us assume that database name is “MyDatabase” which is hosted on SQL Server instance “SQLEXPRESS” on the local machine.
Following command will give you script for schema (not data) of the database. The script is output to a text file C:\Script.txt.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -schemaonly -f C:\Script.txt

Option “-S” specifies the SQL Server instance. Options “-d” specifies the database to script. Options “-schemaonly” specifies that we want to script only schema. Options “-f” specifies the output file.
SqlPubWiz by default works in windows authentication mode. So if your Server does not allow for windows authentication, you will also need to specify user name and password with “-U” and “-P” option respectively.

In the same way as above command, following command will generate script for all the data of the database. It will not contain any schema script.

SqlPubWiz.exe script -S .\SQLEXPRESS -d MyDatabase -dataonly -f C:\Script.txt

We can even use Management Studio to generate database scripts (Right click database, select Task and then select Generate Scripts). However, there is no options in management studio to generate “dataonly” scripts in which case this tool comes handy.

Get all the Primary Keys of a table in SQL Server

Following SQL query can be used to list all the primary keys of a table. In this given example, we assume that table name is ‘Employee’.

SELECT SysColumns.Name
FROM SysIndexes SysIndexes
JOIN SysObjects SysObjects ON SysIndexes.Id = SysObjects.Id
JOIN SysObjects PrimaryKey ON SysIndexes.Name = PrimaryKey.Name
AND PrimaryKey.Parent_Obj = SysIndexes.Id
AND PrimaryKey.Xtype = 'PK'
JOIN SysIndexKeys SysIndexKeys on SysIndexes.Id = SysIndexKeys.Id
AND SysIndexes.IndId = SysIndexKeys.IndId
JOIN SysColumns SysColumns ON SysIndexKeys.Id = SysColumns.Id
AND SysIndexKeys.ColId = SysColumns.ColId
WHERE SysObjects.name = 'Employee'
ORDER BY SysIndexKeys.keyno

List all Default constraints of a table in SQL Server

Following SQL query can be used to list all the default constraints of a tabled named ‘Department’:

SELECT DefaultConstraints.Name 'Constraint Name', DefaultConstraints.Definition 'Default Value' 
FROM Sys.Default_Constraints DefaultConstraints INNER JOIN Sys.Objects SystemObjects
On SystemObjects.Object_Id = DefaultConstraints.Parent_object_Id
Where SystemObjects.Name = 'Department'

Friday, October 29, 2010

Check if given table or stored procedure exists in SQL Server database

Following SQL statement can be used to check if stored procedure named “SelectEmployee” exists in the active database and drop it if exists.

IF EXISTS (SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects 
WHERE SystemObjects.type = 'p' AND SystemObjects.Name = 'SelectEmployee')
BEGIN
DROP PROCEDURE SelectEmployee
END

Following SQL statement can be used to check if table name “Employee” exists in the database and drop it if exists.

IF EXISTS (SELECT * FROM Sys.Tables
WHERE name = 'Employee')
BEGIN
DROP TABLE Employee;
END

Check if given column exists in a table in SQL Server database

Following SQL statement checks to see if column “Address” exists in the “Employee” table. If the column does not exist it adds that column.

If Not Exists (Select * From Information_Schema.Columns 
Where Table_Name = 'Employee' And Column_Name = 'Address')
Begin
Alter Table Employee Add Address nvarchar(200)
End



 

Thursday, October 28, 2010

Get all foreign key constraints in SQL Server database

Often we need to get all the Foreign Key constraints so that you can drop them by making dynamic queries and then re-add them. This is usually needed when you are writing some custom data migration tool in which case you drop all the foreign key constraints first, then populate data, and then you add the constraints again.

Whatever be the use case, following SQL query will give you details of all the foreign key constraints in the active database:

SELECT OBJECT_NAME(ForeignKeyColumn.Constraint_Object_ID) AS 'ForeignKey', OBJECT_NAME(ForeignKeyColumn.Parent_Object_ID) AS 'ForeighKeyTable', ParentColumn.name AS 'ForeighKeyColumn', OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID) AS 'PrimaryKeyTable', ReferencedColumn.Name AS 'PrimaryKeyColumn' 
FROM Sys.Foreign_Key_Columns ForeignKeyColumn
INNER JOIN Sys.All_Columns ParentColumn
ON ForeignKeyColumn.Parent_Object_ID = ParentColumn.Object_ID
AND ForeignKeyColumn.Parent_Column_ID = ParentColumn.Column_ID
INNER JOIN Sys.All_Columns ReferencedColumn
ON ForeignKeyColumn.Referenced_Object_ID = ReferencedColumn.Object_ID
AND ForeignKeyColumn.Referenced_Column_ID = ReferencedColumn.Column_ID
ORDER BY OBJECT_NAME(ForeignKeyColumn.Referenced_Object_ID);

Get all the stored procedures and their definitions in SQL Server

Following SQL query will list all the stored procedures and their definition in the active database:

SELECT SysObject.Name, SysModule.Definition
FROM Sys.Objects SysObject INNER JOIN Sys.Sql_Modules SysModule
ON SysObject.Object_ID = SysModule.Object_ID
WHERE SysObject.Type = 'p'
ORDER BY SysObject.Name;

List all stored procedures in SQL Server database

Following SQL query gives you names of all the stored procedures in the active database:

SELECT SystemObjects.Name
FROM SYS.OBJECTS SystemObjects
WHERE SystemObjects.type = 'p'
ORDER BY SystemObjects.Name;

List all tables in SQL Server database

Following SQL query gives you names of all the tables in the currently active database:

SELECT Name FROM Sys.Tables

Wednesday, October 27, 2010

SQL Server - Windows Installer Service Error (Error Code 1601)

Sometimes, you get this error when installing SQL Server 2005. Following is what has fixed the issue for me:

  • Go to windows services (Start->Run->services.msc) and start Windows Installer service if it is not already started.
  • If you are not able to start Windows Installer service, run msiexec /regserver command from command prompt.

SQL Server Setup cannot install files to compressed or encrypted folder

This SQL Server installation error occurs when you have applied compression on the installation folder. To fix this issue do the following:

  1. Right click [Program Files]\Microsoft SQL Server
  2. Go to properties.
  3. Uncheck ‘Compress contents to save disk space’. This option is at different places on XP and Vista (Win 7) machines. But you should be able to find this from this screen.

Tuesday, October 26, 2010

SQL Server Express – Error Installing MSXML 6.0

This is one of the most common errors occurring when installing SQL Server Express 2005. This error occurs in different scenarios on different machine configurations.
One solution that works in all or most of the cases is to uninstall MSXML 6.0 and then install SQL Server. But this solution is not trivial either. Because, you cannot uninstall MSXML 6.0 from add/remove programs. Microsoft had published an utility called (msicuu2.exe) but they have recently removed this utility and I could not find any reliable source to download it from.
There is however a tool msizap that you can download and then use the following command to uninstall MSXML 6.0.

MsiZap T! {1A528690-6A2D-4BC5-B143-8C4AE8D19D96}


If you package SQL Server with you application, then you can put this command in the batch file and even automate this process.
There is also scenario where above command does not work. In that case i would suggest using some Install Cleanup utility. One of the tools that i have used and works quite well is Your Uninstaller.

Embedded Databases For Windows

An embedded database is a database management system (DBMS) which is tightly integrated with an application software. They do not require any separate installation and are hidden from the end user.
I recently consulted on a project to replace SQL Server with a lightweight embedded database. We had however kept our option open and were also looking at databases with less installation hiccups – the main motivation to get rid of SQL Server.
There are many embedded databases available today and to filter out the one you need its important to have some criteria. Following are the list of things we were looking for in the target database:
  • No Installation Required (Preferably)
  • SQL based.
  • Performance should be as good as possible to SQL Server.
  • RDBMS (So that we have less overhead when migrating from SQL Server)
  • Can work in Server as well as embedded mode.
  • Supports Stored Procedures (Not mandatory but good to have)
  • Has .NET data access API.
  • Costs reasonable for desktop applications with unlimited installation.
  • Some renowned companies using them. This is to judge the stability.
  • Easy migration path from SQL Server
Many of the requirements as you can see are for the sake of less overhead when we migrate from SQL Server. I went through many databases to find the one we needed. Many of the databases did not fit into our criteria just because they were not RDBMS or they didn’t have .NET APIs. Following databases are either non RDBMS or don’t have .NET APIs:
  • MongoDB
  • Casandra
  • CouchDB
  • Apache Derby
  • Raima Database Manager
  • SolidDB
  • Oracle Berkley DB
  • Valentina DB (ORDBMS)
  • Informix Dynamic Server
Now, i will discuss the ones i really looked into.
  • Firebird
    • Both embedded and server flavors.
    • RDBMS
    • Has .NET data provider APIs.
    • Stable. Built on the InterBase source code.
    • Used by many companies. Lots of resources available on the web.
    • Switching between server and embedded version mostly require change of connection string only.
    • Supports stored procedures.
    • Supports standard SQL.
    • Performance is close to SQL Server.
    • Open Source. Free for development and distribution.
  • Scimore DB
    • Both embedded and server flavors. In the embedded version, they can even be configured to run in-process and out-of-process.
    • RDBMS.
    • Has .NET data provider APIs.
    • Not very stable.
    • No big name associated. Tough to find resources on the web.
    • Switching between server and embedded mostly require change of connection string only.
    • Supports stored procedures.
    • Supports standard SQL.
    • Performance is close to SQL Server.
    • Free for development and distribution. Source code is also available at reasonable cost.
  • Elevate DB
    • Both embedded and server flavors.
    • RDBMS.
    • Has .NET APIs.
    • Supports stored procedures.
    • Support standard SQL.
    • No big names associated.
    • Performance is slower than SQL Server.
  • ITTIA DB
    • Embedded and server flavors.
    • RDBMS
    • Has .NET APIs.
    • No stored procedures (It was supposed to be coming soon, i haven’t checked)
    • Supports standard SQL.
    • No big names associated.
    • Performance is slower than SQL Server.
  • Effiproz
    • Only embedded.
    • RDBMS
    • Has .NET APIs.
    • Supports stored procedures.
    • Free (MSPL)
  • SQL CE
    • Only embedded
    • RDBMS
    • Has .NET APIs
    • No stored procedures
    • Free for distribution
    • Supports standard SQL
    • Performance of SELECT queries is considerably slower than SQL Server for large databases.
  • SQLite
    • Only embedded
    • RDBMS
    • Has .NET APIs
    • No stored procedures.
    • Free for development and distribution
    • Supports standard SQL
    • Many big companies using it. Stable.
    • Performance of SELECT queries is slower than SQL Server for large databases.
  • Vista DB
    • Only embedded
    • RDBMS
    • Has .NET API. In fact, the database itself is in managed code.
    • Supports stored procedures.
    • Supports standard SQL.
    • Performance is slower than SQL Server.
  • Empress Embedded Database
    • Only embedded.
    • RDBMS.
    • Has .NET APIs.
    • Supports stored procedure.
    • Supports standard SQL
    • Performance is slower than SQL Server.
  • InterBase
    • Embedded edition only ideal for < 3 MB databases
    • RDBMS
    • Has .NET APIs.
  • MySQL
    • Not embedded
    • RDBMS
    • Has .NET API
    • Supports stored procedures
    • Supports standard SQL
    • Performance is close to SQL Server.
    • Not free when your application is not open.
Note that some databases might have enhanced their features so i would also suggest to check their latest set of supported features for any enhancements they might have come up with.

Sunday, October 24, 2010

Remove ReadOnly attribute of a file

string fileName = @"C:\ReadOnlyFile.txt";
File.SetAttributes(fileName, File.GetAttributes(fileName) & ~FileAttributes.ReadOnly);

Above C# code ensures that fileName does not have ReadOnly attribute set. SetAttribute static method takes fileName as the first parameter and FileAttributes enumeration as the second parameter. FileAttributes is a Flags enumeration meaning that we can combine multiple FlagAttributes values and set that as attribute of the file.
In the above code, we derive the FileAttributes to set by getting actual file attribute and doing a bitwise AND (operator &) with complement (operator ~) of ReadOnly attribute. This ensures that ReadOnly bit is set to zero leaving all the other attributes of the file as they were.
If you are unclear about logical operators, look at the following link for details:

http://msdn.microsoft.com/en-us/library/6a71f45d(VS.71).aspx

Forcefully delete a service

I needed that when i saw that even after i had uninstalled SQL Server from my machine, the service was still showing in Windows services. This is a sign that SQL was not uninstalled correctly and some of the pieces are still lying around. In this case you have to manually delete the service and do the file system and registry cleanup.

Windows has a command line utility called “sc” (I assume it stands for Service Controller) that gives us the APIs to talk to Windows services. Following command deletes SQLExpress service from the computer, for example.

sc delete MSSQL$SQLExpress

To know the actual name of the service, you can go to the properties (Right click and select properties) of that service from Windows Services window (Start—>Run—>Services.msc) and check the service name.

Finding permutations

Here, I am going to present a very compact recursive c# code to compute all the permutations of a given string. Look at the following c# method:

public void PrintPermutations(string stringToPermute, string prefix)
{
if (stringToPermute.Length <= 0)
{
Debug.WriteLine(prefix);
}
else
{
for(int i=0;i<stringToPermute.Length;i++)
PrintPermutations(stringToPermute.Replace(stringToPermute[i].ToString(), ""), prefix + stringToPermute[i]);
}
}

To find all the permutations of the string “ABC”, for example, you will call the method as follows:

PrintPermutations("ABC", "");

Also note that, I assume that stringToPermute parameter is a string containing non-repeating characters.

Saturday, October 23, 2010

Location of executing batch file

Often in the batch script we need to change to the directory from where the batch file is executing. Mainly when you are shipping the batch file with your application, you may want to launch some executable from the batch file, for example. So, if you put batch file where the executable is, following batch script will do the job for you:

cd %~dp0
MyApplication.exe

First statement changes current directory to the location of batch file and then second statement launches the application.

Execute SQL statement from command prompt

Many a time we run into requirement of executing simple SQL scripts using command prompt. Microsoft gives us a utility called "sqlcmd" just for that.

Following is a very simple query executed using this tool:

sqlcmd -S .\SQLEXPRESS -E -Q "Drop Database SampleDB"

Where SampleDB is the database name i want to drop, -S switch specifies the server instance, -E switch specifies that i want to use trusted connection and -Q switch specifies the SQL query i want to execute.

This tool is quite powerful and there are several switches that you can use to meet different requirements. You can open command prompt and see help (sqlcmd /?) for all the different switches that it supports.