static string cs = SqlEasy.connString; //数据库连接字符串
public static IEnumerable <T> GetWhere <T>(object where, string orderby = "") 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) + (orderby != "" ? " order by " + orderby : "");
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 FID 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 virtual int Update(T o)
{
try
{
return(DbUtils.Update(o));
}
catch (Exception ex)
{
LogHelper.WriteLog(typeof(BaseRepository <T>), TableConvention.Resolve(o) + " - " + ex.Message + ex.ToString());
return(0);
}
}
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 FID=@FID";
cmd.InjectFrom <SetParamsValues>(new { FID = id });
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
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(FID AS varchar(50)) + ',',',' + @FID + ',') > 0";
cmd.InjectFrom <SetParamsValues>(new { FID = ids });
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 FID = @FID";
cmd.InjectFrom <SetParamsValues>(o);
conn.Open();
return(Convert.ToInt32(cmd.ExecuteNonQuery()));
}
}
public static int InsertEx(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(), o) + ") values("
.InjectFrom(new FieldsBy().SetFormat("@{0}"), o)
+ ")";
cmd.InjectFrom(new SetParamsValues(), 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("fid"), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields("fid").SetFormat("@{0}"), o) + ")";
cmd.InjectFrom <SetParamsValues>(o);
conn.Open();
return(cmd.ExecuteNonQuery());
}
}
public static int DeleteWhere <T>(object where)
{
using (var conn = new SqlConnection(cs))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "delete 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(cmd.ExecuteNonQuery());
}
}
//public static DataTable GetPage(string sql, int page, int pageSize, string filterJson, string sort, string order, out int recordCount)
//{
// using (var conn = new SqlConnection(cs))
// {
// using (var cmd = conn.CreateCommand())
// {
// cmd.CommandType = CommandType.Text;
// recordCount = 0;
// string strWhere = "where 1 = 1";
// if (!string.IsNullOrEmpty(filterJson))
// {
// strWhere += filterJson;
// }
// cmd.CommandText = string.Format(@"select count(*) AS Count from ({0} {1}) AS T1", sql, strWhere) ;
// LogHelper.WriteLog(cmd.CommandText);
// conn.Open();
// using (var da = new SqlDataAdapter(cmd))
// {
// DataSet ds = new DataSet();
// da.Fill(ds);
// if(ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
// {
// foreach (DataRow row in ds.Tables[0].Rows)
// {
// recordCount = row["Count"].ToInt();
// LogHelper.WriteLog("【配件总数】：" + recordCount);
// break;
// }
// }
// conn.Close();
// }
// cmd.CommandText = string.Format(@"with result as(select *, ROW_NUMBER() over(order by {3} {4}) nr
// from ({0})
// )
// select *
// from result
// where nr between (({1} - 1) * {2} + 1)
// and ({1} * {2}) ", sql, page, pageSize, sort, order);
// conn.Open();
// using (var da = new SqlDataAdapter(cmd))
// {
// DataSet ds = new DataSet();
// da.Fill(ds);
// conn.Close();
// return ds.Tables[0];
// }
// return null;
// }
// }
//}
public static T Get <T>(long FID) 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 FID = " + FID;
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 Insert(object o, string IgnoreFields)
{
string[] strarr = { };
if (!string.IsNullOrEmpty(IgnoreFields))
{
strarr = IgnoreFields.Split(',');
}
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(strarr), o) + ") values("
.InjectFrom(new FieldsBy().IgnoreFields(strarr).SetFormat("@{0}"), o)
+ ") ";
cmd.InjectFrom(new SetParamsValues().IgnoreFields(strarr), o);
conn.Open();
return(Convert.ToInt32(cmd.ExecuteNonQuery()));
}
}
