Resolved Best ORM for working with SQLite

AlexJames

Well-known member
Joined
Mar 20, 2020
Messages
65
Programming Experience
10+
Hi All

I have a C# app that i want to connect to a SQLite database, I originally wanted to use EntityFrameWork 6 as i am familiar with it but it doesn't seem to work with SQLite in the latest version of VS2019. Can someone please suggest the next best option, there seems to be so many, Dapper, NHibernate etc.

Many thanks.
AJ
 
Hi Sheepings

Thank you for that link, i'll work through those suggestions.
I use sqlexpress a lot but want to delve into SQLite, I was testing out ado.net but couldn't quit find what i was looking for or what i am used too. I have long VB6 background and what i do there is create functions for reading data and writing data.

I would setup my connection strings and have routines to open the connection, read, write and delete. I watched a lot of tutorials on ado.net but everyone seems to write out their connection and command parameters every time. The below example is so easy to use, but i just cant find a way to do that in ado.net, plus everything seems to be about ORM's these days, so scratching my head a bit on whicj is the best method to follow.

VB.NET:
Public Sub ReadServer(ByVal Query As String)
    If rs.State = 1 Then
        rs.Close
    End If

    rs.Open Query, cnnMain, adOpenStatic, adLockReadOnly
End Sub
 
Go with Dapper - Search the forums for my name and EF. You'll find many posts why I don't hold EF in high regard and all within good reason.

but everyone seems to write out their connection and command parameters every time.
That's why I am making a separate library for SQL and MySQL which will do all the parameter building, connection, and command building for you based only on your SQL statements you provide. When I will finish it is another dream, as I have projects still in development going back to 2016, some even from 2012 as time hasn't been on my side the last few years to finish them. haha But when I do, I will be publishing them here on this forum. Eventually, I'd like to add the ability for it to map databases and generate the required classes in C# for you. But that won't happen for a few more releases down the line.

Meanwhile, avoid EF and give Sonic or Dapper a shot.
 
I think both @Sheepings and I have been burned badly by EF's bugs/quirky behavior.
 
What are you using in place of it? I know nopCommerce ditched it for something else, can't remember, LinqToDB or something I think.
 
I use db4o for my personal projects. db4o is an object oriented database. Why even use an Object-to-Relational Mapper when you can stay object oriented all the way. Alas, support for db4o has died, even among the Android diehards.

For work related stuff, my back end data is often SharePoint, not SQL so ORM's are useless to me and I have to hand write my object-to-sharepoint-list-item anyway. The few times I need to use SQL, I just use raw SQL and hand write my objects as well. The projects are not big enough to warrant an ORM.
 
Why do you make this statement?
Clarity if you :
Search the forums for my name and EF. You'll find posts why I don't hold EF in high regard and all within good reason.
I've never been a fan of EF, and I have a list of problems as long as my arm. Bugs I've submitted fixes for which were never fixed, memory leaks which I still incur today, even though I've reported those too. If I'm requested to use EF under employer instructions, I simply refuse to use it and consult them on my findings, and they normally are persuaded to use alternatives. I probably should have stated that above, but I didn't need to as I noticed the OP was reading a topic earlier where I had previously given reasons not to use EF. So there was no reason for elaboration. There are a list of alternatives here : What are some good Entity Framework Alternatives

I preference Dapper.
 
haha You'll be so lucky, If i can ever find time to finish it @AlexJames. :rolleyes:

If you need any help with any of the other ORM, should you choose one. Let us know.

Always happy to help.

Thank you Sheepings, i'm currently looking into Dapper but struggling to find a decent tutorial on how to lay out the structure, should i have a "models", "entities" folders with the different classes in it like EF ? i'm relatively new to C# and really want to do it the correct way from the start. If you could advise or guide me on how to get going the correct way, i would really appreciate it.
 
I decided to scrap the ORM idea and stick to a method i'm familiar with, i'm working on a CRUD or database connection class using ADO.NET and creating methods that I can pass sql queries too. Working on the testing phase of it now, please let me know if there is anything I could improve on in the below code, or if anything jumps out as wrong please let me know.

C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;

namespace MyTradeJournal.WinForms.SQLite
{
    public class DatabaseConnection
    {
        private readonly SQLiteConnection sqlConn;
        /// <summary>
        /// Place the connection string inside the constuctor.
        /// </summary>
        public DatabaseConnection()
        {
            SQLiteConnection sQLiteConnection = new SQLiteConnection(@"Data Source=C:\PosData\TradeJournal.db;Version=3;");
            sqlConn = sQLiteConnection;
        }

        public void WriteData(string Query)
        {
            if (sqlConn.State == System.Data.ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            using (SQLiteCommand cmd = new SQLiteCommand(Query, sqlConn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public SQLiteDataReader ReadData(string Query)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(Query, sqlConn))
            {
                SQLiteDataReader dr = cmd.ExecuteReader();
                return dr;
            }
        }

        public void CloseConnection()
        {
            if (sqlConn.State == System.Data.ConnectionState.Open)
            {
                sqlConn.Close();
            }
        }
    }
}

Thanks
AJ
 
The above seems Ok, but where are your parameters?

Just because its a local db, that doesn't mean you should slack on security.

SQLiteConnection sQLiteConnection = new SQLiteConnection(@"Data Source=C:\PosData\TradeJournal.db;Version=3;");
Why not remove this and call on a new connection from whatever method needs it?

Here's how I wrote out mine for testing a customer app which had a user registration process :

C#:
        public void Create_NewUser(TextBox[] tb_controls)
        {
            using (SQLiteConnection liteConnection = new SQLiteConnection(Con_String))
            {
                if (liteConnection.State == System.Data.ConnectionState.Closed)
                    liteConnection.Open();
                Command = "INSERT INTO users (username, email, password, name, company_name) VALUES (@username, @email, @password, @name, @company_name)";
                SQLiteCommand liteCommand = new SQLiteCommand();
                liteCommand.CommandText = Command;
                liteCommand.Connection = liteConnection;
                liteCommand.Parameters.AddWithValue("@username", tb_controls[0].Text);
                liteCommand.Parameters.AddWithValue("@email", tb_controls[1].Text);
                liteCommand.Parameters.AddWithValue("@password", Security.Secure_Password);
                liteCommand.Parameters.AddWithValue("@name", tb_controls[3].Text);
                liteCommand.Parameters.AddWithValue("@company_name", tb_controls[4].Text);
                liteCommand.ExecuteNonQuery();
            }
        }
At a later point, I will introduce a Model for the data to push and pull data to and from the controls instead of passing in the controls to the parameters, I will simply pass in a Model instead...

On the point of Dapper. Normally I'd advise people to stay away from Youtube tutorials, but there are some pretty good ones online. For example, Tim Corey normally has really good examples to follow : dapper tutorial c# - YouTube - Further searching on Google and Bing also turns up trumps of information for beginners.
 
Back
Top Bottom