Connecting to SQL Server with Dapper

Welcome to the C sharp data access series. My name is Tim Corey and today we're going to connect to a SQL Server database. Now sometimes you may look at a SQL Server database and say, man, that was complicated. I'd love to do it. I'd love to get information in and out of it, but it just seems so difficult. In this video, I'm going to show you how to break that down into a really simple and easy to follow process. You don't have to worry about data access. Data access will become the least of your concerns when it comes to your application. But before you get into how to do that, I want to talk through first a couple of options. Now there's a lot of different ways we could connect to a SQL Server. Initially, I had always done directado.net. And what that is, is just a direct connection using just C Sharp. And it's pretty, you know, foundational or core. And that works, and it works well, but it's a little complicated. And I was never really comfortable showing off how to do it because of all the different configuration pieces you need to have. And the other side of that, the other end of the spectrum, is Microsoft's Entity Framework. And Entity Framework is an ORM. And what that means is that it takes care of the magic of connecting to a database. And there's some really powerful things to that. It makes it work really well. But here's the kicker, at least for me, it's only when it works that it works well. And that kind of scares me because it's a black box for most of that data access. And so you could dive really deep and there's some really smart people that have gone off the end of the deep end. into Entity Framework. They've learned a ton of stuff about it and they know how to make it sing. And that's amazing. That's great. But I'm not one of those people. I don't spend hours and hours and hours learning Entity Framework. One of the reasons for that is because I've been concerned by the problems I've seen. I've seen people who have a system in place, it's working great, they put an application into production, and then two weeks down the road, something blows up. and they spend hours trying to debug what's happening inside the black box you see they ask for data and then entity framework takes that request does some magic and talks to the database and gives you back data and that that area does some magic sometimes breaks down and when it does it's scary And so that's just my personal opinion that I don't use Entity Framework. I really avoid whenever possible. It's just a big complexity that I don't feel I have the ability to add to my applications in a way that's going to be positive for the application. Now like I said, I've used directado.net connections for a long time, but again it's a little complex. So we have a little complex on one side, but I control everything. to the very complex on the other side, and I don't really have a whole lot of control, and that's Entity Framework. So I found something that's in the middle that is really, really powerful and easy to use, and that's a tool called Dapper. And we're going to use Dapper for our data access because I really think it adds just enough to make things simple, and yet not enough where that black box is so complex or is easy to break. And actually Dapper is put out by the people who do Stack Overflow. If you're not familiar, Stack Overflow is a massive website for tech help on the internet. And so they actually built Dapper to help them access their database, their SQL database. And so they actually open sourced this and gave it away for free and they support it. So it's heavily supported, heavily tested, and it's production ready in a large production environment. So I feel very comfortable recommending Dapper. Now, normally I don't recommend you add extra things to C Sharp. I try and teach you direct C Sharp, not add-ons. You're not a big fan of having 30 or 50 or 80 different add-ons and plugins and NuGet packages because it adds a dependency every time you do something. But Dapper adds enough value while having small enough overhead I think it makes it into my top two or three things that I will recommend you add. So that being said, let's dive right into our demo. I decided for this demo to start right from scratch. That way you are comfortable with the entire process. Now if you want this source code, if you want bits of it, you'll find all the information on my blog on imtimcorey.com in the article for this video. Let's start with a new project, and we're going to do a Windows Form application. That's pretty simple. And we'll call this Form UI for our form. InfoSolution name, we'll call it SQL Server. I'll just call it SQL data access demo. We'll hit okay. We have our form UI. Let's rename form one to something simple like dashboard. We'll use this as both our yes, I'll rename everything. We'll use this as both our our data input and our data display. That way it's just kind of simple. This is not a production application. This is just to show off what we can do with Dapper. I'll make that a little bigger. I'll actually mess the property just a bit, just because I want to clean it up. And also change the font size so it's a little more readable on this screen. We got a 16 point font. There we go. Something a little bigger. Okay, now I have a database. Let me pull it up for you. All right, here we are. And in this database called sample, I have one table, dbo.people. And then I have two store procedures, one called people underscore get by last name, and one called people underscore insert. So the people table, let's just select top 1,000. That gives us, I have pre-populated this with dummy data from the internet. Okay, there's tools out there that can. generate insert scripts for you just to put some um dummy data that kind of fits you know so i i said give me first names give me last names give me email addresses give me phone numbers from where and how doesn't really matter okay it just creates them for me so that's a pre-populated table i've got and then the store procedures the get by last name you just give it a last name and it finds all people that match that last name and then the people insert simply adds a new person into the people table so that's our database that's where i pull from so the first thing i'm going to do over here in my form is to create a model that i can put data into that model is simply a class so let's add a new class I'm going to call this person. Because remember that each class instance will be one person. So I wouldn't call it people, I'd call it person. I'll make it public. Then what I'll do here is I'm going to add the properties that match the columns of data I'm going to ask for back. So let's go back over to this people table. Let's actually design it and look we have an ID, first name, last name, email address, and phone number. Now I could change those but I'm going to match those up directly because this is a a model I'm going to use to capture information from a database or to send information back to the database. So I'm going to match it up exactly how it is in the database. I'm going to say prop int id Prop string first name, prop string last name, prop string email address, prop string phone number. There's a model and this matches exactly what's in my table. I have these five properties. Four of them are string and one of them is an int. And over here in my table, I have an int for ID and the rest are some type of string. I have an nvarchar 50, nvarchar 50, I have an nvarchar 100, and then a varchar 20. okay so those are the four different types of string fields i have now if if sql is kind of freaking you out don't worry about it if you're talking about accessing a database you already have it's not a big deal we'll walk you through the basics but if you really want to go further into what sql is and how to build a database and all that kind of stuff i do have a course for that the purpose of this video is not really pitched that course but but it is kind of a nice dovetail so I do have a course on I am Tim Corey comm it's about seven hours and it starts from I know nothing to I'm building complex databases with store procedures and creating complex queries and all the rest so that might be something that would interest you so but the basics here are int matches up directly to an int in C sharp Varchar and varchar these are all strings therefore they match up as strings in our C-sharp, okay, so back over in C sharp. We have our model This is what we're going to do to capture each row From that table alright, so we'll come back to this so don't don't get lost here. We're just matched. We're create a model and it matches up to our table. That's all you really need to worry about right now. The next thing I'm going to do is I'm going to set up a connection string helper. Now, a connection string, which we're going to put in our app.config here. See, there's no connection string yet. But when we have a connection string, I'm going to want to pull information out of this app config so that I can read how it talked to the database. So I create a helper for that just because it's a little bit longer of a string. I want to make something simpler. So let's add a new class. We're just going to call this helper.cs or as helper. I'll make this a public static class. All right, public static class helper. I'm going to get public static string CNN string. Or a CNN val. Let's put CNN val. All right, let's do that. I'll say string name. And so what that's going to do is when you say helper.cnnVal and give it a name, it's going to look up which connection string to get out of my app.config and return that value. Now the way to get a connection string is to say configuration manager dot connection strings Then put in square brackets the name of the connection string in this case. We'll call it name That's the variable right here and then dot connection string and we'll actually return this and this is actually a string and Now this whole thing will look up the connection string From just the name of the connection string and return that whole value, but it's yelling at me the reason why we have to add a reference So right click on references say add reference We're gonna search right now in frameworks You mean frameworks extension a recent but just click in the upper right hand corner where it says search and type Configuration just start typing it And it'll give you three options, probably, maybe two. But you're looking for system.configuration. This is baked right into C Sharp. It just isn't enabled by default. So we check the box next to system.configuration. Not the one below it, the system.configuration.install. The system.configuration. Check the box. Hit OK. Now we still have a red squiggly, but this time if we do the control dot while highlighting it somewhere, so I have my cursor somewhere on configuration manager where the red is, I hit control dot and drop down this menu, and it says add a using system dot configuration. Click that, adds it right here, and now we're good to go. So now whenever we say helper dot CNN val and pass the name of our configuration string, it will look to our app.config for that connection string. Now the next thing we need to do is actually add our connection string. And to add a connection string, under configuration here, we say connection strings. Then inside here, we say add. Name equals. This name is whatever name you want to give it. And it just identifies this particular connection. So I typically name these the same as my database name. So I might call this sample or sampleDB. I think I'll call it sampleDB, even though my database name is called sample. It just makes it a little more clear. And the connection string itself. Now this may be a big question mark, especially after you see kind of the complexity of what it could look like, but there is an easier way here, and I'm always about an easier way. So if you go to ConnectionStrings.com and click on the link for SQL Server, you'll get this page right here. ConnectionStrings.com has been around forever, and it's great. It's simple, and it shows you how to connect to... different databases using a connection string. And so basically just copy this, change the names where important, and paste it into your code. So in this case, I'm gonna use a trusted connection versus a standard security connection. This is all for SQL Server. So the difference between standard security and trusted connection is that in standard security, I'm passing a user ID and a password. I don't have to do that because my SQL Server has authorized my Windows login as an authorized user. Therefore, I can say trusted connection. When I say trusted connection, it says use your Windows credentials. Doesn't pass in your password or anything like that. It just says if you're logged in as Tim, then you have Tim's access, whatever that is. So I'm going to use this right here. I'm going to copy this whole string and then I paste it right inside my connection string. Now the things I need to change are my server name. Now this typically is just dot. Dot means local host. It means your current machine. So if you have SQL Server running locally, that's what you do. Now in my case, I have more than one instance of SQL Server running. Therefore mine is dot slash SQL 2016. And that's because I have two different versions of SQL running right now, one with 2014 and one in 2016. So this is the instance I want to connect to. My database. This is the default database that you're connecting to. When you connect to this server, as long as you have access, you can access any database you want, but you'd have to access it with the full database string name. So, for example, if you want to access the people table and you were in the... my database instead of the sample database, you'd have to say sampleDB.dbo.person or people table. Because we're going to put sample here, we don't have to add sample in front of everything we do. It's just assumed that's the database we're playing in. So that's it. Connection string is... Server equals dot slash SQL 2016 database equals sample and then trusted underscore connection equals true we leave that alone and finally Outside of our quotes here. We're going to say provider name equals system dot data dot SQL client and then it says what kind of connection string this is so it's a sql connection string and the reason why it's important is because these connection strings don't have to connect just to sql server they can connect to excel they can connect to mysql to you know sqlite there's a whole list of different things they can connect to so this just says we're connecting to sql server all right so there's our connection string So now we want to access this whole thing right here. This is the real important piece. That's the data we need. So in order to connect to SQL Server, we have to have this string. We're going to get this string by passing in just the sample DB name to our helper.cnnVal. so in here we're going to put sample db and it's going to this code right here is going to grab back and return this connection string now you may ask yourself why are we doing all this work when i could have just pasted this wherever i needed it well obviously the first reason is because it would then be hard coding your application you couldn't change it without recompiling at least but The other issue here is that you don't want to put into your source control server, say Git, now you're storing your data on GitHub, you wouldn't want to put into GitHub any kind of login name and password. And so you'd want to use a trusted connection and not put a login name and password here when you're committing this to source control. But maybe in a production environment, you have to use a login and password. In that case, what you do is what's called a config transform. And so what happens is when you build this application for a certain build profile, it can actually go in here and change this string to be something different. So in my company, we actually have multiple different connection strings. So there's one for the... the development server, there's one for the staging server, there's one for the production server. I also have one locally for my local database connections. They change based upon the build that we do. If that goes over your head, don't worry about it. The key thing here is you want to store connection strings in this app.config, not in your code itself. At the very least, you can change this app.config, which is just a text file. You can change this at runtime, and then just restart your application, and it will pull in the new connection string. So that's very helpful for changing your application, even though it's already compiled. All right, so that's our connection string. That's our connection string helper, and that is our model of our data. So we're doing pretty good so far. We've got things kind of wired up here. Now let's create something where we can actually display data. So I'm going to go ahead and open my toolbox. I'll open up the common controls. And let's get a list box right down here. And I'm going to open this up. i'm going to go into properties the first thing i'm going to do is change the name i'm going to say let's call this the people found list let's call it list box just because i don't want to get confused with this being a list versus a list box so let's call it people found list box all right So there's our list. What's going to happen is whenever we do a lookup, we find people, we're going to put them in this list box just so we can see, yes, we found them. And now let's add, let's actually pin this for now. Just makes it easier. I'm going to add a text box right here. I'm going to call this, I'll pin this one as well, I'm going to call this the last name text, and then I will give it a label as well, just so we have it. I'm going to call this last name label. and then scroll down to the text itself and say just last name all right so there's a last name and i'm gonna move it up a little bit and put in a button i guess button kind of centered here something like that and we'll call this the search button So what's going to happen is we're going to put in a name of a last name, whatever it is, and hit search. And it's going to go to the database. It's going to find the people that match that last name. and it's going to return those people and put them in this list box sounds a little complicated but trust me it really isn't so let's double click on the search button right here to create an event and this is our search button underscore click event and i'll unpin those now so in here we're going to call out to our database and search for just this last name. Now, if we were building a full fledged application, I would not put search code right here or even write in this form. I'd have a little bit of separation here to kind of disconnect me from what I'm doing. I'm gonna put a little bit of separation in, but just don't forget this is a sample application. I'd have a little more complexity in here if this is a real application. I would have broken out into a class library and tried to do everything I could in that class library. But in this case, I'm just going to say add class. Let's just call this data access. I'm going to get public. I'm going to create a method here called public list of person get people. And I'll have it by last name. So you're going to pass in a last name, get back a list of person. So I'll call this. Let's just, so this throw new not implemented exception, what this does is allows us to compile the application because if you throw an exception, that kind of overrides whatever return type you needed. So a not implemented exception, what its job is, is to allow us to compile the application while we're working on it. So I've kind of stubbed out this method here, but I'm not yet ready to put the code in. But I want to go back to my dashboard and call this so that I am ready to use it once it's actually implemented. So I come back over here to dashboard, and I can say, Data access DB equals new data access DB dot get people by and our text box here is called last name text. So last name text dot text. That's the actual value of the text box. And so now we have our list of persons. So list of person. And actually, let's put that list of people up here. List of person. People. equals new list of person just we have an empty list to start with and then we will say we're gonna override that with Whatever we get back from our lookup so with just a little bit of code what I've done is I've populated a list now naturally I tie this to our list box and then we'll come back to how to actually get data out of the database. It's like a branching thing we're doing here. We're doing three things at once. This would be a little bit easier if you mapped out your application. I definitely encourage you to map out your application whenever you build one, map it out to make sure that you know all the different parts you need, because I would have built this and all the code before I ever even started even this. let alone the wire up okay so because i'm doing it kind of in line for you just to show you what i'm doing i'm not kind of doing it all at once i don't want to get that to be too confusing but the same time i don't want it to be like i'm doing a little over here a little over there and all of a sudden boom magic happens and it all works so that's why it's kind of a branching path i'm gonna do to get both the front and the back end done at the same time so we're gonna come back to Actually wiring up how to get the list of person In just a minute, but in the meantime, I'm going to wire up. What do I do with that list of person? so the first thing to do is Connect our list box, which is called people found list box So people found list box dot data source Equals people now. This has got a little bit easier since the earlier days of C sharp it used to be that in order to connect to a list you would need to have a Binding source sit between the list and a list box, but now it's become a little bit easier We can actually connect directly using this just data source equals the list so the only thing we need to do is say People found this box dot display member equals and then give it one property name now If we look at our person class there isn't really a property that kind of Encapsulates all the information you want to display about the person so let's actually create a new property Say prop full and call it a string property and I will call it, let's call it a full info. And then actually I'm gonna delete the private backing field. And I'm gonna delete the set. This is just read only. And I expand out this get. And instead of returning the my var, I'm gonna say dollar sign. and double quotes. Now I can say inside there curly brace first name and curly brace space curly brace space last name and curly brace. And then I'll do me a space and a parens and then a curly brace and say email address and curly brace. and i'll end the parens and so what that'll do is it will return the user in the format of say tim corey test at test.com like that okay without the quotes so that's the format of the return our entry in now we don't have phone number as well we could put that if you wanted to but i think this is good enough So the full info property is a read only property that just reads all the properties here and takes bits and pieces from different ones to make a strain that represents that user. So back over here inside of double quotes for the display member, we're going to paste full info. And I do paste that there because a copy and paste is a whole lot easier. Oops. It's over here Copying from here and then pasting it over into here is a whole lot easier and simpler Than try to type it out and make sure there's no typos because it is inside of Quotes there is no intellisense and so whatever you type here is gonna be accepted but not necessarily work so the final thing we need to do is we need to hook up the data access itself this right here and once you do that it should be that whenever we type a name in here and hit search that it populates a list of people that it finds so let's do that let's go back here to our get people and actually take this out and talk to sql server and so this is the part where you actually connect to sql server Now to talk to SQL, we first need to add a reference to Dapr. So on references over here on the right, I right click and say manage NuGet packages. Now NuGet is a tool by which we can download third party and Microsoft libraries and do so in a way that allows us to also update them automatically or very easily to click a button. So under Browse, I type Search and I start typing Dapper. And the very first one is called Dapper. And it's by Sam Saffron, Mark Gravel, and Nick Craver. So make sure you get this one, not other dappers. There's other kind of like add-ins for it. But this one right here is the official one. And it will connect to not only SQL Server, but MySQL, SQLite, and others. And the current stable release is 1.50.2. And if you really want it, so you can go back in time and connect to... even the or any other versions since then. But in this case, the latest and greatest stable version is great. So I hit install and it's going to come down here and look for what do I need, and then it's going to start installing Dapr for us. and that's it so now dapper has been installed notice it says i can uninstall it or update it to a different version which in this case would actually be a backwards update it's actually reverting back to a previous version if you wanted to but if there's a new version that update would take us to that new version number so now i can add a using statement up here using dapper Then inside of my get people I Start with a using statement Now up here using statements are different than using statements inside my code So you using statement inside my code allows me to call some code Like with a connection and it says as soon as you're done the using statements Destroy that connection And so using statements is very helpful for making sure we don't leave connections open to our server. All right. So I'm going to say IDB connection. Control dot there to add a using system dot data. I'm going to say connection equals new SQL client dot SQL connection. And then inside here I need my connection string. Now let's fix this first. Let's change it to system.data.sqlclient. So inside of this right here, I need my connection string. So this is that string we had back here in app.config, this right here. So how do we get that? Well, we say helper.cnnVal for sample DB. All right, so that will give us our connection string. Then all we need to do is add our curly braces. And these curly braces tell us where the start and finish of this using statement is. So what have we done so far? Well, we have created a new connection, this connection right here. We've created a new connection to our SQL database. It really was that simple. And if you kind of blink, you missed it. Now, is this a little complicated to remember? Sure, it's not the simplest string to remember, but don't worry. Just copy and paste this template right here every time you do it. Okay, kind of keep this in a text file somewhere or something like that. I go back and refer to previous projects, whatever. And I say, okay, here's the way I connect to my SQL server. So and this just creates a connection to our SQL Server. It just says we've opened the door and now when you ask for information, you can get it back. And that's all going to happen inside these curly braces. Because as soon as this curly brace gets hit, the door gets closed again. It's very, very important to close those doors when you're talking about SQL connections. Open connections in the past have been a huge issue. where your application will actually start slowing down and your SQL Server bogged down, you're not quite sure why, and a reboot fixes things, and you're like, I'm not sure what's happening. Well, it's probably open connections that were just never killed properly. So this using statement right here makes all that go away because it automatically closes when it gets here. All right, so how do we actually talk to SQL Server? Well, connection.query. All right, so connection.query says I want to ask for data back. all right and what kind of data well i want person data back now that's my model remember that's got a first name last name email address and phone number just like my table has now we need to give it command how do we get person information well let's say select star from people where last name equals and then we'll put actually we'll put the dollar sign in front of this equals and we'll put single quotes around this in the middle of single quotes we'll put last name now this is not how i recommend you do it but this is the first step to doing this. This is the easiest way or the most simplest way to understand how to do this. Now what this is doing is saying, okay, take the connection you have open already, ask for, get data back, this type of data, here's my command, select star from people where last name equals and the last name is given. Now this returns a IEnumerable of type Person. We want to list back. So we can't just say return because it's the wrong type. But at the very end of this, after the closing paren, we can say.toList. And that gives us all the information we need to connect to our database and ask for information and then return it from our method. And before we move on, the one thing I just noticed is I'm missing a... close paren up here you notice it was yelling at me right here it's giving me a red squiggly which i might have missed but notice it says closed curly brace expected which is an interesting error so what it really means is counter parens Okay, so I had one open paren here, I had one here, and I had one here. Therefore, I need one, two, three over here to close them all out. So that's just what that error is all about. So at this point, barring any typos or other issues, we should have a working application that asks for data from our SQL database. Seems kind of hard to believe. There's not a whole lot of code here, but let's take a peek. So let's run this. Here we go. Last name. So let's put in green. Hit search. See what happens. Nothing happens. Excellent. Let's find out why. All right, so to debug this, what I'm going to do is put my breakpoint right here on the return. And this is where it's going to return my actual data. Now, I'm pretty sure what the problem is. But if I didn't or if I wasn't quite sure, I actually break this line up into two lines. One would be to put this information into a variable. In fact, let's do that just to show you what I'd do. So I would say, whoops, got to stop our code first. I would say var output equals and then down here say return output. Now all that does is puts the list in a variable. I know this is a type var, which means basically whatever you give me, I'm going to create that strong type. So it's going to create a list of person. So it's going to be. But var allows me to just say var and then it fills in the right type for me. I won't be able to change the type once I create it, but this makes it easier. So now if I hit the breakpoint down here, it's captioning the information into this variable right here called output. And then next step is returning output. That way I know what the data is before it gets sent back. So let's start this again. I'll move my form back over. I'll type in just green again. Always repeat your same exact test over again, just in case part of the issue was green. There is no greens or something like that. So I hit search and it goes right to my breakpoint and mouse over output. And if you can see there, it's kind of small. I can zoom in just let's zoom right in and now output count two. Notice that it has Bertha green and also Clementine green. So we do have information coming back, at least to this point. So if we step over this code, let's just step into, and it comes back to where it got called, and the calling is db.getPeople. okay and it puts it into the people count notice also that before i do this next step people has a count of zero meaning there's no people in the person list so if i now step into now people has two people in it that's again bertha green and clementine green so if i hit continue Why isn't anybody in this list? Well, that's because we need to reset our bindings now that we have added or changed the list. So people found list box dot reset. Oops, not reset bindings. Reset bindings is if you actually have that connector, that binding source. So instead, we'll need to actually do a refresh of our data source. Or we might be able to get away with just doing a.refresh. Let's find out first. Okay. No, I don't know everything off the top of my head, and that's okay. I don't have to. all right let's go ahead and um uncheck this uh breakpoint we don't need that right now we know it works and nothing's happening so i'm assuming that that refresh isn't going to work but you know what i do know that that i can do this i'll just paste it in and that's just a repaste of what's right up there let's see if that works first and then we'll make this a little prettier so i'll do this again the same thing again and now we actually have people popping in the list which is great and actually we need to make this list bigger because it's it's just not wide enough so let's let's do that first that's an easy one to fix make that a little more hearty We can actually shrink it up some too because we don't need to have a ton in that list because we're not going to find more than two or three the same last name I don't think. So this right here, this code assigns it initially and we have to refresh the data source every time we have an update to our list. So let's create a private void update binding. And here we put that same code and then instead of the code every spot we just say update binding This code and put everywhere So now we're calling it from multiple locations and we only have this code in one spot That way if we ever need to change that code is something a little different. We change it in one spot not three let's just make sure this all works let's do farmer and there's three farmers patience buckminster and eliana and again just make sure green works again there we go notice how fast that's working that's actually pulling data from my actual sql server and it's putting it right into my list box Now this is what kind of confuses some people in the fact that it's just so simple. Really the data access to talk to our SQL server is just this. Now we did do the helper here for getting the connection string. we could have taken that out and actually had the the connection string look up right in there we made a little longer a little more complex and i like this a little better but this right here is all there is to talking to our sql server now we are going to get a little more complex in the fact that this right here is not good practice this is straight sql And the problem here is we're susceptible to SQL injection. And if you're not already cringing when you hear SQL injection, go look it up. SQL injection is a bad thing. Essentially what it means is you can give a person on the front end access to do terrible things to your database. You don't want to give a person access to do terrible things to your database. So you want to be a little more secure than that. First step to this is always make sure that you are secure. looking at the data coming in. Obviously not physically because this is what an application does, but instead making sure that you filter out certain characters or limit lengths of names and other things. Make sure you drop off certain code or anything else like that that would be a problem. But the other thing here is, and this is what I always recommend, is use store procedures. Store procedures are so much more robust, more powerful, and it'll allow us to do a whole lot more. So how do we change this over to a store procedure? Well, it's actually pretty simple. I'm going to actually wipe this out and start over. I'm going to say, you know what? I'm going to copy this line. I'm going to give you a source code in my blog so you can have it. So I'm going to actually comment this out just so you can see how to call. just raw SQL if you ever needed to. But again, I don't recommend it as the first choice. There are some times when it's useful, but by and large, try and stay away from it, especially when you do this string concatenation thing that could really be a problem. All right. So inside my double quotes here, instead of putting my straight SQL, I'm going to put my dbo.person, I didn't forget the name of it. Let's go ahead and look back here. I'm sorry, it's people. That's what it is, people get by last name. All right, so actually copy that. This is my store procedure for looking up people. All right, and it takes in one parameter. and again if you're not familiar with sql um parameters essentially you have to be named at and then the name of it so in this case at last name and i'll show you that right here let's modify a sql server just start procedure it takes an at last name and it has a envar chart length of 50 and that just says select star from people where last name equals last name looks kind of familiar that's essentially the same code that we did raw We're now going to do inside of a store procedure. So we have to pass in this last name. Well, how do you pass in a last name? Well, this is where we create a new dynamic class. So we say new and then open curly brace. Say last name equals last name. All right. And then close curly brace. Now, what did I just do there? Well, I create a new class, a new class instance of a class that doesn't exist. This is a dynamic class. All right. So this is C sharp being wonderful. So I did what I said, okay, I want a new class instance. And it's going to have a property called last name. Notice how this matches up to this except for the at symbol. And then I said the value is going to be last name. Notice how this is matched up with our variable name. So the variable name last name is being passed in. And I have said, okay, that's the last name I want to look up. That goes into this, which, and here's a little bit of magic that Dapper does. It will take this property and put it into. this spot right here safely. Now we're going to call this store procedure, passing in these parameters and we're matching them up using this dynamic class. The only changes I've made. Let's run this again and let's look for a farmer again. Same thing. Works just great. So the difference is that I'm calling my store procedure instead of calling my direct SQL. So whenever possible, I recommend that you do the store procedure route, not the direct SQL route. And it is very simple to do. as long as you have store procedures or can create them store procedures are not that hard to do so essentially all it is is you say create instead of alter create procedure give it a name make sure you put a dbo dot in front of it don't remember the square brackets here that's what sql server adds i don't find them very useful but dbo dot and then the store procedure name Any variables and then as you have begin and end inside there just have your select statement. This set no count on what it does is it says okay don't return how many rows you returned. There's not really a need for that. So set no count on just returns less information less data across the wire. So, and that's my, my speed version of what is a store procedure and how do you create one? All right. You can right click on these and say script as create to, and it will create the create for you. And then you just modify this in here and modify a name. You're good to go. So it can create the structure for you, or you can right click on store procedure, say new store proc. And then it gives you this, which. quite frankly is way too busy for me but this up here you can get rid of you can really get rid of this too if you want but this name box you can get rid of use something i would say but you can get rid of that you can change the name right here add your parameters and get rid of the rest of this and then just get rid of this and put in your own statement so the It's helpful to do that. It gives you the structure, but really a store procedure is pretty simple to build. Again, if you really want to learn more, I do have that course about seven hours worth on how to start in the SQL all the way through store procedures. We have a store procedure now that gets by last name, the person. displays that inside of our our list box now that's one direction we've got information out of sequel okay so we've we've called it we said give me all the information put it into a person class now I kind of skipped over that for a little bit let's come back to that from it so when we say call this store procedure Put it in the person class. What does that really do? Well, it says I have data that comes back and so what used to happen is we access our SQL server we'd say give me data and return a data table and inside a data table We had rows and columns and we had to kind of match up what the data was and how to use it actually to kind of go through line by line or row by row and take the data out of the table and put it into a model of some kind and Then put that model into a list of model and then finally return that this line right here Does all that for you? It takes the return data and it goes and opens up the person class and this is okay i have five properties i can use five read write properties id first name last name email address and phone number so i'll take my my first row and see okay what columns do i have and if i have an id column i'll put that value in this property and if i have a first name value column i'll put that value in this property and so on and so forth down the list When it's done, it loops through the next record and does the same exact thing. And it keeps creating new instances of type person and puts it in a list for us. So it does all this work for us behind the scenes. And essentially, it's just matching up name for name. So what that means is if you don't include, say if we didn't include phone number. our model everything would still work the matchup would still work it would just say I have extra data that don't have a place to put therefore I just delete it get rid of it in the same way if we have properties in here that we don't have columns for in our sequel table it'll ignore them and it's leaving us null so it's really robust And some people will say, well, but I want to name things differently in this model. And my encouragement to you is don't. You can. And if you go to Adapters documentation, and I'll have a link to that in the blog. If you go to Adapters documentation, they will show you how to map a different property name to a column name in SQL. Just don't. It's a whole lot easier to have a list that matches exactly with your SQL. Then if you want to change that or morph that into something else you take that list and create a new model and Put the data over from this model into that model. Okay, this this is a data model just Pretend like it's actually sequel don't touch it make it the same as sequel and then it's use it that way And then if you need to change something change something later with a different model, so that's just my app my two cents there you could go crazy and change things and that's fine it just makes it much more complex and quite frankly i am just enamored by the fact that in two lines really in one line well two lines this line right here in this line right here in two lines i can open up a connection to sql get data out of it and close that connection that's just beautiful Especially since I'm getting back then a list of type person or a list of my model. Because I can work with that. In fact, here in the dashboard, I am working with that. I'm taking that list, putting it right into my list for my form, and displaying it in my list box. So I can use that very, very easily. So now let's switch gears and go to how to put information into our SQL database. So let's create a new section down here. We're going to open this up. I'm actually going to copy and paste this. I have a naming issue because that last name is going to be the same as above so I have to change that to something else alright so I have my four values here my first name last name email address and phone number so let's go ahead and start naming these pin this now I'll call this first name let's call it first name ints label for insert and then last name ints label and email address ints label and phone number ints label And this will be the first name, I-N-S, text. Last name, I-N-S, text. We call it email or email address. Email address. So email, address, I-N-S, text. and phone number INS text. Now we're going to name these boxes, the actual values in there. So we'll say first name, email address, and phone number. I'll move these boxes over so you can actually have some. alignment there we go so now we have our first name last name email address and phone number we'll create a button down here well let's just copy the same button let's there we go i'll copy this button so it's the same size something like that and instead of search we're going to say insert we'll call this the insert record button and we're going to double click on it and here we're going to do the same kind of thing we're going to say data access db equals new data access and then we're going to say Just db.insertPerson. We'll give him the first name, insert.text.text. Now, this method doesn't exist yet. We're going to create this in just a minute. So I'm just going to kind of stub it out as to what it's going to look like. So last name, ins.text.text. Email address, text.text. and phone number text dot text and that's really all there is to it so with these unpinned you can see that now this doesn't exist yet I could drop down and say generate method if I do that notice inside data access it's created as internal void insert person or something that's public Void insert person now, let's call it text one text to text three and text four which is just awful so first name Last name Email address and phone number No spaces, okay, I notice the throw new not implemented exception Since I had the system auto create it for me, it puts that throw new not implemented exception there just so we can compile again, but it allows me to keep working where I was. So I could have just kept going here if I had to do more work, but that's all the work I need to do. The last thing I'd probably do here though is let's just, let's go ahead and set all these text fields to be empty. That way they're all cleared out. Now we can also tell we've done something. Now over here, we need to do our same using statements. I'm actually just kind of copy this. Remember I said that copy and paste right here is your friend. and the fact that I have to remember this whole string here. But instead of doing the connection.query, I'm going to do things a little bit differently this time. Now, there's a couple ways we can do this. First thing we need to do is get together our data to be inserted. Now, we have the first name, last name, email address, and phone number. But the way that this works is I can insert one row, or 50 rows. So we could do things a little bit differently, but in this case what I'm going to do, I'm going to create a Person class, a Person class instance. So new Person equals new Person. And then I will say, now I could do this in two different ways, I could say equals new Person actually. Stub out first name last name email phone number right here. In fact, I'll probably do that Make it simple All right, oops crate brace So what I did here, let's just expand this here. It's a little large, and I can make that a little smaller by stacking it a little better. But in one line, I said, here's my variable I want to create. It's a person variable called newPerson. Equals newPerson, but instead of the open and close paren, I put open and close curly braces. Inside there, I said, here's the values for the properties. The first name property, the value is first name, which is what comes from our pasting value. The last name property, the value is last name, also comes from pasting value. Email address, you get the picture. Phone number, phone number. So I could do that, or I could have just said open equals new person, open close parens, and then said new person dot first name, you know, new person dot first name equals first name. And then next line, new person dot last name equals last name and all the rest. So I can either have, you know, four or five lines down here, or I can have it all in one line right there. It's really up to you. But that's, that's what we have. Now, because the insert is actually expecting more than one record, now, zero or more, or actually, I'm sorry, one or more records. So what you do is create a list of person called people equals new list of person. And then I'll say people.add new person. All right, now if I want to make things even simpler, I could take this code right here, copy that, and paste it in place of here. Then I don't even need this variable. So let's go ahead and do that. I'm going to take this and I copy it, but then I'll comment this out. I'm going to paste the code right in line. So without even assigning this instance, right here, this instance right here, without assigning it first to a variable and then putting it into my list, I'm directly putting it into my list. Just makes things quick and easy and I don't need to worry about anything else. It's not a list that encompasses one person. I insert that person into the database. How do I do that? Connection. execute I Need my my store procedure DBO dots People leave it's in search. We'll check that in a minute But then I have to pass in for this store procedure at the pass in four variables at first name at Oops. At last name. Notice a comma separation. At email address. At phone number. And then how do I fill that in? Well, I just say people. If I can spell it right. Because people is a list of person. And so I said, okay, in there, in each instance, each person, I need to find four different properties. And it says, okay, first name property. What's the value of that? It pulls it in, puts it there. Last name, same thing, email address, and phone number. fills in those four values, calls the insert statement, and does it for every single record we have in this list, which means that we could just keep adding people to the list, or in our front end, I could have a running list. Say if I just, when I hit insert, it added it to this list up here. And then when I say I'm totally done, it takes this entire list, passes it over to data access for the insert, and says insert them all. And it would do that. Now, again, let's go ahead and check to see if this works. It's always fun to write code and then see if it actually makes a difference. Now, first, let's verify if there's any Corys in the database. I hit search. I get nothing back. So, timcorytestattest.com, and phone number is... 570 555 1212 sounds good. I hit insert now has it cleared all those out. Now if I hit search for Corey I actually have one record coming back from the database. Now, let's just verify that works by saying James Corey, Jimmy at test.com, and phone number is, let's put in a 212 area code, because why not, but it's still 555-1234. We insert James, and now if I do a search for Corey, I have two records in the database, test and. Jimmy, bolt that test.com. So that's all there is to insert. Okay. So instead of doing a connection dot query, we say. connection.execute. Same store procedure. Notice we don't pass in what for query we had to pass in what type because that's the information coming out. But for execute we don't have to pass in the type because we already know that based upon what type we're sending in and we're not actually getting data back out of this. Now we do the call to our store procedure. We pass in the four parameters which happen to line up for good reason they line up with our our person class properties so when we pass in one person it executes the store procedure puts that person in the database okay so that's all there is to adding people or inserting records into the database this could also work for updates it's the same call it's just whatever your store procedure does what's update versus add versus delete versus whatever okay so really that's all there is to reading and writing from sequel server it's pretty simple this including code comments that I made that's all the code you need to talk to a sequel server get information out of it and then put information into it as well and from the front end perspective it's really easy to call these because since this is just kind of hiding everything the reality is when i ask for git people i'm just getting a list of people or a list of person back and so i can deal with that i've been doing that for time you know in my videos i always deal with a list of t a list of person a list of whatever And people ask me, well, how do I get information in and out of the database? The reality is you're working with this list of T all the time because there's one method somewhere like this that says get the information out of the database and put it into a list of T or a list of person, whatever. And then, you know, how do I get information back in the database? No problem. You have something like this where it takes your. List of people or whoever it is and it puts that information into the database and you may say well What do I know? How do I know if it's an insert versus an update versus delete? Well, that's something that your merge statement can handle inside of sequel the merge statement allows you to do an insert and an update and delete depending on how you What data you pass in? So, but that's really something more for once you're more familiar with SQL. In the meantime, you create separate store procedures for insert versus update. So, and if you have any more questions about SQL itself, like I said, I've got that video series, um, that takes you through all of that. Um, it's not something that I'm going to necessarily create a whole bunch more videos on because I really do cover from start to finish in my course. So from a C sharp side of things, Dapper is your friend. It's a great tool. I recommend it highly. It's heavily backed by a company that's not going anywhere. And so it's not something where. you know next week or next month that's going to be gone it's it's here to stay and this makes things really easy this this code right here can't be much simpler for accessing the database So with that being said, definitely let me know down below in the comments what your thoughts are. If you have any questions, please post them down below and I'll try to get back to you and answer them as soon as possible. Also, make sure to subscribe. People who are on my mailing list, which is also linked down below and also in my blog post. People who are on my mailing list do get discounts and early notifications of upcoming courses. Upcoming content and they actually get more content as well So make sure you subscribe to my mailing list and when you do you go get an email it says hey, let me know Of any information you have that you all let me know right at the bat You know any questions you have those type of things and it's gonna ask it's gonna tell you I'm a real person Respond and you'll find out every person who? Joins my mailing list and emails me gets an email back personalized for me Not a form letter. They're going to get a real email. If you email me, I will email you. So with that being said, that's all there is for this video. I appreciate you watching. I appreciate you subscribing. And I appreciate all you guys do to encourage me. I'm definitely coming out with more videos. I've got a start to finish series coming out soon on C Sharp. So definitely look out for that. All right. Thank you very much. Have a great day.

