SQL - CASE WHEN THEN ELSE END

T-SQL CASE check conditions and returns result expressions if it find the first
condition logical true value.

Two version of CASE is available:

1- Searched CASE expression:

CASE
WHEN Boolean_expression THEN result_expression [..n]
[ ELSE else_result_expression ]
END

- For each WHEN in CASE expression it evaluates the conditional expresiion in front of WHEN
- It return the first result_expression of the first TRUE conditional expression in order.
- If no conditional expression is TRUE, then it returns else_result_expression if an ELSE clause is specified.
- If no conditional expression is TRUE and there is no ELSE clause, it returns a NULL value.

Example:

use AdventureWorksLT
SELECT [name], [listprice],
CASE
WHEN [color] = 'Black' THEN [listprice] * 0.9
WHEN [color] = 'Red' THEN [listprice] * 0.45
WHEN [color] = 'White' THEN [listprice] * 0.6
ELSE
[listprice]
END as discountPrice
FROM [AdventureWorksLT].[SalesLT].[Product]

2- The simple CASE expression:
Operates by comparing the first expression to the expression in each WHEN clause.
If equal, the expression in the THEN clause will be returned.

CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

- It evaluates input_expression, and in the order specified for each WHEN clause
- It checks to see if when_expression is equal to input_expression.
- It returns the result_expression of the first match as explained above.
- If no input_expression equals to when_expression, it returns the else_result_expression if an ELSE clause is exist.
- If no input_expression equals to when_expression and ELSE clause is not exist, it returns a NULL value.

Example:

SELECT [name], [listprice],
CASE [color]
WHEN 'Black' THEN 'In stock'
WHEN 'Red' THEN 'Sold out'
WHEN 'White' THEN 'On web'
WHEN 'Blue' THEN 'In order'
ELSE
'CALL'
END as availability
FROM [AdventureWorksLT].[SalesLT].[Product]

Note:
CASE in both formats support an optional ELSE argument.
CASE can be used in any statement or clause that allows a valid expression,
such as SELECT, UPDATE, DELETE and SET, and also in IN, WHERE, ORDER BY, and ...

enjoy using CASE !

SQL - CASE WHEN THEN ELSE END

C# - How to activate another application?

There is not a managed library who offer this function for C#. The solution is using AppActivate method of Microsoft.VisualBasic .NET library in your code.

* First it's necessary to add Microsoft.VisualBasic.dll in References for your solution, If you try "Add Reference ..." you will find it in ".NET" page tab in new opening window.

* Next, you need to include Microsoft.VisualBasic namespace to your code

* Then you can activate another application by the application title or Its ProcessID as following:
...
using Microsoft.VisualBasic
...
Interaction.AppActivate("App Title or ProcessID");

Note: If your application is minimized, it will be activated but AppActivate does not restore the application window (it doesn't come up).

C# - How to activate another application?

C# - Shutdown or Restart MS Windows

To Shutdown:
System.Diagnostics.Process.Start("ShutDown", "/s");

To restart:
System.Diagnostics.Process.Start("ShutDown", "/r");

List of all possible arguments are as following:

-r Shutdown and restart the computer
-s Shutdown the computer
-t xx Set timeout for shutdown to xx seconds
-a Abort a system shutdown
-f Forces all windows to close
-i Display GUI interface
-l Log off

C# - Shutdown or Restart MS Windows

150 million users for Facebook !

Facebook has almost 75 million daily users in 170 countries and on every continent.

http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9125421&source=rss_news

150 million users for Facebook !

Like great art, great software doesn’t just happen

o a bit of planning first, and decide exactly what you want your application to do. When you have a clear idea in mind, take a few minutes to write your thoughts down on a piece of paper. This phase is known as the requirements phase (Notice that the requirements can be in your own words). Collect info, plan it, design and thenimplement, now you need to test your application. You can’t test something if you don’t know exactly how it’s supposed to work !

Like great art, great software doesn’t just happen

Windows 7 upgrades for Windows Vista machines buyer as of July 1

If you have a plan to buy another Vista machine but you are afraid of losing your money when the Windows 7 show up, don't worry if you planed it to do after July 2009!

To find out more, look at the Mary Jo Foley post:
http://blogs.zdnet.com/microsoft/?p=1791

Windows 7 upgrades for Windows Vista machines buyer as of July 1

BUG in Microsoft.JScript evaluator

I encountered with an interesting mis calculation (bug) on Microsoft.JScript expression evaluator.

It calculates the following expression:
"-180.188+38.1-19.05-(57.15)+142.088+19.05+57.15+76.2-19.05-(57.15)"

It returns "-7.105427357601E-15" instead of 0 (Zero)

To Evaluate an expression in a C# class, we need to add Microsoft.JScript and call Eval.JScriptEvaluate method:
using Microsoft.JScript;
...
public static string EvalJScript(string equation)
{
return Eval.JScriptEvaluate(equation, Vsa.VsaEngine.CreateEngine()).ToString();
}

BUG in Microsoft.JScript evaluator

MS SQL Server Common Table Expressions

MS SQL Server 2005 introduced Common Table Expression (CTE) which is temporary result set with a name that will be used in SELECT query statement by FROM clause. It makes the queries simpler and better for future maintenance. CTE defines a virtual view that will be used in another data manipulation language (DML) statement, for example in a SELECT.

CTE main elements:
Name of CTE, It comes after WITH keyword.
List of columns, it's optional.
Query that defines the CTE temporary result set,
The quesry sits after AS keyword and inside open and close paranteses.

It's not possible to use COMPUTE, COMPUTE BY, ORDER BY (unless TOP
is used) in a CTE.

