/// <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; }