How should I interact with a database from a desktop application without opening it to the whole world?

Question

Basically, I have a new desktop application my team and I are working on that will run on Windows 7 desktops on our manufacturing floor. This program will be used fairly heavily as it gets introduced and will need to interact with our manufacturing database. I would estimate there will (eventually) be around 100 - 200 machines running this application at the same time.

We're lucky here, we get to do everything from scratch, so we define the database, any web services, the program design, and any interaction between the aforementioned.

As it is right now, our legacy applications just have direct access to a database, which is icky. We want to not do that with the new application.

So my question is, how do I do this? Vague, I know, but basically I have a lot at my disposal here, and I'm not entirely sure what the right direction to go is.

My initial thought, based on what I've perceived others doing, is to basically wall off the database by using webservices. i.e. all database interactions from the floor MUST occur through the webservices, providing a layer of security by doing much of the database logic behind closed doors. Webservice calls are then secured to individual users via Active Directory.

As I've found though, that has some implications of its own... We have to abstract the data before it reaches the application. There's still potential for malicious abuse by using webservice calls repeatedly to ruin or spam data. We've looked at Entity Framework and really like what it provides, but as best I can tell, that's going to be unavailable by the time we're at the application level in this instance.

It just seems like I can't come to a conclusion on what is "right". So, what is right?

Answer 1

WebServices sounds like a right approach. Implementing a SOA-oriented layer on the webservices layer gives you a lot of control over what happens to the data at the database server.

I don't quite share your doubts about repeated calls doing any damage - first you can have an audit log of every single call so that detecting possible misuses is obvious. But you also could implement a role based security so that web service methods are exposed to users in roles, which means that not everyone will be able to call just any method.

You could even secure your webservices with forms authentication so that authentication is done against any datasource, not only the active directory.

And last thing, the application itself could be published as a ClickOnce application so that it is downloaded and executed from the web page and it automatically updates itself just as you publish new versions.

If you need some technical guidance, I've blogged on that years ago:

http://netpl.blogspot.com/2008/02/clickonce-webservice-and-shared-forms.html

Answer 2

My suggestion since you are greenfield is to use an API wrapper approach with Servicestack.

Check out: http://www.servicestack.net/ServiceStack.Northwind/

Doing that you can use servicestack authentication, abstract away your db layer (because you could move to a different DB provider, change its location, provide queues for work items etc...) and in time perhaps move your whole infrastructure to an internal intranet app.

Plus Servicestack is incredibly fast, interoperable with almost any protocol you through at it, and provides for running it through MONO, so you are not stuck with a MS backend that could be very expensive.

My two cents. :)