A simple helper class to address most of the database operations and use cases using Dapper
using Dapper; | |
using System; | |
using System.Collections.Generic; | |
using System.Data; | |
using System.Data.SqlClient; | |
using static Dapper.SqlMapper; | |
using System.Linq; | |
namespace YourProject.Helpers | |
{ | |
public class DbHelper | |
{ | |
public static IEnumerable<T> ExecuteSP<T>(string sprocName, object sprocParams = null) | |
{ | |
IEnumerable<T> data = Activator.CreateInstance<List<T>>(); | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
data = sc.Query<T>(sprocName, param: sprocParams, commandType: CommandType.StoredProcedure); | |
} | |
return data; | |
} | |
public static IEnumerable<dynamic> ExecuteSP(string sprocName, object sprocParams = null) | |
{ | |
IEnumerable<dynamic> data = System.Linq.Enumerable.Empty<dynamic>(); | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
data = sc.Query(sprocName, param: sprocParams, commandType: CommandType.StoredProcedure); | |
} | |
return data; | |
} | |
public static int UpdateSP(string sprocName, object sprocParams = null, bool hasReturnValue = true) | |
{ | |
int rowsAffected = 0; | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
var p = new DynamicParameters(); | |
if (sprocParams != null) | |
p.AddDynamicParams(sprocParams); | |
if (hasReturnValue) | |
p.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); | |
sc.Execute(sprocName, param: p, commandType: CommandType.StoredProcedure); | |
if (hasReturnValue) | |
rowsAffected = p.Get<int>("@ReturnValue"); | |
} | |
return rowsAffected; | |
} | |
public static T InsertSP<T>(string sprocName, DynamicParameters sprocParams = null, string outputParamName = "Id") | |
{ | |
var outputValue = default(T); | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
sc.Execute(sprocName, param: sprocParams, commandType: CommandType.StoredProcedure); | |
} | |
outputValue = sprocParams.Get<T>(outputParamName); | |
return outputValue; | |
} | |
public static int DeleteSP(string sprocName, object sprocParams = null) | |
{ | |
int rowsAffected = 0; | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
var p = new DynamicParameters(); | |
if (sprocParams != null) | |
p.AddDynamicParams(sprocParams); | |
p.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); | |
sc.Execute(sprocName, param: p, commandType: CommandType.StoredProcedure); | |
rowsAffected = p.Get<int>("@ReturnValue"); | |
} | |
return rowsAffected; | |
} | |
public static IEnumerable<dynamic> ExecuteQuery(string sql) | |
{ | |
IEnumerable<dynamic> data = System.Linq.Enumerable.Empty<dynamic>(); | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
data = sc.Query(sql); | |
} | |
return data; | |
} | |
public static IEnumerable<T> ExecuteQuery<T>(string sql) | |
{ | |
IEnumerable<T> data = System.Linq.Enumerable.Empty<T>(); | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
data = sc.Query<T>(sql); | |
} | |
return data; | |
} | |
public static void ExecuteMultipleQuery(string sql, Action<GridReader> readerBlock) | |
{ | |
using (var sc = new SqlConnection("ConnectionString-Here")) | |
{ | |
sc.Open(); | |
GridReader reader = sc.QueryMultiple(sql); | |
readerBlock?.Invoke(reader); | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment