how to call stored procedure from database independent application c# , codefirst

Question

i created procedure for each db types that supported by my application. and added into their migration files.

i can call stored procedure MSSQL like this two type in my code first app one

worker.StoredProcedures.ExecuteWithStoreProcedure("sp_userVirman @ResourceUserID,@targetUserID", 
new SqlParameter("ResourceUserID",DbType.Int64) { Value = 1 },
 new SqlParameter("targetUserID", DbType.Int64) { Value = 2 });

two

worker.StoredProcedures.ExecuteWithStoreProcedure(string.Format("sp_userVirman {0},{1}", 1, 2));

but when the db provider change to mysql, it gives error.

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in EntityFramework.dll Additional information: Only MySqlParameter objects may be stored

and also providers can change oracle postgresql mysql etc.

how can solve this problem?

i dont want to use if provider== mssql if provider==mysql etc...

this is my main function

 public void ExecuteWithStoreProcedure(string query, params object[] parameters)
    {
        _dbContext.Database.ExecuteSqlCommand(query, parameters);
    }

score 1 · Accepted Answer · answered Jan 11 '17 at 06:17

this is the best use I can find. there is no way without switch case or if else

public void sp_uservirman(Nullable<int> resourceUserID, Nullable<int> targetUserID)
    {
        switch (GlobalData.CustomerDataSourceType)
        {
            case ContextFactory.DataSourceTypes.None:
                break;
            case ContextFactory.DataSourceTypes.MSSQL:
                SqlParameter param= new SqlParameter("@resourceuserıd",resourceUserID);
                SqlParameter param1= new SqlParameter("@targetUserID",targetUserID);
                _dbContext.Database.ExecuteSqlCommand("sp_uservirman  @resourceuserıd,@targetUserID", param, param1);
                break;
            case ContextFactory.DataSourceTypes.MySQL:

                MySqlParameter param3 = new MySqlParameter("@resourceuserıd", resourceUserID);
                MySqlParameter param4 = new MySqlParameter("@targetUserID", targetUserID);


                _dbContext.Database.ExecuteSqlCommand("CALL sp_uservirman  (@resourceuserıd,@targetUserID)", param3, param4);
                break;
            case ContextFactory.DataSourceTypes.ORACLE:

                string query = string.Format("BEGIN SP_USERVIRMAN ({0},{1}) ; END;", resourceUserID, targetUserID);
                _dbContext.Database.ExecuteSqlCommand(query);

                break;
            default:
                break;
        }

    }

Usage:

 worker.StoredProcedures.sp_uservirman(1, 2);

how to call stored procedure from database independent application c# , codefirst

1 Answers1