VB.Net - Database access

May 13, 2021 vb.net

1. Ado. Net object model

2. The data provider

3. The dataset

4. Connect to the database

5. Example 1

6. 示例2

7. Create tables, columns, and rows

8. 示例3

The application communicates with the database, first, retrieving the data stored there and rendering it in a user-friendly manner, and second, updating the database by inserting, modifying, and deleting the data.

Microsoft ActiveX Data Objects.Net (ADO.Net) is part of a model, the .Net framework, which is used by .Net applications to retrieve, access, and update data.

Ado. Net object model

Ado. T he Net object model is simply a structured process through various components. A n object model can be described as:

Data that resides in a data store or database is retrieved through a data provider. V arious components of the data provider retrieve the application's data and update the data.

The application accesses the data through a dataset or data reader.

Datasets dataset: The dataset stores the data in a disconnected cache from which the application retrieves the data.
Data readers data reads: Data readers provide data to applications in read-only and forward-only modes.

The data provider

The data provider is used to connect to the database, execute commands, and retrieve data, store it in a dataset, and read the retrieved /b123> Data and update the database.

In ADO.Net data provider includes the following four objects:

SN	Objects and descriptions
1	Connection This component is used to set up a connection with a data source. This component is used to establish a connection to the data source.
2	Command A command is a SQL statement or a stored procedure used to retrieve, insert, delete or modify data in a data source. A command is a SQL statement or stored procedure used to retrieve, insert, delete, or modify data from a data source.
3	DataReader Data reader is used to retrieve data from a data source in a read-only and forward-only mode. Data readers are used to retrieve data from a data source in read-only and forward-only mode.
4	DataAdapter This is integral to the working of ADO.Net since data is transferred to and from a database through a data adapter. I t retrieves data from a database into a dataset and updates the database. When changes are made to the dataset, the changes in the database are actually done by the data adapter. This ADO.Net part of the work of the database, as data is transferred to and from the database via a data adapter. I t retrieves data from the database to the dataset and updates the database. W hen you make changes to a dataset, the changes in the database are actually made by the data adapter.

Ado. Net contains the following different types of data providers

SQL Server's .Net Framework Data Provider - Provides access to Microsoft SQL Server.
OLE DB's .Net Framework Data Provider - Provides access to data sources exposed using OLE DB.
ODBC's .Net Framework Data Provider - Provides access to data sources exposed by ODBC.
Oracle's .Net Framework Data Provider - Provides access to Oracle data sources.
EnterpriseClient Provider - Allows data to be accessed through an Entity Data Model (EDM) application.

The dataset

DataSet is the memory represented by the data. I t is a set of disconnected cached records retrieved from the database. W hen a connection is established to the database, the data adapter creates a dataset and stores data in it. A fter the data is retrieved and stored in the data set, the connection to the database is closed. T his is known as the "disconnected architecture." The dataset is used as a virtual database containing tables, rows, and columns.

The following illustration shows the dataset object model:

The DataSet class exists in the System.Data namespace. T he following table describes all the components of DataSet:

SN	Components and instructions
1	DataTableCollection It contains all the tables retrieved from the data source. It contains all the tables retrieved from the data source.
2	DataRelationCollection It contains relationships and the links between tables in a data set. It contains relationships and links between tables in the dataset.
3	ExtendedProperties It contains additional information, like the SQL statement for retrieving data, time of retrieval, etc. It contains other information, such as SQL statements used to retrieve data, time to retrieve, and so on
4	DataTable It represents a table in the DataTableCollection of a dataset. I t consists of the DataRow and DataColumn objects. The DataTable objects are case-sensitive. It represents the table in DataTableCollection of the dataset. I t consists of DataRow and DataColumn objects. D ataTable objects are case sensitive.
5	DataRelation It represents a relationship in the DataRelationshipCollection of the dataset. It is used to relate two DataTable objects to each other through the DataColumn objects. It represents the relationship in the DataRelationshipCollection of the dataset. I t is used to associate two DataTable objects with each other through the DataColumn object.
6	DataRowCollection It contains all the rows in a DataTable. It contains all the rows in DataTable.
7	DataView It represents a fixed customized view of a DataTable for sorting, filtering, searching, editing and navigation. It represents a fixed custom view of DataTable for sorting, filtering, searching, editing, and navigating.
8	PrimaryKey It represents the column that uniquely identifies a row in a DataTable. It represents a column that uniquely identifies a row in DataTable.
9	DataRow It represents a row in the DataTable. T he DataRow object and its properties and methods are used to retrieve, evaluate, insert, delete, and update values in the DataTable. The NewRow method is used to create a new row and the Add method adds a row to the table. It represents a line in DataTable. D ataRow objects and their properties and methods are used to retrieve, evaluate, insert, delete, and update values in DataTable. T he NewRow method is used to create a new row, and the Add method adds a row to the table.
10	DataColumnCollection It represents all the columns in a DataTable. It represents all the columns in DataTable.
11	DataColumn It consists of the number of columns that comprise a DataTable. It consists of the number of columns that make up DataTable.

