DAL for Beigners

Imports System.Net
Imports System.Net.WebRequestMethods.Http
Imports System.Data.SqlClient
Imports System.Data

Public Class DataAccess


    Private strConnectionString As String = ""

    Public Sub New()
        strConnectionString = ConfigurationManager.ConnectionStrings("conStr").ConnectionString
    End Sub

    Public Function ExecuteNonQuery(ByVal query As String) As Integer
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("INSERT") Or query.StartsWith("insert") Or query.StartsWith("UPDATE") Or query.StartsWith("update") Or query.StartsWith("DELETE") Or query.StartsWith("delete") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.Text
        End If
        Dim retval As Integer
        Try
            cnn.Open()
            cmd.CommandTimeout = 600
            retval = cmd.ExecuteNonQuery()
        Catch exp As Exception
            Throw exp
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
        Return retval

    End Function

    Public Function ExecuteNonQuery(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As Integer
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("INSERT") Or query.StartsWith("insert") Or query.StartsWith("UPDATE") Or query.StartsWith("update") Or query.StartsWith("DELETE") Or query.StartsWith("delete") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.StoredProcedure
        End If
        Dim i As Integer
        For i = 0 To parameters.Length - 1
            cmd.Parameters.Add(parameters(i))
        Next
        cnn.Open()

        Dim retval As Integer = cmd.ExecuteNonQuery()

        cnn.Close()
        Return retval
    End Function

    Public Function ExecuteScalar(ByVal query As String) As Object
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("SELECT") Or query.StartsWith("select") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.StoredProcedure
        End If
        cnn.Open()
        Dim retval As Object = cmd.ExecuteNonQuery()
        cnn.Close()
        Return retval
    End Function

    Public Function ExecuteScalar(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As Object
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("SELECT") Or query.StartsWith("select") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.StoredProcedure
        End If
        Dim i As Integer
        For i = 0 To parameters.Length - 1
            cmd.Parameters.Add(parameters(i))
        Next
        cnn.Open()
        Dim retval As Object = cmd.ExecuteScalar()
        cnn.Close()
        Return retval
    End Function

    Public Function ExecuteReader(ByVal query As String) As SqlDataReader
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)

        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("SELECT") Or query.StartsWith("select") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.Text
        End If
        cnn.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

    Public Function ExecuteReader(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As SqlDataReader
        Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = New SqlCommand(query, cnn)
        If query.StartsWith("SELECT") Or query.StartsWith("select") Then
            cmd.CommandType = CommandType.Text
        Else
            cmd.CommandType = CommandType.StoredProcedure
        End If
        Dim i As Integer
        For i = 0 To parameters.Length - 1
            cmd.Parameters.Add(parameters(i))
        Next
        cnn.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

    Public Function ExecuteDataSet(ByVal query As String) As DataSet
        Try
            'Global.Agent.LogError(query)
            Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
            Dim cmd As SqlCommand = New SqlCommand(query, cnn)

            If query.StartsWith("SELECT") Or query.StartsWith("select") Then
                cmd.CommandType = CommandType.Text
            Else
                cmd.CommandType = CommandType.Text
                ' cmd.CommandType = CommandType.StoredProcedure
            End If
            Dim da As SqlDataAdapter = New SqlDataAdapter()
            da.SelectCommand = cmd
            cmd.CommandTimeout = 60000
            Dim ds As DataSet = New DataSet()


            da.Fill(ds)
            '  MsgBox("Number of row(s)  -  " & ds.Tables(0).Rows.Count)
            Return ds
        Catch ex As Exception

            'Global.Agent.LogError(ex.Message)
            Return Nothing
        End Try
    End Function

    Public Function ExecuteDataSet(ByVal query As String, ByVal ParamArray parameters() As SqlParameter) As DataSet
        Dim ds As DataSet = New DataSet()
        Try

            Dim cnn As SqlConnection = New SqlConnection(strConnectionString)
            Dim cmd As SqlCommand = New SqlCommand(query, cnn)
            If query.StartsWith("SELECT") Or query.StartsWith("select") Then
                cmd.CommandType = CommandType.Text
            Else
                cmd.CommandType = CommandType.StoredProcedure
            End If
            Dim i As Integer
            For i = 0 To parameters.Length - 1
                cmd.Parameters.Add(parameters(i))
            Next
            Dim da As SqlDataAdapter = New SqlDataAdapter()
            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception

        End Try
        Return ds
    End Function

  
End Class




ConnectionString in web.config