6 New features Shipped With Visual Studio 2013

In this post I'm gonna discuss about 6 new features added to Visual Studio 2013. Keep in mind there are lot more than this list. These are few of them that, everyone might need very frequently. Actually few super cool features...!

• Peek definition
• Search facility in option window
• Quick Launch
• Enhanced scroll bar
• Move text with Alt + Arrow
• 64bit edit & continue

Let's see them one by one.

[1] Peek Definition
In older versions of Visual Studio we are allowed to go to a method declaration by pointing cursor to method and pressing F12 shortcut key or click on go to definition by right clicking. That's fine. But definition window open up as a new separate window.

But in VS 2013 it introduced a new feature called "PEEK DEFINITION" Which opens up the definition window inside the same window. So user no need to swap through various windows & loose the current location.(See figure 1)
short cut: Alt + F12

Fig 1: Peek Definition Window

Inside a peek definition window it is possible to go to a another peek definition window. Then that window change accordingly and it also keep track of recently opened peek definitions.

Another advantage of this option is you can edit inside the peek definition window & save changes right away. Visual Studio keep track of recently opened peek definition windows. We can navigate to them by clicking on little dot in upper right corner of the window. See below image.( See figure 2)

Fig 2: List of peek definition windows

[2] Search facility in option window
This lovely IDE has lots of concerns about users. Comparing to other IDEs available, there are lot of things which can be customized according to preferences of the user. You can change these settings by Selecting

Tools => Options

from top tool bar. There are lot of things. Practically sometimes it is hard to find the place to set those option. Don't worry. With VS 2013 this is just a piece of cake... Did you ever notice the search bar in options window. This newly added feature ease the user with finding whatever the option to change. Just type what you need to find. It will list all settings available, according to your search text.

Fig 3:New Search option in Visual Studio 2013

[3] Quick Launch
Ever had a hard time remembering how to open any window ?? Then this is for you! Just type any thing to find (except content in files). This will show you how to get there.

Fig 4: Quick Launch in VS 2013

[4] Enhanced Scroll Bar

With enhanced scroll bar in VS 2013 you'll get many advantages over a ordinary scroll bar. To enable the feature go to  

Tools => options => Text Editor => All Languages => Scroll Bars 

(See Figure 5)

Fig 5: Enabling Enhanced scroll bar

You can switch between map mode & bar mode. By enabling "preview tooltip mode" you can view some amount of code just by moving the mouse on scroll bar.(See Figure 6)

Fig 6: Preview Tooltip mode

With this new enhanced scrollbar you can easily get an overview of where code changes happens, where find text results are , errors, bookmarks etc...  (can identify according to different colors )

(See Figure 7)

Fig 7: Bar mode color specification

[5] Move text with Alt + Arrow

Cut & paste of any text is just fine. But what if you made a mistake while selecting text or pasting it (Imagine that you cut the code, suddenly one of your peers come and you totally forget to paste the code. Then after some time you come and sit & just save the project :D ) 

With VS 2013 you can move an entire code block using 

Alt + Up/Down arrow keys. 

Select the code block you want & press & hold the Alt key & move the text up or down using arrow keys. That's it. This is a really convenient way to move code here & there.

[6] 64 bit edit & continue

How many of you have experienced the problem that in previous versions of VS didn't allow to edit & continue debugging in 64 bit mode. I'm pretty sure, that most of you have. There are lot of situations that we have to modify the code while debugging. It was not very pleasant to stop the current debugging session, do the modification & debug the code again. With VS 2013 it possible to edit & continue in 64 bit mode also.

That's it for this post. We'll see real soon again.... Until then GOOD BYE....! :) 

Last updated on : 22-March-2015

Read more

Find specific text in Database

Hi again...!
In this post I'm going to show you how to find a specific text in  stored procedures, views or table definitions. When database tables, SPs or views increase day by day it is impossible to remember everything in our mind. Few days ago I've come across a situation, where a database table in a production environment getting update incorrectly. I have to find the root cause & after few hours of analyzing we got nothing but a column name, which is updating incorrectly, caused the error.

Then I have to find all the places (all the stored procedures) where that column updating. This is the sql query I've used in that case.

USE Your_DB_Name
GO
SELECT o.name, m.definition, o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%employee%'; /*put your search text */

