Connectivity in VB .net

Introduction/Overview

• Database Access Technology or connectivity in VB .net is the mechanism through which an application program connects and communicates with data repositories/databases/data sources successfully.
• Database connectivity in VB .net software is also known as database middleware/sometimes called API(Application Program Interface) because it provides an interface between the two totally different natures of software i.e. application program and the database.
• In fact, ODBC, OLE-DB, and ADO.NET types of connectivity in VB .net form the backbone of Microsoft’s Universal Data Access (UDA) architecture, which is a collection of technologies used to access any type of data source and manage the data through a common interface.

Types of Connectivity/Database Access Technology

There are the following types of connectivity in VB.net occur between application programs and data sources/databases –

(F) ADO .Net Connectivity

• ADO.NET provides a powerful and flexible way to interact with databases in VB.NET applications.
• ADO.NET (ActiveX Data Objects for . NET) is a set of libraries in the Microsoft .NET framework used to access and manipulate data from different sources, such as databases, XML files, and web services.
• ADO.NET is a powerful and versatile framework for data access and manipulation in .NET applications, offering various components and classes that streamline database interactions and facilitate building data-driven applications.
• It provides a consistent set of classes and methods to work with data, enabling developers to create data-driven applications in the .NET environment.
• ADO.NET provides a bridge between the application and the database, enabling us to perform operations like querying, updating, and managing data.
• It is an advanced type of connectivity in VB .Net way with a database.
• ADO .NET is a specific module/part of the .Net Framework that is used to establish connections between applications or prog. lang. and data sources/databases.
• ADO .Net also provides security features like parameterized queries to prevent SQL injection attacks, whenever required.
• ADO .Net allows for efficient data access and manipulation due to its streamlined architecture.
• ADO .Net supports various data sources such as databases, XML files, and web services. In this environment, Data sources/Databases can be SQL Server and XML, MS Access, My SQL, Oracle, etc.
• The NET framework contains several classes in which methods can be used to connect, retrieve, insert, and delete data from databases.
• All the related ADO.NET classes are located in the Data.dll file and integrated with XML classes which are located in the System.Xml.dll file.
• In comparison to ORM methods, ADO.NET provides a low-level data access approach, while Object-Relational Mapping (ORM) frameworks like Entity Framework offer a higher-level abstraction for data access by mapping database tables to object-oriented classes.

Components/Architectures of ADO .Net/ADO .Net Class Libraries 

• ADO .NET has two main components that are used for accessing and manipulating data as a whole. These are –

(A) Data Provider Class and (B) The DataSet Class.

(A) Data Provider

• • These are the components that are designed for data manipulation and fast access to data.
  • It contains various objects such as Connection, Command, DataReader, and DataAdapter that are collectively used to perform database operations.
  • The data provider component is used to connect to the database and execute commands to retrieve the stored records.
  • It is a lightweight component of ADO .Net with better performance capability.
  • This component also allows us to place the retrieved data into DataSet (which acts as a buffer) to use it further in our application.
  • The .NET Framework provides/contains the following types of Data Providers that we can use in our different database applications as per our requirements –

.NET Framework Data Provider	Description
Data Provider for SQL Server	It provides data access/connectivity in VB .net with Microsoft SQL Server. It requires/uses the System.Data.SqlClient namespace.
Data Provider for OLE DB	This provider is used to connect with the OLE DB/MS access application. It requires the System.Data.OleDb namespace.
Data Provider for ODBC	It is used to connect to data sources/databases by using ODBC connectivity. It requires the System.Data.Odbc namespace.
Data Provider for Oracle	It is used to connect with Oracle data sources. It requires the System.Data.OracleClient namespace.

Data Providers Classes :

Data Providers Classes exist in the form of VB .Net Objects. Some core objects of Data Providers that are used in database connectivity are:-

