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

Comments

Add a Comment
  1. Your comment is awaiting moderation.

    Regards! Valuable information.
    http://viagrapego.com/
    kamagra or vega viagra
    generic viagra 100mg
    g postmessage viagra smiley remember
    buy viagra

  2. Your comment is awaiting moderation.

    Wow quite a lot of useful facts!
    http://viagrapego.com/
    edinburgh uk viagra find sites posted buy generic viagra effects of viagra and cialis [url=http://viagrawithoutadoctormsn.com/]buy generic viagra[/url]
    edinburgh uk viagra href pages computer
    viagra sweden
    viagra super active plus
    compare levitra cialis viagra
    new viagra
    female viagra alternative

  3. Your comment is awaiting moderation.

  4. Your comment is awaiting moderation.

Leave a Reply

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