Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class ConnectedData

    Public Function SelectStudents() As List(Of Student)
        Dim studentList As New List(Of Student)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim reader As SqlDataReader

        ' Create a connection object providing it with the
        ' connection string to link it to the database
        '
        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        ' Create a command and provide it with the SQL statement
        ' we wish to run on the database.
        ' We must also provide a SqlConnection object so it knows
        ' which database to execute this query on.
        '
        cmd = New SqlCommand("SELECT * FROM Student", conn)

        ' The using statement ensures that the connection
        ' will always be closed when we've finished with it.
        '
        Using conn
            ' Open the connection to the database
            '
            conn.Open()

            ' Execute the command using a ExecuteReader()
            ' call which will return an SqlDataReader
            ' we can then use to read the ResultSet
            '
            reader = cmd.ExecuteReader()

            ' Now we can just read each record.
            ' To return a list of records lets add them
            ' a list.
            '
            Do While reader.Read()
                Dim newStudent As Student

                ' Create new Student object to store
                ' the values of the records fields
                '
                newStudent = New Student
                newStudent.Name = CType(reader("Name"), String)
                newStudent.Age = CType(reader("Age"), Integer)
                newStudent.Course = CType(reader("Course"), String)
                newStudent.Id = CType(reader("Id"), String)

                ' Add the new Student to the list
                '
                studentList.Add(newStudent)
            Loop
        End Using

        Return studentList
    End Function

    Public Function SelectTheStudent(ByVal Id As Integer) As List(Of Student)
        Dim studentList As New List(Of Student)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim reader As SqlDataReader

        ' Create a connection object providing it with the
        ' connection string to link it to the database
        '
        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        ' Create a command and provide it with the SQL statement
        ' we wish to run on the database.
        ' We must also provide a SqlConnection object so it knows
        ' which database to execute this query on.
        '
        cmd = New SqlCommand("SELECT * FROM Student WHERE Id=@Id", conn)
        cmd.Parameters.AddWithValue("@Id", Id)

        ' The using statement ensures that the connection
        ' will always be closed when we've finished with it.
        '
        Using conn
            ' Open the connection to the database
            '
            conn.Open()

            ' Execute the command using a ExecuteReader()
            ' call which will return an SqlDataReader
            ' we can then use to read the ResultSet
            '
            reader = cmd.ExecuteReader()

            If reader.Read() = True Then
                Dim newStudent As Student

                ' Create new Student object to store
                ' the values of the records fields
                '
                newStudent = New Student
                newStudent.Name = CType(reader("Name"), String)
                newStudent.Age = CType(reader("Age"), Integer)
                newStudent.Course = CType(reader("Course"), String)
                newStudent.Id = CType(reader("Id"), String)

                ' Add the new Student to the list
                '
                studentList.Add(newStudent)
            End If
        End Using

        Return studentList
    End Function

    Public Sub InsertStudent(ByVal Name As String, ByVal Age As Integer, ByVal Course As String)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand

        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        cmd = New SqlCommand("INSERT Student(Name,Age,Course) VALUES(@Name,@Age,@Course)", conn)
        cmd.Parameters.AddWithValue("@Name", Name)
        cmd.Parameters.AddWithValue("@Age", Age)
        cmd.Parameters.AddWithValue("@COurse", Course)

        Using conn
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    End Sub

    Public Sub DeleteStudent(ByVal Id As Integer)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand

        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        cmd = New SqlCommand("DELETE FROM Student WHERE Id=@Id", conn)
        cmd.Parameters.AddWithValue("@Id", Id)

        Using conn
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    End Sub

    Public Sub UpdateStudent(ByVal id As Integer, ByVal Name As String, ByVal Age As Integer, ByVal Course As String)
        Dim conn As SqlConnection
        Dim cmd As SqlCommand

        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        cmd = New SqlCommand("UPDATE Student SET Name=@Name,Age=@Age,Course=@Course WHERE Id=@Id", conn)
        cmd.Parameters.AddWithValue("@Id", id)
        cmd.Parameters.AddWithValue("@Name", Name)
        cmd.Parameters.AddWithValue("@Age", Age)
        cmd.Parameters.AddWithValue("@COurse", Course)

        Using conn
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using

    End Sub

    Public Function GetMax() As Integer
        Dim result As Integer
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim obj As Object

        result = 0

        conn = New SqlConnection(WebConfigurationManager.ConnectionStrings("studentdbconnstring").ConnectionString)

        cmd = New SqlCommand("SELECT MAX(Age) FROM Student", conn)

        Using conn
            conn.Open()
            obj = cmd.ExecuteScalar()
            If Not obj Is Nothing Then
                result = CType(obj, Integer)
            End If
        End Using

        Return result

    End Function

End Class