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.

From the question and comments, it seems that the database is stored on Azure, with multiple clients accessing the database over the internet.

With this in mind, I would recommend making use of some sort of public-facing interface between your client application and database and making sure the database itself is not publically accessible (no public-facing ports should be open). Be it a Web Service or REST API. As pointed out by the answer to the question Why shouldn’t you use JDBC in Java applets?, the database server should not be publicly exposed as this opens yourself up to possible attacks.

From a data standpoint, using a single interface between the database and clients allows you to enforce data validation and business logic. Exposing the data as either a Web Service or REST API also abstracts the clients for the underlying database software & schema.

Overall, using an interface between the database and clients provides better control of the access to your database. Doing this also allows for making use of different client types such as a mobile application as accessing the data is abstracted from the underlying database type, software or schema.

The answer to your question will be mostly decided on the below points.

1. Will your WPF application be limited to those ~50 users or there is a scope of increasing userbase..

2. What kind of Read Write operations the user are doing. (Do you really need security)

3. Will your WPF application be the only client for that data (is there any possibility that the application be ported to mobile devices like android)

The reason behind questions is
1. if your application is only being limited to those ~50 users and you know the users are nonTechnical (They will not try to break the system using erroneous input or trying to call the database layer eg. with sql injection attack) in that case you can use the direct SP call from your application.

2.if the data that your users are posting to database or retrieving from the database is very confidential and should not be visible to anybody snoofing the traffic. then in that case you should try to have a more secure way of transport. One way can be implementing the SSL certificate for accessing the data to and from the web service.

3.If the data need to be accessed by different clients (including mobile devices, Web applications etc. In that case also implementing the services is always a better option as you have the complete data access layer separate with proper security implemented)

I hope this answers your question.