Coaelsce in SQL Server

Coalesce in SQL server basically acts like a Case statement i.e. if value in one of the column is not available then use the value of the second column, and if that is also not available, then use the third column. If none of the column has the value, then null is returned for that value. It’s syntax is :

COALESCE ( expression [ 1...n ] )

Here, expressions 1….n are like possible case statements. Let’s discuss this in detail. Consider the following data in a table :

COALESCE in SQL We have different values of alias names for all the users. But some of them have Alias1, some have Alias2 and some have Alias3. We are required to display alias names based on their availability in the database i.e. if we don’t have Alias1, then display Alias2 and if it is also not available, then use Alias3. This can be easily achieved using COALESCE in the Select statement.

To do this, we simply write the following query with the COALESCE keyword.

SELECT EmpId, EmpName,
COALESCE (Empalias1, empalias2, empalias3) AS AliasName
FROM Employees

Execute the query and see the results.COALESCE in SQL Server

COALESCE acts like case statements and checks if the Alias1 is available or not. If not, then use the Alias2 column and even if that is not available, then use Alias3. This is what happens in the records of Ray, Chris, Jenny and Simi. For Norman, there was no value in any of the alias columns, so it returned null for it.

So this is how we can use the COALESCE in SQL server. Happy coding…!!!

Posted in SQL Server | Tagged | Leave a comment

Friend assemblies in .NET using C#

What is internal access modifier in C# ?

Internal modifier allow to access to the elements of an assembly, within the same assembly only. This means if we have one class library type project with two classes Class1 and Class2, any internal classes in it, will be accessible only within this class library project. When we add reference of it’s dll in any other project, the internal classes of this library will not be accessible in that project.

By default, class is internal in nature.

So, using internal access modifier is like telling our children not to talk with the strangers. We tell our assembly not to share the internal classes with the stranger assemblies.

Access internal members outside the assembly

This was basic about the internal modifier. But suppose we have created a product based library. Now we need to provide access to one of our internal member class to some of our customers and not all of them. Very first thing that will come into our minds is to make the class public. But this will expose the class to all the customers, which they don not even require. Moreover, it results in security vulnerability. This is where the concept of friend assemblies come into the role. We will simply tell our assembly or dll that this class is your friend and you can share your internal members with it. To do this, we just have to add the following line of code on our namespace.

[assembly: InternalsVisibleToAttribute("Name_of_Friend_Class")]

And that’s it. We are done. Build the assembly and we can now access the internal class members as well.

Adding this attribute instructs our assembly to share the details with stranger assemblies. It’s like telling our children name of the people with whom they can talk.

Access internal members of the class outside the assembly – Example

For this, we will create a new project solution and add two project types – one class library named ClassLibrary and other, console application named Consumer. In class library, we add two classes, one as internal named InternalClass and other as public named PublicClass. 

Friend Assembly in .Net

Next, we build this project and add its reference in the Consumer project. Then we try to access these two classes in the console application. We will be able to access the public class but not the internal class.

Friend Assembly in .Net using C#

Now in order to access the internal class in the console application, we add the InternalsVisibleTo attribute, with the name of the assembly in which we would like to access the internal members. In our case, it is named Consumer. See the code below.

Friend Assembly in .Net using C#

Build the code and we can now easily access the internal members. In this way, we can add as many assembly names, as we need.

Friend Assembly in .Net using C#

 

So this all what we had to do to allow access to internal classes. We can add multiple attributes, with the names of the assemblies with which we would like to share these internal classes. But the condition here is that we need to add this attribute at the namespace level. Hope you enjoyed reading it…!!!

Posted in .Net, C# | Tagged , , | Leave a comment

REPLACE in SQL Server

REPLACE function in SQL is used to replace all the occurrences of a specified string, in a given string. It’s syntax is

REPLACE(existing_string, string_to_replace, replacement_string)

Here,

  • existing_string is the string in which we would like to replace all the occurrences specific string. This could be any column of an existing table also.
  • string_to_replace is the string, of which, all the occurrences are to be replaced in the existing string.
  • replacement_string is the new string that we would like to be replaced in place of string_to_replace.

