Using SQLite in C# - Building Simple, Powerful, Portable Databases for Your Application

[@RalfsBalodis]

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

[@IAmTimCorey]

Great job, thanks again

[@afterbunny257]

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

[@IAmTimCorey]

I added it to the description. Thanks so much.

[@afterbunny257]

@@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.

[@IAmTimCorey]

Unfortunately, I don't have the ability to do that. Teachable (my host) only provides Stripe and PayPal.

[@ManieVerster]

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!!!!

[@IAmTimCorey]

Awesome! I’m glad it was helpful.

[@gerrycallaghan5519]

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.

[@IAmTimCorey]

I appreciate the kind words.

[@MichaelMyersDoverPA]

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.

[@tomthelestaff-iamtimcorey7597]

Thank you for watching. Please check out the other videos on Tim's Channel.

[@acidhauss7018]

By far and away the best C# material on youtube, thanks for all your help.

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@UnknownMoses]

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.

[@IAmTimCorey]

Thanks for sharing!

[@maciejk125]

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.

[@IAmTimCorey]

I appreciate the kind words.

[@djchaseful]

Tim Corey, Thanks! I can't express how much I have learned from your videos.

[@IAmTimCorey]

I’m glad my content has been so helpful.

[@tanu3873]

Great video! I'm happy you're back!

[@IAmTimCorey]

Thanks. I'm happy to be back.

[@katerose6161]

Love you, Tim!!! I cannot describe how happy I am to find this tutorial :DDD Thank you endlessly !

[@IAmTimCorey]

Be sure to check out the whole channel - kzbin.info/door/-ptWR16ITQyYOglXyQmpzw. Use the search icon under the Subscribe button to search within the channel to find the training you need next.

[@janne_kekalainen]

Your channel is a gold mine. Super awesome content.

[@IAmTimCorey]

I appreciate the kind words.

[@nopelganger]

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!

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@ZajoSTi]

Glad to see you have pulled through. And thank you for another great video.

[@IAmTimCorey]

You are welcome.

[@MikePageDev]

This is the video I have been waiting for. Thanks Tim. I have a number of projects I get me teeth in to now.

[@IAmTimCorey]

Excellent!

[@MikePageDev]

Just need to decide witch one to start with lol

[@manukartofanu]

Thank you for a great introduction to SQLite!

[@IAmTimCorey]

You're very welcome!

[@khandakarrashedhassan8193]

I just needed this. Thanks for the video. There are a lot of resources out there but they are just messy.

[@IAmTimCorey]

Glad it was helpful!

[@samjaouhar7246]

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.

[@IAmTimCorey]

I appreciate that!

[@kopilkaiser8991]

It is a very good tutorial to learn to connect a database for quick use for your application

[@IAmTimCorey]

I am glad it was helpful.

[@alcidesneto6415]

I am going to use this tutorial in some personal tools! Thanks a lot, Tim!

[@IAmTimCorey]

Excellent!

[@GiorgiBekurashvili]

Your tutorials are so clear and straight to the point! ❤

[@IAmTimCorey]

I am glad they are helpful.

[@ehsandaneshvar]

Perfect / Simple and Useful even for experts

[@IAmTimCorey]

Thanks!

[@anshul1212]

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.....

[@IAmTimCorey]

Thank you!

[@hansbyager5795]

i love the intro, iit explains EXACTLY what I am looking for, so in watching this, I went, yes... yees.. YES!!!

[@IAmTimCorey]

Great!

[@michabarbarski7362]

Thank you for the excellent guide, you are a great teacher

[@IAmTimCorey]

You are welcome.

[@mohamedazzouz6086]

Hello Corey, Thank you for the excellent tutorial

[@IAmTimCorey]

You are welcome.

[@sc0tty319]

great tutorial! 샘 짱이에요 :) 쉽게 잘 가르쳐주시네요 from s.korea

[@IAmTimCorey]

Thank you!

[@StudentCompanion]

Great video. Thanks. Just to clear my understanding, SQLite is just meant for 1 user, you can't point 2 or more machines to the same SQLite database so they have same data like in full sql.

[@IAmTimCorey]

You technically can, but SQLite was designed for one user. Having multiple use it will increase the likelihood of collisions.

[@StudentCompanion]

@@IAmTimCorey Thanks.

