We currently have an aging client/server based application where all the clients connect directly to our transactional database (MS SQL Server)... 99% of our business logic is located in the stored procedures.
We've been toying around with building a ServiceStack API on top of the existing database. Eventually, we'd like to migrate all business logic into the ServiceStack API and make it the sole entry point for CRUD operations. Everything makes sense in my head except Reporting and Searching.
My question is this... currently we allow Crystal Reports / Excel / etc to connect directly to the database to pull data - this makes writing reports very flexible as we can JOIN tables and/or write SUBQUERIES to produce desired results.
How will something comparable be accomplished when we make our ServiceStack API the only entry point into the database?
Is it possible to allow robust searching through a ServiceStack API or is there another technology that could give us the same flexibility (joins, subqueries) and what application would be a good tool to build these reports in?
I divide your question in pieces and try to give answer.
If you want single endpoint using Servicestack API and you have your database ready here is the article that can help you.
Now, for reports, instead of pulling from database pull from API and do the query. That can help. But more like Crystal report and/or SSRS, direct access of db is good. Means it will give more flexibility to use. I had work with SSRS and we are doing like that only. But again SSRS take collection also as data provider, check it out if you can do same with Crystal reports.
Searching is little bit complicated with Servicestack and db. But it is possible if you are exposing search as API it self. It will be great if you can move to elastic search or lucane for this kind of stuff. It will be more flexible and useful in that case.
I hope I have clear your quires. If there is anything please let me know.