How can I cause a build error due to mismatch between sproc and calling code?

Question

I have a .NET class (for discussion, ClassA) that calls a SQL Server stored procedure (for discussion, fooSproc), processing the results with a SqlDataReader. The rows are processed, and the columns are referenced using the name of the column in the returned result set. For example, where dr is the SqlDataReader, something like dr["column_foo"].

Now, say, for example, that a someone checks in a change to ClassA such that it is looking for column_bar in the result set returned by fooSproc. However, that person neglected to check in a change to fooSproc such that it would actually return column_bar.

The problem: the code compiles, but fails at runtime due to the missing column.

Is there a way to make this scenario cause a build error to be generated? Naturally a build verification/acceptance test makes sense here, but it's cheaper to catch it earlier. Please pretend that even a superficial code review is out of scope.

Answer 1

I'm not really aware of any way to do that using just .Net and SQL, as your code would have to be connected to your db at compile time.

That is, when you build your code, it would have to have access (right then) to the db, and try to parse your queries. VS doesn't do that. Linq-to-SQL and Entity Framework might get you closer, but I don't think they'd even be able to help with your specific scenario (a change to a stored procedure).

You might be able to create something using .NET SMO (Sql Management Objects), but even that would need to be connected to the db to do any good... The underlying problem seems to be that you need to check that a Stored Procedure still returns what it did when it was originally written, and I don't think SQL provides that kind of data in any easilly accessable form (other than just looking at the proc).

Answer 2

Basically, no. It will always be a runtime error.

You will always have to execute code against SQL Server whether it's to parse, run, reflect using AutoSProc, whatever. SQL Server does not care if you're Excel/VBA or Visual Studio: you're just another customer...