I'd love to do it. I'd love to get information in and out of it, but it just seems so difficult. In this video, I'm going to show you how to break that down into a really simple and easy to follow process.

You don't have to worry about data access. Data access will become the least of your concerns when it comes to your application. But before you get into how to do that, I want to talk through first a couple of options.

Now there's a lot of different ways we could connect to a SQL Server. Initially, I had always done directado.net. And what that is, is just a direct connection using just C Sharp. And it's pretty, you know, foundational or core.

And that works, and it works well, but it's a little complicated. And I was never really comfortable showing off how to do it because of all the different configuration pieces you need to have. And the other side of that, the other end of the spectrum, is Microsoft's Entity Framework. And Entity Framework is an ORM.

And what that means is that it takes care of the magic of connecting to a database. And there's some really powerful things to that. It makes it work really well.

But here's the kicker, at least for me, it's only when it works that it works well. And that kind of scares me because it's a black box for most of that data access. And so you could dive really deep and there's some really smart people that have gone off the end of the deep end. into Entity Framework.

They've learned a ton of stuff about it and they know how to make it sing. And that's amazing. That's great.

But I'm not one of those people. I don't spend hours and hours and hours learning Entity Framework. One of the reasons for that is because I've been concerned by the problems I've seen.

I've seen people who have a system in place, it's working great, they put an application into production, and then two weeks down the road, something blows up. and they spend hours trying to debug what's happening inside the black box you see they ask for data and then entity framework takes that request does some magic and talks to the database and gives you back data and that that area does some magic sometimes breaks down and when it does it's scary And so that's just my personal opinion that I don't use Entity Framework. I really avoid whenever possible. It's just a big complexity that I don't feel I have the ability to add to my applications in a way that's going to be positive for the application.

