Confusion with sqLite

[IliaPreston]

Hi all.

I am trying to write my first Windows Forms C# program using sqLite, but there are a whole lot of confusions:

1- This page:
https://github.com/liviucotfas/ase-w...0-%20SQLite.md
says:

Choose the “System.Data.SQLite.Core” package and hit the “Install” button

But, another page:
https://learn.microsoft.com/en-us/ef...arted/winforms
says:

Select the Microsoft.EntityFrameworkCore.Sqlite package

So, which one should I install?

2- This page:
https://learn.microsoft.com/en-us/ef...arted/winforms
says:

Right-click on the solution and choose Manage NuGet Packages for Solution

But this page:
https://github.com/liviucotfas/ase-w...0-%20SQLite.md
says:

Open the NuGet Package Manager by right clicking on the “References” node in the “Solution Explorer” window

So, what should I do?
Please note that (correct me if I am wrong) right-clicking on "solution" and then installing the NuGet package will install it for the whole solution (that is at solution level), but "References" is at project level, so, if I right-click on References, and then install the NuGet package, it will be installed for only that project.
The solution doesn't have a "References" sub-menu item. Each project does.

3- Another webpage:
https://www.c-sharpcorner.com/Upload...w-application/
says:

After this again right-click on your application and select "Add reference" and go to:

Browser -> BIN -> Debug and select DLL file (Sqlite Net.dll).

which means installing the sqLite DLL under the project's DEBUG folder.
Please correct me if I am wrong. What I understand from this is that for each and every project (or solution) that I will ever want to develop, I will have to install a separate sqlite DLL on to my hard disk, which is quite wasteful.

I am completely confused.
Can you please clarify this.
Thanks a lot for your help.

[jmcilhinney]

Firstly, two pieces of advice are not necessarily mutually exclusive. Secondly, two pieces of advice may both be right, as there may be multiple ways to achieve the same thing.

If your app is going to make use of code in libraries then it needs to know that those libraries exists and where they are. That means that your project needs to reference them. You can add a reference directly, or you can install a NuGet package. If it's a just a single library then a direct reference may be enough, but a NuGet package enables other libraries that the main library is dependent on to be included as well. It also enables easy upgrades to newer versions and targeting multiple platforms. Generally speaking, you should install a NuGet package if possible. If you create a direct reference, you need to consider whether that library will be already present on your users' machines or not. If it will be then there's no need for you to deploy it with your app, but if it won't be then you should have that library copied to the output folder as part of the build so it can be deployed along with your EXE.

When installing NuGet packages, there are multiple ways to do it. There is a UI for managing packages for a project and for a whole solution. Personally, I tend to use the project option when installing new packages and the solution option when updating existing packages, including installing them in additional projects. You might do the same or something else. Some people prefer to use the Package Manager Console over the GUI. Again, what you do is up to you.

Which packages you need to install depends on exactly what you're doing. If you're targeting .NET Core (.NET 5 and later are based on .NET Core) and using a SQLite database then you'll need System.Data.SQLite.Core. If you're using ADO.NET for data access then that's probably all you need for that purpose. If you're using Entity Framework then you'll also need Microsoft.EntityFrameworkCore.Sqlite. I'm not sure but the former may be installed automatically as a dependency when you install the latter. If you're using EF, try installing the latter first and then see if the former is included as a dependency. If it's not, then you can add it it one of the ways mentioned previously.

[IliaPreston]

Thanks for your help

When I install an sqLite NuGet package (for example System.Data.SQLite.Core or Microsoft.EntityFrameworkCore.Sqlite) does that installation put a DLL on my computer?
I guess it does, but where exactly does it place the DLL? What is the pathway?

Now, lets say I do the above and the NuGet installation places a DLL on my computer.
Then a while later when I develop another application (within another solution). and within that new application, I install the same NuGet sqLite reference, will it again place another copy of that DLL on my computer?

Please advise.
Thanks

[jmcilhinney]

NuGet packages are installed per solution. In the Solution Explorer, right-click the solution and select 'Open Folder in File Explorer'. Under that solution folder, you'll notice a 'packages' folder. That's where your Nuget packages are installed.

[IliaPreston]

NuGet packages are installed per solution. In the Solution Explorer, right-click the solution and select 'Open Folder in File Explorer'. Under that solution folder, you'll notice a 'packages' folder. That's where your Nuget packages are installed.

But, isn't this wasteful?
Isn't it better to install the package (for example the sqLite DLL) in only one location on the computer, so that all solutions would refer to that single DLL?

Thanks.

[jmcilhinney]

