C# Database Connection Strings - What They Are, How to Build Them, And More

there are three parts to connecting to external data in your application you need to know how to connect the data you need a tool that can do the talking and you need to know what to tell the tool to do the first part how to tack the data is typically found in a connection string that's a string that says here is where the data is on the network or the computer here is the specifics of how to connect to it and here's the credentials needed to access that data that is what we're going to be covering in this lesson we won't be covering the other two parts but never fear has video that covers that will enough to get started even if your data storage device isn't Microsoft sequel check out my video on how to use dapper to connect c-sharp - sequel that will show the tool which is dapper as well as how to query the data the link to that video is in the description down below now in case you're new to this channel my name is Tim quarry and my goal is to make learning c-sharp in other related languages as easy as possible for you if that sounds good to you hit the subscribe button to hear all of those videos I release and if you feel like you might want to take your learning to the next level there's a link in the description to my mailing list that's really good insider news and discount on the paid courses that I offer all right so let's jump right into the topic now the first place I want to spend some time is an even visual studio it's this website connection strings calm now this is the heart I've had talked to a database or external data in fact if you can see down here it says connect to it's got an awful big list of different places you can connect including kind of offbeat ones like Microsoft Project or an a s400 now most likely you won't be using most of these but you will be using a few that I'm sure the first and most popular one would be Microsoft sequel server so let's start there and look at what this site has to offer now if you see down here I'll scroll real quick there's a number of different ways you can connect to sequel server it all depends on the options in which version and today list here which version this one supports now these first two right here and it's a sequel server specific but these first two are the most important and typically that's how they light a page out the ones they're most common are most important the ones that use the most they put the top so if you're going to connect to a Microsoft sequel server either this one or this one is the important one for you to know so let's start with figuring out what the different parts are and it will discuss at the end which why are different so the first part is connection string says the server equals now what that's identifying is where the database is actually located which machine now it says my server address you replace that string with the IP address of your server or if it's on the same machine as what you're on right now so your code runs in the same machine as your database you just a dot but what this is is a way to identify which machine on the network you're going to talk to so if you had this machine or a sequel server on different machine you could put the IP address there and this connection string going to okay the server's located on different machine here's how to get to it and put the IP address so it's very important to know where your server is located now just a quick note if your servers located on a different machine you have to make sure the firewall on that other machine has been correctly adjusted to allow sequel server traffic by default they do not allow sequel server traffic to come inbound so you need to change that so that's the server now is one other Twiggy let's look at the third connection string we see for a sequel server instance in my course where I build a c-sharp application where I build tournament tracker I actually use a different instance for a default now if you use just dot up here does indicate this machine and indicate the default instance if you put an IP address and that's it that indicate a different machine but the default instance of sequel however if you installed sequel on different instance unnamed instance then what you do is you put slash in the instance name now you may be already thinking all man lost I'm not sure what an instance of sequel really means here's how it works in simplistic terms when you first install sequel server onto a machine it uses the default instance unless you were changes you wouldn't change it unless you knew what you were doing hopefully so therefore if you're not sure it's probably the default instance however like in my case I had sequel server 2012 installed on my machine but then I wanted to use sequel server 2016 so what I did was I installed a second copy of Microsoft sequel server the problem is they both can't use the same default for their name so therefore my second installation I had to give a name other than just the default therefore I said I called it SQL two zero one six my connection string was dot for server name since I'm on the same machine as my sequel server so dot slash SQL two zero one six like I said if you don't have two copies of sequel server installed on your machine or the machine you're talking to you probably don't need to worry about that now that's different than having two databases the installation of sequel happens one time typically the number of databases that can hold is many so don't think they have two databases I have two sequel servers you don't it's only if you specifically go back to the Installer and saw a separate second copy of Microsoft sequel server so it's a little more advanced they said if you're not sure don't do this server instance ignore the slash and the my instance name don't do that one do just the actual name of your server or dot for logo so that's the first part the server where the database is located next for sequel server we have a database name now like I said you can have more than one database on one sequel server so it's asking for is which database are you planning to connect to now there's a little confusion about this so I'm like make sure I clarify this when you specify a database name here that does not mean you can only talk to that database with this connection string what it means instead is by default it's going to assume this database so a full path or a full name for a table actually starts with the database name a lot of times when you access a table or a store procedure people just use the table name or stored procedure name so say your table is called table 1 they'd say well select star from table 1 but in Microsoft sequel server what is actually doing is assuming three things it's assuming which server you're on it's assuming which database you're talking about and it's assuming which owner the owns a table so a fully qualified table name and store procedure name has four parts the server the database the owner and the actual name so with this connection string we know what server we're on and we know now what database were going talking to or at least primarily and so it will default these first two based upon this connection string so if we do not specify all four parts these two things right here will fill in the blanks as to what to default those first two parts to now a database owner that's usually DVO is that again default that is assumed based upon sequel server saying that's the default owner and there are settings in sequel server to change that but typically people don't so that's how it knows it assume a third part so these first two allow for the sumption of where that object is located so what that means is you have to have this part or at least I'm pretty sure you do if you don't that mean that there'd be no assumption and so assume master because that's what sequel server assumes but in really should include this part and put the database your plan working with personally even if I'm connecting to the same sequel server in just different databases I'll create separate connection strings for each it's makes it a little cleaner because I can reference each connection string based upon the database name and I know okay that's where I'm already talking to a server and database name next in this connection string we have a user ID and a password now down here so far within the same server and database but now we have trusted connection equals true so here's the difference in Microsoft sequel server we have two options for database security we can use the windows authentication or we can use sequel authentication sequel authentication is a username and password and that's what comes in that using a password is not your windows username and password it's one that's put right into Microsoft's SQL so this would be something you specify in sequel and say I'm going to create a new user the username will be and you give a username and a password leave this and so that would be what this would be set up for alternatively if you said nope I want to take the permissions from the logged in user you would use trusted connection equals true so here's where people start to get a little concerned using the impasse word in clear-text that should make you a little concerned and while we're not going to go into it in full detail in this video this is more about just actual connection and not worrying about again how to connect and some other things but instead I do want to talk this a little bit just so that we're kind of a little more comfortable with this I don't want you rejecting this out of hand as not useful or should never use it so using a password being stored in a text file not as big a deal as you'd think and here's why whoever has access to your application has the permissions so whether they see the user name and password or not they still have that level of access in fact if you try to hide this that you'd probably still find it out by listening to the network connection because they control one side of it so therefore they're not it's making Amanda middle attack they can just listen to their own network traffic so why isn't a big deal well if they have access to delete users and delete data and mess up your sequel server and some in some nasty way well that should mean that you trust them enough to have that permission otherwise you shouldn't give them that permission so don't ever put administrative login name and password in this file never I don't often say never and always because it's usually an edge case that might be true but here's the deal I can't think of a reason why this should have unlimited rights I just can't you should lock this username and password down to just what the user should have access to now it makes it a little easier if you do a trusted connection equals true and the reason I said is because it actually takes the credentials from Windows now it doesn't transmit them it just says they've already logged in this machine and I know they are this person therefore they're allowed to connect this server as this person now this doesn't typically work across a network the exception of that would be if you're on a Active Directory domain now for those of you who are not in a corporate network that probably means nothing so ignore it but if you are in a corporate network where your credentials come from a centralized server and you can log on different machines with those same credentials this would work across the network or at least as long as a sequel server is set up to allow Windows credentials so it seems a little more safe and in some ways it is because it's very easy then say well Bob has these permissions but Sally has these permissions and therefore if Bob logs in the windows then we'll give Bob his access versus Sally's access it's a little harder to do that here but it is possible it's just again harder what you need to do is instead of taking this blindly from the file it's using an password you would need to in your application ask for them to log in capture user name and password and overwrite these values when you're actually talking to the sequel server that means having them giving them a separate set of credentials that are only usable on Microsoft SQL that's a little more work but as possible now again you can lock down your sequel server to only give them the right they actually need to have for instance if they only ever read data say it's a reporting application you're creating well reports don't typically write data to a sequel server they typically just consume it therefore you give that user really access that's great to have because that means it can't mess anything up one of my favorite ways to lock down a database is to have everything go through a stored procedure and the reason why is because then I can say here this user right here only has access to execute stored procedures they can't read what the table names are even they can't log into the database they can't edit data they can't update data can't delete data they can't do anything except what the these store procedures allow them to do so that's a nice check and balance to have that's my personal favorite so we're get a little far afield for this demonstration but I want to talk a little bit about security just to give you a little more comfortable would be these two options so we've got our server name we've got our database name and then we have user name and password or we have trusted connection equals true so there's the most common two for sequel server but what if you're not using Microsoft sequel server well no worries coming back to this main page there's an awful lot of options here one option might be shut her a little bit this is I'm not a big fan but one option might make a self-access now you have access to the access may you have access to these databases and you can send them along with your application and they are convenient that way well standard security right here this isn't a little bit different because it says first of all the provider we have a specify what the driver is essentially that we're talking to how you know this well because connection strings com provide it just copy and paste it the data source is just where is that database located and then finally persist security info equals false and that's all it really is to connecting to a Access database now if you have a database password that's a bit more of a headache actually so there's the connection string for you but read this down here there's some problems with the encryption scheme and so you really need to back down to the 2007 encryption method in order to use a connection string from a dotnet resource like c-sharp so that's a little more of a problem there's other look other possibilities like network location so if you're on network use that instead and there's other options as well you can look through on your own time but that's pretty simple essentially copy and paste this and just change data source to the location of your file now if you want to go more of the open source or more open source friendly route while the options might be MySQL again real simple server name we're familiar with that database name we're familiar with that user ID and password so you deal the only tricky thing if you want to add a port number there's how you do it so as also things you can do with encryption new and old methods multiple servers forcing the encryption SSL other fun stuff but essentially you find the one that fits your needs you copy it you paste it in your connection string location and just change these values wherever it says equals change that value afterwards and that value should be whatever is on your machine not what somebody else gave you all right so those are three different options are connecting to three different database versions so access my sequel and sequel server you can go a sequel light if you'd like that's a fun little one that's even easier data source age of the location and the version is three all right so there's a lot of different options here depending on what you want to talk to but once you have the connection string that works for you I'll just copy this let me this I've got something similar then you just go over to your visual studio here I have a project open I've been working on it kind of playing around on different things and I opened up the app can see now just typically where we store our connection strings in the reason why is because this app dot config or a web duct config if you're on a web project the app dot config is changeable while your application is running worst case you have to restart your application that's the biggest deal you have to do but otherwise it's just a matter of changing this file saving it and your changes are live and you can change this file using notepad something simple like that text editor of any country if you can avoid it don't hard-code any connection information directly into your c-sharp code what that does is it locks you down to only using that particular server that particular database with those particular credentials and you may think that well I'm never going to change those things but the reality is down the road you probably will so just don't hard-code it so there is a specific section you can add to the configuration file in the app ducking fear web duct config so after the opening configuration tag as long as there's not already one in place now this is pretty simple I only have a start up tag inside here at this level but open up a new tag and say connection strings and again intelligence works which is great inside connection strings you're going to add a new one give it a name so the name can be anything you want but this will refer to that particular connection so if my database name is called tournaments I might call my connection string termos that way it's very obvious that if I'm using the tournament's connection string I'm talking to the tournament's database so next you can say connection string equals now we're going to paste in that connection string now I would change this to be dot to use my local machine no instance so default instance the database name tournaments and trusted connection equals true means I'm going to use my Windows credentials now for a sequel connection this isn't true for all them just for sequel but after the closing quotes of the connection string I need to add one more thing and that's provider name I would say system dot data dot sequel client now how did I know to do that well that's what Google helps with really and so this is the other part of connection strings they might not always work evencio x n-- action string comm there may be a problem with your specific style of connection or maybe you type something wrong or in this case maybe to add a provider name now that's particularly specifies that we're going to use the built-in Microsoft sequel connection client so the provider name is system dot sequel client microsoft tells you about that if you read through their documentation or if you just google the internet for c-sharp sequel connection string it will tell you all about the provider name so that's one option for setting up a connection string now what if you had two different databases well that's pretty simple what you do is you would go back to connect the connection strings com find the connection string you want let's say an Access database this time copy the connection string that applies to you come back in here and inside connection strings add another connection string name equals let's call this my guestbook database and the connection string equals and there's my connection string now it wrapped off the page because it's so large and because on the connection strings page they wrapped as well in order to make it smaller therefore I have to get wrap out there we go so now it's not wrapped anymore the data source let's change this we'll call it into the temp file and it's instead of my Access file I probably call it guest book dot accdb so now that's all I need to connect to the guestbook database which is an Access database so this is sequel this is access maybe if you're storing a same type of data but in two different databases and made a user has options for both I might label these right in the connection string name tournament sequel and guest book ACC that way it's a little more obvious which database type it is especially if you want to call this tournaments that says tournament sequel and determine ACC so you know right away this is the access connection string this is a sequel connection string now it does make sense for my case I don't really care about that because I know which ones which based upon the type so it's up to you that name the only thing that's valuable for is actually talking to the web dot configure a pack until you file in getting this connection string Microsoft has built in a tool to talk to this config file and pull out a connection string they based upon its name therefore it's very easy to say giving a tournament connection string so that's the only place you use that name now the last thing I wanna cover in here is the fact that this connection string area right here is meant to deal with ODBC connections now it does other connectors as well to some extent but in general it deals with ODBC connections an ODBC connection is an open database connectivity connection and it's essentially a standard that says there are certain parts that you had one of them is a connection string that gives the information to talk to your database and so if your database can talk ODBC then you can put your connection string in here now some databases don't use this style of connection string do something different and so if you're going to use a case or a even newer database style it might not fit inside this connection strings area it might not make sense to put the information here so it is up to you to kind of look at how do I connect to that database using c-sharp so even google that or look through the documentation usually I tell you and usually give you an example if I use the connection string you would do the same exact pattern you doing here where whatever connection string they give you your sensor is paste it in this section or the connection string equals and they can access it using the name for anything else it's really up to you to figure out how to connect to that database but here's the cool part if you want to connect to a database and you can't figure out how to get c-sharp to connect to it write down the comments down below say I'm trying to connect to this database type I can't figure out the connection string information or how to connect to it I'll just from Google search my own I'll look through my records if I connected to v4 and I'll try and give you a hand connecting to it all right so that's the essentials of connection strings we actually use these yet but if you again there's a video download comments we're actually use a connection string to talk to sequel from that it's the same or a similar method for connecting to any system that uses the connection strings alright I love know your thoughts down below in the comments leave me a comment let me know what confused you what more you want to see and what you liked also I would love a thumbs up if you like this video I hope you found this video enjoyable check out the rest of these I have to offer and if you have any questions or comments please let me know thanks

Channel: IAmTimCorey

Views: 62,091

Keywords: .net, C#, Visual Studio, code, programming, tutorial, training, how to, tim corey, C# training, C# tutorial, database connection string, connection string, web.config, app.config, connectionstring, sql, mysql, access, microsoft access, mssql, sqlite, dapper, vs2017, visual studio 2017, security, database security, querying, best practices

Id: weE1CwfS9rI

Length: 31min 54sec (1914 seconds)

Published: Sat Jul 29 2017