Now like I said, I've used directado.net connections for a long time, but again it's a little complex. So we have a little complex on one side, but I control everything. to the very complex on the other side, and I don't really have a whole lot of control, and that's Entity Framework. So I found something that's in the middle that is really, really powerful and easy to use, and that's a tool called Dapper. And we're going to use Dapper for our data access because I really think it adds just enough to make things simple, and yet not enough where that black box is so complex or is easy to break.

And actually Dapper is put out by the people who do Stack Overflow. If you're not familiar, Stack Overflow is a massive website for tech help on the internet. And so they actually built Dapper to help them access their database, their SQL database.

And so they actually open sourced this and gave it away for free and they support it. So it's heavily supported, heavily tested, and it's production ready in a large production environment. So I feel very comfortable recommending Dapper. Now, normally I don't recommend you add extra things to C Sharp. I try and teach you direct C Sharp, not add-ons.

You're not a big fan of having 30 or 50 or 80 different add-ons and plugins and NuGet packages because it adds a dependency every time you do something. But Dapper adds enough value while having small enough overhead I think it makes it into my top two or three things that I will recommend you add. So that being said, let's dive right into our demo.

I decided for this demo to start right from scratch. That way you are comfortable with the entire process. Now if you want this source code, if you want bits of it, you'll find all the information on my blog on imtimcorey.com in the article for this video. Let's start with a new project, and we're going to do a Windows Form application. That's pretty simple.

