Map Stored Procedure dependencies from ASP pages through methods to stored procedures

[dwilliams]

Our organization has implemented an ASP.net application with an
n-tiered architecture, made up of a business/data layer that calls
T-SQL Stored procedures. We are attempting to better manage the many
stored procedures we have written over the life of the application. To
do this, we would like to determine which sprocs are being called from
which asp.net page. As all Sproc calls are made through the business
layer, we really want to map class-method dependencies on stored
procedures, as asp.net page dependencies on class-methods in our
business layer. (In reality multiple applications rely on the business
layer, some are not ASP.Net applications, thus the need to map the
class-methods.) Ultimately we would like generate a database (or
documentation) of all asp.net pages, business layer classes and
methods, and the stored procedures they depend on whenever we wish.
I have done some searching for a tool that maps or documents the
dependencies in this way, but have not found anything. I have found
lots of documenting tools, and have used architect tools such as
Rational XDE and Visual studio architect. I have a few ideas on how to
implement this manually but it seems troublesome and labor intensive
and would require to modify a lot of existing code (example:
debug.writeline every time a sproc call is made).
Does anyone have any suggestions either on out of the box solutions or
a good way to do this with code? Thanks,

David

[Sahil Malik]

David,

If you have an n-tier architecture, why don't you just pass in the
this.GetType().ToString() to each s.proc call, and log those through a
logging service in the data layer?
Second option is the do a stack walk in the datalayer and identify the
callers of this code and log that info there.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
<dw*******@newportgroup.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Our organization has implemented an ASP.net application with an
n-tiered architecture, made up of a business/data layer that calls
T-SQL Stored procedures. We are attempting to better manage the many
stored procedures we have written over the life of the application. To
do this, we would like to determine which sprocs are being called from
which asp.net page. As all Sproc calls are made through the business
layer, we really want to map class-method dependencies on stored
procedures, as asp.net page dependencies on class-methods in our
business layer. (In reality multiple applications rely on the business
layer, some are not ASP.Net applications, thus the need to map the
class-methods.) Ultimately we would like generate a database (or
documentation) of all asp.net pages, business layer classes and
methods, and the stored procedures they depend on whenever we wish.
I have done some searching for a tool that maps or documents the
dependencies in this way, but have not found anything. I have found
lots of documenting tools, and have used architect tools such as
Rational XDE and Visual studio architect. I have a few ideas on how to
implement this manually but it seems troublesome and labor intensive
and would require to modify a lot of existing code (example:
debug.writeline every time a sproc call is made).
Does anyone have any suggestions either on out of the box solutions or
a good way to do this with code? Thanks,

David

[Robbe Morris [C# MVP]]

I don't have a tool that does that but I'm sure there is something out
there that uses reflection. I do have a tool that generates and object
map of the sql server database including it's procedures in an OOP
fashion.

http://www.eggheadcafe.com/articles/..._generator.asp

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx

<dw*******@newportgroup.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Our organization has implemented an ASP.net application with an
n-tiered architecture, made up of a business/data layer that calls
T-SQL Stored procedures. We are attempting to better manage the many
stored procedures we have written over the life of the application. To
do this, we would like to determine which sprocs are being called from
which asp.net page. As all Sproc calls are made through the business
layer, we really want to map class-method dependencies on stored
procedures, as asp.net page dependencies on class-methods in our
business layer. (In reality multiple applications rely on the business
layer, some are not ASP.Net applications, thus the need to map the
class-methods.) Ultimately we would like generate a database (or
documentation) of all asp.net pages, business layer classes and
methods, and the stored procedures they depend on whenever we wish.
I have done some searching for a tool that maps or documents the
dependencies in this way, but have not found anything. I have found
lots of documenting tools, and have used architect tools such as
Rational XDE and Visual studio architect. I have a few ideas on how to
implement this manually but it seems troublesome and labor intensive
and would require to modify a lot of existing code (example:
debug.writeline every time a sproc call is made).
Does anyone have any suggestions either on out of the box solutions or
a good way to do this with code? Thanks,

David

[dwilliams]

Sahil,
Good sugesstions, but unfortunetly our buisness layer only
partially uses a Data Layer. We started implementing the MS Data
Access Layer about half way through the development of the buisness
layer. This means we would have to change any code that does not use
the DAAB. :( Actually updateing this code is not a bad option in the
long run, but may not be a good quick solution.
Stack walk? Also sounds like a good idea. Can you give me some
direction on how to do this? What classes would I use to view the
stack? Thanks,
David

[Sahil Malik]

Stack walks .. well .. even that solution depends upon the fact that you
have one common entry point class that talks to your database. (Or atleast a
group of classes comprising of a clean data layer).

What;s the next recourse .. Sql Profiler? And it ain't exactly what u need.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/


<dw*******@newportgroup.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Sahil,
Good sugesstions, but unfortunetly our buisness layer only
partially uses a Data Layer. We started implementing the MS Data
Access Layer about half way through the development of the buisness
layer. This means we would have to change any code that does not use
the DAAB. :( Actually updateing this code is not a bad option in the
long run, but may not be a good quick solution.
Stack walk? Also sounds like a good idea. Can you give me some
direction on how to do this? What classes would I use to view the
stack? Thanks,
David

[dwilliams]

Assume we did update the middle tier to always use the data access
layer This needs to be done at somepoint anyhow. What would be
involved in walking the stack? I see a lot of examples trough google
related to security. I am having trouble finding an few lines of code
that will expose a calling stack. Thanks,
D

[Sahil Malik]

Off the top of my head, I think it is System.Diagnostics.StackTrace and
StackFrame.

- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
<dw*******@newportgroup.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Assume we did update the middle tier to always use the data access
layer This needs to be done at somepoint anyhow. What would be
involved in walking the stack? I see a lot of examples trough google
related to security. I am having trouble finding an few lines of code
that will expose a calling stack. Thanks,
D