WCF OData service against multiple databases

0
votes

Our architecture has many database, all with the same schema. I'd like to expose an odata service via a WCF service for our SQL Server. We are using Entity Framework Odata Service Provider for this implementation.

I saw this post, but his requirements need merging of data.

I just need to authenticate and then direct the user to the database where their data is stored.

Does anyone know how to add in a layer to the odata authenticate service that would set the connection string to the correct database based on the authenticated user?

1
c#.netsql-serverwcfwcf-data-services
Hmm, looks like microsoft would rather you use web api to implement an Odata service. Will post back if I find a solution with web api and Odata. - TWilly

1 Answers

0
votes

I switched to Web Api 2 and Odata Integration.

Once inside Web Api, you can create filters to hook into and add in logic to then select a specific database.

I'm also able to use built in OAuth2 for authentication.

public class MyOdataController : ODataController
{
    protected AMMetricsEntities db;
    protected EntityConnection ec;
    protected IEnumerable<Claim> claims;
    protected long userId;
    protected string userName;

    private string GetConnectionString(string dbId, string dbName)
    {
        // this is where we get token from header information and populate the connection string
        return
            String.Format(
                "metadata=res://*/Models.AMModel.csdl|res://*/Models.AMModel.ssdl|res://*/Models.AMModel.msl;" +
                "provider=System.Data.SqlClient;" +
                "provider connection string='data source={0};initial catalog={1};persist security info=True;user id={2};password={3};" +
                "multipleactiveresultsets=True;application name=EntityFramework'",
                dbServer,
                dbName,
                Username,
                Password);
    }

    public MyOdataController()
    {

        claims = ((ClaimsIdentity)User.Identity).Claims;
        if (claims.Any())
        {
            userId = Convert.ToInt64(User.Identity.GetUserId());
            userName = User.Identity.GetUserName();

            var dbName = claims.Where(s => s.Type == "DBName").Select(c => c.Value).First();
            var dbServer = claims.Where(s => s.Type == "DBServer").Select(c => c.Value).First();

            ec = new EntityConnection(GetConnectionString(dbServer, dbName));
            db = new ProductEntities(ec, false);
        }
    }
}