Search for Stored Procedures in source code

During the development process of an ASP.NET web application, the number of
stored procedures used can grow alarmingly. More often than not, I forget to
document
which stored procedures are being used and which are redundant. So, I decided
to build a little utility that searched through my source code to find all
instances of stored procedures being used.
My goal was to have a web page that listed the procedures being used on each
page. In IE6, I could then right-click on the table and export the list to
Excel. Useful for future documentation.

First of all we start by looping through directories in our root folder:

		private string FileTypes="*.cs";
		private void DirSearch(string Dir) 
		{
			try	
			{
				foreach (string f in Directory.GetFiles(Dir, FileTypes)) 
				{
					ReadFile(f);
				}
				foreach (string d in Directory.GetDirectories(Dir)) 
				{
					DirSearch(d);
				}
			}
			catch (Exception e)
			{
				Response.Write(e.Message);
			}
		}

Like many other, all my stored procedures begin with “sp”, so we can read
each file and look for all instances of “sp*” and store them in a datatable.

		private string SearchExpression="\"(sp[^\"]+)\"";
		private void ReadFile(string fn)
		{
			StreamReader s = new StreamReader(fn);
			string read = s.ReadToEnd();
			s.Close();
			Regex r = new Regex(SearchExpression);
			MatchCollection mc=r.Matches(read);
			foreach (Match m in mc) 
			{    
				DataRow dr=dt.NewRow();
				dr["SP"]=m.Groups[1];
				dr["Page"]=fn.Substring(Folder.Length+1);
				dt.Rows.Add(dr);
			}
		}

Next, we can bind the Datatable to a Datagrid:

		private string Folder=@"c:\mywebsite";
		private void BindData()
		{
			dt=new DataTable();
			dt.Columns.Add("SP");
			dt.Columns.Add("Page");
			DirSearch(Folder);
			DataView dv=dt.DefaultView;
			dv.Sort=SortColumn;
			DataGrid1.DataSource=dv;
			DataGrid1.DataBind();
		}

And then bring it all together when the pages loads:

		private void Page_Load(Object src,EventArgs e) 
		{
			if (!IsPostBack)
			{
				SortColumn="SP";
				BindData();
			}
		}
Updated: March 5, 2015 — 11:06 pm

Leave a Reply

Your email address will not be published. Required fields are marked *

Charon © 2015 Privacy Policy | Terms & Conditions Frontier Theme