Typing Tips & Tricks

Typing Tricks

Below are the few useful typing tricks that help you to speed up your work.

1) Delete the word  

As you all already know by pressing the Backspace will delete single character. However, pressing the CTRL + Backspace will delete the whole word behind the cursor. It is very helpful  particularly Microsoft programs like Word or Outlook.

2) Paste plain text that you copied  

Usually when you copy text from any website or any other source, it will usually copy formatting(means font size and style) that comes with it. To paste this as plain text, press CTRL + ALT + V instead of the standard CTRL + V, and the system will show a 'paste special' dialog box. Select option and system will paste unformatted text.

3) Move Cursor to beginning of the next or previous word  

  

To speed up your work, move the cursor around with keyboard shortcuts. To move it to the beginning of the previous word, use CTRL + Left Arrow. To move it to the beginning of the next word, use CTRL + Right Arrow.



This is all about article. Hope you like it.

Change Rows to Columns - Pivot in SQL Server

PIVOT

In this article, we will discuss PIVOT keyword in Sql Server. Pivot is responsible to  convert Rows to Columns in Sql Server.

Let's take the example.

Step 1: Declare SQL table and add some dummy data

DECLARE @ScoreTable TABLE
(
MatchType VARCHAR(50),
Year INT,
Score  INT
)

INSERT INTO @ScoreTable VALUES('OneDay',2012,1000)
INSERT INTO @ScoreTable VALUES('Test',2012,2200)
INSERT INTO @ScoreTable VALUES('OneDay',2013,800)
INSERT INTO @ScoreTable VALUES('Test',2013,370)
INSERT INTO @ScoreTable VALUES('Test',2014,1100)


Step 2: Run the Select query and see the default data

This is not mandatory step. However, just check you dummy data.

SELECT * FROM @ScoreTable

In above query, you can see we have 3 columns. MatchType, Year and Score.
We will change Year Rows data in Columns.


Step 3: Write query to covert rows to columns


SELECT * FROM @ScoreTable
PIVOT(SUM(Score)
FOR Year IN ([2012], [2013],[2014])) AS PVTTable




In above snapshot, you can see query changed year to columns and score under each column.


This is all about the article. I hope you like it.

Parent and Child Relationship in the Same table - SQL Server

Parent and Child Relationship

In this article, I'll explain the parent and child relationship in the same table.

Let's take the example by declaring the SQL table and insert some records.

Step 1: Declare the table and add some dummy data

DECLARE @table TABLE
(
Id INT,
Name varchar(50),
ManagerId INT

)
INSERT INTO @table VALUES(1,'Sunny',2)
INSERT INTO @table VALUES(2,'Nitin',4)
INSERT INTO @table VALUES(3,'Amit',5)
INSERT INTO @table VALUES(4,'Sumit',1)
INSERT INTO @table VALUES(5,'Jay',null)

SELECT * FROM @table

When you run the query and you will find below result

In the above snapshot, User belongs to the following manager. We need output as below.

Sunny - Nitin
Nitin - Sumit
Amit - Jay
Sumit - Sunny
Jay - No Manager Assigned


Step 2: Write a query to achieve the desired result.

1. With Help of Join

SELECT a.Name,b.Name as ManagerName
FROM   @table a left join @table b on
 b.Id = a.ManagerId

2. With Help of Sub Query

SELECT a.Name, (SELECT b.Name
                       FROM @table b
                       WHERE b.Id=a.ManagerId)as ManagerName
FROM   @table a

Step 3: Output would be the same for the above 2 queries.




This is all about the article. I hope you like it.

New in Visual Studio 2015

Features of Visual Studio 2015

There are many features in Visual Studio 2015. In this article we will discuss top 5 features of Visual studio 2015.

1. Debug Lambda Expressions

The best feature in VS2015 that developer is waiting for so long. Earlier we were not able to debug Lambda expression. Now you can debug the code and see the result in Immediate window as shown in below snapshot.




2. Yellow Light Bulb


This feature is similar to Resharper. It will give you suggestions. Here in below shapshot when you click on Yellow bulb, it will show some suggestion related to missing reference.

3. IntelliSense for Angular


Visual studio will display the Intellisense for Angular. Just write ng in div tag. It will show the list of ng tags as shown in screenshot below.

4. IntelliSense for Bower and NPM

In Asp.Net 5 project, you will find Bower, Gulp and NPM in new template.
Microsoft provide Intellisense for both Bower and NPM.

Bower:  Bower is for client side packaging for e.g. Jquery and Angular.
NPM: NPM is for developer tools for e.g. Grunt, Gulp

5. References Counts

On the top of each Method, Property and Class you will find reference count. This feature is available in Visual Studio 2013 updated version. However, you will find here in Visual Studio 2015 as well.



When you click on 4 references text, you will see list of references as shown in screenshot below.

This is all about the article. I hope you like it.

SQL Server - Caluclate column sum on each Row

Running Total in SQL Server

In this article I'll explain how we can get running total on each row.

Let's take the example.

Step 1: Declare Sql Table

DECLARE @TempTbl TABLE  

(

  Id INT IDENTITY(1,1),

  Score INT

)

