.NET Core: Introduction To Microsoft.Data.Sqlite

Ranjan Dailata
Feb 20, 2016
90.7k
0
6
Article

“Microsoft.Data.Sqlite“, is an open source library and is also available as NuGet package. Here’s the Github source code for reference.

The good thing about this library is, it’s build for .NET Core, meaning you can develop and run applications on Windows and non-Windows platform (Mac, Linux) supporting .NET Core Runtime.

Here’s the quick definition about SQLite from the official website.

“SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world”.

SQLite is a very popular, lightweight, and open source database engine and has gained industry popularity. That’s one reason I believe Microsoft wanted to come-up with an official .NET Core based library.

At this point, the .NET Core is evolving. It’s still in pre-release mode. Especially when it comes to “Microsoft.Data.Sqlite,” it supports basic functionalities for managing data in-memory or file system. In the near future, we can expect some changes.

The primary purpose or intention of writing this article is to provide an introduction and usage about “SQLite” library that Microsoft is currently building. Since it’s at the early stage of development, we cannot expect a full documentation. However, I was able to easily understand by taking a look into the source code and unit tests.

Prerequisite

Previous knowledge and understanding of ADO.NET.
Make sure to install .NET Core. Please follow the link if you want install the same on your Window machine.

Using the Code

Let us see with an example to understand the usage of the Microsoft.Data.Sqlite library. We are going to build a tiny cross-platform console application to demonstrate the usage of the SQLite library.

We are going to perform “CRUD” operation on “User” table. Here’s the code snippet for our “User” entity.

public class User
{
public int Id
{
get;
set;
}
public string Username
{
get;
set;
}
publicstring Email
{
get;
set;
}
public string Password
{
get;
set;
}
}

As I said earlier, you can store all your data in-memory or in file system. We will be focusing on storing in-memory. Below is the connection string for the same.

string connectionString = "Data Source=:memory:";

Let us take a look into the ‘Main’ code to understand the things what we are going to accomplish. We are building a simple user repository so we can encapsulate the “CRUD” operation logic with it.

Below is the code snippet of our “Main” program.

public staticvoid Main(string[] args)
{
string connectionString = "Data Source=:memory:";
DbConnetionTypedatabaseType = DbConnetionType.Sqlite;
using(UserRepositoryuserRepository =
new UserRepository(connectionString, databaseType))
{
Console.WriteLine("Creating user table\n");
userRepository.CreateUserTable();
Console.WriteLine("Inserting data to user table\n");
userRepository.InsertIntoUserTable();
Console.WriteLine("Selecting data\n");
userRepository.SelectFromUserTable();
Console.WriteLine("\nGet User By ID: 1\n");
var user = userRepository.GetUserdById(1);
if (user != null) {
Console.WriteLine("User Name: {0}", user.Username);
Console.WriteLine("Email: {0}", user.Email);
}
Console.WriteLine("\nDeleting data\n");
userRepository.DeleteFromUserTable();
Console.WriteLine("Selecting data\n");
userRepository.SelectFromUserTable();
Console.WriteLine("Inserting multiple data to user table\n");
userRepository.InsertMultipleWithTransaction();
Console.WriteLine("Selecting data\n");
userRepository.SelectFromUserTable();
}
Console.ReadLine();
}

Before we dig into the repository code, there are few basic things we have to code. The below “RC1-final” has the DBConnection extensions as internal. Hence, we won’t be able to access the extension methods within our application as they are private extensions.

"Microsoft.Data.Sqlite": "1.0.0-rc1-final"

So let us reuse and build our own DBConnection Extension. Below is the code snippet for the same. The “ExecuteNonQuery” method is used for insert/update/delete. The ExecuteScalar returns a single value. The “ExecuteReader” is used when you want to fetch some data from a database. All these methods behave similaly to the ones we have in ADO.NET. The below snapshot shows the current .NET framework DbConnection classes. SqliteConnection is something new which is included as part of the “Microsoft.Data.Sqlite” assembly.

Reference:

DbConnection Class

// Reused and Modified Code - https://github.com/aspnet/Microsoft.Data.Sqlite/blob/dev/src/Microsoft.Data.Sqlite/Utilities/DbConnectionExtensions.cs
public static class DbConnectionExtensions
{
public static int ExecuteNonQuery(thisDbConnection connection,
string commandText, int timeout = 30)
{
var command = connection.CreateCommand();
command.CommandTimeout = timeout;
command.CommandText = commandText;
return command.ExecuteNonQuery();
}
public static TExecuteScalar < T > (thisDbConnection connection,
stringcommandText, int timeout = 30) =>
(T) connection.ExecuteScalar(commandText, timeout);
private static objectExecuteScalar(thisDbConnection connection,
string commandText, int timeout)
{
var command = connection.CreateCommand();
command.CommandTimeout = timeout;
command.CommandText = commandText;
returncommand.ExecuteScalar();
}
public static DbDataReader ExecuteReader(thisDbConnection connection,
string commandText)
{
var command = connection.CreateCommand();
command.CommandText = commandText;
return command.ExecuteReader();
}
}

