Alright – so we are now ready to get started in earnest. The first thing we need to do when connecting to a database is setup the connection string. This article covers the various connection string parameters available for the .Net version for SQLite.

Most Simple – Data Source
Format: “Data Source=[DBFileName]”
Example: @”Data Source=C:\CSharpHackerDemo.db3″

This connection string will create the database if it doesnt already exist without notifying you. For some utility functions this may be exactly what is desired, in some cases you want to know your DB doesnt exist!

If you want to programatically create the file intentionally, presumably if  “!File.Exists()” you can do:

SQLiteConnection.CreateFile(@"C:\CSharpHackerDemo.db3");

Only Open if it exists – FailIfMissing
Format: “Data Source=[DBFileName];FailIfMissing=True;”
Example: @”Data Source=C:\CSharpHackerDemo.db3;FailIfMissing=True;”

With this connection string if the DB file is not present the open will fail.

Secure/Encrypt the DB – Password
Format: “Data Source=[DBFileName];Password=[123456789];”
Example: @”Data Source=C:\CSharpHackerDemo.db3;Password=[123456789];”

With this connection string if the DB file will be created or opened using the password as the encryption key.

Other options:

• ReadOnly – When enabled, the database will be opened for read-only access and writing will be disabled. Default False.
• CacheSize – Sets the cache size for the connection. Default value is 2000 bytes. Under the covers this calls “PRAGMA cache_size={0}”.
• PageSize – Sets the database page size. Under the covers this calls “PRAGMA page_size={0}”. Default is 1024 bytes.
• MaxPageCount – Sets the maximum number of pages the database may hold. Default is 0 which is unrestricted. Under the covers this calls “PRAGMA max_page_count={0}”.
• DateTimeFormat - Sets the datetime format for the connection. Default is ISO8601
  • Ticks - Using ticks is not recommended and is not well supported with LINQ.
  • ISO8601 - Default format for this provider.
  • JulianDay - JulianDay format, which is what SQLite uses internally
• JournalMode - Determines how SQLite handles the transaction journal file.  Under the covers this calls “PRAGMA journal_mode={0}”. Default “Delete”
  • Delete - default mode, this causes SQLite to create and destroy the journal file as-needed.
  • Persist - When this is set, SQLite will keep the journal file even after a transaction has completed.  It’s contents will be erased, and the journal re-used as often as needed.  If it is deleted, it will be recreated the next time it is needed.
  • Off - This option disables the rollback journal entirely.  Interrupted transactions or a program crash can cause database corruption in this mode!
• DefaultIsolationLevel - Sets the default isolation level for transactions on the connection. Default “Serializable”. Note this is a standard .Net “System.Data.IsolationLevel” type. Do not even try to change this . Only “Serializable” and “ReadCommitted” can be used, all others will throw a “NotSupportedException”. However for completeness the full list and meanings are outlined below:
  • Serializable - A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
  • ReadUncommitted – A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
  • ReadCommitted - Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
  • RepeatableRead - Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
  • Snapshot - Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.
  • Chaos - The pending changes from more highly isolated transactions cannot be overwritten.
  • Unspecified - A different isolation level than the one specified is being used, but the level cannot be determined.
• Version – Sets the default version of the SQLite engine to instantiate.  Currently the only valid value is 3, indicating version 3 of the SQLite library.
• Synchronous – Sets the synchronization mode (file flushing) of the connection string.  Default is “Normal”.  Under the covers this calls “PRAGMA synchronous={0}”. Supported values are:
  • Normal – Normal file flushing at critical sections of the code
  • Full – Full file flushing after every write operation
  • Off – Use the default operating system’s file flushing, SQLite does not explicitly flush the file buffers after writing
• UseUTF16Encoding – Boolean indicator setting the encoding for the connection.  The default is “False” which indicates UTF-8 encoding.
• Pooling – Sets whether or not to use connection pooling.  The default is “False”
• BinaryGUID - Sets to or not to store GUID’s in binary format.  The default is True which saves space in the database.
• Deprecated Uri – depreciated call to the data source property.
• DefaultTimeout – sets the default command timeout for newly-created commands.  This is especially useful for commands used internally such as inside a SQLiteTransaction, where setting the timeout is not possible. Time is in seconds, the default is 30 seconds.
• Enlist – Determines whether or not the connection will automatically participate in the current distributed transaction (if one exists). Default is True.
• LegacyFormat – If enabled, uses the legacy 3.xx format for maximum compatibility, but results in larger database sizes. Default is False. Under the covers this calls “PRAGMA legacy_file_format={0}”.

So this is the connection string options available on the latest release. The interesting part is how I found this – in the source code (so at least now you dont need to do that part). However the next blog will be how to build the provider to help you diagnose and understand any issues you come across.

Series Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

 SQLiteConnection.CreateFile("c:\\mydatabasefile.db3"); SQLiteConnection.CreateFile("c:\\mydatabasefile.db3");

This entry was posted on Thursday, July 2nd, 2009 at 1:40 am and is filed under Development, SQLite. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.