How to know whether table is replicated or not in SQL Server

SELECT publisher,Publisher_Db,publication,article
FROM dbo.MSreplication_objects
where article = 'Table_Name' 

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

	ListOne.Join(ListTwo, (list1) => list1.Id, (list2) => list2.Id, (list1, list2) =>
	{
	list1.Name = list2.Name;
	return list1;
	}).ToList();

view raw gistfile1.txt hosted with ❤ by GitHub

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.

	CREATE PROCEDURE up_GetTableNameInAllDatabase
	@TableName VARCHAR(256)
	AS
	DECLARE @DataBaseName VARCHAR(256)
	DECLARE @SQL VARCHAR(512)
	DECLARE @getDBName CURSOR
	SET @getDBName = CURSOR FOR
	SELECT name
	FROM sys.databases
	CREATE TABLE #TmpTable (DBName VARCHAR(256),
	SchemaName VARCHAR(256),
	TableName VARCHAR(256))
	OPEN @getDBName
	FETCH NEXT
	FROM @getDBName INTO @DataBaseName
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SET @SQL = 'USE ' + @DataBaseName + ';
	INSERT INTO #TmpTable
	SELECT '''+ @DataBaseName + ''' AS DBName,
	SCHEMA_NAME(schema_id) AS SchemaName,
	name AS TableName
	FROM sys.tables
	WHERE name LIKE ''%' + @TableName + '%'''
	EXEC (@SQL)
	FETCH NEXT
	FROM @getDBName INTO @DataBaseName
	END
	CLOSE @getDBName
	DEALLOCATE @getDBName
	SELECT *
	FROM #TmpTable
	DROP TABLE #TmpTable
	GO
	--EXEC up_GetTableNameInAllDatabase 'TableName_Here'

view raw GetTableNameInallDatabase hosted with ❤ by GitHub

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

	using System.Collections.Generic;
	namespace NSubstitute_Demo
	{
	class DataManager : IDataManager
	{
	public List<int> GetStudentMarks(int studentId)
	{
	//Open connection
	//Call to DB to get the student marks
	//Return the student marks
	return new List<int> { 85, 98, 87, 76, 99, 67 };
	}
	}
	}

view raw DataManager.cs hosted with ❤ by GitHub

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.

	using System.Collections.Generic;
	using System.Linq;
	namespace NSubstitute_Demo
	{
	public class Processor
	{
	IDataManager _dataMgr;
	public Processor(IDataManager dataMgr)
	{
	_dataMgr = dataMgr;
	}
	public double CalculateStudentPercentage(int studentId)
	{
	List<int> marks =_dataMgr.GetStudentMarks(studentId);
	int percentage = marks.Aggregate((a,b) => (a + b) / marks.Count);
	return percentage;
	}
	}
	}

view raw Processor.cs hosted with ❤ by GitHub

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.

	using Microsoft.VisualStudio.TestTools.UnitTesting;
	using NSubstitute;
	using System.Collections.Generic;
	using System.Linq;
	namespace NSubstitute_Demo.Tests
	{
	[TestClass()]
	public class ProcessorTests
	{
	[TestMethod()]
	public void CalculateStudentPercentageTest()
	{
	//Arrange
	List<int> mockMarksList = new List<int> { 50, 60, 70, 80, 90, 60 };
	double expected = mockMarksList.Aggregate((a, b) => (a + b) / mockMarksList.Count);
	var substituteDataMgr = Substitute.For<IDataManager>();
	substituteDataMgr.GetStudentMarks(1101601306)
	.ReturnsForAnyArgs(mockMarksList);
	Processor _processor = new Processor(substituteDataMgr);
	//Act
	double actual = _processor.CalculateStudentPercentage(1101601306);
	//Assert
	Assert.AreEqual(actual,expected);
	}
	}
	}

view raw ProcessorTests.cs hosted with ❤ by GitHub

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

	public DataTable ConvertsomeClassTosomeDataTable(List<someClass> someClassObjectList)
	{
	DataTable convertedsomeClassObjectList = new DataTable("SomeDataTable");
	// Lest assume we have 3 properties in your someClass Class
	convertedsomeClassObjectList.Columns.Add("Property1", typeof(int));
	convertedsomeClassObjectList.Columns.Add("Property2", typeof(DateTime));
	convertedsomeClassObjectList.Columns.Add("Property3", typeof(int));
	foreach (someClass obj in someClassObjectList)
	{
	convertedsomeClassObjectList.Rows.Add(
	obj.Property1,
	obj.Property2,
	obj.Property3);
	}
	return convertedsomeClassObjectList;
	}

view raw ConvertsomeClassTosomeDataTable hosted with ❤ by GitHub

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

	public static class Extensions
	{
	public static DataTable ToDataTable<T>(this IList<T> data)
	{
	PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
	DataTable DataTable = new DataTable();
	for (int i = 0; i < properties.Count; i++)
	{
	PropertyDescriptor prop = properties[i];
	DataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(
	prop.PropertyType) ?? prop.PropertyType);
	}
	object[] objectValues = new object[properties.Count];
	foreach (T item in data)
	{
	for (int i = 0; i < objectValues.Length; i++)
	{
	objectValues[i] = properties[i].GetValue(item);
	}
	DataTable.Rows.Add(objectValues);
	}
	DataTable.TableName = typeof(T).Name + "Your Table Name";
	return DataTable;
	}
	}

view raw Extensions.cs hosted with ❤ by GitHub

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:

	WITH CTE
	AS
	(
	SELECT ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY Id) AS rowNum,
	Id,UserName FROM Users)
	DELETE FROM CTE WHERE rowNum > 1

view raw Delete with CTE hosted with ❤ by GitHub

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   

Must Declare a Scalar Variable Error - Solved | How to insert apostrophe in SQL Query String

You have to use double apostrophe to insert a single apostrophe in SQL Query String. Below is the sample query where it tries to send mail, in which we have to write a SQL query to fetch the details.

In the SQL query we are using a scalar variable, If we are not converting that in to string format SQL throws an error saying "Must declare a scalar variable" and you cant escape from that error

Lets say, we are using a date time value in where condition then its need to be under apostrophes, when we get the value from scalar variable then that value should be converted in to char to form complete SQL query string.

Instead of doing all patch-ups, you can easily convert that value to character and insert in to the query as below.. 


DECLARE @scalarVariable DATETIME

SET @scalarVariable  = (SELECT SomeValue FROM SomeTable )


DECLARE @tab CHAR(1)= CHAR(9);
DECLARE @fileName VARCHAR(256);
DECLARE @sql nvarchar(MAX);
DECLARE @subject nvarchar(MAX);

SET @fileName = 'SomeFile.xls';
SET @sql = N'SELECT * FROM SomeTable WHERE SomeColumn  >' + ''''+CONVERT(VARCHAR(24),@scalarVariable ,21)+'''';


SET @subject = 'Some Subject';

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SomeProfile'
,@recipients='abcd@gmail.com'
,@subject= @subject
,@body='Some data have been changed'
,@body_format = 'Text'
,@query = @sql
,@query_result_separator =  @tab
,@attach_query_result_as_file = 1
,@query_attachment_filename= @fileName
,@query_result_width = 32767
,@query_result_no_padding = 1

How to Disable Startup Programs

If we see unwanted applications or programs are opening when we start the computer. its very annoying right :( :(

Ok let's see how we can stop from that or How to Disable Startup Programs.

1. Press Windows Key + R, now you will be able to see Run Dialogue Box  

2. Now, type msconfig in the Run and Click 'OK'

3. Click the Startup Tab in the System Configuration Window, you will get to see all the programs which are               running in the Startup.

4. Search for the program which is starting when window starts, uncheck from the list and click 'Apply' and 'OK'

5. It will ask for the 'Restart' to affect the changes so, restart the computer. Now, you are free from those                   unwanted programs which are annoying you. 

Know your current DataBase Mail Configuration

Its very important to give the correct @Profile_Name which are configured for a Data base while we are executing the sp_send_dbmail.

Sometimes, everything seems to be correct, even though you wont receive mail and you could not figure out what could be the issue.

So, first get to know are you giving correct @Profile_Name or not,  that you can see using the following predefined system functions

EXEC msdb.dbo.sysmail_help_principalprofile_sp;


When we executes the above query you will see different @Profile_Names which are configured.

What is SET XACT_ABORT ON | OFF

If you have a 10 SQL statements in a stored procedure, where 10th statement is an update statement which updates time in a table indicating that on that particular time that stored procedure got executed successfully. 

Lets consider a situation where 5th statement got run-time error which leads incorrect results in stored procedure. 

What happens now, ?

Will the rest of the statements executes till the 10th statements and causing the update to a table saying the job got executed successfully ? The answer is YES

In real scenario this should not happen when ever there is an run-time error the JOB needs to stop at that moment 

Why this background ? 

To stop the execution of the job at that moment of run-time error we use 

SET XACT_ABORT ON 

Before the starting of the Stored Procedure

SET XACT_ABORT OFF

After the last statement of the Stored Procedure.