Let us code few more helper classes: one for getting the connection instance and the other for building the parameter instance. Below is the code snippet of ConnectionHelper class. We are coming up without own DbConnectionType. For now, let us have one type – “Sqlite”. The “GetDbConnection” method takes a connection string and DbConnectionType as a parameter and returns a connection instance based on the specified type.

public enum DbConnetionType
{
Sqlite
}
public class ConnectionHelper
{
public DbConnectionGetDbConnection(stringconnectionString,
DbConnectionType type)
{
switch (type)
{
caseDbConnetionType.Sqlite:
return newSqliteConnection(connectionString);
default:
return null;
}
}
}

Below is the code snippet of ParameterHelper class. The “GetParameter” method takes a parameter name, value and connection type as parameter. Based on a specific database type, we are building a Parameter instance, set the parameter name and value and return the same to the caller.

public class ParameterHelper
{
public DbParameter GetParameter(stringparameterName, object value,
DbConnetionTypedatabaseType)
{
switch (databaseType)
{
case DbConnetionType.Sqlite:
var sqliteParameter = newSqliteParameter
{
ParameterName = parameterName,
Value = value
};
return sqliteParameter;
default:
return null;
}
}
}

Let us take a look into the “UserRepository” code and try to understand the logic within it. Below is the code snippet for creating a “Users” table in SQLite DB. First we need to get the DB connection so we can build the command instance and execute the command specified in the “CommandText”.

public void CreateUserTable()
{
OpenConnection();
using(var command = connection.CreateCommand())
{
command.CommandText = @ "
CREATE TABLE IF NOT EXISTS Users
(
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Username] NVARCHAR(64) NOT NULL, [Email] NVARCHAR(128) NOT NULL, [Password] NVARCHAR(128) NOT NULL
)
";
// Create table if not exist
command.ExecuteNonQuery();
}
}

Now let us see how to insert into our “Users” table. Below is the code snippet demonstrates how one can insert an entry to “Users” table by making a call to “ExecuteNonQuery”, passing in the “INSERT INTO” DML Query.

public void InsertIntoUserTable()
{
OpenConnection();
using(var command = connection.CreateCommand())
{
// Insert a record
connection.ExecuteNonQuery
(@ "INSERT INTO Users(Username, Email, Password) VALUES('admin', '[email protected]', 'test')
");
}
}

Let us take a look into how to query the “Users” table and print the result to Console. Below is the code snippet for the same.

Open a DB connection if we have not already opened a connection.
Based on the connection instance, Create a Command instance.
Set the command text to select from Users table. Make sure to specify only the columns you are interested in.
Execute the command by making a call to “ExecuteReader”. Which will return a DataReader.
Loop through until we have records by making a call to “Read” method of DataReader.
Get the “UserName” and “Email” values from the DataReader by specifying the appropriate ordinal value.

public void SelectFromUserTable()
{
OpenConnection();
using(var command = connection.CreateCommand())
{
command.CommandText = "SELECT UserName,Email from Users;";
var result = command.ExecuteReader();
while (result.Read())
{
Console.WriteLine(string.Format("UserName: {0}",
result.GetString(0)));
Console.WriteLine(string.Format("Email: {0}",
result.GetString(1)));
}
}
}

Let us take a look into the “Delete” functionality. Below is the code snippet for the same. The code is self-explanatory. We are going to execute a DML command to delete all entries from Users table.

public void DeleteFromUserTable()
{
OpenConnection();
using(var command = connection.CreateCommand())
{
connection.ExecuteNonQuery("DELETE FROM Users");
}
}

Now let us take a look into the code for fetching a specific user by “Id.” Below is the code snippet for the same. We are going to build a parameterized query with the parameter name as ‘UserId’ and value as the user id specified in the function parameters. Here comes the role of our “ParameterHelper” helper class for building the parameter. Ones we build the parameter, we are going to add the same to “Command” instance. Finally, the command is executed by making a call to “ExecuteReader” method which returns a single record (if we have one for the specified user id).

public UserGetUserdById(intuserId)
{
try
{
var command = connection.CreateCommand();
command.CommandText = "SELECT * From Users WHERE Id = @UserId;";
var sqliteParameter = parameterHelper.GetParameter("@UserId",
userId, databaseType);
command.Parameters.Add(sqliteParameter);
var result = command.ExecuteReader();
if (result.Read())
{
var user = newUser
{
Id = result.GetInt32(0),
Username = result.GetString(1),
Email = result.GetString(2),
Password = result.GetString(3)
};
}
} catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
returnnull;
}

As part of our “UserRepository” implementation, there’s one key thing we have to take a look into. That’s how are we dealing with the “Transaction”. Below is the code snippet for the same. The concept of transaction is same as that of ADO.NET.

From the below code you can see, we are executing the DML query little bit differently, that is directly making a call to “ExecuteNonQuery” of connection instance. That’s possible because of our custom extension class – “DbConnectionExtensions”.

In the below case, we are performing multiple data insert into “Users” table. If everything goes well, we are going to commit the transaction. Say if something goes wrong, the ‘catch’ gets executed so we can rollback the transaction.