gistfile1.vb

Imports Nest
Imports Dapper

Module Module1

    Sub Main()
        Dim setting = New ConnectionSettings(New Uri("http://texsrv7:9200")).SetDefaultIndex("cases")
        Dim client = New ElasticClient(setting)
        Console.WriteLine("Indexing")
        Dim stopwatch = New Stopwatch
        stopwatch.Start()
        client.Stats()
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("Time to getstats {0}", stopwatch.ElapsedMilliseconds)
        Console.ForegroundColor = ConsoleColor.White
        stopwatch.Stop()
        stopwatch.Restart()
        client.DeleteIndex(Of TexCaseElastic)()
        stopwatch.Stop()
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("Time to delete index {0}", stopwatch.ElapsedMilliseconds)
        Console.ForegroundColor = ConsoleColor.White
        Using con = New SqlClient.SqlConnection(String.Format("Server={0};Database={1};Trusted_Connection=True;Application Name={2}", "texsrv3", "TexDatabase2012_test", "elasticsearch_test"))
            Console.WriteLine("Gettting texcases from the database")
            stopwatch.Restart()
            Dim texcasesSql = con.Query(Of TexCaseSql)("select casename, pimsnumber, datein, dateout, dateassignement, datereport, regarding, placeofcrime, dateofcrime,magistratecasenumber, mission, comments, magistrateid, casestatusid, languageid,PoliceContactId,LabContactId, CityId,TypeId from texcase")
            stopwatch.Stop()
            Dim id = 1
            Dim texcasesElastic = New List(Of TexCaseElastic)
            Console.WriteLine("Found {0} texcases", texcasesSql.Count)
            For Each texcaseSql In texcasesSql
                'Console.WriteLine("indexing texcase {0}", id)
                texcasesElastic.Add(New TexCaseElastic With {.Id = id, .CaseName = texcaseSql.CaseName, .PimsNumber = texcaseSql.PimsNumber, .Mission = texcaseSql.Mission, .Comments = texcaseSql.Comments, .Regarding = texcaseSql.Regarding, .DateAssignement = texcaseSql.DateAssignement, .Magistrate = New Contact With {.Id = texcaseSql.MagistrateId}})
                id += 1
            Next
            Console.ForegroundColor = ConsoleColor.Red
            Console.WriteLine("Time to get texcases from database {0}", stopwatch.ElapsedMilliseconds)
            Console.ForegroundColor = ConsoleColor.White
            Console.WriteLine("indexing texcases {0}", texcasesElastic.Count)
            stopwatch.Restart()
            client.IndexMany(Of TexCaseElastic)(texcasesElastic)
            stopwatch.Stop()
            Console.ForegroundColor = ConsoleColor.Red
            Console.WriteLine("Time to insert texcases in elasticsearch {0}", stopwatch.ElapsedMilliseconds)
            Console.ForegroundColor = ConsoleColor.White
        End Using
        client.Refresh()
        Console.WriteLine("Getting texcase")
        stopwatch.Restart()
        Dim ids = client.Search(Of TexCaseElastic)(Function(x) x.QueryString("jan").Size(20))
        stopwatch.Stop()
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("Time to search for texcases jan {0}", stopwatch.ElapsedMilliseconds)
        Console.ForegroundColor = ConsoleColor.White
        Console.WriteLine("Found {0} texcases", ids.Documents.Count)
        Console.WriteLine("Outputting data")
        PrintTexcaseElastics(ids.Documents.ToList)
        stopwatch.Restart()
        ids = client.Search(Of TexCaseElastic)(Function(x) x.Query(Function(y) y.Term(Function(z) z.CaseName, "TEX00300".ToLower)).Size(20))
        stopwatch.Stop()
        Console.ForegroundColor = ConsoleColor.Red
        Console.WriteLine("Time to search for TEX00300 {0}", stopwatch.ElapsedMilliseconds)
        Console.ForegroundColor = ConsoleColor.White
        Console.WriteLine("Found {0} texcases", ids.Documents.Count)
        Console.WriteLine("Outputting data")
        PrintTexcaseElastics(ids.Documents.ToList)
        Console.ReadLine()
    End Sub

    Public Sub PrintTexcaseElastics(ByVal texcases As IList(Of TexCaseElastic))
        For Each texcase2 In texcases
            PrintTexcaseElastic(texcase2)
        Next
    End Sub

    Public Sub PrintTexcaseElastic(ByVal texcase As TexCaseElastic)
        Console.WriteLine("Id        : {0}", texcase.Id)
        Console.WriteLine("Casename  : {0}", texcase.CaseName)
        Console.WriteLine("Regarding : {0}", texcase.Regarding)
        Console.WriteLine("Mission   : {0}", texcase.Mission)
        Console.WriteLine("Comments  : {0}", texcase.Comments)
        Console.WriteLine("***************")
    End Sub

    Public Class TexCaseElastic
        Public Property Id As Integer
        <ElasticProperty(Index:=FieldIndexOption.not_analyzed)>
        Public Property CaseName As String
        Public Property PimsNumber As String
        Public Property CaseStatus As String
        Public Property DateIn As Nullable(Of DateTime)
        Public Property DateOut As Nullable(Of DateTime)
        Public Property DateAssignement As Nullable(Of DateTime)
        Public Property DateReport As Nullable(Of DateTime)
        Public Property Regarding As String
        Public Property Magistrate As Contact
        Public Property PoliceContact As Contact
        Public Property LabContact As Contact
        Public Property City As String
        Public Property Type As String
        Public Property Language As String
        Public Property PlaceOfCrime As String
        Public Property DateOfCrime As Nullable(Of DateTime)
        Public Property MagistrateCaseNumber As String
        Public Property Mission As String
        Public Property Comments As String
    End Class

    Public Class TexCaseSql
        Public Property Id As Integer
        Public Property CaseName As String
        Public Property PimsNumber As String
        Public Property CaseStatusId As Integer
        Public Property DateIn As Nullable(Of DateTime)
        Public Property DateOut As Nullable(Of DateTime)
        Public Property DateAssignement As Nullable(Of DateTime)
        Public Property DateReport As Nullable(Of DateTime)
        Public Property Regarding As String
        Public Property MagistrateId As Integer
        Public Property PoliceContactId As Integer
        Public Property LabContactId As Integer
        Public Property CityId As Integer
        Public Property TypeId As Integer
        Public Property LanguageId As Integer
        Public Property PlaceOfCrime As String
        Public Property DateOfCrime As Nullable(Of DateTime)
        Public Property MagistrateCaseNumber As String
        Public Property Mission As String
        Public Property Comments As String
    End Class

    Public Class Contact
        Public Property Id As Integer
        Public Property LastName As String
        Public Property FirstName As String
        Public Property Gender() As String
        Public Property WorkFunction() As String
        Public Property Organisation() As String
        Public Property Street() As String
        Public Property Housenumber() As String
        Public Property Community() As String
        Public Property PostalCode() As String
        Public Property Postalbox() As String
        Public Property Country As String
    End Class

End Module