For example, in AdventureWorksLT database sample (is available
in CodePlex web pages, you can download it from following here. Product table keeps information about products and category of the products are available in ProductCategory table. The following example provides a temporary result set in the name of CheapProducts which is derived from Product table and those products' price is less than $100 (Ooops, I know, $100 is not cheap thing, just take it easy). Then query that comes after, request for BLACK products with their names and their category.

WITH CheapProducts (ProductName, ProductColor, ProductPrice, ProductCategoryID)
AS
(
   SELECT [name], [color], [listprice], [ProductCategoryID]
   FROM [AdventureWorksLT].[SalesLT].[Product]
   WHERE [listprice] < 100
)
SELECT chpPrd.ProductName, chpPrd.ProductPrice, prdCtg.Name as ProductCategory
   FROM CheapProducts as chpPrd
   JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
      ON chpPrd.ProductCategoryID = prdCtg.ProductCategoryID
   WHERE chpPrd.ProductColor = 'BLACK'

This one was a simple sample, but when we have a little more
complex queries CTE can really be a good hand to make it simple.
Next example lists the QUANTITY of PRODUCTS (and the products' CATEGORY) which are RED and their price is less than $100, ordered by companies:

WITH PrdAndCat (PrdID, PrdName, PrdColor, PrdPrice, PrdCategory)
AS
(
SELECT prd.[ProductID], prd.[name], prd.[color], prd.[listprice], prdCtg.[Name]
FROM [AdventureWorksLT].[SalesLT].[Product] as prd
JOIN [AdventureWorksLT].[SalesLT].[ProductCategory] as prdCtg
   ON prd.ProductCategoryID = prdCtg.ProductCategoryID
)
SELECT cstmr.CompanyName, ordrHdr.OrderDate, ordrDtl.OrderQty,
pNc.PrdName, pNc.PrdCategory
FROM [AdventureWorksLT].[SalesLT].[SalesOrderHeader] as ordrHdr
JOIN [AdventureWorksLT].[SalesLT].[SalesOrderDetail] as ordrDtl
   ON ordrHdr.SalesOrderID = ordrDtl.SalesOrderID
JOIN [AdventureWorksLT].[SalesLT].[Customer] as cstmr
   ON ordrHdr.CustomerID = cstmr.CustomerID
JOIN PrdAndCat as pNc
   ON pNc.[PrdID] = ordrDtl.[ProductID]
WHERE pNc.PrdColor = 'RED' AND pNc.PrdPrice

Bulk Discount Store              2004-06-01  3      Sport-100 Helmet, Red Helmets
Metropolitan Bicycle Supply      2004-06-01  1      Sport-100 Helmet, Red Helmets
Many Bikes Store                 2004-06-01  2      Sport-100 Helmet, Red Helmets
Riding Cycles                    2004-06-01  6      Sport-100 Helmet, Red Helmets
Action Bicycle Specialists       2004-06-01 10      Sport-100 Helmet, Red Helmets
Eastside Department Store        2004-06-01 10      Sport-100 Helmet, Red Helmets
Professional Sales and Service   2004-06-01  3      Sport-100 Helmet, Red Helmets

MS SQL Server Common Table Expressions

Remembering NULL-Coalescing operator

It's A clean and smart operator to deal with null values to make a
decision on them. Nullable types since .NET 2.0 brought opportunity
to work with value type which can be null too. To make a value type
"Nullable" we can add a ? follwoing the type like:
...
int? height = null;
...
Well now that we have nullable types, we can check null value and use
Coalesce Operator for a conditional assignment. ?? means if left side is
null, then give the right side value.

The ?? operator returns the left-hand operand if it is not null, or else it
returns the right operand.
...
string firstName = this.getFirstName();
Console.WriteLine( firstName ?? "No name" );
...
?? operator is also protecting it. It makes us able to assign a nullable type
to a non-nullable type with correcting the value if its null.
...
int? i = null;
int x = i ?? -1; // x = -1
...
string txtMsg = null;
string outMsg = txtMsg ?? "Hello world!"; // outMsg = "Hello world!"
...

?? is the same as ISNULL in Transact-SQL which implemented
in .NET but it's not popular between developers.
ISNULL ( check_expression, replacement_value )

Remembering NULL-Coalescing operator

MS Silverlight 2 released

A programmable web browser plug-in which enables features like Audio-Video, graphic, animation, and more based on .NET framework by Microsoft, Silverlight. Silverlight tries to compete with Adobe Flash, JavaFx, Javascript, and ... in making applications rich with interactive and graphical features on the web.
MS Silverlight 2 released October 14, 2008, you can try it out!

http://silverlight.net/default.aspx

MS Silverlight 2 released