[@nilsfrahm1323]

I think with sqlite you may even be limited on how many connections you can open to db. It is limited to single user.

[@harag9]

Nice tutorial Tim, looking forward to the full course.

[@IAmTimCorey]

Great!

[@thejourney4609]

In .Net Core 3.1 using Visual Studio 2019 Community Edition, you have to download a Nuget Package Called System.Configuration.ConfigurationManager in order to read the app.Config. See video clip at 24:53.

[@IAmTimCorey]

Yep, I was using the .NET Framework in this video. Thanks for sharing how to do it in .NET Core 3.1.

[@thejourney4609]

@@IAmTimCorey Thanks Tim for the video. Your samples are really amazing. My app is 100x better now incorporating your sample!

[@the_noise_principle]

That was the just info I needed. Thank you!

[@ordishysa]

A very comprehensive method Tim

[@IAmTimCorey]

Thanks!

[@fbaez51]

Tim Corey is my .net god!

[@IAmTimCorey]

lol thanks.

[@n9434178]

Great video - looking forward to doing the full course

[@IAmTimCorey]

Great!

[@nishan375]

Thanks. I am definitely buying your start to finish course.

[@IAmTimCorey]

Thank you!

[@vanttildet3069]

Thanks for the great course and general sharing knowledge

[@tomthelestaff-iamtimcorey7597]

Thanks for growing your skills with Tim

[@Malikz29]

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

[@IAmTimCorey]

I do what I can but trying to scale it for mobile is almost impossible.

[@ivandrofly]

I like the video before watching 😊😊

[@IAmTimCorey]

I am glad you have such faith in me.

[@grampygamer8584]

Superb tutorial yet again! you never fail buddy :)

[@IAmTimCorey]

Thank you!

[@markcediegabinete2647]

Thanks for this tutorial Tim

[@IAmTimCorey]

You are welcome.

[@belmiris1371]

Thank you for the video! Just FYI, in WinForms you would want to use a Binding List, not an observable collection.

[@IAmTimCorey]

Thanks!

[@deborahconde4634]

Tim Corey you saved my ass, love you

[@IAmTimCorey]

Great!

[@heinrichwurstwatten7553]

YES, I have been waiting for this!

[@IAmTimCorey]

Excellent!

[@captainslowmtb7409]

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).

[@IAmTimCorey]

Glad it was helpful!

[@Jfarro]

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!

[@IAmTimCorey]

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.

[@ghostcode829]

Thank you so much for this! You're an absolute lifesaver!

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@DamagedDingo]

@iamtimcorey any chance of a WPF example? I can't work out where the connections strings etc go.

[@IAmTimCorey]

They go in the same place. WPF projects have an app.config file too. If you are referring to WPF Core projects, you would put the connection string in the appsettings.json file (examples of using connection strings with both of these types and more are in the TimCo Retail Manager series).

[@janne_kekalainen]

I was just thinking the same thing with .NET Core and figured it out with a bit of googling. This works: 1. Add "System.Configuration.ConfigurationManager" NuGet-package (instead of reference) - Major change 2. Create App.config with the connection string inside configuration (like in the video, but doesn't have any of the other groups) then set the file to "content" and "copy to output" (like the database file). - Has to be created manually. 3. Rest works like in the video.

[@vanishdoma2675]

Thank you. it is what i was looking for!

[@IAmTimCorey]

You are welcome.

[@ptsg]

Extremely clear explanation, great job!

[@IAmTimCorey]

Thank you!

[@HopInTheCloud]

Very good video. Exactly what I was looking for and the video already answered a lot of my questions.

[@IAmTimCorey]

Glad it was helpful!

[@signo18]

How can I use triggers in Sqlite and c#? Is that possible? I don't think so, because sqlite doesn’t work with stored procedured. How do I send values to my triggers without stored procedured?

[@stanscocom]

Unfortunately, you cannot use Stored Procedures in SQLite. You don't send parameters to a Trigger. You automatically have access to inserted, updated and deleted rows when you're writing the trigger code..

[@signo18]

@@stanscocom so i can't send dinamics values for the triggers?

[@stanscocom]

​@@signo18 No - Triggers are not really triggered by your code. They are triggered by the database when table data is changed. So, even in the SQLite Browser (where you can edit the data in the database), if you change the value of a column that has a trigger on it, the database will automatically execute the trigger immediately.

[@nilsfrahm1323]

You could make an extra table for parameters, e.g. TrgParams, and in your table add a column called TrgParamID. Insert parameter in TrgParam table, get its id, and when updating your real table, also set value of TrgParamId column to the new id, e.g. 10. The trigger should fetch the id from the new record, search for the parameter in the TrgParams table, then work based on that.

[@jiminaaa1993]

Thank you so much sir.... Nice and easy tutorial.

[@IAmTimCorey]

You are welcome.

[@sooyx]

23:00. I get the error "type or namespace "Personmodel" could not be found". Please help and thanks in advance.

[@IAmTimCorey]

You probably did not mark your PersonModel class as public. Either that or you mistyped it as "Personmodel" when you meant "PersonModel".

[@trustingod0]

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 !!!

[@IAmTimCorey]

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).

