I have a rather big application using dapper and Oracle. Now, I want to support SQL Server.
The problem is that all my queries are written with : and not with @, so SQL Server complains:
Incorrect syntax near ':'
Is it possible to tell Dapper that it should replace : with @ or do I have to do that in my own code?
I have found out that this is indeed impossible directly with dapper :
Sorry for the duplicates.
This is not built in, but you could provide it relatively simply by writing a custom extension method and doing whatever tweaks you need:
public static IEnumerable<T> MyMagicQuery<T>(this IDbConnection conn,
string query, object args = ...)
{
query = RunSomeRegexReplace(query);
return conn.Query<T>(query, args, ...);
}
However, while the regex for this is not particularly taxing, the bigger problem is that T/SQL and PL/SQL are both distinct variants of SQL. Many features will not work at all when given a simple direct translation. Other features may work, but require different syntax. A third group of features may work syntactically but give different results (yes, really), or have very different performance characteristics unless re-written to exploit RDBMS-specific preferences.
Fundamentally, changing between RDBMS is a lot more than just changing @ to :.
SqlConnectionorOracleConnection- I highly doubt it has any means of replacing parameter naming conventions. That would be something a full ORM like Entity Framework would probably handle for you