CODEWITHSUNDEEP
c#.netwcfentity-framework
Brian Hanf
Brian Hanf

Reputation: 564

How to have application use different databases via wcf dataservice

We are building a c# .Net application that will be sold to clients, the SQL Server database will be hosted by us. We will have a database for each client. We are not sure if we can use one WCF data service to access the different databases. Using Entity Framework to build the database.

How can I accomplish this so the client can pass in the correct database name or connection string?

This Different databases using WCF dataservice said it is possible, but doesn't really get into specifics.

This WCF Service for Multiple clients with database(each for client) looks to be the same question, but has no answers.

Upvotes: 2

Views: 2118

Answers (3)

Wiktor Zychla
Wiktor Zychla

Reputation: 48230

We are doing it for years and it all it about url rewriting so that

http://the.application/client1/service

http://the.application/client2/service

are internally rewritten to the same

http://the.application/service

but in the same time the rewritten part is used to find a connection string in a server-side mapping between client codes and connection strings.

There is a security issue here, you don't want your clients to switch between data sources just anytime. We have solved this by putting the client code in the user data section of the security cookie and we validate the cookie upon every request.

Upvotes: 1

makim
makim

Reputation: 3284

Make your WCF-Service Session based, provide a Login-Method and in this Method you have to decide which Database to use, you can either change the ConnectionString for the edmx if the DataModel is the same or if you have differnt DataModels for each client you have to create an edmx instance for each client!

Here some simple pseude-code, entityID identifies the Client

for creating an EntityConnectionString check out this Link

To create a Session-Based WCF Service you have to define your Service Interface like that

[ServiceContract(SessionMode = SessionMode.Required)]
public interface ISampleService
{
    [OperationContract]
    void Login(string user, string password, int entityID);
}

and the ServiceImplementation should have these Attributes, change these values based on your needs

[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession, ConcurrencyMode = ConcurrencyMode.Single, AutomaticSessionShutdown = true)]
public class SampleService : ISampleService
{
    SampleEntities datacontext = null;
    public void Login(string user, string password, int entityID)
    {
       if(CheckLoginData(user, password))
       {
         InitDataContext(entity_id);
       }
    }
    private void InitDataContext(int entityID)
    {
       var connectionString = GetConnectionStringFromEntityID(entityID);
       datacontext = new SampleEntities(connectionString);
    }
    private string GetConnectionStringFromEntityID(int entityID)
    {
        var providerName = "System.Data.SqlClient";
        var serverName = "localhost";
        var databaseName = GetDatabaseNameFromEntityID(entityID);

        var sqlBuilder = new SqlConnectionStringBuilder();
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = true;

        var providerString = sqlBuilder.ToString();

        var entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.Provider = providerName;
        entityBuilder.ProviderConnectionString = providerString;

        entityBuilder.Metadata = @"res://*/SampleDatabase.csdl|
                        res://*/SampleDatabase.ssdl|
                        res://*/SampleDatabase.msl";

        return entityBuilder.ToString();
    }
}

Upvotes: 2

Todd Richardson
Todd Richardson

Reputation: 1129

How can I accomplish this so the client can pass in the correct database name or connection string?

I honestly wouldn't give the client too much flexibility on what connection string it can use. You could store your connection strings to a common DB, which given a user credential can return a correct connection string... But that just doesn't give me a warm and fuzzy feeling.

In my mind if you are planning on having lots of customers, it would make more sense to deploy a WCF service per customer. That way they are isolated from your other customers. You can still host them within the same IIS server, but you can use different endpoints. This would also allow you to leverage different authentication schemes as provided by WCF.

Good luck on your solution.

Upvotes: 0

Related Questions