Objects	Description
(1.) Connection	The Connection object is the first component of ADO.NET. A Connection object sits between a data source and a DataAdapter (via Command). This VB .Net object is used to establish/link a connection to a specific data source/database with ADO .Net. Through this connection, we can access and manipulate a database. A Connection object represents a unique session with a data source. We need to define a data provider and a data source when we create a connection. With these two, we can also specify the user ID and password depending on the type of data source. Connection can also be connected to a Command object to execute SQL queries. Each data provider or database (MS Access/OleDb – OleDbConnection, SQL Server – SqlConnection, ODBC – OdbcConnection) has a Connection class. The ConnectionString, Provider, State, and Mode are common Connection properties. The connection string specifies the server, database, and authentication details. The Open(), Close(), Cancel(), and Execute() are the most common methods of the Connection class.
(2.) Command	This VB .Net object is used to execute queries, procedures, and SQL statements to perform specific database operations. The Command object uses the connection object to execute SQL queries. Each database has a (such as SqlCommand, OleDbCommand, etc.) different Command classes. It can also execute stored parameterized procedures and queries. The Command object is the core component of data processing with ADO.NET. It typically wraps a SQL statement or a call to a stored procedure.
(3.) DataReader	The data reader is also called a firehose cursor or forward-only, read-only cursor because it moves forward through the data. In ADO.NET, a DataReader is a broad category of objects used to sequentially read the stream of data from a data source/database. DataReaders provide a very efficient way to access data. The DataReader is a good choice when we’re retrieving large amounts of data because the data is not cached/unbuffered in memory. Each database has a (such as SqlDataReader etc.) different DataReader classes. DataReader fetches the data at a very fast rate when compared with the dataset. The DbDataReader is a base class for all DataReader objects. DataReader fetches the data from the database only when the connection is open.
(4.)DataAdapter	The base class for all DataAdapter objects is the DbDataAdapter class. The DataAdapter object in VB.NET is a class that acts as a bridge between a DataSet and a Data Source for retrieving and storing/saving the data. It can be used to fill in the DataSet and update the data source. DataAdapter opens a connection, creates a data adapter object with a SELECT string, creates a dataset object, calls the data adapter’s FILL method to fill the dataset, and binds the dataset to the DataGrid. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. This object is used to read the data sequentially from a data source. Thus, DataReaders provides a very efficient way to access data, The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. The DataAdapter object works as a bridge between a DataSet and a data source/database to retrieve data. DataAdapter is a class that contains a set of SQL commands and a database connection. It can be used to fill in the DataSet and update the data source.

DataAdapter Constructors :

Constructors	Description
DataAdapter()	It is used to initialize a new instance of a DataAdapter class.
DataAdapter(DataAdapter)	It is used to initialize a new instance of a DataAdapter class from an existing object of the same type.

DataAdapter Methods :

Methods	Description
Dispose(Boolean)	It is used to release the unmanaged resources used by the DataAdapter.
Fill(DataSet)	It is used to add rows in the DataSet to match those in the data source.
GetFillParameters()	It is used to get the parameters set by the user when executing an SQL SELECT statement.
ResetFillLoadOption()	It is used to reset FillLoadOption to its default state.
Update(DataSet)	It is used to call the respective INSERT, UPDATE, or DELETE statements.

(B) The Dataset :

• ADO.NET provides a DataSet class that can be used to create a DataSet object.
• A DataSet usually contains several DataTable objects of data i.e., A DataSet is a collection of data tables that contain the data.
• A DataSet in VB.NET is an in-memory representation of a cache of data that can hold multiple tables and their relationships. It’s a powerful object used in ADO.NET to work with disconnected data. You can populate a DataSet from a database, manipulate it in memory, and update the database when needed.
• The most common way to create a DataSet is to use the Fill method of the DataAdapter object.
• This component of ADO .Net is used to access data independently from any data resource.
• A DataSet in VB.NET is a container having multiple tables.
• A DataSet can be treated as a database in program code.
• It’s an in-memory object/representation of data that acts as a mini-database. It contains more than one data table, DataColumn, and DataRow objects at the same time that can store and modify data from one or more databases without maintaining an open connection.
• A DataSet is used to fetch data without interacting with a Data Source that’s why, it is also known as a disconnected data access method.
• The DataSet can also be used to read and write data as an XML document.
• A DataSet method has the following advantages over DataReader: –
  • It caches data locally to our application, so we can manipulate it locally before updating the database.
  • A single DataSet can hold one or multiple related tables as per requirement.
  • It interacts with data dynamically such as binding to windows forms control.
  • It allows performing processing on data without an open connection,i.e., we can work while the connection is disconnected.
  • In other words, DataSet works without maintaining a constant connection to the database. Hence, it is called Disconnected Architecture.
