Techie Hub

.Net Standard versioning and PCL comparision

2017-02-20T19:04:00.000-08:00

In previous post, we have learned how to create simple .Net Standard library using VS 2015 and VS 2017 RC. Today we will discuss how .Net Standard is different from PCL and how .Net Standard is versioned.

PCL is an afterthought. So, individual platforms such as .Net Core, Xamarin etc. is built and developed against their base libraries. When code reuse is needed, we use PCL which takes an intersection of the base libraries of individual platforms.

This has lots of issues like what happens when tomorrow your PCL needs to support a new platform?

You create a new PCL profile and take again intersection of all the platforms involved. This means recreate the library again with a different profile. Hence a new PCL will be created with intersection of the platforms and hence lot of changes needed in the already written library as the base library is now further reduced in terms of APIs.

On the other hand, .Net Standard is a before thought. Platforms in .Net develop against .Net Standard. So tomorrow your .Net Standard library can be consumed by any new platform as long as that platform supports that version or a higher version of .Net Standard.

Let’s now see more on .Net Standard versions. Below are 2 rules.

Higher the version, Larger is the API set.

Lower the version, more number of platforms supports that version of .Net Standard.

Currently they are versioned as 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 2.0. So, version 1.2 has all APIs introduced in 1.2 version along with all APIs from 1.0. Similarly, 2.0 has all APIs from 1.6 plus APIs introduced in 2.0.

1.6 is the stable version and 2.0 is the latest release candidate as of writing this article.

Mapping PCL Profiles to .Net Standard:

As we know PCL has different profile numbers. For example, if PCL supports .NET Framework 4.5, Windows 8 then its profile 7.

Based on the profile number we can convert a PCL to a specific version of .Net Standard.

PCL Profile	.NET Standard	PCL Platforms
7	1.1	.NET Framework 4.5, Windows 8
31	1.0	Windows 8.1, Windows Phone Silverlight 8.1
32	1.2	Windows 8.1, Windows Phone 8.1
44	1.2	.NET Framework 4.5.1, Windows 8.1
49	1.0	.NET Framework 4.5, Windows Phone Silverlight 8
78	1.0	.NET Framework 4.5, Windows 8, Windows Phone Silverlight 8
84	1.0	Windows Phone 8.1, Windows Phone Silverlight 8.1
111	1.1	.NET Framework 4.5, Windows 8, Windows Phone 8.1
151	1.2	.NET Framework 4.5.1, Windows 8.1, Windows Phone 8.1
157	1.0	Windows 8.1, Windows Phone 8.1, Windows Phone Silverlight 8.1
259	1.0	.NET Framework 4.5, Windows 8, Windows Phone 8.1, Windows Phone Silverlight 8

So, if your PCL is of profile 151, then you can convert it to support .Net Standard 1.2.

How to create a .Net Standard Library

2017-02-20T12:36:00.000-08:00

In previous post, I had explained what is .Net Standard. Let’s now use visual studio and see how to build a simple .Net Standard Library.

Using VS 2015:

Creating a .Net Standard library using VS 2015 is not straight forward. Below are the steps.

Create a Portable Class Library and choose any selection of platforms you want

Right click on the PCL project in Solution explore and go to properties

As you can see there is an option to change this PCL to a .Net Standard library. Click on it and select yes in the prompt.

Now this library is .net Standard Library and not a PCL library anymore.

You can see it is using .Net Standard 1.4.

Using VS 2017 RC:

Its straight forward in VS 2017. Create a .Net Standard Library as shown below

The option is directly there when creating a class library.

Now create a simple .Net Standard Library and consume it in .Net core application and a WPF application. We just need to add reference to the .net Standard library as we do for other libraries.

In next post I will explain how .Net Standard library differs from PCL and versioning in .Net Standard Library. Stay tuned.

What is .Net Standard and basic Purpose of .Net Standard

2017-02-19T22:24:00.003-08:00

.Net Standard:

.NET Standard is a standard (like html 5 standard) and it has a set of APIs that all .NET platforms have to implement.

Let’s understand this a bit deeper with a scenario.

Let’s say I am building an application and it is based on WPF. So, it will use some version of .net framework as its base class library(BCL). Now let’s say the requirement is to develop a web version of the same and the team want to built it in ASP.Net core. Also, lets say after some days the team now want built an iOS app for the same application.

So now the application has 3 flavors. WPF, ASP.NET Core and iOS app

Below are few issues in above scenario:

Here clearly the core business of the application will be similar across different platform. Hence, we expect the business library written for one platform to be reused to maximum in other two platforms. Here library means the core business logic. However, we cannot do much code reuse here. The reason is the core libraries of WPF, Asp.Net core and Xamarin iOS app remains different and are not compatible.
Below is a nice diagram depicting the above

As you see above the base libraries of the 3 platforms are different. Hence library written for one platform won't be supported in the others.

Also, we need the developer to learn APIs in 3 different libraries to develop same or similar logic.
One more side effect is, any changes needed done in one library has to be replicated in all the 3 libraries for uniformity.

