/// <summary>
/// Checks the in parameter.
/// </summary>
/// <param name="para">The para.</param>
/// <returns></returns>
private string CheckInParam(MPagerInParam para)
{
if (para == null)
{
return "分页查询，输入参数为null。";
}
if (string.IsNullOrEmpty(para.DataBaseName))
{
return "分页查询，数据库名为空。";
}
if (para.DataBaseType == Model.MDataBaseType.UNKNOW)
{
return "分页查询,数据库类型未知";
}
if (string.IsNullOrEmpty(para.FieldNames))
{
return "分页查询，无查询字段。";
}
if (string.IsNullOrEmpty(para.TableName))
{
return "分页查询，表名为空。";
}
return string.Empty;
}
/// <summary>
/// Pagers the query.
/// </summary>
/// <param name="para">The para.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public MPagerReturn PagerQuery(MPagerInParam para)
{
string errorstr = this.CheckInParam(para);
if (!string.IsNullOrEmpty(errorstr))
{
throw new Exception(errorstr);
}
if (para.PageIndex < 1)
{
para.PageIndex = 1;
}
if (para.PageSize == 0)
{
// 默认每页20条
para.PageSize = 20;
}
var dao = DALFactory.GetPagerQueryDAO(para.DataBaseType);
MPagerReturn ret = null;
using (var conn = ConnectionFactory.GetDbConn(para.DataBaseName, para.DataBaseType))
{
ret = dao.PagerQuery(conn, para);
}
return ret;
}
/// <summary>
/// Pager
/// </summary>
/// <param name="pageIndex">pageIndex</param>
/// <returns>结果</returns>
public ActionResult Pager(int pageIndex = 1)
{
MPagerInParam param = new MPagerInParam()
{
DataBaseName = "ServiceRouteDBRead",
FieldNames = "RouteID,ContractName,ServiceType,CallSystem,MachineNO,DataCenter,UniqueSign,BindingType,ServiceIP,ServicePort,SvcPath,IsValid,ModifyTime,IsDelete,IsVirtualAddress,ProgramName",
PageIndex = pageIndex,
Parameters = this.GetConditon(),
PageSize = 20,
TableName = "ServiceRoute",
Sort = "ModifyTime DESC"
};
MPagerReturn result = ServiceFactory.GetPagerContractDAO().PagerQuery(param);
List<VMServiceRoute> list = new List<VMServiceRoute>();
if (result != null && result.PageData != null && result.PageData.Rows.Count > 0)
{
foreach (DataRow row in result.PageData.Rows)
{
VMServiceRoute model = new VMServiceRoute();
model.RouteID = row["RouteID"].ToString();
model.ContractName = row["ContractName"].ToString();
model.ServiceType = row["ServiceType"].ToString();
model.CallSystem = row["CallSystem"].ToString();
model.MachineNO = row["MachineNO"].ToString();
model.DataCenter = row["DataCenter"].ToString();
model.UniqueSign = row["UniqueSign"].ToString();
model.BindingType = row["BindingType"].ToString();
model.ServiceIP = row["ServiceIP"].ToString();
model.ServicePort = Convert.ToInt32(row["ServicePort"].ToString());
model.SvcPath = row["SvcPath"].ToString();
model.IsValid = Convert.ToInt32(row["IsValid"].ToString());
model.ModifyTime = Convert.ToDateTime(row["ModifyTime"].ToString());
model.IsDelete = Convert.ToInt32(row["IsDelete"].ToString());
model.IsVirtualAddress = Convert.ToInt32(row["IsVirtualAddress"].ToString());
model.ProgramName = row["ProgramName"].ToString();
list.Add(model);
}
}
PagedList<VMServiceRoute> pagelist = new PagedList<VMServiceRoute>(list, result.PageIndex, param.PageSize, (int)result.RowCount);
return this.View(pagelist);
}
public ActionResult UserInfoManage(int pageIndex = 1)
{
MPagerInParam param = new MPagerInParam()
{
DataBaseName = "AmyDBRead",
FieldNames = "KeyID,LoginName,Password,UserName,Telephone,Email,ModifyTime,AddTime",
PageIndex = pageIndex,
Parameters = this.GetConditon(),
PageSize = 20,
TableName = "userinfo",
Sort = "ModifyTime DESC"
};
string json = JsonConvert.SerializeObject(param);
MPagerReturn result = ServiceFactory.GetPagerContractDAO().PagerQuery(param);
List<VMUserInfo> list = new List<VMUserInfo>();
if (result != null && result.PageData != null && result.PageData.Rows.Count > 0)
{
foreach (DataRow row in result.PageData.Rows)
{
VMUserInfo model = new VMUserInfo();
model.KeyID = row["KeyID"].ToString();
model.LoginName = row["LoginName"].ToString();
model.UserName = row["UserName"].ToString();
model.Password = row["Password"].ToString();
model.Telephone = row["Telephone"].ToString();
model.Email = row["Email"].ToString();
model.ModifyTime = Convert.ToDateTime(row["ModifyTime"].ToString());
model.AddTime = Convert.ToDateTime(row["AddTime"].ToString());
list.Add(model);
}
}
PagedList<VMUserInfo> pagelist = new PagedList<VMUserInfo>(list, result.PageIndex, param.PageSize, (int)result.RowCount);
return this.View(pagelist);
}
public MPagerReturn PagerQuery(MPagerInParam para)
{
return new BPagerQuery().PagerQuery(para);
}
public MPagerReturn PagerQuery(IDbConnection conn, MPagerInParam para)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT COUNT(1)");
sb.AppendFormat(" FROM {0}", para.TableName);
if (!string.IsNullOrEmpty(para.Condition))
{
sb.AppendFormat(" WHERE {0}", para.Condition);
}
string countsql = sb.ToString();
sb = new StringBuilder();
sb.AppendFormat("SELECT {0}", para.FieldNames);
sb.AppendFormat(" FROM {0}", para.TableName);
if (!string.IsNullOrEmpty(para.Condition))
{
sb.AppendFormat(" WHERE {0}", para.Condition);
}
if (!string.IsNullOrEmpty(para.Sort))
{
sb.AppendFormat(" ORDER BY {0}", para.Sort);
}
int start = (para.PageIndex - 1) * para.PageSize;
int end = para.PageIndex * para.PageSize;
sb.AppendFormat(" LIMIT {0},{1}", start, end);
string sql = sb.ToString();
List<MySqlParameter> list = new List<MySqlParameter>();
foreach (var item in para.Parameters)
{
MySqlParameter model = new MySqlParameter()
{
DbType = item.DbType,
ParameterName = item.ParameterName,
Value = item.Value,
};
list.Add(model);
}
object count = MySqlHelper.ExecuteScalar((MySqlConnection)conn, countsql, list.ToArray());
MPagerReturn ret = new MPagerReturn();
ret.RowCount = long.Parse(count.ToString());
DataSet ds = MySqlHelper.ExecuteDataset((MySqlConnection)conn, sql, list.ToArray());
if (ds != null && ds.Tables.Count > 0)
{
ret.PageData = ds.Tables[0];
}
int total = (int)(ret.RowCount / para.PageSize);
int yu = (int)(ret.RowCount % para.PageSize);
total = yu == 0 ? total : total + 1;
ret.PageCount = total;
ret.PageIndex = para.PageIndex;
return ret;
}