How could this be done -- efficiently -- in .Net?
As you can tell if you check out the links on the right, I had a previous career as an IBM AS/400 specialist. Now, I have a whole rant on the subject of What I Miss About The Bad Old Days, that goes into things the AS/400 did right, but this post is about one thing in particular: level checks.
A program written on the AS/400, when compiled, contained information about the database tables it had been compiled against. Specifically, for each table or view (we called them different things, but that's what they were), it contained a guid-like string that represented the structure and some of the attributes of the table / view as it had been at compile time. If you attempted to run the program against a different table, or the same table after structure changes, the level ids wouldn't match, and the program wouldn't run. This was called a level check.
I'd like to be able to do something similar with a .Net application and the database it was written for. Specifically, I'd like for an application to be able to tell that the stored procedure it's about to call isn't the same procedure that it was compiled for. Oh, the specifics of the change may or may not matter: what I want is to have some flag raised that says: something has changed since we finalized this release.
This would be handy in sorting out problems with installation packages and incorrect deployments. I'll readily admit that if you had to contend with this during development, it would drive you crazy. But once you think you have everything finalized, if you could turn on this integrity check, it would be very handy.
A program written on the AS/400, when compiled, contained information about the database tables it had been compiled against. Specifically, for each table or view (we called them different things, but that's what they were), it contained a guid-like string that represented the structure and some of the attributes of the table / view as it had been at compile time. If you attempted to run the program against a different table, or the same table after structure changes, the level ids wouldn't match, and the program wouldn't run. This was called a level check.
I'd like to be able to do something similar with a .Net application and the database it was written for. Specifically, I'd like for an application to be able to tell that the stored procedure it's about to call isn't the same procedure that it was compiled for. Oh, the specifics of the change may or may not matter: what I want is to have some flag raised that says: something has changed since we finalized this release.
This would be handy in sorting out problems with installation packages and incorrect deployments. I'll readily admit that if you had to contend with this during development, it would drive you crazy. But once you think you have everything finalized, if you could turn on this integrity check, it would be very handy.
posted by Ann at 12:16 PM
3 Comments:
You could use something like...
SELECT
[p].[object_id] AS [id],
[s].[name] AS [schema],
[p].[name] AS [name],
[m].[definition] AS [definition] FROM [sys].[procedures] [p] INNER JOIN [sys].[sql_modules] [m]
ON [m].[object_id] = [p].[object_id]
INNER JOIN [sys].[schemas] [s]
ON [s].[schema_id] = [p].[schema_id]
WHERE [p].[is_ms_shipped] = 0;
To obtain the all of the procedure names and actual source, and then hash the source of each in some type of PROCEDURE_NAME=SHA1HASH file as an embedded resource in the assembly.
Then the query again during startup to query the procedure sources and names, and compare their hashes to your stored ones.
Here is an example wrapper around the stored procedure sources that I use for my scheduler (it's in java, but should be about the same for C#)...
http://code.google.com/p/jezel/source/browse/src/main/java/org/havokmultimedia/jezel/storage/Sql.java
What Steve said. You could even use Reflection to get the details of the sproc.
Hmmm ... this would be a really cool add-on/function to write for .NET.
But nowadays, I'm developing in python on a Mac.
Thanks Don!
I started learning Python just recently as well, after I switched to Mercurial.
As far as the hash file goes, a little EXE to scan your web.config/app.config for connection strings and build the hash file would be all you needed. Add that to your NANT/MSBUILD script you use for TAGS/RELEASES to add the file to your final assembly and you should be about good to go.
Post a Comment
<< Home