Is there a database agnostic way to execute a stored procedure with params

1

Using Entity Framework Core 2.1 I have code that calls a stored procedure, passing in some params. During execution we are using Sql Server and everything works as expected. Our test cases however, run using Sqlite. Is there a decent way (without overly polluting the code with unwanted code added only to support testing) to get the code to run regardless of which database is actually being used?

Currently the code in question looks like this:

await MyContext.Database.ExecuteSqlCommandAsync("UpdateEndDate @p0, @p1, @p2", id, from, thru);

And causes a SqliteException:

Microsoft.Data.Sqlite.SqliteException
  HResult=0x80004005
  Message=SQLite Error 1: 'near "UpdateEndDate": syntax error'.
  Source=Microsoft.Data.Sqlite
  ...

(Also wondering why are there SqlParameter and SQLiteParameter? Why cant there be a database agnostic parameter class?)

Configuration for DBContext in test harness is below:

public static SqliteConnection GetConnection()
{
    SqliteConnection connection = new SqliteConnection("DataSource=:memory:");
    connection.Open();

    connection.CreateFunction(
    "NEWID",
    Guid.NewGuid(),
    g => g);

    return connection;
}

serviceProvider = new ServiceCollection()
    .<snipped>
    .AddEntityFrameworkSqlite()
    .AddDbContext<MyContext>(
    (theServiceProvider, opt) => opt
    .UseInternalServiceProvider(theServiceProvider)
    .UseSqlite(GetConnection()))
    .BuildServiceProvider();

MyContext cc = serviceProvider.GetService(typeof(MyContext)) as MyContext;
cc.Database.EnsureCreated();
entity-framework-core
asked on Stack Overflow Aug 28, 2018 by Stephen • edited Nov 16, 2018 by Stephen

1 Answer

0

Have you considered using the DbProviderFactory class? It became available in Core 2.1. Put both connection strings in your appSettings.json and in the startup set appropriate DbContext based on the environment it's running in.

https://docs.microsoft.com/en-us/dotnet/api/system.data.common.dbproviderfactory?view=netcore-2.1

answered on Stack Overflow Mar 25, 2019 by ingep

User contributions licensed under CC BY-SA 3.0