• A data set contains several constructors, properties, and methods to perform data-related operations. These are –

(a.) DataSet Constructors

Constructor	Description
DataSet()	It is used to initialize a new instance of the DataSet class.
DataSet(String)	It is used to initialize a new instance of a DataSet class with the given name.

(b.) DataSet Properties

Properties	Description
CaseSensitive	It is used to check whether DataTable objects are case-sensitive or not.
DataSetName	It is used to get or set the name of the current DataSet.
DefaultViewManager	It is used to get a custom view of the data contained in the DataSet to allow filtering and searching.
HasErrors	It is used to check whether there are errors in any of the DataTable objects within this DataSet.
IsInitialized	It is used to check whether the DataSet is initialized or not.
Namespace	It is used to get or set the namespace of the DataSet.
Tables	It is used to get the collection of tables contained in the DataSet.

(c.) DataSet Methods

Method	Description
BeginInit()	It is used to begin the initialization of a DataSet that is used on a form.
Clear()	It is used to clear the DataSet of any data by removing all rows in all tables.
Clone()	It is used to copy the structure of the DataSet.
Copy()	It is used to copy both the structure and data for this DataSet.
CreateDataReader(DataTable[])	It returns a DataTableReader with one result set per DataTable.
CreateDataReader()	It returns a DataTableReader with one result set per DataTable.
EndInit()	It ends the initialization of a DataSet that is used on a form.
GetXml()	It returns the XML representation of the data stored in the DataSet.
Merge(DataSet)	It is used to merge a specified DataSet and its schema into the current DataSet.
Merge(DataTable)	It is used to merge a specified DataTable and its schema into the current DataSet.
ReadXml(XmlReader, XmlReadMode)	It is used to read XML schema and data into the DataSet using the specified XmlReader and XmlReadMode.
Reset()	It is used to clear all tables and remove all relations, foreign constraints, and tables from the DataSet.

Creation of DataSet

SYNTAX :

• • Step 1 : Import Required ADO .NET Namespaces of VB .Net 

    Imports System.Data

    Imports System.Data.SqlClient

  • Step 2 : Establish a Connection between VB .Net and SQL Server database

    • For this, we use SqlConnection to connect to the database.

  • Step 3 : Now, Create a DataAdapter to fetch data from the database

    • For this, we use a SqlDataAdapter in the case of SQL Server.

  • Step 4 : Now, Create and Fill a DataSet

    • For this, we use the Fill method of the SqlDataAdapter to load data into a DataSet.

  • Step 5 : Now, Bind the Data, if needed (Optional)

    • For this, we bind the DataSet data to a control-like DataGridView to display the data.

  • Step 6 : Finally, Manipulate the Data as per requirement (Optional)

    • For this, we add, update, or delete rows in the DataSet programmatically.

EXAMPLE : 

(i) Creation of a DataSet using a Database

(iii) Creation of a DataSet using XML data/file

• XML DataSet in VB.NET is a powerful way to manage data in disconnected scenarios and sharable it across different media and platforms and can be easily transported or backed up.
• This can be explained with the help of an example –

Advantages of ADO .Net 

