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(); } }