Working with SQLite on .NET-2

 Published On

In the previous article of this series, I tried to understand creation of a SQLite database and to discover tools, libraries that wrap SQLite for .Net development. In this part of the series, I want to get a comprehensive map of connection string parameters for SQLite.

Every database connection needs a connection string which contains essential information. The content of this essential information may vary depending on the database you are using for the project. For example for an SQL SERVER connection string Server, Database Name are essential and almost always part of the connection string. For SQLite however, when you look on the web, you’ll realize that very few parameters are used on the connection string.

What are the parameters passed in the connection string

Let’s take a very common SQLite connection string that you can find almost everywhere on {:target=”_blank”}the web.

SQLiteConnection con = new SQLiteConnection("data source=TestDb.s3db;Version=3;")

What I call parameters are the attributes that you see in the connection string separated by “;”. For example Data Source or Version are parameters of the connection string. TestDb.s3db is the value of the Data Source parameter and 3 is the value of the Version parameter. Important point to note: I am using official DLL’s provided by SQLite team.

There are other libraries like Finisar.SqLite which will provide different and/or additional parameters in the connection string. Unfortunately, I didn’t find an exhaustive list of parameters that can be used in the connection string except this one which doesn’t hold enough details. To get a complete list, I decompiled the binary and took a look at the Open method in SQLiteConnection.cs file. You can also look at the comments present at the very beginning of the class description or look directly at the source code of System.Data.SQLite.SQLiteConnectionStringBuilder class.

Here is a synthetic table which resumes these parameters with some additional information. All the information is borrowed from the comments in the class definition and deducted from the decompiled code. Regarding the headers of the following table:

  • Property Name: Name of the property that is in System.Data.SQLite.SQLiteConnectionStringBuilder class
  • Display Name: Data annotation that decorates the property. This information is used in the connection string.
  • Description: Describes what is the property for and defines an enumeration of valid values when possible
  • Notes: Contains some remarks and also default values if there is one for the property.
