How should I access SQL Server from a WCF service solution?

Question

I have some existing WCF code that accesses SQL Server 2005, but honestly I've come to mistrust that developer's methods, so I want to know how this should be done correctly and professionally. I need to be able to pass an SQL statement to a method (within the WCF service, not from the client) that returns the resultant dataset (to the method in WCF that called it, not the client). I'm not interested in entity frameworks or other abstraction layers. I need to run SQL, DML, and hopefully DDL too.

I also want to know how to manage the connections.

Please point out your thoughts on better alternatives if you feel like it. I'm prepared to listen.

Solution

Honestly, you shouldn't access SQL Server from a WCF service.

You should access data from a WCF Service, without knowing there is SQL Server under the hood.

Exposing a webservice that accepts a SQL statement looks definitely like a terrible idea to me. You don't want to expose your database to the client of your service for a lot of reasons (security...etc.).

What you could (should) do is to write a service that returns the data you actually want to return. For instance:

[DataContract]
public class Customer
{
    [DataMember]
    public string Name { get; set; }
}

[ServiceContract]
public interface IService
{
    Customer GetCustomer(int customerId);
}

[ServiceBehavior]
public class Service
{
    [OperationContract]
    public Customer GetCustomer(int customerId)
    {
        // Insert DB-related implementation of your query:
        // - you could hard-code a SQL query
        // - you could use Entity-Framework or other ORM
        //
        // First, create your connection to your database
        // Then query
        // Then close your connection
        //
        // Example with SQL connection
        // Connection string comes from server configuration (app.config or whatever)
        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            Customer res = new Customer();

            // query here

            Customer.Name = XXX;    // From DB result
        }
    }
}

On client side:

ServiceClient proxy = new ServiceClient();
Customer myCustomer = proxy.GetCustomer(42);

You really shouldn't think about reusing SQL connections. The connection pool of your database will handle this for you. Creating/closing a connection to the DB is a light operation. Creating a new connection for each webservice call will avoid LOTS of trouble (connection lifetime...etc.).

Use stateless services as much as possible, it'll save you a lot of time (concurrency issues, object lifetime management...).

Comments

[Sam]

+1 for your "honesty"... but I clarified in my edited question text that the sql is not being passed in from the client. I'm talking about an SQL statement from one method to another within the WCF service. The client will be limited to the exposed contracts.