November 10, 2015

SQL SERVER 2016 FEATURES

SQL Server 2016 introduces the new statement because it is very confusing when you need to check an object either it is present or not.

Previously when we need to check the Store procedure either it is exists or not we use following long lengthy syntax:


IF OBJECTPROPERTY(object_id('dbo.sp_GenerateReport'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[sp_GenerateReport]
GO

Now in the latest version of SQL Server, they reduce the long lengthy statements into one line for Store procedure, function and tables. They named the statement as 'DIE' => DROP IF EXISTS


DROP FUNCTION IF EXISTS fn_CalculateSum
DROP PROCEDURE IF EXISTS sp_GenerateReport
DROP TABLE IF EXISTS Subscriber

See that!!! this is so simple to remember and use in your daily routine queries.

Cheers

October 29, 2015

BENEFITS OF AYAT UL KURSI

1. Recite while Leaving Your House, And 70,000 Angels Will Protect You from All sides.

2. Recite On Entering Your Home, And Poverty Won't Enter Your Home.

3. Recite After Wuzu, And It Raises You 70 Times In Allah's Rank.

4. Recite Before Sleeping And 1 Angel Will Protect You The Whole Night.

5. Recite After Fard Salah And The Only Thing That Is Separating You From Paradise Is Death.

If you pass this on Its Sadqa-e-Jaria (meaning you on getting rewarded for it, every time someone one acts upon it even when you have died, up until the day of judgement).

October 26, 2015

SOME GOLDEN WORDS OF PROPHET MUHAMMAD (صلى الله عليه وآله وسلم)

1. RESPECT 3 PEOPLE
    a. TEACHER
    b. PARENTS
    c. ELDERLY

2. HAVE 3 THINGS IN YOU
    a. HONESTY
    b. FAITH
    c. GOOD DEEDS

3. FREE YOURSELF FROM 3 THINGS
    a. ARROGANCE (THINKING OTHERS AS BELOW YOU AND DENYING THE TRUTH)
    b. CHEATING
    c. DEBT

4. HAVE CONTROL OVER 3 THINGS
    a. TONGUE
    b. ANGER
    c. SOUL (SELF DESIRES)

5. SAVE YOURSELF FROM 3 THINGS
    a. BAD DEEDS
    b. BACKBITING
    c. JEALOUSY

6. OBTAIN 3 THINGS
    a. KNOWLEDGE
    b. MANNERS
    c. PIETY

7. KEEP 3 THINGS PURE
    a. BODY
    b. CLOTHES
    c. THOUGHTS

8. REMEMBER 3 THINGS
    a. DEATH
    b. FAVOR (OTHERS HELPING)
    c. ADVICE

August 28, 2015

Custom Transpose In SQL Server

When we are playing with the database queries in order to create store procedure, triggers or job...we sometimes need to pass the comma separated string of integer values into the IN clause of SQL query.

Took the example from School where case is that we need to get the list of different Students based on StudentID provided in the procedure as a comma separated string.

DECLARE @StudentIdList AS VARCHAR(8000) = '1,10,15,18,22,45,99'

So when we try to get the Student List based on this parameter using the IN clause as:

SELECT StudentID, StudentName, RollNumber, Age
FROM   Student
WHERE  StudentID IN (@StudentIdList)

What happend!! when we try to run the above query. It will return the error on the IN parameter and mesage is mentioning that Cannot able to convert varchar to bigint....(something like that)

So how do we solve the problem???

Here comes the powerful transpose concept. Here we will create a function which will convert the comma separated string into the table structure where each value will be place into the rows.

CREATE FUNCTION [dbo].[ParamsToTable]
(
    @ParameterString VARCHAR(8000)
)
RETURNS @TblParam TABLE(IdString VARCHAR(10))
AS
BEGIN
    DECLARE @SingleValue VARCHAR(10)

    WHILE LEN(@ParameterString) > 0
    BEGIN
        SET @SingleValue = LEFT(@ParameterString, ISNULL(NULLIF(CHARINDEX(',', @ParameterString) -1, -1), LEN(@ParameterString)))
        SET @ParameterString = SUBSTRING(@ParameterString, ISNULL(NULLIF(CHARINDEX(',', @ParameterString), 0), LEN(@ParameterString)) + 1, LEN(@ParameterString))
     
        INSERT INTO @TblParam VALUES (@SingleValue)
    END
RETURN
END

Use the above created function in your sql query in order to generate the desire result as:

SELECT StudentID, StudentName, RollNumber, Age
FROM   Student
WHERE  StudentID IN (SELECT * FROM ParamsToTable(@StudentIdList))

The reason to put the query block into the function is that by doing this we can use it on several places where we need such type of activity to perform.

Let me know if you have any query over this implementation.

Cheers

August 26, 2015

Recursion In LINQ

A method which called itself is called "Recursive". These methods are extensively used in daily programming practices. We use it to solve the complex problems or puzzles we face in code. Using recursion add needless complication in other programs.

Before jump into the LINQ world, let me tell you the method definition and implementation. The method should contain the reference variable parameter. It checks a condition near the top of its method body, as many recursive algorithms do.

So if you see the same method signature repeated many times in the call stack, you have a recursive method.

Back to topic, so how the we can acheive the recursion in LINQ query. It is good here to follow the example and write a LINQ query according to it.

Suppose I have list Candy. And each candy have different flavors while some flavor may extend to their child flavors. We need to calculate the 'Total Count' of candy flavors. How do we acheive this?

Diagram of this case is like this tree:


Candy --> Flavor1
Candy --> Flavor2 --> SubFlavor1 --> One, Two
Candy --> Flavor2 --> 
Candy --> Flavor2 --> SubFlavor1 --> Three --> Four, Six

SOLUTION:

We will use 'SelectMany' in order to calculate the 'Total Flavors' count. When we use 'SelectMany', it collapses many elements into a single collection. The code logic will be:


var flavorCount = candyList.SelectMany(sm => sm.Flavors).Count(); // C#

Dim flavorCount = candyList.SelectMany(Function(sm) sm.Flavors).Count() ' VB.NET

You see that!!! this is very simple in LINQ in order to count the 'Total Flavor'. Here I used 'SelectMany' in order to get the count. 

Let me know if have any queries left.

Cheers

July 30, 2015

Updates In Visual Studio 2015

In the month of July, 2015... a major release annoucement being made for VS 2015 and .Net Framework 4.6. This post is intent to share with you the great updates which are available in this version.

1. ASP.NET Tooling Improvements:
    a. JSON Editor
    b. HTML Editor Updates
    c. JavaScript Editor Improvements
    d. ReactJS Editor Support
    e. Support for JavaScript package managers like Grunt, Gulp and Task Runners

2. ASP.NET 4.6 Runtime Improvements
    a. HTTP/2 Support
    b. Support for the .Net Compiler Platform
    c. Async Model Binding For Web Forms

3. ASP.NET 5

4. Entity Framework
    a. Entity Framework 6.x
    b. Entity Framework 7

This the summary of updates came in this release. In order to read these updates in detail please visit the blog of Scott Guthrie:

June 23, 2015

ERROR: MSDTC on server 'SomeServerName' is unavailable.

This exception is rise when you use 'TransactionScope' in your LINQ to SQL or LINQ to Entity logic where updating the database with multiple tables at once. Exception is belongs to 'System.Data.SqlClient.SqlException'. Exception is raised because of 'Distributed Transaction Coordinator' which not enabled or running on your server/machine.

SOLUTION:

We have three different types of methods available in order to fix the exception:

1. Check the firewall settings:

In Run, type firewall.cpl.
Click Exceptions Tab.
Click Add Program.
Browse for Msdtc.exe (normally found under C:\Windows\System32).
Click Add Port.
In the Add A Port window, give a name in the Name field.
Give 135 in the Port Number Field.
Select TCP.
Click OK.

2. Start via Services

Method A:
   
You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES
Find the service called 'Distributed Transaction Coordinator'.
RIGHT CLICK (on it and select) > Start.

Method B:

Type services.msc in the run command box
Select “Services” manager; Hit Enter
Select the service “Distributed Transaction Coordinator
Right on the service and choose “Start

3. Use this for windows Server 2008 r2

Click Start, click Run, type dcomcnfg and then click OK to open Component Services.
In the console tree, click to expand Component Services, click to expand Computers, click to expand My Computer, click to expand Distributed Transaction Coordinator and then click Local DTC.
Right click Local DTC and click Properties to display the Local DTC Properties dialog box.
Click the Security tab.
Check mark "Network DTC Access" checkbox.
Finally check mark "Allow Inbound" and "Allow Outbound" checkboxes.
Click Apply, OK.


VERIFICATION:

We can check that either the 'Distributed Transaction Coordinator' is enabled or not. In LINQ to Entity, we use 'TransactionScope.Complete();' line at the end of transaction. Run the application using breakpoint and you can check that either this line is parsed successfully or raise error/exception. 

If it is parsed then it means 'Distributed Transaction Coordinator' is enabled successfully and if it is not try the other method mentioned above in the post.

Let me know if you face any exception after implementing the suggested methods.

Cheers