How To Use SQLite With C#

Swaraj Ketan Santra
7y
16.6k
0
2

Article

Introduction

SQLite is open source file system database. Unlike SQL Server, this doesn’t require any Service to interact with the database but the real problem occurs, when we try to interact with SQLite DB in C#. There is no proper Entity Framework support available, as of now. Thus, we are compelled to use inline queries. After working on Linq and Entity framework-like ORM, it feels quite irritating to write inline queries for CRUD operation in SQLite DB. Thus, can we use quasi-lambda codes to interact with such file system DB? Can we avoid hardcoded table/column names from our C# code? If you ask me these questions, I would say "absolutely”.

In the article given below, I will show you.

How to architect a SQLite +C# client Application without any ORM.
How to use reflection and generic to create your custom and extensible ORM to interact with SQLite database.
Also, how to optimize the code and its maintainability.

Let’s get started.

First of all, let’s create a SQLite database, using any SQLite manager. In our case, we have only one table inside the database – Employee.

Database name

SQLiteDemo.db and table name and schema is shown below.

Now, we have to create our solution and tiers. In my case, I will create a solution, as given below.

SQLiteWithCSharp
It is the host. It can be Windows form Application of WPF Application or any other.
SQLiteWithCSharp.Common
As the name says, it can be used by all the projects under this solution.
SQLiteWithCSharp.Models
This class library will contains all the model classes. The model class name will be exactly the same as the table name (“Employee”). The properties will be same as the column's name. These model classes will be exactly similar to entity classes in case of an Entity framework with SQL Server. Another point I must mention at this moment is, each property will be decorated with a custom attribute called DbColumn, which denotes it to be a table column in the database. I will explain this custom attribute little later.

Employee model class looks like this.

public class Employee
{
[DbColumn(IsIdentity =true, IsPrimary =true)]
public long EmployeeId { get; set; }
[DbColumn]
public string Name { get; set; }
[DbColumn]
public string JobDescription { get; set; }
[DbColumn]
public string Technology { get; set; }
[DbColumn]
public string Email { get; set; }
public long Age { get; set; }
}

SQLiteWithCSharp.Services
This library contains all the CRUD operation methods and business logics. You can also go one step ahead and split into the multiple libraries for your own convenience.
SQLiteWithCSharp.Utility
This is the heart of the CRUD operation. It contains all the operations but in very usable and generic form.

From my attached solution, you can figure out how these projects are referencing each other.

Now, let’s go over the Utility library.

This custom attribute is used to decorate a property in model class.

public class DbColumnAttribute : Attribute
{
/// <summary>
/// Set true if implicit conversion is required.
/// </summary>
public bool Convert { get; set; }
/// <summary>
/// Set true if the property is primary key in the table
/// </summary>
public bool IsPrimary { get; set; }
/// <summary>
/// denotes if the field is an identity type or not.
/// </summary>
public bool IsIdentity { get; set; }
}

This interface is basically implemented by a custom class called Filter.

public interface IFilter<T> where T : class, new()
{
string EntityName { get; }
string Query { get; }
void Add(Expression<Func<T, object>> memberExpression, object memberValue);
}

This is a Filter class and it provides you the capability to use member expression to get filtered records in an optimized way.

public class Filter<T> : IFilter<T> where T : class, new()
{
public Filter()
{
_Query = new StringBuilder();
EntityName = typeof(T).Name;
}
public void Add(Expression<Func<T, object>> memberExpression, object memberValue)
{
if (_Query.ToString() != string.Empty)
_Query.Append(" AND ");
_Query.Append(string.Format(" [{0}] = {1}", NameOf(memberExpression), memberValue == null ? "NULL" : string.Format("'{0}'", memberValue)));
}
public string EntityName { get; private set; }
private readonly StringBuilder _Query;
public string Query
{
get
{
return string.Format("SELECT * FROM [{0}] {1} {2};"
, EntityName
, _Query.ToString() == string.Empty ? string.Empty : "WHERE"
, _Query.ToString());
}
}
private string NameOf(Expression<Func<T, object>> exp)
{
MemberExpression body = exp.Body as MemberExpression;
if (body == null)
{
UnaryExpression ubody = (UnaryExpression)exp.Body;
body = ubody.Operand as MemberExpression;
}
return body.Member.Name;
}
}

Now, we have a class called EntityMapper, which actually reads SQLite db reader and fills in the property values.

public class EntityMapper
{
// Complete
public IList<T> Map<T>(SQLiteDataReader reader)
where T : class, new()
{
IList<T> collection = new List<T>();
while (reader.Read())
{
T obj = new T();
foreach (PropertyInfo i in obj.GetType().GetProperties()
.Where(p => p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(DbColumnAttribute)) != null).ToList())
{
try
{
var ca = i.GetCustomAttribute(typeof(DbColumnAttribute));
if (ca != null)
{
if (((DbColumnAttribute)ca).Convert == true)
{
if (reader[i.Name] != DBNull.Value)
i.SetValue(obj, Convert.ChangeType(reader[i.Name], i.PropertyType));
}
else
{
if (reader[i.Name] != DBNull.Value)
i.SetValue(obj, reader[i.Name]);
}
}
}
catch (Exception ex)
{
#if DEBUG
Console.WriteLine(ex.Message);
Console.ReadLine();
#endif
#if !DEBUG
throw ex;
#endif
}
}
collection.Add(obj);
}
return collection;
}
}

The class given below is called BaseService, which is inherited by all the Service classes (example: EmployeeService). It provies basic CRUD operations to all the Service classes. The above-mentioned claases, attributes, interfaces are used and the BaseService methods are prepared. The methods are given below. Since the code is bit lengthy, you can get the complete working code from the attachment. Here, I will explain only the methods inside BaseService and how to use them.

public long Add(T entity) // to insert single entity.
public void AddRange(IList<T> entities) // to bulk-insert multiple entities
public void Update(T entity) // to update one entity
public void UpdateRange(IList<T> entities) // to update multiple entities
public T GetById(object id) // get single entity by its Id
public IList<T> Find(IEnumerable<object> ids) // get multiple entities by ids
public IList<T> Find(IFilter<T> filter) // find entities by filter criteria
public IList<T> GetAll() // to get all entities

In the solution, you will get all other methods, which provides you controls over database through model classes only. You can also write or extend more.

Now, to insert one employee in a database and to get the newly inserted employeeId (identity) in one single transaction, we need to write only a few lines of code, as given below.

//Create employee object and fill the properties.
Employee e = new Employee();
e.Name = "Swaraj";
e.Email = "[email protected]";
e.JobDescription = "Software Developer";
e.Technology = "DotNet";
e.Age = 27;
long generatedEmployeeId = InsertEmployee(e);
textBox1.Text = string.Format("{0}", generatedEmployeeId);
// Similarly call other methods
public long InsertEmployee(Employee newEmployee)
{
//Call the add method in employee service.
return new EmployeeService().Add(newEmployee);
}

EmployeeService class looks very clean and simple. Please notice, the employee class has been passed to BaseService as an entity.

Similarly, you can update, search employees as given in the sample methods given below.