If you need to choose which data provider for SQLite to use with dotConnect Universal, we recommend using dotConnect for SQLite because of its obvious advantages. This article provides a brief overview of dotConnect for SQLite, describes some useful features and helps you get started quickly.

• Overview
• Compatibility
• Deployment
• Typical Connection String
• Quick Start Steps
• Special Features
• UniSQL Notes
• UniDbType Map
• Additional Information

Overview

dotConnect for SQLite is a full-featured 100% managed ADO.NET data provider for the .NET Framework and the .NET Compact Framework. dotConnect for SQLite is acknowledged as the leading ADO.NET data provider for SQLite, and it has many advantages over any rival product. The main features of dotConnect for SQLite are:

• High performance
• Easy deployment
• Comprehensive support for the latest versions of SQLite server
• Advanced design-time editors and wizards
• All SQLite data types support

The full list of dotConnect for SQLite features can be found on the product page.

Developer Edition of dotConnect Universal includes dotConnect for SQLite as a bundled provider. Trial Edition includes trial dotConnect for SQLite Data Provider as well. For Express Edition of dotConnect Universal, dotConnect for SQLite should be purchased and installed separately.

Compatibility

dotConnect Universal is compatible with dotConnect for SQLite version 2.00 and above.

Deployment

To deploy applications written with dotConnect Universal, copy the run-time assemblies Devart.Data.Universal.dll and Devart.Data.Universal.SQLite.dll to target machine. If you are using the standalone (not bundled) dotConnect for SQLite Data Provider, you will also have to deploy its Devart.Data.SQLite.dll assembly.

These assemblies can be registered at the Global Assembly Cache (GAC) for the appropriate .NET Framework or placed in the folder of your application.

For web projects, you may also need to deploy the Devart.Data.Universal.Web.dll, Devart.Data.SQLite.Web.dll and App_Licenses.dll assemblies. The *.Web.dll assemblies are required for using the ASP.NET Provider Model and the App_Licenses.dll assembly is a part of licensing mechanism.

Web projects are usually run as partially trusted code. The following permissions are required by dotConnect Universal and dotConnect for SQLite: medium trust plus ReflectionPermission.

Connection String

Suppose you want to connect in Direct mode to the D:\TestApplication\database.db database and want to create the database in case it does not exist. In this case your connection string would look like this:

Data Source=D:\TestApplication\database.db;FailIfMissing=False;

The following table enumerates most important connection string parameters.

Quick Start Steps

To get up and running quickly, you have to perform the following steps:

1. Embed licensing information into your application. If you use bundled dotConnect for SQLite version, you need only dotConnect Universal licensing. If you use a standalone dotConnect for SQLite installation, embed the licensing information about both dotConnect Universal and dotConnect for SQLite data providers. For more details on licensing refer to the Licensing article. For bundled providers it may be enough to drop a UniConnection component onto a form designer to embed the licensing information.
2. Create a UniConnection object.
3. Set the ConnectionString property of the UniConnection object to a string containing the part Provider=SQLite.
4. Create a UniCommand object and link it to the UniConnection object.
5. Open the connection.

That's all you need to start executing queries on the server. Below is a code fragment that illustrates steps 2-5. It also shows how to perform INSERT and SELECT operations.

UniConnection connection = new UniConnection("Provider=SQLite;DataSource=mydatabase.db");
try {
  UniCommand cmd = connection.CreateCommand();
  cmd.CommandText = "INSERT INTO dept (DeptNo, Dname, Loc) VALUES (:DeptNo, :DName, :Loc)";
  UniParameter parameter = cmd.Parameters.Add("DeptNo", UniDbType.Int);
  parameter.Value = 150;
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar);
  parameter.Value = "DEPTNAME";
  cmd.Parameters.Add(parameter);
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar);
  parameter.Value = "LOCATION";
  cmd.Parameters.Add(parameter);
  connection.Open();
  cmd.ExecuteNonQuery();
}
finally {
  connection.Close();
}

...

DataSet dataSet = new DataSet();
UniDataAdapter da = new UniDataAdapter("SELECT * FROM dept", "Provider=SQLite;DataSource=mydatabase.db");
da.Fill(dataSet, "Dept");

Dim connection As Devart.Data.Universal.UniConnection = New _
  Devart.Data.Universal.UniConnection("Provider=SQLite;DataSource=mydatabase.db")
Try
  Dim cmd As UniCommand = connection.CreateCommand()
  cmd.CommandText = "INSERT INTO dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, :Loc)"
  Dim parameter As Devart.Data.Universal.UniParameter = cmd.Parameters.Add("DEPTNO", UniDbType.Int)
  parameter.Value = 150
  parameter = cmd.Parameters.Add("DName", UniDbType.VarChar)
  parameter.Value = "DEPTNAME"
  parameter = cmd.Parameters.Add("Loc", UniDbType.VarChar)
  parameter.Value = "LOCATION"
  connection.Open()
  cmd.ExecuteNonQuery()
Finally
  connection.Close()
End Try

...

Dim dataSet As DataSet = New DataSet()
Dim da As UniDataAdapter = New UniDataAdapter("Provider=SQLite;DataSource=mydatabase.db")
da.Fill(dataSet, "Dept")

UniSQL Notes

dotConnect for SQLite does not support the UniSQL except the IF operator. Use SQLite predefined macro to build conditional UniSQL statements. For more information refer to the UniSQL topic.

UniDbType Map

The following table shows how the UniDbType enumeration maps to the SQLite database type.

The national character encoding (NChar, NClob types) in the fields is not supported by SQLite server. However, you can achieve same effect with the Unicode=true connection string parameter.

Additional Information

For more information on dotConnect for SQLite Data Provider please visit the following locations:

• Product home page at www.devart.com/dotconnect/sqlite
• Product downloads page at www.devart.com/dotconnect/sqlite/download.html to obtain the latest documentation
• Discussion forum at https://forums.devart.com/viewforum.php?f=29 to ask for help or report a bug

See Also

Using dotConnect Universal with SQL Client  | Using dotConnect Universal with dotConnect for Oracle  | Using dotConnect Universal with dotConnect for PostgreSQL  | Using dotConnect Universal with dotConnect for MySQL