Jakob Lithner
3/31/2008 8:54:00 AM
1) IDENT_CURRENT in Insert statement is quite clever. The downside is I have
to override the Insert statement generated by LINQ-to-SQL.
2) External systems previously called logic in database. This forced me to
have quite complex logic in stored procedures. It has given me many problems
as logic is changed. I found Enum values in stored procedures is no good idea
when many of them needed to change .... The logic in SQL is also quite
limited causing complicated code.
Now I am switching interface and will use webservices for external systems.
In .Net code I have much better overview and will immediately detect
dependencies of changes.
What kind of security issue are you afraid of? The SQL user will only be
used by my code and must be protected anyhow. Integrity should be possible to
uphold in both cases.
3) I always like to guarantee data integrity in database and hesitate to
open up ParentID column by allowing NULL. Actually I decided to go for a
compromise. I call IDENT_CURRENT function and use this value to calculate
expected next ID which is used to set ParentID value. After insert I check
created ID column to ensure it is same as ParentID. In most cases it will of
course be identical. In most unlikely case where they differ I am able to
adjust ParentID to the ID value. Much ado about nothing maybe, but it is
robust and I am happy :)
4) I guessed IsolationLevel=Serialize was necessary but decided to avoid it
because it is too expensive for intensive transactions.
Thanks for useful feedback!