Skip to content

VB.NET – Database Access

October 8, 2005

Database technologies have changed considerably in VB.NET with the introduction of ADO.NET. Though, VB.NET still supports ADO used in VB but 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’ object is used to specify the settings for the connection. For e.g.: Driver specification, Data Source name, database password specification etc. ‘OleDbdataAdapter’ object is used to actually connect to the database. In the above code, we first declare the two objects. Then, we initialize the ‘OleDbConnection’ object which takes a ConnectionString as parameter. This ConnectionString is used to specify the settings of database connection. For more information about the ‘ConnectionString’, Click Here. 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 is a very important class as it binds itself to the data adapter. It actually builds the commands for the update process. We will discuss it later. For now, you should know how to initialize it. It takes a ‘OleDbDataAdapter’ object as parameter.

Now, this is for making a connection to the database. The next step is to perform 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. This code 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 initialized 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. Then, 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.

Comments

Tell us what do you think.

  1. Dinesh says: February 9, 2013

    I have a Excel sheet say for eg. Test.xlsI have 2 sheets :- Sheet1 and Sheet2.In each sheet , the foowlling columns are there with more 10000 values.LEGAL ENTITYPayroll Company CodeLegal EntityREGIONCLLILocation CodeAddressCityStateZipFor eg:I010F1SBC Internet Services, Inc.MidwestABHLMI05MIA9362550 N SQUIRREL RDAUBURN HILLSMI’48326 I have to load those vaues from Excel to SQL Server table.Note:- We dont have Excel in Server.So v hv to find some solution to convert XL to doc or txt.SQL table format isCLLI_Tbl_Id intCLLI varchar 15Site_Type_Id int4Address1 varcharAddress2 varcharCity varcharState char2Geo_Loc varcharState_Id intZip varcharCLLI_Last_Updated datetimeLegal_Entity_Id intRegion_Id intCan you tell me the SQL query to load those values to SQL table?Also Site_Type_Id ,State_Id , Region_Id ,Legal_Entity_Id , i will get from 4 other tables.Any help appreciated.

    • admin says: February 9, 2013

      Hi Dinesh,

      Thanks for your comment.

      You can make use of MS Office Intero classes. MS Office comes with COM that provides classes which you can plug into a .NET application and then do programming for reading/generating Excel/Word files. You can refer to this article for more information:

      http://msdn.microsoft.com/en-us/library/dd264733.aspx

      Basically you will read each row from each worksheet and then form a SQL query dynamically and then execute each query either separately or as a part of bulk operation to insert data into the SQL tables.

      If you don’t have office installed on development machine, you can have a look at this:

      http://netoffice.codeplex.com/

      However I haven’t tried it yet.

      The other option is you can open Excel file on some other system on which Excel is installed. Export the data in CSV format and then you can write an algorithm in C#/VB.NET (whichever you use) to parse it and create SQL commands.

      I hope this helps.

      Regards,

      Sumit

Trackbacks

Websites mentioned my entry.

There are no trackbacks on this entry

Add a Comment

Fill in the form and submit.