Store Multiple NULL values with unique data in SQL Server

Hello friends,

Sometimes we get the data that needs to be unique but can also get NULL records. Existing ways to achieve uniqueness doesn’t allow NULLs (Primary Key) or allow max one NULL (Unique Constraint).

Let’s take an example to understand this better. Suppose, you have an Employee table with fields like Id, FirstName, MiddleName, LastName, Email etc. As per requirement, email needs to be unique (if supplied), however, users are also allowed to register without entering email and hence in the database Email is a nullable field.

So how would you achieve the Email uniqueness along with having more than one NULLs.

CREATE UNIQUE NONCLUSTERED INDEX [UX_Employee_Email] ON [dbo].Employee WHERE Email IS NOT NULL

Above code will ensure that Email doesn’t have duplicate data however it may store more than one NULLs.

I hope you may have liked the tip. You share your comments.

Enable CORS in ASP.NET Core Web API

Hello friends,

As we know that in Web API, CORS is by default disabled due to security reasons. and hence if a client tries to access API in different server and port, you can end up with error as “Origin http://localhost:xxxx is not allowed by Access-Control-Allow-Origin

To enable it in ASP.NET Core Web API, there are few steps involved as following.

Go to startup.cs

Put code as follows inside ConfigureServices method

services.AddCors();

Put code as follows inside Configure method

app.UseCors(
options => options.WithOrigins("http://localhost", "https://localhost")
.AllowAnyMethod()
.AllowAnyHeader()
.AllowCredentials()
);

In the WithOrigins array, you can add or remove the client URL based on your local machine domain and port settings.

Hope you found the post useful. Please let me know your comment below.

Working with Prototype Design Pattern

Hello friends, I am resuming series on design patterns. Today we will go through another design pattern called Prototype. It falls under a creational pattern category. Before talking about its implementation let’s begin with defining it.

A prototype is a design pattern that believes and enforces object cloning or copying over new object creation.

But object copy is that important

The answer is simple, copying objects improves performance in a situation when one needs a lot of objects and creating them turns out to be a costly affair.
Since we are talking about copy let’s discuss shallow and deep copy as well.

Shallow Copy

As the name suggests, it’s a partial or incomplete copy and any reference types of the source object will not be copied and will keep on referring to the same memory location.

In .NET it is achieved by the MemberwiseClone method.

Deep Copy

Similar to the name, it’s a real copy of all the members in the source object including reference types. For reference types, it creates a new memory location in heap to provide true copy experience. We need to write custom code to achieve deep copy. Now let’s talk about the implementation of the pattern.
Assume we have an abstract class Vehicle that has the following structure.

  • internal abstract class Vehicle  
  • {  
  •     internal string VehicleType  
  •     {  
  •         get;  
  •         set;  
  •     }  
  •     internal string Brand  
  •     {  
  •         get;  
  •         set;  
  •     }  
  •     internal string Model  
  •     {  
  •         get;  
  •         set;  
  •     }  
  •     internal abstract void ShowDetails();  
  •     internal abstract Vehicle ShallowCopy();  
  •     internal abstract Vehicle DeepCopy();  
  •     internal ExtraInformation MoreInfo = new ExtraInformation();  
  • }  

We can take interface as well but in that we need to override all the properties again which looks duplicate in this case. Here class Vehicle works like the prototype. We have added class ExtraInformation to depict deep copy and shallow copy concepts.

  • class ExtraInformation  
  • {   
  •    internal string Cost { getset; }  
  •    internal int Ratings { getset; }  
  • }  

Now let’s have concrete prototype FourWheelar as in the following.

  • internal class FourWheelar: Vehicle, ICloneable  
  • {  
  •     internal override Vehicle ShallowCopy()  
  •     {  
  •         return this.MemberwiseClone() as Vehicle;  
  •     }  
  •     internal override Vehicle DeepCopy()  
  •     {  
  •         Vehicle vehicle = this.MemberwiseClone() as Vehicle;  
  •         vehicle.MoreInfo = new ExtraInformation();  
  •         vehicle.MoreInfo.Cost = this.MoreInfo.Cost;  
  •         vehicle.MoreInfo.Ratings = this.MoreInfo.Ratings;  
  •         return vehicle;  
  •     }  
  •     internal override void ShowDetails()  
  •     {  
  •         Console.WriteLine(string.Format(“Vehicle Type: {0} \tBrand: {1} \tModel: {2} \tCost: {3} \tRatings: {4}”, this.VehicleType, this.Brand, this.Model, this.MoreInfo.Cost, this.MoreInfo.Ratings));  
  •     }  
  •     public object Clone()  
  •     {  
  •         return DeepCopy();  
  •     }  
  • }  

You can see here that we have also implemented the ICloneable interface. To be frank, it’s not required here but I have added it to present its existence. It just has a Clone method and when called, we are returning the custom DeepCopy method.

So the setup is done now, let’s use this in the client.

  • static void Main()  
  • {  
  •     Console.Title = “Prototype pattern demo”;  
  •     FourWheelar car = new FourWheelar();  
  •     car.VehicleType = “Car”;  
  •     car.Brand = “Maruti”;  
  •     car.Model = “Swift”;  
  •     car.MoreInfo.Cost = “6.5 Lakhs INR”;  
  •     car.MoreInfo.Ratings = 2;  
  •     FourWheelar truck = car.ShallowCopy() as FourWheelar;  
  •     truck.VehicleType = “Truck”;  
  •     truck.Brand = “Tata”;  
  •     truck.Model = “ACE”;  
  •     truck.MoreInfo.Cost = “4.5 Lakhs INR”;  
  •     truck.MoreInfo.Ratings = 3;  
  •     Console.WriteLine(“******************************Shallow Copy******************************”);  
  •     Console.WriteLine(“Origional Object:”);  
  •     car.ShowDetails();  
  •     Console.WriteLine();  
  •     Console.WriteLine(“Shallow Cloned Object:”);  
  •     truck.ShowDetails();  
  • }  

Output:

run

You can see here that when we change the cost and ratings in the target object, the original object is also getting changed. This is happening because the copied object is still holding the same reference as the source.

Now let’s test deep copy.

  • static void Main()  
  • {  
  •     Console.Title = “Prototype pattern demo”;  
  •     FourWheelar car = new FourWheelar();  
  •     car.VehicleType = “Car”;  
  •     car.Brand = “Maruti”;  
  •     car.Model = “Swift”;  
  •     car.MoreInfo.Cost = “6.5 Lakhs INR”;  
  •     car.MoreInfo.Ratings = 2;  
  •     FourWheelar truckNew = car.Clone() as FourWheelar;  
  •     truckNew.VehicleType = “Truck”;  
  •     truckNew.Brand = “Tata”;  
  •     truckNew.Model = “ACE”;  
  •     truckNew.MoreInfo.Cost = “4.5 Lakhs INR”;  
  •     truckNew.MoreInfo.Ratings = 3;  
  •     Console.WriteLine(“******************************Deep Copy******************************”);  
  •     Console.WriteLine(“Origional Object:”);  
  •     car.ShowDetails();  
  •     Console.WriteLine();  
  •     Console.WriteLine(“Deep Cloned Object:”);  
  •     truckNew.ShowDetails();  
  • }  

Output:

Output

As you can see here that when we change the cost and ratings in the target object, original object is not getting changed. This is happening because the copied object is having new reference.

Hope you have liked the article. Looking forward for your comments/suggestions.