• ADO .Net supports Disconnected Architecture i.e. in this, Data retrieved using DataSet can be manipulated offline and synchronized later.
• ADO .Net supports higher Scalability i.e., this connectivity efficiently handles large datasets.
• ADO .Net is Platform Independence i.e., it works with different database systems like SQL Server, Oracle, MySQL, etc. easily.
• ADO .Net easily Integrates/interacts with XML data.

Data Provider for a Database: SQL Server Data Source/Database

• Syntax :

• This Data.SqlClient namespace contains the following important classes that help in Sql Server database connectivity in VB .net. These are –

Class	Description
SqlConnection	This class is used to create a connection with the SQL Server database. This class cannot be inherited.
SqlCommand	This class is used to execute database SQL queries. This class also cannot be inherited.
SqlDataAdapter	This class contains a set of data commands and a database connection that are used to fill the records in the DataSet. This class also cannot be inherited.
SqlDataReader	This class is used to read rows/records from a SQL Server database. This class also cannot be inherited.
SqlException	This class is used to throw SQL exceptions, if any. This class also cannot be inherited.

 NB : SqlConnection class uses SqlDataAdapter and SqlCommand classes together to increase the processing performance when connecting to a Microsoft SQL Server database.

Some Common Methods of SqlConnection Class Used in Connectivity in VB .net

Method	Description
ChangePassword(String, String)	This method changes the SQL Server password; if any, for the user indicated in the connection string.
Close()	This method is used to close the connection from the database.
Open()	This method is used to open a database connection.

Some Common Methods & Constructors of SqlCommand Class Used in Connectivity in VB .net

• This class is used to store and execute SQL statements for SQL Server databases.
• It is a sealed class so that cannot be inherited.

(a) Constructors

Constructors	Description
SqlCommand()	It is used to initialize a new instance of the SqlCommand class.
SqlCommand(String)	It is used to initialize a new instance of the SqlCommand class with a string parameter.
SqlCommand(String, SqlConnection)	It is used to initialize a new instance of the SqlCommand class with two parameters, the first is the query string and the second is the connection string.

(b) Methods

Methods	Description
Cancel()	It cancels the execution of a SqlCommand.
Clone()	It creates a new SqlCommand object that is a copy of the current instance.
ExecuteReader()	It is used to send the CommandText to the Connection and builds a SqlDataReader.
ExecuteXmlReader()	It is used to send the CommandText to the Connection and builds an XmlReader object.
ExecuteScalar()	It executes the query and returns the first column of the first row in the result set. Additional columns or rows are ignored.
Prepare()	It is used to create a prepared version of the command by using the instance of SQL Server.

Working Mechanism of ADO.NET Connectivity with a Database (such as SQL Server Database):

• Step1 : Import the required Namespaces ( such as Imports System.Data and Imports System.Data.SqlClient)
• Step2 : (Connect) Now, a connection is created/established to the data source/databases using the connection (such as SqlConnection ) object.
• Step3 : (Query) Now, execute the required SQL commands (SQL queries or stored procedures) using the command (such as SqlCommand ) object.
• Step4 : (Retrieve Data) Now, fetch the required data using DataReader (such as SqlDataReader) object or DataAdapter object (such as SqlDataAdapter) to populate with a DataSet .
• Step5 : (Manipulate Data) Now, modify the data as per requirements that stored within DataSet objects.
• Step6 : (Update Data) Finally we use DataAdapter (such as SqlDataAdapter) object to reconcile changes and update the database.
• Step6  : We should always close the connection after operations.

These steps can be represented in VB .Net for SQL Server Database in source codes form is as follows –

