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

have you ever wanted a stored data for an application but didn't want to deal the hassle of a full database server or maybe you whined to have one database per installation those and many other scenarios are a perfect fit for sequel Lite today I'm going to show you how you get started using sequel Lite in c-sharp we're gonna build a small database we're gonna attach it to a c-sharp project and then wire up a project to read from and write to the database now if you're new to this channel my name is Tim quarry and it's my goal to make learning c-sharp easier this channel is full of videos explain of various parts of c-sharp I also have a website where I provide full courses on c-sharp and sequel in fact I has launched a new course on how to use WPF and sequel light in a real-world application it's called sequel Lite app from start to finish if you'd like to take what you learn in this video to the next level that course is a great next step the length of the course is in the description below also in the description is a link to the blog post for this video included in that post are links to a starter code as well as a finish code from this video okay so let's look at the demo application I've set up here I have a win form user interface really simple this is just for demo purposes it's not to be really in-depth okay so I have on here a first name and last name for the add person we can add the person to a list we can refresh the list in theory that goes to the database but it doesn't yet and then we have a list of people over here really simple stuff okay now right now it's all demo coded and we're gonna change that over to use a database instead now my purse and model is really simple has an ID first name last name and a full name which is an auto property which is just first name and last name put together okay real simple stuff I was like the code behind for the people form and start the top so we have our list of person model now I did not create an observe observe all collection here I asked create a list which means that this list right here won't update automatically and that's okay for this purposes I'm just trying to get a simple list up there and that works with a little better than an observable collection now in the start-to-finish course we want to do it the right way and so you use an observable collection and actually how to convert from a list over to the collection and back again okay so when the the constructor is run we load the people list which right now just generates three new people which has it to do saying hey get real data here and they wire the list and we're at the list sets the dis or the list box to null and then two people and then sets the display member to full name okay now if we hit the refresh button which is this right here then that refresh button calls this load people list again which again wires things up afterwards the add person button which that one is right here what that does is it creates a new person but has us to do here it says do something with this these right now we added to the list and then why our list that's not really what you want to do want to save it to the database and then you wipe out the form okay so if we run this application let's do that now an actual estranges start up because right now it's starting with the other screen so let's go over here to my form start position I believe it is center screen let's give that a shot there we go so now I have three build my lists and if I add a fourth marry Mansi all right and there we go so we've added a fourth person to the list we can refresh the list and of course it it actually adds those people over again which is not ideal but it's only demo code we're gonna ahead and refresh this with access in the database so let's start off with the basics so what is sequel Lite well sequel Lite is a database that self contained in a file so it's a really small little database and yet it's a full acid database with your motive acid it's a relational database so that you can do transactions we can have data security and protection when you're inserting multiple sets and all that fun stuff essentially think of it like sequel only light okay so that's why I call it sequel light so what we're gonna do is where I create a sequel light database to start with now the first thing I'm going to do is I'm gonna show you how to get one of the tools you can use to very easily build the sequel light database so that's at sequel light browser org if you go here you'll see that you can download an executable for just about any platform you want I download the 64 bit for Windows Exe and this is what its gonna look like okay it's a very easy little tool that you can download and run now I've already downloaded installed it and I had on my machine but first I'm going to on my demo oops I win for me why I'm gonna say open folder and five spore and I will grab this half okay and then I'm gonna bring in my DD browser for sequel Lite so this is the tool to create a database and it's very very easy to use and we'll go through how to do it so I'll say new database yeah paste in that path so this is my location that's right here the wind form you I okay so I call this my demo DB okay so this is my daily DB I hit okay done I've got my demo database started but now I need to add at least one table and that's all I'm going to do actually just one table so I call this table the person table so as person and I'll start adding fields so notice down here it's creating a sequel statement for me but I don't need to type this in myself or remember all the syntax and I really wouldn't probably just because I don't do it often enough and so try member all the syntax it's kind of hard but having this nice GUI builder really saves you a lot of work so the add field I'm gonna say ID is the first one it's an integer I'm gonna check all boxes now if you hover over them to tell you what these things are so the first one is not null the second one is a primary key the third one is auto increment and the fourth one is unique now we shouldn't have to have not null or unique checked because the fact the primary key auto increment should take care of that but it doesn't hurt anything and so that way I know for sure that all of these are true that we're not gonna have a knowable value in the ID that so this is not null me can't null primary key means this is the identifier for that row the auto increment means we're going to start number one and it's count up with every record okay and then the unique means that you have to have unique value per row meaning you can't have two ID fields have a value one one of them has to be something different okay so there's the ID record and now we're at first-name and here's our options now these are a little bit limited and we kind of use some with categories it more like categories let me our types there's a lot of things in sigh here we do so integer your price familiar with that's those are whole numbers text is anything text blob would be for storing files or images or stuff like that real we use more for decimal point or double numbers and then numeric is I believe another version of real there's other things we sort use it for but typically I don't used America I use real or integer those are my truth I use okay so let's get me use text so first name let's make it not null I don't like the idea of how you a null first name but the rest of will leave alone now I can set default if you want we're not going to for this but last the next field is last name and that's of type text and also not null okay so if we click over here we could add you know a couple double quotes to make it an empty string by default we could add you know test inside double quote or sale quotes for making tests the default value but really I just want to see you know nothing here because I want the person inserting data to actually give me a real first and last name it's not too much to ask I don't think to give me a first and last name now that's our entire table we go and add a lot more cool stuff but the reality is that's all we need so hit ok so now there's the person table if you go a database structure know if you actually have two tables sequel light sequence that's create by sequel light but in the person table also notice on the right hand side here we have the sequel log and that shows us the statements that you've run recently so for example here's the create table statement and then here's some other statements as well so that's kinda nice we can choose that we can clear it out I'm gonna close it out all right but those are some options you can add to view other parts your database or other things you can do with the database but the focus we're going to spend on today is on the database structure the Browse data and if we really want to execute a sequel okay so let's start off with the database structure if we said oops we made a mistake on the person knows we're gonna expand this here and see the three fields if I select person I can say modify table and get right back to where I was if I want to add a new table just click create table if I want to look at the data in this table I actually go to browse data this tab and then select the person record and if is anything here it would show up but if I want to add a record let's just add one so we have something gonna say the the first name is Tim and a last name is Cory okay so now I have one record in this table and the record says the ID is one first name is Tim last name is Cory now I didn't put the one there that was put there for me by that auto incrementing ID now I can also delete if I want I can also filter so if I have a whole list here I can filter down to a certain records so this is a really nice way to quickly browse our data and look at anything in here that might be of value now I'm gonna start off by deleting this record because I want this database to be clean when I give it to the project okay dressed off with nothing in here and it has just one table that's really ours one table we use and that's it okay but let's leave this alone I'm actually gonna close this database after I write the changes okay so I write the changes and close the database there we go we're done there so now what I can do is remember this is right underneath the wind form UI it's in the same root directory so if I highlight up here show all files notice the demo DB DB shows up but it's got this if you can see it it's a little bit of a dotted outline if I right click and say include in project that's now part of my project I can now click this button again - it says show all files button that's gonna hide those shown files so now I have my database in my UI project and you want your UI project now I can go to properties when I have is selected and notice the build action we're gonna change that to content and the copy to output directory is going to be copy if newer so I have three options don't copy copy always and copy if newer I think don't copy it's pretty self-explanatory it's gonna do nothing copy always means every time you build it's going to take this file and put it into our bin directory the problem with that is anytime you do any work on your database and then restart the application it wipes that work out not really want instead where I say copy if newer which means that we'll copy the first time but then after that we'll leave it our bin directory until either gets deleted or until we change this file in which case is going to overwrite with a blank database just to demonstrate that we haven't built yet so if I right-click and say open folder and file explorer and go to bin-debug notice there's no demo DB in here but if I move us up off a screen for a minute and I say build solution on debug and bring it back down here notice demo DB DB so move that into this bin debug folder okay so now it comes with our project okay so now we have that setup we need to start thinking about how do we talk to it and the first step in talking to it is setting up our connection string okay we don't have any connection strings here yet so right add one now I'm going to show you the connection string is but if you have any questions you always got a connection strings dot-org I believe no pets.com sorry just look that up so it's connection strings calm and there you can find connection strings force equal light so in configuration I'm going to create a new tag and say connection strings and then inside there I'm going to say add oops name equals we can choose any you want I'm going to choose default this is my default connection string the connection string itself is going to be data source equals now I'm going to do here is I put a relative path we have two options we can do an absolute path or a relative path an absolute path means C colon slash temp slash my database dB that's an absolute path they gives you a a location that does not change depending on where the project is located that's good for certain circumstances but our circumstances I want this database to go with the executable so wherever the executable is the database lives in that same location therefore I give a relative path so I hit dot slash and then a on my database demo DB DB so that's good look in the same directory as my executable for my database so this dot means the current directory that's what that means so slash and here's the name of the file now at the end of this we need to specify version equals three and that uses a sequel Lite database version three and lastly me at our provider which we as used system dot data dot sequel client and that's a more generic sequel client not specific a sequel server okay so that's our connection string that's all you need okay now we can use this to connect to our database save that now we need to do is we need to add our ability to connect to or to look at sequel Lite so it's not baked in to C sharp TLC sequel Lite and the reason why is because you know C sharp is from Microsoft sequel Lite is not sequel like the third party app which is great in a lot of ways because that allows us to have a disconnected process where the two can talk but they're not controlled by the same entity so we need to do is on our library where our right click go references we're going to manage NuGet packages and we're going to look for two packages the first one is going to be sequel Lite and it's actually the core version system data sequel Lite core now we're using dot corbs we're not gonna need all the extra stuff okay so if we get sequel Lite what that brings with it is it brings the sequel Lite core and entity framework six and entity framework itself we don't need those things so we can you say sequel Lite core we installed out at the latest version of that there and now the next thing we need is dapper now if you've watched my other videos you'll know that I am very much in favor of dapper dapper allows us to talk to a database almost as fast as direct a do net but yet it gives us a whole lot more usability it makes it a whole lot easier to work with we're not working with days set some dead tables we're not making sure that our parameters are working the way should but he has all kind of done for us so and the greatest thing is it Maps everything out to our models so by far this is the best way in my opinion to connect to a database very fast very easy to use so hit install now the one thing I've come across that is interesting is that when we try to run this and I have just the new packages in my library that sequel light doesn't know how to figure out which Interop library to use to connect to c-sharp in my form and so I found you have to do is on the form add a reference management packages and look for sequel light and look for the core version and install that there as well so not a huge fan you have to do that but at the same time it's not a big deal we just add that one reference nuga package in our UI and now it knows how to talk to c-sharp okay don't forget that even though we're building the DLL here it's getting used in the executable over here and so that's where it needs to run and if not running properly right now if you know a better way of doing this by all means please let me know but before I found my research this is the easiest way to solve this problem so now they have the NuGet packages ad we can start setting up our our connections to our database so let's right-click on demo library we're going to add a class and let's call this class sequel light data access and we get public and what we'll do is we'll add two methods here we're going to add a method I'll stub them out now so public list of person model let's make it static first public static list of person model and we'll say load people and we're not gonna ask for any parameters we're gonna say give us all people okay and the second method where I have public static void save person and we'll take in a person model person okay so those are our two methods where I have now we could optimize this to be a lot easier to call but I think it's too much work for just this demo now if you want to see how to set this up with generics and making it really optimized so that you only have to have one load and one save and you don't have to have one per table you can watch the the sequel Lite start-to-finish course okay but but for our purposes here I think we can get by adjust these two methods now we also need one more method nothing to load the connection string nothing at private so let's say private static string load connection strength and we're not going to pass in what the connection string name is we're gonna assume that that's default okay so we could pass you know what let's pass it in let's do it right string ID equals default there we go so now we we can pass it in but we're going to assume that we want is default normally okay and we're going to do is we know connect on configuration manager sorry so right click on references add a reference and we have search for configuration manager right here and this is part of of c-sharp as in our assemblies this is part of c-sharp but it's not add to our projects by default and what that allows us to do is talk to the app config now notice I Magnus to my demo library but my demo library does not have an app dot config and that's okay because the DLL will actually use the app config of the executable so it will actually talk to the app dot config of the window form UI so here I can say return configuration manager control dot to add the using statement for that dot connection strings we need to pass in our ID which is the name of connection string dot connection string so what that does is it goes out to our app config and says here's the connection strings look through all them for one that has a name of default and when it does return the connection string portion of that which is this right here that's the important piece of information ok so now we can start by creating our load people method so we start with using i DB connection control dot here to add a user fitment for system data CNN equals new sequel Lite connection now one thing I haven't pointed out yet is sequel light doesn't have to else and that's something you might have missed so it's not sequel and then light its sequel light one word one L so you're going to type something just make sure you have with just one L there so happen I'll hit my control dot to add a user thement for system dot dat sequel light and then say I need to get a connection string which is load connection string okay so that's only I do for my users the event to get connected to sequel light say DB I DB connection equals new sequel light connection and there's the connection string to it okay which will actually use down below as well so make a copy and paste this and what this using statement does is it opens the connection it in guarantees that no matter what by the time we hit this closing curly brace or by the time we crash either way that it will close properly the connection to the database so that's what a fail safe right here this you didn't see that right here it protects us from our own bad code leaving connections open to the database because that can cause problems so usually say that really protects us from that okay so now we need to do is we need to query the database so we can say let's do this we'll save our for now VAR people or output actually equals CN n dot play control dot to add a user to that for dapper query and it's going to a person model generic and we need our sequel command and we need our we need a dynamic parameter list so we can say new dynamic parameters like so Nick is a blank list of parameters we're not having it's going to be select star from person ok that's our sequel statement right there that's it ok so that thing that's going to give us a list or a set of person model actually an ienumerable which I'm not a big fan of I like lists instead so we're gonna do now is say return output to list that's it so that's my connection to the database my request for this query my list of parameters if I having and I don't and the return type object which is person model list a set of person model that's it that's my load people entry and returns a list of person model save person model pretty similar so let's create a dynamic parameters we'll call it P equals new actually know what really need to do that last let's do this CNN execute we'll pass in our sequel statement which we'll get to that a minute and then we'll pass in person because as long as we have matched up property to the parameters where Pat we have here we're good to go not sure what I mean by that insert into person first name last name values at first name at last name so these right here will be what we're going to populate with the values from this person object that's called at first name and at last name well the person model has first name and last name and those will match up so we should be able to execute this without a problem if we use dynamic parameters we can do additional things I don't think we need to go that far down the rabbit hole again we have a model let's just use it in theory we have now written all the code we need to read to a read from and write to the database that's it so let's see if we can't wire this up to our form and see if it works so our form will right-click and say view code we've got a couple of - dues here one is the load people list so instead of people that add we're going to say people equals sequel light data access dot load people alright that's our list of people which right now is being blank but we'll add some people to it and then we're gonna add people to it is right here instead of this to do right here we're going to do instead is say sequel light data access dot c f-- person and we'll pass in P now the debate right here is do i also put p into this people list I think the answer is no instead we're going to take advantage of that refresh button to refresh the list when every aside we've added enough people so let's Wyatt let's see if this wire up actually worked I'm gonna have a bugger - and that's okay so there's no big old people list right now let's add Tim Cory add person refresh the list check it out Tim Cory is there now if we say Bob Smith add person will say Jill Jones add person refresh the list they're both there we close this list out start up again just to show you they're still there okay so we have now written to our sequel light database and we've read from it now that's all getting written to the debug database because we're in debug mode but what if I change is from debug to release watch what happens we can continue debugging notice the list is empty and the reason why is because remember that this demo DB gets redeployed every time it's not there or every time it gets changed so this is a blank database that's over writing the the use database if it gets changed or if it's not there it fills it in again so that's kind of real it's a really easy way to allow us to both do debugging so if we go back to debug that Dale will still be there and yet it allows us them or ready to blow it away and start over with a fresh database we can then deploy so you for we're done let's go ahead and go win a form you I open folder and file explorer go to our bin-debug then i copy this this path right here now is this xa6 it's got a sequel light Interop dll and the same thing with x64 that's the Interop thing I was talking about before you have to have the sequel Lite nougat package in your UI so that's what that creates you don't have the sequel Lite in your UI it causes a problem doesn't create those two files so now we have a path here let's bring back our browser and say open database we're going to browse write the debug and open the debug version of demo DB now if you go to browse data there we go Tim quarry Bob Smith and Jill Jones so there's our data in fact if we add a new record and let's say Mary mayhem and now we can write the changes and we can close the database we can close this and if we open or start this backup there's Mary mayhem okay so that one file is our complete database we can write all the normal sequel commands to it select insert update delete we can have multiple tables we can create views we can have indexes there's a lot we can do with a sequel Lite database and yet it goes with us if we want doesn't have to but it can and this way you can create little configuration databases for your application or you can create a full database for an application where the user wants to have their own copy let's say you want to create a to-do list well you can have each user that uses a to-do list have their own personal database and you're not installing sequel you're not saying okay where is it and check my connection strand changing it for every installation it's just there with that little file okay in our start to finish course for this we build an application called build time we're a consultant contract the hours and bill a client for the work they've done and also accept payments and all the rest all that gets stored locally in a sequel light database so it goes with that particular consultant so there's a lot of uses for a sequel light database it's not again the full power of sequel but it does have a lot of power to it so I'd encourage you try it play around it see how you you might use it I think you'll find a lot of uses for it in the configuration or small database realm so that's it for the video don't forget the source code in this final version including the full database will be on the blog post and if you you want to see this more in a real-world application then don't fear the check out that start-to-finish course on sequel Lite and WPF alright thanks so much as always I am Tim quarry [Music] you [Music]

Channel: IAmTimCorey

Views: 183,621

Rating: 4.8855019 out of 5

Keywords: .net, C#, Visual Studio, code, programming, tutorial, course, training, how to, tim corey, C# course, C# training, C# tutorial, C# app start to finish, sqlite (software), c# sqlite, c# sqlite database, tim corey sql, sqlite, sqlite3, sqlite tutorial, sqlite tutorial for beginners, sqlite database, sqlite from start to finish, sqlite course, sqlite demo

Id: ayp3tHEkRc0

Length: 38min 40sec (2320 seconds)

Published: Tue Jul 17 2018