What is the simplest, but solid, interface from WinForms to a SQL Server database?

Question

Hi,

If I wanted to have my data in SQL Server, but wanted to use a thick client WinForms application for users, what would be the best practice way to have calls occurring from WinForms to database? And how simple is this?

I guess I'm trying to gauge to what extent there are issues with this approach and one needs to go for some (a) middle tier with web services, or (b) have to go asp.net or something.

I really just have a simple app that needs a database and I'll only have a 10 - 30 clients on a LAN/WAN network that would be connecting in.

EDIT: So the focus of my question was regarding the communications protocol/approach from the client PC to the Database, i.e. over the LAN/WAN

Answer 1

A simple way is to use an ORM such as LINQ to SQL. This will save you having to write the SQL yourself but still generate reasonably efficient database queries. Other examples of ORMs are the entity framework and NHibernate.

If you want to write the database queries yourself then you can use ADO.NET.

You might not want clients to have direct access to the database but instead to communicate to a service you are running. Then you could choose to create a WCF service instead of allowing direct access to the database.

Answer 2

Having a data access layer would be a good idea as it will separate your choice of data persistance from the rest of your application and allow you to more easily change it should the need arise in the future.

If your data is heavily object related then you might decide to use an Object Relational Mapping tool/framework to map the application entities to the data in the database. If you don't want to use an ORM, then the least you should be doing is using parameterized SQL statements.

Answer 3

The simplest solution if you have PC's and DB server close by is to use direct Linq-to-SQL approaches - no extra WCF service layer in between. Such a service layer adds a significant amount of complexity.

If you want to go that route (maybe because of your WAN clients), then I'd recommend you check out the WCF Data Services, a layer on top of bare WCF. It's probably the easiest and quickest way right now to expose a data model (e.g. your Linq-to-SQL or Entity Framework model) as a REST feed to WAN/internet clients.

WCF in itself is indeed a big beast and thus scares away a lot of folks - but you don't need to know each and every setting and knob it has in order to use it. For some intro to WCF, I recommend those two episodes of DotnetRocks TV - excellent intro material:

• Miguel Castro: Extreme WCF
• Keith Elder Demystifies WCF