I am converting an existing T-SQL stored procedure into CLR C# .NET. It has been drilled into me that hardcoding SQL statements in .NET application source code is evil. Is a CLR stored procedure an exception to this rule? What other alternatives do I have? I can't very well call a T-SQL stored procedure instead...
6
-
1You certainly can call T-SQL stored procedures from a CLR procedure..stuartd– stuartd2015-06-02 14:28:58 +00:00Commented Jun 2, 2015 at 14:28
-
While this question may be construed as opinion-based, I think it should stay open because I would really like to see how people answer this.John Wu– John Wu2015-06-02 14:29:03 +00:00Commented Jun 2, 2015 at 14:29
-
Hardcoding Sql into C# is not necessarily evil, but it can lead to abuse and injection with inexperienced devs.Joel Coehoorn– Joel Coehoorn2015-06-02 14:29:54 +00:00Commented Jun 2, 2015 at 14:29
-
1I suggest to close this question as it's more appropriate for some "highly-theoretical" discussion forums. In case you want to keep it open, then please re-word it professionally instead of discussing something like that (quote): "...hardcoding SQL statements in .NET application source code is evil". Formulate the specific issues (rather than abstract "evil") and provide samples to highlight these issues. Thanks for the understanding. Best regards,Alexander Bell– Alexander Bell2015-06-02 14:36:44 +00:00Commented Jun 2, 2015 at 14:36
-
1Is there a reason you can't just call the stored procedure? Why are you trying to move the database logic out of the database?TTeeple– TTeeple2015-06-02 14:40:17 +00:00Commented Jun 2, 2015 at 14:40
|
Show 1 more comment
1 Answer
I can't very well call a T-SQL stored procedure instead...
I"m not sure what logic you need to be held inside your CLR component, however you can certainly call stored procedures from the CLR component to retrieve the data you want for processing. You can also call stored procedures to update the data after you've processed it.
It has been drilled into me that hardcoding SQL statements in .NET application source code is evil. Is a CLR stored procedure an exception to this rule?
There are many reasons not to put hard coded SQL statements into compiled code and instead to use stored procedures. We could easily list and debate the reasoning behind it but I suggest that if you currently have this rule, then, yes, it applies to CLR's as well. If for no other reason than to be consistent.
1 Comment
stuartd
Agreed. Just noting though that many databases have a rule that all data access must use stored procedures (eg for security reasons), so hardcoded SQL cannot be used. However when you are running in a stored procedure already (ie the CLR procedure) then the lines do become blurred.
