public DataTable QueryDataTable(string cmdText)
{
try
{
return(_AdoTemplate.DataTableCreate(CommandType.Text, cmdText));
}
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="selectSql"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <T> GetPageList <T>(int pageIndex, int pageSize, string orderBy)
{
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 ";
}
if (!string.IsNullOrEmpty(orderBy))
{
sql += " order by " + orderBy;
}
int totalItemCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(1) from (" + sql + ") as A");
sql += " limit " + (pageIndex - 1) * pageSize + "," + pageSize + "";
DataTable dt = AdoTemplate.DataTableCreate(CommandType.Text, sql);
T[] list = ObjectHelper.CopyToObjects <T>(dt);
PagedList <T> pageList = new PagedList <T>(list, pageIndex, pageSize, totalItemCount);
return(pageList);
}
public DataTable GetDataTable(CommandType cmdType, string cmdText)
{
DataTable dt = new DataTable();
dt = AdoTemplate.DataTableCreate(cmdType, cmdText);
return(dt);
}
/// <summary>
/// 获取排序数据
/// </summary>
public DataTable GetSortDataByKey(string tablename, string field, string sortType)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from {0} order by [{1}] {2}", tablename, field, sortType);
DataTable dt = AdoTemplate.DataTableCreate(CommandType.Text, sb.ToString());
dt.TableName = tablename;
return(dt);
}
/// <summary>
/// 获取DataTable数据集
/// </summary>
/// <param name="fields">数据字段，如：string fields="ID,Name,Sex";如为"*",则为所有字段</param>
/// <param name="where">条件，如：string fields="ID=1"</param>
public DataTable GetData(string tableName, string fields, string where)
{
StringBuilder sbSql = new StringBuilder();
if (where == "")
{
sbSql.AppendFormat("select {0} from {1}", fields, tableName);
}
else
{
sbSql.AppendFormat("select {0} from {1} where {2}", fields, tableName, where);
}
return(AdoTemplate.DataTableCreate(CommandType.Text, sbSql.ToString()));
}
public DataTable GetEmptyTable(string tablename)
{
string sql = "select name,xtype,typestat from syscolumns where id=object_id('" + tablename + "')";
DataTable dt = AdoTemplate.DataTableCreate(CommandType.Text, sql);
DataTable dtRes = new DataTable(tablename);
foreach (DataRow dr in dt.Rows)
{
/*
* 34 image； 48 tinyint； 56 int； 61 datetime； 104 bit； 106 decimal
* 165 varbinary；167 varchar； 231 nvarchar；239 nchar
*/
Type type = typeof(string);
byte t = (byte)dr["xtype"];
if (t == 48)
{
type = typeof(byte);
}
else if (t == 56)
{
type = typeof(int);
}
else if (t == 61)
{
type = typeof(DateTime);
}
else if (t == 104)
{
type = typeof(bool);
}
else if (t == 106)
{
type = typeof(decimal);
}
else if (t == 167 || t == 231 || t == 239)
{
type = typeof(string);
}
else if (t == 165 || t == 34)
{
type = typeof(byte[]);
}
dtRes.Columns.Add(dr["name"].ToString(), type);
}
return(dtRes);
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="cType">执行类型</param>
/// <param name="sql">sql语句</param>
/// <returns>表数据DataTable</returns>
public DataTable ExecteSqlGetDataTable(CommandType cType, string sql)
{
return(AdoTemplate.DataTableCreate(CommandType.Text, sql));
}
