Resolved Best ORM for working with SQLite

[AlexJames]

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

 

[AlexJames]

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.

       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

 

[NoUserHere]

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 :

                SQLiteParameter[] parameters = { new SQLiteParameter
                {
                    ParameterName = "foo",
                    Value = "bar"
                }};

There is also SQLiteParameterCollection which allows you to AddRange such as a parameter array.

 

[AlexJames]

Ahhh, I understand now, thank you for clearing that up Sheepings. I'll look into that and make those changes.

Many thanks
AJ.

 

[NoUserHere]

No problem.

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

If you need any other help. Let us know.

 

[ausm]

Clarity if you :

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.

@Sheepings have you ever tried this Library?

 

[NoUserHere]

Lol are you nervous about posting? You've been contemplating posting that message all day.

I have not used that library, no but you have. Why do you ask?