Resolved Best ORM for working with SQLite

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
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
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
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
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
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.
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
Thanks again for the great advice Sheepings, i look forward to using your library one day.
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
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.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,227
Location
Virginia Beach, VA
Programming Experience
10+
I think both @Sheepings and I have been burned badly by EF's bugs/quirky behavior.
 

Neal

Forum Admin
Staff member
Joined
Apr 15, 2011
Messages
85
Location
VA
Programming Experience
10+
What are you using in place of it? I know nopCommerce ditched it for something else, can't remember, LinqToDB or something I think.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,227
Location
Virginia Beach, VA
Programming Experience
10+
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.
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
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.
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
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.
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
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
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
The above code seems to be working well for my needs, thanks Sheepings for all your advice.

AJ
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
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.
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
Hi Sheepings

So if i'm understanding correctly, you cant just have a generic read and write data method that you can pass the sql queries too ? Do you have to have a separate class for each of your database tables ? Like your sample above, if i have three database tables for example "products", "Users", "Exchanges" i would need to have a class for each of those tables and each class will hold the methods for INSERT, UPDATE and DELETE ? This seems like a lot of repetitive work. With the sample below i can call the method and pass the query to it from any form.

C#:
       private void CmdSave_Click(object sender, EventArgs e)
        {
            DatabaseConnection myDbConn = new DatabaseConnection();
            myDbConn.WriteData("INSERT INTO ExchangeSetup " +
                                  "(ExchangeName " +
                                  ",ExchangeAPIKey " +
                                  ",ExchangeSecret) " +
                               "VALUES " +
                                  "('" + CboExchange.Text +  "' " +
                                  ",'" + TxtApiKey.Text + "' " +
                                  ",'" + TxtApiSecret.Text + "')");

            myDbConn.CloseConnection();
            PopulateGrid();
        }
Apologies if i'm misunderstanding you
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
The problem with your code is that it leaves you open to SQL injection attacks and if you ever at a later point decided to move from a local db to a remote db, your database can be exploited.
What you could do is; create your own array of parameters and then once you construct them based off your statements, you can pass them into your write method. Your write method will need to be altered to read the parameters and values :
C#:
                SQLiteParameter[] parameters = { new SQLiteParameter
                {
                    ParameterName = "foo",
                    Value = "bar"
                }};
There is also SQLiteParameterCollection which allows you to AddRange such as a parameter array.
 

AlexJames

Active member
Joined
Mar 20, 2020
Messages
36
Programming Experience
10+
Ahhh, I understand now, thank you for clearing that up Sheepings. I'll look into that and make those changes.

Many thanks
AJ.
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,141
Location
UK
Programming Experience
10+
No problem.

And now you know why I opted to make this new library. :cool:

If you need any other help. Let us know.
 
Top Bottom