.Net standard tries to solve the core problem mentioned in above scenario. It is a specification which all the .net platforms will adhere to.

Below is a diagram depicting .Net Standard.

As you observe all the platforms in .Net world now have their core library as .Net Standard. Hence the issues we discussed such as code reuse, developer learning new apis for new platforms will be eliminated.

So, let’s say tomorrow a new platform X comes in .Net world. So, X will adhere to .Net Standard and won’t have its own base library. Hence any application built using platform X can consume a .Net Standard library and improves productivity.

In next post, I will explain how to create a simple .Net Standard based library using VS 2017 RC. Stay tuned.

Visual studio 2013 community launch by BDotnet and Microsoft

2013-12-14T07:23:00.002-08:00

Today I attended an event at MS office in bangalore where VS 2013 was relaunched infront of around 130 enthusiastic bdotnet members.

It started with TEJASVI KUMAR giving an overview of TFS 2013. I liked the code review enhancements, chat room and VS online feature specially.

Then VIC PARMAR gave an much needed overview of VS 2013 IDE features. The quicklaunch and the synchronization feature were awsome.

Then came the keyboard expert RAJASEKHARAN VENGALIL. As always he showed awsomeness in web feature enhancements like one ASP.net and web api 2.

After a nice launch the session by KARTHIKEYAN ANBARASAN gave knowledge on azure. came to know that MS gives $150 credit for month for a developer to use azure services !!!

Then came Ujjwal. He gave nice ideas on how a modern app should be.He showed new enhancements in Windows 8.1 apis.I came to know how xamarin tool can be used for multi platform apps.

Then cam energetic Lohith. He gave an overview of "Monaco". He showed how azure website can be edited from an web interface. It is awsome. We can edit websites on the fly without opening VS.

Overall a day well spent.
Please join BDOTNET if you are still not.
https://www.facebook.com/groups/BDotNet/

A Comparison of IComparable and IComparer interfaces in C#

2012-12-03T03:36:00.000-08:00

.Net framework provides 2 interfaces (IComparable and IComparer) for purpose of sorting/Comparing .

IComparable <T>:

This interface is to be implemented by the class itself whose instances need ordering/sorting .It provides CompareTo method to implement.

All .net primitive types like Int32,String etc. implements IComparable.So we can easily compare 2 built in types using CompareTo method.

Eg.

int i = 9;

int j=10;

i.CompareTo(j);

Also if we have a collection of a built in type we can sort the collection easily.

Eg.

List<int> lstInt = new List<int>();

lstInt.Sort();

Now say we have a custom type Employee. Lets try the following

List< Employee > lstEmp = new List< Employee >();

…………………………………….

Add some employees

…………………………………

lstEmp.Sort();

Now when we try to do sorting on a custom type we will get exception “Failed to compare two elements in the array.”

It is because to use sort() on anytype , the type has to implement IComparable .Now lets try by implementing IComparable .

e.g.

public class Employee:IComparable<Employee>

{

public string Name { get; set; }

public int JanSalary {get;set; }

public int FebSalary { get; set; }

#region IComparable<Employee> Members

public int CompareTo(Employee other)

{

return this.Name.CompareTo(other.Name);

}

#endregion

}

public class Comparison1

{

List<Employee> lstemp = new List<Employee>();

public void docomparison()

{

Employee emp1 = new Employee();

emp1.Name = "satya";

emp1.JanSalary = 2000;

Employee emp2 = new Employee();

emp2.Name = "biswa";

emp2.JanSalary = 300;

Employee emp3 = new Employee();

emp3.Name = "parsottam";

emp3.JanSalary = 1000;

Employee emp4 = new Employee();

emp4.Name = "kalyani";

emp4.JanSalary = 15000;

EmpJanSalComparer cc = new EmpJanSalComparer();

lstemp.Add(emp1);

lstemp.Add(emp2);

lstemp.Add(emp3);

lstemp.Add(emp4);

lstemp.Sort();

}

Here when we try to sort the list , CompareTo method will be called in Employee class and sorting/ordering can be performed.

We can use LINQ for the same . In that case we don’t have to implement IComparable in “Employee” class.

lstemp.Sort((x,y)=>x.Name.CompareTo(y.Name));

As we have to use IComparable with the class which needs comparison(Employee in above case) , we are restricted to do one type of comparison / ordering only(in this case on basis of property “Name“).

Suppose I need comparison on basis of “JanSalary” then I can’t do it now with IComparable .

IComparer<T> :

For these scenarios we have “IComparer<T>” interface using which we can have our custom comparisons. IComparer is used with a separate class as shown below.

public class EmpJanSalComparer : IComparer<Employee>

{

#region IComparer<Employee> Members

public int Compare(Employee x, Employee y)

{

if (x.JanSalary > y.JanSalary)

{

return 1;

}

else if (x.JanSalary < y.JanSalary)

return -1;

else

return 0;

}

#endregion

}

public class EmpFebSalComparer : IComparer<Employee>

{

#region IComparer<Employee> Members

public int Compare(Employee x, Employee y)

{

if (x.FebSalary > y.FebSalary)

{

return 1;

}

else if (x.FebSalary < y.FebSalary)

return -1;

else

return 0;

}

#endregion

}

So as we see it has a method “Compare” which takes 2 parameters of type “T” .And it is used with a separate class and we can use many of these classes as needed. So it gives flexibility/extendibility in terms of comparison/sorting.

Here for comparing jan month salary we used “EmpJanSalComparer” class and for comparing feb month salary we used “EmpFebSalComparer” .We can use these comparer for sorting as shown below.

EmpJanSalComparer cc = new EmpJanSalComparer();

lstemp.Add(emp1);

lstemp.Add(emp2);

lstemp.Add(emp3);

lstemp.Add(emp4);

lstemp.Sort(cc);

Hope you enjoyed the article. Please leave a honest comment/feedback.

DataTemplateSelector in wpf

2012-10-30T04:59:00.001-07:00

Say I have a scenario where I have to apply different templates to different rows of a listbox,datagrid etc based on some condition .

Example :

I want to show employee data in a listbox . But only if age of employee’s age is greater that 20 he/she will be able to edit their name.

So here for rows where age> 20 we need a textbox in name column and for others we need textblocks.

For these type of scenarios we need DataTemplateSelector .Below is the way to do it.

Employee class

public class Employee

{

public string name { get; set; }

public int age { get; set; }

}

The following steps will give our solution

Step 1:

Create templates in xaml..

