Coalesce in SQL Server

Coalesce function accepts "n" number of arguments and returns the first non-null expression of the arguments. If all the arguments are null then it returns null.

Syntax

select coalesce(p1, p2, p3.....n)

Let's take example to understand more clearly how coalesce function works

select coalesce(null, 1)
select coalesce(null, null, 1)
select coalesce(null, null, 1, null)
select coalesce(1, 2)

OUTPUT

Look at the output, we are getting value as "1" in each output because in all select statements "1" is first non-null value in the arguments.

NOTE: At least one of the null values must be a typed NULL.

select coalesce(null, null)

OUTPUT

In the above select statement we are passing NULL as value in all arguments and NULL are not typed, so we are getting the error.

Now, let's try with NULL values as typed

declare @i int
select coalesce(null, @i)

OUTPUT

In this Example, it worked fine without any error because values of the argument are still NULL but at least one of them is typed.

Coalesce can be used in place of following case expression

case when expression1 is not null then expression1
     when expression1 is not null then expression1
     ...
     when expressionN is not null then expressionN
end

Let's take an example to show how coalesce can be used in place of case expression

declare @tab1 table(id int, value varchar(10))
insert into @tab1 values (1, 'val1')
insert into @tab1 values (2, null)
insert into @tab1 values (3, null)
insert into @tab1 values (4, null)
declare @tab2 table(id int, value varchar(10))
insert into @tab2 values (1, null)
insert into @tab2 values (2, 'val2')
insert into @tab2 values (3, null)
insert into @tab2 values (4, null)
declare @tab3 table(id int, value varchar(10))
insert into @tab3 values (1, null)
insert into @tab3 values (2, null)
insert into @tab3 values (3, 'val3')
insert into @tab3 values (4, null)

select  t1.id
        , case when t1.value is not null then t1.value
               when t2.value is not null then t2.value
               when t3.value is not null then t3.value
          end as [value using case]
        , coalesce(t1.value, t2.value, t3.value) as [value using coalesce]
from    @tab1 t1
inner join @tab2 t2 on t1.id = t2.id
inner join @tab3 t3 on t1.id = t3.id

OUTPUT

Coded UI Test in Visual Studio 2012

A coded UI test provides a mechanism to automatically execute and drive the application in much the same manner as a human sitting in front of a computer. It operates at the user interface layer and can be programmed to validate elements of the UI at various points during the test to confirm that the application is behaving properly.
Coded UI tests can be authored in C# or Visual Basic, and Visual Studio 2012 provides tools to help auto-generate much of this required code.

We will start by creating a simple Coded UI Test using the Coded UI Test Builder and adding some validation logic.

Setting Up the Sample Application
We will use a simple web application having one web form.

Create a test project
We will need a test project in which to house Coded UI test. Click File -> New -> Project, which will display the New Project dialog box. Select Visual C# -> Test -> Coded UI Test Project. Name the project “CodedUITestProject” and select “Add to solution” in the Solution drop-down. Click OK.

“Generate Code for Coded UI Test” will appear, providing options for generating test.

Choose the first option “Record actions, edit UI map or add assertions”. This option allows recording a Coded UI test from scratch by navigating through the application in the same manner that a user might.
The moment you choose above mentioned option Coded UI Test Builder appears.

Assuming you have launched the web form (calculate.aspx) in browser. Click the record button of the Test Builder. You can now begin recording the Coded UI test by using the application in same manner you would expect a user to. Type 20 in first textbox, then type 30 in second textbox, and click the Add button.
You can visually inspect the actions that the Test Builder has captured by clicking on Show recorded steps of the Test Builder.

At this point of time, validation logic can be added to confirm that the result of addition operation is correct. Before adding assertion, performed steps must be converted into source code. Do so by clicking on Generate Code.
A dialog will appear prompting you to name the method you want to create within coded UI test. Type “EnterDataAndClickAdd”; then click Add and Generate to resume building coded UI test.

You can now add assertion logic to validate the properties of one or more controls. The Test Builder allows you to easily select the control you want to validate. Do so by clicking the Add Assert and dragging the crosshair icon from the Test Builder on to the bottommost label next to Result of the form. As you hover over controls, you will notice that they become highlighted to indicate which control you are selecting. Once you have selected the bottommost label of the form, release your mouse button.

