Handling SQLite in C# - advancing pass string manipulations as commands

Question

I'm using SQLite in my app (through the System.Data.SQLite package). Right now all the insertion, queries, and other operations are done by sending commands using strings, for example:

        SQLiteCommand command = new SQLiteCommand(comStr, db);

where comStr - is a string variable holding the command.

Are there other options I can use instead of strings? Or are strings the proper way one should use when handling SQL queries from .NET?

The problem is that using strings can get rather messy, for example I have some filters that the user can set. Manipulating the command using the strings - although works - feels to me very brittle:

    public string GetFilterString()
    {
        string fil1 = "";
        string fil2 = "";
        string fil3 = "";
        string fil4 = "";

        // filter by time
        switch (WithinTimeBtnStatus)
        {
            case WithinTime.All:
                break;
            case WithinTime.Hour:
                string minusHour = (DateTime.Now - new TimeSpan(0, 1, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss.fff");
                fil1 = $" timestamp >= datetime('{minusHour}')";
                break;
            case WithinTime.Day:
                string minusDay = (DateTime.Now - new TimeSpan(1, 0, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss.fff");
                fil1 = $" timestamp >= datetime('{minusDay}')";
                break;
            case WithinTime.Week:
                string minusWeek = (DateTime.Now - new TimeSpan(7, 0, 0, 0)).ToString("yyyy-MM-dd HH:mm:ss.fff");
                fil1 = $" timestamp >= datetime('{minusWeek}')";
                break;
        }

        // filter by extension
        for (int i = 0; i < FilteredExt.Count; i++)
        {
            fil2 += " ext != '" + FilteredExt[i] + "'";
            if (i < FilteredExt.Count - 1)
                fil2 += " and";
        }

        // filter by process
        if (_processFilterSelected.ToLower() != "all" && _processFilterSelected != "")
        {
            fil3 = $" proc == '{_processFilterSelected}'";
        }

        // filter by File Operation
        if (_FileOperationFilterSelected.ToLower() != "all" && _FileOperationFilterSelected != "")
        {
            FileOperation fo = Converters.StringToFileOperation(_FileOperationFilterSelected);
            switch (fo)
            {
                case FileOperation.Deleted:
                    fil4 = " oper == 'DELETED'";
                    break;
                case FileOperation.Renamed:
                    fil4 = " oper == 'RENAMED'";
                    break;
                case FileOperation.Modified:
                    fil4 = " oper == 'MODIFIED'";
                    break;
            }
        }


        string fil = "";
        var tmp = new[] { fil1, fil2, fil3, fil4 };
        foreach (var t in tmp)
        {
            if (t != "")
            {
                fil += " and" + t;
            }
        }

        return fil;
    }

Answer 1

Since I didn't get a satisfactory answer, I will post what I ended up doing. I think this probably a decent way, but there might be other better ways to achieve what I was looking for (using LINQ type syntax on my DB, instead of using strings containing queries).

Also - I am not sure this is faster then simply using query strings.

TL;DR: Use SQLite.CodeFirst + EntityFramework.

(On a side note, it might be possible to use LINQ to SQL instead of EntityFramework, but not sure if also in a CodeFirst approach. Will update once I tried and tested this).

---

First thing, you need to add these packages:

• Entity Framework
• System.Data.SQLite (which will probably install also:)
• System.Data.SQLite.Core
• System.Data.SQLite.EF6
• System.Data.SQLite.Linq

And finally if you're starting from code (like I did), you'll also need

• SQLite.CodeFirst

The next thing to do is set up the connection string in app.config.

(On a side note - There seems to be tons of bugs with specifying the providers, which uninstalling and reinstalling the packages above seems to fix. I'm not so sure about the logic behind removing and adding, and invariant names - if you care for what I wrote, it's this:

<system.data>
   <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".NET Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
   </DbProviderFactories>
</system.data>

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6"/>
     </providers>
  </entityFramework>

)

The connection string should specify a name, and at least a path to where your DB file should be located. You can also use relative paths that you later define in the code (by using the |DataDirectory| syntax):

<connectionStrings>
  <add name="YourModel" connectionString="Data Source=|DataDirectory|\NameOfYourDBFile.sqlite" providerName="System.Data.SQLite" />
</connectionStrings>

The next step, if you're doing Code-First, is creating a new class that will be your model, this is basically using the SQLite.CodeFirst package:

class YourModel : DbContext
{
    // Your context has been configured to use a 'YourModel' connection string from your application's 
    // configuration file (App.config or Web.config). By default, this connection string targets the 
    // 'YourProject.YourModel' database on your LocalDb instance. 
    // 
    // If you wish to target a different database and/or database provider, modify the 'YourModel' 
    // connection string in the application configuration file.
    public YourModel()
        : base("name=YourModel")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<YourModel>(modelBuilder);
        Database.SetInitializer(sqliteConnectionInitializer);
        Database.SetInitializer(new SqliteDropCreateDatabaseWhenModelChanges<YourModel>(modelBuilder));
    }

    // Add a DbSet for each entity type that you want to include in your model. For more information 
    // on configuring and using a Code First model, see http://go.microsoft.com/fwlink/?LinkId=390109.

    public virtual DbSet<YourTableClass> YourTable { get; set; }
}

[Table("YourTable")]
public class YourTableClass
{
    [Key]
    public string Id { get; set; }
    [Required]
    public FileOperation Oper { get; set; }
    [Required, Index]
    public OperationState State { get; set; }
    [Index]
    public string Proc { get; set; }
    [Required]
    public string Src { get; set; }
    public DateTime Timestamp { get; set; }
    // etc.
}

}

You can read more about it here.

And that's basically it with the preparations.

(On a side note, if you want to change the relative path to the DB file from code behind, you'll need to write:

AppDomain.CurrentDomain.SetData("DataDirectory", @"the\path\you\desire");

)

Now you can just use it. The basic syntax is very simple, you just use:

using (var context = new YourModel())
{
     // some query
}

So Select:

using (var context = new YourModel())
{
     var t = context.YourTable
       .Where(e => e.State == OperationState.BackedUp)
       .Select(e => e.Proc)
       .Distinct()
 }

If you want to Insert:

using (var context = new YourModel())
{
    var e = context.YourTable.Create();
    e.Id = guid;
    // ...etc
    e.Timestamp = timestamp;
    context.YourTable.Add(e);
    context.SaveChanges();
}

If you want to still use string queries:

using (var context = new YourModel())
{
    context.Database.ExecuteSqlCommand(comString);
}

Some important things to remember:

• If you change something in the DB, you have to call Context.SaveChange() in the end (you don't need this for ExecuteSqlCommand)
• Deletion can be done with RemoveRange + SaveChange() or by still using a query string.

So the example in the question GetFilterString changes to:

    public static IQueryable<YourTable> GetFilteredQueryable(IQueryable<YourTable> yourTable)
    {
        // filter by time
        switch (RestoreLogic.WithinTimeBtnStatus)
        {
            case WithinTime.All:
                break;
            case WithinTime.Hour:
                DateTime offsetHour = DateTime.Now.Add(new TimeSpan(-1, 0, 0));
                yourTable = yourTable.Where(e => e.Timestamp >= offsetHour);
                break;
           // etc.
        }

        // filter by extension
        foreach (var i in FilteredExt)
        {
            yourTable = yourTable.Where(e => e.Ext != i);
        }

        // etc.

        return yourTable;
    }

Answer 2

EDIT to provide some solution in answer.

This tutorial shows you how to properly implement SQLite and use Linq extensions to interact with your database tables. I have copied the relevant parts below. Once you have your database connection open, and have created your data first tables, you can then interact with the table as you would with any IEnumerable with Linq. It does also provide an option of passing in SQL as a string, however as this is not checked at compile time, you run the risk of having runtime errors.

https://developer.xamarin.com/guides/xamarin-forms/application-fundamentals/databases/

The TodoItemDatabase constructor is shown below:

public TodoItemDatabase(string dbPath)
{
  database = new SQLiteAsyncConnection(dbPath);
  database.CreateTableAsync<TodoItem>().Wait();
}

This approach creates a single database connection that is kept open while the application runs, therefore avoiding the expense of opening and closing the database file each time a database operation is performed. The remainder of the TodoItemDatabase class contains SQLite queries that run cross-platform. Example query code is shown below (more details on the syntax can be found in the Using SQLite.NET article):

public Task<List<TodoItem>> GetItemsAsync()
{
  return database.Table<TodoItem>().ToListAsync();
}

public Task<List<TodoItem>> GetItemsNotDoneAsync()
{
  return database.QueryAsync<TodoItem>("SELECT * FROM [TodoItem] WHERE [Done] = 0");
}

public Task<TodoItem> GetItemAsync(int id)
{
  return database.Table<TodoItem>().Where(i => i.ID == id).FirstOrDefaultAsync();
}

public Task<int> SaveItemAsync(TodoItem item)
{
  if (item.ID != 0)
  {
    return database.UpdateAsync(item);
  }
  else {
    return database.InsertAsync(item);
  }
}

public Task<int> DeleteItemAsync(TodoItem item)
{
  return database.DeleteAsync(item);
}