Step 2: Insert dummy records in the table

INSERT INTO @TempTbl(Score) VALUES(10)

INSERT INTO @TempTbl(Score) VALUES(20)

INSERT INTO @TempTbl(Score) VALUES(30)

INSERT INTO @TempTbl(Score) VALUES(40)

INSERT INTO @TempTbl(Score) VALUES(50)

Step 3: See the records in table

This is not mandatory step. However, check everything is fine.

SELECT * FROM @TempTbl



Check you table has above records.

Step 4: Write the query to get running total

There are many ways to get running total. I'll explain 3 ways.

1. With help of Sub query

     SELECT Id, Score,

(

   SELECT SUM(b.Score)FROM @TempTbl b 

   WHERE b.Id <= a.Id

 ) as Total

     FROM   @TempTbl a

2. With help of Join
     

    SELECT a.Id,a.Score,SUM(b.Score) as Total

    FROM   @TempTbl a, @TempTbl b

   WHERE b.Id <= a.Id

    GROUP BY  a.Id,a.Score

3. With help of Over (Only for SQLServer 2012 and above)

     SELECT a.Id, a.Score, SUM(a.Score) OVER (ORDER BY a.Id) as Total

     FROM   @TempTbl a

All 3 options will give the same output as shown in below screenshot. See the Total column, it is showing running total of Score column.



That is all about this article. I hope you like it.

Window Service in Asp.Net C#

Window service

Window service is the application that run in background and perform various tasks.

How to create window service?

Its very easy to create window service. Follow the steps mentioned below

Step 1

In visual studio 2013,  File --> New Project --> Visual C# --> Windows Desktop --> Window Service.



In Visual Studio 2015,  File --> New Project --> Visual C# --> Windows  --> Classic Desktop --> Window Service.

Give the appropriate name to window service. I've given MyFirstWindowService and press ok.

Step 2

From Solution explorer select Service1.cs file and rename file to your appropriate name of the service. Let's give the name MyService.

Step 3

Right click on MyService.cs designer file and click properties as per below screenshot and changed the ServiceName.

Step 4

Right click on MyService.cs designer file again and click AddInstaller option. Designer will add Program.cs file and ProjectInstaller with 2 controls

  1. serviceProcessInstaller1
  2, ServiceInstaller1

Step 5

Right click on serviceProcessInstaller1 and select properties and change Account to LocalSytem

Step 6

Right click on ServiceInstaller1 and change StartType property to Automatic and  DisplayName property.

We did all the setting now its time to write some code.

Step 7

Right click on MyService.cs file and select View Code.

You can see we have 2 methods in the file. OnStart, OnStop we will write our all code in these 2 methods. In the article service will write text in notepad file.
When service will start, It will write something in notepad. After every minute service will append date time in notepad file.

1. Initialize the timer in the code above constructor.

    Timer timer = new Timer();

        public MyService()

        {

            InitializeComponent();

        }

2. On Start Method write below lines

 protected override void OnStart(string[] args)
        {
            WriteFile("Service Started " + DateTime.Now);
            timer.Elapsed += new ElapsedEventHandler(AddNewEntry);
            timer.Interval = 60000;
            timer.Enabled = true;
        }

AddNewEntry is the method that will run after every minute. because we have set the timer interval to 1 minuete.

 private void AddNewEntry(object source, ElapsedEventArgs e)
        {
            WriteFile("New entry at " + DateTime.Now);
        }

3. OnStop Method

 protected override void OnStop()
        {
            timer.Enabled = false;
            WriteFile("Service Stopped" + DateTime.Now);
        }

4. WriteFile private method

WriteFile is the private method that will write file after every minute.

 private void WriteFile(string content)
        {
            FileStream fs = new FileStream(@"E:\SunnySetia\MyService.txt", FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);
            sw.BaseStream.Seek(0, SeekOrigin.End);
            sw.WriteLine(content);
            sw.Flush();
            sw.Close();
        }

Code will look like below screenshot

How to install window Service?

Step 1: Open Visual Studio command prompt in administrator mode.

Step 2: Go to location where project in placed. In my example my project is located in E:\Sunny\WindowServiceVS2015\MyFirstWindowService\MyFirstWindowService 

Step 3: Install the exe file located in bin\debug folder.
Step 4: For installion, you need to use installutil command as per below screenshot.

Step 5: Press enter to install the window service.


If you followed all the steps then you will get above message.

If you are getting Exception while installing the exe file then use Release folder rather than Debug folder.

E:\Sunny\WindowServiceVS2015\MyFirstWindowService\MyFirstWindowService\bin\Release.

Step 6: Go to  Start --> Control Panel --> Open Control Panel --> Select Administrative Tools --> Computer Management --> Services and Applications --> Services --> Open services

or In run command write services.msc

Now you can you service MyService in Services list. Look at the screenshot below.




Step 7: Right click on MyService and Press Start. After start
Step 8: Go to notepad file location and you can see window service writing in notepad file.



Step 9: For stopping the window service. Right click on service and press stop.
Once you stop you can see updated notepad file.

 Step: 10 For uninstalling, you need to write installutil servicename.exe -u  as below screenshot




That is all about window service. I hope you like it.