DataAccess VB

Imports System.Collections.Generic
Imports System.Linq
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationManager
Imports System.DirectoryServices
Public Class DataAccess
    Inherits MasterDAL
    Property Domain As String

#Region "Get DB details"

    Private Property ProjectID As String

    Public Function GetDBContent(ByVal SPName As String, ByVal Param As String, ByVal ParamValue As String) As DataSet
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand(SPName, sqlcon)
            If Param <> "" Then
                sqlcmd.Parameters.AddWithValue(Param, ParamValue)
            End If
            sqlcmd.CommandType = CommandType.StoredProcedure

            Try
                sqlcon.Open()
                Dim ds As New DataSet("DataSet")
                Dim adp As New SqlDataAdapter(sqlcmd)
                adp.Fill(ds)

                Return ds
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "USERS"
    Public Function Users_Detail(ByVal DomainID As String) As DataTable
        With Me
            .AddInputParam("@DomainID", DomainID)
            Return .GetDataTable("Users_Detail", True)
        End With
    End Function

    Public Function Users_LoginAttempt(ByVal username As String, ByVal stats As String, ByVal ipAddress As String, ByVal browserName As String) As String
        With Me
            .AddInputParam("@Username", rValue(username))
            .AddInputParam("@Stats", rValue(stats))
            .AddInputParam("@IPAddress", rValue(ipAddress))
            .AddInputParam("@BrowserName", rValue(browserName))
            Return .ExecScalar("Users_UpdateLoginAttempt", True)
        End With
    End Function

#End Region

#Region "ROLES"
    Public Function Role_GetName(ByVal RoleId As Long) As String
        With Me
            .AddInputParam("@RoleID", RoleId)
            Return .ExecScalar("Role_GetName", True)
        End With
    End Function

#End Region

#Region "PROFILES"
    Public Function getAccessByRoleID(ByVal roleID As String) As DataTable
        With Me
            .AddInputParam("@roleID", roleID)
            Return .GetDataTable("Profiles_GetAccessByRoleID", True)
        End With
    End Function

    Public Function getSubMenuAccess(ByVal roleID As String) As DataTable
        With Me
            .AddInputParam("@roleID", roleID)
            Return .GetDataTable("RolesMenu_GetMenuByRoleID", True)
        End With
    End Function
#End Region

#Region "GET INITIAL ACTIVITIES FOR PLANNED DATE"
    Public Function getIniPlanDate(ByVal ParentActivityID As String) As DataSet
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("test_activity", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ParentActivityID", ParentActivityID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Dim ds As New DataSet("DataSet")
                Dim adp As New SqlDataAdapter(sqlcmd)
                adp.Fill(ds)
                Return ds
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "GET ACTUAL DATES OF FINAL ACTIVITY"
    Public Function getIniActDate(ByVal ParentActivityID As String) As DataSet
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("test_activity", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ParentActivityID", ParentActivityID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Dim ds As New DataSet("DataSet")
                Dim adp As New SqlDataAdapter(sqlcmd)
                adp.Fill(ds)

                Return ds
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "GET ACTUAL DATES OF FINAL ACTIVITY"
    Public Function getIniSubPlanDate(ByVal ActivityID As String) As DataSet
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("Activity_GetSubActivity", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ActivityID", ActivityID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Dim ds As New DataSet("DataSet")
                Dim adp As New SqlDataAdapter(sqlcmd)
                adp.Fill(ds)
                Return ds
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "GET INITIAL ACTIVITIES AND DELIVERABLES"
    Public Function getIniDeliverables(ByVal ParentActivityID As String) As DataSet
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("Deliverables_GetDetails", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ParentActivityID", ParentActivityID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Dim ds As New DataSet("DataSet")
                Dim adp As New SqlDataAdapter(sqlcmd)
                adp.Fill(ds)

                Return ds
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "INSERT TO PROJECT"
    Public Function saveProjectPlan(ByVal ProjectName As String, ByVal Description As String, ByVal Status As String) As String
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("ProjectDetails_Insert", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ProjectName", ProjectName))
            sqlcmd.Parameters.Add(New SqlParameter("@Description", Description))
            sqlcmd.Parameters.Add(New SqlParameter("@Status", Status))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                'Dim ds As New DataSet("DataSet")
                'Dim adp As New SqlDataAdapter(sqlcmd)
                'ProjectID = Convert.ToString(sqlcmd.ExecuteScalar())
                'adp.Fill(ds)
                Return Convert.ToString(sqlcmd.ExecuteScalar())
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "GET PARENT DESCRIPTION"
    Public Function getParentDescs(ByVal ParentActivityID As String) As String
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("ParentActivity_getDesc", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ParentActivityID", ParentActivityID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Return Convert.ToString(sqlcmd.ExecuteScalar)
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "GET PROJECT DESCRIPTION"
    Public Function getProjDesc(ByVal ProjectID As String) As String
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("ProjectDetails_getDesc", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ProjectID", ProjectID))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Return Convert.ToString(sqlcmd.ExecuteScalar)
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "INSERT TO INITIAL PLAN DATES"
    Public Function saveIniPlanDates(ByVal ProjectID As String, ByVal ActivityID As String, ByVal ActivityType As String, ByVal PlannedStartDate As String, ByVal PlannedEndDate As String) As String
        Using sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectPlanConnectionString").ConnectionString)
            Dim sqlcmd As New SqlCommand("InitialPlanDate_Insert", sqlcon)
            sqlcmd.Parameters.Add(New SqlParameter("@ProjectID", ProjectID))
            sqlcmd.Parameters.Add(New SqlParameter("@ActivityID", ActivityID))
            sqlcmd.Parameters.Add(New SqlParameter("@ActivityType", ActivityType))
            sqlcmd.Parameters.Add(New SqlParameter("@PlannedStartDate", PlannedStartDate))
            sqlcmd.Parameters.Add(New SqlParameter("@PlannedEndDate", PlannedEndDate))
            sqlcmd.CommandType = CommandType.StoredProcedure
            Try
                sqlcon.Open()
                Return Convert.ToString(sqlcmd.ExecuteScalar())
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                sqlcon.Close()
                sqlcmd = Nothing
            End Try
        End Using
    End Function
#End Region

#Region "Sum Code"
    Private Function rValue(ByVal properties As String) As String
        If IsNothing(properties) Then
            Return String.Empty
        ElseIf properties.Equals(String.Empty) Then
            Return String.Empty
        Else
            Return properties
        End If
    End Function
#End Region
End Class