This tutorial describes how to connect to SQLite server.

In this tutorial:

• Requirements
• General Information
• Creating SQLiteConnection
• Opening Connection
• Closing Connection
• Modifying Connection
• Additional Information

Requirements

In order to connect to SQLite database you need dotConnect for SQLite installed and IDE running.

Note that if you do not use design-time (specifically, if you do not place the SQLiteConnection component from the toolbox on a form designer), you have to embed licensing information manually. This is described in the Licensing topic.

General Information

SQLite is an embedded relational database management system. SQLite engine and its interface are implemented in the one library (sqlite3.dll) that increases the speed of the query execution. dotConnect for SQLite places this library to the system directory during installation. So you can either specify the existing database file you want to work with to the SQLiteConnection object or let dotConnect for SQLite to create the necessary file if it does not exist yet.

Creating SQLiteConnection

Design-time creation

Note that design-time connection creation, described here, is applicable only to Windows Forms applications for Full .NET Framework.

The following assumes that you have IDE running, and you are currently focused on a form designer.

1. Open Toolbox and find SQLiteConnection component in dotConnect for SQLite Data tab.

2. Double-click the component. Note that new object appears on the designer underneath the form. If this is first time you create SQLiteConnection in this application, it is named sqLiteConnection1.

3. Click on the sqLiteConnection1 object and press F4 to focus on object's properties.

4. Set the ConnectionString property to "FailIfMissing=False;". This will allow dotConnect for SQLite to create the needed file. If you already have the database file, you may skip this step.

5. Specify the path to your database file in the DataSource property. E.g.: D:\TestApplication\database.db.

6. Note that as you assign values to these properties the ConnectionString property is automatically updated to reflect your settings. Now it contains something like Data Source=D:\TestApplication\database.db;FailIfMissing=False;.

Run-time creation

Same operations performed in runtime look as follows (note that you have to add references to Devart.Data.SQLite.dll and Devart.Data.dll assemblies for Full .NET Framework or install the Devart.Data.SQLite NuGet package for .NET Core):

using Devart.Data.SQLite;
...
SQLiteConnection sqLiteConnection1 = new SQLiteConnection();
sqLiteConnection1.ConnectionString = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";

Imports Devart.Data.SQLite
...
Dim sqLiteConnection1 As SQLiteConnection = New SQLiteConnection()
sqLiteConnection1.ConnectionString = "Data Source=D:\TestApplication\database.db;FailIfMissing=False;"

Using connection string builder

If you decide to setup a connection by assigning values to several properties, consider using the SQLiteConnectionStringBuilder class. It has all of the possible connection settings exposed as properties, thus allowing you to customize the connection at full extent. The following example demonstrates how to compose a more complex connection string:

SQLiteConnectionStringBuilder connSB = new SQLiteConnectionStringBuilder();
connSB.DataSource = @"D:\TestApplication\database.db";
connSB.FailIfMissing = false;
connSB.Locking = LockingMode.Exclusive;
connSB.AutoVacuum = AutoVacuumMode.Full;
connSB.ConnectionTimeout = 20;
SQLiteConnection sqLiteConnection1 = new SQLiteConnection(connSB.ConnectionString);

Dim connSB As SQLiteConnectionStringBuilder
connSB.DataSource = "D:\TestApplication\database.db"
connSB.FailIfMissing = false
connSB.Locking = LockingMode.Exclusive
connSB.AutoVacuum = AutoVacuumMode.Full
connSB.ConnectionTimeout = 20
Dim sqLiteConnection1 As SQLiteConnection(connSB.ConnectionString)

Note that in this example we used SQLiteConnection constructor that accepts connection string as argument.

For the information on arguments allowed in the connection string, refer to the description of the SQLiteConnection.ConnectionString property.

Opening Connection

Opening a connection is as simple as that:

sqLiteConnection1.Open();

sqLiteConnection1.Open()

Of course, sqLiteConnection1 must have a valid connection string assigned earlier. When you call Open, dotConnect for SQLite tries to find and open the database file. If any problem occurs it raises an exception with brief explanation on what is wrong. If no problem is encountered dotConnect for SQLite tries to establish the connection during ConnectionTimeout interval. Finally, when connection is established, the Open method returns and State property is changed to Open.

In design-time you can connect to server in few steps:

1. Right-click on sqLiteConnection1 object in designer.

2. Select Connect from the popup menu.

3. In the dialog window provide necessary logon information.

4. Click the Connect button to establish connection.

Or you can simply change the State property to Open in the Properties window to establish a connection using the current connection string.

Closing Connection

To close a connection call its Close method, or set its State property to Closed.

The following example summarizes aforementioned information and shows how to create, setup, open, use and then close the connection.

SQLiteConnection sqLiteConnection1 = new SQLiteConnection();
sqLiteConnection1.ConnectionString = @"Data Source=D:\TestApplication\database.db;FailIfMissing=False;";
sqLiteConnection1.Open();
MessageBox.Show(sqLiteConnection1.ServerVersion);
sqLiteConnection1.Close();

Dim sqLiteConnection1 As SQLiteConnection = New SQLiteConnection()
sqLiteConnection1.ConnectionString = "Data Source=D:\TestApplication\database.db;FailIfMissing=False;"
sqLiteConnection1.Open()
MessageBox.Show(sqLiteConnection1.ServerVersion)
sqLiteConnection1.Close()

The sample code connects to database, shows the version of the instance of SQLite and then closes the connection. This actually is rare usage, because in real applications connections are used by other objects like SQLiteCommand, SQLiteDataTable and others. For more information on this, please see the corresponding tutorials or the reference information.

Modifying Connection

You can modify connection by changing properties of SQLiteConnection object. Keep in mind that while some of the properties can be altered freely, most of them close connection when new value is assigned. For example, if you change DataSource property, it gets closed immediately, and you have to reopen it manually.

Additional Information

dotConnect for SQLite has wide set of features you can take advantage of. The following list enumerates some of them so you can explore the advanced techniques to achieve better performance, balance network load or enable additional capabilities.

• Asynchronous connection opening
• Connection pooling (refer to MSDN documentation for information about connection pooling)

See Also

Getting Started  | SQLiteConnection Class