public DataTable GetDataByKey(string tablename, string field, object obj)
{
string sql = "select * from " + tablename + " where " + field + "=@" + field;
IDbParameters param = AdoTemplate.CreateDbParameters();
param.AddWithValue(field, obj);
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
dt.TableName = tablename;
return(dt);
}
/// <summary>
/// 获取指定sql的数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="hs"></param>
/// <returns></returns>
public List <T> GetList <T>(CommandType commandType, string sql, IDbParameters param)
{
try
{
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
return(ObjectHelper.CopyToObjects <T>(dt).ToList() ?? default(List <T>));
}
catch (Exception ex)
{
return(default(List <T>));
}
}
public DataTable QueryDataTable(CommandType commandType, string cmdText, IDbParameters parameters)
{
if (commandType == CommandType.StoredProcedure)
{
cmdText = cmdText.Split(' ')[0];
}
try
{
return(_AdoTemplate.DataTableCreateWithParams(commandType, cmdText, parameters));
}
catch (Exception e)
{
dsExceptionHandler.HandleException(e);
ReConnection(e);
throw e;// new Goodway.CommonModel.Exception.DbException(DbResult.SqlError, e.Message ?? "" + Environment.NewLine + e.StackTrace ?? "", cmdText);
}
}
/// <summary>
/// 分页(最好是通过存储过程去分页取数据，因为数据库sql语法不一样)
/// 这里只支持了mysql分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <T> GetPageList <T>(string sql, IDbParameters param, int pageIndex, int pageSize, string orderBy)
{
PagedList <T> pageList = null;
if (!string.IsNullOrEmpty(orderBy))
{
sql += " order by " + orderBy;
}
var obj = AdoTemplate.ExecuteScalar(CommandType.Text, "select count(1) from (" + sql + ") as A", param);
int totalItemCount = int.Parse(obj.ToString());
sql += " limit " + (pageIndex - 1) * pageSize + "," + pageSize + "";
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
T[] list = ObjectHelper.CopyToObjects <T>(dt);
pageList = new PagedList <T>(list, pageIndex, pageSize, totalItemCount);
return(pageList);
}
/// <summary>
/// 获取指定条件的数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="hs"></param>
/// <returns></returns>
public List <T> GetList <T>(Hashtable hs)
{
List <T> list = new List <T>();
try
{
Type type = typeof(T);
List <PropertyInfo> propertys = type.GetProperties().ToList();
var attribute = type.GetCustomAttributes(typeof(TableMapAttribute), false).FirstOrDefault();
if (attribute == null)
{
throw new Exception("类" + type.Name + "必须添加'TableMapAttribute'属性");
}
string tableName = ((TableMapAttribute)attribute).TableName;
string sql = "select * from " + tableName + " where 1=1";
//对于有IsDelete属性的，过滤已删除的数据
if (propertys.Exists(p => p.Name.ToLower() == "isdelete"))
{
sql += " and IsDelete=0 ";
}
IDbParameters param = AdoTemplate.CreateDbParameters();
foreach (string key in hs.Keys)
{
sql += string.Format(" and {0}=?{0}", key);
param.AddWithValue(key, hs[key]);
}
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
dt.TableName = tableName;
list = ObjectHelper.CopyToObjects <T>(dt).ToList();
return(list ?? new List <T>());
}
catch (Exception ex)
{
return(list);
}
}
/// <summary>
/// 执行sql语句
/// </summary>
public DataTable ExecteSqlGetDataTable(string sql, IDbParameters parms, CommandType commandType = CommandType.Text)
{
return(AdoTemplate.DataTableCreateWithParams(commandType, sql, parms));
}
