Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Collections.Generic
Imports System.Text
Imports System.Data.SqlClient
Namespace Departamentos

	Public Class AccesoDatos

		Public Sub New()
		End Sub

        Public Function ObtenerDepartamentos(ByVal grupo As String) As List(Of Departamento)
            Dim departamentos As List(Of Departamento) = New List(Of Departamento)
            Dim query As StringBuilder = New StringBuilder
            query.Append("SELECT ")
            query.Append("DepartmentID, ")
            query.Append("Name, ")
            query.Append("ModifiedDate ")
            query.Append("FROM [HumanResources].[Department] ")
            query.AppendFormat("WHERE GroupName = '{0}' ", grupo)
            query.Append("Order By Name")
            Dim command As SqlCommand = New SqlCommand
            command.CommandText = query.ToString

            Dim conn As SqlConnection = Nothing

            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ADVENTUREWORKSConnectionString").ConnectionString)
                command.Connection = conn
                conn.Open()
                ' Using 
                Dim dr As SqlDataReader = command.ExecuteReader
                Try
                    If dr.HasRows Then
                        While dr.Read
                            Dim dep As Departamento = New Departamento(Convert.ToInt32(dr(0)), dr(1).ToString, grupo, Convert.ToDateTime(dr(2)))
                            departamentos.Add(dep)
                        End While
                    End If
                Finally
                    CType(dr, IDisposable).Dispose()
                End Try
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
            Return departamentos
        End Function

        Public Function ObtenerGrupos() As List(Of String)
            Dim grupos As List(Of String) = New List(Of String)
            Dim query As StringBuilder = New StringBuilder
            query.Append("Select ")
            query.Append("GroupName ")
            query.Append("FROM [HumanResources].[Department] ")
            query.Append("Group By GroupName")
            Dim command As SqlCommand = New SqlCommand
            command.CommandText = query.ToString
            Dim conn As SqlConnection = Nothing
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ADVENTUREWORKSConnectionString").ConnectionString)
                command.Connection = conn
                conn.Open()
                ' Using 
                Dim dr As SqlDataReader = command.ExecuteReader
                Try
                    If dr.HasRows Then
                        While dr.Read
                            grupos.Add(dr(0).ToString)
                        End While
                    End If
                Finally
                    CType(dr, IDisposable).Dispose()
                End Try
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
            Return grupos
        End Function
	End Class 
End Namespace
