C# – Clients connect to WCF or SQL Server

Architecturecsqlwcfweb services

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?

Best Answer

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.

Related Topic