Using SQLite in C# - Building Simple, Powerful, Portable Databases for Your Application
HTML-код
- Опубликовано: 21 июл 2024
- Have you ever wanted to store data for an application but didn't want to deal with the hassle of a full database server? Or maybe you wanted to have one database per installation. Those and many other scenarios are a perfect fit for SQLite. Today I am going to show you how to get started using SQLite in C#. We will build a small database, attach it to a C# project, and then wire up our project to read from and write to the database.
SQLite From Start to Finish course (now renamed to Bill Time): www.iamtimcorey.com/salespage...
Source Code: leadmagnets.app/?Resource=SQL...
Patreon: / iamtimcorey
Newsletter signup: signup.iamtimcorey.com/
Thanks to Ralfs HBK for the chapter breakdown:
0:00 - Intro
1:14 - Demo app overview (win form app)
4:30 - What is SQLite
5:09 - How to create and build SQLite Database: DB Browser for SQLite
11:17 - Database Structure
13:04 - Adding the Database to the project
15:26 - Adding the Connection string
18:15 - SQLite and Dapper NUGet packages
21:58 - Data access setup
28:17 - Retrieving data form DB: LoadPeople()
30:14 - Sending data to DB: SavePerson()
31:51 - Wiring up the Demo app
33:08 - Workings with the Database
36:18 - Summary and concluding remarks
Tim's courses are by far the most clear and concise I've done. Having the code to download to play with is so useful. A great resource.
I appreciate the kind words.
Thank you for a great, direct and useful tutorial for SQLite in C#. I am new to SQLite and this video was a real example of a quality presentation with real-world scenarios of using C# with a file-based database system in a contained environment. Well done, sir.
Thank you for watching. Please check out the other videos on Tim's Channel.
Hi Tim, you know the saying you cannot teach an old dog new tricks? Well, you busted that one. You taught this old dog a new trick and it's just marvellous. Thanks man!!!!
Awesome! I’m glad it was helpful.
I had to learn a bit of java lately, forcing me to watch some different tutorials, and let me say this, you have achieved your goal. You do make learning c# easier.
I appreciate the kind words.
Glad to see you have pulled through. And thank you for another great video.
You are welcome.
This is an incredible tutorial, even if it just covering the very basics.
i gotta admit, in itself SQLite is quite a powerful tool.
Thanks a lot, Tim!
You are most welcome. Thanks for watching.
Great video! I'm happy you're back!
Thanks. I'm happy to be back.
Love you, Tim!!! I cannot describe how happy I am to find this tutorial :DDD Thank you endlessly !
Be sure to check out the whole channel - ruclips.net/channel/UC-ptWR16ITQyYOglXyQmpzw. Use the search icon under the Subscribe button to search within the channel to find the training you need next.
Your channel is a gold mine. Super awesome content.
I appreciate the kind words.
Tim Corey, Thanks! I can't express how much I have learned from your videos.
I’m glad my content has been so helpful.
This is the video I have been waiting for. Thanks Tim. I have a number of projects I get me teeth in to now.
Excellent!
Just need to decide witch one to start with lol
thanks, Tim for all the videos and effort your providing. you are a legend, you are the best teacher ever. definitely, I will have a course with you one day.
I appreciate that!
Thank you for a great introduction to SQLite!
You're very welcome!
Your tutorials are so clear and straight to the point! ❤
I am glad they are helpful.
Nice tutorial Tim, looking forward to the full course.
Great!
Great video - looking forward to doing the full course
Great!
I am going to use this tutorial in some personal tools! Thanks a lot, Tim!
Excellent!
YES, I have been waiting for this!
Excellent!
By far and away the best C# material on youtube, thanks for all your help.
You are most welcome. Thanks for watching.
Thanks. I am definitely buying your start to finish course.
Thank you!
Superb tutorial yet again! you never fail buddy :)
Thank you!
Extremely clear explanation, great job!
Thank you!
Thank you so much for this! You're an absolute lifesaver!
You are most welcome. Thanks for watching.
17:26 Relative connection string
19:11 - 21:50 Dependent packages
26:00 Get connection string in library project
29:47 Query list of PersonModel from db
31:50 Write a person to db
I added it to the description. Thanks so much.
@@IAmTimCorey Can you set up a pay method for AliPay? I believe there are many Chinese subscriber like me who what to enroll your course. Most chinese don't use credit cards.
Unfortunately, I don't have the ability to do that. Teachable (my host) only provides Stripe and PayPal.
0:00 - Intro
1:14 - Demo app overview (win form app)
4:30 - What is SQLite
5:09 - How to create and build SQLite Database: DB Browser for SQLite
11:17 - Database Structure
13:04 - Adding the Database to the project
15:26 - Adding the Connection string
18:15 - SQLite and Dapper NUGet packages
21:58 - Data access setup
28:17 - Retrieving data form DB: LoadPeople()
30:14 - Sending data to DB: SavePerson()
31:51 - Wiring up the Demo app
33:08 - Workings with the Database
36:18 - Summary and concluding remarks
Great job, thanks again
Thanks for the great course and general sharing knowledge
Thanks for growing your skills with Tim
Boss ...you are just brilliant ......wooww...every topic explained very clearly.....u know what is missing in daily soap and u just bang on fish eye.....
Thank you!
Very good video. Exactly what I was looking for and the video already answered a lot of my questions.
Glad it was helpful!
Thank you so much for this video Tim, I've been looking for a basic video like this for a good few days, the whole SQL localdb vs SQL Express vs SQLite was just far too confusing when I knew SQLite was what I needed, I just couldn't find a video to show a simple connection (ignore me (apart from the thanks bit) as these might just be words from a rambling programming student).
Glad it was helpful!
Thank you. it is what i was looking for!
You are welcome.
when you use the var.Query(query , dynamicParameters);
how does that save the data from the database? like if i have a person class with firstName field and i have a table with a firstName column, how does the Query... know to save the data in each column to the specific field needed in the object?
I just needed this. Thanks for the video. There are a lot of resources out there but they are just messy.
Glad it was helpful!
Thanks a lot for the video and explanation man, was easy to understand and extremely helpful.
You are most welcome. Thanks for watching.
Thanks for this tutorial Tim
You are welcome.
Thank you for the excellent guide, you are a great teacher
You are welcome.
i love the intro, iit explains EXACTLY what I am looking for, so in watching this, I went, yes... yees.. YES!!!
Great!
Thank you very much! very useful course.
You are welcome.
Tim - "Its not too much to ask a person to give me a first and last name". Maddona- "Am I a Joke to you?! Do I not get to play in your database?" :P J/k, this is amazing and I'm subscribing just because of how well laid out this is. I needed something to explain c# and sql lite but didn't want a 3 hour course, and I needed some basics on the sql lite part as well. You did a great job with this!
I'm glad it was helpful. And yes, people will always have an exception to the rule, which makes creating any type of validation hard.
Great video tutorial! Thanks for that!
You are welcome.
I love the simplicity if SQLite, especially with WPF development.
I’m writing an app now that will allow the user to password protect specific records.
For the average user, they won’t know how to open the .Db file, but I can’t help but ask, HOW can I encrypt the SQLite database?
I’ve seen mentioned SQLite Cipher, but is that the best way to go when working w/WPF applications?
Any suggestions are definitely appreciated!
I also feel SQLite encryption would make a great follow up video to your other training on SQLite!
Thanks!
It is just password protection (which is probably all you need) but have you tried this: stackoverflow.com/a/17855620/733798
Hello Corey! I found your implementation of Sqlite a little bit different but interesting appoarch and I would like to know how this PersonalModel works and it's functions. I am a real noobie when it comes to this Models and I would like to ask if you have any videos about it and how it works because I can't seem to see any related videos of you regarding it. Thank you!
Hello Corey, Thank you for the excellent tutorial
You are welcome.
Thanks for the video Tim
You are welcome.
Is there a good way using this setup, that I could pass a network location that the user enters in the UI, into the Sqlite connection as the data source... instead of using the db file created by the app. So rather than a new database being written for each app... if multiples of this app were on the same network, they could both browse to the same db file location?
super work Tim Corey
Thanks!
Thanks for informative video, Tim!
Thanks for watching
Thank you so much for posting this! New subscriber here!!
You are welcome.
Waw, this is the best explaining. It's incredible
Thank you!
great tutorial!
샘 짱이에요 :) 쉽게 잘 가르쳐주시네요 from s.korea
Thank you!
Awesome Tutorial. Thanks!
You are welcome.
Hello Tim, I am a big fan, thank you for your content! I have a question related to this tutorial. When I build my application, open it using the .exe file and add some data (in RELEASE mode), all CRUD operations works fine. Once I make a change in the program, and I click on the RELEASE -> Build Solution, the database always gets wiped out and I have to start from scrach. It works fine on the DEBUG mode, keeps the data intact, I can rebuild it as much as I want and it will still be there, but once switched to RELEASE mode and the program gets re-built, I end up with a blank DB (all tables and columns are there but the data is gone). I have set Build Action -> "Content" and Copy to Output Directory -> "Copy if newer". Could you help me out please?
It is a very good tutorial to learn to connect a database for quick use for your application
I am glad it was helpful.
Hi Tim!
Great video that helped me kickstart my project. Thanks!
I have issues regarding foreign keys that I would like to implement in my database, specifically with "on delete cascade". I have included "PRAGMA foreign_keys=true" in my connection string, but deleting a row in my "master" table does not result in its "children" being deleted. (Unless i delete the row by using DB Browser). Any hot tips would be highly appriciated.
Do I need to change my localdb's (service-based database or .mdf) "Copy to output directory" (@14:08) to "copy if newer"? Because by default it is Copy always, or is copy if newer only set for sqlite database?
Hey Tim! Your channel is the go to resource for my C# learning. Thanks!
In VS2019, what template type should I select to create the DemoLibrary Project? By template I mean, when I try to create the project, I have to choose one of these (e.g., Console App, or Class Library, or WPF App, .......).
Class library (.net framework)
Thanks man! Helped me alot.
You are welcome.
Hi Corey,
Can you please share your thoughts on encrypting sqlite db?
What may be the best and cheapest way to encrypt sqlite db?
Thanks!
Excellent video. Thank you
You are most welcome. Thanks for watching.
This helped me a lot, thank you
You are welcome.
Hello Tim Corey. I was wondering if you could please explain why the Database file that we created and saved to the WinFormUI project folder is not written to, but instead another Db file is created in the WinFormUI\bin\Debug directory that is written to? Also, if you have a link to some literature that explains why the Db in the Debug directory is used that would be helpful too. Thanks !!!
The connection string tells the application where the database is. Since the connection string did not specify a path, it assumes a relative path (relative to the executable running the command that uses the connection string). That's why we have to copy the database into the debug folder. Also, by using a copy of the database instead of the one we include with our project, we won't have sample data in the database we give to customers (unless we want it in there).
Hi, great video, watched till the end!
What would you suggest as the optimal way of inserting a dataset in a SQLite datatable, since we cannot use table valued parameters?
Just loop through it, doing an insert per row. It feels inefficient, but it is actually not.
I know this is sort of outside the scope but, If you want to distribute your application with some tables containing data, I prefer using EmbeddedResource as the compile action and using that to create the db file if it doesn't exist at the specified path. This allows you control when the database file is copied/written. I feel like this offers more control over the database file on deployment when users execute the program. You can offer the user features such and restarting from scratch you can disconnect from the database, delete the db file and replace it with the embedded resource version of the database. Like for example if the database somehow got corrupted.
Thanks for sharing!
Hi, first of all, thanks for your work.
Am working on project and I need to ask like how would you edit the first or last name without deleting and creating new a record in your project.
You mean like running an update statement? Like: update TableName set FirstName='Tim", LastName='Corey' where id=1
Thank you for the video! Just FYI, in WinForms you would want to use a Binding List, not an observable collection.
Thanks!
Thanks a lot for this video. Quite explanatory!!!
Glad it was helpful!
High quality like all your trainings! One question: which reporting tool can you recommend for c# and sqlite?
Get that one a lot. Exploring options so stay tuned.
Interesting video and certainly a lot of help but I have a couple of questions.
For reference im working on a game server adapting open source code to tailor the game to how myself and a friend want it...started off as a hobby to keep sanity while in lockdown, but found im enjoying coding it and seeing what can be done.
I want to be able to store 4 different things to a players characters and not 100% sure about how to go about retrieving and saving them.
In the video you had it load at the start (or when you click refresh) and save as each entry was made, is that the best way to do dynamicaly changing values? load them into the players object when they connect and save the changed values to the database when they disconnect.... or is it better/possible to save them to the Db when they change (still reading them from stored values in the player object that will be updated at the same time) ?
the values I want to store also lead me to another question: each player will have up to 20 professions and each profession has the following values:
a boolean (i can use INT 1 or 0 for true false)
A byte (int again..)
A long (int? heres an issue how do you save a long thats over the 2 billion limit of int?)
and my final question
each player also needs a list of waypoints stored:
containing,
map name (string)map id (int) coords (Point...though essentially two ints, x and y)
but is it even possible to save a list to a table in sql? those 4 data sets suggest this is not the database i am looking for...but then Blob...i dont know what Blob is lol
Despite the fact im still searching around and looking into sql so may find the answer, I figured why not ask an expert lol. Sorry if these are really simple questions, I am only a hobby coder so its possible I am asking something dumb
Yes, you could read the data into a class object like I did in this video. Then use that object to update the values as you go. At the end (or whenever they update the data - whichever you choose), you could take this object and use it to save the data back to the database.
If you needed to exceed the storage capacity of an integer, you would probably need to use the text type.
If you want to save a set of data for a person, you would want to save that set in a different table and link it to the primary table by id.
You AMAZING Tim. Thank you
Thanks for trusting Tim
Hello, loved the vid. I although have a question about my situation.
I want to create an Windows Forms app in which the user can look for book titles/publishers/shelf number from book entities that are in an SQLite database. The application should be "portable" meaning it should all work between any computer with a single executable. Unlike your example where you just added entities, i want to manually add all the book titles along with publisher and shelf number (which shelf they are at)and have them stored in the database and the user should be able to search/delete/edit and add more to the db. For example if they search for shelf 4 to have all books on that shelf shown etc. What is the best way to go about this?
Thank you
All that is possible. The idea of a single executable is fine as long as you know that the config file and any DLL files will also come with the executable (as will the database).
Thank you very much Tim.
You are welcome.
What are the advantages of using SQLite over SQL localDB? I guess they are all file based database and with sql localdb you could easily migrate to a full SQL very easily by just changing a connection string I believe. Why would one prefer SQLite over localdb?
Thanks
SQLite is designed to be portable with just a dll and the database file.
Thank you very much Tim!
You are welcome.
Thank you so much sir.... Nice and easy tutorial.
You are welcome.
A very comprehensive method Tim
Thanks!
At 15:30 you go over communications with the database using App.config, but these references are no longer used in Net 6.0. What would you recommend people following along with your video at this point? Do you have another video that is similar to this but more up to date?
You would use appsettings.json instead, but beyond that, there isn't much change in the code. Here is a video on appsettings: ruclips.net/video/_2_qksdQKCE/видео.html
Hello Tim, I'm happy you're back ! I got a question, are you planning on doing a NHibernate 4+ course/tutorial?
Probably not. It isn't something I use.
Hi i'm tryna make web app like exactly google drive (login with user and pass and download, upload, delete "any kind of media files") for my school project , i can't find helpful video except this but this isn't enough (has to be c#) . Any recommend ?
Thanks for the video. I can't figure out where the Dapper is been used. So I actually think it is not needed for this example. Is this correct?
The Query and Execute methods are from Dapper. Query gets the data from SQLite and maps it to an IEnumerable for us. If we didn't have that, we would have to use ADO.NET and get a DataTable back that we would then have to manually map to models and carefully cast the data to the correct types. Execute goes the opposite direction. It takes our parameters in whatever type we give it and correctly builds the SQL command with SQL parameters in order to help prevent SQL injection. So yes, we definitely need Dapper here. Otherwise, we need to write a LOT of extra code.
Great course, Tim. I am a new subscriber and I have been learning more from your tutorials than I have on other learning platforms. I encountered an interesting issue with Dapper while coding your demo. It turns out that Dapper isn't a big fan of interfaces so when I tried to be "smart" and created a list of IPersonModel objects, the insert failed. I had to switch my type back to the actual type.
Yep, because Dapper is going to use an actual type to do work. It cannot take an interface because it doesn't know which type to use to do the actual work.
Tim Corey you saved my ass, love you
Great!
Hi Tim, i have a question. What is the purpose of the DemoLibrary project? Is that needed or can i have my models and NUGet packages in the UI project?
It keeps a separation between your business logic and data access from your user interface. Putting everything in your UI locks you in by tightly coupling your application together. By using a class library, we can disconnect this and make it easier to upgrade parts of our application without upgrading the entire thing.
Super video thanks!
Glad you liked it!
Gracias excelente Mister Tim.....
Thank you!
Tim, I found this video incredibly useful. I am just getting started on a project where I plan to use Blazor and SQLite, which are both new to me. However, I am struggling trying to get the "LastInsertRowId" after using SQLiteConnection() and executing an Insert in the way you describe in this video. The data is getting inserted into the table but I get the exeception "System.InvalidOperationException: Database connection not valid for getting last insert rowid" when I try to access cnn.LastInsertRowId. What needs to be done to get this to work?
It turns out that LastInsertRowId cannot be used aster using the SQLiteConnection() the way it is used in this video. Instead I had to use CreateConnection(), then set the connection string before opening the connection.
hi Tim, I need help about that how to compile sqlite with ICU. I cant search unicode character by lowercase or uppercase. so I have to get all data ram by AsEnumerable. Is there any other method for this? How to compile sqlite with ICU. Can you explain it by video
Tim, I agree the way of teaching is amazing with slow pace and injecting in viewers mind slowly and nicely.
But at time confusing may be because of the method you took up about SQL connections and accessing.
In fact I saw better methods in the net without confusion.
Thanks any way...
Well, if you know an easier way of doing it for you, that's great!
Pls. don't mind Tim.
Again and again I say your way of teaching is ultimate. That was the only video confusing a bit, rest all the best.
Thanks for the useful videos.. One useful suggestion is please zoom to the text in focus to reduce strain in eyes especially for mobile phone user
I do what I can but trying to scale it for mobile is almost impossible.
IAmTimCorey, did you find a way around having to add a reference to System.Data.SQLite in the assembly consuming your library?
Nope, that is still the thing to do.
Hi Tim, if i have 2 table that need to fetch data, do i have to write 2 queries for each model?
Or can i write 1 query and just replace the table name and model with variable?
You could do a replacement of the query table name but I would be REALLY careful. That can introduce injection vulnerabilities if you don't do it right.
Great, Thank you so much.
You are welcome.
Hi Tim, could you make a video on using Sqlite and Dapper for reading an XML file and copy the elements content on a Sqlite table (or Oracle table)? (or give me some hints for this task).
Reading/writing XML is on the list. Not sure when I'll get to it since it isn't a huge need, but it is something a few people have asked for. The more that ask, the more important it becomes.
Thank you for excellent tutorial....deeply appreciated.
You are welcome.
The reason you need to reference the sql lib also in WinFormUI, is a visual studio bug, that sticks around.
I usually use typeof on a class from sqlcore lib in the DemoLibrary, so visual studio see's you actually using it, and doesn't get rid of the .dll, and it doesn't need to be referenced in the WinFormUI.
Thanks for sharing.
Thank you for the video.
You are welcome.
Great video. But I have a problem with sqllite. It does not handle storage to well. What I mean is that once a data is deleted. It does not use that space again. So the database can get bloated with empty space.
Awesome video first of all. What are your opinion for using sqlite if application generating say 1Gb data per year. Will it perform well.
It depends. If that means 1GB of data and then you start over, SQLite is probably a good fit. If it is cumulative, you might want to think through your options. SQLite can contain terabytes of data but remember that it is one file. Trying to manage that file on disk could be an issue if you use this for years. It would probably be fine, but make sure you don't store it on a Fat32-formatted disk (max size would be 4GB, I believe, although I have heard that 2GB is the true max in some circumstances) and make sure your backup strategy can handle a large file. Honestly, even a 10GB file isn't horrible to deal with so you should be fine.
Hey Tim, thank you for the video, I gave password to my connections string but I keep facing this error.
System.Data.SQLite.SQLiteException: 'file is not a database
file is not a database'
it's Okay if I remove the password section from connection string, Idk if I need to use that password somewhere or something else. I appreciate if you help me with this
In adapting this to another table, I get the following error on the cnn.Query execution step: "A parameterless default constructor or one matching signature" followed by the fields in the table in db. I've checked the object, and all the field names match up.
I have ensured that there is a constructor for the object that matches the signature exactly to what is shown in the error message.
Determined the issue; from SQLite, there are only bare bones types - and for int's it's Int64 while my contstructor was just int. I should not have assumed there would be an implicit casting.
Thank you for sharing this experience and the solution. I know it will help others
Great Video Mr. Corey. I just have a quick question. My listbox loads with items worded demolibrary. personmodel, not sure why thats happening when I used .ToList on my returned output or maybe this issue involves a setting on the listbox not sure. Thanks !
Nevermind. I realized I had commented out my display member line listPeopleListBox.DisplayMember = "FullName";
I am glad you figured it out.
I'm getting the exception " System.Exception: 'Keyword not supported: 'version'. '"
I checked everything in my stringConnections and everything is ok.
Anyone has idea what could it be?
when I copy the files to another folder and proceed to execute the .exe it is ok, but when I do an insert or update it shows me the following message: Error "attempt to write a readonly database"
Please help me, I want to make portable programs.