How to Create Entity Framework like POCO Classes to pair with SQLite

OK,

I was a little frustrated with using DataTable Objects in C# to load data. I knew there was a better way based on using EF at work, but as I was using SQLite, that wasn't really an option

Lo and behold after enough searching and effort, I found a way to accomplish it that I think others would really like to use.

First step, follow the instructions on this site to install this extension for Visual Studio.

https://github.com/ErikEJ/SqlCeToolbox/wiki

(Actual extension below)
https://marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolbox

This is what is going to create your POCO classes for you automatically from your SQLite DB(if you already have them created as I did). If not, you can actually do it the other way as well using the second part of this.

After you follow the instructions there and have your POCO classes set up, you are going to want to get this nuGet Package:

https://www.nuget.org/packages/sqlite-net/

-if for some reason the nuget package fails, you can download the 2 .cs files, it simply uses its own namespace to wrap SQLite functionality for the most part.

and make sure you also install this PCL that goes with it as well

https://www.nuget.org/packages/sqlite-net-pcl

So you'll end up with POCO Classes like this:

public partial class Agents
{
[PrimaryKey, AutoIncrement]
public Int32 AgentID { get; set; }

[MaxLength(20)]
public String FName { get; set; }

[MaxLength(20)]
public String LName { get; set; }

[MaxLength(50)]
public String College { get; set; }

public Int32? Height { get; set; }

public Int32? Weight { get; set; }

public Int32? Age { get; set; }

[MaxLength(12)]
public String DOB { get; set; }

public Int32? Experience { get; set; }

[MaxLength(20)]
public String AgentType { get; set; }

public String ClientList { get; set; }
}


And you'll actually end up with a Create fnuction like this:

public void Create()
{
using (SQLiteConnection db = new SQLiteConnection(_path))
{
db.CreateTable<Agents>();
db.CreateTable<Coaches>();
db.CreateTable<DraftPlayers>();
db.CreateTable<Owners>();
db.CreateTable<Personnel>();
db.CreateTable<RosterPlayers>();
db.CreateTable<TeamDefense>();
db.CreateTable<TeamOffense>();
db.CreateTable<Teams>();
db.CreateTable<Trainers>();

}
}

Which can be used to create the Table directly from the POCO Class.

Then to use it is simple:
Let's say this is a new script---make sure you have references to the following:
using SQLite;
using System;
using System.Linq;

-Here we are simply going to create the SQLiteConnection object, which is what is used to return the data by passing in the path of the file---note that it is just the actual path---no URI:File or ;version=3 at the end of the DB extension.

You create a List of the POCO Class you want to get back, then you use Linq to return the query directly to the List which in my instance returned 3000 players to my List object in one line of code.

void Start () {
string SqlitePath = Application.dataPath + "/SQLite DB/Football.sqlite";
var DraftPlayer = new List<DraftPlayers>();
using (SQLiteConnection db = new SQLiteConnection(SqlitePath))
{
DraftPlayer = db.Query<DraftPlayers>("Select * From DraftPlayers").ToList();
}
}

And that is prety much it---you now have POCO classes that are automatically mapped to and from your SQLite DB and can do whatever you need to with ease.

 

@CodeLiftSleep
This setup works for multiplat-form: android, ios, webgl, linux, windows, uwp at same time????

 

This also seems to be go0d option, I just started using it and so far it is working fine
https://github.com/robertohuertasm/SQLite4Unity3d

 

Doesn't support Foreign Keys