No, you don't need to worry about a problem that isn't occurring. If your query isn't timing out or running unacceptably slow, you can allocate your time elsewhere.
If you are allowing very complex queries, the question of why you are not allowing slightly sanitized SQL arises. And if you allow arbitrary select statements, then how you handle timeouts and errors and cancellations becomes more important than the performance of your gui-sourced sql.
I've seen and worked with web apps that don't allow direct sql even for competent users, and the results have always been disappointing. Empowerment lets you handle the edge cases from a design perspective, and focus your attention on where it can get the most benefit instead of doing a user's work for them all the time.
a WPF application that will deploy to ~50 users
Each user will probably make about 25 read/writes a day.
I have everything working in Azure but my issue/fear/question is I'm directly calling stored procedures in the Azure SQL db for all CRUD/Loading operations... is this wise/correct?
There are two main issues here: security and maintenance.
Security
When the WPF app connects to the database, it uses a connectionstring. These connectionstrings can be found bu your users, whether it's by capturing the network call or simply reading it from the application's config file.
There are many possibilities here, but all of them are bad in some way.
- If you use windows authentication, then every user needs personal access to the database server, which is not what you generally want.
- If you use explicit credentials, the user now has access to the database server address and valid credentials to login and perform actions.
- In either case, this connectionstring can be used to connect to the database directly (e.g. using SSMS) and allows the user to do things they shouldn't be allowed to do.
At best, this means the user can circumvent business validation logic that's baked into your WPF app. At worst, this is a massive privacy breach. It depends on how much access the user from the connectionstring has in the database.
When you implement a web service (which is the most appropriate for an Azure database), you have a nice gatekeeper for your database access. Users log in to the service (not the db), and you can obfuscate the internal workings of your service (whether it uses a database, third party API, ... is hidden from the user).
Maintenance
I'm directly calling stored procedures
- What happens if tomorrow you change your database structure, and no longer rely on a stored procedure?
- What happens if you change the input (or output) parameters of the sproc?
- What happens if you release a new major version but you require backwards compatibility for earlier versions?
- What happens if your database gets split up into many small databases in a future major version?
- What happens if part of your data is fetched from a third party API? Do you expect your application to directly call this third party API?
In theory, you could prevent this by versioning your stored procedures (MySProc_v1.0, MySProc_v1.1, ...). But I think you quickly see that this is going to massively bloat the amount of sprocs your database has, and it's going to be maintenance hell.
Disclaimer: As a general rule I don't like sprocs because they are (IMHO) maintenance hell even if you don't version them; so I might be biased here.
With a web service, you allow for dynamically redirecting the user's request to the correct data store (in case you have more than one). Any change to the used data store can be covered for by the web service so that your user's application doesn't notice the difference and thus doesn't need constant updating to account for every minor change to the backend.
I'm basically just doing what it takes to make it WORK.
Ah, the nostalgia of being a junior developer :) I get your point and I'm aware that junior developers shouldn't be measured by a medior/senior's standards.
But I do want to stress that "it works" is not a valid measure of quality. It is the first box to tick, but it is not the only box to tick.
Extending the "it works" logic into an obviously silly example, I shouldn't take my car to a mechanic as long as it still moves; even if the car only has two wheels, broken windows and emits a massive black smoke column.
However, which boxes you need to tick is a contextual consideration. The security concern I listed above is valid in most enterprise scenarios, but doesn't matter for a small personal tool you've written. The maintenance concern is valid for projects where you expect reworks to happen, but doesn't matter for a project that is not expected to be reworked (I write single-use tools regularly, e.g. to automate a small repetitive job).
To summarize
Should you be using a web service here? Well, that is the better approach in general. There is a strong current in software engineering to not directly connect the end user to the database, and instead have an intermediary to handle the interactions between user and database.
But contextual exceptions can exist.
Best Answer
The idea behind this is probaby to keep a low degree of coupling. Your application talks to the web service only, and the web service talks to stored procedures only. There are no SQL statements in your front-end code, only web service calls; and there are no SQL statements other than stored procedure calls in the web service. There are several advantages to this:
And there's the responsibility thing: By moving your actual queries to the database, they become part of the DBA's realm. If you have a decent DBA, he's probably much better at SQL than you are (especially when it comes to obscure yet business-critical edge cases that can have dramatic effects on performance), and it's a good thing to have him at least check your SQL before it goes into production.