astro-gin

[astro-gin]

How to establish a connection to a Microsoft sequel database using visual basic

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 gon na have to supply it. 

Some values you can do it either directly here or in a new sub, I'm just gon na. Do it right here and kind of kill two birds with one stone, so I'm gon na 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 gon na 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 gon na be using my default sequel. Server admin account. So what I'm gon na 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 gon na 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 gon na 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 gon na 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 gon na 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 gon na want to return that value or we're gon na want to at least have maybe some way of catching that you could dump it to a log file or something I'm just gon na 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 gon na 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 gon na 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 gon na go back to my sequel, control and deliberately set one of these values to an incorrect value. I'M just gon na put a D, my user name and then I'm gon na 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,