How To Use SQLite With C#

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.

C#

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

C#

  • 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. 

  1. public class Employee  
  2.     {  
  3.         [DbColumn(IsIdentity =true, IsPrimary =true)]  
  4.         public long EmployeeId { get; set; }  
  5.         [DbColumn]  
  6.         public string Name { get; set; }  
  7.         [DbColumn]  
  8.         public string JobDescription { get; set; }  
  9.         [DbColumn]  
  10.         public string Technology { get; set; }  
  11.         [DbColumn]  
  12.         public string Email { get; set; }  
  13.   
  14.         public long Age { get; set; }  
  15.   
  16.     }   

  • 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. 

  1. public class DbColumnAttribute : Attribute  
  2. {  
  3.         /// <summary>  
  4.         /// Set true if implicit conversion is required.  
  5.         /// </summary>  
  6.         public bool Convert { get; set; }  
  7.         /// <summary>  
  8.         /// Set true if the property is primary key in the table  
  9.         /// </summary>  
  10.         public bool IsPrimary { get; set; }  
  11.         /// <summary>  
  12.         /// denotes if the field is an identity type or not.  
  13.         /// </summary>  
  14.         public bool IsIdentity { get; set; }  
  15. }   

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

  1. public interface IFilter<T> where T : classnew()  
  2.     {  
  3.         string EntityName { get; }  
  4.         string Query { get; }  
  5.   
  6.         void Add(Expression<Func<T, object>> memberExpression, object memberValue);  
  7.     }   

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

  1. public class Filter<T> : IFilter<T> where T : classnew()  
  2.     {  
  3.   
  4.         public Filter()  
  5.         {  
  6.             _Query = new StringBuilder();  
  7.             EntityName = typeof(T).Name;  
  8.         }  
  9.   
  10.         public void Add(Expression<Func<T, object>> memberExpression, object memberValue)  
  11.         {  
  12.   
  13.             if (_Query.ToString() != string.Empty)  
  14.                 _Query.Append(" AND ");  
  15.   
  16.             _Query.Append(string.Format(" [{0}] = {1}", NameOf(memberExpression), memberValue == null ? "NULL" : string.Format("'{0}'", memberValue)));  
  17.         }  
  18.   
  19.         public string EntityName { get; private set; }  
  20.   
  21.         private readonly StringBuilder _Query;  
  22.   
  23.         public string Query  
  24.         {  
  25.             get  
  26.             {  
  27.                 return string.Format("SELECT * FROM [{0}] {1} {2};"  
  28.                     , EntityName  
  29.                     , _Query.ToString() == string.Empty ? string.Empty : "WHERE"  
  30.                     , _Query.ToString());  
  31.             }  
  32.         }  
  33.   
  34.         private string NameOf(Expression<Func<T, object>> exp)  
  35.         {  
  36.             MemberExpression body = exp.Body as MemberExpression;  
  37.   
  38.             if (body == null)  
  39.             {  
  40.                 UnaryExpression ubody = (UnaryExpression)exp.Body;  
  41.                 body = ubody.Operand as MemberExpression;  
  42.             }  
  43.   
  44.             return body.Member.Name;  
  45.         }  
  46.     }  

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

  1. public class EntityMapper  
  2.     {  
  3.         // Complete  
  4.         public IList<T> Map<T>(SQLiteDataReader reader)  
  5.             where T : classnew()  
  6.         {  
  7.             IList<T> collection = new List<T>();  
  8.             while (reader.Read())  
  9.             {  
  10.                 T obj = new T();  
  11.                 foreach (PropertyInfo i in obj.GetType().GetProperties()  
  12.                     .Where(p => p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(DbColumnAttribute)) != null).ToList())  
  13.                 {  
  14.   
  15.                     try  
  16.                     {  
  17.                         var ca = i.GetCustomAttribute(typeof(DbColumnAttribute));  
  18.   
  19.                         if (ca != null)  
  20.                         {  
  21.                             if (((DbColumnAttribute)ca).Convert == true)  
  22.                             {  
  23.                                 if (reader[i.Name] != DBNull.Value)  
  24.                                     i.SetValue(obj, Convert.ChangeType(reader[i.Name], i.PropertyType));  
  25.                             }  
  26.                             else  
  27.                             {  
  28.                                 if (reader[i.Name] != DBNull.Value)  
  29.                                     i.SetValue(obj, reader[i.Name]);  
  30.                             }  
  31.                         }  
  32.                     }  
  33.                     catch (Exception ex)  
  34.                     {  
  35. #if DEBUG  
  36.                         Console.WriteLine(ex.Message);  
  37.                         Console.ReadLine();  
  38. #endif  
  39.  
  40. #if !DEBUG  
  41.                         throw ex;  
  42. #endif  
  43.                     }  
  44.                 }  
  45.   
  46.                 collection.Add(obj);  
  47.             }  
  48.   
  49.             return collection;  
  50.         }  
  51.   
  52.     }  

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.

  1. public long Add(T entity) // to insert single entity.  
  2. public void AddRange(IList<T> entities) // to bulk-insert multiple entities  
  3. public void Update(T entity) // to update one entity  
  4. public void UpdateRange(IList<T> entities) // to update multiple entities  
  5. public T GetById(object id) // get single entity by its Id  
  6. public IList<T> Find(IEnumerable<object> ids) // get multiple entities by ids  
  7. public IList<T> Find(IFilter<T> filter) // find entities by filter criteria  
  8. 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.

  1. //Create employee object and fill the properties.  
  2.             Employee e = new Employee();  
  3.             e.Name = "Swaraj";  
  4.             e.Email = "swaraj.ece.jgec@gmail.com";  
  5.             e.JobDescription = "Software Developer";  
  6.             e.Technology = "DotNet";  
  7.             e.Age = 27;  
  8.   
  9.            long generatedEmployeeId = InsertEmployee(e);  
  10.             textBox1.Text = string.Format("{0}", generatedEmployeeId);  
  11.             // Similarly call other methods  
  12. public  long InsertEmployee(Employee newEmployee)  
  13.         {  
  14. //Call the add method in employee service.  
  15.            return new EmployeeService().Add(newEmployee);  
  16.         }  

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

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

  1. public void UpdateEmployee(Employee existingEmployee)  
  2.         {  
  3.             new EmployeeService().Update(existingEmployee);  
  4.         }  
  5.   
  6.         public Employee GetEmployee(long id)  
  7.         {  
  8.             return new EmployeeService().GetById(id);  
  9.         }  
  10.   
  11.         public List<Employee> GetEmployeesByTechnology(string technology)  
  12.         {  
  13.   
  14.             var employeeFilter = new Filter<Employee>();  
  15.             employeeFilter.Add(x => x.Technology, technology);  
  16.             // You can add more filters  
  17.   
  18.             EmployeeService svc = new EmployeeService();  
  19.             return svc.Find(employeeFilter).ToList();  
  20.         }  

Conclusion

Thus, you can write much cleaner code while working with SQLite and C#.