The properties for the lblResult label will be displayed.

For this test, we want to confirm that the number 50 (the sum of 20 and 30) is properly displayed in the textbox. In the list of properties for this control, you will see that the InnerText property has a value of 50. Highlight this row, then click Add an Assertion. “Add assertion” dialog will appear, allowing you to define the behavior of your assertion. Click the Comparator drop - down to examine your assertion choices. Accept the default value (AreEqual) for Comparator and the current value (50) for Comparison Value. You can also type message in case assertion fails .Click OK. The Test Builder will display a message indicating that your assertion has been added.

Click Generate Code from within the Test Builder to codify the assertion just added. A dialog will appear to name the method the will correspond to you assertion. Name the method “AddAssert” and click Add and Generate. The Test Builder now converts the assertion you defined into C# and insert this into test project.

Once you are finished, close the Test Builder, which will return you to Visual Studio.
Generated Code
In Solution Explorer, Open CodedUITest1.cs.

This is the main execution harness for the test, and calls all of the action and assertion methods you have defined earlier, as shown here:

[TestMethod]
public void CodedUITestMethod1()
{
      this.UIMap.EnterDataAndClickAdd();
      this.UIMap.AddAssert();
}

To better understand what each method is actually doing, you can examine the partial class file name UIMap.Desginer.cs. Right click on the EnterDataAndClickAdd method call and select “Go to definition”.

public void EnterDataAndClickAdd()
{
      #region Variable Declarations
      HtmlEdit uITxtFNoEdit = this.UIHttplocalhost52996CaWindow.UIHttplocalhost52996CaDocument.UITxtFNoEdit;
      HtmlEdit uITxtSNoEdit = this.UIHttplocalhost52996CaWindow.UIHttplocalhost52996CaDocument.UITxtSNoEdit;
      HtmlInputButton uIAddButton = this.UIHttplocalhost52996CaWindow.UIHttplocalhost52996CaDocument.UIAddButton;
      #endregion

      // Type '20' in 'txtFNo' text box
      uITxtFNoEdit.Text = this.EnterDataAndClickAddParams.UITxtFNoEditText;

      // Type '30' in 'txtSNo' text box
      uITxtSNoEdit.Text = this.EnterDataAndClickAddParams.UITxtSNoEditText;

      // Click 'Add' button
      Mouse.Click(uIAddButton, new Point(40, 13));
}

This method is responsible for various actions, as defined by the action recorded earlier. It will put values in two textboxes and then click on Add.
Running Test
Launch the form in browser. Open Test Explorer by clicking TEST menu -> Windows -> Test Explorer. You need to build the solution to load all the tests in Test Explorer. Right click on CodedUITestMethod1 and select Run Selected Test.

Avoid using your mouse or keyboard while the text executes. If you have recorded test properly, the values 20 and 30 will be inserted into the textboxes, and Add button will be clicked. When finished, the test results will be displayed as shown

Creating Data-Driven Coded UI Test

Coded UI Test can be data-driven. So that it can be run multiple times with different data sources. Instead of having to implement a number of different tests permutations, we can instead bind the test to a data source and access the current data row for the test.
The different data sources are: CSV, Excel, Test Case, XML and SQL Express.
We will use XML as data source to make the Coded UI Test we created earlier; data-driven. Add an XML file to CodedUITestProject by the name “CalcData.xml” and put following xml in it.

xml version="1.0" encoding="utf-8" ?>
<DataContextData>
  <DataContextRow>
    <InputValue1>11</InputValue1>
    <InputValue2>22</InputValue2>
    <ExpectedAddAnswer>33</ExpectedAddAnswer>
  </DataContextRow>
  <DataContextRow>
    <InputValue1>12</InputValue1>
    <InputValue2>11</InputValue2>
    <ExpectedAddAnswer>23</ExpectedAddAnswer>
  </DataContextRow>
</DataContextData>

Now open the “CodedUITest1.cs” and add DataSource attribute to CodedUITestMethod1 and modify the code as shown below.

[TestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\CalcData.xml", "DataContextRow", DataAccessMethod.Sequential)]
public void CodedUITestMethod1()
{
      this.UIMap.EnterDataAndClickAddParams.UITxtFNoEditText = TestContext.DataRow["InputValue1"].ToString();
      this.UIMap.EnterDataAndClickAddParams.UITxtSNoEditText = TestContext.DataRow["InputValue2"].ToString();
this.UIMap.EnterDataAndClickAdd();
      this.UIMap.AddAssertExpectedValues.UIItem50PaneInnerText = TestContext.DataRow["ExpectedAddAnswer"].ToString();
      this.UIMap.AddAssert();

}

We use TextContext.DataRow to fetch row from the datasource. So this method will run for each row in datasource; putting the values of columns in respective controls along the way.
Now, launch the form in browser and run the test from Test Explorer. If no assertion fails, it should pass for all datarows.
Note: If you encounter error “The unit test adapter failed to connect to the data source or to read the data” during test run; Right click on “CalcData.xml” in Solution Explorer and select Properties and in Copy to Output Directory, choose either Copy always or Copy if newer.

Make some changes to XML file to make the test fail.

xml version="1.0" encoding="utf-8" ?>
<DataContextData>
  <DataContextRow>
    <InputValue1>11</InputValue1>
    <InputValue2>22</InputValue2>
    <ExpectedAddAnswer>33</ExpectedAddAnswer>
  </DataContextRow>
  <DataContextRow>
    <InputValue1>12</InputValue1>
    <InputValue2>11</InputValue2>
    <ExpectedAddAnswer>33</ExpectedAddAnswer>
  </DataContextRow>
</DataContextData>

Notice we have changed expected value for second row to 33, which is wrong (12 + 11 = 23). Now run the test again.

You can notice the test passed for first row but failed for second row.
Apart from recorded asserts, you can also add assertion logic by writing custom code. Now we will add some code to check that FirstTextbox should not be blank. Open UIMap.cs and add following code.

public partial class UIMap
{
       public void AssertIfFNoIsBlank()
       {
            HtmlEdit uITxtFNoEdit = this.UIHttplocalhost52996CaWindow.UIHttplocalhost52996CaDocument.UITxtFNoEdit;
            Assert.AreNotEqual(string.Empty, uITxtFNoEdit.Text.Trim(), "First Number cannot be blank.");
       }
}

Now add a call to AssertIfFNoIsBlank to CodedUITest1.cs.

public void CodedUITestMethod1()
{
      this.UIMap.EnterDataAndClickAddParams.UITxtFNoEditText = TestContext.DataRow["InputValue1"].ToString();
      this.UIMap.EnterDataAndClickAddParams.UITxtSNoEditText = TestContext.DataRow["InputValue2"].ToString();
      this.UIMap.EnterDataAndClickAdd();
      this.UIMap.AssertIfFNoIsBlank();
      this.UIMap.AddAssertExpectedValues.UIItem50PaneInnerText = TestContext.DataRow["ExpectedAddAnswer"].ToString();
      this.UIMap.AddAssert();
}

Again modify the xml file so that first textbox remain blank; to make this test fail.

xml version="1.0" encoding="utf-8" ?>
<DataContextData>
  <DataContextRow>
    <InputValue1></InputValue1>
    <InputValue2>22</InputValue2>
    <ExpectedAddAnswer>33</ExpectedAddAnswer>
  </DataContextRow>
  <DataContextRow>
    <InputValue1>12</InputValue1>
    <InputValue2>11</InputValue2>
    <ExpectedAddAnswer>23</ExpectedAddAnswer>
  </DataContextRow>
</DataContextData>

Run the test again.

You can verify the test passed for second row but it failed for first row because we modified the xml for the first value to be blank. Similarly more custom assertion logic can be added to verify the UI elements.
You can now maintain the CalcData.xml file within test project to add new rows of data. Those rows can be used for future test runs.

www.huaykk.com

Computed Column in SQL Server

Today, on one of my post related to Auto generation of Id, a reader asked the meaning of PERSISTED PRIMARY KEY that is related with Computed Column. I answered the question on the post itself, but it gave me an idea to have a detailed post on computed column on my blog.

Computed Column is the column whose values are generated with an expression. An expression can be a non-computed column name, constant, function and combination of any of these connected by one or more operators.
Computed columns can be used in select list, where clause, order by clause.

There are two types of computed column.

• Persisted Columns: Persisted columns values are calculated at the time of insertion and updation (when any columns that is part of their calculation changes) and are physically stored in the table. Indexes can be created on persisted columns as they are physically stored.
• Non-Persisted Columns: Non-persisted columns are calculated on run-time every time they are referenced in a query. Indexes cannot be created on Non-persisted columns as they are not physically stored.

Some examples of Computed column

Example 1: Auto generation of Id 

Example 2:  Calculate Date of Retirement 
Date of retirement is dependent on column Date of Birth as Date of retirement is calculated as (DOB + 60 years), so we are creating DOR as computed column to compute the same

create table tblEmployee(     
    EmpId    varchar(10)
    , DOB    datetime
    , DOR    as (dateadd(yy, 60, DOB)) persisted
)
insert into tblEmployee (EmpId, DOB)
select 'EMP0001', '01/1/1980' union all
select 'EMP0002', '04/1/1981' union all
select 'EMP0003', '03/06/1977'

select * from tblEmployee

OUTPUT

Example 3: Calculate total (price * qty) using function

create function dbo.udf_CalclateTotal (@price int, @qty int)
returns int
as
begin
    return @price*@qty
end

create table tblSales(
      OrderId    int identity   
      , Price    int
      , Qty      int
      , Total as (dbo.udf_CalclateTotal(Price, Qty))
)

insert into tblSales (Price, Qty)
select 8, 3 union all
select 5, 5 union all
select 2, 50

select * from tblSales

OUTPUT

Delete Folder and all Sub-Folders and Files in c#

In this post, I am sharing code snippet to delete the specified directory and all its sub directory and files in C#.

In the solution we are using members of Directory Class to delete the files and folders which exists in System.IO Namespace. So, we have added namespace using System.IO in our solution

using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string sDirectoryPath;
            sDirectoryPath = "d:\\abc"; //set directory path to delete
            if (Directory.Exists(sDirectoryPath))
            {
                RemoveDirectories(sDirectoryPath);
            }
        }

        private static void RemoveDirectories(string sDirectoryPath)
        {
            //This condition is used to delete all files from the Directory
            foreach (string file in Directory.GetFiles(sDirectoryPath))
            {
                File.Delete(file);
            }
            //This condition is used to check all child Directories and delete files
            foreach (string subfolder in Directory.GetDirectories(sDirectoryPath))
            {
                RemoveDirectories(subfolder);
            }

            Directory.Delete(sDirectoryPath);
        }
    }
}

