Using SQLite in C# - Building Simple, Powerful, Portable Databases for Your Application
Vložit
- čas přidán 25. 07. 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.
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.
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 - czcams.com/channels/-ptWR16ITQyYOglXyQmpzw.html. 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.
Great video! I'm happy you're back!
Thanks. I'm happy to be back.
Tim Corey, Thanks! I can't express how much I have learned from your videos.
I’m glad my content has been so 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.
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!
Your tutorials are so clear and straight to the point! ❤
I am glad they are helpful.
Thank you for a great introduction to SQLite!
You're very welcome!
Nice tutorial Tim, looking forward to the full course.
Great!
By far and away the best C# material on youtube, thanks for all your help.
You are most welcome. Thanks for watching.
I am going to use this tutorial in some personal tools! Thanks a lot, Tim!
Excellent!
Thanks. I am definitely buying your start to finish course.
Thank you!
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
Great video - looking forward to doing the full course
Great!
Extremely clear explanation, great job!
Thank you!
Thanks for the great course and general sharing knowledge
Thanks for growing your skills with Tim
Superb tutorial yet again! you never fail buddy :)
Thank you!
Thank you for the excellent guide, you are a great teacher
You are welcome.
Very good video. Exactly what I was looking for and the video already answered a lot of my questions.
Glad it was helpful!
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!
Thanks a lot for the video and explanation man, was easy to understand and extremely helpful.
You are most welcome. Thanks for watching.
Thank you so much for this! You're an absolute lifesaver!
You are most welcome. Thanks for watching.
Hello Corey, Thank you for the excellent tutorial
You are welcome.
great tutorial!
샘 짱이에요 :) 쉽게 잘 가르쳐주시네요 from s.korea
Thank you!
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
YES, I have been waiting for this!
Excellent!
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!
Thank you. it is what i was looking for!
You are welcome.
It is a very good tutorial to learn to connect a database for quick use for your application
I am glad it was helpful.
Thanks for this tutorial Tim
You are welcome.
Thank you very much! very useful course.
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 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!
Thanks for informative video, Tim!
Thanks for watching
Great video tutorial! Thanks for that!
You are welcome.
Waw, this is the best explaining. It's incredible
Thank you!
Thank you so much for posting this! New subscriber here!!
You are welcome.
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.
Thanks for the video Tim
You are welcome.
Awesome Tutorial. Thanks!
You are welcome.
Thank you so much sir.... Nice and easy tutorial.
You are welcome.
Excellent video. Thank you
You are most welcome. Thanks for watching.
Thanks a lot for this video. Quite explanatory!!!
Glad it was helpful!
A very comprehensive method Tim
Thanks!
This helped me a lot, thank you
You are welcome.
Thanks man! Helped me alot.
You are welcome.
Thank you very much Tim.
You are welcome.
super work Tim Corey
Thanks!
Thank you very much Tim!
You are welcome.
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.
You AMAZING Tim. Thank you
Thanks for trusting Tim
Gracias excelente Mister Tim.....
Thank you!
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.
good job beautiful video very easy to understand useful for beginner helpful for manipulate project include sqlite data for mono pc i like your job continue
Thank you.
Great, Thank you so much.
You are welcome.
Super video thanks!
Glad you liked it!
Thank you for the video! Just FYI, in WinForms you would want to use a Binding List, not an observable collection.
Thanks!
Thanks for the video, it was powerful
You are welcome!
@IAmTimCorey: Fantastic tutorial! I've worked with SQLite on a mobile app via xamarin forms, but the process was VERY different. Thanks!
Thanks!
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.
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.
I love your tutorials, really easy to understand and follow. At the moment i'm struggling with creating and printing custom documents in c# (reports, receipts) . I tried RDLC but it feels kind of clunky . That would my suggestion for a future video.
Thanks for the suggestion.
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.
Thank you for excellent tutorial....deeply appreciated.
You are welcome.
Tim Corey you saved my ass, love you
Great!
Gracias Mister Tim
De nada.
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!
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.
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?
very very nice thanks so much
Thanks for the support! Tim appreciates it.
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?
I like the video before watching 😊😊
I am glad you have such faith in me.
Tim Corey is my .net god!
lol thanks.
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?
Thank you so much
You are welcome.
Thank you!
You are welcome.
great video.
Thanks!
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?
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!
Thank u Tim
You are welcome.
Hi tim and thanks for the amazing tutorial. I have a question. I'm trying to release a standalone exe file for my application. 35:31 Except for the ".xml" files, I was able to embed all of the other files into the exe. Is it possible to embed them as well?
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 ?
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)
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!
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.
By far the best SQLite in C# example video I saw. The only problem I'm having now is in visual studio it runs fine (debug and release mode), but when I publish it I'm getting an 'unable to load dll 'sqlite.interop.dll'' error.
When you publish, make sure the SQLite interop dlls are going too. I believe they are in a sub-folder under Debug or Release and there are separate DLLs for 32- vs 64-bit.
@@IAmTimCorey I think this is my problem too. How do you "make sure" they are included in the publish?
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
I included my .db file in the project and when I build in Release mode it creates a debug folder within my release folder, is there a way for this to be avoided?
very good.
Thank you.
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
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
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
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.
hey, thank you for all the great content
love to show and learn from your videos :)
i have quastion hope you can replay.
i am using UWP app and there is not App.Config in uwp, how can i connect the SQLite to the uwp app?
thank you.
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.