ASP.NET MVC4 Project + SQL CE 4 limitations

With the official RTM release of Visual Studio 2012 and the MVC 4 project (also available for VS 2010), I have been using some of the features of new features and out of the box features of hte MVC 4 project to ease the development of my user stories.

SQL CE 4 made database backends much simpler by allowing developers to utilize a SQL engine even if you don’t have it installed on your computer and the zero-config usage is great. SQL CE does have some its limitations and the OAuthWebSecurity class  exposes one such limitation: Transasction Scopes.

Given a development environment with a SQL CE 4 database, you can register a number of authentication providers such as Facebook and Google out of the box with no issues.

Dictionary<String,Object> facebookPermissions = new Dictionary<string,object>() ;
facebookPermissions.Add( "scope", "email" );
OAuthWebSecurity.RegisterFacebookClient(
  appId: "My Facebook App ID",
  appSecret: "My Facebook App Secret",
  displayName: "Facebook",
  extraData: facebookPermissions
);

OAuthWebSecurity.RegisterGoogleClient();

The limitation materializes when trying to disassociate the authentication provider (in this case, Google) from the base account. If you are using SQL CE 4 and the code made available to you with the out of the box ASP.NET MVC 4 project, you will get the following error when attempting to disassociate the account with the OAuth provider.

The connection object can not be enlisted in transaction scope.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection object can not be enlisted in transaction scope.

Source Error:

Line 110: using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
Line 111: {
Line 112:  bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
Line 113:   if (hasLocalAccount || OAuthWebSecurity.GetAccountsFromUserName(User.Identity.Name).Count > 1)
Line 114:   {

Deploying an ASP.NET Web Application with SQL CE 4 & Entity Framework without installation

Update – 12/15/2011

As of Visual Studio 2010 SP1, there’s an easier way to do everything below.

In the Solution Explorer, right click the project and select ‘Add Deployable Dependencies…’, select the dependencies you want to include, and click OK.

Visual Studio 2010 - Add Deployable Dependencies

This will generate the folder _bin_deployableAssemblies with the proper DLLs. Now when you build your solution, the DLLs in _bin_deployableAssemblies are copied to the bin folder.

See Scott Hanselman’s post for a more thorough explanation.

Original Post

I have built an ASP.NET project using SQL CE 4.0 and Entity Framework 4.1. When the time came to deploy the web application to our managed environment, I ran into some issues due to some missing references.

Do to the nature of our hosted environment, installing the EntityFramework and SQLCE4 libraries was not an option but I have worked out all of the required steps to get the web application working in the deployed environment.

Referenced Libraries

The following libraries should be referenced in your project.

EntityFramework
System.Data.SqlServerCE
System.Data.SqlServerCE.Entity
System.Web.Providers

Make sure to set the Copy Local property to true.

Manual Copy

The following files should be copied as part of your build script process. These files are placed on your file system when you install the SQL CE 4 package.

sqlceca40.dll
sqlcecompact40.dll
sqlceer40EN.dll
sqlceme40.dll
sqlceoledb40.dll
sqlceqp40.dll

Web.config

On my development box, the machine.config has been modified (when I installed SQL CE 4) to include the SQL CE 4 provider. To use the provider without explicitly installing it on the server, we need to manually add the provider in the Web.config

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

Template for SQL Server Stored Procedures

Talking with our in house DBA, it was mentioned that DROP/CREATE is a bad model to follow when updating stored procedures. However, this is the model used in the stored procedure template provided by Visual Studio 2005/2010. Here is a modified template that creates an empty stored procedure if it doesn’t exist and performs an ALTER on all subsequent calls.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
BEGIN
exec sp_executesql N'CREATE PROCEDURE [dbo].[Stored_Procedure_Name] AS select 1'
END
GO
ALTER Procedure Stored_Procedure_Name
(
  @parameter1 int = 5,
  @parameter2 datatype OUTPUT
)
AS
GO

GRANT EXEC ON Stored_Procedure_Name TO PUBLIC

GO

Enterprise Library Data Access Application Block + SQL Optional Parameters

The Enterprise Library Data Access Block provides a number of ways of calling a stored procedure.

Database _db = DatabaseFactory.CreateDatabase(<Connection String Name>);

Let’s now assume that we have a stored procedure usp_MyStoredProc that takes a single parameter @param1. If this is a require parameter, both of the following statement blocks will result in the same outcome:

public DataSet CallMyStoredProc( int value ){
return _db.ExecuteDataSet( “usp_MyStoredProc”, value )
}

public DataSet CallMyStoredProc( int value ){
DbCommand command = _db.GetStoredProcCommand(“usp_MyStoredProc” );
_db.AddInParameter( command, “param1”, value );
return _db.ExecuteDataSet( command );
}

However, now let’s assume that @param1 is an optional parameter that we don’t want to pass in from our data access method, we are limited in our options.

public DataSet CallMyStoredProc(){
return _db.ExecuteDataSet( “usp_MyStoredProc”)
}

This method will fail with an exception because the parameters provided to the stored procedure (none) don’t match the parameter count for the stored procedure (1).

Creating an actual DbCommand and executing it, however, will not cause an exception. In this scenario, the following code block will work without causing an exception:

public DataSet CallMyStoredProc( int value ){
DbCommand command = _db.GetStoredProcCommand(“usp_MyStoredProc” );
return _db.ExecuteDataSet( command );
}