Write a VB.NET program to create a player table (PID, PName, Game, no_of_matches). Insert records and update the number of matches of ‘Rohit Sharma’ and display the result in a DataGridView.

Answer:

Imports System.Data.SqlClient

Public Class Form1
    Private connectionString As String = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=PlayerDB;Integrated Security=True"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CreateDatabaseAndTable()
        InsertRecords()
        UpdateMatches()
        LoadData()
    End Sub

    Private Sub CreateDatabaseAndTable()
        Using con As New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True")
            con.Open()
            Dim cmd As New SqlCommand("IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'PlayerDB')
                                        CREATE DATABASE PlayerDB", con)
            cmd.ExecuteNonQuery()
        End Using

        Using con As New SqlConnection(connectionString)
            con.Open()
            Dim cmd As New SqlCommand("IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Player')
                                        CREATE TABLE Player (
                                            PID INT PRIMARY KEY IDENTITY,
                                            PName NVARCHAR(50),
                                            Game NVARCHAR(50),
                                            no_of_matches INT
                                        )", con)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub InsertRecords()
        Using con As New SqlConnection(connectionString)
            con.Open()
            Dim cmd As New SqlCommand("IF NOT EXISTS (SELECT * FROM Player WHERE PName='Rohit Sharma')
                                        BEGIN
                                            INSERT INTO Player (PName, Game, no_of_matches) VALUES
                                            ('Rohit Sharma', 'Cricket', 230),
                                            ('Virat Kohli', 'Cricket', 260),
                                            ('Lionel Messi', 'Football', 800)
                                        END", con)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub UpdateMatches()
        Using con As New SqlConnection(connectionString)
            con.Open()
            Dim cmd As New SqlCommand("UPDATE Player SET no_of_matches = 235 WHERE PName = 'Rohit Sharma'", con)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub LoadData()
        Using con As New SqlConnection(connectionString)
            Dim adapter As New SqlDataAdapter("SELECT * FROM Player", con)
            Dim dt As New DataTable()
            adapter.Fill(dt)
            DataGridView1.DataSource = dt
        End Using
    End Sub

    Private Sub btnLoadData_Click(sender As Object, e As EventArgs) Handles btnLoadData.Click
        LoadData()
    End Sub
End Class