Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Data.SqlClient

<ServiceContract(Namespace:="")>
<SilverLightFaultBehavior()>
<AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)>
Public Class UsersRoles
  <OperationContract()> _
  Public Function Users() As List(Of String)
    Dim ls As List(Of String) = Nothing
    Dim strConexion As String = ConfigurationManager.
      ConnectionStrings("ApplicationServices").ConnectionString
    Dim ConexionConBD As New SqlConnection(strConexion)
    Using ConexionConBD
      ' Crear una consulta
      Dim Consulta As String =
        "SELECT aspnet_Users.UserName, aspnet_Roles.RoleName " &
        "FROM aspnet_Roles INNER JOIN " &
        "aspnet_UsersInRoles ON aspnet_Roles.RoleId = " &
        "aspnet_UsersInRoles.RoleId INNER JOIN " &
        "aspnet_Users ON aspnet_UsersInRoles.UserId = " &
        "aspnet_Users.UserId"
      Dim OrdenSql As New SqlCommand(Consulta, ConexionConBD)
      ' Abrir la conexión con la base de datos
      ConexionConBD.Open()
      ' ExecuteReader hace la consulta y devuelve un SqlDataReader
      Dim Lector As SqlDataReader = OrdenSql.ExecuteReader()
      ' Llamar siempre a Read antes de acceder a los datos
      ls = New List(Of String)()
      While Lector.Read() ' siguiente registro
        ls.Add(Lector("UserName") + ",  " + Lector("RoleName"))
      End While
      ' Llamar siempre a Close una vez finalizada la lectura
      Lector.Close()
    End Using
    Return ls
  End Function
End Class
