We have some back end processes that runs* on our sql server (SQL Server), they involve processing claims. This requires both data manipulation (biz logic) and data read/write to tables. The biz logic contained should never be used by any of our end user (web/fat client) applications, just for this (runs once, nightly) process.
* By "runs" I mean it runs, but it is slow, full or errors, and is a nightmare to maintain, we're looking to redo this to fix these issues.
One thing I'm trying to avoid is writing a 3000+ line Stored Proc (we have a few) that does everything, it isn't maintainable.
My initial thoughts/approach is to use:
- SQL CLR (Sql 2012 / C# .Net 4.0) for the biz logic (there will be some complexity to the logic)
- Entity Framework for any simple CRUD read/write
- Stored Procedures for any complex data read/writes.
There will be a final step of writing our end results (data) to other companies databases, but that's someone else responsibility.
I'd like some thoughts about this approach if it is a good one (is there a better approach?), any suggestions would be appreciated.