/// <summary>
/// 根据语句，返回list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="args"></param>
/// <returns></returns>
public IEnumerable <T> Query <T>(string sql, params object[] args) where T : class
{
using (var db = new LcLib.DBLib.DBHelper())
{
return(ConvertFun.ConvertToIEnumerable <T>(db.ExecuteDataTable(sql, args)));
}
}
/// <summary>
/// 分页查询，itemsPerPage=0时查询全部
/// </summary>
/// <param name="sql"></param>
/// <param name="itemsPerPage">每页条数</param>
/// <param name="currentPage">当前页数</param>
/// <param name="args">查询参数</param>
/// <returns></returns>
public TablePage QueryTablePage(string sql, int itemsPerPage = 20, int currentPage = 1, params object[] args)
{
using (var db = new LcLib.DBLib.DBHelper())
{
TablePage page = new TablePage()
{
CurrentPage = currentPage, PerPageItems = itemsPerPage
};
BuilderPageSql(sql, itemsPerPage, currentPage, db.dbType);
if (itemsPerPage == 0)
{
page.Items = db.ExecuteDataTable(_sqlPage, args);
page.TotalItems = page.Items.Rows.Count;
page.TotalPages = 1;
}
else
{
int itemcount = db.ExecuteScalar <int>(_sqlCount, args);
page.TotalItems = itemcount;
page.TotalPages = (itemcount % itemsPerPage == 0 ? itemcount / itemsPerPage : itemcount / itemsPerPage + 1);
if (itemcount == 0)
{
page.Items = new DataTable();
}
else
{
page.Items = db.ExecuteDataTable(_sqlPage, args);
}
}
return(page);
}
}
/// <summary>
/// 对单表进行Insert操作
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <param name="model">实体参数</param>
/// <param name="tran">事务</param>
/// <returns>返回影响的行数</returns>
public int Insert <T>(T model) where T : class
{
string sql = string.Empty;
string tableName = model.GetType().Name;
object[] customAttribute = null;
object[] tableAttribute = null;
Type t = model.GetType();
tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
if (tableAttribute != null && tableAttribute.Length > 0)
{
tableName = (tableAttribute[0] as TableAttribute).TableName;
}
sql = "insert into " + tableName;
StringBuilder fieldList = new StringBuilder();
StringBuilder valueList = new StringBuilder();
PropertyInfo[] pis = t.GetProperties();
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(model, null) == null)//如果属性值为null 不插入
{
continue;
}
customAttribute = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
if (customAttribute.Count() == 1 && (customAttribute[0] as ColumnAttribute).NotCreateWhere)
{
continue;
}
if (customAttribute.Count() == 0 || (customAttribute.Count() == 1 && !(customAttribute[0] as ColumnAttribute).Identity))
{
fieldList.AppendFormat(",{0}", pi.Name);
valueList.AppendFormat(",@{0}", pi.Name);
}
}
if (fieldList.Length == 0 || valueList.Length == 0)
{
throw new ArgumentException();
}
sql = string.Format("insert into {0}({1}) values({2})", tableName, fieldList.Remove(0, 1), valueList.Remove(0, 1));
using (var db = new LcLib.DBLib.DBHelper())
{
return(db.ExecuteNonQuery(sql, model));
}
}
/// <summary>
/// 对单表进行Update操作
/// </summary>
/// <typeparam name="T">泛型T</typeparam>
/// <param name="model">实体参数</param>
/// <param name="tran">事务</param>
/// <returns>返回影响的行数</returns>
public int Update <T>(T model) where T : class
{
string sql = string.Empty;
string tableName = model.GetType().Name;
Type t = model.GetType();
object[] customAttribute = null;
object[] tableAttribute = null;
tableAttribute = t.GetCustomAttributes(typeof(TableAttribute), false);
if (tableAttribute != null && tableAttribute.Length > 0)
{
tableName = (tableAttribute[0] as TableAttribute).TableName;
}
StringBuilder keyValuePaire = new StringBuilder();
StringBuilder condition = new StringBuilder();
PropertyInfo[] pis = t.GetProperties();
foreach (PropertyInfo pi in pis)
{
if (pi.GetValue(model, null) == null)//如果属性值为null 不更新
{
continue;
}
customAttribute = pi.GetCustomAttributes(typeof(ColumnAttribute), false);
if (customAttribute.Count() == 1 && (customAttribute[0] as ColumnAttribute).NotCreateWhere)
{
continue;
}
if (customAttribute.Count() == 1 && (customAttribute[0] as ColumnAttribute).PrimaryKey)
{
condition.AppendFormat(" and {0}=@{0}", pi.Name);
}
else
{
keyValuePaire.AppendFormat(",{0}=@{0}", pi.Name);
}
}
if (condition.Length == 0 || keyValuePaire.Length == 0)
{
throw new ArgumentException();
}
sql = string.Format("update {0} set {1} where 1=1 {2}", tableName, keyValuePaire.Remove(0, 1), condition);
using (var db = new LcLib.DBLib.DBHelper())
{
return(db.ExecuteNonQuery(sql, model));
}
}
/// <summary>
/// 根据语句，返回对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="args"></param>
/// <returns></returns>
public T QueryModel <T>(string sql, params object[] args) where T : class
{
using (var db = new LcLib.DBLib.DBHelper())
{
DataTable dt = db.ExecuteDataTable(sql, args);
if (dt.Rows.Count > 0)
{
return(ConvertFun.ConvertToModel <T>(dt.Rows[0]));
}
else
{
return(default(T));
}
}
}