And we'll call this Form UI for our form. InfoSolution name, we'll call it SQL Server. I'll just call it SQL data access demo. We'll hit okay.

We have our form UI. Let's rename form one to something simple like dashboard. We'll use this as both our yes, I'll rename everything. We'll use this as both our our data input and our data display. That way it's just kind of simple.

This is not a production application. This is just to show off what we can do with Dapper. I'll make that a little bigger. I'll actually mess the property just a bit, just because I want to clean it up.

And also change the font size so it's a little more readable on this screen. We got a 16 point font. There we go.

Something a little bigger. Okay, now I have a database. Let me pull it up for you. All right, here we are.

And in this database called sample, I have one table, dbo.people. And then I have two store procedures, one called people underscore get by last name, and one called people underscore insert. So the people table, let's just select top 1,000. That gives us, I have pre-populated this with dummy data from the internet. Okay, there's tools out there that can.

generate insert scripts for you just to put some um dummy data that kind of fits you know so i i said give me first names give me last names give me email addresses give me phone numbers from where and how doesn't really matter okay it just creates them for me so that's a pre-populated table i've got and then the store procedures the get by last name you just give it a last name and it finds all people that match that last name and then the people insert simply adds a new person into the people table so that's our database that's where i pull from so the first thing i'm going to do over here in my form is to create a model that i can put data into that model is simply a class so let's add a new class I'm going to call this person. Because remember that each class instance will be one person. So I wouldn't call it people, I'd call it person.

