Connectivity in VB .net

Published by Admin on

Table of Contents hide
1. Introduction
2. Types of Connectivity
3. (F) ADO .Net Connectivity
3.1. (A) .NET Framework Data Provider
3.1.1. .NET Framework Data Providers Objects
3.1.1.1. DataAdapter Constructors
3.1.1.2. Methods
3.2. (B) The Dataset :
3.2.1. (a.) DataSet Constructors
3.2.2. (b.)DataSet Properties
3.2.3. (c.) DataSet Methods
3.2.4. Data Provider for a Database: SQL Server Data Source/Database
3.3. Some Common Methods of SqlConnection Class Used in Connectivity in VB .net
3.4. Some Common Methods & Constructors of SqlCommand Class Used in Connectivity in VB .net
3.5. (a)Constructors
3.6. (b)Methods
3.7. Working Mechanism of ADO.NET with SQL Server Database:

Introduction

  • Data Access 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

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

(A) Native SQL connectivity in VB .net (Vendor provided).
(B) Microsoft’s Open Database Connectivity in VB .net(ODBC)
(C) Microsoft’s Data Access Objects (DAO) Connectivity in VB .net
(D) Microsoft’s Remote Data Objects (RDO) Connectivity in VB .net
(E) Microsoft’s Object Linking and Embedding for Database (OLE–DB).
(F) Microsoft’s ActiveX Data Objects (ADO.NET) Connectivity in VB .net
(G) Sun’s Java Database Connectivity (JDBC) Connectivity in VB .net.

(F) ADO .Net Connectivity

  • It is an advanced type of connectivity in VB .Net way with a database.
  • 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.
  • 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 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.
  • 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.
  • ADO .NET has two main components that are used for accessing and manipulating data as a whole. These are – 
(A) .NET Framework Data Provider and
(B) The DataSet.

(A) .NET Framework 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

.NET Framework 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.

.NET Framework Data Provider for OLE DB

  • This provider is used to connect with OLE DB/MS access application.
  • It requires the System.Data.OleDb namespace.

.NET Framework Data Provider for ODBC

  • It is used to connect to data sources/databases by using ODBC connectivity.
  • It requires the System.Data.Odbc namespace.

.NET Framework Data Provider for Oracle

  • It is used to connect with Oracle data sources.
  • It requires the System.Data.OracleClient namespace.

.NET Framework Data Providers Objects

Some core objects of Data Providers that are used in database connectivity are:-

(1.) ADO .Net Connection Object

(2.) ADO .Net Command Object

(3.) ADO .Net DataReader Object

(4.) ADO .Net DataAdapter Object.

Object

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(MS Access/OleDb -OleDbConnection, SQL Server -SqlConnection, ODBC – OdbcConnection) has a Connection class.
  • The ConnectionString, Provider, State, and Mode are common Connection properties. 
  • 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.
  • 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 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 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.
  • 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.

Methods

Method

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.
  • 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 method has the following advantages over DataReader: –
    • It caches data locally to our application, so we can manipulate it locally.
    • 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.
  • 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 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.

    ————————————  X  ————————————

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

    • Syntax :
                 using System.Data.SqlClient;       //in C# .net
                 import System.Data.SqlClient      ‘in Vb .net  
    • 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 with SQL Server Database:

    • Connect: First of all, create a connection to the data source/databases using SqlConnection object.
    • Query: Now, execute commands (SQL queries or stored procedures) using SqlCommand object.
    • Retrieve Data: Now, fetch required data using SqlDataReader or populate a DataSet using SqlDataAdapter.
    • Manipulate Data: Now, modify the data as per requirements that stored within DataSet objects.
    • Update Data: Finally use SqlDataAdapter object to reconcile changes and update the database.
    Categories: VB .Net Theory

    0 Comments

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Related Posts

    VB .Net Theory

    Operator Overloading Examples

    Example : Operator Overloading Examples in VB .Net program to show Binary Addition(+) Operator Overloading. Class Example Dim num1 As IntegerDim num2 As Integer Sub Input(ByVal val1 As Integer, ByVal val2 As Integer)     num1 Read more…

    VB .Net Theory

    Interface Examples

    Example : Interface Examples in VB .Net program to demonstrate the Interface.  Example : Interface Examples in VB .Net program to demonstrate Multiple Inheritance using an Interface. 

    VB .Net Theory

    In-Built Functions

    There are the following categories of in-built functions or library functions available in VB .net :  – (1.) VB Math in-Built Functions CInt() This function is used to convert the value(practically numeric) of different data Read more…