Setup SQLite Database in Visual Studio (14/23)

hello everyone today we're going to set up a database inside of our project to manage our much higher scaled level of information so we're going to build off of that Library app that we that we just finished and that worked with strictly CSV files but to work with more than just basic information this is where a database becomes really important and I'll show a little bit more in detail why that is in a minute here so the first thing we need to do is set up a couple of things in our program we're going to use SQL Lite there's a variety of programs you can use SQL Server I'm just choosing a very lightweight one that's pretty easy to use there's a couple of different ways to install what we have here you can use visual Studio's data sources if you'd like you can go through these menus if you're familiar this is a little bit clunkier I'm going to go over a different way that I got it to work so let's do that so the first thing to use SQL Lite is we need to import the the libraries so the way we import the libraries is through I'm right clicking on my project name here and we go to this n you get and we want the system data SQL Lite package here and mine says update because I've already installed it but you should be able to find yours and it should say install or download one of the two and we want make sure you're using this SQL Lite one not the core but we're using the sqlite one here's the description and then once we do that that gives us access to the libraries inside of sqlite so now we can we can actually use the libraries that are inside of the program I'll show what that is in just a minute next we're going to install the SQL light extension uh like I said earlier you could use the database connections window that I just closed but we're going to use the SQL Lite extension and the way we can use that minus right here but let me show you how to install it we install it with extensions and updates here and mine's at the top right here because I've already installed it but you will be searching here SQL Lite and it's the only result I get when it shows up you could probably type in some more of this header line to to get that now there's a couple of different ways you can open it uh you can open it right here if you're not able to find it under Tools you can also find that SQL Lite inside of this view menu for other windows it's also right here now this gets us a very similar looking window that's tailored specifically for SQL light uh someone built this little Library works pretty nice so now that we have that open we want to set up our database I have this database file set up right here let's see if I can delete that and just do our own here let me show you how to build your own SQL SQL database file here we want to go to where is my button here add sqlite connection and then we would go to create so you can create a file here and here we could just name it whatever we wanted see if it'll let me name it what I named my previous when I was walking through the example that looks good then we do test connection to make sure that we can see that database file and once that's done we should be up and running yeah now this file should appear once you've done that so this is just one way to do setting up the the database file um and from there you should be able to to do a wide variety of things you can build tables through it I'm going to automate some of that so I'm going to show you how we can automate some of that code so that we don't need to manually build the database here's some of the menus the information yeah let's let's automate the table creation here so we're not going to use this database file that's just one way to build it but a better way to build it I think would be going back to where we started when we installed the when we installed this netgu package here our new get package here right which one was it was this one here um looks like it auto installed all these too but yeah this is the one we'll be using so this package here gives us access to system.data.sqlite so you'll see that here in a minute so the first thing I want to do is I want to build this I want to add a a class to just keep my code organized and clean you don't necessarily need to do it this way but this is the way I would recommend building your code going forward so here we have a little database helper class and this is where we're going to put our code for building our database I have already written the code and it's a lot so I'm just going to copy and paste it and go over what it's doing line by line here so here is that net or new get package that we just installed and we're also using the i o to read if there's a file there and this database helper the most important part I would probably say is this connection string this is how the the program here can see the file so data source is the file location where we're going to save the file one important detail is you need to have this files folder created which I've done here you need this files folder created before you add this library.db or in this case we're gonna do library manage system dot DB DB for database version three seems to be the standard for SQL Lite I haven't tried other versions but version three seems to work pretty well and yeah I use the double ellipses to get out of the bin debug folder or bin release folder connection string this is very important if you're using this database you're going to need this connection string in the future in many circumstances here this just sees I'm only going to create this I'm only going to create a new database if the file is not already there which It Isn't So that's all this line of code is doing uh this SQL light connection create file so this creates the database file same as same way we built the library.database file using the the toolbox here and then here we have some code this is what will automate our table creation so you can think of a table as maybe its own sheet on a spreadsheet that's an easy way to understand what a table is if you but it's very structured and we'll look at the structure of it here in just a minute but uh we'll cover what this is doing in a in a later course but this is creating one spreadsheet this is creating another spreadsheet or technically their tables what if I like to think of it in terms of Excel spreadsheets for Simplicity uh or this this is just the uh this is the query the SQL query so you would need to learn a little bit SQL if you wanted to build this way and then here we can run the command text which is just run this query run the SQL query and keep in mind it's using this connection here so yeah we're going to run this file by modifying let me switch back we're going to modify the program okay yeah I have the code right here so we would run this in the program main so the minute I press start this file should Auto populate so let's do that hopefully I don't get any errors okay cool now we should see that file there perfect we see our library and now we want to add a connection to it so let's add a connection here we're going to browse out of the connection the same way test it test succeeded perfect close gets us completion here and now we have our database tables already done we're just going to remove this connection here so we have our database tables and what's really cool is now we can add data to them very easily here and now going forward we're going to read from these tables and what these tables do rather than just using a CSV file to pull in data and save data what these tables do is allow us to link information between the two tables very easily so I'll be able to say this user has this book right this is my books table and this is my users table so we'll be able to link between those two really easily yeah and this is uh this is all that's needed to get your your database set up I can post this code if you're interested but the last thing that you can do also is if you're not super keen on this uh this setup here there's also a a website that does SQL Lite for your uh creates a little browser looks like it works in a variety of uh operating systems but I already downloaded this and DB browser for sqlite I have it open here and let's let's take a look at this this is also a really nice way to um where's this file location copy path sure there we go pasted it into the wrong section here so this gets us access to our tables here this is a nice little interface too you can browse the data pretty easily so if you prefer using guise this helps you uh helps you maneuver the database a little bit better but yeah this is just a quick little step by step to how to get set up with a database inside of Visual Studio using SQL Lite and that's all we're going to cover in this video thanks for watching I'll see you in the next one take care bye

Channel: rory mulcahey

Views: 15,903

Keywords: Database, SQLite, Visual Studio, c#

Id: 7TpljNN0IvA

Length: 13min 36sec (816 seconds)

Published: Fri Apr 07 2023