This is the part 2 of the 2 part article on the basics of vb.net database connectivity. In this guide I’ll show you how the few things which changes whenever you try to connect to different kinds of database. Here is a link on the part 1 of this series in case you haven’t red it yet.
There are only few things that changes whenever you try to connect to different database:
According to Wikipedia, a connection string is a string that specifies information about a data source and the means of connecting to it.
This only means that for every database , there is unique connection string to be used.
The connection string that were referring here, is the parameter which goes inside the declaration of the database connection in the vb.net application. Here is an example of a connection string which I use in connecting to a mysql database using the MySql ODBC driver.
Dim cn As New OdbcConnection("DSN=yoh")
As you can see, the connection string is the one that is inside the parenthesis which is then enclosed in quotation marks. The connection string here is short compared to the other connection strings, this is because I have used a DSN. Short for Data Source Name. The DSN is used as a reference to all the information which is needed by the program in order to connect to the database. And the DSN is defined somewhere else in the operating system.
If you want to know how to connect mysql and vb.net using ODBC , then check this one out.
This is the one which is on uppermost part of the visible code. It is often placed on a Parent Class so that other forms can inherit from it. And there will be no need to re-declare the namespace on every form that you’re going to create.
Here’s a list of namespace that I know:
- System.Data.Odbc- the.NET Framework Data Provider for ODBC
- System.Data.SqlClient- the.NET Framework Data Provider for SQL Server
- System.Data.Oledb- the .NET Framework Data Provider for OLE DB.
- System.Data.MysqlClient- the .NET Framework Data Provider for MySql DB. Mainly used when connecting mysql to vb.net using the .NET Connector.
Basically you will need to type the keyword ‘imports’ before each namespace. So that the program will know that it’s a namespace that you are declaring. Pretty much like declaring a variable, we typically use the ‘dim’ keyword in vb.net.
Methods used by the namespace
Of course, the namespace will determine the methods which can be used in manipulating the database. The methods that I’m referring here are built-in methods which is contained on every namespace. Because each namespace is like a large library of codes and there are methods in it that you can use.
I’ll just give few examples here, so that you’ll understand what I’m talking about.
In this example, I’m going to use the ODBC namespace:
So it is automatic that we’ll also be using odbc in each of the methods that were going to use. For the connection we use:
Dim cn As New OdbcConnection("DSN=yoh")
As you can see, I used OdbConnection in the declartion. That is because I used the ODBC namespace.
When I declare a command, I also do something like this:
Dim cmd As OdbcCommand
If I declare a data reader:
Dim rdr As OdbcDataReader
For those of you who do not know what data reader is. It is a built-in method in vb.net which is mainly used for fetching records or data from any database. So even if you are using a different database, you would still use data reader. But keep in mind that you have to change the word before it when you use a different database. I’m using ODBC as an interface between MySql and Vb.net that is why I declared it as OdbcDataReader.
Here’s the round two of the examples, so that you’ll fully understand how the namespace dictates the name of the methods that were going to use.
If on the other hand I use connector.net to connect vb.net and mysql. Then I make use of this as my namespace:
Then the connection declaration would be:
Dim con as new MySqlConnection("Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;")
It’s a bit longer since I didn’t use a DSN.
Driver to be used
Lastly, we also need to consider the different drivers that we could use to connect our application to a database. Here are some database drivers:
If you already visited the links you will see that most of the drivers are for ODBC. Which means that you can also use a DSN on each of them to make the declaration of the connection string less tiring.