SQLite Synchronization with Office 365, SharePoint, Dynamics and 150+

 

SQLite data can be integrated and synchronized codeless with various external systems, on premises or in the cloud, using the Layer2 Cloud Connector. You will find a selection of supported systems and applications like Microsoft Office 365, SharePoint, CRM Online and 150+ more here.​​​​​

 

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world. SQLite data can be integrated with 150+ other systems and apps codeless using the Layer Cloud Connector.

Benefits of SQLite Synchronization and Integration

 

SQLite data integration via Layer2 Cloud Connector has the following features and benefits:

 

  • Connect and sync your SQLite data with 150+ other data sources, e.g. native SharePoint lists in Office 365 or on-premises, keeping them always up-to-date.
  • High speed of implementation: No programming or tools required. Predefined sample connection templates included. Setup in a few minutes guided by wizards. Remote setup support (30 minutes are free).
  • Low risk in project: No installation or customizing for the SQLite data source and the data destination required. Just install the connector at a Windows server or client in your network, in the cloud, or even at your laptop.
  • Highest Security: Connection via secure web services (SSL). Disconnected systems: A connection is only required for data exchange – not for data usage. Users do not have direct access to external corporate data sources. You don't have to open your network for the Internet. The application runs where your data resides today - on a server or client inside your corporate network.
  • Highest performance: Can manage very large databases, SharePoint lists and libraries. No SharePoint 5,000 items limit. Can scale-out to multiple servers or to the Microsoft Azure Cloud, if required. No bulk-import as with other tools, data changes are processed only (full CRUD).
  • Fully compatible with 3rd-party data providers: Data providers of the application vendors (e.g. Oracle, mySQL, IBM), 3rd-party data providers, or included Layer2 data providers can be used to access 150+ data sources.
  • Virtually any data sources supported: ODBC, OLEDB, OData, SOAP, Microsoft .NET based providers, Files (Excel, XML, CSV, Text), RSS / XML feeds, SQL databases like MS SQLSQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix; Notes, SharePoint, Office 365 (lists and libraries), local file systemcloud-based file storesExchangeActive DirectoryDynamics NAV/CRM, Navision, SAP, and many more. More complex data sources, REST-based web services, custom COM, or .NET objects can be connected using 3rd-party add-ons. Cloud-to-cloud connections, e.g. to salesforce.comTwitter, Facebook, Google, Amazon, BOX.NET and others are also supported.
  • Layer2 CSOM Data Provider included: You can connect and sync your SQLite data with SharePoint lists and libraries on-premise or in the Office 365 / OneDrive for Business cloud.
  • Layer2 File System Data Provider included: You can sync local files or file server shares with SharePoint Online or on-premises libraries, e.g. for backup, collaboration or mobile access. Optionally, you can sync metadata only - not the file itself. You can link to, for example, your own FTP/HTTP, to Azure, BOX, or SkyDrive / OneDrive to save space or use low-cost Office 365 plans. You can update file metadata based on SQL Server queries.
  • Layer2 OData Data Provider included: You can connect your SQLite data to all OData sources, e.g. Microsoft CRM/ERP or SAP via NetWeaver.
  • Layer2 XML Data Provider included: You can connect your SQLite data to any XML-based files and web requests, and filter results by using XPath query language.
  • Very flexible sync options: Uni- and bi-directional sync is supported to and from SQLite. Execute a specific connection scheduled in background (Windows Service) or on demand (e.g. by command line). Sync all data or pre-selected subsets only. Select source and target folders, make use of additional no-delete, no-insert, no-update options. Field mapping and data type conversion included.
  • Process integration: Raise change notifications and start workflows in the cloud, triggered on external SQLite data changes. Find your data changes in Delve via Office Graph. All features of native SharePoint lists and libraries are supported, e.g. views, sorting, filtering, grouping, search, mobile and offline access, social features like tagging and enterprise metadata, RSS feeds, change notifications per email, or workflows in the cloud on external data change. You can also add your own business logic.
  • Mobile and offline access: You can sync database queries with Office 365 / SharePoint Online for data access from everywhere, with any device, at any time - also offline with write-back of changes.
  • Additional unmapped "SharePoint-only" columns can be created: Ranking, rating, managed metadata, comments or file attachments.
  • Data aggregation: Multiple connections can combine several different data queries into one list.
  • Flexible logging and alerts: You can log to files, databases, and Windows Event Log via NLOG.
  • Free Shareware Edition: You can test the free Shareware Edition as long as you want or you can use in production with limited features. You can upgrade the Shareware Edition to a licensed version at any time without new installation.
  • Flexible, cost-effective licensing: Personal Edition, List and Table Edition and Enterprise Edition, depending on requirements. Licensing per installation (no user CALs, not bound to any SharePoint server or site, just a one-time fee annual subscription - no data volume-related costs). Easy to setup and maintain.

