/// <summary>
/// 根据传入的条件执行查询，并返回一个IEnumerable<T>类型的集合
/// （注意传入的 T 必须约束为 where T : class, new()）
/// </summary>
/// <typeparam name="T">类型：【 约束为 where T : class, new() 】</typeparam>
/// <param name="where">查询的条件，请省略 Where 关键字</param>
/// <returns></returns>
public IEnumerable <T> GetEntityList(string where)
{
Type type = typeof(T);
//遍历获得字段
string sql = string.Format("SELECT {0} FROM [{1}] WHere {2} ",
SenctionHelper.GetModelSenction <T>(),
type.Name,
where);
return(SenctionHelper.Excute <IEnumerable <T> >(sql, s =>
{
List <T> tList = new List <T>();
using (SqlDataReader reader = s.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
tList.Add(SenctionHelper.MapEntity <T>(reader));
}
}
else
{
return default(List <T>);
}
}
return tList;
}));
}
/// <summary>
/// 获得单个实体
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public T GetEntity(int ID)
{
Type type = typeof(T);
string where = type.GetProperties().Where(w => w.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || w.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(p => string.Format("[{0}]=@{1}", p.Name, p.Name)).FirstOrDefault();
SqlParameter[] sqlParameters = type.GetProperties().Where(w => w.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || w.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(p => new SqlParameter(string.Format("@{0}", p.Name), ID)).ToArray();
string sql = $" SELECT {SenctionHelper.GetModelSenction<T>()} from [{type.Name}] where {where} ";
return(SenctionHelper.Excute <T>(sql, s =>
{
using (SqlDataReader reder = s.ExecuteReader())
{
if (reder.HasRows)
{
if (reder.Read())
{
return SenctionHelper.MapEntity <T>(reder);
}
else
{
return default(T);
}
}
else
{
return default(T);
}
}
}, sqlParameters));
#region old
//try
//{
// using (SqlDataReader reader = SenctionHelper.ExecuteReader(sql))
// {
// if (reader.HasRows)
// {
// reader.Read();
// return SenctionHelper.MapEntity<T>(reader);
// }
// else
// {
// return default(T);
// }
// }
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
// return default(T);
//}
#endregion
}
public IEnumerable <Company> GetList()
{
string sql = "select * from Company ";
using (SqlDataReader reader = SenctionHelper.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
yield return(SenctionHelper.MapEntity <Company>(reader));
}
}
}
}
/// <summary>
/// 根据Model更新一条数据
/// </summary>
/// <param name="Entity"></param>
/// <returns></returns>
public bool Update(T Entity)
{
string sqlText = "UPDATE [{0}] SET {1} where {2}";
Type type = typeof(T);
object oT = Activator.CreateInstance(Entity.GetType());
#region old
//var oEntity = Entity.GetType().GetProperties();//
//StringBuilder builder = new StringBuilder(256);
//StringBuilder builderWhere = new StringBuilder(256);
//foreach (var item in oEntity)
//{
// //字段和值得拼接
// if (!item.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) && !item.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase))
// {
// builder.Append(item.Name + "=" + item.GetValue(oT));
// }
// //获取ID条件
// if (item.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase))
// {
// builderWhere.Clear();
// builderWhere.Append(item.Name + "=" + item.GetValue(oT));
// }
//}
//string execSql = $" update {type.Name} {builder.ToString()} where {builderWhere.ToString()}";
#endregion
//
string columString = string.Join(",", type.GetProperties().Where(item => !item.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) && !item.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(s => string.Format("{0}=@{1}", s.Name, s.Name)));
var where = type.GetProperties().Where(p => p.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || p.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(s => string.Format("[{0}]={1}", s.Name, s.GetValue(Entity))).FirstOrDefault();
sqlText = string.Format(sqlText, type.Name, columString, where);
SqlParameter[] sqlParameters = type.GetProperties().Where(item => !item.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) && !item.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(s => new SqlParameter(string.Format("@{0}", s.Name), s.GetValue(Entity) ?? DBNull.Value)).ToArray();
return(SenctionHelper.Excute <bool>(sqlText, t =>
{
return t.ExecuteNonQuery() > 0;
}, sqlParameters));
// return true;//SenctionHelper.ExecuteNonQuery(sqlText, sqlParameters) > 0;//old
}
/// <summary>
/// 根据ID删除一条数据
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public bool Del(int ID)
{
Type type = typeof(T);
string where = type.GetProperties().Where(w => w.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || w.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(p => string.Format("[{0}]=@{1}", p.Name, p.Name)).FirstOrDefault();
SqlParameter[] sqlParameters = type.GetProperties().Where(w => w.Name.Equals("ID", StringComparison.InvariantCultureIgnoreCase) || w.Name.Equals("FID", StringComparison.InvariantCultureIgnoreCase)).Select(p => new SqlParameter(string.Format("@{0}", p.Name), ID)).ToArray();
string execSql = $" DELETE FROM [{type.Name}] where {where} ";
return(SenctionHelper.Excute <bool>(execSql, t =>
{
return t.ExecuteNonQuery() > 0;
}, sqlParameters));
// return SenctionHelper.ExecuteNonQuery(execSql) > 0;//old
}
/// <summary>
/// 根据传入的SQL语句执行查询，并返回一个IEnumable<T>类型的集合
/// 注意 W 必须约束为 where W : class, new()）
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public IEnumerable <W> GetEntityListSQl <W>(string sql)
{
IList <W> list = new List <W>();
return(SenctionHelper.Excute <IList <W> >(sql, s =>
{
using (SqlDataReader reader = s.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
list.Add(SenctionHelper.MapEntity <W>(reader));
}
}
else
{
return default(List <W>);
}
}
return list;
}));
}
/// <summary>
/// 执行一个插入操作
/// </summary>
/// <typeparam name="W"></typeparam>
/// <param name="Entity"></param>
/// <returns></returns>
public bool ADD(T Entity)
{
string sqlText = "Insert into [{0}] ({1}) values({2}) ";
var type = typeof(T);
object oT = Activator.CreateInstance(type);
//PropertyInfo[] propertys = Entity.GetType().GetProperties();
//获取字段
var columString = string.Join(",", type.GetProperties().Where(w => !w.Name.Equals("ID", StringComparison.CurrentCultureIgnoreCase) && !w.Name.Equals("FID", StringComparison.CurrentCultureIgnoreCase)).Select(s => s.Name));
//参数化
var valuePars = string.Join(",", type.GetProperties().Where(w => !w.Name.Equals("ID", StringComparison.CurrentCultureIgnoreCase) && !w.Name.Equals("FID", StringComparison.CurrentCultureIgnoreCase)).Select(s => string.Format("@{0}", s.Name)));
SqlParameter[] sqlParameters = type.GetProperties().Where(w => !w.Name.Equals("ID", StringComparison.CurrentCultureIgnoreCase) && !w.Name.Equals("FID", StringComparison.CurrentCultureIgnoreCase)).Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(Entity) ?? DBNull.Value)).ToArray();
sqlText = string.Format(sqlText, type.Name, columString, valuePars);
return(SenctionHelper.Excute <bool>(sqlText, s =>
{
return s.ExecuteNonQuery() > 0;
}, sqlParameters));
//return SenctionHelper.ExecuteNonQuery(sqlText, sqlParameters) > 0;
}
public List <User> Test()
{
return(SenctionHelper.Excute <List <User> >("Select * From [User]", u =>
{
List <User> userList = new List <User>();
using (SqlDataReader reader = u.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
userList.Add(SenctionHelper.MapEntity <User>(reader));
}
}
else
{
return default(List <User>);
}
}
return userList;
}));
}
