Skip to content

Instantly share code, notes, and snippets.

@abhilashca
Last active May 20, 2019 15:56
Show Gist options
  • Save abhilashca/6d5d73c6803789a5572d750e9b47a06a to your computer and use it in GitHub Desktop.
Save abhilashca/6d5d73c6803789a5572d750e9b47a06a to your computer and use it in GitHub Desktop.
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