Let’s use a simple example to discuss this concept now. See the commands below :

Replace in SQL Server

We have declared a simple string and applied the replace function, to replace all the occurrences of ‘Test’ with ‘updated’. It will simply REPLACE all the occurrences of ‘Test’ in the existing string. Similarly we could have used any table column in the Select statement above.

STUFF vs REPLACE

It might sound quite similar to the stuff function but they are different. Stuff can be only used to replace a particular string in our existing string. But, replace will replace all the occurrences of a string, in the existing string. Also, stuff may or may not replace any characters in the string, but replace will always replace the specified characters in the string. In other words, stuff can play the role of insertion and replacement in a string, but replace will always play the role of replacement in a string.

Posted in SQL Server | Tagged | Leave a comment

Subquery vs Co-related subquery

It might sound that both the queries are same but there is difference between the two. The difference is order of how these queries are executed and what is the relation between these two queries. Let’s create two sample tables and some sample data. So our initial data setup is like :

Sub-query vs Co-related sub-Query

Now let’s discuss these queries one by one.

Normal Sub-query : In this type of query, the outer or parent query and the inner query are independent of each other when executing. In this, inner query is executed first and then the result set returned by this query, is used by the outer query to generate the final result set. Let’s write a sub-query with the data setup we created above. This will provide us with all the employees, which have a record in the EmployeeDepartment table. So our query is like below, with the results.

Sub-query vs Co-related sub-Query

How normal sub-query works

First it selects all the records in the EmployeeDepartment table, which returns all the EmployeeId‘s in the table (4 records). Then, using the IN operator, the outer query gets all the records from Employees table, which have an EmployeeId in the result set returned by the inner query, to return only the matching records. So comes the output which we can see above.

Co-related sub-query : In this type of query, the two queries i.e. the outer and the inner query are dependent or linked to each other, to generate the final result set. Now, let’s use the co-related sub-query to get the same results and then we will discuss how they are linked and how it works. Our query changes to the following and we can see the results accordingly.

Sub-query vs Co-related sub-Query

How co-related sub query works 

First of all, the outer query executes and selects the first record from the Employees table. In this case, it is Mark with EmpId as 1. This record is then sent as an input to the inner query, where it checks whether the EmployeeDepartment table has any record with EmployeeId matching the EmpId of the record Mark, received from the outer query. If it finds any match in EmployeeDepartment table, then this record is kept for the result set (as the where condition in outer query is satisfied by it) else the outer query fetches the next record. Again, the second record is sent as an input to the inner query and this process continues until all the records of the Employees table are traversed by the outer query.
So we can see here that the inner query is dependent on the input or EmpId from the outer query to generate the results. This is how the outer and inner query are linked to each other. So this is how the sub-query and co-related sub-query work.

Posted in SQL Server | Tagged | Leave a comment

Stuff in SQL Server

Stuff function in SQL is used to insert one string into the another string. It may or may not result in replacing the characters of the existing string, based on the parameters provided to the function. It’s syntax is

Stuff (existing_string, start_point, length, string_to_insert)

Here,

  • existing_string is the string in which we would like to insert our string. This could be column name of any table also.
  • start_point is the position from where we would like to start inserting our string
  • length is the number of characters which we like to replace in the existing string. If 0 is specified, then the new string will be added to the existing string and no character in existing string will be replaced.
  • string_to_insert is the new string we would like to insert in the existing string.

Let’s use a simple example to discuss this concept now. See the commands below :

Stuff in SQL Server

We have declared a simple string and applied STUFF function with two different arguments. In first, we have specified the 9th position as the start_point and 2 as the length of the characters to be replaced in the existing_string. So it updates the existing string accordingly. In second case, we have specified the length as 0. So it does not replace any character in the existing_string and just fits the new string at the specified position i.e. 9th position.

Similarly we could have used any table column in the Select statement above.

Posted in SQL Server | Tagged | Leave a comment