VB.NET Database Tutorial - Connecting To Microsoft SQL Server (PART 1) (Visual Basic .NET)

welcome to the VB toolbox in this tutorial we'll be showing you how to establish a connection to a Microsoft sequel database using visual basic.net for this tutorial all is it is expected that you already have a sequel server that is operational and accessible and the necessary credentials to log in to that server what I'll be using in my environment is Microsoft sequel Server 2008 Express and I've set up a test database here called sequel apps as well as a basic forum structure including a members table forum boards threads and replies and as you can see I can from my management studio I can already query that database it is functional and working so what I want to do is first off just establish a connection to the database from my application so let's go ahead and get started I'm going to go back to my Visual Studio Start page here from here I'm going to create a new project and it'll be a Windows Windows forms application I'm going to call it sequel app okay and that will generate a project template for us from here what I'm going to do is create a sequel connection class you can put all your sequel code in your main form if you'll and I just like to break it out to connect kind of keep it clean so in our solution Explorer if you can't see that click view and solution Explorer you should be able to get to that click on your project header and then right click and add a new class and I'm going to name my class sequel control SQL controller then I'll click add that creates a new class outlined for us now the first thing to connecting to a sequel database is making sure that you have imported the proper library so I'm going to start by saying imports system SQL I'm sorry oh data dot SQL and then imports system data SQL client alright and with those libraries accessible we can now create a sequel connection and our sequel command to connect to our database and query it so we'll start out by adding a public SQL con that'll be our connector as a new SQL connection and we're gonna have to supply it some values you can do it either directly here or in a new sub I'm just gonna do it right here and kind of kill two birds with one stone so I'm gonna click with and open some curly braces and supply a connection string and this connection string is very important for establishing our connection so we have to supply at least four parameters here to get a connection to our database one server pointer and then a database pointer and then we have to supply our credentials for connecting to that database so first thing we'll do is supply a server and your server is going to be your server and instance that you're trying to connect to in my case my server instance is on an old minecraft box and so it's minecraft back slash sequel Express is my instance so that will be my server name so I'll say minecraft back slash SQL Express mmm-hmm now each parameter in this string will be separated by a semicolon so we'll add a semicolon after that and now we will point to our database that we want to connect to in my case the database is SQL Apps okay so I'm gonna say database equals SQL Apps all right and finally we need to supply a user and password with proper credentials to access our database so I'm gonna be using my default sequel server admin account so what I'm gonna do is I'm going to say user equals si that's the default account and then I just need to supply password the password parameter is PWD and set this to equal whatever your password is to connect and they say pass for the one not very secure but that's just perfect for a test so I'm gonna finalize that with one last semicolon not sure if that last one is absolutely required but I like to put it in there anyway so then I'm gonna drop down and say public SQL command and this is what you use for querying your database after you've successfully connected to it so I'm gonna say as SQL command apart commands sorry we probably won't actually be using that name in this part but in the next segment we will so I'm just gonna add it now finally to test whether our connection is successful or not we're going to create a functional it's a public function has connection and I'm just going to return this as a boolean value so I'm going to say as blue in and in this function what we'll do is we will simply test our SQL con our connection string and see if it successfully opens and closes if not we're going to fire back an exception message to tell us why it failed and to do this what we're going to use is a try-catch and this allows an error to occur without actually crashing your application it's you can catch the exceptions and return them gracefully and then return back to your application if it fails so let's go ahead and just test our sequel connection by saying SQL Con dot open now if everything we've supplied here is accurate then this should not fail and if it is successful then let's go ahead and clean up after ourselves and close that connection now if there is an error we're gonna want to return that value or we're gonna want to at least have maybe some way of catching that you could dump it to a log file or something I'm just gonna say show me a message box telling me why it failed so to do that we'll well capture the exception dot message so we'll say X dot message now as a function we need to return a value so I'm going to say if the opening close occurs successfully I'm gonna say return true otherwise return false all right so let's go ahead and test this connection and see what happens if my authentication information here is accurate to do this I'm going to go back to my main form and I'm just going to create a new instance of this sequel control class that we've created but to do that I'm just gonna say dim SQL has new a new instance of SQL control okay so we're going to create a new instance of our sequel control class that we created and then we can use that to call the has connection function so what we'll do is we'll say if SQL dot has connection equals true then return to us a message box this is successfully connected let's go ahead and run that and see if it works might take me a moment here this old box that I'm using is a sequel server is incredibly old and slow so as you can see the connection was a success it was able to connect to the database and close its connections successfully so let's go ahead and force it to crash I'm gonna go back to my sequel control and deliberately set one of these values to an incorrect value I'm just gonna put a D my user name and then I'm gonna run that again and it immediately failed it tried to login and it says it failed for user sad so my catch exception worked properly and then it returned back to my main form without actually killing my application so as you can see it was a complete success and we can also try altering another parameter here let's mess up the database connection and put a T in front of that run that and boom it caught another exception cannot open the database requested by the login so it noticed that the date that was a failure as well so and return that to the way it was so as you can see it's really very simple to connect to a sequel database now if you stick around the next tutorial I will show you how to query the database and return values from it from our fields and we'll do this in a couple of different ways we'll retrieve values and put them in a form as well as just testing with message boxes and things like that there are a number of other ways to return your data as well as populating data grids and hopefully we'll be able to cover that in a an upcoming tutorial as well so I hope you found this helpful for getting started if this was helpful please share it with anybody else that you think would find it useful and if you like it now give me a thumbs up I appreciate that thank you for coming along and I look forward to sharing more with you on the next step I will talk to you later bye bye

Channel: VB Toolbox

Views: 200,596

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET SQL Server, VB SQL Server, VB Connect to SQL Server, VB.NET How to Connect to SQL Server, VB.NET Microsoft SQL Server, Visual Basic SQL Connection, VB.NET Database Connection, VB SQL Connection String, VB Database Connection, Visual Basic .NET (Programming Language), Visual Basic (Programming Language)

Id: odrntJn2o98

Length: 12min 18sec (738 seconds)

Published: Fri Dec 06 2013