[@connecting1409]

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?

[@IAmTimCorey]

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.

[@zerosandones7547]

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?

[@sooryaprakash8984]

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...

[@IAmTimCorey]

Well, if you know an easier way of doing it for you, that's great!

[@sooryaprakash8984]

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.

[@_savage]

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?

[@williambell4591]

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!

[@IAmTimCorey]

It is just password protection (which is probably all you need) but have you tried this: stackoverflow.com/a/17855620/733798

[@DanFlakes]

Thanks for informative video, Tim!

[@tomthelestaff-iamtimcorey7597]

Thanks for watching

[@pequadcob2009]

I am unsure why you need a second project (DemoLibrary) to do this SQLite thing. Could you do all that within one project or do you need to have two projects to accomplish a SQLite database connection?

[@IAmTimCorey]

It is good to separate out your data access from your user interface. That way you can change your user interface without changing your data access. It makes for a cleaner design. You don't have to do it, though.

[@younessesoft]

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

[@IAmTimCorey]

Thank you.

[@janostrowski8932]

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.

[@IAmTimCorey]

You mean like running an update statement? Like: update TableName set FirstName='Tim", LastName='Corey' where id=1

[@bobbybooballin3218]

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.

[@fouadchelfi2590]

Thank you very much! very useful course.

[@IAmTimCorey]

You are welcome.

[@DJone4one]

Cool thanks for the video. It helped me to get back into the database. However, I have a little problem with this example. I have built a small inventory system in which the item, quantity, unit price and total price should be stored. The debug database shows the items, quantity, unit price and total price correctly, it also adds up the total price correctly and saves it to the database, it just doesn't show up correctly in the listbox in the window. It says: 1 0 0.000€ 0.000€ Do you have an idea how to solve this? Maybe switch to DataGridView, or?

[@IAmTimCorey]

The issue isn't your control type. The issue seems to be in either the data itself or in the display of the data. Do some debugging and verify that you are getting the correct data back before binding it. Then check the display values.

[@fredrikjag]

Hi Tim! So im fairly new to programming, and have had the recommendation to learn Dependency Injection, to create connection/close connection to my SQLite database. I am having a hard time understanding on what DI is, and how to build it. I've also heard i should follow "KISS" and "DRY" when coding. I suppose that is why i should use Dependancy Injections in my code? Have i understood this correctly?

[@IAmTimCorey]

First, if you are fairly new to C#, I would recommend you wait on DI until WAY down the road. That is a hard thing to learn up front because DI depends on advanced OOP, and a number of other smaller things. Learn the basics first (variables, if/else, for, foreach, methods, classes, project types, etc.), then move on to OOP (class instances, static classes, properties, interfaces, inheritance, abstract classes, etc.) Then dip into data access and finally into SOLID. I have videos that cover most, if not all of these things. Just take it slow. When you learn something, create 3-5 practice applications that use what you learned (small ones). That will help you grasp the concepts better and it will help build up that foundational muscle memory for writing code. Also don't forget to put it all together a few times in a larger project (like my C# Application from Start to Finish course here on KZbin does).

[@fredrikjag]

@@IAmTimCorey Thats what i thoght, i should do. I have an application i am passiomate about, and would like tp create. I've learnt alot in a short matter of time, however beeing told i should learn DI have made me more upset with programing than seeing the fun in learning it🙈😑

[@IAmTimCorey]

