Imports Microsoft.VisualBasic

Imports System.Data.SqlClient

Imports System.Data

Public Class MyDataAccessLayerClass

Dim Connstring As String = ConfigurationManager.ConnectionStrings("SMTBankConnectionString").ConnectionString

Public Function GetSomeData() As DataSet

Dim cs As String = ConfigurationManager.ConnectionStrings("SMTBankConnectionString").ConnectionString

Dim objConn As New SqlConnection(cs)

Dim objCommand As New SqlCommand

Dim objDa As New SqlDataAdapter

Dim objDs As New DataSet

Dim objCommandBuilder As New SqlCommandBuilder

        objCommand.CommandText = "Select id_reference,[Reference] From [References]"

        objCommand.Connection = objConn

        objDa.SelectCommand = objCommand

        objDa.Fill(objDs, "Table1")

Return objDs

End Function

Public Sub UpdateSomeData(ByVal Reference, ByVal Id_reference)

Dim cs As String = ConfigurationManager.ConnectionStrings("SMTBankConnectionString").ConnectionString

Dim objConn As New SqlConnection(cs)

Dim objcommand As New SqlCommand

Dim QStr As String

        QStr = "UPDATE [References] SET "

        QStr = QStr & "[Reference] = '" & Reference & "'"

        QStr = QStr & " WHERE id_reference = " & Id_reference

        objcommand = New SqlCommand(QStr, objConn)

        objConn.Open()

        objcommand.ExecuteNonQuery()

        objConn.Close()

End Sub

Public Sub DeleteSomeData(ByVal id_reference)

Dim cs As String = ConfigurationManager.ConnectionStrings("SMTBankConnectionString").ConnectionString

Dim objConn As New SqlConnection(cs)

Dim objcommand As New SqlCommand

Dim QStr As String

        QStr = "Delete  From [References] where id_reference = " & id_reference

        objcommand = New SqlCommand(QStr, objConn)

        objConn.Open()

        objcommand.ExecuteNonQuery()

        objConn.Close()

End Sub

Public Sub InsertSomeData(ByVal Reference, ByVal id_bank)

Dim cs As String = ConfigurationManager.ConnectionStrings("SMTBankConnectionString").ConnectionString

Dim objConn As New SqlConnection(cs)

        objConn.Open()

Dim objCommand As New SqlCommand

Dim v1 As String = "'" & Reference & "'"

Dim v2 As String = id_bank

'WebForm Fields....

Dim strValues As String = v1 & "," & v2

'Database Fields...

Dim strFields As String = "[Reference],Id_bank"

        objCommand = New SqlCommand("INSERT INTO [References] (" & strFields & ") VALUES (" & strValues & ")", objConn)

        objCommand.ExecuteNonQuery()

        objConn.Close()

End Sub

Public Sub InsertScalar()

Dim objConn As New SqlConnection

Dim objCommand As New SqlCommand

Dim v1 As String = "19"

Dim v2 As String = "16"

Dim v3 As String = "'hello'"

Dim Values As String = v1 & "," & v2 & "," & v3

Dim FieldNames As String = "Id_Score_File,Id_Exam_Bank,Comment"

        objConn = New System.Data.SqlClient.SqlConnection("Server=STEVESCHIMSKY;Database=Exam;Trusted_Connection=True;")

        objCommand = New SqlCommand("Insert into score_comments (" & FieldNames & ") values(" & Values & ") select scope_identity() as iID_Score_Comment", objConn)

        objConn.Open()

'Select Either (A) or (B) *******************

'(A)...DO NOT RETURN IDENTITY FIELD VALUE...

'objCommand.ExecuteNonQuery()

'(B)...RETURN IDENTITY FIELD VALUE...

'Dim o As Object

'o = objCommand.ExecuteScalar

'********************************************

        objConn.Close()

End Sub

End Class