public n_findby_dw()
{
try
{
Dbtype = SqlHelper.GetDBType(SqlHelper.Dbtype); ;
}
catch
{
Dbtype = "mssql";
}
if (inv_where == null)
{
inv_where = new n_form_where();
}
if (inv_where_debug == null)
{
inv_where_debug = new n_form_where();
}
//初始化大小
Array.Resize(ref isa_cols, 11);
Array.Resize(ref isa_or, 11);
}
public DataTable Of_GetPageDataTable(string as_keyName, int ai_PageSize, int ai_PageIndex, ref int ai_totalrow,GysoftParameter[] ap_list)
{
ErrorStr = "";
if (string.IsNullOrEmpty(this.SQL))
{
ErrorStr = " Of_GetPageDataTable 取this.sql为空：" + as_keyName;
//Log.WriteTextLog("n_findby_dw", "error", " Of_GetPageDataTable 取this.sql为空：" + as_keyName);
return null;
}
n_form_where lnv_part=new n_form_where();
lnv_part.of_SetSQL(this.SQL);
string ls_key_md5, ls_json;
ls_key_md5 = DES.of_Md5(this.SQL);
string ls_Sql;
//取到前面多少条记录
int li_top = 0;
li_top = ai_PageSize * ai_PageIndex;
if ((ai_PageIndex == 1) && (ai_totalrow >= 0))
{
li_top = 20000; //最大只取到2万行
}
//只取主键列的数据 ，目前假设主键列，只有一个字段
GysoftParameter[] Pa;
ls_Sql = this.vf_GetSQLselect(out Pa, as_keyName, li_top);
//查看一下是否缓存已经有了
DataTable ldt_Tb = new DataTable();
if (ap_list != null)
{
int li_paCount;
li_paCount = Pa.Length;
Array.Resize(ref Pa, li_paCount + ap_list.Length);
for (int li_p = 0; li_p < ap_list.Length; li_p++)
{
Pa[li_paCount + li_p] = new GysoftParameter(ap_list[li_p].PaName, ap_list[li_p].Value);
}
}
//没有缓存的数据
if (gydatatable.of_DataTableRowcount(ldt_Tb) <= 0)
{
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
ai_totalrow = gydatatable.of_DataTableRowcount(ldt_Tb); //最大只取到2万行
ErrorStr = SqlHelper.ErrStr;
ls_json = Json.DataTableToJson(ldt_Tb);
}
//if (ldt_Tb != null)
//{
// li_count = ldt_Tb.Rows.Count; //没有记录
//}
string ls_InWhere = "";
if (ai_totalrow == 0)
{
ai_totalrow = 0;
inv_where.of_add_ons("findbytop", "0=1");
ls_Sql = inv_where.of_GetSQLselect();
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
inv_where.of_del_ons("findbytop");
//返回空结果值
return ldt_Tb;
}
string ls_ColType = null;
if (ldt_Tb.Columns.Count == 1)
{
ls_ColType = ldt_Tb.Columns[0].DataType.Name.ToLower();
}
else
{
ls_ColType = "string"; //有多列
}
string ls_One = null;
string ls_In = null;
ls_In = "";
int li_row = 0;
//取到这一页的主键值
int li_Total = 0;
li_Total = ai_PageSize * ai_PageIndex;
if (ldt_Tb.Rows.Count < li_Total)
li_Total = ldt_Tb.Rows.Count;
for (li_row = ai_PageSize * (ai_PageIndex - 1); li_row <= li_Total - 1; li_row++)
{
ls_One = ldt_Tb.Rows[li_row][0].ToString().Trim();
//目前最多支持二列
if (ldt_Tb.Columns.Count > 1)
{
ls_One += "#" + ldt_Tb.Rows[li_row][1].ToString().Trim();
}
if (ls_ColType == "string")
{
ls_One = "'" + ls_One + "'";
}
GYstring.gf_addstring_list(ref ls_In, ls_One, ",");
//每5个值，换一行，好看点
if (li_row % 5 == 0)
{
ls_In += "\r\n";
}
}
invalue = ls_In;
if (ls_In.Trim().Length > 0)
{
if (ldt_Tb.Columns.Count > 1)
{
//多列用#联结起来
string ls_oneCol, ls_TwoCol;
int li_pos;
li_pos = as_keyName.IndexOf(",");
ls_oneCol = as_keyName.Substring(0, li_pos);
ls_TwoCol = as_keyName.Substring(li_pos + 1);
if (SqlHelper.Dbtype.ToString().ToLower() == "mssql")
{
as_keyName = "TRIM(" + ls_oneCol + ")+TRIM(" + ls_TwoCol + ")";
}
else //mysql版
{
as_keyName = "CONCAT(TRIM(" + ls_oneCol + "), CONCAT('#',TRIM(" + ls_TwoCol + "))) ";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
else
{
ls_InWhere = "0=1";
}
//本次需要显示的记录对应的主键 id
inv_where.of_add_ons("findbytop", ls_InWhere);
ls_Sql = inv_where.of_GetSQLselect();
ErrorStr = "";
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
ErrorStr = SqlHelper.ErrStr;
inv_where.of_del_ons("findbytop");
//显示全部的SQL
//__AllSQL = RepealPa(ls_Sql, Pa);
return ldt_Tb;
}
public int of_setSQL(string as_sql)
{
if (inv_where == null)
{
inv_where = new n_form_where();
}
this.SQL = as_sql;
inv_where.of_SetSQL(as_sql);
inv_where_debug.of_SetSQL(as_sql);
return 1;
}
