Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Goto definition Add-In but for SQL Stored Procedures !!

3.61/5 (9 votes)
3 Feb 2007CPOL 1   156  
This add-in lets you navigate to the specified SQL stored procedure if you refer to it in your code,
Sample image

Introduction

Using this Add in, you can find the SQL stored procedure and open it within the VS IDE. I found it very useful.

Step 1: Install the Add-in and activate it in your VS.NET from tools->addin manager.

Step 2: You must have a Database project for your solution which is the most popular case.

Step 3: Copy file (XMLFile.xml) to your C:\ drive and put in it the path for your Database application.

<Path>C:\FileNet_VSS\Passports\Development\FNDevFramework\FNSysDB</Path> 

Just highlight the name of the SP in your Middle-Tier and click SPViewer icon.

The Code

Every SP begins with the Create statement in the *.sql file:

SQL
CREATE Procedure dbo.SP_NationalityCodes_Get

Or without the dbo:

SQL
CREATE Procedure SP_NationalityCodes_Get

This is the main idea of the search.

This is the first method:

C#
applicationObject.Find.Target = vsFindTarget.vsFindTargetSolution;
applicationObject.Find.FindWhat=string.Format("CREATE Procedure dbo.{0}",sp); 
applicationObject.Find.PatternSyntax=vsFindPatternSyntax.vsFindPatternSyntaxLiteral;
applicationObject.Find.Action = vsFindAction.vsFindActionFind;
applicationObject.Find.MatchCase=false;
applicationObject.Find.MatchWholeWord=true;
applicationObject.Find.SearchPath=path;
applicationObject.Find.ResultsLocation = 
vsFindResultsLocation.vsFindResults1;
applicationObject.Find.FilesOfType="*.sql";
applicationObject.Find.SearchSubfolders=true;
EnvDTE.vsFindResult res = applicationObject.Find.Execute();

If there is no result, an exception will be thrown. This is the second method:

C#
catch( Exception s)
{
applicationObject.Find.Target = vsFindTarget.vsFindTargetSolution;
applicationObject.Find.FindWhat=string.Format("CREATE Procedure {0}",sp); 
applicationObject.Find.PatternSyntax=vsFindPatternSyntax.vsFindPatternSyntaxLiteral;
applicationObject.Find.Action = vsFindAction.vsFindActionFind;
applicationObject.Find.MatchCase=false;
applicationObject.Find.MatchWholeWord=true;
applicationObject.Find.SearchPath=path;
applicationObject.Find.ResultsLocation = 
vsFindResultsLocation.vsFindResults1;
applicationObject.Find.FilesOfType="*.sql";
applicationObject.Find.SearchSubfolders=true;
applicationObject.Find.Execute();
}

Special thanks to my brother mohammed barqawi.

Hope it will be helpful for you.

History

  • 3rd February, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)