{"id":899,"date":"2016-01-25T20:22:04","date_gmt":"2016-01-25T10:22:04","guid":{"rendered":"http:\/\/www.moneystock.net\/wp_e\/?p=899"},"modified":"2016-08-06T00:19:02","modified_gmt":"2016-08-05T14:19:02","slug":"sqlite-in-net","status":"publish","type":"post","link":"https:\/\/moneystock.net\/wp_e\/2016\/01\/25\/sqlite-in-net\/","title":{"rendered":"SQLite in .Net"},"content":{"rendered":"

After using SQLite for a while, a few conclusions that I had were<\/p>\n

    \n
  1. SQLite is quite stable enough to be used in corporate environment.<\/li>\n
  2. SQLite is faster than SQLCE.<\/li>\n
  3. For a SQLite admin, use the Firefox addon – \u00a0https:\/\/addons.mozilla.org\/de\/firefox\/addon\/sqlite-manager\/<\/a><\/li>\n
  4. SQL Server Compact Toolbox will be useful:\u00a0https:\/\/visualstudiogallery.msdn.microsoft.com\/0e313dfd-be80-4afb-b5e9-6e74d369f7a1<\/a><\/li>\n
  5. Both SQLite and SQLCE cannot be used in a condition that requires to write in DB concurrently. To make sure that an application does not access the DB concurrently, ‘lock’ statement was used.<\/li>\n
  6. Both can be used with Entity Framework, which is nice.<\/li>\n<\/ol>\n

    Installation is relatively easy, but set up could be confused as some of tutorial does not work as it should be due to the version difference (?) and NuGet didn’t properly updated config file in my case, so I had to update it manually.<\/p>\n

    I’ve current set up both SQLite and SQLCE with the versions as below.<\/p>\n

    Entity Environment 6.0
    \nData.Dqlite.EF6 1.0.98.0
    \nSystem.Data.SqlServerCe 4.0<\/p>\n

    Below is the configuration for SQLite and SQLCE to be used together.<\/p>\n

    <connectionStrings>\r\n <add name=\"SQLiteLogContext\" connectionString=\"Data Source=D:\\Stash\\Projects\\AU\\WestPac\\CustomWebService\\WcfCustomService\\Log\\RTWS.sqlite\" providerName=\"System.Data.SQLite.EF6\" \/>\r\n <add name=\"SQLCELogContext\" providerName=\"System.Data.SqlServerCe.4.0\" connectionString=\"Data Source=D:\\Stash\\Projects\\AU\\WestPac\\CustomWebService\\WcfCustomService\\Log\\RTWS.sdf\" \/>\r\n<\/connectionStrings> \r\n<runtime>\r\n <assemblyBinding xmlns=\"urn:schemas-microsoft-com:asm.v1\">\r\n <dependentAssembly>\r\n <assemblyIdentity name=\"EntityFramework\" publicKeyToken=\"b77a5c561934e089\" culture=\"neutral\" \/>\r\n <bindingRedirect oldVersion=\"0.0.0.0-4.4.0.0\" newVersion=\"4.4.0.0\" \/>\r\n <\/dependentAssembly>\r\n <\/assemblyBinding>\r\n<\/runtime>\r\n<entityFramework>\r\n <defaultConnectionFactory type=\"System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework\">\r\n <parameters>\r\n <parameter value=\"System.Data.SqlServerCe.4.0\" \/>\r\n <\/parameters>\r\n <\/defaultConnectionFactory>\r\n <providers>\r\n <provider invariantName=\"System.Data.SQLite.EF6\" type=\"System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6\" \/>\r\n <provider invariantName=\"System.Data.SqlClient\" type=\"System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer\" \/>\r\n <provider invariantName=\"System.Data.SQLite\" type=\"System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.98.0, Culture=neutral\" \/>\r\n <provider invariantName=\"System.Data.SqlServerCe.4.0\" type=\"System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact\" \/>\r\n <\/providers>\r\n<\/entityFramework>\r\n<system.data>\r\n <DbProviderFactories>\r\n <remove invariant=\"System.Data.SQLite.EF6\" \/>\r\n <add name=\"SQLite Data Provider (Entity Framework 6)\" invariant=\"System.Data.SQLite.EF6\" description=\".NET Framework Data Provider for SQLite (Entity Framework 6)\" type=\"System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6\" \/>\r\n <remove invariant=\"System.Data.SqlServerCe.4.0\" \/>\r\n <add name=\"Microsoft SQL Server Compact Data Provider 4.0\" invariant=\"System.Data.SqlServerCe.4.0\" description=\".NET Framework Data Provider for Microsoft SQL Server Compact\" type=\"System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91\" \/>\r\n <\/DbProviderFactories>\r\n<\/system.data><\/pre>\n
    Useful links regarding SQLite in .Net C#<\/p>\n
    Tutorial<\/p>\n
    http:\/\/www.tutorialspoint.com\/<\/a><\/p>\n
    http:\/\/www.codeproject.com\/Articles\/236918\/Using-SQLite-embedded-database-with-entity-framewo<\/a><\/p>\n
    Good and concise article<\/p>\n
    https:\/\/damienbod.wordpress.com\/2013\/11\/14\/using-sqlite-with-net\/<\/a><\/p>\n
    Best example<\/p>\n
    https:\/\/damienbod.wordpress.com\/2013\/11\/18\/using-sqlite-with-entity-framework-6-and-the-repository-pattern\/<\/a><\/p>\n
    Get VS13 DB designer with SQLite<\/p>\n
    http:\/\/stackoverflow.com\/questions\/25089346\/database-first-create-entity-framework-6-1-1-model-using-system-data-sqlite-1-0<\/a><\/p>\n
    Setting for Performance<\/p>\n
    PRAGMA main.page_size = <\/span>4096<\/span>;
    \nPRAGMA main.cache_size=<\/span>10000<\/span>;<\/span><\/p>\n
    PRAGMA main.cache_size=<\/span>5000<\/span>;<\/span><\/p>\n
    PRAGMA main.locking_mode=EXCLUSIVE;
    \nPRAGMA main.synchronous=NORMAL;
    \nPRAGMA main.journal_mode=WAL;<\/span><\/p>\n
    Pasted from <http:\/\/stackoverflow.com\/questions\/784173\/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file<\/a>><\/p>\n
    Keep the max size of database<\/p>\n
    http:\/\/stackoverflow.com\/questions\/5987042\/how-do-you-efficiently-trim-an-sqlite-database-down-to-a-given-file-size<\/a><\/p>\n
    SQLite Dispose or Close issue<\/p>\n
    http:\/\/stackoverflow.com\/questions\/8511901\/system-data-sqlite-close-not-releasing-database-file<\/a><\/p>\n
    http:\/\/stackoverflow.com\/questions\/12532729\/sqlite-keeps-the-database-locked-even-after-the-connection-is-closed\/12679840#12679840<\/a><\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    After using SQLite for a while, a few conclusions that I had were SQLite is quite stable enough to be used in corporate environment. SQLite is faster than SQLCE. For a SQLite admin, use the Firefox addon – \u00a0https:\/\/addons.mozilla.org\/de\/firefox\/addon\/sqlite-manager\/ SQL Server Compact Toolbox will be useful:\u00a0https:\/\/visualstudiogallery.msdn.microsoft.com\/0e313dfd-be80-4afb-b5e9-6e74d369f7a1 Both SQLite and SQLCE cannot be used in a… Continue reading SQLite in .Net<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[128,203],"tags":[184,95,100,210,209],"class_list":["post-899","post","type-post","status-publish","format-standard","hentry","category-db","category-library","tag-net","tag-c","tag-entity-framework","tag-sqlce","tag-sqlite","entry"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/899","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/comments?post=899"}],"version-history":[{"count":6,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/899\/revisions"}],"predecessor-version":[{"id":974,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/posts\/899\/revisions\/974"}],"wp:attachment":[{"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/media?parent=899"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/categories?post=899"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/moneystock.net\/wp_e\/wp-json\/wp\/v2\/tags?post=899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}