Understanding Data Access in VB.NET

ActiveX Data Object (ADO).NET provides consistent data access from database management system (DBMS) such as SQL Server, MySQL, and Oracle. ADO.NET is exclusively designed to meet the requirements of web-based applications model such as disconnected data architecture, integration with XML, common data representation, combining data from multiple data sources, and optimization in interacting with the database.

In VB.NET there are many data access components such as SqlConnection, SqlCommand, OracleConnection, OLeDbConnection, and DataSet that are used to access data from a data source. These components in ADO.NET are known as classes. All of the important ADO.NET classes exist in the System.Data namespace. This namespace, in turn, contains child namespaces such as System.Data.SqlClient and System.Data.OleDb. These data providers provide classes to access data from SQL Server databases and OLE DB-compliant databases.

You can access data from a data store either using DataReader or DataAdapter objects. The DataReader object just reads the data using Connection and Command objects and passes it on to your application. You will not be able to modify or update data. The DataAdapter object though reads the data from Connection and Command objects; it passes the data to the DataSet object. The DataSet object contains a collection of tables, relationships, and constraints that are consistent with the data read from the data store. Moreover, you can save a DataSet as a file or pass it over a network. The DataView is another object in the data access process of DataAdapter. The DataView allows you to customize your data view i.e. you can extract or exclude the rows and columns from a table.

The syntax for data accessing through SqlClient is:

Imports System.Data
Imports System.Data.SqlClient

Sub BindGrid()
Dim strConnectionString as String = "server=SERVER;user id=username;password=passwd;initial catalog=databasename"
Dim connectionObj As New SqlConnection(strConnectionString)
Dim adaptorObj As New SqlDataAdapter("Select * from tablename", connectionObj)
Dim datasetObj As New DataSet()

adaptorObj.Fill(datasetObj)

DataGrid1.DataSource = datasetObj.Tables().DefaultView
DataGrid1.DataBind()
End Sub

The code given above uses a Datagrid control to display the data accessed.