Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Public Class GestoreUtente
    Public Function Search(ByVal _Str1 As String, ByVal _Str2 As String) As List(Of PropertyUtente)
        Using scope As New TransactionScope()
            Dim lista As New List(Of PropertyUtente)
            Try
                Dim conString = ConfigurationManager.ConnectionStrings("TrycontactString")
                Dim strConnString As String = conString.ConnectionString
                Using db As New SqlConnection(strConnString)
                    db.Open()
                    Using sqlcmd As New SqlCommand("Sp_searching_like", db)
                        sqlcmd.CommandType = CommandType.StoredProcedure
                        sqlcmd.Parameters.AddWithValue("@Search", SqlDbType.VarChar).Value = _Str1
                        sqlcmd.Parameters.AddWithValue("@Searchluogo", SqlDbType.VarChar).Value = _Str2
                        Using sqldr As SqlDataReader = sqlcmd.ExecuteReader
                            While sqldr.Read
                                Dim _image As String
                                If sqldr.GetSqlString(6).IsNull = True Then
                                    _image = "/image/avatarborder.png"
                                Else
                                    _image = sqldr.GetSqlString(6).Value
                                End If
                                Dim recapiti As String = ""
                                If sqldr.GetSqlString(4).Value = String.Empty And sqldr.GetSqlString(5).Value = String.Empty Then
                                    recapiti = "Non disponile"
                                ElseIf sqldr.GetSqlString(4).Value <> String.Empty And sqldr.GetSqlString(5).Value = String.Empty Then
                                    recapiti = sqldr.GetSqlString(4).Value
                                ElseIf sqldr.GetSqlString(4).Value = String.Empty And sqldr.GetSqlString(5).Value <> String.Empty Then
                                    recapiti = "(cell.) " & sqldr.GetSqlString(5).Value
                                ElseIf sqldr.GetSqlString(4).Value <> String.Empty And sqldr.GetSqlString(5).Value <> String.Empty Then
                                    recapiti = sqldr.GetSqlString(4).Value & " (cell.) " & sqldr.GetSqlString(5).Value
                                End If
                                lista.Add(New PropertyUtente With {.idutente = sqldr.GetSqlInt32(0).Value, _
                                                                    .utente = sqldr.GetSqlString(1).Value, _
                                                                    .Desccategoria = sqldr.GetSqlString(2).Value, _
                                                                    .Desccomune = sqldr.GetSqlString(3).Value, _
                                                                    .Telefono = recapiti, _
                                                                    .Imageprofile = _image, _
                                                                    .Email = sqldr.GetSqlString(7).Value})
                            End While
                        End Using
                    End Using
                End Using
            Catch ex As SqlException
                Exit Try
            Catch ex As Exception
                Exit Try
            End Try
            Return lista
            scope.Complete()
        End Using
    End Function
End Class