Skip to content

Instantly share code, notes, and snippets.

@gazlu
Created September 4, 2015 05:12
Show Gist options
  • Save gazlu/09f162f42fa5e13f0506 to your computer and use it in GitHub Desktop.
Save gazlu/09f162f42fa5e13f0506 to your computer and use it in GitHub Desktop.
Call Stored Procedure with DbContext without including in EDMX
public class DataRepository<T> : IDataRepository<T>
where T : class, new()
{
private DbContext context;
Logger log = new Logger();
public DataRepository()
{
}
public IList<dynamic> ReadSP(string sp, IDictionary<string, object> parameters)
{
IList<dynamic> result = new List<dynamic>();
try
{
var keys = from parameter in parameters
select "@" + parameter.Key;
var values = from parameter in parameters
select new SqlParameter(parameter.Key, parameter.Value);
string paramNames = string.Join(",", keys.ToArray());
using (var context = new EmployeeHubEntities())
{
var resultQuery = context.Database.SqlQuery<T>(
sp + " " + paramNames,
values.ToArray()
);
result = resultQuery.ToList<dynamic>();
}
}
catch (Exception ex)
{
var dbException = new DatabaseException();
dbException.DatabaseMethod = "ReadSP";
dbException.ActualException = ex;
throw dbException;
}
return result;
}
public List<TReturn> ExecuteStoreProcedure<TReturn>(string sp, IDictionary<string, object> parameters)
{
List<TReturn> result = new List<TReturn>();
try
{
var keys = from parameter in parameters
select "@" + parameter.Key;
var values = from parameter in parameters
select new SqlParameter(parameter.Key, parameter.Value);
string paramNames = string.Join(",", keys.ToArray());
using (var context = new EmployeeHubEntities())
{
var resultQuery = context.Database.SqlQuery<TReturn>(
sp + " " + paramNames,
values.ToArray()
);
result = resultQuery.ToList<TReturn>();
}
}
catch (Exception ex)
{
string Excp = ex.StackTrace;
var dbException = new DatabaseException();
dbException.DatabaseMethod = "ExecuteStoreProcedure<TReturn>";
dbException.ActualException = ex;
int a = ex.LineNumber();
log.LogFile(ex.Message, ex.StackTrace, a);
}
return result;
}
public int UpdateSP(string sp, IDictionary<string, object> parameters)
{
try
{
var keys = from parameter in parameters
select "@" + parameter.Key;
var values = from parameter in parameters
select new SqlParameter(parameter.Key, parameter.Value);
string paramNames = string.Join(",", keys.ToArray());
using (var context = new EmployeeHubEntities())
{
return context.Database.ExecuteSqlCommand(
sp + " " + parameters,
values.ToArray()
);
}
}
catch (Exception ex)
{
var dbException = new DatabaseException();
dbException.DatabaseMethod = "UpdateSP";
dbException.ActualException = ex;
throw dbException;
}
}
/// <summary>
///
/// </summary>
public void Dispose()
{
if (context != null)
{
context.Dispose();
context = null;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment