How to connect to a SQL Server database using Visual Studio

Hoping that the credit screen is working properly, lets move on to the basics of connecting to a database and retrieving single or multiple data items. For this tutorial we are using Visual Studio 2008 with Microsoft SQL Server (which is normally installed with Visual Studio)

So fire up the IDE (Integrated Development Environment) which is Yes! You guessed it right! Visual Basic Studio and lets get down to some serious business.... (Well its easy! Believe me! )

So once the IDE is up and running start fresh with a new project. Make it a Visual Basic Windows Application.

Now lets first add a database to this project.

Go to Project -> Add New Item

Now you will be presented with a huge list of items that can be added to the Project. And as you may have seen, there are 2 types of databases. Local Database which has a file name like Database1.sdf and Service Based Database which has a name like Database1.sdf. So what is the difference? Well i was very curious about it and did some simple googling and found out that


Local Database
A Local Database which has the file extension .sdf is a type of database that is usually used in Mobile Applications. Its a kind of a database which is normally handles database tasks in your PDA or your favourite .
Remember, that though they are intended for Mobile devices, they can be used in desktop standalone applications as well. In contrast to a Service Based Database, as the name implies it does not require a service to be running in the host computer to access the data. The main disadvantage of this is the lack of support for multiple concurrent users. Enough said! Lets move on to Service Based. Well, a lil bit of googling wont hurt you!

Service Based Database
Service Based Databases, again, as the name implies requires a service (Microsoft SQL Server) to be running in the host computer to access the data. These databases ends with the extension .mdf and can be used in large scale applications with zillions of data. These kind of servers coupled with a high end server machine can handle A LOT of concurrent users. Also remember that these databases can also be used in a normal database applications as well. These databases can act as a back end for data intensive websites as well. Another very popular similar database is MySQL.


Now for this tutorial we will use a Service Based Database. Don't worry! I will cover the Local Databases as well. Though they are different conceptually, the coding is not much of a difference.

So now select the Service Based Database from the list of items available. If you find it hard to find it (i dont think so :D) select the category "Data" from the left panel to narrow down your search.




Finally, press Add to add the database to the project. If you are getting a problem at this time, you must have either not installed SQL Server or SQL Server is not running. That being the most basic reasons for the errors, please Google if otherwise.

In the next screen press "FINISH"

You will see that the Database1.mdf is added to the Server Explorer and is displayed in the Solution Explorer.

If you are familiar with creating Tables. Go ahead and skip this section or else keep reading...

Creating a Table
Now lets create a table in the database which we can add/retrieve data. For that simply expand the Database1.mdf using the + button in the solution explorer. After that Right Click on the Tables (In the folder like list of icons) and select Add New Table.



Now you will be presented with a screen which will allow you to define the structure of the table you will be creating. For now, we will create a simple table but i will emphasize on important features with this simple table.

Lets imagine, you have being called by your employer to create an application to keep in track of all the employee's names, their telephone numbers, their addresses and their current status. So for this, you will need 4 fields with different data types.

Read this for further information on choosing data types

So lets create the Table. But there's a catch! Actually you will require 5 fields! The extra one being for the Primary Key. I cannot dig in to deep about creation about databases here because i would need to write a book about it :D But simply put, a Primary Key is something that would identify each record of the database uniquely. For an example we can make a social security number or national identity card number as a PK (Primary Key) because they will never ever ever be duplicated! Please read these articles i found if you do not understand yet, what PK means. Please read THIS, THIS and THIS

So now lets move on and create the fields. Now we have to insert a special field! The PK! So lets choose something like empID which means Employee ID. The data type depends on how many employees are going to be working in the company. Lets say its a very small company with only around 50 people are working, you can select "tiny int". The data range for Tiny Int is 0-255. Always choose a data type which will support extra for future expansion. If the company has more than 1000 employee's the go for Small Int which supports up to -32,768 to 32,767. Forget about the -minus! But now you can store up to 32000+ employee records. For this example, i will choose Small Int. Now right click on the empID field and click "Set Primary Key"


As soon as you make it a primary key notice that "Allow Nulls" checkbox gets unchecked. Because a primary can never ever ever be null (or duplicated!"). Now lets make the primary key get automatically incremented on each record. So when you insert the first employee record, it will get the employee id 1 and the next employee 2 and so on. To do this, scroll down in Column Properties just below where you are creating the table while empID is selected and expand (+) Identity Specification. Now make the "Is Identity" Property True. Now you can specify for "Identity Increment" the amount that should be incremented on each record and "Identity Seed" to specify on where to start incrementing. Lets keep it on the default.

When selecting data types to hold text there is char,varchar,nchar,nvarchar.
the difference between char and varchar is that char is a fixed length text. So if you specify a text shorter than the fixed length, it will add additional spaces to make it to length. In contrast, varchar while having a maximum character limit does not do anything if the input text is smaller than the length.

Use char for text with a fixed length such as a telephone number/identification numbers.
Use nvarchar for anything that the length is unsure such as names, addresses, email addresses etc.

The "n" in front denotes that the field can hold non-unicode characters.

Now lets quickly fill the rest of the table.

Finish the table according to the picture below.


Now Save the Table! Use the Save button on the toolbar and enter the name “Employee”. Try to avoid spaces in the name which will later be a trouble when writing SQL statements.
Now that you have created the data, lets fill it with some data… :)

Right Click the Table and Select “Show Table Data”. Now you can add data in to the table.


Fill the table with appropriate data. Don’t worry about inserting an empID, just finish empName and other columns and move on to the next. Filling the empID is automatically handled.
Now that you have Database and a table with data, lets see how you can connect and retrieve the data! :D

First add a DataGridView to the form. A DataGridView is a table like view which will allow to show data in a convenient manner.
Now that the DataGridView is added, add a button too and change the name to “cmdLoad” and Text to “Load”
Now your form should look like this.

Now my favorite part! Coding!!!!!!
Double Click the Load button and lets start CODING!
First you have to create a Sql Connection to the Database. It is achieved by a SqlConnection object and its connection String.

Dim con As New SqlClient.SqlConnection

Now we have to set the con’s connection string property. The easiest way to get a connection string is from http://www.connectionstrings.com/

But for SQL Server the connection string is pretty straight forward.

Because we are using SQL Server Express Edition (Default SQL Server edition installed with VS 2008) the connection string is the following

con.ConnectionString = "Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\Database1.mdf;User Instance=true;"

Now when the connection string is added the next thing you should do is you should open the connection.

con.Open()

Now lets fill the DataGridView with the data in the Table.

First you should understand that the database only understands SQL. So we should construct an SQL statement to talk with the database. SQL stands for Structured Query Language. If you like to learn more about SQL syntax please refer to w3schools and w3schools SQL Select Statement.

Dim sqlString As String = "SELECT * FROM Employee"

This is simply a string which contains the command we are going to pass to the database. This simply says SELECT ALL RECORDS FROM THE TABLE Employee.

Now we require a middle man which will do the talking to the database. Ladies and Gentlemen, say Hello to the SQLDataAdapter. This nice friend when told what to get from the database, will talk in a nice way to the database and get the job done. 

Dim da As New SqlClient.SqlDataAdapter(sqlString, con)

Our “da” is the SqlDataAdapter and we tell him to use sqlString that we wrote and the connection. It is like telling someone what to buy and where to buy :)

Now the “da”, our SqlDataAdapter knows what to buy and where to buy but where to put all the grocery ??

That is where a DataSet comes to play! A DataSet is a object similar to a Shopping Cart :D You can hold one or more tables inside the DataSet. 

Dim ds As New DataSet

OK! Now we’ve got everything! Lets go shopping!

da.Fill(ds, "EmployeeTable")

What the above code does is calls the Fill method of the Data Adapter where two parameters are passed. We are telling the data adapter to use the SQL Statement and the connection we gave and fill the DataSet ds with the data returned from the database and name that set of data “EmployeeTable”

If you did not understand what was told above, take the Shopping Cart for an example. 

A DataSet can keep hold of different sets of tables and data. But when we want a particular set of data how do we retrieve them? That is why each set of data is given a name. Daddy could be a table in the Database, so is Mommy, John and Dan. 

Shopping is over folks! If you did not encounter any problems you have successfully filled the Dataset with all the information in the Employee Table. (Remember? “SELECT * FROM Employee”? SELECT ALL)

So now we should tell to the DataGridView to load the data from the DataSet.

DataGridView1.DataSource = ds.Tables("EmployeeTable")

DataSource is a property of the DataGridView which tells where to get the data. We are telling to get the data from the object ds’s (the dataset). Notice that ds contains “Tables”, which are like compartments storing data. So we are asking for the container named “EmployeeTable” which we filled prior. If you know about arrays, you can use the array index instead of the name of the container. The following code will work too, because it is the first container of data we filled.

DataGridView1.DataSource = ds.Tables(0)

Notice that we have included an array index (aka array subscript) 0. Why 0?? Why not 1??? Because in VB .NET, an array always starts with the index 0. 

OK now after the coding is done. Run the program and press the “Load” button. Your DataGridView should be filled now!

Here is the complete coding!

Public Class Form1

Private Sub cmdLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoad.Click

Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\Database1.mdf;User Instance=true;"
con.Open()

Dim sqlString As String = "SELECT * FROM Employee"
Dim da As New SqlClient.SqlDataAdapter(sqlString, con)

Dim ds As New DataSet


da.Fill(ds, "EmployeeTable")

DataGridView1.DataSource = ds.Tables("EmployeeTable")

End Sub
End Class

Technorati Tags: vb,programming,net,connect,vb net,coding,sql,server,express,sql server,data,datagrid,data grid,data grid view,DataGridView,connection,connection string,sql client,client,source,datagridview,dataGridView,ds,data source,mdf,sdf,sql connection,DataAdapter,SqlDataAdapter,SqlClient.SqlConnection,server based,service based,database,nchar,char,varchar,nvarchar,pk,primary key,mdf vs sdf,user,instance,local database,service based database