Connect C# Application to SQLite Database and interact with it

hey guys a new video in this one I'm going to go through how to use sequel lights along with c-sharp so before I start getting to writing any code or hospital anything like that I want to go over what would you use the collected teashop so let's assume that you are working on a project for a client and application is only going to run and his own computer so if you use then my sequel or sequel server you're going to have to have for example on my sticker so everything in order for it to work or or install it on his computer now what's now what makes sequel is pretty interesting and awesome in my opinion is the fact that it's serverless which means does not require a server or anything like that for it to work it's just a file that you want to read and write data form into and and it will be included with the project file so all you have to do is include in your own style and you're all ready and done to deliver you're close to the client I'll let you get started the first thing I'm going to do is you have a head to a visual studio and I'm going to create a new project in my videos tea shop then a two windows console application and I'm going to cover the sequel Lite demo now after creating the project what I have to do is unstop sequel Lite now you can do this in two different ways the first is you can head today to the sequel Lite website and download the DLL file inhabited the references here and the second way which are there which is the one that I'm using is using a mega package so gate tools library package manager and manage medic package packages for solution I can go ahead and search for sequel Lite this is the one I'm going to be installing so system does equal the data obstacle like so I'm going to as both big innate okay nice and starting exist and as you can see it is installed and we have our systems of that obstacle light here and we can start to use it so the first thing I'm going to do is I'm going to create a new file I'll which I'm going to call I'm going to create a new class I'm going to call it database of yes it is yes and this is the file that's going to hold on my database related stuff so the first name is great song that I use is I'm going to need you system the database if you like so now I can go ahead and start so the first thing I'm gonna need is a sequel a connection so public connection and I'm gonna call this Malcolm action and action and now the second thing I'm going to do with you I'm going to create a constructor and this construction oh man I'm gonna initialize Malcolm X Pro makes you click new action and if you take a look at this this sequel connection the constructor of the sequel Lite connection you can say that this takes a string which is the connection string so so in this connection I can pass a couple of a couple of options so the main option and the required option with the data source which is the name - the name of the database to use so I'm going to our database base of database 3 and you can pass them a lot of options in here for that you can have like things like a password you can have like amongst amongst cooling connections and allows us so now after creating and often specifying the datasource I can go ahead and start using my database so the first thing I need is the database itself now is this and there is dis method in the sequel Lite connection which called create file which creates the database for now this one takes the power at the database client name so it's just like just like in the in the connection string I'm going to get it database so we now the problem with this is this is going to execute every time you call this database constructor which means that this is going to be overwritten every time so what I'm going to do is I'm going to actually check now in most cases you're going to have like a database class in there is this you're just gonna you just gonna have to read and write data to it you're not going to have to create everything but if you want to do so you can have like a condition checks with it so first any of this do them using just a namespace and I'll check actually I need output and I can check if I exist file this and further looking for a database of like three now what I want is execute this when the file does not exist so when the file does not exist I want you to create now to this I'm going to go ahead and create and create an instance of the original database my daily object new database and console given system namespace and G key and X I'm going to go ahead and come to the right length of the table today screen to the console just to make sure that it's working properly so this is going to say database twice wait so now if I go ahead and run this you can see that it says that the database file is created now if I go ahead and run this again it's not going to create it together follow the case of fun or really exists so it's not going to be overwritten okay now that we get that out of the way now let's go ahead and start reading writing some data to the database and getting data out of it today now what I'm going to use to edit the schema of my database is this DB browser for sequel Lite so I'm just going to open the database now you can have you can you can different write your queries and this in your program like this using something you know just just regular queries but I'm not going to do that because in most of the time you have if you want to have like something to manage your database outset of your application because as I said before this the database is just can be applied sitting there you just going to have to read and write data from if you're like kinda has to make changes to it to it every time so now I'm going to go ahead and open the database file and hit the documents where's my closer to its path with my project and this demo not in the pinned I'm going to bin-debug and here is my data based also database toxic lock screen I'm going to open that and as you can see I have no tables no views no tread and nothing else so what I'm going to do is I'm going to create a very simple table so I called Alice and I want to have my D it's going to be a primary key or increment and I'm going to have value she's going to be a text so it's gonna have service tables and my own stables can have the name of the table and the artist of the table I'm sorry the artist who made the album so one last tool I need is and it's going to be a text as well so I'm gonna okay now that I have my table my god who pays I'm going to hit back so now I'm going to write a change that first I'm going to write the change to my database so i'm going to hit like change and i'm going to close that i'm going to close the database and the DB browser fascicular because the database cannot be used by two applications of this two applications at the same time okay now let's go ahead and insert something into the database so I'm just gonna do x-men 1 plug in the main function so I'm going to call this and serve now the first thing I need is I'm going to write the query so you have Frank query now this is just simple sequels lots of insert into albums values first in aid and then here is specify the coins of the title I'm going to emphasize and often values tell you sorry and I'm going to explain what this means in a second now the next thing I need if it's equal life to man and you and this takes two parameters it takes the query that we are going to execute and the sequel like connection sequel a connection we are going to get it from this database object so the query is difference on that go ahead and doesn't query and the connection is database object of connection my connection was like that without the collection and so we have this in terms of a physical item and and and what this style means it means so this one is represent sequel injection in sequel injection so this means that this parameter is going to be injected with this my command of Angus of added value so in here I'm going to specify the parameter that I need for title and the value of it which is going to the user to the stable example I'm gonna go ahead and move now going so so love you and this one is by artist this one is very Russian we just like that we pass the values of these 2 parameters so artists entirely and what I need to do is execute this so I can either execute mouth will execute Mach wave is not a query because this is not going to return like select something from the database now this is not going to work as first I need to open the connection to the data to the database so they are soon so to open the connection to deliver it to the database open Google's database object database object though my connection open now this is fine but there's this now I prefer to check whether the connection is already open or not so what I usually do is I am going to head to what I usually do it is in my database file so where my connection it means I like to create two functions of two functions very first one here so the first one is often mentioned and in this I check whether the connection is open or not so what I have to do is go my connection my next-gen of spades I'm going to check whether the state is open now actually I need to check it's not absurd if it's not open what I need is actually open the connection so connection but open and if it's already closed if it's already offering there's none I mean I there's nothing to do there's nothing required to do so this it open connects you the second function in the close connection so close connection basic thing so check if my connection of state connection state is different this is officially closed just like that now to go up and hit light my main function and it a bit more connection and I need to go to connection after query is done so just like this now this execute mark where command is going to return an integer so this value is the number of rows effects in the database so now what I'm going to do is I'm actually going to slow this I'm gonna call this result and what I'm going to do is after the database after the close connection I'm gonna go ahead and come to write the value of results check how many rows of rows and it 0 and bring this reversal screen as I go ahead and run this today hit start this because this resolved very well-attended one so which means one roll was inserted into the database now I can go ahead and check myself so I'm gonna head to my DB browser for sequel Lite I'm going to open the database and in my albums I'm going to browse the table and as you can see we have the structure by drop so by pressing 2 okay so that's what goes so let's go ahead and have another arm so then I can go tomorrow so go ahead and execute and thanks and one row as you can see I have my my albums and my records and Alan's dead so now that's how we insert stuff into database now I'm going to show I'm going to show you how to get it from to get stuff in the database now it's pretty much the same things that mean anything to do except for the query Excel so let's just get rid of this query so I'm going to change this from insert to actually leave that there so I'm going to do is I'm going to copy this here so let some database so I'm going to comment this and I'm going to change this on inserting to select all from Alvin's voice and this one is fine I don't need things also now instead of executing our query I'm going to need to change this to execute query executes actually execute reader so execute either have this execute reader returns sequel Lite data to the pen so this results we're going to have to hold the results from this execute pleaded today is I think you read it now I can go ahead and display the data by going wild so first I'm going to check if so Rose is a browser I'm going to do and a wild sort of read the wild sweet of reading data I'm going to go ahead and super what I'm just for display so [Music] yes it is I wanted its best here is long and which we go to the table take a look there so when you get is close focus to the column and the second is we want the font now this is what I need by going out so I don't exist anymore so if I go ahead now and run this you can see that I got my results of the database so so thank you for watching and happy coding the kind of um kind of kind of so that's it a very simple video kind of an introduction into sequel light swim with t sharp so if you have any questions about this a measurement comes below

Channel: OverSeas Media

Views: 74,387

Keywords: tutorial, c#, java, sqlite, mysql, postgre, sql server, how to, how, internet, learn, windows, install, connect, query, select, sql injection

Id: anTP-mgktiI

Length: 22min 5sec (1325 seconds)

Published: Sun May 14 2017