Hello all, I'm looking for some advice from someone more experienced than I.
To help provide some context to the issues I'm facing, i'll quickly describe my situation.
I have been a developer at my company for around a year, and have inherited a rather complex, undocumented and half-broken Web Application.
The app was initially created by a contractor, and basic things like source control and test environments were non-existent, and database is full of broken stored procedures which may or may not be used within the app (or in some random external report)
The architecture of the app itself is a C# .Net WebForms application, with a MS SQL database. We use the DevExpress control library for the basic stuff like GridViews etc.
I have been tasked with maintaining and developing new features for this app.
So far, I think I've did alright. I have set up TFS and Dev/Test servers. The code solution now consists of two separate projects: One SSDT project for the database and another standard C# Web Application project. I think this is a marked improvement on how things used to work, I.e. WebSite project, manually copy-pasting files to the server to make changes, performing all database changes in SSMS.
Onto my question... Over the course of trying to get this mess under control, - refactoring everything so it can build and run and be deployed onto different servers etc, one gripe that I have is the loose coupling between the C# code and the database.
For example, much of the data access code will use SqlCommand, where the SqlCommand.CommandText will look something like this:
SqlCommand.CommandText="SELECT ID, Customer, ProductDescription FROM dbo.CustomerProductList"
In this hypothetical example, if one of my columns changed name, the problem would not present itself until the code itself fails to run and throws an exception.
This goes equally for stored procedures - if I add a new mandatory variable to the procedure, If there is a lingering reference to the proc anywhere that I have failed to update, everything would build, run and deploy without issue until it is time for the procedure to be executed.
Is there any way I can close this gap and identify these errors when the program is compiled?
How can I avoid T-SQL queries /Stored Procedure names within strings?
My process at the moment, prior to making any database change, is to view the dependencies in SSMS and then do a literal CTRL + F in Visual Studio for the database object name.
Surely there is a smarter way to address this? It may sound obvious that making database changes has obvious consequences, but there is a great deal of code and virtually zero documentation on how it all interlinks.
Thank you very much for your time.
