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 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 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());
}
}
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 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 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> the id of the inserted object </returns>
public static int Insert(object o, string cs, string[] ignore = null)
{
ignore = ignore ?? new[] { "Id" };
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(ignore), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields(ignore).SetFormat("@{0}"), o)
+ ") select @@identity";
cmd.InjectFrom(new SetParamsValues().IgnoreFields(ignore), o);
conn.Open();
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
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));
}
public static int CountWhere <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 count(*) 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();
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 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 IEnumerable <T> GetWhereStartsWith <T>(string prop, string with, int max, string cs) where T : new()
{
using (var conn = new SqlConnection(cs))
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select top " + max + " * from "
+ TableConvention.Resolve(typeof(T)) + string.Format(" where {0} like '{1}%' ", prop, with);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var o = new T();
o.InjectFrom <ReaderInjection>(dr);
yield return(o);
}
}
}
}
}