  <DataTemplate x:Key="t1">  
       <StackPanel Orientation="Horizontal">  
         <TextBlock Text="{Binding Path=age}"/>  
         <TextBlock Text="  "/>  
         <TextBlock Text="{Binding Path=name}"/>  
       </StackPanel>  
     </DataTemplate>  
     <DataTemplate x:Key="t2">  
       <StackPanel Orientation="Horizontal">  
         <TextBlock Text="{Binding Path=age}"/>  
         <TextBlock Text="  "/>  
         <TextBox Text="{Binding Path=name}"/>  
       </StackPanel>  
   </DataTemplate>

Step 2:

Create a class that inherits DataTemplateSelector class and override its SelectTemplate method.

  public class tselector:DataTemplateSelector  
   {  
     public override DataTemplate SelectTemplate(object item, DependencyObject container)  
     {  
       Employee currEmp = item as Employee;  
       if (currEmp!=null)  
       {  
         if (currEmp.age>20)  
         {  
           return System.Windows.Application.Current.MainWindow.FindResource("t2") as DataTemplate;  
         }  
         else  
         {  
           return System.Windows.Application.Current.MainWindow.FindResource("t1") as DataTemplate;  
         }  
       }  
       return System.Windows.Application.Current.MainWindow.FindResource("t1") as DataTemplate;  
     }  
   }

Step 3:

In ListBox set ItemtemplateSelector property

The Full xaml is

 <Window x:Class="DataTemplateSelectorSol.MainWindow"  
     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
     xmlns:sel="clr-namespace:DataTemplateSelectorSol"  
     Title="MainWindow" Height="350" Width="525">  
   <Window.Resources>  
     <sel:tselector x:Key="tsel"/>  
     <DataTemplate x:Key="t1">  
       <StackPanel Orientation="Horizontal">  
         <TextBlock Text="{Binding Path=age}"/>  
         <TextBlock Text="  "/>  
         <TextBlock Text="{Binding Path=name}"/>  
       </StackPanel>  
     </DataTemplate>  
     <DataTemplate x:Key="t2">  
       <StackPanel Orientation="Horizontal">  
         <TextBlock Text="{Binding Path=age}"/>  
         <TextBlock Text="  "/>  
         <TextBox Text="{Binding Path=name}"/>  
       </StackPanel>  
     </DataTemplate>  
   </Window.Resources>  
   <StackPanel>  
     <ListBox ItemsSource="{Binding}" ItemTemplateSelector="{StaticResource ResourceKey=tsel}" >  
     </ListBox>  
   </StackPanel>  
 </Window>

So its simple to implement.

Named parts in wpf control template

2012-10-28T08:40:00.003-07:00

When a template author in WPF tries to modify the look of the control , it is the controltemplate of the control that get changed.

To retain core functionality of a control , the template looks for some named elements with fixed names.If it is not found in the template , the core functionality of the control won’t work.

Eg.

Lets say we are creating a textbox which has an image as below.

 <TextBox Width="150" Height="80">  
               <TextBox.Template>  
                 <ControlTemplate>  
                   <StackPanel>  
                         <Image Source="tab_active.png"/>  
                   </StackPanel>  
                 </ControlTemplate>  
               </TextBox.Template>  
  </TextBox>

This code will get executed but you can’t type in anything which is one of the core functionality of a textbox.It is because the template of the textbox assumes a name “PART_ContentHost” to be present in the template so that it can apply the core functionality of text box to it. The “PART_ContentHost” named part for a textbox can be named to a ScrollViewer or any control derived from AdornerDecorator (Decorator,Border etc…). Lets modify out textbox with this named part,