SYNTAX :

Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        ' Connection string to connect to the database
        Dim connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True"
        
        ' Create a connection object
        Dim connection As New SqlConnection(connectionString)

        Try
            ' Open the connection
            connection.Open()
            Console.WriteLine("Connection Opened Successfully.")

            ' Define a SQL query
            Dim query As String = "SELECT * FROM Customers"

            ' Create a command object
            Dim command As New SqlCommand(query, connection)

            ' Execute the command and get a data reader
            Dim reader As SqlDataReader = command.ExecuteReader()

            ' Read and display the data
            While reader.Read()
                Console.WriteLine("ID: " & reader("CustomerID") & " - Name: " & reader("CustomerName"))
            End While

            ' Close the reader
            reader.Close()
        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)
        Finally
            ' Close the connection
            If connection.State = ConnectionState.Open Then
                connection.Close()
                Console.WriteLine("Connection Closed.")
            End If
        End Try
    End Sub
End Module

EXAMPLE:

Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        ' Connection string
        Dim connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True"

        ' SQL query
        Dim query As String = "SELECT * FROM Products"

        ' Create a connection object
        Dim connection As New SqlConnection(connectionString)

        ' Create a DataAdapter object
        Dim adapter As New SqlDataAdapter(query, connection)

        ' Create a DataSet to hold data
        Dim dataSet As New DataSet()

        Try
            ' Fill the DataSet with data
            adapter.Fill(dataSet, "Products")

            ' Display data from the DataSet
            For Each row As DataRow In dataSet.Tables("Products").Rows
                Console.WriteLine("Product ID: " & row("ProductID") & " - Name: " & row("ProductName"))
            Next
        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)
        End Try
    End Sub
End Module

Data Bound Controls in ADO .Net

• Data-bound controls are UI elements in VB.NET that automatically bind to data sources, such as a database, enabling developers to display and interact with data without manually writing repetitive code for data access or updates.
• Data-bound controls are linked to data sources using a process called Data Binding. This can be achieved either programmatically or through design-time properties.
• Data-bound controls in VB.NET simplify the development of database-driven applications by providing a seamless way to interact with data using ADO.NET. This integration makes it easier to build modern, data-centric applications.
• These controls leverage ADO.NET for data connectivity and manipulation.
• Some commonly used data-bound controls for binding data in VB .Net are –
  • TextBox: This control displays single pieces of data, such as names or IDs, from a data source.
  • Label: This control displays read-only data from a data source.
  • ComboBox: This control displays a list of data from a data source with the ability to select an item.
  • ListBox: This control displays a list of data items with the option to select one or more.
  • DataGridView: This control displays data in a tabular (grid) format for easy visualization and editing.
  • CheckBox: This control displays boolean data.
  • PictureBox: This control is used for binding and displaying images stored in the database.

Working Mechanism of Data Binding in VB.NET

Data Binding occurs in two phases –

• Data Binding Types:

  • Simple Data Binding: It binds a single control to a single data field.
  • Complex Data Binding: It binds a control to multiple records (e.g., DataGridView).

• Data Sources:

  • We can use ADO.NET objects like DataSet, DataTable, DataView, or directly using BindingSource.

Advantages of Data-Bound Controls

• It simplifies data management because it automatically fetches, displays, and updates data.
• It provides user-friendly interfaces for data interaction.
• It is design-time support i.e., it allows binding directly from the Visual Studio designer.

Source Codes Example of Binding Data to a TextBox/DataGridView

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    ' Connection string
    Private connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Create a connection to the database
        Using connection As New SqlConnection(connectionString)
            Try
                ' SQL query to retrieve data
                Dim query As String = "SELECT * FROM Employees"

                ' Create a data adapter
                Dim adapter As New SqlDataAdapter(query, connection)

                ' Create a DataTable to hold the data
                Dim dataTable As New DataTable()

                ' Fill the DataTable with data
                adapter.Fill(dataTable)

                ' Bind the DataTable to the DataGridView
                DataGridView1.DataSource = dataTable

                ' Bind the TextBox to the first row of the DataTable
                If dataTable.Rows.Count > 0 Then
                    TextBox1.DataBindings.Add("Text", dataTable, "EmployeeName")
                End If

            Catch ex As Exception
                MessageBox.Show("Error: " & ex.Message)
            End Try
        End Using
    End Sub
End Class