I'll make it public. Then what I'll do here is I'm going to add the properties that match the columns of data I'm going to ask for back. So let's go back over to this people table. Let's actually design it and look we have an ID, first name, last name, email address, and phone number.

Now I could change those but I'm going to match those up directly because this is a a model I'm going to use to capture information from a database or to send information back to the database. So I'm going to match it up exactly how it is in the database. I'm going to say prop int id Prop string first name, prop string last name, prop string email address, prop string phone number. There's a model and this matches exactly what's in my table. I have these five properties.

Four of them are string and one of them is an int. And over here in my table, I have an int for ID and the rest are some type of string. I have an nvarchar 50, nvarchar 50, I have an nvarchar 100, and then a varchar 20. okay so those are the four different types of string fields i have now if if sql is kind of freaking you out don't worry about it if you're talking about accessing a database you already have it's not a big deal we'll walk you through the basics but if you really want to go further into what sql is and how to build a database and all that kind of stuff i do have a course for that the purpose of this video is not really pitched that course but but it is kind of a nice dovetail so I do have a course on I am Tim Corey comm it's about seven hours and it starts from I know nothing to I'm building complex databases with store procedures and creating complex queries and all the rest so that might be something that would interest you so but the basics here are int matches up directly to an int in C sharp Varchar and varchar these are all strings therefore they match up as strings in our C-sharp, okay, so back over in C sharp. We have our model This is what we're going to do to capture each row From that table alright, so we'll come back to this so don't don't get lost here.

We're just matched. We're create a model and it matches up to our table. That's all you really need to worry about right now.

The next thing I'm going to do is I'm going to set up a connection string helper. Now, a connection string, which we're going to put in our app.config here. See, there's no connection string yet.

But when we have a connection string, I'm going to want to pull information out of this app config so that I can read how it talked to the database. So I create a helper for that just because it's a little bit longer of a string. I want to make something simpler. So let's add a new class. We're just going to call this helper.cs or as helper.

I'll make this a public static class. All right, public static class helper. I'm going to get public static string CNN string. Or a CNN val. Let's put CNN val.

All right, let's do that. I'll say string name. And so what that's going to do is when you say helper.cnnVal and give it a name, it's going to look up which connection string to get out of my app.config and return that value. Now the way to get a connection string is to say configuration manager dot connection strings Then put in square brackets the name of the connection string in this case.

We'll call it name That's the variable right here and then dot connection string and we'll actually return this and this is actually a string and Now this whole thing will look up the connection string From just the name of the connection string and return that whole value, but it's yelling at me the reason why we have to add a reference So right click on references say add reference We're gonna search right now in frameworks You mean frameworks extension a recent but just click in the upper right hand corner where it says search and type Configuration just start typing it And it'll give you three options, probably, maybe two. But you're looking for system.configuration. This is baked right into C Sharp. It just isn't enabled by default. So we check the box next to system.configuration.

Not the one below it, the system.configuration.install. The system.configuration. Check the box.

Hit OK. Now we still have a red squiggly, but this time if we do the control dot while highlighting it somewhere, so I have my cursor somewhere on configuration manager where the red is, I hit control dot and drop down this menu, and it says add a using system dot configuration. Click that, adds it right here, and now we're good to go.

So now whenever we say helper dot CNN val and pass the name of our configuration string, it will look to our app.config for that connection string. Now the next thing we need to do is actually add our connection string. And to add a connection string, under configuration here, we say connection strings.

Then inside here, we say add. Name equals. This name is whatever name you want to give it. And it just identifies this particular connection. So I typically name these the same as my database name.

So I might call this sample or sampleDB. I think I'll call it sampleDB, even though my database name is called sample. It just makes it a little more clear.

And the connection string itself. Now this may be a big question mark, especially after you see kind of the complexity of what it could look like, but there is an easier way here, and I'm always about an easier way. So if you go to ConnectionStrings.com and click on the link for SQL Server, you'll get this page right here. ConnectionStrings.com has been around forever, and it's great.

It's simple, and it shows you how to connect to... different databases using a connection string. And so basically just copy this, change the names where important, and paste it into your code. So in this case, I'm gonna use a trusted connection versus a standard security connection. This is all for SQL Server.

So the difference between standard security and trusted connection is that in standard security, I'm passing a user ID and a password. I don't have to do that because my SQL Server has authorized my Windows login as an authorized user. Therefore, I can say trusted connection.

When I say trusted connection, it says use your Windows credentials. Doesn't pass in your password or anything like that. It just says if you're logged in as Tim, then you have Tim's access, whatever that is.

So I'm going to use this right here. I'm going to copy this whole string and then I paste it right inside my connection string. Now the things I need to change are my server name.

Now this typically is just dot. Dot means local host. It means your current machine.

So if you have SQL Server running locally, that's what you do. Now in my case, I have more than one instance of SQL Server running. Therefore mine is dot slash SQL 2016. And that's because I have two different versions of SQL running right now, one with 2014 and one in 2016. So this is the instance I want to connect to.

My database. This is the default database that you're connecting to. When you connect to this server, as long as you have access, you can access any database you want, but you'd have to access it with the full database string name. So, for example, if you want to access the people table and you were in the...

my database instead of the sample database, you'd have to say sampleDB.dbo.person or people table. Because we're going to put sample here, we don't have to add sample in front of everything we do. It's just assumed that's the database we're playing in.

So that's it. Connection string is... Server equals dot slash SQL 2016 database equals sample and then trusted underscore connection equals true we leave that alone and finally Outside of our quotes here.