 <TextBox Width="150" Height="80">  
               <TextBox.Template>  
                 <ControlTemplate>  
                   <StackPanel>  
                      <Image Source="tab_active.png"/>  
                      <ScrollViewer Name="PART_ContentHost" />  
                   </StackPanel>  
                 </ControlTemplate>  
               </TextBox.Template>  
  </TextBox>

A way to find these named parts is digging into the definition of the control or its base.The TemplatePart attribute contains the named parts of the control.

E.g.

[TemplatePart(Name = "PART_ContentHost", Type = typeof(FrameworkElement))]

[Localizability(LocalizationCategory.Text)]

public abstract class TextBoxBase : Control

for combobox ....

[Localizability(LocalizationCategory.ComboBox)]

[TemplatePart(Name = "PART_EditableTextBox", Type = typeof(TextBox))]

[TemplatePart(Name = "PART_Popup", Type = typeof(Popup))]

[StyleTypedProperty(Property = "ItemContainerStyle", StyleTargetType = typeof(ComboBoxItem))]

public class ComboBox : Selector

Hope this helps.

The amazing TRAILING SPACE theory

2012-07-17T09:18:00.002-07:00

Sql server ignores trailing spaces while comparing using WHERE and HAVING clauses .However this behavior is different for LIKE clause.

Lets some examples on it

 create table #temp ( a varchar(10) )  
 insert into #temp values(' a ')  
 select COUNT(*) from #temp group by a having a=' a ' ->1  
 select COUNT(*) from #temp where a=' a     ' ->1  
 select COUNT(*) from #temp where ' a        '=a ->1  
 select COUNT(*) from #temp where a='a' ->0  
 select COUNT(*) from #temp where a like ' a ' ->1  
 select COUNT(*) from #temp where a like ' a' ->1  
 select COUNT(*) from #temp where a like ' a '->0  
 select COUNT(*) from #temp where ' a ' like a ->1  
 select COUNT(*) from #temp where ' a' like a->0  
 drop table #temp

For LIKE sql has following principle when the string is ASCII(i.e. for varchar and char etc…)

Msdn:

When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned.

However when string is Unicode(nvarchar…) the above rule is not applicable. i.e. there is no significance of trailing spaces and matching is done exactly as it is done for ASCII.

Lets try above example on a Unicode string

 create table #temp ( a nvarchar(10) )  
 insert into #temp values(' a ')  
 select COUNT(*) from #temp group by a having a=' a ' ->1  
 select COUNT(*) from #temp where a=' a     ' ->1  
 select COUNT(*) from #temp where ' a        '=a ->1  
 select COUNT(*) from #temp where a='a' ->0  
 select COUNT(*) from #temp where a like ' a ' ->1  
 select COUNT(*) from #temp where a like ' a' ->0  
 select COUNT(*) from #temp where ' a ' like a->0  
 select COUNT(*) from #temp where ' a' like a ->0  
 drop table #temp

So LIKE is used for pattern matching and not for comparisons. And we need to be extra careful when using LIKE and might take care of trailing spaces using RTRIM to be very sure of correct data retrieval.

For where clause trailing spaces are ignored as can be seen in example and RTRIM can be avoided.

Now we need to remember that string storage is affected by ANSI_PADDING setting which is by default ON .But the comparison is not affected. Again as storage changes , pattern matching(LIKE) changes accordingly. This is very important to see.