Use a SELECT * and check all the data available for you. Also you can modify this query to find specific text in tables, views, table types etc. Just change the FROM clause (change "sql_modules" into tables, table_types or views)

If you want to find something like "[your_text]" (text with square brackets) use escape character as following

LIKE '%[[your_text]]%' /*wrap text with aextra pair of square brackets*/ 

OR

LIKE '%\[your_text\]%' ESCAPE '\'

Apart from these queries you can use redgate's SQL Search plugin which is freely available good tool.

Read more

Dynamic SQL in a Stored Procedure - part 1

Hi, welcome again... :)
I'll cover this topic under two parts

• Dynamic SQL in a Stored Procedure - part I
• Dynamic SQL in a Stored Procedure - part II

With part I, I'll explain the basic concepts behind the dynamic sql queries and in second post I'll go more deep covering performance, security etc... Sometimes we face situation where, hard coded sql queries are not a solution. In case such a situation we need to create dynamic sql queries on the fly & execute them.

So what is a Dynamic SQL query?
With Dynamic SQL you can write one or more sql statement(s) & put it inside a string variable. We can write dynamic sql queries in both application side and database server side. Handling application side is pretty much easy. But when it comes to write dynamic queries in a stored procedure, there are many concerns.


Why use Dynamic SQL Query?
As developers we might face situations, where we have to deal with database in a different manner, for same task. For a example consider you have a simple application (See below image), that enable user to find a student by providing his ID or Name or both of them. Now think, how would you query the database in order to find out student with given parameters (think about what would be the where clause in your query). In such scenarios Dynamic Query comes very handy.

Methods of Implementing
There are two ways of executing dynamic sql queries.

1.  SP_EXECUTESQL
2.  EXECUTE()

Let's see how to write dynamic sql query for above scenario. Here I am using first method which has many advantages over execute method (In a stored procedure). If I have to made a choice between these two methods I'll definetly go for first one(in most cases :) ). Advantages of sp_executesql over EXECUTE() explained in part II.

1. Using sp_executesql system stored procedure

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_GetStudent]
 
 /*PARAMETERS PASSED FROM APPLICATION SIDE*/
 @ID INT,
 @NAME NVARCHAR(50) 

AS
BEGIN
 
 SET NOCOUNT ON;

 DECLARE @SQL_QUERY NVARCHAR(MAX) ;/* VARIABLE TO HOLD SQL QUERY STRING*/
 DECLARE @WHERE_CLAUSE NVARCHAR(MAX);/*VARIABLE TO HOLD WHERE PART OF THE QUERY*/
 DECLARE @PARAMETER_DEFINITION NVARCHAR(MAX); /* PARAMETER TO HOLD, DEFINITIONS OF USED PARAMETERS INSIDE THE QUERY STRING */
 
 SET @PARAMETER_DEFINITION = '@ID INT,@NAME NVARCHAR(50)' 
 SET @WHERE_CLAUSE = ' '
 SET @SQL_QUERY = 'SELECT * FROM TestDB.dbo.tblStudent WHERE (1=1)'

 IF @ID IS NOT NULL
 BEGIN 
  SET @WHERE_CLAUSE = @WHERE_CLAUSE + ' AND ID = @ID'
 END

 IF @NAME IS NOT NULL
 BEGIN
  SET @WHERE_CLAUSE = @WHERE_CLAUSE + ' AND Name = @NAME'
 END

 SET @SQL_QUERY =  @SQL_QUERY + @WHERE_CLAUSE
 PRINT @SQL_QUERY
 EXEC SP_EXECUTESQL @SQL_QUERY, @PARAMETER_DEFINITION, @ID, @NAME
END

Query string will be assigned to @SQL_QUERY variable. That must be declared as a nvarchar variable. Based on the values passed to parameters, where clause get created. Finally sp_executesql
should be execute by passing query string, parameter definition and list of parameters used in query string. @PARAMETER_DEFINITION variable used to hold the definition of parameters. Make sure you include all parameters use in sql string, into @PARAMETER_DEFINITION. This variable must be type of nvarchar too.

Both EXECUTE & EXEC keywords are equivalent here. You can use either one of them. I usually go with the short version. I've used PRINT command here just for check the query. You can omit that line if you do not want it.