Here's the deal - people are going to tell you their opinions as fact. It lends to a sometimes toxic environment. The key is to remember that no one has it fully figured out. You code at the level you are at. Don't try to be a senior developer on day one. Write ugly, messy, barely-functional code. As you grow in your abilities, your code will evolve and you will cringe at what you wrote six months ago. Guess what? I still cringe at what I wrote six months ago. That's actually a really good thing. It means you are progressing as a developer. If you try to leap right to senior developer, you won't make it. Think of it as taking the stairs. If you try to stretch out and put your foot on the top step, you will fall short and no matter how much effort you put in, you won't get to the top. However, if you take one small step at a time, you will reach the top easily. Have fun where you are at and ignore people that tell you that you are doing it wrong. Just don't forget to learn how to improve whenever possible.

[@fredrikjag]

Thank you Tim, for the explenation. I understand, and will try to not rush threw the learning curve, to start developing the app i have as a goal to create. Your videos are alot of help to me, and I now know what effort is needed to become a developer. I appricate the honesty!

[@MrCensorMe]

Thanks a lot for the video and explanation man, was easy to understand and extremely helpful.

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@djidopeled8605]

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.

[@ajvenable7937]

Thanks for the video Tim

[@IAmTimCorey]

You are welcome.

[@djmj12714]

Thank you so much for posting this! New subscriber here!!

[@IAmTimCorey]

You are welcome.

[@ИльяМанишин]

Waw, this is the best explaining. It's incredible

[@IAmTimCorey]

Thank you!

[@CoderboyPB]

Hi Tim, what about if I send the program, to a friend, who hasn't installed SQLite? Will it work anyways, I mean, does the compilation process embed a standalone database?

[@IAmTimCorey]

The database is just a file. You need to send that file with the application and make sure the connection string won't have to change (use a relative path). Then it will work great.

[@johnkramer5108]

@@IAmTimCorey - What about the DLLs? Or are they embedded in the exe?

[@dcaetfvn]

Hi Thannks for your video. Do you recommend a way to trace/debug queries on SQLite in C#?

[@IAmTimCorey]

No, sorry. I don't think I've ever had the need.

[@dcaetfvn]

@@IAmTimCorey How can you make sure to have right queries all the time? I have a syntax error but I can't figure it out how

[@jaskaransingh-ez4vf]

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!

[@nsreality2781]

super work Tim Corey

[@IAmTimCorey]

Thanks!

[@NotAHomelessGamer]

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?

[@IAmTimCorey]

You would use appsettings.json instead, but beyond that, there isn't much change in the code. Here is a video on appsettings: kzbin.info/www/bejne/lWPCop6pmbZ-eac

[@lucianiordache3229]

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?

[@IAmTimCorey]

Just loop through it, doing an insert per row. It feels inefficient, but it is actually not.

[@kitsunesuzuka1029]

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!

[@Gray-Wolf-Media]

Hey Tim, got a question, are you planning on doing a MySQL video? I was having a problem with a dotnet core app I was working on and at first I thought that my data source to MySQL was wrong but after trashing the program and reverting to SQLServer I found that I was having the same issue with it. Turned out to be a flaw in my Angular code

[@IAmTimCorey]

MySQL is also on the list of database topics.

[@Scarvarg]

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?

[@karendoran3692]

Tim can you take this course and use a SQL server database? I don't have admin rights to install SQLite on my work computer but have access to create sql server databases.

[@IAmTimCorey]

To answer direct, yes you can. However, you can also use SQLite. The browser can be "installed" as a portable app. That means it doesn't need admin permissions to run. The database itself doesn't install (it is just a file) so you should be all set to operate it as designed without admin permissions.

[@GameOver-ge2hr]

Hello, I do not understand why you have to create 2 projects (DemoLibrary and WinFormUI) because, it makes you add twice the nuGet packages? Thank you :)

[@IAmTimCorey]

I almost always create a class library because your code should not be directly tied to your user interface. Otherwise it is rigid and hard to update.

[@GameOver-ge2hr]

Hello, thank you for your explanation. This is similar to the MVC, I understand better;) did a series of video to explain precisely how organized his project? I'm waiting for that, the creation of file to "tidy up" these classes, etc., thank you!

Thank you very much Tim!

[@IAmTimCorey]

