How do I use C# and .NET with SQLite?

[Music] foreign [Applause] and I'm a product manager on the.net data access team today I'm going to show you how you can access a sqlite database and query data from it just by writing c-sharp in the next five-ish minutes I'm going to tell you what sqlite databases are and why you might consider using them I'm also going to talk you through the four different ways you can work with sqlite using c-sharp and help you figure out which one to choose for your project last but definitely not least I'm going to jump right into the code so you can see for yourself how easy it can be to get sqlite set up with c-sharp the thing about SQL Lite is you're probably already using it it's the most popular database engine out there because it's built into all mobile phones most computers and even some fridges it's so self-contained that the sample database I'm using in my code sample is actually included right in the GitHub repository of the four different ways you can use C sharp with a sqlite database you're going to have high level options like Entity framework core on the left which will help you work with data like objects and avoid learning SQL At All by writing c-sharp to generate your queries instead this also holds true for a SQL light Dash net which provides a lot of the same functionality but is a bit better for Mobile cases if you want a bit more performance and something lightweight and might prefer to write your own SQL queries Dapper would be a great option on the far right we have adl.net which is the most low level of our options meaning you're going to handle the database connection and the data querying all on your own however adl.net does apply for all scenarios even the really weird ones and you'll get a really good understanding of exactly what your code does now let's take a look at some code and see what each of these four options look like in action so let's start off with the EF core approach which is going to be the most high level you can see that the project structures pretty straightforward we have the model.cs file where we configure EF core to point to our database and then we create a class named blog this is going to be the form in which our data will get brought into our program let's take a look at program.cs this is where our console application lives we see that our database is brought in as an instance called DB then we can immediately query that database using c-sharp Code down in line 11 to 14. then we read things out that's it let's take a look at what this looks like in action I'm going to go over to debug start without debugging cool got my blog now let's say you are working on a mobile application or you care a bit more about performance sqlite Dash net could be a good option the file structure is pretty similar but the model.cs file looks a lot simpler than the one at EF core that's because we're not bringing in the whole database but rather putting them in the form of the blog objects as a result the program.cs file looks pretty similar the only thing is our database is represented as a table of logs which we can then query let's take a look at what that gets us cool got my blog now let's mix things up a little bit let's say you're pretty used to writing SQL and you would rather write that instead of C sharp or maybe you're working with thousands of queries at once and performance is really important for you in that case Dapper could be a good option looking at the model.cs program we can see huh looks pretty similar to sqlite Dash net it's only when we get into program.cs where we start seeing the big differences Dapper handles the connection to the database for you but you can see that there's no c-sharp code that's generating your query you have to write your query yourself and then pass it in manually going to run this application so you can see what it does cool so you can see that it essentially does the same thing the only difference is you're writing SQL instead of C sharp now let's say your scenario is pretty specific or you just want to get a very Hands-On view on what you're writing in that case check out adl.net the file structure is really different from the other ones there's just a oneprogram.cs and that's because we're not bringing in data in the form of objects so there's no need to Define them in another file instead we create our database connection manually we write the SQL and pass it in and the data we get back is not going to be in the form of an object instead it's going to be lines of text that we can then transform but it's not going to be easy to query that data immediately now that we've looked at all four options in action let's take a look at the important pieces efcore is a great option if you want to write C sharp instead of SQL otherwise known as language integrated queries or link and if you want to use data in the form of objects similarly for sqlite Dash net you get the abstraction that comes with link instead of SQL and objects instead of data but it's further optimized for Mobile cases then if you want to go a bit lighter Dapper lets you use data in the form of objects and connect to the database really easily while also being lightweight and letting you write your own SQL last but not least ado.net offers pretty much no abstraction at all and works for all scenarios the cons being you'll have to do everything yourself anyways thank you for watching through this video and you can find the.net data access team also known as a unicorn squad on Twitter Reddit GitHub and others if you're curious about the code here's a link to the GitHub repository so you can dive in yourself [Music] thank you

Channel: dotnet

Views: 20,779

Keywords: .NET

Id: h9c7TZb2QuU

Length: 6min 58sec (418 seconds)

Published: Thu Jun 15 2023