How to connect C# to SQL (the easy way)

welcome to the C sharp data access series my name is Tim quarry and today we're going to connect to a sequel server database now sometimes you may look at a sequel server database and say man that was complicated I'd love to do it I love the information in a bit 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 so that you don't have to worry about did access did it access will become the least of your concerns when it comes to your application 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 sequel server initially I always done direct a do net and what that is is just a direct connection using just c-sharp and it's pretty you know foundation or core and that works and it works well but it's a little complicated now is 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 are some really powerful things to that it makes it work very 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 did access and so you can dive really deep and there's some really smart people of Gaunt the end of the deep end into entity framework they've learned a ton of stuff that 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 is working great they put an application into production and then two weeks on the road something blows up and they spend hours trying to debug what's happening inside the black box you see the 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 pain that I don't use entity framework I really avoid whatever 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 direct a do net connections for a long time but again it's a little complex so you have a little complex on one side but I control everything to the very complex and the other side and I don't really have a whole lot of control and that's any 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 they really think it 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 people who do Stack Overflow if you're not familiar Stack Overflow is a massive website for tech help on the internet and today actually built dapper to help them access their database their sequel database and so they actually open-source this and gave away for free and they support us 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 nougat packages because it adds a dependency every time you do something but dapper adds enough value while having small enough overhead that 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 size this demo to start right from scratch that way you are comfortable 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 I am Tim quarry calm 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 and flow solution name we'll call it sequel server I'll just call it sequel beta access demo okay 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 use this as both our our data input and our data display that way it is kind of simple this is not a production application this is just to show off what we can do with dapper little bigger I'll actually mess the property just a bit just because I want clean up and also change the font size so it's a little more readable on this screen we've 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 got people and then I have to store procedures one called people on a score get by last name and one called people underscore insert so the people table I'll just select top 1000 that gives us I've pre-populated with dummy data from the internet okay is there's tools out there that can generate insert scripts for you just to put some don't we data to cough it's you know so I said give me first names give me last names give me email addresses give phone numbers from where and how doesn't 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 to match that last name and then the people insert simply adds new person into the people table so that's our database that's we're going to 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 and I 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 and I get public then we'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 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 okay prop int ID prop string first name prop string last name prop string email address prop string phone number okay 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 so over here my table I have an inch for ID and the rest are some type of string I have n VAR n varchar' 50 and varchar' 50 at n bar char 100 and then of our chart 20 okay so those are the four different types of string fields I have now if ifs equal kind of freaking out don't worry about it if you're talking about accessing a database you already have not a big deal we'll walk you through the basics but if you really want to go further into what sequel is and how to build a database and all that kind of stuff I do have a course for that the purposes videos not the pitch that course but but it is kind of a nice dovetail so I do have a course on I am Tim quarry calm 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 all the rest so that might be something that would interest you so but the basics here are inte 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 match we're crab model and it matches up to our table it's all you really need to worry about right now the next thing I'm going to do is I am going to set up a connection string helper now a connection string which we're going to put in our app config here so there's no connection string yet but we have a connection string I'm gonna want to pull information out of this app config so that I can read how it talks to the database so I create a helper for that just because it's a little bit it's a bit longer of a string and what makes them simpler so let's add a new class we're just going to call this helper CS or is helper I'll make its a public static class I place that class helper I make a public static string CNN string or CNN Val puts en Val I'll say string name and so what it's going to do is you say helper dot CNN Val and give it a name it's going to look up which connection string to get out of my app that config and return that value and a way to get a connection string is to say configuration manager dot connection strings then put in square brackets the name of the connection strain this case or call it name that's a variable right here and then dot connection string and will actually return this 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 and reason why so you have to add a reference so right click on references say add reference rest search right now in frameworks you mean frameworks extension a recent but just an upper right-hand corner where it says search and type configuration to start typing it and you'll give you three options probably maybe two what you're looking for system dot configuration this is baked right into c-sharp it just isn't enabled by default so we check the box method system dot configuration not the one below it the system configuration install the system configuration check the box say ok now it's still hello red squiggly but this time if we do the control dot while highlighting it somewhere so my cursor somewhere on configuration manager where the rate is a 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 c NN val and pass the name of our configuration string it will look to our app config for that connection string now next thing you need to do is actually add our connection string and 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 sample DB I think I'll call it sample DB even though my database name is called sample it just makes a little more clear and the connection string itself now this may be a big question mark especially after you see kind of complexity what it could look like but there is an easier way here I'm always about an easier way so if you go to connection strings comm and click on the link for sequel server you'll get this page right here connected strings comas 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 were important and paste it into your code so in this case I'm gonna use a trusted connection versus standard security connection this is all for sequel server so the difference between standard security and trusted connection is that in standard security I'm passing a user ID and password I don't have to do that because my sequel server has authorized my windows login as an authorized user therefore I can say trust to the connection and I say trust the connection it says use your windows credentials doesn't pass in your password write that it says if you're logged in as Tim then you have Tim's access whatever that is so I use this right here it's going to copy this whole string I'm going to paste it right inside my connection string now things that need to change are my server name notice typically is just dot dot means localhost it means your current machine so if you have sequel server rain locally that's what you do now in my case I have more than one instance of sequel server running therefore mine is dot slash sequel 2016 and that's because they have two different versions of sequel running right now one with 2014 and one 2016 so this is the instance of on connect to my database this is the default database that you're connecting to when you connect to the server as long as you have access you can access any database you want but you'd have to access with the full database string name so for example if you want access the people table and you were in the my database instead of the sample database you'd have to say sample DB DB o dot 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 sequel 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 sequel client and then it says what kind of connection string it is is this equal connection string and the reason why it's important is because these connection strings don't have to connect just a sequel server they can connect to Excel they can connect to my sequel to you know sequel light there's a whole list of different things that can connect you so it just says we're connecting the sequel server alright so there's our connection string so now we want to access this whole thing right here yes this is the the real important piece that's the data we need so we're connect to sequel server we have to have this string we're going to get this string by passing in just a sample DB name to our helper dot CNN Val so in here we're going to put sample DB and so that 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 just pasted this wherever I needed it well obviously the first reason is because it would then be hard code in your application you couldn't change it without recompiling least but the other issue here is that you don't want to put into your source control server say get now you're storing your data on github you wouldn't want to put in a github any kind of login name and password and so you'd want to use a trusted connection and not pro login and password here when you're committing it's the 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 we actually have multiple different connection strings so is 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 and so they change based upon the build that we do now 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 there's a 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 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 got things kind of wired up over here now let's create something where we can actually display data so I'm 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 properties the first I'm going to do is change the name going to say let's call this the people found list let's call it list box just because I don't want to confuse with just 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 as we can see yes we found them and now let's add let's actually pin that's for now just like to easier I'm going to add a text box right here I call this I'll pin this one as well I call this the last name text and I will give it a label as well this we have it I'll call this last name label and then scroll down to the text itself and say just last name alright so there's a last name and wrap it up a little bit and put in a button this button kind of centered here like that it will 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 actually go to a database it's going to find the people that match that last name and can 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 on a store click event now unpin owes now so in here we're going to call out to our database and search for just this last name now you if we are building a full-fledged application I would not put search code right here or even write in this form I have a little bit of separation here to kind of disconnect me from what I'm doing I'm going to put a little bit of separation in but just don't forget this is a sample application I had a little more complexity in here if it's a real application I've broken out into a class library and try to every day quit in that class library but in this case I'm just going to say add class let's just call this data access I get public and create a method here called public list of person get people now 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 implement exception what this does is allows us to compile the application because if you thrown out an exception that kind of overrides would ever return type you need it so not implement exception what its job is is to allow us to compile the application while we're working on it so I 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 come back over here a dashboard and I can say data access DB equals new to access DB dodge get people by and our text box here is call 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 person 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 override that with whatever you get back from our lookup so with just a little bit of code what I've done is I've populated a list now that we're going to tie this to our list box and then we'll come back to how to actually get data out of the database so it's kind of got branching thing we're doing here we're doing kind of three things at once this would be a little bit easier if you mapped out your application and I definitely encourage you a map at 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 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 like it that to be too confusing but the same time don't want it to be like I'm doing a little over here a little over there all sudden boom magic happens and it all works so that's why it's kind of a branching path I'm going to do to get both the front and back end done at the same time so we're going to come back to actually wearing 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 so it's 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 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 a source equals the list so the only thing we need to do is say people found this box dot display member equals and then get it one property name now if we look at our person class there isn't Li a property that kind of encapsulates all the information while I display about the person so let's actually create a new property B prop full I call a string property and I will call it let's call it a full info I'm actually going to delete the private backing field and I delete the set this is just read-only and I expand out this yet instead of returning the my farm let's say dollar sign and double quotes now I can say inside there curly brace first name the creative raised space curly-brace space last name and career ace now I'll do me a space and a parens and then a curly brace and say email address and Crace now in the parens and so what that will do is it will return the user in the format of say tim corey test at test comm like that okay without the quotes so that's the formatted return our entry and now we don't have phone number as well we could put that he wanted to but i think this is good enough so the full info property is a really property that just reads all the properties here and takes bits and pieces from different ones to make a string that represents that user so back over here inside of double quotes for the display member we're just going to paste full info I do paste that there because a copy and paste is a whole lot easier if it's over here copying from here and then pasting it over into here is a whole lot easier and simpler then try to type it out and make sure there's no typos because it is inside of quotes there is no intellisense and so ever you type here it's going accepted but not necessarily work so finally we need to do is we need to hook up the data access itself this right here and want to do that it should be whenever we type a name in here and hit search that it populates a list of people it finds so let's do that let's go back here to our get people and actually take this out and talk to sequel server and so this is the part we actually connect to sequel server talk of sequel we first need to add a reference to damper so on references over here on the right I right click and say manage new hit packages now newt is a tool by which we can download third-party and microsoft libraries and do so in a way that allows it to also update them automatically or very easily the click of a button so in under browse I type search and 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 dapper ziz other cally add-ins for it but this one right here is the official one and it will connect to not only sequel server but my sequel sequel Lite and others and the current stable release is 1.5 0.2 and if you really wanted to go back in time and connect to even though 1.0 or any other versions since then but in this case the latest and greatest stable version is great so I hit install and to come down here and look for what do I need and then it's going to start installing dapper for us and that's it so now adapter has been installed notice it says like an on-site or update to a different version which in this case was actually a backwards updates actually reverting back to a previous versions 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 user thing up here using dapper and inside of my get people I start with a using statement now up here using statements are different than using statements inside my coat so you can see the inside code allows me to call some code like with a connection and it said as soon as you're done the using statement destroy that connection and so using so it's very helpful for making sure we don't leave connections open to our server all right so I'm going to say I DB connection and control dot there to add a using system dot data I'm going to say connection equals new sequel client dot sequel connection and then inside here I need my connection string now let's fix this first let's change the system dot data X equal client so inside of this right here I need my connection string so this is that string we had back here app config that's right here so how we get that well we say helper dot CNN Val for sample dB alright so they'll give us our connection string now we need to do is add our curly braces and these clear braces tell us where the start and finish does using statement is so what we've done so far well we have create new connection this connection right here to create a new connection to our sequel database it really was that simple if you kind of blink you miss it now is this a little complicated remember sure it's not a simple a string remember but don't worry just copy and paste this template right here every time you do it okay kind of keep this note in a text file somewhere or something like that I'll go back and refer to previous projects whatever I say okay here's my the way I connect to my sequel server so and this just creates a connection to our sequel 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 so very very important to close those doors when you're talking about sequel connections open connections in the past have been a huge issue where where your application elastics are slowing down and your sequel server bogged down you're not quite sure why in a reboot fixes things you're like I'm not sure what's happening well it's probably open connections they 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 we actually talk to sequel server well connection dot not kind of string connection dot query all right so connection that query says I want to ask for data back alright and what can I data well I want person data back now that's my model that's got a first name last name email address and phone number just like my table has now we need to give it commands how it gets person information well let's say select star from people where last name equals and then we'll put a she will put the dollar sign in front of this equals and we'll put single quotes around this in the middle of single quotes will put last name now this is not how I recommend you do it but it's the first step to doing this this is the first the easiest way would most simply to understand how to do this now what this is doing it is saying okay take the connection you have open already asked for get it it back this type of data here's my command select star from people where last name equals and the last name was given now it returns a enumerable of type person we want to lift back so we can't just say return because it's the wrong type alright but at the very end of this after the closing Parente dot to list 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 a free move on the one thing I just notice is I'm missing a close Corinne up here you know it's yelling at me right here getting 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 air 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 sequel database seems kind of hard to believe is not a whole lot of code here but 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 excellence let's find out why all right so debug this I'm going to do is put a breakpoint right here on the return and this is where it can return my actual data now I'm pretty sure that the problem is but if I didn't or wasn't quite sure I actually break this line up into two lines one would be a put this information into a variable in fact let's do that just to show you what I do so I would say whoops guys stop our code first I would say var outputs equals and then down here say return output now all that does is puts the lists in a variable I know it's the type var which means basically whatever you give me I'm 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 that's makes it easier so now if I hit the breakpoint down here it's capturing 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 tests over again just in case part of the issue was you know green there is no green - something like that so get search and it goes right to my breakpoint and mouse over output and as you can see there it's kind of small I can zoom in just that's zoom right in and now output count of to 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 that's a step in - and it comes back to where it get called and the calling is DB get people okay and it puts it into the people account notice also that before i do this next step people has a count of zero i mean 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 listbox dot reset oops not reset bindings reset bindings if you actually have a those that connect to that binding source so instead we'll need to actually do a refresh of our of our data source or you might go to get away with it just doing a dot 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 alright let's go ahead and uncheck this break point 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 repay stuff what's your up there let's see if that works first and then we'll make it a little prettier so this again same thing again and now we actually have people popping the list which is great next we need to make its 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 like that a little more hearty we can I can shrink it up some too because you don't need to have a ton netlist because we're not going to find more than two or three the same last name out think so this right here this code assigns 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 so click this code input 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 I'll just make sure this all works let's do framer and there's three farmers patients Buckminster and Elia and again it's make sure green works again there we go notice how fast that's working that's actually pulling data from my actual sequel server and it's putting it right into my list box now this is what kind of confusing some people in the fact that it's just so simple really the data access to talk to our sequel server is just this now we did do the helper here for K in the connection string we could have taken that out and actually had the the connection string lookup right in there we made a little longer little more complex and I like this a little better but this right here is all there is to talking to our sequel server now we are going to be a little more complex in the fact that this right here is not good practice there's a straight sequel and the problem here is we're susceptible to sequel injection now if you're not already cringing we hear sequel injection go look it up sequel 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 your database so you want to be a little more secure than that the first step to this is always make sure that you're looking at the data coming in obviously not physically because this is what an application does but instead making sure is 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 is what I always 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 many changes over to a store procedure well actually pretty simple going to actually wipe this out and start over I'm going to say you know what I'm gonna copy this line I give you a source code in my blog so you can have it so I'm going to actually comment that's out just again see how to call just raw sequel if you ever needed to but again I don't recommend it as the first choice there are some times when it's it's useful but by-and-large try and stay away from it especially do this like this string concatenation thing that could really be a problem alright so inside double quotes here instead of putting my straight sequel I'm going to put my DB o dots person I entry the name of it let's go ahead and look back here I'm sorry people that's what it is people get by last name alright so actually it's 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 to seek hwal parameters essentially has been 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 that sequel server restore procedure it takes an at last name and it has a n varchar' length of 50 and that just says select star from people where last name equals last name look on familiar that essentially the same code that we did it raw we're not 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 Plus will recreate a new dynamic class so we say new and then open curly brace say a 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 does exist this is a dynamic class all right so this is this is c-sharp being wonderful so I did what I said okay a new class instance and if you have a property called last name notice how this matches up to this except for the @ symbol and then I to 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 thing a lot of lookup that goes into this which and here's a little bit of magic dapper does it will take this property and put it into this spot right here safely so now we're going to call this store procedure passing in these parameters and are matching them up using this dynamic class so that's only the only changes I've made to run it again and let's look for a farmer again same thing works just great so the difference is that I'm call my store procedure instead of calling my direct sequel so whenever possible I recommend that you do the stored procedure route not the direct sequel route and it is very simple to do as long as you have store procedures or and create them slow procedures are not that hard to do so essentially all it is if you say creating this alter create procedure it was a name make sure you put a DB ODOT in front of it don't remit these square brackets here that's what sequel server adds I don't find them very useful but DB o dots and then the store procedure name and these variables and then as you know begin and end inside there just have your select statement this set no count on what does it says okay don't return how many rows you returned this is 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 stored procedure how you create one all right you can right click on these and say script as create two and it will create the create sent for you and they just modify dozen here in modify name you're good to go so they can create they 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 get rid of that's too if you want but this name box you can get rid of use something I would say but can get rid of that you can change the name right here add your parameters and get rid of rest of this and then just get rid of this and putting your own statement so the it's helpful to do that account you to the structure but really a store procedure is pretty simple to build and again if you really want to learn more I do have that collect course about seven hours worth on how to start in the sequel always through store procedures so have this procedure now that that gets by last name the person and 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 can't skipped over that a little bit let's come back to definite so when we say call this store procedure and 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 sequel server we'd say give me data and return a data table in inside 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 the tab go through line by line or row by row and take the data out of table and put it into a model of some kind and then put that model into a list of model and then find returned that this line right here does all that for you it takes the returned data and it goes and opens up the person class and it says okay I have five properties that can use five readwrite properties ID first name last name email address and phone number so I take my my first row and say 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 die 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 into the same next thing and it keeps creating new instances of type person inputs in the list for us so it does all this work force behind the scenes and essentially it's just matching up name for name so what that means is if you don't include you'd say we include phone number in our model everything would still work the matchup would still work it would just say I have extra data they don't have 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 you'll ignore them as leave as null so it's really robust and some people will say well by one name things differently in this model and my encouragement to you is dumped you can and if you go to add a pers documentation I'll have a link to that in the blog if you go add a pers documentation they will show you how to map a different property name to a column name in sequel just don't it's a whole lot easier to have a list that matches exactly with your sequel and then if you want to change that or more for 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 if you use it that way and then if you need to change something change something later with a different model so that just my two cents there you could go crazy and change things and that's fine it is makes it much more complex and quite frankly I am just enamored by the fact that in two lines we in one line well two lines this line right here in this line right here in two lines I can open up connection to sequel 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 could work with that and you know 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 let's switch gears and go to how to put information into our sequel database so let's create a new section down here we're to open this up actually I copy and paste this I have a naming issue because that last name is going to be the same as above so to change that 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 and I will call this first name let's call it first name in INF label for insert and then last name I NS label and email oops email address in label and phone number I NS label and this will be the first name IMS texts last name i NS text we call it email email address email address so email address I and s text and phone number IMS texts now we're needs boxes the actual values in there so they'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 I'll just copy the same button let's go on top is button so it's the same size so like that and to search we're uh say insert we call this the insert record button and we're going to double click on it and here we're going to do a 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 dot insert person give them 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's going to look like so last name is text dot text email address text dot text and phone number text dot text and that's with all there is to it so with these unpinned you can see that now this is things this yet I could drop down and say generate method if I do that notice inside due to access it created internal void insert person personally this public void insert person now 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 notice the throw new not implemented exception since I had the system auto create for me it puts that throw new not implement exception there just so you 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 would 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 nobody can often tell we've done something now over here we need to do our same using statements that I'm going to copy this remember I said to copy and paste right here is your friend in fact I'll remember this whole string here but instead of doing the connection query I do things a little bit differently this time now it's a couple ways you can do this first thing we need to do is get together our day is to the insert now we have the first name last name email it as a phone number but the way that this works is I can insert one row or d-rose so we could do things a little bit differently but in this case I'm going to do I'm going to create a person class person class instance so new person equals new person and then I will say now I could do a different way they could say equals new person and actually stub out first name last name email and phone number right here if I I'll probably do that make it simple all right whoops crate brace so what I did here I'll just expand this here it's a little large and I can make it a little smaller by stacking a little better but in one line I said here's my variable I'll create the person variable called new person equals new person but instead of the open closed paren I put open and closed 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 past in value the last name properties the value is last name also comes the pass and value email address you get the picture phone number phone number so I can do that or I could just said open equals new person open closed parens and then said new person that first name you know new person dot first name equals first name and then next line new person dot last names with last name all the rest so I either have you know four or five lines down here or I have it all in one line right there it's really up to you but that's that's where 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 we do is create a list of person called people equals new list of person and then I'll say people that add new person all right now if I want make things even simpler I could take this code right here copy that and paste it in places here then I don't even need this variable so let's go ahead and do that now take this I copy it down comment it's out and that paste the code right in 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 down the list that encompasses one person that inserts that person into the database how I do that connection dot execute I mean might my store procedure dbo dots people leave us in search we'll check that in a minute but then I have to pass in for this store proceed right to pass in for variables at first name at at last name nodes a common separation adds email address at phone number and then how do I fill that in well I just say people we can still it right because people is a list of person and so it okay in there in each instance each person I need to have find four different properties it says okay first name property what's the value of that it pulls it in puts it there last name same thing emails about some phone number fills in those four values call the insert statement and does it for every single record we have in this list which means that we could just keep adding people a list or in our friend I could have a running list say if I just next hit insert it added to this list up here and then when I say I'm totally done it takes its entire list pass it over to data access to the insert and says insert them all and it would do that now again let's go ahead and check the see this works so I just fun to write code then see if it actually makes a difference now first let's verify if there's any quarries in the database I hit search I get nothing back so Tim quarry test at test calm and phone number is five seven zero five five five one two one two sounds good I hit in search now is it clear the holes out now if I hit search for quarry I actually have one record coming back in my database now I'll just verify that works by saying James quarry Jimmy add test calm and phone number is let's put the two one two area code because why not play still five five five one two three four we insert James and now if I do a search for quarry I have two records in the database test and Jimmy bolt that test calm so that's all there is to insert okay so instead of doing a connection query we say connection dot execute same store procedure notice you 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 it into type because we already know that based upon what type are 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 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 is what's update versus add vs. 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 as well and from the front perspective it's really easy to call these because since this is just kind of hiding everything the reality is when I ask for get people I'm just getting a list of people or less a person 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 let's a person a list of whatever and people ask me well how I get information in out of 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 database and put it into a list of T or list of person whatever and then you know how you 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 well 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 state allows you to do an insert and an update and delete depending on how you what they did pass in so but that's really more for once you're more familiar with sequel in the meantime you can create separate store procedures for insert versus update so if you have any more questions about sequel itself like I said I've got that video series that takes you through all of that it's not something I'm going to say creates 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 it's not going anywhere and so it's not something where you know next week your next month that's going to be gone it's it's here stay and this makes things really easy this this code right here can't be much simpler for accessing 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 get back to you an erm 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 and upcoming content they actually get more content as well so make sure you subscribe to my mailing list and when you do you're going to email it says hey let me know of any information you have that you all let me know we're at the bat you know any any questions you have those two the things and it's an ask it's going to tell you I'm a real person respond and you'll find out every person who joins a mailing list and emails me gets an email back personalized for me okay not a form letter doing a real email if you email me I will email you okay so with that being said that's all the rage for this video I appreciate you watching I appreciate you subscribing and I appreciate all you guys do to encourage me I'm definitely kind of more videos that start to finish series coming out soon on c-sharp so definitely look out for that alright thank you very much have a great day [Music]

Channel: IAmTimCorey

Views: 740,260

Rating: 4.8885117 out of 5

Keywords: .net, C#, Visual Studio, code, programming, tutorial, sql, database, sql server, mssql, ado.net, dapper, connect C# to SQL, C# tutorial, C# training, C# code, c# dapper, dapper sql, stackoverflow, sqlite, dapper database

Id: Et2khGnrIqc

Length: 80min 39sec (4839 seconds)

Published: Tue Feb 28 2017