VB.net connecting to SQL Server

Connecting to SQL server 2014 from VB.Net 2017, read values from the database and display the data in windows form.

Before begin to write this program, first we need to import SqlClient class.

Imports System.Data.SqlClient

Step 1

Create a sql connection object. This is to make connection to object.

Dim conn As New SqlConnection()

Step 2

Set the connection string to the sqlconnection object:

conn.ConnectionString = connStr

Road block

The variable connStr is a connection string. Connection object need connection string to make connection to database. It has information like who providing the database, the name of the database and some security information.

Dim connStr As String =
"Data Source = ServerInstanceName;Initial Catalog=testDB;Integrated Security=True"

Let's break down the connection string into the three main components.

Dim provider As String = "ServerInstanceName;"
Dim database As String = "Initial Catalog=testDB;"
Dim security_info As String = "Integrated Security=True"

So,

connStr = "Data source =" + provider + database + security_info

Step 3

open the sql connection.

conn.open()

Step 4

Create an data adapter and pass the sql statements and the opened connection object

Dim dataAdapter As New SqlDataAdapter(sql, conn)

Road block

The sql variable is a string that holds the sql statement.

Dim sql As String = "SELECT * FROM persons"

Slightly longer second option

Create a SqlCommand object, set the command type as text, then assign your desired sql select
statement.

Since we want to execute select statement  use the data adapter select commend property. Assign the prepared command to this property.

Dim cmd As New SqlCommand()
Dim dataAdapter As New SqlDataAdapter

cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM persons"         

dataAdapter.SelectCommand = cmd
dataAdapter.SelectCommand.ExecuteNonQuery()

at this stage we can close the database connection because we are no longer communicating with the database.

conn.close()

Step 5

Create a data set and ask your data adapter to fill the dataset

Dim dataSet as DataSet
dataAdapter.fill(dataSet)

Step 6

Accessing data data set and display the data in window form control.

option 1 display first table in data grid

dataGridView1.dataSource = dataSet.Tables(0)

option 2 display particular cell from the data set in text box

TextBox1.Text = dataSet.Tables(0).Rows(0).Item("name")

exploded version :)

Dim columnName As String = "name"
Dim tableIndex As Integer = 0
Dim rowIndex As Integer = 0

TextBox1.Text = dataSet.Tables(tableIndex).Rows(rowIndex).Item(columnName)

Put everything together

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim provider = "ServerInstanceName;"
        Dim database = "Initial Catalog=testDB;"
        Dim security_info = "Integrated Security=True"

        connStr = "Data source =" + provider + database + security_info      

        Dim conn As New SqlConnection()
        conn.ConnectionString = connStr

        Try
            conn.Open()

            Dim cmd As New SqlCommand()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT * FROM persons"

            Dim dataAdapter As New SqlDataAdapter
            dataAdapter.SelectCommand = cmd
            dataAdapter.SelectCommand.ExecuteNonQuery()
            Dim dataSet As New DataSet        
            dataAdapter.Fill(dataSet)
            DataGridView1.DataSource = dataSet.Tables(0)
            conn.Close()

        Catch ex As SqlException
            MsgBox(ex.ToString)

        End Try    
    End Sub
End Class

This should display all the records in the persons table in data grid view control of the window form.

Discussion is welcome. Please email me at civmook@gmail.com for any assistance...