Given how cheap and plentiful storage space is on modern computers, duplication of libraries like this is considered insignificant compared to the fact that it allows you to have different versions of the same library in use in different applications and to upgrade them each at different times as required.

[jpbro]

But, isn't this wasteful?
Isn't it better to install the package (for example the sqLite DLL) in only one location on the computer, so that all solutions would refer to that single DLL?

Thanks.

Is wasteful in terms of space? Yes.

Is it wasteful in terms of time? No. (at least in the short term. In the mid-to-long term, we'll see).

[kareninstructor]

It is not wasteful to have the dll in each project today, maybe back in 1990. Most developers have extremely large drives and most users have ample space for a hard drive.

Have you seen Central Package Management?

[techgnome]

Also isn't that kind of thinking that got us into DLL Hell back in the day? And that's the reason we don't do that any more and why SxS deployment is superior (when you can package it all into one Exe).



-tg

[IliaPreston]

Now let's say I add the sqLite Nuget package to 100 different solutions.
Then a while later, sqLite releases a new version of sqLite with new features and bug fixes.

Should I then update all those 100 NuGet packages separately?
Isn't that a maintenance nightmare?

[techgnome]

You'ld still have to rebuild all your apps ... so either 1) you deal with it and just update them all and rebuild each one, or 2) you just update the nuget packages the next time you work on that app, or 3) you don't worry about it at all.


And if you think this is a waste,, then I won't tell you that this is the same setup that Java and Maven use and have been using for some time. When ever I add a dependency to my Java projects, maven downloads a copy of the jar and puts it in my cache ... then when a project needs it, it's pulled out of the cache and into the project. My cache then as multiple versions of the same library in it ... which is fine because depending on which branch I'm in, I might need V1.2.3. .... or v2..2.1.... or 2.3.2-RELEASE NUGet works under the same principle/ When ypu get the package, it'll go into a locaal cache, from there it copies to the project where it is used.


-tg

[PlausiblyDamp]

Now let's say I add the sqLite Nuget package to 100 different solutions.
Then a while later, sqLite releases a new version of sqLite with new features and bug fixes.

Should I then update all those 100 NuGet packages separately?
Isn't that a maintenance nightmare?

How do you know the update won't introduce bugs or issues in some of those 100 projects?

Blindly upgrading everything without considering potential issues is a recipe for disaster. Upgrading should be an opt-in approach with appropriate testing being performed.

[IliaPreston]

I am reading this page:
https://learn.microsoft.com/en-us/ef...arted/winforms
in order to learn how to do it.

I haven't created the project that it is explaining yet.
I want to first read this page to the end, and completely understand it before creating that project.

There are some things that I don't understand in this page:

1- In this line of code:

    public string? Name { get; set; }

why is there a question mark after string?
I know that "string" is a c# datatype, but, is "string?" also another datatype?

2- In this code:

    public int CategoryId { get; set; }
    public virtual Category Category { get; set; } = null!;

I suppose, it is defining two columns.
I understand the first one, but why does the second one have a = null! at the end?

3- In the other table:

public class Category
{
    public int CategoryId { get; set; }

    public string? Name { get; set; }

    public virtual ObservableCollectionListSource<Product> Products { get; } = new();
}

I suppose, it is defining another table.
So, I guess the first two lines inside the class, define two ordinary columns (CategoryId and Name).
But, what is that third line (the red line)?
And what are those angular brackets (that is "<" and ">")?

4- in this code:

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

it is declaring two variables (Products and categories) but the data types are strange.
Given that Product is a class that it has declared, we can declare a variable of type Product.
But, what is DbSet<Product>?
Same thing about DbSet<Category>?

5- In that page there is code for two events:

   protected override void OnLoad(EventArgs e)

and

   protected override void OnClosing(CancelEventArgs e)

I haven't created this project yet, but, I looked at my C# HelloWorld project, and the form in there does not have an OnLoad event.
It doesn't have an OnClosing event either.
It has a Load event but not an OnLoad or OnClose event
So, why is this tutorial page talking about nonexistent events as if they existed?

Quite confusing.
Please forgive my ignorance.
I come from a VB6 background, that is why some things in C# are quite strange to me. But I really want to do whatever it takes to learn it.

Please help.
Thanks.
Ilia

[jmcilhinney]

Basically, the DbContext represents your database and each DbSet represents a table. You can query a DbSet using LINQ like you would query a database table using SQL. The navigation properties of the entities represent the foreign key relations. Where a Category has a collection of Product entities, that represents a 1-to-many relation between the corresponding tables.

[IliaPreston]

Basically, the DbContext represents your database and each DbSet represents a table. You can query a DbSet using LINQ like you would query a database table using SQL. The navigation properties of the entities represent the foreign key relations. Where a Category has a collection of Product entities, that represents a 1-to-many relation between the corresponding tables.

Thanks a lot for your help.
Can you (or any other friend in here) also please answer the fifth question?
I repeat that question in here:

5- In that page (https://learn.microsoft.com/en-us/ef...arted/winforms) there is code for two events:

   protected override void OnLoad(EventArgs e)

and

   protected override void OnClosing(CancelEventArgs e)

I haven't created this project yet, but, I looked at my C# HelloWorld project, and the form in there does not have an OnLoad event.
It doesn't have an OnClosing event either.
It has a Load event but not an OnLoad or OnClose event
So, why is this tutorial page talking about nonexistent events as if they existed?

Thanks

[PlausiblyDamp]

Those two methods aren't events, they are overriding methods from the base class.

[IliaPreston]

I finished reading this page:
https://learn.microsoft.com/en-us/ef...arted/winforms
and now, I am trying to implement it.

At the first stage (installeng the Microsoft.EntityFrameworkCore.Sqlite package) it fails.
Here is a screen print:
https://i.imgur.com/XKxwpau.jpeg

Please help.
Thanks

[jmcilhinney]

5- In that page (https://learn.microsoft.com/en-us/ef...arted/winforms) there is code for two events:

   protected override void OnLoad(EventArgs e)

and

   protected override void OnClosing(CancelEventArgs e)

I haven't created this project yet, but, I looked at my C# HelloWorld project, and the form in there does not have an OnLoad event.

Those methods are inherited from the Form class. Like any other inherited members, you only override them if you want to change their behaviour. Those methods raise the Load and Closing events so you would override them if you want your own form to have additional behaviour when those events are raised. You might read this to learn how those methods relate to the events.

[PlausiblyDamp]

I finished reading this page:
https://learn.microsoft.com/en-us/ef...arted/winforms
and now, I am trying to implement it.

At the first stage (installeng the Microsoft.EntityFrameworkCore.Sqlite package) it fails.
Here is a screen print:
https://i.imgur.com/XKxwpau.jpeg

Please help.
Thanks

That package is for a dotnet core application, but your application is a framework application.

https://learn.microsoft.com/en-us/sh...e-dev-question might help explain the difference.

In the link you have followed it tells you to create a Windows Forms App, it looks like you have created a Windows Forms App (Framework) instead.

[IliaPreston]

That package is for a dotnet core application, but your application is a framework application.

https://learn.microsoft.com/en-us/sh...e-dev-question might help explain the difference.

In the link you have followed it tells you to create a Windows Forms App, it looks like you have created a Windows Forms App (Framework) instead.

Yes. You are right.
Thanks a lot for letting me know.
I deleted that whole solution and started again and this time created a Windows Forms App, and then I managed to install the sqLite EF NuGet package successfully.

But, now I have faced another problem.
It keeps giving me this error:
https://i.imgur.com/TgsiRGB.jpeg

This error pops up when I place a button on the form.
Also, if I try to save the Solution, or try to save the Solution, this error pops up again.
I cannot even save the Solution.

What mistake have I made to cause this?
Please help.
Thanks.

[jmcilhinney]

Yes. You are right.
Thanks a lot for letting me know.
I deleted that whole solution and started again and this time created a Windows Forms App, and then I managed to install the sqLite EF NuGet package successfully.

But, now I have faced another problem.
It keeps giving me this error:
https://i.imgur.com/TgsiRGB.jpeg

This error pops up when I place a button on the form.
Also, if I try to save the Solution, or try to save the Solution, this error pops up again.
I cannot even save the Solution.

What mistake have I made to cause this?
Please help.
Thanks.

Read the title of this thread and then ask yourself whether this post belongs in this thread. You can't ask every question you have about this project in the same thread. If your SQLite confusion has been sorted then mark this thread Resolved and move on. If you have a new question then create a new thread that contains all and only the information relevant to that issue. Also, don't post error messages as screenshots alone. Post them as text, formatted appropriately, then add a screenshot if and only if it adds value.

[IliaPreston]

Those two methods aren't events, they are overriding methods from the base class.

Thanks for your help.

What base class?
What is the name of that base class?
How can I see the text of that base class?

Please advise.
Thanks again.

[PlausiblyDamp]

Thanks for your help.

What base class?
What is the name of that base class?
How can I see the text of that base class?

Please advise.
Thanks again.

I think this question is getting beyond the original sqlite question and might be worth a thread to itself.

However in a Windows Forms app all Forms inherit from https://learn.microsoft.com/en-us/do...ows.forms.form as the base, you should have a <formname>.desginer.cs file for each form in your project - look in there as you should see that your form is inheriting from the above class.

[IliaPreston]

I learned how to use this technique of Entity Framework to do sqLite programming in C#.
But, it is not really a good way of doing so.
This technique defines a class for each table, and also does a whole lot of things to simulate every sqLite action in C#.
This is not what I am looking for (even though I learned it successfully).

What I am looking for is to write genuine SQL queries in C#, like:

   string sql = "update Students  set FName = 'John', LName = 'Milton' where ST_ID = 542135458 "

and call a C# command to execute that query.
Or like this one:

   string sql = "select * from Students  where FName = 'John' "

and call a C# command to execute that query and return a recordset that I could feed to a DataGridView.

In other words instead of using all those class definitions that Entity Framework requires for sqLite programming, I want to write genuine queries in the form of strings and run them, just like what I did in VB6.

To do that, I have google searched and found this video:
https://www.youtube.com/watch?v=1FfKGBcJAEQ
At 24m into this video, it explains how to do it.
It uses system.data.sqlite and everything else that it explains.


Now, my question is this:
Is that video the right approach that I am looking for?
Please correct me if I am wrong: As far as I understand, that video looks exactly like what I am looking for, but I am not sure.

Please note that even though that video looks like what I am looking for, it is three years old.
So, is it still valid today?
Also, I know that there are many different and diverse ways of sqLite programming in C#, so, is that video the right approach that I am looking for, or not.

Please help.

Thanks.
Ilia

[jmcilhinney]

If you want to use ADO.NET then use ADO.NET. If you don't then don't. That's your own decision. Neither is right or wrong. Most professional developers will use some sort of ORM, e.g. EF or Dapper, rather than using ADO.NET directly. All those ORMs use ADO.NET under the hood because that's how data access works in .NET.

[PlausiblyDamp]

I learned how to use this technique of Entity Framework to do sqLite programming in C#.
But, it is not really a good way of doing so.

Why isn't it a good way of doing so, an awful lot of modern software development will use an ORM like EF rather than writing SQL directly.

This technique defines a class for each table, and also does a whole lot of things to simulate every sqLite action in C#.
This is not what I am looking for (even though I learned it successfully).

Again, is there a reason you don't want to do this?

What I am looking for is to write genuine SQL queries in C#, like:

   string sql = "update Students  set FName = 'John', LName = 'Milton' where ST_ID = 542135458 "

and call a C# command to execute that query.
Or like this one:

   string sql = "select * from Students  where FName = 'John' "

and call a C# command to execute that query and return a recordset that I could feed to a DataGridView.

In other words instead of using all those class definitions that Entity Framework requires for sqLite programming, I want to write genuine queries in the form of strings and run them, just like what I did in VB6.

EF can make it a lot easier than writing an maintaining all the SQL yourself, although if you would rather do all the SQL yourself nothing is stopping you - you would just need to use the underlying classes provided by system.data.sqlite directly.

I would definitely consider why you want to do the SQL yourself, rather than use an ORM - using an ORM can remove an awful lot of the burden of writing and maintaining data access code.

[IliaPreston]

Why isn't it a good way of doing so, an awful lot of modern software development will use an ORM like EF rather than writing SQL directly.

Again, is there a reason you don't want to do this?

EF can make it a lot easier than writing an maintaining all the SQL yourself, although if you would rather do all the SQL yourself nothing is stopping you - you would just need to use the underlying classes provided by system.data.sqlite directly.

I would definitely consider why you want to do the SQL yourself, rather than use an ORM - using an ORM can remove an awful lot of the burden of writing and maintaining data access code.

Well, as I said, I went through this page: https://learn.microsoft.com/en-us/ef...arted/winforms to learn EF.
It shows how EF defines a class for each sqLite table.
Hmm. I may not have a problem with that sometimes. But, not always.

What if I want to select only a part of a table (some columns and not all columns?
Then probably EF has invented some way of doing it. Not sure about the details, but I am sure EF has some way of doing it.
What if I want to select expressions on columns for example col1 * col2 + col3 ?
Again probably EF has invented some way of doing this one as well. Not sure about the details, but I am sure EF has some way of doing it.
What if I want to use the "case" phrase in the select statement?
What if I want to use "group by" phrase?
How about "having" phrase?
How about "distinct" phrase?
How about "order by" phrase?
etc.
Does EF have some way of doing all of those things?
In other words, does EF have some simulation for all those things?

It also shows how a relation between two tables can be established, but that is a simple "inner join"

What if I want to use left outer join?
Does EF have some way of doing that too?
Again probably EF has invented some way of doing this one as well. Not sure about the details, but I am sure EF has some way of doing it.
What if I want to use right outer join?
What if I want to use full outer join?

What if I want to define and use foreign keys?

What if I want to create some temp tables, then write sql code to populate them, update them, etc, then use them, and in the end, drop those temp tables?
What if I want to have multiple temp tables, and join them (inner join some, left outer join the others, etc)?
What if I want to create indexes on temp tables?

And all kinds of other complexities involved in sqLite.
Does EF (or any other ORM) have ways of doing all of those things?
In other words does EF (or any other ORM) have ways of SIMULATING all of those things?
I don't know, but I am kind of sure that the answer is probably yes.
Probably EF has ways to simulate all those things.
But, if that is the case, then EF has re-invented a copy of sql language

sqLite language (and any other sql language) has a whole lot of tiny nuances.
Why should there be SIMULATIONS of all those nuances?

I have done a whole lot of sql programming (in Microsoft sql).
A typical sql program that I write (usually in stored procedures) involves creating dozens of temp tables, then numerous complex queries to populate and then update them.
Those queries sometimes involve many tables joining one another (a combination of inner joins and left outer joins, and sometimes right outer joins) between tables (some of them temp tables and some others being hard tables), and then using the results and in the end, dropping the temp tables.

And one last thing: In the stored procedures that I write, sometimes the business logic is so complex that occasionally there are some queries that are composed at run-time before being executed (the query itself cannot be anticipated in full details until at run-time).
Now, THIS ONE I doubt can be done using EF. I am not 100% sure about it, but probably EF cannot do that.

Now imagine, I am being told to do ALL of those things by SIMULATING them by EF or any other ORM.
My first reaction is:
Are you kidding?
Is ALL of that really possible?

[jmcilhinney]

EF and other ORMs are used by many thousands - probably millions - of developers worldwide. Do you think that you've stumbled on something that none of those developers or the people who created the ORMs in the first place have thought of before? Of course all this stuff can be done using ORMs. There may be some edge cases where you have to fall back to SQL - as an example, EF provides a way to execute your own SQL if required - but they are edge cases and not encountered by most people most of the time.

The whole point of these ORMs is that you get to work with application code and applications types, thus benefitting from the full capabilities of your development tools, e.g. Intellisense and compile-time type-checking, rather than writing SQL code in Strings and working with Object references and casting left and right and not finding out if there are any obvious issues until run time. If you don't want to use one then that is absolutely up to you but, if you ever wonder whether it's you who doesn't understand all the relevant information or all the experienced developers creating and using ORMs, assume that it's the former.

Note that there is no "simulation" of anything. ORMs work the way they work but databases are still accessed using SQL, they will map whatever you do to ADO.NET code under the hood. It's still ADO.NET and SQL but you just don't have to write it yourself. You write application code in your chosen language, just as you always do.

[IliaPreston]

Thanks for your help.
Your advice actually convinces me to learn ORMs, but maybe not now.
Probably I'd be better off writing the program that I want to develop by just using direct sqLite, and then later I can learn ORMs.

If I choose to use direct sqLite programming, I need to make sure about one thing please:
I have google searched and found this video:
https://www.youtube.com/watch?v=1FfKGBcJAEQ

At 24m into this video, it explains how to do it.
It uses system.data.sqlite and everything else that it explains.

Now, my question is this:
Is that video the right approach that I am looking for?
Please correct me if I am wrong: As far as I understand, that video looks exactly like what I am looking for, but just I am not sure.
Is system.data.sqlite the right DLL that I am looking for?
And is everything else that that video says about using system.data.sqlite correct?

Please note that even though that video looks like what I am looking for, it is three years old.
So, is it still valid today?
I just need to make sure about that before starting to do programming.

Please advise.
Thanks again.

[jmcilhinney]

I doubt that too many people are going to watch that video to see whether it's right for you or not. I'm certainly not. What I will say is this: there's no such thing as "direct SQLite programming". If you're not going to use an ORM then you're going to use ADO.NET. That means using the appropriate ADO.NET provider for the database you intend to use. System.Data.Sqlite contains the ADO.NET provider for SQLite so, if you're going to use ADO.NET with SQLite, that's what you need. You will then be using types like SqliteConnection, SqliteCommand, SqliteDataAdapter and SqliteDataReader, along with the standard DataSet, DataTable, DataRow, etc that are used for in-app data storage, regardless of the ADO.NET provider you use.