Using Reflection to Dynamically Determine Stored Procedure Name

[Tod Birdsall]

Hi,

My boss has asked me to create a tool that can give us a list of
stored procedure names that are being called from each of our app's
web pages. This is an ASP.NET application that is rather large and
about 5 years old. I am not here to debate the logic of the request.

The structure of most the application is pretty standardized: We
created a business/data layer that contains methods that have the
names of the stored procedures to call as hard coded strings. The
stored procedures all begin with "USP_", so should be pretty easy to
ferret out.

My initial thought was to use reflection to start at the ASP.NET
assembly, then work my way down to the business/data layer assembly.
So the end report would tell you each namespace.class.method that
leads up to the proc and look something like:

-CompanyApp.Defaultl.aspx.cs.PageLoad()
--BizLayer.GetCustomerData()
---DataLayer.GetCustome()
----"USP_Customer_Get"


Has anyone tried to do anything like this before? Does anyone have any
suggestions on where to begin or can you point me to a tutorial on
something similar?

Thanks for any help you can provide.

Tod Birdsall
http://tod1d.net

 

[Jeroen Mostert]

My boss has asked me to create a tool that can give us a list of
stored procedure names that are being called from each of our app's
web pages. This is an ASP.NET application that is rather large and
about 5 years old. I am not here to debate the logic of the request.

It's actually a very reasonable request. I've had to maintain stuff like
this and not knowing exactly how it tickles the database causes major
maintenance headaches. "We don't know exactly how it interfaces with the
database, so better not change anything" is something you don't want to hear.

The structure of most the application is pretty standardized: We
created a business/data layer that contains methods that have the
names of the stored procedures to call as hard coded strings. The
stored procedures all begin with "USP_", so should be pretty easy to
ferret out.

My initial thought was to use reflection to start at the ASP.NET
assembly, then work my way down to the business/data layer assembly.
So the end report would tell you each namespace.class.method that
leads up to the proc and look something like:

-CompanyApp.Defaultl.aspx.cs.PageLoad()
--BizLayer.GetCustomerData()
---DataLayer.GetCustome()
----"USP_Customer_Get"

Has anyone tried to do anything like this before? Does anyone have any
suggestions on where to begin or can you point me to a tutorial on
something similar?

Reflection won't actually do you any good because it doesn't give you access
to method code. You can use ildasm and grovel through its output, or you can
simply use Lutz Roeder's Reflector (http://www.aisto.com/roeder/dotnet/) and
use its Search function (F3 -> Ctrl+S) to find all strings starting with
USP_. Ildasm would be more suitable if you need to do this programmatically,
but Reflector's certainly an order of magnitude easier.

 

[Tom Dacon]

If you have access to the source code, there's no reason that you couldn't
just do a file system search. If that's true, I don't see the need to get
more elaborate. I've done the same thing for a body of source code that
amounts to several hundred thousands of lines of code, and it was no big
deal.

Tom Dacon
Dacon Software Consulting

 

[Tod Birdsall]

Thank you both for your reply. It is good to know that Reflection
won't help me. Reflector is very useful, but I don't think it will
work for the problem I am trying to solve.

The problem is that my boss, who is a DBA and not a programmer, wants
to know what stored procedures are being called by each aspx page. If
no developer is available to troubleshoot an issue with an aspx page,
he would like to know what procs the page is calling so he might be
able to troubleshoot the problem from the database side. I have access
to the source code and can easily search through it to get a list of
procs that are used by each of the three assemblies that are being
used in this application. The data layer is in a separate assembly
from the website and, to make things more interesting, the website
itself is not in single assembly.

I think I will need to look at this problem from another angle, or
possibly go the ildasm route, if possible.

Tod Birdsall
http://tod1d.net

 

[Jeroen Mostert]

The problem is that my boss, who is a DBA and not a programmer, wants
to know what stored procedures are being called by each aspx page. If
no developer is available to troubleshoot an issue with an aspx page,
he would like to know what procs the page is calling so he might be
able to troubleshoot the problem from the database side.

If he wants it for troubleshooting, he can just run SQL Server Profiler to
see what's *actually* being executed at the DB side at the time trouble
happens. That's probably more helpful than a theoretical list of what pages
*might* be calling what (which could be highly variable depending on input),
especially if he can't actually change the source.

It's a bit silly, really. If I had a DBA like that I'd say "I'll call
whatever stored procedure is in the DB, whenever I want, in whatever way I
want, permissions willing". Since the DBA can't understand or fix my code,
he has no business knowing it in further detail, and indeed the whole point
of setting up the DB this way is that he doesn't need to. If he wants to fix
problems at the DB side, he can; he's got all the tools he needs for that.
But there's no benefit to him knowing what ASPX page does what, if only for
the reason that such a list is bound to become outdated and worse than useless.

Print this and show it to your boss for interesting conversation!

I have access to the source code

Problem solved? FINDSTR on the lot and the file name should match the page.