We're going to say provider name equals system dot data dot SQL client and then it says what kind of connection string this is so it's a sql connection string and the reason why it's important is because these connection strings don't have to connect just to sql server they can connect to excel they can connect to mysql to you know sqlite there's a whole list of different things they can connect to so this just says we're connecting to sql server all right so there's our connection string So now we want to access this whole thing right here. This is the real important piece. That's the data we need.

So in order to connect to SQL Server, we have to have this string. We're going to get this string by passing in just the sample DB name to our helper.cnnVal. so in here we're going to put sample db and it's going to this code right here is going to grab back and return this connection string now you may ask yourself why are we doing all this work when i could have just pasted this wherever i needed it well obviously the first reason is because it would then be hard coding your application you couldn't change it without recompiling at least but The other issue here is that you don't want to put into your source control server, say Git, now you're storing your data on GitHub, you wouldn't want to put into GitHub any kind of login name and password. And so you'd want to use a trusted connection and not put a login name and password here when you're committing this to source control.

But maybe in a production environment, you have to use a login and password. In that case, what you do is what's called a config transform. And so what happens is when you build this application for a certain build profile, it can actually go in here and change this string to be something different. So in my company, we actually have multiple different connection strings. So there's one for the...

the development server, there's one for the staging server, there's one for the production server. I also have one locally for my local database connections. They change based upon the build that we do.

If that goes over your head, don't worry about it. The key thing here is you want to store connection strings in this app.config, not in your code itself. At the very least, you can change this app.config, which is just a text file.

You can change this at runtime, and then just restart your application, and it will pull in the new connection string. So that's very helpful for changing your application, even though it's already compiled. All right, so that's our connection string.

That's our connection string helper, and that is our model of our data. So we're doing pretty good so far. We've got things kind of wired up here.

Now let's create something where we can actually display data. So I'm going to go ahead and open my toolbox. I'll open up the common controls.

And let's get a list box right down here. And I'm going to open this up. i'm going to go into properties the first thing i'm going to do is change the name i'm going to say let's call this the people found list let's call it list box just because i don't want to get confused with this being a list versus a list box so let's call it people found list box all right So there's our list. What's going to happen is whenever we do a lookup, we find people, we're going to put them in this list box just so we can see, yes, we found them.

And now let's add, let's actually pin this for now. Just makes it easier. I'm going to add a text box right here. I'm going to call this, I'll pin this one as well, I'm going to call this the last name text, and then I will give it a label as well, just so we have it. I'm going to call this last name label.

and then scroll down to the text itself and say just last name all right so there's a last name and i'm gonna move it up a little bit and put in a button i guess button kind of centered here something like that and we'll call this the search button So what's going to happen is we're going to put in a name of a last name, whatever it is, and hit search. And it's going to go to the database. It's going to find the people that match that last name. and it's going to return those people and put them in this list box sounds a little complicated but trust me it really isn't so let's double click on the search button right here to create an event and this is our search button underscore click event and i'll unpin those now so in here we're going to call out to our database and search for just this last name. Now, if we were building a full fledged application, I would not put search code right here or even write in this form.

I'd have a little bit of separation here to kind of disconnect me from what I'm doing. I'm gonna put a little bit of separation in, but just don't forget this is a sample application. I'd have a little more complexity in here if this is a real application.

I would have broken out into a class library and tried to do everything I could in that class library. But in this case, I'm just going to say add class. Let's just call this data access. I'm going to get public. I'm going to create a method here called public list of person get people.

And I'll have it by last name. So you're going to pass in a last name, get back a list of person. So I'll call this. Let's just, so this throw new not implemented exception, what this does is allows us to compile the application because if you throw an exception, that kind of overrides whatever return type you needed. So a not implemented exception, what its job is, is to allow us to compile the application while we're working on it.

So I've kind of stubbed out this method here, but I'm not yet ready to put the code in. But I want to go back to my dashboard and call this so that I am ready to use it once it's actually implemented. So I come back over here to dashboard, and I can say, Data access DB equals new data access DB dot get people by and our text box here is called last name text. So last name text dot text.

That's the actual value of the text box. And so now we have our list of persons. So list of person. And actually, let's put that list of people up here. List of person.

People. equals new list of person just we have an empty list to start with and then we will say we're gonna override that with Whatever we get back from our lookup so with just a little bit of code what I've done is I've populated a list now naturally I tie this to our list box and then we'll come back to how to actually get data out of the database. It's like a branching thing we're doing here.

We're doing three things at once. This would be a little bit easier if you mapped out your application. I definitely encourage you to map out your application whenever you build one, map it out to make sure that you know all the different parts you need, because I would have built this and all the code before I ever even started even this. let alone the wire up okay so because i'm doing it kind of in line for you just to show you what i'm doing i'm not kind of doing it all at once i don't want to get that to be too confusing but the same time i don't want it to be like i'm doing a little over here a little over there and all of a sudden boom magic happens and it all works so that's why it's kind of a branching path i'm gonna do to get both the front and the back end done at the same time so we're gonna come back to Actually wiring up how to get the list of person In just a minute, but in the meantime, I'm going to wire up.

What do I do with that list of person? so the first thing to do is Connect our list box, which is called people found list box So people found list box dot data source Equals people now. This has got a little bit easier since the earlier days of C sharp it used to be that in order to connect to a list you would need to have a Binding source sit between the list and a list box, but now it's become a little bit easier We can actually connect directly using this just data source equals the list so the only thing we need to do is say People found this box dot display member equals and then give it one property name now If we look at our person class there isn't really a property that kind of Encapsulates all the information you want to display about the person so let's actually create a new property Say prop full and call it a string property and I will call it, let's call it a full info.

And then actually I'm gonna delete the private backing field. And I'm gonna delete the set. This is just read only.

And I expand out this get. And instead of returning the my var, I'm gonna say dollar sign. and double quotes.

Now I can say inside there curly brace first name and curly brace space curly brace space last name and curly brace. And then I'll do me a space and a parens and then a curly brace and say email address and curly brace. and i'll end the parens and so what that'll do is it will return the user in the format of say tim corey test at test.com like that okay without the quotes so that's the format of the return our entry in now we don't have phone number as well we could put that if you wanted to but i think this is good enough So the full info property is a read only property that just reads all the properties here and takes bits and pieces from different ones to make a strain that represents that user. So back over here inside of double quotes for the display member, we're going to paste full info.

And I do paste that there because a copy and paste is a whole lot easier. Oops. It's over here Copying from here and then pasting it over into here is a whole lot easier and simpler Than try to type it out and make sure there's no typos because it is inside of Quotes there is no intellisense and so whatever you type here is gonna be accepted but not necessarily work so the final thing we need to do is we need to hook up the data access itself this right here and once you do that it should be that whenever we type a name in here and hit search that it populates a list of people that it finds so let's do that let's go back here to our get people and actually take this out and talk to sql server and so this is the part where you actually connect to sql server Now to talk to SQL, we first need to add a reference to Dapr. So on references over here on the right, I right click and say manage NuGet packages. Now NuGet is a tool by which we can download third party and Microsoft libraries and do so in a way that allows us to also update them automatically or very easily to click a button.

So under Browse, I type Search and I start typing Dapper. And the very first one is called Dapper. And it's by Sam Saffron, Mark Gravel, and Nick Craver. So make sure you get this one, not other dappers.

There's other kind of like add-ins for it. But this one right here is the official one. And it will connect to not only SQL Server, but MySQL, SQLite, and others. And the current stable release is 1.50.2. And if you really want it, so you can go back in time and connect to...

even the or any other versions since then. But in this case, the latest and greatest stable version is great. So I hit install and it's going to come down here and look for what do I need, and then it's going to start installing Dapr for us.

and that's it so now dapper has been installed notice it says i can uninstall it or update it to a different version which in this case would actually be a backwards update it's actually reverting back to a previous version if you wanted to but if there's a new version that update would take us to that new version number so now i can add a using statement up here using dapper Then inside of my get people I Start with a using statement Now up here using statements are different than using statements inside my code So you using statement inside my code allows me to call some code Like with a connection and it says as soon as you're done the using statements Destroy that connection And so using statements is very helpful for making sure we don't leave connections open to our server. All right. So I'm going to say IDB connection. Control dot there to add a using system dot data. I'm going to say connection equals new SQL client dot SQL connection.

And then inside here I need my connection string. Now let's fix this first. Let's change it to system.data.sqlclient. So inside of this right here, I need my connection string.

So this is that string we had back here in app.config, this right here. So how do we get that? Well, we say helper.cnnVal for sample DB.

All right, so that will give us our connection string. Then all we need to do is add our curly braces. And these curly braces tell us where the start and finish of this using statement is. So what have we done so far? Well, we have created a new connection, this connection right here.

We've created a new connection to our SQL database. It really was that simple. And if you kind of blink, you missed it. Now, is this a little complicated to remember?

Sure, it's not the simplest string to remember, but don't worry. Just copy and paste this template right here every time you do it. Okay, kind of keep this in a text file somewhere or something like that. I go back and refer to previous projects, whatever.

And I say, okay, here's the way I connect to my SQL server. So and this just creates a connection to our SQL Server. It just says we've opened the door and now when you ask for information, you can get it back.

And that's all going to happen inside these curly braces. Because as soon as this curly brace gets hit, the door gets closed again. It's very, very important to close those doors when you're talking about SQL connections.

Open connections in the past have been a huge issue. where your application will actually start slowing down and your SQL Server bogged down, you're not quite sure why, and a reboot fixes things, and you're like, I'm not sure what's happening. Well, it's probably open connections that were just never killed properly.

So this using statement right here makes all that go away because it automatically closes when it gets here. All right, so how do we actually talk to SQL Server? Well, connection.query.