 SET ANSI_PADDING OFF  
 create table #temp ( a varchar(10) )  
 insert into #temp values(' a ')  
 select DATALENGTH(a) from #temp ->2  
 select COUNT(*) from #temp where a=' a    ' ->1  
 select COUNT(*) from #temp where a=' a' ->1  
 select COUNT(*) from #temp where a like ' a ' ->0 (important ..)  
 drop table #temp

So storage is done by trimming trailing spaces when ANSI_PADDING is OFF.

Sql server neglects trailing spaces

2012-07-13T04:58:00.001-07:00

create table #temp ( a varchar(10) )

insert into #temp values(' a ')

select COUNT(*) from #temp where a=' a'

select COUNT(*) from #temp where a='a'

first query will return 1

2nd will return 0 as sql server will only ignore trailing spaces

So we can avoid use of RTRIM in many situations .

The NULL Dilemma : The NULL is not equal to NULL : Part-3

2012-07-13T04:53:00.001-07:00

In part 1 and part 2 we learned about NULL propagation , ISNULL,COALESCE, tristate logic etc.

Now let’s learn few remaining parts.

SET ANSI_NULLS

As we saw comparing NULL with another NULL returns UNKNOWN . This is when ANSI_NULLS is ON .

This is ANSI standard to have this value as ON. But in older sql server versions we can get rows returned when we query as WHERE col=NULL .This is in legacy codes . As Microsoft is deprecating this feature avoid using it.In future versions it will always be ON.

The NULL Dilemma : The NULL is not equal to NULL : Part-2

2012-07-06T08:17:00.001-07:00

In previous post(part 1) we learned about tristate logic for NULL ,Not Exists and Null propagation.In this post I will be explaining about ISNULL ,COALESCE, IS NULL, NULL relation with aggregate functions etc.

First of all lets create a temporary table to explain

CREATE TABLE #temp

(

name varchar(20),

age int

)

INSERT INTO #temp VALUES('satya',26)

INSERT INTO #temp VALUES('biswa',NULL)

INSERT INTO #temp VALUES('panu',15)

INSERT INTO #temp VALUES('rahul',NULL)

INSERT INTO #temp VALUES('devi',35)

In last post I said any expression involving NULL becomes NULL. Like NULL +10,NULL/0 etc. But aggregate functions like SUM ,AVG just ignore NULL while computing.

eg.

SELECT AVG(age) FROM #temp - >25

SELECT SUM(age) FROM #temp - >76

IS NULL/IS NOT NULL Predicate

If requirement is select record from table #temp for which age is equal to NULL ..

First temptation is to use below query

SELECT * FROM #temp where age = NULL

But as I explained in previous post about NULL propagation this wont return any records.The IS NULL operator is to be used in this kind of senario.

SELECT * FROM #temp where age IS NULL

This will fetch all record where age is NULL .

o/p

biswa NULL

rahul NULL

Now I want records for which age is not NULL.

SELECT * FROM #temp where age IS NOT NULL

SELECT * FROM #temp where NOT age IS NULL

This will fetch all record where age is not NULL .

O/P

satya 26

panu 15

devi 35

ISNULL/COALESCE Function:

ISNULL function takes 2 arguments and returns the first non-null value.

Syntax:

ISNULL(arg1,arg2) (takes 2 arguments)

Here if arg1 is not null it will return arg1 ,

if arg1 is null, arg2 is not null it will return arg2

else if both are null it will return NULL.

The dataype of return value is same as datatype of arg1.

So if I want records with NULL values I can write as

SELECT * FROM #temp where ISNULL(age,0)=0

To get records without NULL column

SELECT * FROM #temp where ISNULL(age,0)!=0

COALESCE function work similar to ISNULL but it can take anynumber of arguments .

Syntax:

COALESCE(arg1,arg2,arg3,arg4)

Here return value will be the first non-null argument.

Eg.: SELECT * from #temp where COALESCE(age,NULL,0)=0

As it can take any number of parameters it has more advantage compared to ISNULL.Also COALESCE is ANSI standard .

Another very important fact is ISNULL returns data with a datatype same as datatype of first parameter.This causes problem in some situations.

Eg. Add one more row in #temp

INSERT INTO #temp VALUES(NULL,35)

SELECT ISNULL(name,'satyajit mohanty is the author') FROM #temp where ISNULL(name,'')=''

Will return

‘satyajit mohanty is’

Remember ISNULL will return data with same datatype as argument 1. Here it is varchar(20) .So o/p is a varchar with length 20.This would lead to wrong results when you tru to us this data or try to insert it into some other table.

On the other hand COALESCE will return data with a datatype which is same as datatype of argument in accordance with highest datatype presedence.

Just replace ISNULL with COALESCE and check the output which will be correct one.

Also try this

COALESCE(name,'satyajit mohanty is the author',0)

Remember datatype would be datatype of argument in accordance with highest datatype precedence before running above expression in a query.

So try to avoid ISNULL esspecially when you are using the output from ISNULL in further manipulation/storage.

Strange:

When try to run below queries

CREATE TABLE #temp1

(

class int NOT NULL,

age int NULL,

mark AS ISNULL (class,age) Primary Key

)

CREATE TABLE #temp1

(

class int NOT NULL,

age int NULL,

mark AS COALESCE(class,age) Primary Key

)

Here the columns with ISNULL/COALESCE are called as computed columns.It is strange that first query will run sucessfully where as query 2 wont . I ran this on sql server 2008 . Don't know the reason yet.If you find/know the reason please post a reply.

So in this case ISNULL is unavoidable.

In next article we will see on NULLIF as this article already getting sufficiently large :) .

The NULL Dilemma : The NULL is not equal to NULL : Part-1

2012-06-25T09:13:00.000-07:00

In this post I will discuss few interesting facts and problems with NULL in sql server .

Null signifies ‘absence of any data value’ .In other words value is missing or value is unknown. It is important to understand that NULL is not zero or empty value .

NULL propagation

An expression involving NULL will result in NULL .(with few exception which I will discuss in part 2).This is called NULL propagation.

As I said NULL means value is unknown, so if we try to do some operation on unknown data it should give me another unknown data, isn't it ?

Eg. Try below in sql server

NULL + 100 -> add 100 to an unknown value. Clearly I ll get another unknown value i.e. NULL

Similarly NULL * 10 -> NULL

NULL * 0 -> NULL (Interesting)

NULL / 0 -> NULL (Amazing...no divide by zero exception!!!)

On a lighter note, it seems for MS SQL server creators NULL is bigger than 0 .

Three valued logic:

