• 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();
    }
}

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

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.

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

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'

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

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

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);

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;

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;