All right, so connection.query says I want to ask for data back. all right and what kind of data well i want person data back now that's my model remember that's got a first name last name email address and phone number just like my table has now we need to give it command how do we get person information well let's say select star from people where last name equals and then we'll put actually we'll put the dollar sign in front of this equals and we'll put single quotes around this in the middle of single quotes we'll put last name now this is not how i recommend you do it but this is the first step to doing this. This is the easiest way or the most simplest way to understand how to do this. Now what this is doing is saying, okay, take the connection you have open already, ask for, get data back, this type of data, here's my command, select star from people where last name equals and the last name is given.

Now this returns a IEnumerable of type Person. We want to list back. So we can't just say return because it's the wrong type.

But at the very end of this, after the closing paren, we can say.toList. And that gives us all the information we need to connect to our database and ask for information and then return it from our method. And before we move on, the one thing I just noticed is I'm missing a...

close paren up here you notice it was yelling at me right here it's giving me a red squiggly which i might have missed but notice it says closed curly brace expected which is an interesting error so what it really means is counter parens Okay, so I had one open paren here, I had one here, and I had one here. Therefore, I need one, two, three over here to close them all out. So that's just what that error is all about. So at this point, barring any typos or other issues, we should have a working application that asks for data from our SQL database.

Seems kind of hard to believe. There's not a whole lot of code here, but let's take a peek. So let's run this. Here we go. Last name.

So let's put in green. Hit search. See what happens.

Nothing happens. Excellent. Let's find out why. All right, so to debug this, what I'm going to do is put my breakpoint right here on the return. And this is where it's going to return my actual data.

Now, I'm pretty sure what the problem is. But if I didn't or if I wasn't quite sure, I actually break this line up into two lines. One would be to put this information into a variable. In fact, let's do that just to show you what I'd do. So I would say, whoops, got to stop our code first.

I would say var output equals and then down here say return output. Now all that does is puts the list in a variable. I know this is a type var, which means basically whatever you give me, I'm going to create that strong type.

So it's going to create a list of person. So it's going to be. But var allows me to just say var and then it fills in the right type for me. I won't be able to change the type once I create it, but this makes it easier. So now if I hit the breakpoint down here, it's captioning the information into this variable right here called output.

And then next step is returning output. That way I know what the data is before it gets sent back. So let's start this again.

I'll move my form back over. I'll type in just green again. Always repeat your same exact test over again, just in case part of the issue was green.

There is no greens or something like that. So I hit search and it goes right to my breakpoint and mouse over output. And if you can see there, it's kind of small. I can zoom in just let's zoom right in and now output count two.

Notice that it has Bertha green and also Clementine green. So we do have information coming back, at least to this point. So if we step over this code, let's just step into, and it comes back to where it got called, and the calling is db.getPeople. okay and it puts it into the people count notice also that before i do this next step people has a count of zero meaning there's no people in the person list so if i now step into now people has two people in it that's again bertha green and clementine green so if i hit continue Why isn't anybody in this list? Well, that's because we need to reset our bindings now that we have added or changed the list.

So people found list box dot reset. Oops, not reset bindings. Reset bindings is if you actually have that connector, that binding source. So instead, we'll need to actually do a refresh of our data source. Or we might be able to get away with just doing a.refresh.

Let's find out first. Okay. No, I don't know everything off the top of my head, and that's okay. I don't have to.

all right let's go ahead and um uncheck this uh breakpoint we don't need that right now we know it works and nothing's happening so i'm assuming that that refresh isn't going to work but you know what i do know that that i can do this i'll just paste it in and that's just a repaste of what's right up there let's see if that works first and then we'll make this a little prettier so i'll do this again the same thing again and now we actually have people popping in the list which is great and actually we need to make this list bigger because it's it's just not wide enough so let's let's do that first that's an easy one to fix make that a little more hearty We can actually shrink it up some too because we don't need to have a ton in that list because we're not going to find more than two or three the same last name I don't think. So this right here, this code assigns it initially and we have to refresh the data source every time we have an update to our list. So let's create a private void update binding. And here we put that same code and then instead of the code every spot we just say update binding This code and put everywhere So now we're calling it from multiple locations and we only have this code in one spot That way if we ever need to change that code is something a little different.

We change it in one spot not three let's just make sure this all works let's do farmer and there's three farmers patience buckminster and eliana and again just make sure green works again there we go notice how fast that's working that's actually pulling data from my actual sql server and it's putting it right into my list box Now this is what kind of confuses some people in the fact that it's just so simple. Really the data access to talk to our SQL server is just this. Now we did do the helper here for getting the connection string.

we could have taken that out and actually had the the connection string look up right in there we made a little longer a little more complex and i like this a little better but this right here is all there is to talking to our sql server now we are going to get a little more complex in the fact that this right here is not good practice this is straight sql And the problem here is we're susceptible to SQL injection. And if you're not already cringing when you hear SQL injection, go look it up. SQL injection is a bad thing. Essentially what it means is you can give a person on the front end access to do terrible things to your database. You don't want to give a person access to do terrible things to your database.

So you want to be a little more secure than that. First step to this is always make sure that you are secure. looking at the data coming in. Obviously not physically because this is what an application does, but instead making sure that you filter out certain characters or limit lengths of names and other things.

Make sure you drop off certain code or anything else like that that would be a problem. But the other thing here is, and this is what I always recommend, is use store procedures. Store procedures are so much more robust, more powerful, and it'll allow us to do a whole lot more.

So how do we change this over to a store procedure? Well, it's actually pretty simple. I'm going to actually wipe this out and start over. I'm going to say, you know what?

I'm going to copy this line. I'm going to give you a source code in my blog so you can have it. So I'm going to actually comment this out just so you can see how to call.

just raw SQL if you ever needed to. But again, I don't recommend it as the first choice. There are some times when it's useful, but by and large, try and stay away from it, especially when you do this string concatenation thing that could really be a problem.

All right. So inside my double quotes here, instead of putting my straight SQL, I'm going to put my dbo.person, I didn't forget the name of it. Let's go ahead and look back here. I'm sorry, it's people. That's what it is, people get by last name.

All right, so actually copy that. This is my store procedure for looking up people. All right, and it takes in one parameter. and again if you're not familiar with sql um parameters essentially you have to be named at and then the name of it so in this case at last name and i'll show you that right here let's modify a sql server just start procedure it takes an at last name and it has a envar chart length of 50 and that just says select star from people where last name equals last name looks kind of familiar that's essentially the same code that we did raw We're now going to do inside of a store procedure.

So we have to pass in this last name. Well, how do you pass in a last name? Well, this is where we create a new dynamic class. So we say new and then open curly brace. Say last name equals last name.

All right. And then close curly brace. Now, what did I just do there? Well, I create a new class, a new class instance of a class that doesn't exist.

This is a dynamic class. All right. So this is C sharp being wonderful. So I did what I said, okay, I want a new class instance. And it's going to have a property called last name.

Notice how this matches up to this except for the at symbol. And then I said the value is going to be last name. Notice how this is matched up with our variable name. So the variable name last name is being passed in.

And I have said, okay, that's the last name I want to look up. That goes into this, which, and here's a little bit of magic that Dapper does. It will take this property and put it into.

this spot right here safely. Now we're going to call this store procedure, passing in these parameters and we're matching them up using this dynamic class. The only changes I've made.

Let's run this again and let's look for a farmer again. Same thing. Works just great. So the difference is that I'm calling my store procedure instead of calling my direct SQL.

So whenever possible, I recommend that you do the store procedure route, not the direct SQL route. And it is very simple to do. as long as you have store procedures or can create them store procedures are not that hard to do so essentially all it is is you say create instead of alter create procedure give it a name make sure you put a dbo dot in front of it don't remember the square brackets here that's what sql server adds i don't find them very useful but dbo dot and then the store procedure name Any variables and then as you have begin and end inside there just have your select statement.

This set no count on what it does is it says okay don't return how many rows you returned. There's not really a need for that. So set no count on just returns less information less data across the wire. So, and that's my, my speed version of what is a store procedure and how do you create one?

All right. You can right click on these and say script as create to, and it will create the create for you. And then you just modify this in here and modify a name.

You're good to go. So it can create the structure for you, or you can right click on store procedure, say new store proc. And then it gives you this, which. quite frankly is way too busy for me but this up here you can get rid of you can really get rid of this too if you want but this name box you can get rid of use something i would say but you can get rid of that you can change the name right here add your parameters and get rid of the rest of this and then just get rid of this and put in your own statement so the It's helpful to do that. It gives you the structure, but really a store procedure is pretty simple to build.

Again, if you really want to learn more, I do have that course about seven hours worth on how to start in the SQL all the way through store procedures. We have a store procedure now that gets by last name, the person. displays that inside of our our list box now that's one direction we've got information out of sequel okay so we've we've called it we said give me all the information put it into a person class now I kind of skipped over that for a little bit let's come back to that from it so when we say call this store procedure Put it in the person class. What does that really do? Well, it says I have data that comes back and so what used to happen is we access our SQL server we'd say give me data and return a data table and inside a data table We had rows and columns and we had to kind of match up what the data was and how to use it actually to kind of go through line by line or row by row and take the data out of the table and put it into a model of some kind and Then put that model into a list of model and then finally return that this line right here Does all that for you?

It takes the return data and it goes and opens up the person class and this is okay i have five properties i can use five read write properties id first name last name email address and phone number so i'll take my my first row and see okay what columns do i have and if i have an id column i'll put that value in this property and if i have a first name value column i'll put that value in this property and so on and so forth down the list When it's done, it loops through the next record and does the same exact thing. And it keeps creating new instances of type person and puts it in a list for us. So it does all this work for us behind the scenes.

