Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

Select TOP, LIMIT workaround on SQLCE 2.0 or limiting the number of rows/getting a range from a dataset

Problem:
-In SQLCE 2.0 it is documented that 2 of the common used keywords are not supported in gettings a range of records from a dataset. They are LIMIT and TOP which are use this way:

LIMIT:
SELECT * FROM tableA LIMIT 5, 10;

This query will select a recordset from tableA, which will get 10 records starting from the fifth record. So it will return recordset 5 - 15.

TOP:
SELECT TOP 5 * FROM tableA;

The query will return the first 5 records.

Since these keywords are not supported by SQLCE 2.0, I've tried several possible workaround and one that worked for me is using the:

SqlCeDataAdapter.Fill(DataSet ds, int StartRecord, int MaxRecord, String "TableName") method, the parameters are self-explanatory. Using this method you will be able to select a range in your dataset. Example call:

public DataSet QueryAsDataset(string sql, int startRecord, int maxRecord) 
{
 DataSet ds = new DataSet();
 try 
 {
  conn.Open();
  SqlCeDataAdapter da = new SqlCeDataAdapter(sql, conn);
  da.Fill(ds, startRecord, maxRecord, "czetsuya");
 } 
 catch(SqlCeException e) 
 {
  LogHelper.WriteLog(ErrorCode.DATABASE_EXECUTE_SQL_DATASET, e.Message);
 } 
 finally 
 {
  conn.Close();
 }
 return ds;
}
Select TOP, LIMIT workaround on SQLCE 2.0 or limiting the number of rows/getting a range from a dataset Select TOP, LIMIT workaround on SQLCE 2.0 or limiting the number of rows/getting a range from a dataset Reviewed by czetsuya on Tuesday, December 15, 2009 Rating: 5

No comments:

Powered by Blogger.