When you'll need Dynamic SQL Queries
Dynamic queries very helpful when you need to use following items in variables in a query,(assume following items in a query get changed time to time based on a given scenario. So you can't hard code them).

• Server name
• Table name 
• Column name

This is because sql server doesn't allow user to replace above parts with a variable. Also if you have different kind of filtering scenario like explained above it will be a good choice to go for dynamic sql queries.

Hope now you have a good understanding of basics of dynamic sql queries. See you again with the Part II of this post...

Read more

How To Write a SQL Cursor

All programming languages have it's own way of handling iteration operations. In SQL server one way to do this is using a 'CURSOR'.

Although there are some other sql iterative solutions like while loop, here we focus only on T-sql cursors.

When writing sql queries we often need to loop through different types of records sets, in order to achieve certain tasks. It's very common.

Personally I always forget cursor syntax & when I need to use it what I'm doing is, just get it copied from somewhere else. :D Let's look into cursors in detail.

How it operates

Cursor is a database object & it manipulates given record set row by row basis. Which means it take first record, perform what it needs to be done & move to next record and do whatever the thing mentioned. It goes like this until the end of the record set. 

In this example I'm trying to read some data from few tables & insert them into a temp table called #TEMP_TABLE. First of all go to following link to download complete Sql query for creating required tables (Test data included). I've used MSSQL Server 2012.

(3 KB)



After download, open this file using SQL Server Management Studio & execute it. Then you have test data to see how cursor works ! Following is a code snippet of a typical sql cursor example.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

USE TestDB_CURSOR CREATE TABLE #TEMP_TABLE(CUSTOMER VARCHAR(50), ITEM VARCHAR(50)) DECLARE @Cusname varchar(50) DECLARE @Item varchar(50) DECLARE TEST_CURSOR CURSOR FOR SELECT CUS.CusName, ITEM.ItemName FROM tblOrder AS ORD INNER JOIN tblCustomer AS CUS ON ORD.CustomerID = CUS.CustomerID INNER JOIN tblItem AS ITEM ON ORD.ItemID = ITEM.ItemID OPEN TEST_CURSOR FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP_TABLE VALUES(@Cusname, @Item) FETCH NEXT FROM TEST_CURSOR INTO @Cusname, @Item END CLOSE TEST_CURSOR DEALLOCATE TEST_CURSOR SELECT * FROM #TEMP_TABLE DROP TABLE #TEMP_TABLE

Line no. 5,6 => Declaration of cursor variables.

Line no. 8 => Declaration of cursor [Test_Cursor is the name of the cursor]. There are more cursor options like FORWARD_ONLY, FAST_FORWARD etc... But it's not discuss in here. You can view more details form MSDN

Next, a select statement to retrieve values. Here we are selecting two values. This is the result set we are going to loop.

Then start the cursor
In line no 15 => assign values which are got from select statement into the cursor variables.
Important : Selected columns types and relevant variables types must match each other.

Next thing is While loop. @@FETCH_STATUS = 0 means fetching was successful. If something goes wrong and fetching did not happened correctly it will get a value other than 0 (Usually this will be -1 or -2). If @@FETCH_STATUS = 0 iteration will continue and will stop when it become -1 or -2.

Line no. 18 => Values assigned to cursor variables insert into temporary table created above. This is optional & should change according to your scenario.

Then move onto next raw of the result set and assign cursor variables with relevant values. This iterative process will continue until end of the result set row count.

When everything happened correctly we need to close the cursor & release the resources use by cursor itself. That's it for cursor & you may execute the sql query to see the final output.

Performance Concerns

In generally it is considered as, looping in T-SQL is much slower than the looping structures of other programming languages'  such as .Net, VB6 or Java etc.. But it may vary depend on the scenario that someone is working. It's totally up to you to decide which one to use :)

This post regarding sql cursors is the very first technical post written by me :) Hope you liked it. Got something to say?? Please, do comment below...!
See you soon with a another post.!!!

Read more

Welcome Aboard...!

I warmly welcome all of you all to Coding Pulse...!
This can be introduced as a resource for .Net coding, SQL & also a place for Tech geeks to share what they know.

In my point of view, if you want to memorize something, writing it down is your BEST FRIEND !!!
I know it from my own experience. So I've started to write things (even very simple things), that I need frequently in my career life as a Software Engineer. Writing things & keep them online is even better. Isn't it?

Finally, I believe the time  spend writing will be a helping hand to at least one person out there.Come join with us & spread the word... :D

Your Feedback is highly appreciated....

Read more