And essentially, it's just matching up name for name. So what that means is if you don't include, say if we didn't include phone number. our model everything would still work the matchup would still work it would just say I have extra data that don't have a place to put therefore I just delete it get rid of it in the same way if we have properties in here that we don't have columns for in our sequel table it'll ignore them and it's leaving us null so it's really robust And some people will say, well, but I want to name things differently in this model.

And my encouragement to you is don't. You can. And if you go to Adapters documentation, and I'll have a link to that in the blog.

If you go to Adapters documentation, they will show you how to map a different property name to a column name in SQL. Just don't. It's a whole lot easier to have a list that matches exactly with your SQL. Then if you want to change that or morph that into something else you take that list and create a new model and Put the data over from this model into that model.

Okay, this this is a data model just Pretend like it's actually sequel don't touch it make it the same as sequel and then it's use it that way And then if you need to change something change something later with a different model, so that's just my app my two cents there you could go crazy and change things and that's fine it just makes it much more complex and quite frankly i am just enamored by the fact that in two lines really in one line well two lines this line right here in this line right here in two lines i can open up a connection to sql get data out of it and close that connection that's just beautiful Especially since I'm getting back then a list of type person or a list of my model. Because I can work with that. In fact, here in the dashboard, I am working with that.

I'm taking that list, putting it right into my list for my form, and displaying it in my list box. So I can use that very, very easily. So now let's switch gears and go to how to put information into our SQL database.

So let's create a new section down here. We're going to open this up. I'm actually going to copy and paste this. I have a naming issue because that last name is going to be the same as above so I have to change that to something else alright so I have my four values here my first name last name email address and phone number so let's go ahead and start naming these pin this now I'll call this first name let's call it first name ints label for insert and then last name ints label and email address ints label and phone number ints label And this will be the first name, I-N-S, text.

Last name, I-N-S, text. We call it email or email address. Email address. So email, address, I-N-S, text.

and phone number INS text. Now we're going to name these boxes, the actual values in there. So we'll say first name, email address, and phone number.

I'll move these boxes over so you can actually have some. alignment there we go so now we have our first name last name email address and phone number we'll create a button down here well let's just copy the same button let's there we go i'll copy this button so it's the same size something like that and instead of search we're going to say insert we'll call this the insert record button and we're going to double click on it and here we're going to do the same kind of thing we're going to say data access db equals new data access and then we're going to say Just db.insertPerson. We'll give him the first name, insert.text.text.

Now, this method doesn't exist yet. We're going to create this in just a minute. So I'm just going to kind of stub it out as to what it's going to look like.

So last name, ins.text.text. Email address, text.text. and phone number text dot text and that's really all there is to it so with these unpinned you can see that now this doesn't exist yet I could drop down and say generate method if I do that notice inside data access it's created as internal void insert person or something that's public Void insert person now, let's call it text one text to text three and text four which is just awful so first name Last name Email address and phone number No spaces, okay, I notice the throw new not implemented exception Since I had the system auto create it for me, it puts that throw new not implemented exception there just so we can compile again, but it allows me to keep working where I was. So I could have just kept going here if I had to do more work, but that's all the work I need to do. The last thing I'd probably do here though is let's just, let's go ahead and set all these text fields to be empty.

That way they're all cleared out. Now we can also tell we've done something. Now over here, we need to do our same using statements. I'm actually just kind of copy this. Remember I said that copy and paste right here is your friend.

and the fact that I have to remember this whole string here. But instead of doing the connection.query, I'm going to do things a little bit differently this time. Now, there's a couple ways we can do this.

First thing we need to do is get together our data to be inserted. Now, we have the first name, last name, email address, and phone number. But the way that this works is I can insert one row, or 50 rows.

So we could do things a little bit differently, but in this case what I'm going to do, I'm going to create a Person class, a Person class instance. So new Person equals new Person. And then I will say, now I could do this in two different ways, I could say equals new Person actually.

Stub out first name last name email phone number right here. In fact, I'll probably do that Make it simple All right, oops crate brace So what I did here, let's just expand this here. It's a little large, and I can make that a little smaller by stacking it a little better. But in one line, I said, here's my variable I want to create. It's a person variable called newPerson.

Equals newPerson, but instead of the open and close paren, I put open and close curly braces. Inside there, I said, here's the values for the properties. The first name property, the value is first name, which is what comes from our pasting value.

The last name property, the value is last name, also comes from pasting value. Email address, you get the picture. Phone number, phone number.

So I could do that, or I could have just said open equals new person, open close parens, and then said new person dot first name, you know, new person dot first name equals first name. And then next line, new person dot last name equals last name and all the rest. So I can either have, you know, four or five lines down here, or I can have it all in one line right there. It's really up to you.

But that's, that's what we have. Now, because the insert is actually expecting more than one record, now, zero or more, or actually, I'm sorry, one or more records. So what you do is create a list of person called people equals new list of person.

And then I'll say people.add new person. All right, now if I want to make things even simpler, I could take this code right here, copy that, and paste it in place of here. Then I don't even need this variable.

So let's go ahead and do that. I'm going to take this and I copy it, but then I'll comment this out. I'm going to paste the code right in line. So without even assigning this instance, right here, this instance right here, without assigning it first to a variable and then putting it into my list, I'm directly putting it into my list. Just makes things quick and easy and I don't need to worry about anything else.

It's not a list that encompasses one person. I insert that person into the database. How do I do that? Connection.

execute I Need my my store procedure DBO dots People leave it's in search. We'll check that in a minute But then I have to pass in for this store procedure at the pass in four variables at first name at Oops. At last name.

Notice a comma separation. At email address. At phone number.

And then how do I fill that in? Well, I just say people. If I can spell it right.

Because people is a list of person. And so I said, okay, in there, in each instance, each person, I need to find four different properties. And it says, okay, first name property. What's the value of that?

It pulls it in, puts it there. Last name, same thing, email address, and phone number. fills in those four values, calls the insert statement, and does it for every single record we have in this list, which means that we could just keep adding people to the list, or in our front end, I could have a running list.

Say if I just, when I hit insert, it added it to this list up here. And then when I say I'm totally done, it takes this entire list, passes it over to data access for the insert, and says insert them all. And it would do that. Now, again, let's go ahead and check to see if this works.

It's always fun to write code and then see if it actually makes a difference. Now, first, let's verify if there's any Corys in the database. I hit search. I get nothing back.

So, timcorytestattest.com, and phone number is... 570 555 1212 sounds good. I hit insert now has it cleared all those out.

Now if I hit search for Corey I actually have one record coming back from the database. Now, let's just verify that works by saying James Corey, Jimmy at test.com, and phone number is, let's put in a 212 area code, because why not, but it's still 555-1234. We insert James, and now if I do a search for Corey, I have two records in the database, test and.

Jimmy, bolt that test.com. So that's all there is to insert. Okay.

So instead of doing a connection dot query, we say. connection.execute. Same store procedure.

Notice we don't pass in what for query we had to pass in what type because that's the information coming out. But for execute we don't have to pass in the type because we already know that based upon what type we're sending in and we're not actually getting data back out of this. Now we do the call to our store procedure. We pass in the four parameters which happen to line up for good reason they line up with our our person class properties so when we pass in one person it executes the store procedure puts that person in the database okay so that's all there is to adding people or inserting records into the database this could also work for updates it's the same call it's just whatever your store procedure does what's update versus add versus delete versus whatever okay so really that's all there is to reading and writing from sequel server it's pretty simple this including code comments that I made that's all the code you need to talk to a sequel server get information out of it and then put information into it as well and from the front end perspective it's really easy to call these because since this is just kind of hiding everything the reality is when i ask for git people i'm just getting a list of people or a list of person back and so i can deal with that i've been doing that for time you know in my videos i always deal with a list of t a list of person a list of whatever And people ask me, well, how do I get information in and out of the database?

The reality is you're working with this list of T all the time because there's one method somewhere like this that says get the information out of the database and put it into a list of T or a list of person, whatever. And then, you know, how do I get information back in the database? No problem.

You have something like this where it takes your. List of people or whoever it is and it puts that information into the database and you may say well What do I know? How do I know if it's an insert versus an update versus delete?

Well, that's something that your merge statement can handle inside of sequel the merge statement allows you to do an insert and an update and delete depending on how you What data you pass in? So, but that's really something more for once you're more familiar with SQL. In the meantime, you create separate store procedures for insert versus update.

So, and if you have any more questions about SQL itself, like I said, I've got that video series, um, that takes you through all of that. Um, it's not something that I'm going to necessarily create a whole bunch more videos on because I really do cover from start to finish in my course. So from a C sharp side of things, Dapper is your friend.

It's a great tool. I recommend it highly. It's heavily backed by a company that's not going anywhere.

And so it's not something where. you know next week or next month that's going to be gone it's it's here to stay and this makes things really easy this this code right here can't be much simpler for accessing the database So with that being said, definitely let me know down below in the comments what your thoughts are. If you have any questions, please post them down below and I'll try to get back to you and answer them as soon as possible.

Also, make sure to subscribe. People who are on my mailing list, which is also linked down below and also in my blog post. People who are on my mailing list do get discounts and early notifications of upcoming courses. Upcoming content and they actually get more content as well So make sure you subscribe to my mailing list and when you do you go get an email it says hey, let me know Of any information you have that you all let me know right at the bat You know any questions you have those type of things and it's gonna ask it's gonna tell you I'm a real person Respond and you'll find out every person who? Joins my mailing list and emails me gets an email back personalized for me Not a form letter.

They're going to get a real email. If you email me, I will email you. So with that being said, that's all there is for this video. I appreciate you watching. I appreciate you subscribing.

And I appreciate all you guys do to encourage me. I'm definitely coming out with more videos. I've got a start to finish series coming out soon on C Sharp. So definitely look out for that.

All right. Thank you very much. Have a great day.

Transcript for:Connecting to SQL Server with Dapper

Transcript for:
Connecting to SQL Server with Dapper