Linq query between two lists | Update one list with another list based on a condition

Let's say we have 2 lists ListOne and ListTwo and we want to update the ListOne.Name with ListTwo.Name whenever there is a match with the ID in the both lists using linq then below is the simplest query

Search a table name in all databases at once

It's very difficult to select each and every database and search for the table from sys.tables something look like below 

Select * from sys.tables where name like '%TableName_Here%'

What if you don't know the exact database, do you search all the databases to find the table?

if your database server has 100 database's then it will be a very tedious task for you to select a database and search so to avoid such pain below is the procedure you can execute this procedure in any of the databases and execute the procedure by giving the table name as a parameter.

Hope the above procedure will solve your problem cheers... !!!

Super easy mocking with NSubstitute | Dot Net Recipe

In this post, I will share how to super easy mocking with NSubstitute. First, let's understand the use of mocking and why we need to mock the methods.  In any project, if you are required to write unit test cases then the project is not having any external calls like DataBase or Services then it is easy to write unit test cases, 

Let's understand what is external call, any method call which is not under our project can be called as an external call.

But in real time projects, it's not the case where the project involves at least some external calls it may be DB or other service or something else. 

Any unit test case which you write is required to execute in within the milliseconds, what if it takes more that one minute, then that is the bad practice of writing the unit test cases.

So what's all the point of talking about external calls and unit test case execution performance? I will tell you,

Let's say your unit test case contains any DB call which will get the data for you then that call will take the maximum amount of time in your unit test, so drastically it will reduce the test case execution performance. so you want to eliminate that DB call? 

But from where you get the data here comes the concept of Mocking where you will mock the DB call and return the likewise data which DB will return.

So to mock a method you will need a mechanism, here I am going to explain that mechanism with NSubstitute.


I have created a sample demo project where it will read the student marks from the DB and calculates the percentage of the student. 

So now, I want to write a  test case for the CalculateStudentPercentage Method. You can create Unit test project from "Add New Project"  Window or you can directly "right click" on the method and select "Create Unit Tests",

To use the NSubstitute, firstly you need to install the NSubstitute package

Install-Package NSubstitute

I have the following DataManager.cs Class and Interface IDataManager.cs where it will be used to call the DB and get the results

and I have Processor.cs class where it will have the business logic in your application and you need to write the test cases for the methods which are there in this class. As of now we have one method CalculateStudentPercentage

The only requirement is, we need to have an Interface architecture for your processor so that we can pass NSubstitute to the processor.

Let's write a unit test case for the above method with mocking the DataManager method call using NSubstitute. In the below, we have created a substitute for the IDatamanager and we are returning mockMarksList instead of the original DB Results.

Whenever we have a DB call we are returning the mock data which we have created in the test method. In the below method we have 2 steps

• Creating an NSubstitute.
• Invocking the Substitute method and returning the mock data.

Now we have just mocked the GetStudentMarks method and we have returned our mock data instead of the actual data like these you can avoid the external calls. If you any doubts and comments feel free to comment. 

Convert Generic List to DataTable using Extension Method

If you want to convert a generic list to DataTable you can use the below extension method for IList<T>

Let's say you have the following list with you 

List<someClass> someClassObjectList = new List<someClass>();

DataTable someDataTable = new DataTable();

and you want to convert the someClassObjectList to DataTable object 

The possible solution you end up by writing a convert or translate method which will convert the each and every property and assigns to DataTable object, something looks like this




By using the above code also your job will get done, but what if your class has 100 properties and what if you want convert lots of different generic lists like this. You can't write the different method for the different generic list, and you don't have the patience to list the 100 properties like above. 

Now what do, you can write a single extension method.

Create a static class in your project with some name like "Extensions" and write the below code in that class

Now in your actual class  wherever you want to convert the generic list to DataTable write code like below

var someDataTable  = someClassObjectList .ToDataTable<someClass>();

using a single method you can convert any generic list to DataTable.

var anotherDataTable  = anotherClassObjectList .ToDataTable<anotherClass>();

var oneMoreDataTable  = oneMoreClassObjectList .ToDataTable<oneMoreClass>();
.
.
.

Write once use anywhere.

Deleting only dupicate rows from the table using CTE

Suppose we have a table "Users" with columns Id, UserName and the table contains some duplicate rows.

How to remove only the duplicate rows from the above table.

Below is the Solution:

How to enable SQL Server IntelliSense | Troubleshooting SQL Server IntelliSense

Intellisense is the most useful feature in SQL Server. Full stack developers who are working on the SQL Server Management Studio they will know the pain if intellisense is not working, because they are habituated with the other IDE's which are much powerful in intellisense than SQL Server Management Studio. Let's find out how to enable the Intellisense feature. 

1. Open SQL Server Management Studio
2. Go to Tools Or Click Alt + T and click Options
3.  Under Text Editor click on Transact - SQL and IntelliSense
4. Check "Enable IntelliSense" and click "OK".


Want to check IntelliSense is enabled or not ?

1. Open SQL Server Management Studio
2. Go to Query Or click Alt + Q,
3.  You will find an option "IntelliSense Enabled", see whether that is highlighted or not.


IntelliSense is stopped working in between, don't worry we have an option to make it work again.

1. Open SQL Server Management Studio
2. Got to Edit or click Alt + E
3. Now, click on IntelliSense and click "Refresh Local Cache"
4. Or you can use Shortcut key "Ctrl + Shift + R"

Still, Intellisense is not working uff, you got thug life then. There could be various reasons for this check out your reason. 

1. Have you installed any recent Microsoft products like visual studio 
2. Have you upgraded your SQL Server Management Studio to higher version. 
3. Have you uninstalled and again installed your SQL Server Studio

Could be other reasons also above are the most common,

Now, what to do ??

We live in a technology where we get everything in a form of plugins, you can install third party plugins which enables IntelliSense without doing any settings, one-of the most liked plugin is SQL Complete you can download the basic version for free. If you are rich enough then you can buy the premium :P :P

click on the below link, download and install. Enjoy free IntelliSense.

SQL Complete

How to update multiple columns with multiple rows with different values using single query

Let's say a table named Audit_Table have 3 columns: AuditId, Old value and New Value 

Select * from Audit_Table

AuditId     Old Value                             New Value
----------- ----------------------       ------------------------
1             1                                        2   
2             3                                        4 
2             5                                        6
3             7                                        8


And, we have one more table named Department_Table with the following columns DepartmentId and Department Name

Select * from Department_Table

DepartmentId  Department_Name
------------        ---------------
1                       Computers 
2                       Electrical
3                       Civil     
4                       Mechanical
5                       Electronic
6                       Chemical  
7                       Bio - Tech
8                       Embeded   

In the above Audit_Table, Old Value and New Value having the values of Department id's and you want to update those values with the Department Names. But you want to update the Audit table with a single query, you don't want to run multiple update statements

Below is the query for which we need top update multiple columns and multiple rows with different values

UPDATE Audit_Table SET [Old Value]= (SELECT Department_Name
                                 FROM Department_Table 
                                 WHERE [Old Value] = DepartmentId),
                          [New Value]= (SELECT Department_Name
                                 FROM Department_Table 
                                 WHERE [New Value] = DepartmentId)

And, output goes like this, in a single query you have updated multiple columns and 'n' number of rows with a different values. 

Select * from Audit_Table

AuditId     Old Value                                          New Value
----------- ----------------------------------- ------------------------
1             Computers                                          Electrical
2             Civil                                                     Mechanical
2             Electronic                                            Chemical  
3             Bio - Tech                                           Embeded