I'm a junior programmer, am working on a WPF application that will deploy to ~50 users.
We basically are streamlining all of his charting/tracking of client data. 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? Should I be leveraging another technique like a web service? Never worked with web services or web apis or web workers.. etc.. I'm basically just doing what it takes to make it WORK.
Again… I'm a junior dev…& I know just enough to be dangerous.
My concerns are that it is less secure and I'm afraid of higher costs from Azure because I'm doing something a bad or terrible way.
Best Answer
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.
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
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.
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.