SQLite Specific Integration and Configuration Settings

 

In the Layer2 Cloud Connector,the data source or destination entity must be configured as follows to connect to SQLite:

 

Example Configuration of SQLite

Fig. SQLite example connection in the Layer2 Cloud Connector.

 

 

Please note the following about settings, features and possible issues.

 

  • You need to have the System.Data.SQLite ADO .NET provider installed on the computer that runs the Layer2 Cloud Connector. You can find the component on the System.Data.SQLite Downloads page.
  • Install the appropriate bit version of the System.Data.SQLite provider. It must match the same bit architecture as the Cloud Connector version installed (32- or 64-bit) AND be the same .NET version as the Cloud Connector. The proper selection and installation of the provider is more complex than most. See the SQLite Integration - Known Issues & Workarounds below for more specific information.
  • Please select the "SQLite Data Provider" from the list of installed providers.
  • You can make use of standard connection string for SQLite, for example:
    Data Source=C:\myDatabase.db;Version=3;
  • You can make use of all SQL queries your data provider supports. See the SQLite Documentation for more information.
  • You can make use of aliases in query to force auto-mapping in the Cloud Connector. See the SQLite Documentation for more information.
  • The primary key is normally set automatically, if available.
  • Bi-directional sync is generally supported. You query must be updatable for this (note that most joins are not). You will also need write access rights for this.
  • For more information about SQLite, see the User Documentation on their site.

 

Do you have any issues to connect? Please contact [email protected] for next steps.

SQLite Integration - Known Issues & Workarounds

 

For SQLite Integration via Layer2 Cloud Connector we have found the following issues and workarounds:

 

  • Installing the System.Data.SQLite data provider is a bit more complicated that most .NET providers. If you don't install the right item (the bundle installer for the right .NET framework), then it will not show up on the list of providers within the Layer2 Cloud Connector. Below is the list of proper versions to download for each version of the Cloud Connector you could have installed. 

    64-bit .NET 4+: sqlite-netFx40-setup-bundle-x64-2010-1.0.1xxxx.exe

     

    32-bit .NET 4+: sqlite-netFx40-setup-bundle-x86-2010-1.0.1xxxx.exe

     

    64-bit .NET 3.5: sqlite-netFx35-setup-bundle-x64-2008-1.0.1xxxx.exe

     

    32-bit .NET 3.5: sqlite-netFx35-setup-bundle-x86-2008-1.0.1xxxx.exe

  • During installation, you MUST select the option to install the provider to the "Global Assembly Cache" for it to show up in the provider list.

 

 

 

SQLite Setup Screenshot

 

 

  • You may need to manually add the <DbProviderFactories> reference to the provider to your machine.config files. The exact text you need to add is in the ReadMe.htm file include with the provider, so you should reference that for the proper wording. This was most commonly a problem with the 64-bit version of the provider, so the proper machine.config file to add this too would be this one: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

 

If you have issues with the provider installation, as it is tricker than most, please contact [email protected] for further guidance.

Step-by-Step Intros For Specific SQLite Integration Scenarios

 

​Just some ideas about systems and apps to connect and sync:

 

  • Connect and sync SQLite data queries to SharePoint lists, on-prem or in Office 365.
  • Connect and sync SQLite data queries with Dynamics CRM or ERP via OData.
  • Sync with 3rd party tools based on SQL databases like Oracle, mySQL, or Microsoft SQL Server.
  • Push SQLite data to Microsoft Exchange on-premises or Exchange Online for mobile and offline access on any device (BYOD).

 

Can't find the scenario you are looking for? Please contact [email protected] for help.

 Ready to go next steps?

Layer2 leading solutions product regsitration icon: a grey square with a big orange pen symbol.

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Contact Us Icon for Layer2 leading solutions

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.