Date:

Share:

Database Record Navigation Example Using ADO.NET

Related Articles

This article illustrates an example of how to navigate database records using ADO.NET. The following example uses VB.NET. While C # code is available here. Basically, the .NET Framework provides the ADO.NET library which includes departments and methods for connecting the application with a database and performing various operations in it.

Creating a database in SQL Server

First, launch Visual Studio and create a Windows Forms application in VB.NET. The following application was created using Visual Studio 2019.

After creating the app, select Explore servers M e View menu. Now press Explore servers And then right-click Add a connection option. The following illustration illustrates this.

Add a connection in SQL Server

Then, enter the name of the database and click OK as the following illustration shows.

Create a new database in SQL Server
Create a new database in SQL Server

If the database does not exist, click the “Yes” option to create it. The database is now visible in the server explorer. More, expand the database and click tables folder. Then right-click Add a new table option. The following illustration illustrates this.

image 14
Add a new table to the SQL Server database.

In addition, create a new table as shown in the figure.

Create a table in SQL Server
Create a table in SQL Server

Now click the Update button and then click Update Database. When you click the Refresh Server Explorer option, the database table becomes visible as shown below.

Database table for an example of navigating database records
Database table for an example of navigating database records

Create a database table

Then right-click on the student table and select Show Table Data. The following illustration shows this.

Adding records to a sample table for navigating database records
Adding records to a sample table for navigating database records

After opening the database table, add some records, save the table data and close it.

Database table records
Database table records

Furthermore, right-click on the database in the server explorer and click on the Properties option as shown below.

Select Properties
Select Properties

Now select the value of the Connection String property and copy it. When we create a Connection object, we must specify the connection string. Therefore, we will use the value of the connection string in the code.

Database connection string
Database connection string

Design a form for an example of navigating database records

Once our database is ready, we can develop the app. Therefore, we can start with designing the form. The following figure shows the form for navigating records in a database.

Form for an example of navigating database records
Form for an example of navigating database records

Full code

Then click on the form and write the code to connect to the database and the appropriate buttons.

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim con As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim i As Integer = 0
    Dim n As Integer
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        con = New SqlConnection("Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=C:UsersKAVITADocumentsStudentDatabase.mdf;Integrated Security=True;Connect Timeout=30")
        con.Open()
        da = New SqlDataAdapter("select * from Student", con)
        ds = New DataSet()
        da.Fill(ds, "Student")
        n = ds.Tables("Student").Rows.Count
        Dim dr As DataRow
        dr = ds.Tables("Student").Rows(0)
        TextBox1.Text = dr(0).ToString
        TextBox2.Text = dr(1).ToString
        TextBox3.Text = dr(2).ToString
        TextBox4.Text = dr(3).ToString
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dr As DataRow
        dr = ds.Tables("Student").Rows(0)
        TextBox1.Text = dr(0).ToString
        TextBox2.Text = dr(1).ToString
        TextBox3.Text = dr(2).ToString
        TextBox4.Text = dr(3).ToString
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim dr As DataRow
        dr = ds.Tables("Student").Rows(n - 1)
        TextBox1.Text = dr(0).ToString
        TextBox2.Text = dr(1).ToString
        TextBox3.Text = dr(2).ToString
        TextBox4.Text = dr(3).ToString
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim dr As DataRow
        If (i = n - 1) Then
            Button4.PerformClick()
        Else
            i = i + 1
            dr = ds.Tables("Student").Rows(i)
            TextBox1.Text = dr(0).ToString
            TextBox2.Text = dr(1).ToString
            TextBox3.Text = dr(2).ToString
            TextBox4.Text = dr(3).ToString
        End If
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim dr As DataRow
        If (i = 0) Then
            Button1.PerformClick()
        Else
            i = i - 1
            dr = ds.Tables("Student").Rows(i)
            TextBox1.Text = dr(0).ToString
            TextBox2.Text = dr(1).ToString
            TextBox3.Text = dr(2).ToString
            TextBox4.Text = dr(3).ToString
        End If
    End Sub
End Class

Productivity

Output an example for navigating a database record
Output of an example for database record navigation

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles