If you’re using SQL Server Compact Editition version 4 or higher, there’s finally a decent and efficient way to do data paging:
var streets = db.Query<StreetName>(@"SELECT * FROM StreetNames ORDER BY Id OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY", new { offset = group * GroupSize, rows = GroupSize });
The code above uses Dapper-dot-net (with Contrib Extensions) to make the syntax of executing the Query succinct. (Recently, I’ve all but abandoned the Entity Framework in favor of Dapper).
The above code should be straightforward.
Given a table named, StreetNames created thusly:
db.Execute(@"CREATE TABLE StreetNames (
Id int IDENTITY NOT NULL,
Name nvarchar(24) NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (Id))");
with some sample data inserted into the table:
foreach (var name in GetStreetNames()) { db.Execute("INSERT INTO StreetNames (Name) Values (@Name)", new { Name = name }); }
you’ll likely want to grab the data in pages. To get the data as pages, you’ll need to pick an index or something ordered (in this case, I used the Id column), specify the starting index and the total number to fetch:
OFFSET @offset ROWS
FETCH NEXT @rows ROWS ONLY
So, using the sample database:
using (var db = new SqlCeConnection(connectionString)) { db.Open(); for (var group = 0; ; group++) { Console.WriteLine(string.Format("== GROUP {0} ==", group)); var streets = db.Query<StreetName>(@"SELECT * FROM StreetNames ORDER BY Id OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY", new { offset = group * GroupSize, rows = GroupSize }); foreach (var street in streets) { Console.WriteLine(string.Format("{0} ({1})", street.Name, street.Id)); } if (streets.Count() < GroupSize) { break; } } }
The code loops until it can’t load a full GroupSize.
const int GroupSize = 8;
I added an extension method to IDbConnection to check whether a table exists:
public static class DbExtensions { /// <summary> /// Determines whether the table exists /// </summary> /// <param name="connection">Existing, opened, database connection</param> /// <param name="tableName">The name of the table to test for.</param> /// <returns>True if table exists.</returns> public static bool TableExists(this IDbConnection connection, string tableName) { Debug.Assert(connection != null); Debug.Assert(!string.IsNullOrWhiteSpace(tableName)); var cmd = connection.CreateCommand(); cmd.CommandText = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName"; var p1 = cmd.CreateParameter(); p1.DbType = DbType.String; p1.ParameterName = "TableName"; p1.Value = tableName; cmd.Parameters.Add(p1); var result = cmd.ExecuteScalar(); return (int)result == 1; } }
I really like the simple syntax it permits:
if (!db.TableExists("StreetNames"))
It’s used like this:
private static string CreateDatabase() { var connectionString = string.Format("DataSource={0}", "test.sdf"); SqlCeEngine engine = new SqlCeEngine(connectionString); try { engine.CreateDatabase(); } catch (SqlCeException ex) { // file exists? (if so, we'll just ignore it ...) if (ex.NativeError != 25114) { throw; } } using (IDbConnection db = new SqlCeConnection(connectionString)) { db.Open(); // create a sample table if (!db.TableExists("StreetNames")) { db.Execute(@"CREATE TABLE StreetNames ( Id int IDENTITY NOT NULL, Name nvarchar(24) NOT NULL, CONSTRAINT pk_id PRIMARY KEY (Id))"); foreach (var name in GetStreetNames()) { db.Execute("INSERT INTO StreetNames (Name) Values (@Name)", new { Name = name }); } } } return connectionString; }
Oddly, Sql Compact doesn’t throw proper (specific) exceptions, so you’ll be forced to check for NativeErrors or against strings rather than specific Exception types. (Seriously!)
Using the SqlCeEngine class, the code attempts to create a new Database file. If it already exists, it throws an exception. All but the file exists exception are rethrown.
Using an open connection and the TableExists extension, it creates a sample table called StreetNames, populated with this data:
private static IEnumerable<string> GetStreetNames() { // Most common street names in UK, apparently :) yield return "High Street"; yield return "Station Road"; yield return "Main Street"; yield return "Church Street"; yield return "Victoria Road"; yield return "Park Road"; yield return "Church Road"; yield return "London Road"; yield return "Manor Road"; yield return "New Road"; yield return "Park Avenue"; yield return "Queens Road"; yield return "Kings Road"; yield return "Church Lane"; yield return "Green Lane"; yield return "Alexandra Road"; yield return "The Crescent"; yield return "George Street"; yield return "Grange Road"; yield return "Main Road"; yield return "King Street"; yield return "The Avenue"; yield return "New Street"; yield return "North Street"; yield return "Victoria Street"; yield return "West Street"; yield return "Queen Street"; yield return "Springfield Road"; yield return "Stanley Road"; yield return "Albert Road"; yield return "Albert Street"; yield return "Park Lane"; yield return "Chapel Street"; yield return "Highfield Road"; yield return "The Green"; yield return "Mill Lane"; yield return "Broadway"; yield return "St. Johns Road"; yield return "Marlborough Road"; yield return "Windsor Road"; yield return "Forest Road"; yield return "South Street"; yield return "Warwick Road"; yield return "Grove Road"; yield return "Kingsway"; yield return "York Road"; yield return "Woodlands Road"; yield return "Clarence Road"; yield return "School Lane"; yield return "Cromwell Road"; }
(I’d wanted to use the top 50 USA street names, but they’re far more boring as
they’re like: Fourth Street, Fifth Street, Oak Street.
).
There’s a tiny class to represent a StreetName that’s used later by the Dapper extension functions:
public class StreetName { public int Id { get; set; } public string Name { get; set; } }
The full main function:
static void Main(string[] args) { const int GroupSize = 8; var connectionString = CreateDatabase(); using (var db = new SqlCeConnection(connectionString)) { db.Open(); for (var group = 0; ; group++) { Console.WriteLine(string.Format("== GROUP {0} ==", group)); var streets = db.Query<StreetName>(@"SELECT * FROM StreetNames ORDER BY Id OFFSET @offset ROWS FETCH NEXT @rows ROWS ONLY", new { offset = group * GroupSize, rows = GroupSize }); foreach (var street in streets) { Console.WriteLine(string.Format("{0} ({1})", street.Name, street.Id)); } if (streets.Count() < GroupSize) { break; } } } Console.WriteLine("** Done **"); Console.ReadKey(); }
The db.Query<StreetName> function is Dapper provided. Given the type and a Query, it automatically and efficiently constructs/deserializes objects of type StreetName for each row returned from the SQL query.
While the syntax isn’t quite as slick as “Entity Framework” by any means, the resulting run-time efficiency far makes up for that as far as I’m concerned (and if you care about performance, you might want to re-evaluate your use of the EntityFramework as well).
I took the photo above years ago at the Boeing Museum of Flight in Seattle (which inspired me to look for videos). For fun, check out this video of a model SR-71 blackbird. It takes off around 2:21.