Created
September 4, 2015 05:12
-
-
Save gazlu/09f162f42fa5e13f0506 to your computer and use it in GitHub Desktop.
Call Stored Procedure with DbContext without including in EDMX
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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