public static DataTable getPtData(string strWhere, int nPageIdx, int nPageSize)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
string strSql = string.Format("select {0} from {1} {2} limit {3},{4}", tbShow_base.ToShowString(), tbShow_base.StrTbName, strWhere, (nPageIdx - 1) * nPageSize, nPageSize);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
//初始化cbo控件
public static DataTable Select_YongHu(string _strLname)
{
string strSql = "select * from sys_user where Lname=@Lname";
//准备调用的对应参数
MySqlParameter[] SQlCMDpas =
{
new MySqlParameter("@Lname", MySqlDbType.String),
};
SQlCMDpas[0].Value = _strLname;
//实例化DAL层对应的类，调用DAL类，传入参数
DataTable dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql, SQlCMDpas);;
return(dt);
}
public static DataTable getBaseData(string _strSid, string _strDbType)
{
DataTable dt = null;
try
{
string strIsFiled = MySqlDbAccess.ExecuteScalar(CommandType.Text, string.Format("select cfg_stcolumn_filed from cfg_dbtype where dbty='{0}'", _strDbType)).ToString();
string strSql = MySqlDbAccess.ExecuteScalar(CommandType.Text, string.Format(@"SELECT GROUP_CONCAT(CONCAT_WS(' as ',col_name,showname) order by id asc separator ',') as fileds
FROM cfg_stcolumn where tb_name='show_base' and {0}=true", strIsFiled)).ToString();
dt = MySqlDbAccess.GetDataTable(CommandType.Text, string.Format("select {0} from show_base", strSql));
}
catch (Exception ex)
{
}
return(dt);
}
public static DataTable getPtData(string strWhere, string strOdery, string strTbName)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
string strSql = string.Format("select {0} from {1} {2} {3}", "*",
strTbName, strWhere, strOdery);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static DataTable getPtData(string strWhere, string strOdery, int nPageIdx, int nPageSize, string strDbTy)
{
DataTable dt = null;
if ((!strWhere.Trim().Equals("")) && (!strWhere.ToLower().Contains(" where ")))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
//string strSql = string.Format("select (@rowIdx:=@rowIdx+1) AS 序号,{0} from {1} a,(SELECT @rowIdx:={3}) b {2} {5} limit {3},{4}", tbShow_base.ToShowString(strDbTy),
// tbShow_base.StrTbName, strWhere, (nPageIdx - 1) * nPageSize, nPageSize, strOdery);
string strSql = string.Format("select (@rowIdx:=@rowIdx+1) AS 序号,{0} {1} {2} {5} limit {3},{4}",
tbShow_base.ToFiledFromString(strDbTy, string.Format("(SELECT @rowIdx:={0}) b ", (nPageIdx - 1) * nPageSize), strWhere),
"", "", (nPageIdx - 1) * nPageSize, nPageSize, strOdery);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
public static DataTable getSysKey(string strWhere, string strOdery, int nPageIdx, int nPageSize)
{
DataTable dt = null;
if (!strWhere.Trim().Equals(""))
{
strWhere = " where " + strWhere;
}
if (!strOdery.Trim().Equals(""))
{
strOdery = " order by " + strOdery;
}
string strSqlTmp = @"SELECT (@rowIdx:=@rowIdx+1) AS 序号,a.id,a.idx_key as 标引项,b.Lname as 创建人,
(SELECT GROUP_CONCAT(val ORDER BY id ASC SEPARATOR ',') FROM idx_val WHERE keyid = a.id)AS 标引词 ,
a.createtime as 创建时间 FROM idx_key a,sys_user b,(SELECT @rowIdx:={2}) c where a.createuser=b.id {0} {1} limit {2},{3}";
string strSql = string.Format(strSqlTmp, strWhere, strOdery, (nPageIdx - 1) * nPageSize, nPageSize);
dt = MySqlDbAccess.GetDataTable(CommandType.Text, strSql);
return(dt);
}
/// <summary>
/// 得到专题库列表
/// </summary>
/// <returns></returns>
public static DataTable GetZTList()
{
return(MySqlDbAccess.GetDataTable(CommandType.Text, "select * from st_ztlist where isdel =0"));
}
/// <summary>
/// 得到专题库列表
/// </summary>
/// <returns></returns>
public static DataTable GetZTList(string strTy, int nExceptId)
{
return(MySqlDbAccess.GetDataTable(CommandType.Text, string.Format("select * from st_ztlist where isdel =0 and dbtype='{0}' and id!={1}", strTy, nExceptId)));
}
/// <summary>
/// 得到专题库列表
/// </summary>
/// <returns></returns>
public static DataTable GetZTList()
{
return(MySqlDbAccess.GetDataTable(CommandType.Text, "select * ,'系统管理员' as 'createusername' from st_ztlist where isdel =0"));
}