jump to navigation

Connecting to Databases in VB.NET June 29, 2008

Posted by prasu in : Databases, VB.NET , trackback

In most of the practical applications made using VB.NET we need to access a database to retrieve or manipulate the data. To access the data stored in a database from VB.NET we need the following objects-

  1. Connection: The connection object tells VB.NET how to get the data (i.e the database and the driver to connect to it) and also the database user credentials. This initialization is made in the Connection String
  2. Container: We need a object to locally hold the data we retrieve from the database. This object is known as the DataSet
  3. Bridge:We need a bridge between the Connection and the DataSet. This object is known as the DataAdapter.
  4. Command: The SQL Command which determines the kind of data we are trying to retrieve from the database is given in the Command Object.

A Connection string has to be specified so that the program can connect to the required database.

The data is stored in the DataSet via DataAdapters. This is done through a method called Fill, which takes two arguments The DataSet object and the Table name

To bind the data present in the DataSet to a DataGridView  (a control where the data will be echoed on the form), the DataSet and the tables are to be specified.

Now to code a simple program to access the data from a table to DataGridView

  1. Add a windows form.
  2. Drag a DataGridView control from the toolbox to the form.
  3. Double click on the form. you will go to the Form_Load method. Now type the code as given.

Public Class Form2

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

   'cn is the Connection Object with the Connection String to establish a database connection using the connection string in the constructor
    Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb; User=me;Password=xxx;")
    cn.Open() 'Open the connection in the form load event
    Dim cmd As New Odbc.OdbcCommand 'Command Object
    Dim adp As Odbc.OdbcDataAdapter 'Data Adapter
    Dim ds As New DataSet  'Data Set
    cmd.Connection = cn  'Associate the 'cn' connection with the Command Object
    cmd.CommandText = "Select * from mytable"  'The SQL Command
    adp = New Odbc.OdbcDataAdapter(cmd) 'Data Adapter- Associate it with the Command
    adp.Fill(ds, "mytable")   'The fill method. This stores data from the DB into the dataset
    Me.DataGridView1.DataSource = ds  'The source of data for the grid view control is the dataset
    Me.DataGridView1.DataMember = "mytable" 'Specify data from which table is needed
    cn.Close() 'Close the connection after data access is done
    End Sub

End Class

As you can by now figure out from the Connection String (ps-read earlier posts), we have used a MySQL database connected to the VB.NET Application using the ODBC Connector.

This is how your output should look like-

DataGrid

For those of you looking to develop applications dealing with databases, VB.NET is the tool to create an elegant front-end.

Comments»

1. dhilipkumar - September 26, 2008

its good……
useful

2. Carlos Gámez - October 15, 2008

hi prasu! your post is really interesting! Now I have a question I hope you can help me with… Im developing an application that connects to mysql via ODBC, but instead of using a DataAdapter and a DataSet, I store the data into an OdbcDataReader object, and then programatically fill a DataGridView. Im having trouble when dealing with large databases, it becomes quite slow. Thanks to you now I know how to fill the DataGridView using a DataSet, and i’d like to implement this method, but I don’t know if this will increase the speed of the reading process, can you help me here? thank you very much!

3. Carlos Gámez - October 15, 2008

Or what method using the ODBC Driver do you recomend implementing to accelerate reading? I use very complicated querys.. it also would be very helpful some advice in building querys, really i’ll apreciate it so much

thanks!

good day!

4. tuse - October 19, 2008

Hi Carlos.

Take a look at this-

http://articles.techrepublic.com.com/5100-10878_11-5066984.html

Article quoted from there-

When to consider using a DataReader:

* The DataReader is a better choice for applications that require optimized read-only and forward-only access to data such as binding to a DataGrid control. The sooner the data is off-loaded from the DataReader and the connection is closed the better the application performance.
* The DataReader is a better choice when you are planning to make repeated calls to retrieve small amounts of information or the data you are retrieving must be as up to date as possible each time it is used.

When to consider using a DataSet:

* The DataSet is a better choice for applications that will not off-load the query result immediately, or when there is extensive processing such as complex business logic involved between data accesses. The DataSet will retrieve the data, off-load the data into memory and return the database connection to the connection pool, where as a DataReader would keep the connection locked open until processing is complete. This could easily cause a high traffic application to run out of available database connections.
* The DataSet is a better choice when you need to navigate through the data more than once. For example, if you have multiple controls you need to build off the same data, then a DataSet is the better answer because a DataReader can only be read once so it can only be bound to a single control and would require the data to be retrieved for use with each control.
* The DataSet is a better choice when the data does not change frequently enough to warrant always retrieving it from the database or is specific to the user requesting the data. A DataSet can be stored in Session or Application variables or cached through the System.Web.Caching.Cache class to improve application performance by not having to retrieve the data from the database each time it is needed.
* The DataSet is a better choice when building a Web service that will return the retrieved data. Since a DataSet is serializable it can serve as the return value. Since a DataReader requires a persistent database connection, it cannot be used as a return type from a Web service.

5. jane - October 13, 2009

how update the database

6. adasd - January 20, 2010

sadfasd

7. Quezon City Polytechnic University - December 16, 2010

Thank for the article very useful

8. harold - June 17, 2011

i dont understand the code because im beginner in connecting database.

9. jaya - July 20, 2011

hi i want some more information how to connect to a common server database in vb
im doing project in online examination
i want to know about the connection can u plzzzzzzz help me

10. arun - September 7, 2011

please send connection coding in vb 2008 to access database

11. sally - January 13, 2012

hello…
I have a problem to retrieve a record from mysql database and display in textbox using vb.net. I tried to find in the web, but not found..I just found how to retrieve a set of recortd using sqldataadapter. could you please help me ..

12. mon - January 15, 2012

very useful

13. hide my ip - March 12, 2012

you’re in point of fact a good webmaster. The website loading speed is incredible. It seems that you’re doing any unique trick. In addition, The contents are masterpiece. you’ve done a magnificent process on this topic!