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   

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.