basically the most common operations are covered by the Repo<T>, the rest you can do via stored procedures and calling them and fetching the result is going to be really easy with the help of DbUtil class
also when I need to save a entity togheter with a list of children or something similar I use:
using(var scope = new TransactionScope())
{
// save/delete/update lots of stuff
scope.Complete();
}
The Generic Repository
usually I create one generic Repo<T> with the basic operations and inherit it after for specific stuff e.g. FooRepo : Repo<Foo>
public class Repo<T> : IRepo<T> where T : new()
{
protected string Cs;
public Repo(IConnectionFactory connectionFactory)
{
Cs = connectionFactory.GetConnectionString();
}
public T Get(int id)
{
return DbUtil.Get<T>(id, Cs);
}
public IEnumerable<T> GetAll()
{
return DbUtil.GetAll<T>(Cs);
}
//returns the new autogenerated id
public virtual int Insert(T o)
{
return DbUtil.Insert(o, Cs);
}
public virtual int Update(T o)
{
return DbUtil.Update(o, Cs);
}
//example repo.UpdateWhatWhere( new{ FirstName = "Danny", LastName = "Ocean"}, new{ NickName = "The Idea Man"} );
public virtual int UpdateWhatWhere(object what, object where)
{
return DbUtil.UpdateWhatWhere<T>(what, where, Cs);
}
//returns rows affected
public virtual int InsertNoIdentity(T o)
{
return DbUtil.InsertNoIdentity(o, Cs);
}
public IEnumerable<T> GetPage(int page, int pageSize)
{
return DbUtil.GetPage<T>(page, pageSize, Cs);
}
public int Count()
{
return DbUtil.Count<T>(Cs);
}
public IPageable<T> GetPageable(int page, int pageSize)
{
return new Pageable<T>
{
Page = GetPage(page, pageSize),
PageCount = DbUtil.GetPageCount(pageSize, Count()),
PageIndex = page,
};
}
// example: repo.GetWhere(new { FirstName ="jhon", LastName="philips"});
public IEnumerable<T> GetWhere(object where)
{
return DbUtil.GetWhere<T>(where, Cs);
}
public int Delete(int id)
{
return DbUtil.Delete<T>(id, Cs);
}
}
Example of executing stored procedures
public int ChangeState(int id, int stateId)
{
return DbUtil.ExecuteNonQuerySp("changeDossierState", Cs, new { id, stateId });
}
public IEnumerable<Dossier> GetBy(int measuresetId, int measureId, DateTime month)
{
return DbUtil.ExecuteReaderSp<Dossier>("getDossiers", Cs, new { measuresetId, measureId, month });
}
Db utilities
public static class DbUtil
{
public static IEnumerable<T> GetWhere<T>(object where, string cs) where T : new()
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where "
.InjectFrom(new FieldsBy()
.SetFormat("{0}=@{0}")
.SetNullFormat("{0} is null")
.SetGlue("and"),
where);
cmd.InjectFrom<SetParamsValues>(where);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var o = new T();
o.InjectFrom<ReaderInjection>(dr);
yield return o;
}
}
}
}
}
public static int Delete<T>(int id, string cs)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where id=" + id;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
///<returns> the id of the inserted object </returns>
public static int Insert(object o, string cs)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
.InjectFrom(new FieldsBy().IgnoreFields("Id"), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields("Id").SetFormat("@{0}"), o)
+ ") select @@identity";
cmd.InjectFrom(new SetParamsValues().IgnoreFields("Id"), o);
conn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static int Update(object o, string cs)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update " + TableConvention.Resolve(o) + " set "
.InjectFrom(new FieldsBy().IgnoreFields("Id").SetFormat("{0}=@{0}"), o)
+ " where Id = @Id";
cmd.InjectFrom<SetParamsValues>(o);
conn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static int UpdateWhatWhere<T>(object what, object where, string cs)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update " + TableConvention.Resolve(typeof(T)) + " set "
.InjectFrom(new FieldsBy().SetFormat("{0}=@{0}"), what)
+ " where "
.InjectFrom(new FieldsBy()
.SetFormat("{0}=@wp{0}")
.SetNullFormat("{0} is null")
.SetGlue("and"),
where);
cmd.InjectFrom<SetParamsValues>(what);
cmd.InjectFrom(new SetParamsValues().Prefix("wp"), where);
conn.Open();
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
public static int InsertNoIdentity(object o, string cs)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert " + TableConvention.Resolve(o) + " ("
.InjectFrom(new FieldsBy().IgnoreFields("Id"), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields("Id").SetFormat("@{0}"), o) + ")";
cmd.InjectFrom<SetParamsValues>(o);
conn.Open();
return cmd.ExecuteNonQuery();
}
}
/// <returns>rows affected</returns>
public static int ExecuteNonQuerySp(string sp, string cs, object parameters)
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.InjectFrom<SetParamsValues>(parameters);
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static IEnumerable<T> ExecuteReaderSp<T>(string sp, string cs, object parameters) where T : new()
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.InjectFrom<SetParamsValues>(parameters);
conn.Open();
using (var dr = cmd.ExecuteReader())
while (dr.Read())
{
var o = new T();
o.InjectFrom<ReaderInjection>(dr);
yield return o;
}
}
}
}
public static IEnumerable<T> ExecuteReaderSpValueType<T>(string sp, string cs, object parameters)
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.InjectFrom<SetParamsValues>(parameters);
conn.Open();
using (var dr = cmd.ExecuteReader())
while (dr.Read())
{
yield return (T)dr.GetValue(0);
}
}
}
}
public static int Count<T>(string cs)
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from " + TableConvention.Resolve(typeof(T));
conn.Open();
return (int)cmd.ExecuteScalar();
}
}
}
public static IEnumerable<T> GetAll<T>(string cs) where T : new()
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T));
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var o = new T();
o.InjectFrom<ReaderInjection>(dr);
yield return o;
}
}
}
}
}
public static IEnumerable<T> GetPage<T>(int page, int pageSize, string cs) where T : new()
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
var name = TableConvention.Resolve(typeof(T));
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(@"with result as(select *, ROW_NUMBER() over(order by id desc) nr
from {0}
)
select *
from result
where nr between (({1} - 1) * {2} + 1)
and ({1} * {2}) ", name, page, pageSize);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var o = new T();
o.InjectFrom<ReaderInjection>(dr);
yield return o;
}
}
}
}
}
public static T Get<T>(long id, string cs) where T : new()
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from " + TableConvention.Resolve(typeof(T)) + " where id = " + id;
conn.Open();
using (var dr = cmd.ExecuteReader())
while (dr.Read())
{
var o = new T();
o.InjectFrom<ReaderInjection>(dr);
return o;
}
}
return default(T);
}
}
the injections used
public class ReaderInjection : KnownSourceValueInjection<IDataReader>
{
protected override void Inject(IDataReader source, object target)
{
for (var i = 0; i < source.FieldCount; i++)
{
var activeTarget = target.GetProps().GetByName(source.GetName(i), true);
if (activeTarget == null) continue;
var value = source.GetValue(i);
if (value == DBNull.Value) continue;
activeTarget.SetValue(target, value);
}
}
}
public class FieldsBy : KnownTargetValueInjection<string>
{
private IEnumerable<string> ignoredFields = new string[] { };
private string format = "{0}";
private string nullFormat;
private string glue = ",";
public FieldsBy SetGlue(string g)
{
glue = " " + g + " ";
return this;
}
public FieldsBy IgnoreFields(params string[] fields)
{
ignoredFields = fields;
return this;
}
public FieldsBy SetFormat(string f)
{
format = f;
return this;
}
public FieldsBy SetNullFormat(string f)
{
nullFormat = f;
return this;
}
protected override void Inject(object source, ref string target)
{
var sourceProps = source.GetProps();
var s = string.Empty;
for (var i = 0; i < sourceProps.Count; i++)
{
var prop = sourceProps[i];
if (ignoredFields.Contains(prop.Name)) continue;
if (prop.GetValue(source) == DBNull.Value && nullFormat != null)
s += string.Format(nullFormat, prop.Name);
else
s += string.Format(format, prop.Name) + glue;
}
s = s.RemoveSuffix(glue);
target += s;
}
}
public class SetParamsValues : KnownTargetValueInjection<SqlCommand>
{
private IEnumerable<string> ignoredFields = new string[] { };
private string prefix = string.Empty;
public SetParamsValues Prefix(string p)
{
prefix = p;
return this;
}
public SetParamsValues IgnoreFields(params string[] fields)
{
ignoredFields = fields.AsEnumerable();
return this;
}
protected override void Inject(object source, ref SqlCommand cmd)
{
if (source == null) return;
var sourceProps = source.GetProps();
for (var i = 0; i < sourceProps.Count; i++)
{
var prop = sourceProps[i];
if (ignoredFields.Contains(prop.Name)) continue;
var value = prop.GetValue(source) ?? DBNull.Value;
cmd.Parameters.AddWithValue("@" + prefix + prop.Name, value);
}
}
}
Misc
public interface IPageableInfo
{
int PageCount { get; set; }
int PageIndex { get; set; }
}
public interface IPageable<T> : IPageableInfo
{
IEnumerable<T> Page { get; set; }
}
public class Pageable<T> : IPageable<T>
{
public int PageCount { get; set; }
public IEnumerable<T> Page { get; set; }
public int PageIndex { get; set; }
}
public static class Tools
{
public static int GetPageCount(int pageSize, int count)
{
var pages = count / pageSize;
if (count % pageSize > 0) pages++;
return pages;
}
}
public static class TableConvention
{
public static string Resolve(Type type)
{
return type.Name;
}
}
TODO: explain