Calculate Median in SQL Server

In this post, I am sharing how to calculate the median in SQL Server.

Let's first create a table and populate it with some data

create table tblData (val int)
go
insert into tblData
select 10 union all
select  8 union all
select 14 union all
select 20 union all
select 11 union all
select 18 union all
select 21

In the table we have following list of values

• 8, 10, 11, 14, 18, 20, 21

Here, the no. of values is 7 (odd), so median value is (7+1)/2 = 4th value i.e 14

Now, lets run the script to calculate median

In the below example, we have used Row_Number.

Refer related post to understand in detail:  Row_Number in SQL Server

declare @cnt int
select @cnt = count(1) from tblData
;with cte as(
    select  val, ROW_NUMBER() over (order by val) as rid
    from    tblData
)
select  cast(AVG(val*1.0) as numeric(10,2)) as median
from    cte   
where   rid in ((@cnt+1)/2, (@cnt+2)/2)

OUTPUT

median
---------------------------------------
14.00
(1 row(s) affected)

Now, Let's add another value in the table

insert into tblData values(21)

Now, In the table we have following list of values

• 8, 10, 11, 14, 18, 20, 21, 21

Now, the no. of values is 8 (even), so median value is (8/2 + (8/2+1))/2
= (4th value + 5th value)/2 = 14 + 18 = 16

Let's run the script again to calculate median

declare @cnt int
select @cnt = count(1) from tblData
;with cte as(
    select  val, ROW_NUMBER() over (order by val) as rid
    from    tblData
)
select  cast(AVG(val*1.0) as numeric(10,2)) as median
from    cte   
where   rid in ((@cnt+1)/2, (@cnt+2)/2)

OUTPUT

median
---------------------------------------
16.00

(1 row(s) affected)