{"id":1597,"date":"2012-04-01T14:05:00","date_gmt":"2012-04-01T19:05:00","guid":{"rendered":"http:\/\/www.wiredprairie.us\/blog\/?p=1597"},"modified":"2012-04-01T14:18:25","modified_gmt":"2012-04-01T19:18:25","slug":"paging-data-with-sql-server-compact","status":"publish","type":"post","link":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1597","title":{"rendered":"Paging Data with SQL Server Compact"},"content":{"rendered":"

\"image\"<\/p>\n

If you\u2019re using SQL Server Compact Editition<\/a> version 4 or higher, there\u2019s finally a decent and efficient way to do data paging:<\/p>\n

var <\/span>streets = db.Query<StreetName<\/span>>(@"SELECT * FROM StreetNames\n                   ORDER BY Id\n                   OFFSET @offset ROWS\n                   FETCH NEXT @rows ROWS ONLY"<\/span>,\n                        new <\/span>{ offset = group * GroupSize, \n                            rows = GroupSize });<\/pre>\n
The code above uses Dapper-dot-net<\/a> (with Contrib Extensions) to make the syntax of executing the Query succinct. (Recently, I\u2019ve all but abandoned the Entity Framework in favor of Dapper).<\/p>\n
The above code should be straightforward.<\/p>\n
Given a table named, StreetNames<\/strong> created thusly:<\/p>\n
db.Execute(@"CREATE TABLE StreetNames (          \n            Id int IDENTITY NOT NULL,\n            Name nvarchar(24) NOT NULL,\n            CONSTRAINT pk_id PRIMARY KEY (Id))"<\/span>);<\/pre>\n
with some sample data inserted into the table:<\/p>\n
foreach <\/span>(var <\/span>name in <\/span>GetStreetNames())\n{\n    db.Execute("INSERT INTO StreetNames (Name) Values (@Name)"<\/span>,\n        new <\/span>{ Name = name });\n}<\/pre>\n
you\u2019ll likely want to grab the data in pages. To get the data as pages, you\u2019ll 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:<\/p>\n
OFFSET @offset ROWS\nFETCH NEXT @rows ROWS ONLY\n<\/span><\/pre>\n
So, using the sample database:<\/p>\n
using <\/span>(var <\/span>db = new <\/span>SqlCeConnection<\/span>(connectionString))\n{\n    db.Open();\n    for <\/span>(var <\/span>group = 0; ; group++)\n    {\n        Console<\/span>.WriteLine(string<\/span>.Format("== GROUP {0} =="<\/span>, group));\n        var <\/span>streets = db.Query<StreetName<\/span>>(@"SELECT * FROM StreetNames\n                           ORDER BY Id\n                           OFFSET @offset ROWS\n                           FETCH NEXT @rows ROWS ONLY"<\/span>,\n                                new <\/span>{ offset = group * GroupSize, \n                                    rows = GroupSize });\n\n        foreach <\/span>(var <\/span>street in <\/span>streets)\n        {\n            Console<\/span>.WriteLine(string<\/span>.Format("{0} ({1})"<\/span>,\n                street.Name, street.Id));\n        }\n        if <\/span>(streets.Count() < GroupSize) { break<\/span>; }\n    }\n}<\/pre>\n
The code loops until it can\u2019t load a full GroupSize.<\/p>\n
const int <\/span>GroupSize = 8;<\/pre>\n
I added an extension method to IDbConnection<\/a> to check whether a table exists:<\/p>\n
public static class <\/span>DbExtensions\n<\/span>{\n    \/\/\/ <summary>\n    \/\/\/ <\/span>Determines whether the table exists\n    <\/span>\/\/\/ <\/summary>\n    \/\/\/ <param name="connection"><\/span>Existing, opened, database connection<\/span><\/param>\n    \/\/\/ <param name="tableName"><\/span>The name of the table to test for.<\/span><\/param>\n    \/\/\/ <returns><\/span>True if table exists.<\/span><\/returns>\n    <\/span>public static bool <\/span>TableExists(this <\/span>IDbConnection <\/span>connection, string <\/span>tableName)\n    {\n        Debug<\/span>.Assert(connection != null<\/span>);\n        Debug<\/span>.Assert(!string<\/span>.IsNullOrWhiteSpace(tableName));\n\n        var <\/span>cmd = connection.CreateCommand();\n        cmd.CommandText = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES \n                            WHERE TABLE_NAME=@TableName"<\/span>;\n        var <\/span>p1 = cmd.CreateParameter();\n        p1.DbType = DbType<\/span>.String;\n        p1.ParameterName = "TableName"<\/span>;\n        p1.Value = tableName;\n        cmd.Parameters.Add(p1);\n\n        var <\/span>result = cmd.ExecuteScalar();\n        return <\/span>(int<\/span>)result == 1;\n    }\n}<\/pre>\n
I really like the simple syntax it permits:<\/p>\n
if <\/span>(!db.TableExists("StreetNames"<\/span>))<\/pre>\n
It\u2019s used like this:<\/p>\n
private static string <\/span>CreateDatabase()\n{\n    var <\/span>connectionString = string<\/span>.Format("DataSource={0}"<\/span>, "test.sdf"<\/span>);\n    SqlCeEngine <\/span>engine = new <\/span>SqlCeEngine<\/span>(connectionString);\n    try\n    <\/span>{\n        engine.CreateDatabase();\n    }\n    catch <\/span>(SqlCeException <\/span>ex)\n    {\n        \/\/ file exists? (if so, we'll just ignore it ...)\n        <\/span>if <\/span>(ex.NativeError != 25114) { throw<\/span>; }\n    }\n\n    using <\/span>(IDbConnection <\/span>db = new <\/span>SqlCeConnection<\/span>(connectionString))\n    {\n        db.Open();\n        \/\/ create a sample table \n        <\/span>if <\/span>(!db.TableExists("StreetNames"<\/span>))\n        {\n            db.Execute(@"CREATE TABLE StreetNames (                \n                        Id int IDENTITY NOT NULL,\n                        Name nvarchar(24) NOT NULL,\n                        CONSTRAINT pk_id PRIMARY KEY (Id))"<\/span>);\n            foreach <\/span>(var <\/span>name in <\/span>GetStreetNames())\n            {\n                db.Execute("INSERT INTO StreetNames (Name) Values (@Name)"<\/span>,\n                    new <\/span>{ Name = name });\n            }\n        }\n    }\n    return <\/span>connectionString;\n}<\/pre>\n
Oddly, Sql Compact doesn\u2019t throw proper (specific) exceptions, so you\u2019ll be forced to check for NativeErrors or against strings rather than specific Exception types. (Seriously!)<\/p>\n
Using the SqlCeEngine<\/a> 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. <\/p>\n
Using an open connection and the TableExists extension, it creates a sample table called StreetNames, populated with this data:<\/p>\n
private static <\/span>IEnumerable<\/span><string<\/span>> GetStreetNames()\n{\n    \/\/ Most common street names in UK, apparently :)\n    <\/span>yield return <\/span>"High Street"<\/span>; yield return <\/span>"Station Road"<\/span>; yield return <\/span>"Main Street"<\/span>;\n    yield return <\/span>"Church Street"<\/span>; yield return <\/span>"Victoria Road"<\/span>; yield return <\/span>"Park Road"<\/span>;\n    yield return <\/span>"Church Road"<\/span>; yield return <\/span>"London Road"<\/span>; yield return <\/span>"Manor Road"<\/span>;\n    yield return <\/span>"New Road"<\/span>; yield return <\/span>"Park Avenue"<\/span>; yield return <\/span>"Queens Road"<\/span>;\n    yield return <\/span>"Kings Road"<\/span>; yield return <\/span>"Church Lane"<\/span>; yield return <\/span>"Green Lane"<\/span>;\n    yield return <\/span>"Alexandra Road"<\/span>; yield return <\/span>"The Crescent"<\/span>; yield return <\/span>"George Street"<\/span>;\n    yield return <\/span>"Grange Road"<\/span>; yield return <\/span>"Main Road"<\/span>; yield return <\/span>"King Street"<\/span>;\n    yield return <\/span>"The Avenue"<\/span>; yield return <\/span>"New Street"<\/span>; yield return <\/span>"North Street"<\/span>;\n    yield return <\/span>"Victoria Street"<\/span>; yield return <\/span>"West Street"<\/span>; yield return <\/span>"Queen Street"<\/span>;\n    yield return <\/span>"Springfield Road"<\/span>; yield return <\/span>"Stanley Road"<\/span>; yield return <\/span>"Albert Road"<\/span>;\n    yield return <\/span>"Albert Street"<\/span>; yield return <\/span>"Park Lane"<\/span>; yield return <\/span>"Chapel Street"<\/span>;\n    yield return <\/span>"Highfield Road"<\/span>; yield return <\/span>"The Green"<\/span>; yield return <\/span>"Mill Lane"<\/span>;\n    yield return <\/span>"Broadway"<\/span>; yield return <\/span>"St. Johns Road"<\/span>; yield return <\/span>"Marlborough Road"<\/span>;\n    yield return <\/span>"Windsor Road"<\/span>; yield return <\/span>"Forest Road"<\/span>; yield return <\/span>"South Street"<\/span>;\n    yield return <\/span>"Warwick Road"<\/span>; yield return <\/span>"Grove Road"<\/span>; yield return <\/span>"Kingsway"<\/span>;\n    yield return <\/span>"York Road"<\/span>; yield return <\/span>"Woodlands Road"<\/span>; yield return <\/span>"Clarence Road"<\/span>;\n    yield return <\/span>"School Lane"<\/span>; yield return <\/span>"Cromwell Road"<\/span>;\n}<\/pre>\n
(I\u2019d wanted to use the top 50 USA street names, but they\u2019re far more boring as they\u2019re like: Fourth Street, Fifth Street, Oak Street. \"Smile\" ).<\/em><\/p>\n
There\u2019s a tiny class to represent a StreetName that\u2019s used later by the Dapper extension functions:<\/p>\n
public class <\/span>StreetName\n<\/span>{\n    public int <\/span>Id { get<\/span>; set<\/span>; }\n    public string <\/span>Name { get<\/span>; set<\/span>; }\n}<\/pre>\n
The full main function:<\/p>\n
static void <\/span>Main(string<\/span>[] args)\n{\n    const int <\/span>GroupSize = 8;\n\n    var <\/span>connectionString = CreateDatabase();\n    using <\/span>(var <\/span>db = new <\/span>SqlCeConnection<\/span>(connectionString))\n    {\n        db.Open();\n        for <\/span>(var <\/span>group = 0; ; group++)\n        {\n            Console<\/span>.WriteLine(string<\/span>.Format("== GROUP {0} =="<\/span>, group));\n            var <\/span>streets = db.Query<StreetName<\/span>>(@"SELECT * FROM StreetNames\n                               ORDER BY Id\n                               OFFSET @offset ROWS\n                               FETCH NEXT @rows ROWS ONLY"<\/span>,\n                                    new <\/span>{ offset = group * GroupSize, \n                                        rows = GroupSize });\n\n            foreach <\/span>(var <\/span>street in <\/span>streets)\n            {\n                Console<\/span>.WriteLine(string<\/span>.Format("{0} ({1})"<\/span>,\n                    street.Name, street.Id));\n            }\n            if <\/span>(streets.Count() < GroupSize) { break<\/span>; }\n        }\n    }\n\n    Console<\/span>.WriteLine("** Done **"<\/span>);\n    Console<\/span>.ReadKey();\n}<\/pre>\n
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.<\/p>\n
While the syntax isn\u2019t quite as slick as  \u201cEntity Framework\u201d by any means, the resulting run-time efficiency far makes up for that as far as I\u2019m concerned (and if you care about performance, you might want to re-evaluate your use of the EntityFramework as well).<\/p>\n
I took the photo above years ago at the Boeing Museum of Flight<\/a> in Seattle (which inspired me to look for videos). For fun, check out this video of a model<\/strong> SR-71 blackbird. It takes off around 2:21.<\/p>\n
\n
<\/param><\/object><\/div>\n
Model SR-71 Blackbird in flight.<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
If you\u2019re using SQL Server Compact Editition version 4 or higher, there\u2019s 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spay_email":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[4],"tags":[82,81,83,84],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pd5QIe-pL","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":1926,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1926","url_meta":{"origin":1597,"position":0},"title":"Using Sqlite and C# to determine if a specified table exists","date":"August 12, 2013","format":false,"excerpt":"I had need of a bit of code in C# to determine whether several tables in a Sqlite database had been created, so \u2026 public static class DbExtensions { \/\/\/ 
 \/\/\/ Determines whether the table exists \/\/\/ <\/summary> \/\/\/ Existing, opened, database connection<\/param> \/\/\/ The name of\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1170,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1170","url_meta":{"origin":1597,"position":1},"title":"WebMatrix and SQL Server Compact 4.0 Table Editing workaround","date":"February 2, 2011","format":false,"excerpt":"Occasionally, I encounter the following error when editing a\u00a0 SQL Server Compact 4.0 database table within WebMatrix. Alter table only allows columns to be added which can contain null values. The column cannot be added to the table because it does not allow null values. The issue arises when adding\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2011\/02\/image.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1730,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1730","url_meta":{"origin":1597,"position":2},"title":"How to find an element in a DataTemplate in WinRT\/XAML.","date":"September 7, 2012","format":false,"excerpt":"Here\u2019s one way to find a named element in a DataTemplate in XAML in Windows 8 XAML. You might try FindName to discover it doesn\u2019t work. That\u2019s because it\u2019s not recursive. So, I created a simple extension method to do the same thing: public static class FrameworkElementExtensions { public static\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"image","src":"https:\/\/i0.wp.com\/www.wiredprairie.us\/blog\/wp-content\/uploads\/2012\/09\/image.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1754,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1754","url_meta":{"origin":1597,"position":3},"title":"Nest Thermostat API using Node JS and Nest API Update","date":"October 9, 2012","format":false,"excerpt":"I\u2019ve been asked by a few people for more details on the API Nest Labs uses for their thermostats, especially regarding setting data (and not just polling). The API uses mostly JSON formatted data POSTed to their web servers. Authentication To authenticate, POST the username and password, encoded as form\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1345,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1345","url_meta":{"origin":1597,"position":4},"title":"Creating a simple Entity Reference system for Ember.js","date":"December 28, 2011","format":false,"excerpt":"I had some data stored in a structure similar to this: Nothing too fancy. A table of Gift(s) and a table of Person(s). Each gift had a foreign key relationship to a Person (the person who \u201cgave\u201d the gift). Since some people may give several gifts, I didn\u2019t want to\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1800,"url":"https:\/\/www.wiredprairie.us\/blog\/index.php\/archives\/1800","url_meta":{"origin":1597,"position":5},"title":"Named routing with Knockout, ASP.NET MVC, and AttributeRouting, from JavaScript","date":"January 24, 2013","format":false,"excerpt":"It's hard to describe exactly what I've built here, but I'm just throwing these pieces out on the Internet in case someone: a) finds them useful, or b) has a better solution. Goals: Named routes, with a wee bit of a Rails feel A Knockout friendly syntax for binding to\u2026","rel":"","context":"In "Coding"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1597"}],"collection":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/comments?post=1597"}],"version-history":[{"count":3,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1597\/revisions"}],"predecessor-version":[{"id":1600,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/posts\/1597\/revisions\/1600"}],"wp:attachment":[{"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/media?parent=1597"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/categories?post=1597"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wiredprairie.us\/blog\/index.php\/wpjson\/wp\/v2\/tags?post=1597"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}