We know about Boolean algebra. The typical TRUE/FALSE thing. Here we compare two values which are known. It results in either TRUE or FALSE.

Now if we do comparison with a NULL , then !!!We can’t get a Boolean value . Rather it results in UNKNOWN.So we now have 3 values possible from a comparison TRUE/FALSE/UNKNOWN . Its not surely bollean logic . Its three valued logic.

Lets see some example .

 DECLARE @var as VARCHAR(10)=NULL  
 IF @var='satya'  
 SELECT 1  
 ELSE  
 SELECT 0

Here @var is NULL . I am trying to compare NULL with a known value. So as I said it will result as UNOWN. Sql server passes anything other than TRUE to else clause . So output is 0 .
Similarly for case statement any comparison not TRUE will go to else part.

SELECT CASE WHEN @var='satya' THEN 1 ELSE 0 END

Same is for while loop too. It will not enter the loop if comparison involves a NULL .

As 2 valued logic has a truth table , our three valued logic too have a truth table.

AND	TRUE	UNKNOWN	FALSE
TRUE	TRUE	UNKNOWN	FALSE
UNKNOWN	UNKNOWN	UNKNOWN	FALSE*
FALSE	FALSE	FALSE*	FALSE

*Simple rule of AND operator, if one expression results false whole operation is false

OR	TRUE	UNKNOWN	FALSE
TRUE	TRUE	TRUE*	TRUE
UNKNOWN	TRUE*	UNKNOWN	UNKNOWN
FALSE	TRUE	UNKNOWN	FALSE

*Simple rule of OR operator, if one expression results TRUE whole operation is true

NOT

TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

Let’s have an example to demonstrate .

 CREATE TABLE #temp  
 (  
 name varchar(20),  
 age int  
 )  
 INSERT INTO #temp VALUES('satya',26)  
 INSERT INTO #temp VALUES('biswa',29)  
 INSERT INTO #temp VALUES('panu',15)  
 INSERT INTO #temp VALUES('rahul',NULL)  
 INSERT INTO #temp VALUES('devi',35)

# temp contains below data.

Satya	26
Biswa	29
Panu	15
Rahul	NULL
Devi	35

Now ,

SELECT * FROM #temp WHERE age>10 AND name='rahul'

Here age>10 evaluates to UNKNOWN and name='rahul' evaluates to TRUE at 4^th row.

So comparison is done as UNKNOWN AND TRUE. So from truth table it is UNKNOWN. So it will not be included in result set.

Now let’s change a bit

SELECT * FROM #temp WHERE age>10 OR name='rahul'

Here for 4^th row comparison is done as 'UNKNOWN OR TRUE' .From truth table it is TRUE.So 4^th row will be included in resultset.

Similar test can be done while checking a query against a table with NULL data..

The NOT IN problem.

Lets create another temporary table in addition to #temp.

 CREATE TABLE #temp1  
 (  
 newage int  
 )  
 INSERT INTO #temp1 VALUES(26)  
 INSERT INTO #temp1 VALUES(15)  
 INSERT INTO #temp1 VALUES(NULL)

#temp1's data

NULL

Now,

SELECT * FROM #temp WHERE age IN(SELECT newage from #temp1)

Will translate to

SELECT * FROM #temp WHERE age = 26 OR age =15 OR age = NULL

Here where age=26 , comparison is done as TRUE OR FALSE OR UNKNOWN.->TRUE

For age = 15 , its FALSE OR TRUE OR UNKNOWN->TRUE

For age = NULL , its UNKNOWN OR UNKNOWN OR UNKNOWN -> UNKNOWN , so excluded

So RESULT :

satya	26
panu	15

Now see following

SELECT * FROM #temp WHERE age NOT IN(SELECT newage from #temp1)

Translates to

SELECT * FROM #temp WHERE age <> 26 AND age <>15 AND age <> NULL

When age = 26 , it is doing FALSE AND TRUE AND UNKNOWN -> FALSE

When age = 15 , its TRUE AND FALSE AND UNKNOWN -> FALSE

WHEN age=NULL , its UNKNOWN OR UNKNOWN OR UNKNOWN -> UNKNOWN

But when age = 29 , its TRUE AND TRUE AND UNKNOWN -> UNKNOWN , so excluded(!!!)

So we ll have empty result set (which is wrong). So we need to be careful when using NOT IN.

One solution is

SELECT * FROM #temp WHERE age NOT IN(SELECT newage from #temp1 where newage IS NOT NULL)

(We will see IS NOT NULL in detail in part2)

O/P

biswa	29
devi	35

But this will miss NULL rows . So preferable way is to use NOT EXISTS

SELECT * FROM #temp WHERE NOT EXISTS(SELECT 1 from #temp1 WHERE age=newage)

Here any 'age' in table #temp for which the subquery doesnot return any row will be included in resultset .

SELECT 1 from #temp1 WHERE age=newage Translates to

SELECT 1 from #temp1 WHERE age=26 OR age=15 OR age=NULL

