Clients connect to WCF or SQL Server

Question

I was wondering what is the prefered way of accessing data from a server in a client application. The data will be stored on the server in an SQL database and I have previously used the following 2 methods for getting at the data:

1) Client's connect directly to the SQL database
2) Client's connect to a web service which runs the sql query and then returns the data.

The program in this instance will be written in C#, so the following technologies will be in place:

Entity Framework (connect to sql database)
WCF (web service)

From a coding point of view all of the data access logic will be written in repository classes whatever method is used, so the physical setup of connections should not have any effect on this. For example a simple repository might be:

public interface IUserRepository
{
    IList<User> GetUsers();
}

public SqlUserRepository : IUserRepositoy
{
   private MyDbContext _dbContext;
   public SqlUserRepository(MyDbContext dbContext)
   {
      _dbContext = dbContext;
   }

   public IList<User> GetUsers()
   {
       return _dbContext.Users.ToList();
   }
}

public WcfUserRepository : IUserRepository
{
   IUserRepositoy _userRepository;

   public WcfUserRepository(IUserRepository userRepository)
   {
   }

   [WebGet(UriTemplate = "/users")]
   public IList<User> GetUsers()
   {
      return _userRepository.GetUsers();
   }
}

So either way the clients will only rely on IUserRepository.

The problem I am having is that using WCF seems more secure, and the extra level of abstraction makes it easy to change database providers. However when using this solution before I have always had problems with performance, and big problems if a large data set is returned.

For example if there are 10,000 users in the system using entity framework this is no problem, but depending on how WCF is configured this can cause a message too large error.

Any opinions on the best practise here?

How can WCF be configured to reduce the likelihood of giving problems for large return values?

Obviously WCF will always be slower than direct SQL as there is an extra step of negotiation the SOAP / REST message. But can this be optimized down to an acceptable level?

Answer 1

I'd say it depends on the application and its users. If its an internal company app localized to an office location talking to the db directly is better, as building a service between the app and db makes the building of your software more expensive for no real gain. If there are physically disconnected branch locations or your app is available outside of the company's internal network a service will bring benefits such as security (opening your SQL db to the world shouldn't be considered) as well as keeping the relatively chatty db traffic on the company network which is faster while having few round trips between the client and service.

Entity framework in either case is how you abstract specific db servers, although in practice changing is rarely done.

Also in either event you should limit how much data comes back. Its unlikely users can work with 10,000 rows on and individual basis. Paging will help there.

Answer 2

By all means, the second option you said is the better choice: 2) Client's connect to a web service which runs the sql query and then returns the data.

There are a lot of reasons as to why the DB access should be shunted away from the client, but here's a list of some of the more important considerations:

Security

• Using a web service allows you to filter and sanitize the incoming requests prior to executing the query.

• Access credentials directly into the database or stored only within the web service and not at every single client trying to access the database. A central location makes it much easier to provide routine security maintenance like updating passwords and locking out previous users of the system.

Administration

• When you need to update the database (move to another server, schema, etc...), it's much more easily accomplished when there's a single connection point to worry about instead of a large number of distributed connection points.

• Using a service provides a looser coupling between the clients and the database. That makes it easier to scale the database, the number of clients, and to change out the types of clients as necessary.

• If query loads get too heavy, it's easier to enable data caching within the web service than it would be within the database environment.