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();
}
}
}
Are there any code examples left?
New code examples in category SQL
-
SQL 2022-03-27 22:45:25 how to get mysql on ubuntu
-
SQL 2022-03-27 20:00:47 install latest mysql on debian 9
-
SQL 2022-03-27 19:45:11 sql '=' cannot be applied to date varchar(10) athena
-
SQL 2022-03-27 19:20:41 sql is not null
-
SQL 2022-03-27 19:20:39 sql group by error
-
SQL 2022-03-27 18:35:10 replace null with 0 in sql
-
SQL 2022-03-27 17:30:32 concat column value of same user in mysql
-
SQL 2022-03-27 17:05:02 sql developer sql worksheet not showing
-
SQL 2022-03-27 16:45:07 kill a pid redshift
-
SQL 2022-03-27 16:00:09 joins in sql server