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.
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).
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.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).
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.