Exception on SQL Server Compact 4.0

[feinstein]

I get an exception saying : "Invalid set option. [ Set option = nocount ]" when I try to run the enumToLookup.Apply(context);

[sveinhelge]

SQL CE only accept one SQL statement for each ExecuteSqlCommand. So we would need to loop a set of statements(One call to ExecuteSqlCommand for every line/statement). SQL CE does not support nocount, xact_abort, begin, end.

We would need to create a new SqlCompactServerHandler that implements IDbHandler. But since SQL CE only support one statement for each call we need to change the apply method. May be having an option that says call execute SQL command for each line.

I would change this from bug to improvement

[timabell]

I got as far as putting all the SQL behind an interface so we can swap out implementations for different RDBMs but I haven't had time to work out what we're connected to.

[sveinhelge]

You could use:

context.Database.Connection is SqlCeConnection;

If true it's an SQL CE.

Or get the provider name:
context.Database.Connection.ToString();

[sveinhelge]

In the custom schema branch I added an new extension for DbContext.

https://github.com/sveinhelge/ef-enum-to-lookup/blob/custom-schema/EfEnumToLookup/LookupGenerator/Extensions/ContextExtensions.cs

We could add these methods to that extension.

public static string GetProviderName(this DbContext context)
{
return context.Database.Connection.ToString();
}

public static string GetServerVersion(this DbContext context)
{
return context.Database.Connection.ServerVersion;
}

public static bool IsSqlCe(this DbContext context)
{
return context.Database.Connection is SqlCeConnection;
}

This way we get the name of the provider, database version and if this is an SQL CE.

Database version could be used to decide if we should use table variable instead of #temp-table like mentioned in issue #23