You are welcome.

[@laurentiubancila7675]

Hi Tim! What should I do If I don't have App.config file?

[@IAmTimCorey]

Change your project type. You built a .NET Core application instead of a .NET Framework project. Usually that isn't a big deal, but in this case, it is messy to try to follow along.

[@robertvocals]

@@IAmTimCorey Did the same mistake. Thanks for clarification! :D

[@שקדסער-ו1ג]

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?

[@jamesscott6864]

Hi Tim, please do you have a Blazor Server video with SQLite and dapper ?

[@IAmTimCorey]

No, I don't. Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/

[@jamesscott6864]

@@IAmTimCorey sure Tim. I'll do that

[@TheDaveStream]

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

[@IAmTimCorey]

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.

[@mrspacer1]

Please, make video about dependency injection

[@IAmTimCorey]

It is coming.

[@mrspacer1]

cooool! you are the best

[@balthaaa]

Great video tutorial! Thanks for that!

[@IAmTimCorey]

You are welcome.

[@rbfreitas]

Thanks a lot for this video. Quite explanatory!!!

[@IAmTimCorey]

Glad it was helpful!

[@MdAbdullahAlFahim]

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, .......).

[@ChristianHaugland74]

Class library (.net framework)

[@josbexerr5166]

Gracias excelente Mister Tim.....

[@IAmTimCorey]

Thank you!

[@patrickdavis102]

@IAmTimCorey: Fantastic tutorial! I've worked with SQLite on a mobile app via xamarin forms, but the process was VERY different. Thanks!

[@IAmTimCorey]

Thanks!

[@gregorchard1629]

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?

[@gregorchard1629]

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.

[@jurgenbartalis1933]

High quality like all your trainings! One question: which reporting tool can you recommend for c# and sqlite?

[@IAmTimCorey]

Get that one a lot. Exploring options so stay tuned.

[@alexn4217]

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.

[@IAmTimCorey]

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.

[@MrMrkBo]

Hi Tim. Great video. I am currently teaching myself C#. The DB browser you highlight in your video looks almost identical to the visual studio environment for SQL server. Like DB browser, Visual Studio also has two views, one for design that shows the table, and one for the code for the table. I'm wondering what the advantage is to using a separate browser since it looks like it does the same thing as the Visual Studio IDE. Thanks!

[@IAmTimCorey]

I have avoided using the SQL Server tools built into Visual Studio because they lag behind SSMS (for SQL). Because of that, I often don't use them for other databases either. In this case, I don't think you can manage a SQLite database using the standard Visual Studio tools. I think you need to use a plug-in (like this: marketplace.visualstudio.com/items?itemName=ErikEJ.SQLServerCompactSQLiteToolbox). That seems like a good option. One of the reasons (beyond more functionality) that I use external database tools, though, is that you can make the modifications without Visual Studio. That means I don't have to launch my solution to modify my database and it also means I don't have to use the same window for both (Visual Studio's multi-window story is not great. It is possible, but ugly).

[@MrMrkBo]

IAmTimCorey Hi Tim! After posting my question I learned that SQLite isn't a Microsoft creation (I'm a newbie, I'm learning fast however). The book I'm using to learn the .net framework uses SQL Express for teaching databases, so that's all I know right now really. Because SQLite is from a different vendor, I probably couldn't use the Visual Studio designer anyway, although the designer in your video looks just like it. Thanks for posting your videos. You have helped me a great deal. If I can get some extra money together I'll head over to your website and sign up. Glad to see your feeling better! Mark

[@huskypsycho7833]

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

[@IAmTimCorey]

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).

[@notdaveschannel9843]

So am I right thinking you have to create the model classes on your app manually and make sure they map to the tables in the db? Also, how do we handle the difference in datatypes between C# and Sqlite? Apologies if this was covered in another video.

[@IAmTimCorey]

You've got it right. We are using a tool called Dapper that handles the mapping from SQLite to C# (and the reverse) for us.

[@notdaveschannel9843]

@@IAmTimCorey Thanks Tim. I guess SQLite is aimed at reasonably simple database requirements anyway so keeping the models in line with the db shouldn't be much of a challenge.

[@AhmadHalabi001]

Now this app can work on any machine without any additional installation? (SQL server or other tools)

[@IAmTimCorey]

Correct.