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
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 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.
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.
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.
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?
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 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.
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.
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.
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).
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.
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.
hello tim ı hope youre fine. ı m happy if you answer. ı got a projet done via sql server maybe 3 years old.. ı tried to use a slqlite convertor. so databse now sqlite.. after that ı used entıtyframework nuget pakcages for sqlite dlls. ı can open project seing database but cant do save operatiıons. errror says ıdentıty problem . IS the problem ı didnot use .net core at the beginnig for may old project??
Be sure to check out the whole channel - ru-vid.com/show-UC-ptWR16ITQyYOglXyQmpzw. Use the search icon under the Subscribe button to search within the channel to find the training you need next.
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.
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?
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.
You would run a SQL statement like "delete from Person where Id = 1". Execute that as a query that doesn't return data and you will delete all records from the Person table that have an Id of 1 (probably just one record but you could change that where clause to be "firstName = 'Tim'" and it would delete all Tims from the database). To see an example of calling a query that does not return data, look at 31:54. The SavePerson method is making a similar call. Just change the SQL statement to the one I gave you above and change out the value for a parameter and you should be all set.
I am watching this in 2023 - is there a workaround to having to include that System.Data.Sqlite.Core dependency in your consuming project? I am on a dependency management kick and would like to break that one if possible. Regardless - thank you Tim again for saving me so much time.
Hi Tim, iI read the data from the database and use that data to calculate the main data. Then, I update the calculated data into the database again. The problem is that the 'database is locked'. I can't think of a solution. If you have good ideas, please tell me.
It sounds like you aren't closing out a transaction to SQLite before you are attempting a different transaction. Make sure you are closing your transactions right away (I'm using "using" statements to ensure they get closed right after the call).
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
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.
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.
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.
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.
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!
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).
Loved the tutorial, but I'm racking my brains trying to figure out how to delete an object using sql syntax from the listbox we used in the tutorial. I cant seem to be able to turn the selection, which argues I can't turn an object into a string. I'm trying to store the selection displayed so I can send an argument back to remove it from my sqlite server. p.FirstName = (string)listPeopleListBox.SelectedItem; , and various other ways without casting not working. Tired and frustrated. =)
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?
There are options, but the easiest one in my opinion is to use a text field. You could use an integer and calculate seconds from 1970 among other options, but those options aren't human-readable when scanning database columns. I prefer just using text and converting it back to DateTime.
@@IAmTimCorey So If We Have A DateTimePicker named as DTP this means we can define string theDate = DTP.Value.ToString("yyyy-MM-dd") OR Something Like This and Then We Should Store THis String onto the DB
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.....
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 :)
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!
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.
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).
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!!!!
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.
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.
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
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?
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.
So i want to use SQLite as a database for a discord bot. Discord uses ulongs. I know i can convert a string to an integer. But is there a convert that works nicely for a ulong? I don't use converters too often and when i do its int16 or int32.
ULongs are messy in SQLite. Here is a post that gives you an option of what to do: stackoverflow.com/questions/15201776/retrieving-ulong-from-sqlite-relsults-in-notsupportedexception
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.
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?
Not sure what you mean by "parse columns from the database". I do have a video on DataGrid that might be what you are looking for: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-zvyQNuuTqks.html
@@IAmTimCorey Well, when I am reading it now after lof of tutorials (I was, actually still i am, noob in terms of sql), I am not sure too, But I have used DataGrid and it works well. But there is other problem with adding possibility to edit and delete of records from database by selecting them on DataGrid.
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?
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 ?
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?
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
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
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!
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?
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?
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?
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.
Hi. I have recently created a windows forms application with sqlite. I added system.data.sqlite to my project and created my database in the project->bin-> debug folder. I also created an installer for this application. When I run the application in visual studio, it works fine.But as I install the application, I can see my database file but it is empty(0 tables).It does not have the table I created earlier. I want that table to be there in the database file.Could you suggest me what I should do?
That was answered in this video I believe. The database should not be in the bin folder or any folder under the bin folder. You need to add it to the project outside that folder and let the compile put the database into that folder. Also, when you want to modify the database, you do it to the one in your project (outside the bin folder) since that is the one that is re-deployed.
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?
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 RU-vid does).
@@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🙈😑
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.
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!
The first thing you should do before ever considering SQLite for an actual application, is to make sure you thoroughly understand SQLite's limitations. Here's a great link towards that end: www.sqlite.org/whentouse.html
Thanks for sharing that link. You are correct. SQLite isn't for every project and identifying if it is a good fit or not is a great thing to do early on in the process.
Ok so i added everything on the code but for some reason My DemoDB.db is locked how do i unlock it. I have no other instance of it open I even when in to the browser SQLite and added a name so I can write changes and then close DB but still is Locked anyone know how to unlock this please thanks
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
Thanks for your videos ,its really help. Iv problem when creat exe file i got 1 succeed and 1 failed,and ive exe file but msi file doesnt exist What could be the problem
I get "An unhandled exception of type 'System.Configuration.ConfigurationErrorsException' occurred in System.Configuration.dll", but I followed all steps of tutorial, why can this happen?
@@IAmTimCorey I use .NET 4.8, there's no more issue with configuration, but now when I add elements to database in my application they don't appear in the actual database (when I open it with DB Browser) :(
@@Allyourneedsmet Go to your cmd, type in "dotnet --version". It should show you which version is currently being used (Incase you dont know how to go to your cmd, press windowskey + r and then type in "cmd". You can also find your cmd by looking it up in de windows search bar)
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.
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?
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.
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.
Thanks a lot for sharing such a high-quality tutorial. I had this error when running the program. ArgumentException: Connection string keyword 'version' is not supported. Anybody encountered this problem or have an idea of how to solve this problem?
I don't think it is case-sensitive, but you can try "Version". Also, you might want to check the rest of the connection string to be sure you aren't missing a semicolon or something else.
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/
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.
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!
