generic repository pattern c# entity framework core execute mysql storeprocedure 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();
}
}
}