/// <summary>
/// 用于读取特定分页的内容。
/// </summary>
/// <param name="dbname">数据库名字</param>
/// <param name="tableName">数据表名</param>
/// <param name="page">页面</param>
/// <param name="rows">行数</param>
/// <returns></returns>
public static string read(string fname, string tableName, int page, int rows)
{
tableName = fname + "_" + tableName;
var constr = ConfigFileTool.SAPGlobalSettings.GetDefaultDbConnection();
netlib7 helper = new netlib7(constr);
// string _dbFile = DBLOCATION;
// _dbFile = _dbFile + "\\" + dbname + ".db";
//if (File.Exists(_dbFile)) {
// File.Delete(_dbFile);
//}
//if (!File.Exists(_dbFile))
//{
// return null;
// // SQLiteConnection.CreateFile(_dbFile);
//}
// string sqlstr = "";
// SQLiteDBHelper helper = new SQLiteDBHelper(_dbFile);
//检查表是否存在
// var sqlstr = String.Format("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName);
var sqlstr = String.Format("SELECT count(*) FROM '{0}'", tableName);
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = string.Format("SELECT count(*) FROM {0}", tableName);
}
else if (helper.ProviderType == netlib7.ProviderTypes.MySql)
{
sqlstr = string.Format("SELECT count(*) FROM {0}", tableName);
}
int isexist = Convert.ToInt32(helper.ExecScalar(sqlstr, null));
if (isexist <= 0)
{
return("");
}
//计算行数与偏移
int total = Convert.ToInt32(helper.ExecScalar("select count(*) from " + tableName, null));
int offset = (page - 1) * rows;
if (offset != 0)
{
// If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number.
// sqlstr = "select * from {0} limit {1}, {2}";
// sqlstr = String.Format(sqlstr, tableName, offset,rows );
sqlstr = "select * from {0} limit {1} offset {2}";
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = "SELECT top {1} * FROM ( SELECT * , ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM {0} ) AS dt WHERE dt.RowNum > {2}";
}
sqlstr = String.Format(sqlstr, tableName, rows, offset);
}
else
{
sqlstr = "select * from {0} limit {1}";
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = "SELECT top {1} * FROM {0}";
}
sqlstr = String.Format(sqlstr, tableName, rows);
}
DataTable dt = helper.DataTableFill(sqlstr);
TableObject tr = new TableObject();
tr.total = total;
tr.rows = dt;
var output = JsonConvert.SerializeObject(tr);
return(output);
}
/// <summary>
/// 用于读取特定分页的内容。
/// </summary>
/// <param name="dbname">数据库名字</param>
/// <param name="tableName">数据表名</param>
/// <param name="page">页面</param>
/// <param name="rows">行数</param>
/// <returns></returns>
public static string read(string fname, string tableName, int page, int rows)
{
tableName = fname + "_" + tableName;
var constr = ConfigFileTool.SAPGlobalSettings.GetDefaultDbConnection();
netlib7 helper = new netlib7(constr);
// string _dbFile = DBLOCATION;
// _dbFile = _dbFile + "\\" + dbname + ".db";
//if (File.Exists(_dbFile)) {
// File.Delete(_dbFile);
//}
//if (!File.Exists(_dbFile))
//{
// return null;
// // SQLiteConnection.CreateFile(_dbFile);
//}
// string sqlstr = "";
// SQLiteDBHelper helper = new SQLiteDBHelper(_dbFile);
//检查表是否存在
// var sqlstr = String.Format("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName);
var sqlstr = String.Format("SELECT count(*) FROM '{0}'", tableName);
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = string.Format("SELECT count(*) FROM {0}",tableName);
}
else if (helper.ProviderType == netlib7.ProviderTypes.MySql)
{
sqlstr = string.Format("SELECT count(*) FROM {0}",tableName);
}
int isexist = Convert.ToInt32(helper.ExecScalar(sqlstr, null));
if (isexist <= 0)
{
return "";
}
//计算行数与偏移
int total = Convert.ToInt32(helper.ExecScalar("select count(*) from " + tableName, null));
int offset = (page - 1) * rows;
if (offset != 0)
{
// If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number.
// sqlstr = "select * from {0} limit {1}, {2}";
// sqlstr = String.Format(sqlstr, tableName, offset,rows );
sqlstr = "select * from {0} limit {1} offset {2}";
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = "SELECT top {1} * FROM ( SELECT * , ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM {0} ) AS dt WHERE dt.RowNum > {2}";
}
sqlstr = String.Format(sqlstr, tableName, rows, offset);
}
else
{
sqlstr = "select * from {0} limit {1}";
if (helper.ProviderType == netlib7.ProviderTypes.SqlServer)
{
sqlstr = "SELECT top {1} * FROM {0}";
}
sqlstr = String.Format(sqlstr, tableName, rows);
}
DataTable dt = helper.DataTableFill(sqlstr);
TableObject tr = new TableObject();
tr.total = total;
tr.rows = dt;
var output = JsonConvert.SerializeObject(tr);
return output;
}