Connect to the database

. T he Net framework provides two types of Connect classes:

SqlConnection - Designed to connect to Microsoft SQL Server.
OleDbConnection - Designed to connect to a variety of databases, such as Microsoft Access and Oracle.

Example 1

We have a table stored in Microsoft SQL Server called Customers in a database called TestDB. For information about creating databases and database tables in SQL Server, refer to the SQL Server tutorial.

Let's connect to this database. Follow these steps:

Select the tool - connect to the database
Select the server name and database name in the Add Connection dialog box.
Click the Test Connection button to check that the connection was successful.
Add a DataGridView to the form.
Click the Select Data Source combo box.
Click the add project data source link.
This opens the Data Source Configuration Wizard.
Select Database as the data source type
The dataSet selected is used as the database model.
Select the connection you have set up.
Save the connection string.
In our example, select the database object Customers table, and then click the Finish button.
Select the Preview Data link to view the data in the Results grid:

When you run an application using the Start button on the Microsoft Visual Studio toolbar, the following window appears:

Example 2

In this example, let's use code to access the data in the DataGridView control. F ollow these steps:

Add a DataGridView control and a button to the form.
Change the text of the button control to Fill.
Double-click the button control to add the required code to the click event of the button, as follows:

Imports System.Data.SqlClient
Public Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) _
   Handles MyBase.Load
        'TODO: This line of code loads data into the 'TestDBDataSet.CUSTOMERS' table.   You can move, or remove it, as needed.
      Me.CUSTOMERSTableAdapter.Fill(Me.TestDBDataSet.CUSTOMERS)
      ' Set the caption bar text of the form.   
      Me.Text = "tutorialspoint.com"
   End Sub
   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      Dim connection As SqlConnection = New sqlconnection()
      connection.ConnectionString = "Data Source=KABIR-DESKTOP; _
          Initial Catalog=testDB;Integrated Security=True"
      connection.Open()
      Dim adp As SqlDataAdapter = New SqlDataAdapter _
      ("select * from Customers", connection)
      Dim ds As DataSet = New DataSet()
      adp.Fill(ds)
      DataGridView1.DataSource = ds.Tables(0)
   End Sub
End Class

When you execute and run the code above using the Start button on the Microsoft Visual Studio toolbar, the following window is displayed:

Click the Fill button to display the table on the data grid view control:

Create tables, columns, and rows

As we've discussed, DataSet components like DataTable, DataColumn, and DataRow allow us to create tables, columns, and rows separately.

The following example demonstrates this concept:

Example 3

So far, we've used tables and databases that already exist on our computers. I n this example, we'll create a table to which we'll add columns, rows, and data, and display the table using the DataGridView object.

Follow these steps:

Add a DataGridView control and a button to the form.
Change the text of the button control to Fill.
Add the following code to the code editor.

Public Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
      ' Set the caption bar text of the form.   
      Me.Text = "tutorialspont.com"
   End Sub
   Private Function CreateDataSet() As DataSet
      'creating a DataSet object for tables
      Dim dataset As DataSet = New DataSet()
      ' creating the student table
      Dim Students As DataTable = CreateStudentTable()
      dataset.Tables.Add(Students)
      Return dataset
   End Function
   Private Function CreateStudentTable() As DataTable
      Dim Students As DataTable
      Students = New DataTable("Student")
      ' adding columns
      AddNewColumn(Students, "System.Int32", "StudentID")
      AddNewColumn(Students, "System.String", "StudentName")
      AddNewColumn(Students, "System.String", "StudentCity")
      ' adding rows
      AddNewRow(Students, 1, "Zara Ali", "Kolkata")
      AddNewRow(Students, 2, "Shreya Sharma", "Delhi")
      AddNewRow(Students, 3, "Rini Mukherjee", "Hyderabad")
      AddNewRow(Students, 4, "Sunil Dubey", "Bikaner")
      AddNewRow(Students, 5, "Rajat Mishra", "Patna")
      Return Students
   End Function
   Private Sub AddNewColumn(ByRef table As DataTable, _ 
   ByVal columnType As String, ByVal columnName As String)
      Dim column As DataColumn = _ 
       table.Columns.Add(columnName, Type.GetType(columnType))
   End Sub

   'adding data into the table
   Private Sub AddNewRow(ByRef table As DataTable, ByRef id As Integer,_
   ByRef name As String, ByRef city As String)
      Dim newrow As DataRow = table.NewRow()
      newrow("StudentID") = id
      newrow("StudentName") = name
      newrow("StudentCity") = city
      table.Rows.Add(newrow)
   End Sub
   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      Dim ds As New DataSet
      ds = CreateDataSet()
      DataGridView1.DataSource = ds.Tables("Student")
   End Sub
End Class