For age=NULL , it is doing UNKNOWN OR UNKNOWN OR UNKNOWN -> UNKNOWN

So the row with a null will be included in final output of

SELECT * FROM #temp WHERE NOT EXISTS(SELECT 1 from #temp1 WHERE age=newage)

O/P

biswa	29
rahul	NULL
devi	35

Clearly this approach preferable as it will include null row .If requirement needs rows without null we can easily modify it to our needs.

So always prefer to use NOT EXISTS ( I never use NOT IN in my stored procedures due to this reason) .

Use of XACT_ABORT ON

2011-11-10T03:17:00.000-08:00

SET XACT_ABORT
As I told in my previous article (http://dotnetpen.blogspot.com/2011/11/exception-handling-in-sql-server.html) I am bak with small article on XACT_ABORT

XACT_ABORT is a command in sql server . When set to ‘ON’ it will rollback any transaction if an error occurred in it.If used where there is no transaction it will just roll back the statement where error occurred and aborts execution.

When a timeout occurs in client or client presses cancel button catch block wont get called . In this case the connection remains open and the transaction also becomes uncommitable.
If we use SET XACT_ABORT to ON then these situation is taken care and sql server roll backs any current transaction.

syntax:
SET XACT_ABORT ON;

please note that it helps to rollback explicit transactions. But we cant handle exceptions ourselves as we do in catch block. Also if used in stored procedures where no transaction is used , it will just stops executing statements after the error generating statement.But any statement before the error generating statement will get commited .

play with below sample to explore more....
eg.
SET NOCOUNT ON;
SET XACT_ABORT ON;
          BEGIN TRAN
               BEGIN TRY
--RAISERROR('error happened',16,1);
             INSERT INTO t1 VALUES(22);
             INSERT INTO t1 VALUES(null);
             COMMIT
          END TRY
         BEGIN CATCH
                    IF XACT_STATE()<>0
                              ROLLBACK
        END CATCH

It is a good practice to set it 'ON' in every stored procedure where transaction is used.

Exception Handling in SQL SERVER

2011-11-09T05:45:00.000-08:00

First of all let us understand where exception handling can be useful in sqlserver.
I am listing 2 main purpose of it.
1. In transactions it is a must .
2.To return the client some useful/custom error message.

Untill SQL SERVER 2003 exception handling was done by using system variable @@ERROR.But it has its own disadvantages.
EG.
       BEGIN TRANSACTION
               DELETE FROM tablestaff WHERE staffid>30
               IF @@ERROR <> 0
                    BEGIN
                         ROLLBACK
                         RAISERROR ('Error in staff', 16, 1)
                         RETURN
                   END
               DELETE FROM tableEmp WHERE empid<70
               IF @@ERROR <> 0
                   BEGIN
                      ROLLBACK
                      RAISERROR ('Error in Emp', 16, 1)
                      RETURN
                   END
         -- Commit the transaction....
              COMMIT
As seen from above code we can list out major disadvantages of handling exception using @@ERROR variavle .
1. One has to make sure that he/she is not missing to check @@ERROR afterany DML statement.Else we may miss some part while doing error handling which may cause transaction related errors.
2.Stored procedures becomes too long.
3.Diffcult to know exactline number at which error occured. etc....

REMEADY:
SQL SERVER 2005 inducted TRY....CATCH block, much similar to what we are used to in any modern object oriented language.
The above code can be rewritten using TRY...CATCH block as...
          BEGIN TRAN
                BEGIN TRY
                    DELETE FROM tablestaff WHERE staffid>30
                    DELETE FROM tableEmp WHERE empid<70
                    COMMIT
               END TRY
               BEGIN CATCH
                    IF XACT_STATE()<>0
                    ROLLBACK
                    RAISERROR ('Error in line %d, ERROR_SEVERITY(), ERROR_STATE(),ERROR_LINE())
                   END CATCH

Simple stuff !!!(ll explain the functions below soon)
I think the construct is straight forward to be understood.Now lets see what XACT_STATE() stands.
XACR_STATE can have 3 values:
0- No transaction in place currently.
1-Current transaction is a commitable transaction.It means it can be commited.
-1-Current transaction is uncommitable.
Eg:
             BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               SELECT 1/0
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=1 , because error occured when performing a select statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

      If error would have occured while say insertin / deleting operation then XACT_STATE() would have been -1, meaning u cant commit the transaction in any means.

    BEGIN TRAN
                       BEGIN TRY
                               DELETE FROM tablestaff WHERE staffid>30
                               INSER INTO tablestaff values('Enter an int')
                               COMMIT
                      END TRY
                      BEGIN CATCH
--XACT_STATE=-1 , because error occured when performing a insert statement and we can
--commit it in some senarios as this one with no affect to DB table.
                     END CATCH

IMP: If XACT_STATE() is -1 then user cannot perform any operation in database that will affect the current transaction log. These operation can be an insert statement, droping table,rolling back to a saving point etc.Only after rollback he/she can perfor these tasks.

@@TRANCOUNT:
If you see articles in web , many have used this variable instead of XACT_STATE() for rollback. But I felt using XACT_STATE is more appropriate. You can do your own analisys.
@@TRANCOUNT increment by 1 on begin transaction statement. On commit decrements by 1.
On rollback becomes 0.
So it can be used as below in catch block.
IF @@TRANCOUNT>0
ROLLBACK

RAISERROR:It helps to notify the client abt the details of the error , like error message, and othe details passed on to the argument.So if the above SP's called by an .net application , the .net application can able to know where exactly in database error occured.

The syntax is :

RAISERROR(msg id | msg text , severity,state ,<user defined variables>)

Msg id is the error number of the error occurred.It should be greater than 50000 when created by user.By default when we raise errors msg id generated is 50000.

Now the functions used in catch block.
        ERROR_SEVERITY(): Returns severity level of the error.
        ERROR_NUMBER():returns same as @@ERROR
        ERROR_LINE(): line at which the error occured
        ERROR_PROCEDURE():sp name where error occured
        ERROR_MESSAGE(): full error message that got generated
These help in letting the client know where error has occured.
eg.
SELECT @sev=ERROR_SEVERITY()
SELECT @state=ERROR_STATE()
SELECT @line=ERROR_LINE()
SELECT @msg=ERROR_MESSAGE()
RAISERROR('error occured at line %d. The error message is : %s',@severity,@state,@line,@msg)

Note the identifiers '%d' '%s' work similar to printf statement in 'C'.

NOTE: These functions will work only in catch block.Elsewhere it will return null.

There is another feature where we can add command 'SET XACT_ABORT' to 'ON' for automatic rollback when an error occurs.This I will explain in a later article.

Also in the next version of sql server code named 'Denali' a new keyword 'throw' has been added which overcomes some shortcomings of RAISERROR. I have not yet got a chance to try it as I dont have sql server 2012 :-(.

So next time you go for exception handling , keep in mind all these features .

NVarchar(MAX)/Varchar(Max) in sql server

2011-09-19T23:26:00.000-07:00

Quite often we come across datatypes in sql server with a 'Max' as a size !!!
Lets first understand what it stand for.In sql server varchar can have 8000 characters(=8000 bytes), Nvarchar 4000 characters(1 unicode char=2 bytes).In sql server 2005 a new keyword 'MAX' is added aiming to replace older datatypes like TEXT,NTEXT,IMAGE etc.The size of data it can hold is 2 GB , the same size that can be hold by TEXT,NTEXT etc.

Problem with TEXT,NTEXT,IMAGE:
In sql server these 3 datatypes are used as BLOBs(Binary Large Objects).What it means data belonging to these datatypes stored in collection of 8 kb datapages that are separate from datapages that store other data in the same table.A pointer is stored in the original column .So when we try to read the data from table , a LOB table scan(LOB datapages are read) is performed which is expensive.

With Varchar(MAX) data is stored in the same datapages as used by other columns.(of course upto 8kb it is fine , above it sqlserver uses some datapages called as overflow page.But accessing these pages are handled well by sqlserver and no lob datapages needed.).So simple table scan is performed when select statement is used.(faster than a LOB scan)

So when programming sql server we should avoid the use of these BLOB types as these will be removed in future editions of sql server.

An Example:
Take two tables Table_1 and Table_2

Table_1: LobColumn ntext
Table_2:MaxColumn nvarchar(max)

now see the below analisys after inserting say 26 rows

SET STATISTICS IO ON
Select * from Table_1
(26 rows affected)
Table 'Table_1'.Scan count 1 ,logical read 1,physical reads 0, read-ahead reads 0,lob logical reads 52,lob physical reads 0,lob read ahead reads 0

SET STATISTICS IO ON
Select * from Table_2
(26 rows affected)
Table 'Table_2'.Scan count 1 ,logical read 1,physical reads 0, read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read ahead reads 0

Clearly for Table_2 scan is faster as there is no LOB data read.

Apart from it sql server does not allow BLOB types to be used in stored procedure/user defined functions.Also string library functions like REPLACE etc are not allowd to be used with BLOB types.
NOTE:Always use Varchar unless you need to store multilinguial characters, in which case use Nvarchar.Remember Nvarchar takes more memory than varchar.

How to use MAX datatypes in Stored Procedures:
Consider following statements in a stored procedure :

Declare @maxData nvarchar(max)
SET @maxData='-----some statements--------'

When we use statement in this way , after 4000 chars this will throw exception saying nvarchar can store maximum 4000 characters.
But in principle it should have stored 2GB chars right !!!!
Here LHS is of type nvarchar(max) , RHS is of type simple nvarchar(which can store 4000 bytes).The workaround of the problem is break @maxData and use as below:

@maxData='-----some statements----'
@maxData=@maxData+'-----some statements----'
@maxData=@maxData+'-----some statements----'

In each statement RHS should have a new string with maximum 4000 chars.

(This is a pratical problem I faced in one of my project and found it helpful)
Happy Sql Programming :-)
Please leave an honest comment so that I can improve on my blogging aswell as technical skill :)