Sharepoint 365 MVC 4 with Azure Database

Question

I've created an autohosted MVC 4 app to embed into a Sharepoint 365 account.

I have followed the tutorial here: http://msdn.microsoft.com/en-us/library/fp179902.aspx to the letter AFAIK.

The application is standalone, I just need to embed it into Sharepoint for the correct people to have access.

Here is my Home Controller:

namespace ResourceTrackerWeb.Controllers
{
    public class HomeController : Controller
    {
        static SqlConnection GetActiveSqlConnection()
        {
            return new SqlConnection(GetCurrentConnectionString());
        }
        static string GetCurrentConnectionString()
        {
            return WebConfigurationManager.AppSettings["SqlAzureConnectionString"];
        }
        private ResourceTrackerWebContext db = new ResourceTrackerWebContext(GetActiveSqlConnection(), true);

        [SharePointContextFilter]
        public ActionResult Index()
        {
            User spUser = null;

            var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);

            using (var clientContext = spContext.CreateUserClientContextForSPHost())
            {
                if (clientContext != null)
                {
                    spUser = clientContext.Web.CurrentUser;

                    clientContext.Load(spUser, user => user.Title);

                    clientContext.ExecuteQuery();

                    ViewBag.UserName = spUser.Title;
                }
            }
            var projects = db.Projects.Include(p => p.Customer).Include(p => p.JobType).Include(p => p.Location).Include(p => p.ProjectStatus);
            return View(projects.ToList());
            //return View();
       }
    }
}

If I uncomment the last return View() and comment out the projects variable load, I work fine. Once I use the DbContext, I get an "Unknown User" error. This seems to be something with using the SharepointContext and DbContext at the same time.

Also, the code works fine in debug mode. When I deploy it to Sharepoint 365, this happens.

I also altered the init function for my ResourceTrackerWebContext as such:

public ResourceTrackerWebContext(SqlConnection conn, bool contextOwnsConnection) : base("name=ResourceTrackerWebContext")
    {

    }

So my question is. How do I configure my DbContext to work with my Azure database in this case? The only answer I've found is to manually rewrite all of my Controllers to access the SqlConnection and query the db from there. Is there a way to do this without rewriting the functionality of the Controllers?

Answer 1

The "Unknown User" error is displayed whenever any unhandled exception occurs in your app. Obviously that's confusing because often it's nothing to do with whether the user is known or not. The best way to sort it out is to find out what the actual error is. You can do this by enabling the exception stack trace page in ASP.NET:

1. Delete the /Views/Shared/Error.cshtml page - you can put it back in later.
2. Open your project's main web.config file and find the system.web tag. Insert this tag inside it: <customErrors mode="Off"/>

Deploy your app and give it another go. Now, in my case, I wasn't able to instantiate the DbContext as a class member. So this could be the offending line:

    private ResourceTrackerWebContext db = new ResourceTrackerWebContext(GetActiveSqlConnection(), true);

If you rewrite it to use lazy initialisation, it should work:

    private object _lockObj = new object();
    private ResourceTrackerWebContext _db;

    private ResourceTrackerWebContext DB
    {
        get
        {
            if(_db == null)
            {
                lock(_lockObj)
                {
                    if(_db == null)
                    {
                        _db = new ResourceTrackerWebContext(GetActiveSqlConnection(), true);
                    }
                }
            }

            return _db;
        }
    }

I did initially try using the .NET 4 Lazy class but for some reason I saw the same problem.

I know you mentioned wanting to not make changes to all of your controllers, but you could always just pop this into a base class and make it protected.