Property Name Display Name Description Notes
DataSource DataSource This may be a file name, the string ":memory:", or any supported URI (starting with SQLite 3.7.7).Starting with release 1.0.86.0, in order to use more than one consecutive backslash. (e.g. for a UNC path), each of the adjoining backslash characters must be doubled (e.g. "\Network\Share\test.db" would become "\\Network\Share\test.db"). There is no default value, this is a must-have parameter!
Uri URI An alternate to the data source property null is the default value.
FullUri Full URI An alternate to the data source property that uses the SQLite URI syntax. null is the default value.
Version Version Version of the SQLite you use 3 is the default value for the library I am using at the moment I write this post.
UseUTF16Encoding Use UTF-16 Encoding True
False
False is the default value.
DateTimeFormat DateTime Format Ticks: Use the value of DateTime.Ticks.
ISO8601:Use the ISO-8601 format. Uses the "yyyy-MM-dd HH:mm:ss.FFFFFFFK" format for UTC and DateTime values and "yyyy-MM-dd HH:mm:ss.FFFFFFF" format for local DateTime values
JulianDay: The interval of time in days and fractions of a day since January 1, 4713 BC
UnixEpoch: The whole number of seconds since the Unix epoch (January 1, 1970).
InvariantCulture: Any culture-independent string value that the .NET Framework can interpret as a valid DateTime.
CurrentCulture:Any string value that the .NET Framework can interpret as a valid DateTime using the current culture.
ISO8601 is the default value.
DateTimeKind DateTime Kind Unspecified: Not specified as either UTC or local time.
Utc: The time represented is UTC.
Local:The time represented is local time.
Unspecified is the default value.
DateTimeFormatString DateTime Format String The exact DateTime format string to use for all formatting and parsing of all DateTime values for this connection. null is the default value.
BaseSchemaName Base Schema Name Some base data classes in the framework (e.g. those that build SQL queries dynamically) assume that an ADO.NET provider cannot support an alternate catalog (i.e. database) without supporting alternate schemas as well; however, SQLite does not fit into this model. Therefore, this value is used as a placeholder and removed prior to preparing any SQL statements that may contain it. sqlite_default_schema is the default value.
BinaryGUID Binary GUID True: Store GUID columns in binary form
False: Store GUID columns as text
True is the default value.
CacheSize Cache Size cache size in bytes 2000 is the default value.
Synchronous(-SyncMode-) Synchronous Normal: Normal file flushing behavior
Full: Full flushing after all writes
Off: Underlying OS flushes I/O's
Normal is the default value. In the comments that precede SQLiteConnection class definition, the default value is indicated as Full which should be a problem of comment update.
PageSize Page Size page size in bytes 1024 is the default value.
Password Password Using this parameter requires that the CryptoAPI based codec be enabled at compile-time for both the native interop assembly and the core managed assemblies; otherwise, using this parameter may result in an exception being thrown when attempting to open the connection. String.Empty is the default value.
HexPassword Hexadecimal Password Must contain a sequence of zero or more hexadecimal encoded byte values without a leading "0x" prefix. Using this parameter requires that the CryptoAPI based codec be enabled at compile-time for both the native interop assembly and the core managed assemblies; otherwise, using this parameter may result in an exception being thrown when attempting to open the connection. String.Empty is the default value.
Enlist Enlist Y: Automatically enlist in distributed transactions
N: No automatic enlistment
Y is the default value
Pooling Pooling True: Use connection pooling.
False: Do not use connection pooling.
False is the default value.
Warning: When using the default connection pool implementation,setting this property to True should be avoided by applications that make use of COM (either directly or indirectly) due to possible deadlocks that can occur during the finalization of some COM objects.
FailIfMissing Fail If Missing True: Don't create the database if it does not exist, throw an error instead
False: Automatically create the database if it does not exist
False is the default value.
MaxPageCount Maximum Page Count Limits the maximum number of pages (limits the size) of the database 0 is the default value to indicate there isn't any limit.
Legacy Format Legacy Format True: Use the more compatible legacy 3.x database format
False: Use the newer 3.3x database format which compresses numbers more effectively
False is the default value.
DefaultTimeout Default Timeout The default command timeout in seconds 30 is the default value
JournalMode Journal Mode Delete: Delete the journal file after a commit
Persist: Zero out and leave the journal file on disk after a commit
Off Disable the rollback journal entirely
Delete is the default value
ReadOnly Read Only True: Open the database for read only access
False: Open the database for normal read/write access
False is the default value
Max Pool Size The maximum number of connections for the given connection string that can be in the connection pool 100 is the default value
DefaultIsolationLevel Default Isolation Level The default transaction isolation level. The enum IsolationLevel is defined in the source code as the following:
Unspecified = -1
Chaos = 16
ReadUncommitted = 256
ReadCommitted = 4096
RepeatableRead = 65536
Serializable = 1048576
Snapshot = 16777216
Serializable is the default value.
Warning I strongly advice not to play with this.
ForeignKeys Foreign Keys True: Enable foreign key constraints
False: Disable foreign key constraints
False is the default value
Flags Flags Extra behavioral flags for the connection. See below the System.Data.SQLite.SQLiteConnectionFlags enumeration for possible values. System.Data.SQLite.SQLiteConnectionFlags.Default is the default value
SetDefaults Set Defaults True: Apply the default connection settings to the opened database.
False:Skip applying the default connection settings to the opened database.
True is the default value.
ToFullPath To Full Path True: Attempt to expand the data source file name to a fully qualified path before opening.
False: Skip attempting to expand the data source file name to a fully qualified path before opening.
True is the default value.
NoSharedFlags No Shared Flags True: Skip using the configured shared connection flags
False: Do not skip using the configured shared connection flags
False is the default value.

SQLiteConnectionFlags

[Flags]
public enum SQLiteConnectionFlags
{
    None = 0,
    LogPrepare = 1,
    LogPreBind = 2,
    LogBind = 4,
    LogCallbackException = 8,
    LogBackup = 16,
    NoExtensionFunctions = 32,
    BindUInt32AsInt64 = 64,
    BindAllAsText = 128,
    GetAllAsText = 256,
    NoLoadExtension = 512,
    NoCreateModule = 1024,
    NoBindFunctions = 2048,
    NoLogModule = 4096,
    LogModuleError = 8192,
    LogModuleException = 16384,
    TraceWarning = 32768,
    ConvertInvariantText = 65536,
    BindInvariantText = 131072,
    NoConnectionPool = 262144,
    UseConnectionPool = 524288,
    UseConnectionTypes = 1048576,
    NoGlobalTypes = 2097152,
    StickyHasRows = 4194304,
    StrictEnlistment = 8388608,
    MapIsolationLevels = 16777216,
    DetectTextAffinity = 33554432,
    DetectStringType = 67108864,
    NoConvertSettings = 134217728,
    BindAndGetAllAsText = GetAllAsText | BindAllAsText,
    ConvertAndBindInvariantText = BindInvariantText | ConvertInvariantText,
    BindAndGetAllAsInvariantText = BindAndGetAllAsText | BindInvariantText,
    ConvertAndBindAndGetAllAsInvariantText = BindAndGetAllAsInvariantText | ConvertInvariantText,
    LogAll = LogModuleException | LogModuleError | LogBackup | LogCallbackException | LogBind | LogPreBind | LogPrepare,
    Default = LogModuleException | LogCallbackException,
    DefaultAndLogAll = Default | LogModuleError | LogBackup | LogBind | LogPreBind | LogPrepare,
}

