entity framework repostiory stored procedure code example

Example: generic repository pattern c# entity framework core execute mysql storeprocedure

public class Repository : IRepository where T : class 
    {
        public DbContext context;
        public DbSet dbSet;

        public Repository(DbContext context)
        {
            this.context = context;
            dbSet = context.Set();
        }

        public ICollection ExcuteSqlQuery(string sqlQuery, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (commandType == CommandType.Text)
            {
                return SqlQuery(sqlQuery, parameters);
            }
            else if (commandType == CommandType.StoredProcedure)
            {
                return StoredProcedure(sqlQuery, parameters);
            }

            return null;
        }

        public void ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (context.Database.Connection.State == ConnectionState.Closed)
            {
                context.Database.Connection.Open();
            }

            var command = context.Database.Connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            command.ExecuteNonQuery();
        }

        public ICollection ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            if (context.Database.Connection.State == ConnectionState.Closed)
            {
                context.Database.Connection.Open();
            }

            var command = context.Database.Connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }

            using (var reader = command.ExecuteReader())
            {
                var mapper = new DataReaderMapper();
                return mapper.MapToList(reader);
            }
        }

        private ICollection SqlQuery(string sqlQuery, SqlParameter[] parameters = null)
        {
            if (parameters != null && parameters.Any())
            {
                var parameterNames = new string[parameters.Length];
                for (int i = 0; i < parameters.Length; i++)
                {
                    parameterNames[i] = parameters[i].ParameterName;
                }

                var result = context.Database.SqlQuery(string.Format("{0}", sqlQuery, string.Join(",", parameterNames), parameters));
                return result.ToList();
            }
            else
            {
                var result = context.Database.SqlQuery(sqlQuery);
                return result.ToList();
            }
        }

        private ICollection StoredProcedure(string storedProcedureName, SqlParameter[] parameters = null)
        {
            if (parameters != null && parameters.Any())
            {
                var parameterNames = new string[parameters.Length];
                for (int i = 0; i < parameters.Length; i++)
                {
                    parameterNames[i] = parameters[i].ParameterName;
                }

                var result = context.Database.SqlQuery(string.Format("EXEC {0} {1}", storedProcedureName, string.Join(",", parameterNames), parameters));
                return result.ToList();
            }
            else
            {
                var result = context.Database.SqlQuery(string.Format("EXEC {0}", storedProcedureName));
                return result.ToList();
            }
        }
    }