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.