static string cs = SqlEasy.connString; //数据库连接字符串
public static IEnumerable <T> GetWhere <T>(object where) 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) 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 keyid 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>(string ids)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where charindex(',' + cast(keyid AS varchar(50)) + ',',',' + @KeyID + ',') > 0";
cmd.InjectFrom <SetParamsValues>(new { KeyID = ids });
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
public static int Delete <T>(int id)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete from " + TableConvention.Resolve(typeof(T)) + " where KeyID=@KeyID";
cmd.InjectFrom <SetParamsValues>(new { KeyID = id });
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
public static int Count <T>()
{
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, params string[] fields)
{
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(fields).SetFormat("{0}=@{0}"), o)
+ " where KeyID = @KeyID";
cmd.InjectFrom <SetParamsValues>(o);
conn.Open();
return(Convert.ToInt32(cmd.ExecuteNonQuery()));
}
}
///<returns> the id of the inserted object </returns>
public static int Insert(object o)
{
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("jblpid"), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields("jblpid").SetFormat("@{0}"), o)
+ ") select @@identity";
cmd.InjectFrom(new SetParamsValues().IgnoreFields("jblpid"), o);
conn.Open();
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
public static int InsertNoIdentity(object o)
{
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("keyid"), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields("keyid").SetFormat("@{0}"), o) + ")";
cmd.InjectFrom <SetParamsValues>(o);
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
public static T Get <T>(long keyid) 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 keyid = " + keyid;
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) 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>() 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)
{
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(cmd.ExecuteNonQuery());
}
}