Database technologies have changed considerably in VB.NET with the introduction of ADO.NET. Though, it still supports ADO used in VB6, Microsoft recommends the new ADO.NET technology for the VB.NET developers. It is a new technology for managing database programming. There are many ways of using ADO.NET and it provides many objects and components to program database connectivity. You can design an entire project at design-time as well as at runtime. In design-time, you need to drag the data controls from the toolbox and follow the wizards associated with them to establish database connectivity and perform operations. For design-time designing, you may find a lot of articles on the web. So, we will discuss how to make a database project at runtime.
As we mentioned above, there are many techniques using ADO.NET to implement database connectivity and operations. Here we are going to implement database operations using data adapters and datasets provided by ADO.NET.
Connecting to database
The first step in every database project is to establish a connection to the data source or the database. Before we discuss, have a look at the code below which is used to connect to the database:
In this example, we are having a database located at the root drive ‘D:/’ having the file name ‘db1.mdb’. This database has one table named ‘Data’ which has three columns: ‘ID’, ‘Name’ and ‘Age’. ‘ID’ is the primary key with ‘Auto Increment’ feature.
Dim da As OleDb.OleDbDataAdapter
Dim con As OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommandBuilder
con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/db1.mdb")
da = New OleDb.OleDbDataAdapter("SELECT * FROM Data", con)
cmd = New OleDb.OleDbCommandBuilder(da)
This code will give you an idea of how to establish a connection with the database in VB.NET. VB.NET provides two objects: ‘OleDbConnection’ and ‘OleDbDataAdapter’ to manage database connectivity. These two objects reside in ‘OleDb’ package. ‘OleDbConnection’ is used to specify the settings for the connection like driver specification, ‘Data Source’ name, database password etc.
‘OleDbdataAdapter’ is used to actually connect to the database. In the above code, we first declare the two objects. Then, we initialise the ‘OleDbConnection’ object which takes a ConnectionString as parameter. This ConnectionString is used to specify the settings of database connection. Finally, we declare an ‘OleDbDataAdapter’ object which takes two parameters: First one is the SQL ‘SELECT’ command. You specify this command so that you can tell the adapter which table to use for database operations. The second one is the ‘OleDbConnection’ object which you want to use for this connection. You will notice another object here which is ‘OleDbCommandBuilder’. This object binds itself to the data adapter and actually builds the commands for the update process. We will discuss it later. It takes ‘OleDbDataAdapter’ object as parameter.
Now, this is for making a connection to the database. The next step is to perform data operations.
Performing Data Operations
For performing data operations, we are using a ‘Dataset’ object here. A ‘Dataset’ object is a collection of tables. These tables have columns and rows. You can use the dataset object in two ways: Either create your own tables, and their columns and rows or fill it with the tables and their rows and columns from a database. As we discussing databases here, we will use the second way and explain you how to use it for database access. Have a look at the code below:
Dim ds As DataSet
ds = New DataSet()
da.Fill(ds)
This code is quite simple. It has the object references from the code written earlier for establishing a connection to the database. As you can see in this code, we have first initialised the ‘DataSet’ object and then used the ‘OleDbDataAdapter’ object’s ‘Fill’ function to fill the dataset with the the table ‘Data’ and it’s columns and rows. (A ‘DataSet’ can have multiple tables but as we have selected a single table named ‘Data’ in the ‘OleDbDataAdapter’, we have a single table here in a dataset which has a starting index of 0).
Now, as we have all the data of the table ‘Data’, we can use ‘DataSet’ object’s functions to add, edit and delete entries from the table. Below is an example of each.
Adding a Row
For adding a row, write the following code:
Dim dr As DataRow
dr = ds.Tables(0).NewRow
dr.Item("Name") = "George"
dr.Item("Age") = "13"
ds.Tables(0).Rows.Add(dr)
da.Update(ds)
The process of adding a new row starts by declaring a variable of type ‘DataRow’ and setting it to a new row in the first table in the dataset. After that, individual column values are assigned by using the ‘Item’ function of the ‘DataRow’ object. Then we add this row to the table in the dataset. But the main trick is done by the last line in which we call the ‘OleDbDataAdapter’ object’s ‘Update’ function which takes a ‘DataSet’ object as parameter. For this, you need to understand that a ‘DataSet’ is actually a queue in memory in which we make the changes but the changes are actually transferred to the data source once we call the ‘OledbDataAdapter’ object’s ‘Update’ function. This function takes a ‘DataSet’ as parameter in which you have made the changes and you want to commit the changes to the data source.
Deleting a Row
For deleting a row, add the following code:
ds.Tables(0).Rows(2).Delete()
da.Update(ds)
This code deletes the third row (index starts from 0) in the table of the dataset.
