Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
    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)
            If Cache("risultati") Is Nothing Then
                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.GetSqlValue(4).IsNull = True Or sqldr.GetSqlValue(4).ToString = String.Empty) And (sqldr.GetSqlValue(5).IsNull = True Or sqldr.GetSqlValue(5).ToString = String.Empty) Then
                                    recapiti = "Non disponile"
                                ElseIf sqldr.GetSqlValue(4).IsNull = False And (sqldr.GetSqlValue(5).IsNull = True Or sqldr.GetSqlValue(5).ToString = String.Empty) Then
                                    recapiti = sqldr.GetSqlValue(4).Value
                                ElseIf (sqldr.GetSqlValue(4).IsNull = True Or sqldr.GetSqlValue(4).ToString = String.Empty) And (sqldr.GetSqlValue(5).IsNull = False) Then
                                    recapiti = sqldr.GetSqlValue(5).Value
                                ElseIf sqldr.GetSqlValue(4).IsNull = False And sqldr.GetSqlValue(5).IsNull = False Then
                                    recapiti = sqldr.GetSqlValue(4).Value & " - " & sqldr.GetSqlValue(5).Value
                                End If
                                lista.Add(New PropertyUtente With {.idutente = sqldr.GetSqlInt32(0).Value, _
                                                                    .utente = sqldr.GetSqlString(1).Value.ToUpper.ToUpper, _
                                                                    .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
            End If
           Cache("risultati") = lista
            Return lista
            scope.Complete()
        End Using
    End Function