public override JToken page2(string table, string primaryKey, string fields, int pageSize, int pageIndex, string where = "", string sort = "")
{
ConfigReader cr = new ConfigReader();
var database = cr.module(string.Format("database.{0}", table));
//加载结构
var table_fields = database.SelectToken("fields");
var fields_arr = fields.Split(',').ToList();
var field_sels = (from f in fields_arr
join tf in table_fields
on f equals tf["name"].ToString()
select tf).ToArray();
//选择所有字段
if (fields.Trim() == "*")
{
field_sels = table_fields.ToArray();
fields_arr = (from f in field_sels
select f["name"].ToString()).ToList();
fields = string.Join(",", fields_arr.ToArray());
}
int pageStart = (pageIndex - 1) * (pageSize - 1);
int pageEnd = (pageIndex - 1) * pageSize + pageSize;
string sql = string.Format("select {0} from {1} where {2} limit {3},{4}",
fields,
table,
where,
pageStart,
pageEnd
);
DbHelper db = new DbHelper();
var cmd = db.GetCommand(sql);
var r = db.ExecuteReader(cmd);
JArray a = new JArray();
SqlCmdReader scr = new SqlCmdReader();
while (r.Read())
{
int index = 0;//从1开始,0是行号
var o = new JObject();
foreach (var field in fields_arr)
{
var fd = field_sels[index];
var fd_type = fd["type"].ToString().ToLower();
o[field] = scr[fd_type](r, index++);
}
a.Add(o);
}
r.Close();
return(JToken.FromObject(a));
}
public virtual JToken page2(string table, string primaryKey, string fields, int pageSize, int pageIndex, string where = "", string sort = "")
{
ConfigReader cr = new ConfigReader();
var database = cr.module(string.Format("database.{0}", table));
//加载结构
var table_fields = database.SelectToken("fields");
var fields_arr = fields.Split(',').ToList();
var field_sels = (from f in fields_arr
join tf in table_fields
on f equals tf["name"].ToString()
select tf).ToArray();
//选择所有字段
if (fields.Trim() == "*")
{
field_sels = table_fields.ToArray();
fields_arr = (from f in field_sels
select f["name"].ToString()).ToList();
fields = string.Join(",", fields_arr.ToArray());
}
DbHelper db = new DbHelper();
var cmd = db.GetCommandStored("spPager");
db.AddString(ref cmd, "@table", table, 200);
db.AddString(ref cmd, "@primarykey", primaryKey, 50);
db.AddInInt32(cmd, "@pagesize", pageSize);
db.AddInInt32(cmd, "@pageindex", pageIndex);
db.AddBool(ref cmd, "@docount", false);
db.AddString(ref cmd, "@where", where, 1000);
db.AddString(ref cmd, "@sort", sort, 50);
db.AddString(ref cmd, "@fields", fields, 500);
var r = db.ExecuteReader(cmd);
JArray a = new JArray();
SqlCmdReader scr = new SqlCmdReader();
while (r.Read())
{
int index = 1;//从1开始,0是行号
var o = new JObject();
foreach (var field in fields_arr)
{
var fd = field_sels[index - 1];
var fd_type = fd["type"].ToString().ToLower();
o[field] = scr[fd_type](r, index++);
}
a.Add(o);
}
r.Close();
return(JToken.FromObject(a));
}
public virtual JObject read(string table, string fields, string where)
{
//加载结构
this.m_table = this.table(table);
var field_all = this.m_table.SelectToken("fields");
var field_sel = this.selFields(fields, field_all);
if (!string.IsNullOrEmpty(where))
{
where = string.Format("where {0}", where);
}
JObject o = null;
string sql = string.Format("select {0} from [{1}] {2}"
, this.selFieldNames(field_sel)
, table
, where);
DbHelper db = new DbHelper();
var cmd = db.GetCommand(sql);
SqlCmdReader scr = new SqlCmdReader();
var r = db.ExecuteReader(cmd);
if (r.Read())
{
o = this.m_cmdRd.read(r, field_sel);
}
r.Close();
return(o);
}
public virtual JObject read(string table, string fields, SqlParam[] where)
{
//加载结构
this.m_table = this.table(table);
var field_all = this.m_table.SelectToken("fields");
var field_sel = this.selFields(fields, field_all);
var field_cdt = this.selFields(where, field_all);
JObject o = null;
string sql = string.Format("select {0} from {1} where {2}"
, this.selFieldNames(field_sel)
, table
, this.toSqlCondition(where, "and"));
DbHelper db = new DbHelper();
var cmd = db.GetCommand(sql);
this.m_parSetter.setVal(cmd, field_cdt, where);
SqlCmdReader scr = new SqlCmdReader();
var r = db.ExecuteReader(cmd);
if (r.Read())
{
o = this.m_cmdRd.read(r, field_sel);
}
r.Close();
return(o);
}
public SqlExec()
{
//初始化变量设置器
this.m_pvSetter = new SqlParValSetter();
this.m_parSetter = new SqlParamSetter();
this.m_pc = new SqlParamCreater();
this.m_cmdRd = new SqlCmdReader();
this.m_cfg = new DBConfig();
}