That’s was quite lecture for me and while reading the source code, I noticed some important points.

Display name vs parameter

When you try to create a new connection with your connection string, behind the doors, SQLite tries to parse it into a

SortedDictionary<string ,string>

using

SQLiteConnection.ParseConnectionString method. This method has 2 overloads:

private static SortedList<string, string> ParseConnectionString(string connectionString, bool parseViaFramework)
{
  if (!parseViaFramework)
     return SQLiteConnection.ParseConnectionString(connectionString);
  else
     return SQLiteConnection.ParseConnectionStringViaFramework(connectionString, false);
}

The following part will explain the way following method

SQLiteConnection.ParseConnectionString(string connectionString)

works:

  1. It splits the string using “;” as separator *
  2. Itt finds the position of “=”, from now on it has a left part like Data Source and right part like TestDb.s3db.
  3. For each part, it gets rid of the spaces on the left side and on the right side using of “=” using String.Trim method
  4. It adds the left side as key , the right side as value.
  5. It tries to find the key using the following method:
static internal string FindKey(SortedList<string, string> items, string key, string defValue)
{
  string ret;
  if (String.IsNullOrEmpty(key)) return defValue;
  if (items.TryGetValue(key, out ret)) return ret;
  if (items.TryGetValue(key.Replace(" ", String.Empty), out ret)) return ret;
  return defValue;
}

It is important to note that the lookup value is hardcoded in the Open method that calls FindKey method.

...
string str = SQLiteConnection.FindKey(sortedList, "Data Source", (string) null);

Given these steps all the followings would work -look at how DataSource parameter is formatted-.

SQLiteConnection con = new SQLiteConnection("data source=TestDb.s3db;Version=3;")
SQLiteConnection con = new SQLiteConnection("datasource=TestDb.s3db;Version=3;")
SQLiteConnection con = new SQLiteConnection("DataSource=TestDb.s3db;Version=3;")
SQLiteConnection con = new SQLiteConnection("Data Source=TestDb.s3db;Version=3;")
SQLiteConnection con = new SQLiteConnection("data Source=TestDb.s3db;Version=3;")
SQLiteConnection con = new SQLiteConnection("Data source=TestDb.s3db;Version=3;")

On the other hand the following would not work -an additional space between data and source-:

(SQLiteConnection con = new SQLiteConnection("Data  Source=TestDb.s3db;Version=3;")) // with an additional space

Boolean values of the parameters

If you look carefully on the table of connection parameters above, you’ll see that for boolean values “Y” , “N”, “True”, “False” are used. How is that managed ? Let’s take an example of a property:

[DisplayName("To Full Path")]
[Browsable(true)]
[DefaultValue(true)]
public bool ToFullPath
{
  get
  {
    object source;
    this.TryGetValue("tofullpath", out source);
    return SQLiteConvert.ToBoolean(source);
  }
  set
  {
    this["tofullpath"] = (object) (bool) (value ? 1 : 0);
  }
}

The key to this conversion is right under our eyes: SQLiteConvert.ToBoolean(source). This method checks whether the object we are sending is already a bool or not , if it is not, it calls another helper method.

public static bool ToBoolean(object source)
{
  if (source is bool)
    return (bool) source;
  else
    return SQLiteConvert.ToBoolean(SQLiteConvert.ToStringWithProvider(source, (IFormatProvider) CultureInfo.InvariantCulture));
}

We are close to find the secret, let’s find the source code of static bool ToBoolean(string source)

public static bool ToBoolean(string source)
{
  if (string.Compare(source, bool.TrueString, StringComparison.OrdinalIgnoreCase) == 0)
    return true;
  if (string.Compare(source, bool.FalseString, StringComparison.OrdinalIgnoreCase) == 0)
    return false;
  switch (source.ToLower(CultureInfo.InvariantCulture))
  {
    case "yes":
    case "y":
    case "1":
    case "on":
      return true;
    case "no":
    case "n":
    case "0":
    case "off":
      return false;
    default:
      throw new ArgumentException("source");
  }
}

Aha! That’s it. So all of the followings are accepted and will be understood by the system:

// to say false
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=false")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=False")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=no")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=n")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=0")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=off")
// to say true
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=true")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=True")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=yes")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=y")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=1")
SQLiteConnection con = new SQLiteConnection("data source  = TestDb.s3db;Version=3;FailIfMissing=on")

What’s next?

I believe I gathered some essential information here. Now I know how to create a database and how to find it. I can move forward. In the next article, I’ll try to learn how to do CRUD operations on the SQLite database.


Tags:   .net   c#   database   decompiling   sqlite   programming

Comments:

comments powered by Disqus

© 2017 - Mechanical Object. All rights reserved
Built using Jekyll