vb.net mysql Asynchrones Server-Socket


michael85

Grünschnabel
Hallo,
mein Name ist Michael und ich bin ein Newbie...
Habe vor über ein Asynchronen Client-Socket (Windows) an einen Asynchronen Server-Socket (Linux) Daten zu übermitteln diese zu splitten und dann in eine MySql-Datenbak auf einem Ubuntu-Server zu speichen.

Die übermittlung der Daten, dass Splitten und die Verbindung zu der MySql-Datenbak geht auch. Ein test Eintrag war möglich. Hat natürlich dann "host" "ip" "game" "modx" eingetragen.

Der Client sendet:
Code:
Send(client, ";hostname=paradox1;ip=192.168.178.38;game=DayZ;mod=Epoch 1.0.5.1;<EOF>;")




Code:
     'Save data to mySQL database...
                Console.WriteLine("Write data to mySQL database...")

                'Login to mySQl database....
                Console.WriteLine("Login to mySQL...")

                Dim conMySQL As New MySqlConnection

                Dim MyConnectionString = "Server=xxxxxxxx;port=xxxxx;Database=xxxxxx;Uid=xxxxxx;Pwd=xxxx"



                'MySql command....
                Dim InsertQuery = _
                "INSERT INTO dayzconnector (hostname, ip, game, modx) VALUES ('host', 'ip', 'game', 'modx')"
               

                Dim myCommand As New MySqlCommand(InsertQuery)
                Dim conn As New MySqlConnection(MyConnectionString)
                myCommand.Connection = conn

                conn.Open()

                myCommand.ExecuteNonQuery()


                myCommand.Connection.Close()

                Console.WriteLine("Write data to mySQL database done...")

Nun möchte ich aber die Daten von dem Client in die MySql-Datenbank schreiben und keine festen Werte.
Habe das hier versucht geht aber leider nicht und ich komme nicht weiter.



Code:
Imports System
Imports System.Net
Imports System.Net.Sockets
Imports System.Text
Imports System.Threading
Imports System.Data.Sql
Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient


' State object for reading client data asynchronously

Public Class StateObject
    ' Client  socket.
    Public workSocket As Socket = Nothing
    ' Size of receive buffer.
    Public Const BufferSize As Integer = 1024
    ' Receive buffer.
    Public buffer(BufferSize) As Byte
    ' Received data string.
    Public sb As New StringBuilder
End Class 'StateObject


Public Class AsynchronousSocketListener
    ' Thread signal.
    Public Shared allDone As New ManualResetEvent(False)

    Public Shared Sub Main()
        ' Data buffer for incoming data.
        Dim bytes() As Byte = New [Byte](1023) {}

        ' Establish the local endpoint for the socket.
        Dim ipHostInfo As IPHostEntry = Dns.Resolve(Dns.GetHostName())
        Dim ipAddress As IPAddress = ipHostInfo.AddressList(0)
        Dim localEndPoint As New IPEndPoint(ipAddress, 12000)

        ' Create a TCP/IP socket.
        Dim listener As New Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp)

        ' Bind the socket to the local endpoint and listen for incoming connections.
        listener.Bind(localEndPoint)
        listener.Listen(100)


        While True
            ' Set the event to nonsignaled state.
            allDone.Reset()

            ' Start an asynchronous socket to listen for connections.
            Console.WriteLine("Waiting for a connection...")
            listener.BeginAccept(New AsyncCallback(AddressOf AcceptCallback), listener)

            ' Wait until a connection is made and processed before continuing.
            allDone.WaitOne()
        End While
    End Sub 'Main


    Public Shared Sub AcceptCallback(ByVal ar As IAsyncResult)
        ' Get the socket that handles the client request.
        Dim listener As Socket = CType(ar.AsyncState, Socket)
        ' End the operation.
        Dim handler As Socket = listener.EndAccept(ar)

        ' Create the state object for the async receive.
        Dim state As New StateObject
        state.workSocket = handler
        handler.BeginReceive(state.buffer, 0, StateObject.BufferSize, 0, New AsyncCallback(AddressOf ReadCallback), state)
    End Sub 'AcceptCallback


    Public Shared Sub ReadCallback(ByVal ar As IAsyncResult)
        Dim content As String = String.Empty

        ' Retrieve the state object and the handler socket
        ' from the asynchronous state object.
        Dim state As StateObject = CType(ar.AsyncState, StateObject)
        Dim handler As Socket = state.workSocket

        ' Read data from the client socket.
        Dim bytesRead As Integer = handler.EndReceive(ar)

        If bytesRead > 0 Then
            ' There  might be more data, so store the data received so far.
            state.sb.Append(Encoding.ASCII.GetString(state.buffer, 0, bytesRead))

            ' Check for end-of-file tag. If it is not there, read
            ' more data.
            content = state.sb.ToString()
            If content.IndexOf("<EOF>") > -1 Then

               
                Dim revdata As String = content
                Console.WriteLine("Read {0} bytes from socket. ", content.Length)
                Console.WriteLine("Server-Infos:")

                Dim part() As String
                part = revdata.Split(";")

                Console.WriteLine(part(0)) 'leer
                Console.WriteLine(part(1)) 'hostname
                Console.WriteLine(part(2)) 'ip
                Console.WriteLine(part(3)) 'game
                Console.WriteLine(part(4)) 'mod
                'Console.WriteLine(part(5)) 'ende
                Console.WriteLine(part(6)) 'leer
               
                'Save data to mySQL database...
                Console.WriteLine("Write data to mySQL database...")

                'Login to mySQl database....
                Console.WriteLine("Login to mySQL...")

                Dim conMySQL As New MySqlConnection

                Dim MyConnectionString = "Server=xxxxxxxxxx;port=xxxxx;Database=xxxxxx;Uid=xxxxxxxx;Pwd=xxxxxx"


                Dim sqlCommand As New MySqlCommand
                'MySql command....
                Dim InsertQuery = _
                "INSERT INTO dayzconnector (hostname, ip, game, modx) VALUES (@host, @ip, @game, @modx)"
                sqlCommand.Parameters.AddWithValue("@host", part(1))
                sqlCommand.Parameters.AddWithValue("@ip", part(2))
                sqlCommand.Parameters.AddWithValue("@game", part(3))
                sqlCommand.Parameters.AddWithValue("@mod", part(4))

                Dim myCommand As New MySqlCommand(InsertQuery)
                Dim conn As New MySqlConnection(MyConnectionString)
                myCommand.Connection = conn

                conn.Open()

                myCommand.ExecuteNonQuery()


                myCommand.Connection.Close()

                Console.WriteLine("Write data to mySQL database done...")
                ' Echo the data back to the client.
                Send(handler, content)
            Else
                ' Not all data received. Get more.
                handler.BeginReceive(state.buffer, 0, StateObject.BufferSize, 0, New AsyncCallback(AddressOf ReadCallback), state)
            End If
        End If
    End Sub 'ReadCallback

    Private Shared Sub Send(ByVal handler As Socket, ByVal data As String)
        ' Convert the string data to byte data using ASCII encoding.
        Dim byteData As Byte() = Encoding.ASCII.GetBytes(data)

        ' Begin sending the data to the remote device.
        handler.BeginSend(byteData, 0, byteData.Length, 0, New AsyncCallback(AddressOf SendCallback), handler)
    End Sub 'Send


    Private Shared Sub SendCallback(ByVal ar As IAsyncResult)
        ' Retrieve the socket from the state object.
        Dim handler As Socket = CType(ar.AsyncState, Socket)

        ' Complete sending the data to the remote device.
        Dim bytesSent As Integer = handler.EndSend(ar)
        Console.WriteLine("Sent {0} bytes to client.", bytesSent)

        handler.Shutdown(SocketShutdown.Both)
        handler.Close()
        ' Signal the main thread to continue.
        allDone.Set()
    End Sub 'SendCallback
End Class 'AsynchronousSocketListener

Was mache ich falsch? Wäre über eure Hilfe sehr dankbar!

M.f.G Micha
 

Spyke

Premium-User
Probier mal anstatt @ ein ? anzugeben als Parameterangabe.

Und ev. bei Parameters.AddWithValue den Parameternamen mal ohne @ angeben.

Seh grad du erstellt ein neues MySqlCommand für deine Insert Anweisung, wenn ich das gerade richtig überfliege hast du aber die Parameter bei einem anderen Command angegeben.
Kurz gesagt, prüf mal ob du wirklich die parameter beim richtigen Command angegeben hast.
 

michael85

Grünschnabel
Danke für deine schnelle Antwort.

Habe es schon gefixt.
@ war nicht das Problem. Aber du hast recht mit dem command war mit einer der Fehler.

Wichtig ist: Allow User Variables=True

Code:
Dim MyConnectionString As String = "Server=xxxxxxxxx;port=xxxxxx;Database=xxxxxxxxxxx;Uid=xxxxxxxxxxxx;Pwd=xxxxxx;Allow User Variables=True"

So geht der Code:

Code:
Imports System
Imports System.Net
Imports System.Net.Sockets
Imports System.Text
Imports System.Threading
Imports System.Data
Imports System.Data.Sql
Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient


' State object for reading client data asynchronously

Public Class StateObject
    ' Client  socket.
    Public workSocket As Socket = Nothing
    ' Size of receive buffer.
    Public Const BufferSize As Integer = 1024
    ' Receive buffer.
    Public buffer(BufferSize) As Byte
    ' Received data string.
    Public sb As New StringBuilder
End Class 'StateObject


Public Class AsynchronousSocketListener
    ' Thread signal.
    Public Shared allDone As New ManualResetEvent(False)

    ' This server waits for a connection and then uses  asychronous operations to
    ' accept the connection, get data from the connected client,
    ' echo that data back to the connected client.
    ' It then disconnects from the client and waits for another client.
    Public Shared Sub Main()
        ' Data buffer for incoming data.
        Dim bytes() As Byte = New [Byte](1023) {}

        ' Establish the local endpoint for the socket.
        Dim ipHostInfo As IPHostEntry = Dns.Resolve(Dns.GetHostName())
        Dim ipAddress As IPAddress = ipHostInfo.AddressList(0)
        Dim localEndPoint As New IPEndPoint(ipAddress, 12000)

        ' Create a TCP/IP socket.
        Dim listener As New Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp)

        ' Bind the socket to the local endpoint and listen for incoming connections.
        listener.Bind(localEndPoint)
        listener.Listen(100)


        While True
            ' Set the event to nonsignaled state.
            allDone.Reset()

            ' Start an asynchronous socket to listen for connections.
            Console.WriteLine("Waiting for a connection...")
            listener.BeginAccept(New AsyncCallback(AddressOf AcceptCallback), listener)

            ' Wait until a connection is made and processed before continuing.
            allDone.WaitOne()
        End While
    End Sub 'Main


    Public Shared Sub AcceptCallback(ByVal ar As IAsyncResult)
        ' Get the socket that handles the client request.
        Dim listener As Socket = CType(ar.AsyncState, Socket)
        ' End the operation.
        Dim handler As Socket = listener.EndAccept(ar)

        ' Create the state object for the async receive.
        Dim state As New StateObject
        state.workSocket = handler
        handler.BeginReceive(state.buffer, 0, StateObject.BufferSize, 0, New AsyncCallback(AddressOf ReadCallback), state)
    End Sub 'AcceptCallback


    Public Shared Sub ReadCallback(ByVal ar As IAsyncResult)
        Dim content As String = String.Empty


        ' Retrieve the state object and the handler socket
        ' from the asynchronous state object.
        Dim state As StateObject = CType(ar.AsyncState, StateObject)
        Dim handler As Socket = state.workSocket

        ' Read data from the client socket.
        Dim bytesRead As Integer = handler.EndReceive(ar)

        If bytesRead > 0 Then
            ' There  might be more data, so store the data received so far.
            state.sb.Append(Encoding.ASCII.GetString(state.buffer, 0, bytesRead))

            ' Check for end-of-file tag. If it is not there, read
            ' more data.
            content = state.sb.ToString()
            If content.IndexOf("<EOF>") > -1 Then

                'Put Server-Info-line in revdata.
                Dim revdata As String = content
                'Write in console read bytes from socket.
                Console.WriteLine("Read {0} bytes from socket. ", content.Length)
                'Write in console the Server-Infos.
                Console.WriteLine("Server-Infos:")

                Dim part() As String
                part = revdata.Split(";")
                Dim hostL As String = part(1)
                Dim ipL As String = part(2)
                Dim portL As String = part(3)
                Dim gameL As String = part(4)
                Dim modxL As String = part(5)

                Dim hostdb() As String
                hostdb = hostL.Split("=")
                Dim host As String = hostdb(1)

                Dim ipdb() As String
                ipdb = ipL.Split("=")
                Dim ip As String = ipdb(1)

                Dim portdb() As String
                portdb = portL.Split("=")
                Dim port As String = portdb(1)

                Dim gamedb() As String
                gamedb = gameL.Split("=")
                Dim game As String = gamedb(1)

                Dim modxdb() As String
                modxdb = modxL.Split("=")
                Dim modx As String = modxdb(1)


                Console.WriteLine(part(0)) 'leer
                Console.WriteLine(part(1)) 'hostname
                Console.WriteLine(part(2)) 'ip
                Console.WriteLine(part(3)) 'port
                Console.WriteLine(part(4)) 'game
                Console.WriteLine(part(5)) 'mod
                'Console.WriteLine(part(6)) 'ende
                Console.WriteLine(part(7)) 'leer

                'Save data to mySQL database...
                Console.WriteLine("Write data to mySQL database...")

                'Login to mySQl database....
                Console.WriteLine("Login to mySQL...")

                Dim conMySQL As New MySqlConnection

                Dim MyConnectionString As String = "Server=xxxxxxxxx;port=xxxxxx;Database=xxxxxxxxxxx;Uid=xxxxxxxxxxxx;Pwd=xxxxxx;Allow User Variables=True"

              

                'MySql command....
              
                Dim Command As New MySqlCommand
                Dim conn As New MySqlConnection(MyConnectionString)
                Command.Connection = conn
                conn.Open()
                Try
                    Command.CommandText = "INSERT INTO dayzconnector (hostname, ip, port, game, modx) VALUES (@host, @ip, @port, @game, @modx)"
                    Command.Parameters.AddWithValue("@host", host)
                    Command.Parameters.AddWithValue("@ip", ip)
                    Command.Parameters.AddWithValue("@port", port)
                    Command.Parameters.AddWithValue("@game", game)
                    Command.Parameters.AddWithValue("@modx", modx)
                    Command.ExecuteNonQuery()
                Catch ex As MySqlException
                    MsgBox(ex.ToString)
                End Try

                Command.Connection.Close()

                Console.WriteLine("Write data to mySQL database done...")
                ' Echo the data back to the client.
                Send(handler, content)
            Else
                ' Not all data received. Get more.
                handler.BeginReceive(state.buffer, 0, StateObject.BufferSize, 0, New AsyncCallback(AddressOf ReadCallback), state)
            End If
        End If
    End Sub 'ReadCallback

    Private Shared Sub Send(ByVal handler As Socket, ByVal data As String)
        ' Convert the string data to byte data using ASCII encoding.
        Dim byteData As Byte() = Encoding.ASCII.GetBytes(data)

        ' Begin sending the data to the remote device.
        handler.BeginSend(byteData, 0, byteData.Length, 0, New AsyncCallback(AddressOf SendCallback), handler)
    End Sub 'Send


    Private Shared Sub SendCallback(ByVal ar As IAsyncResult)
        ' Retrieve the socket from the state object.
        Dim handler As Socket = CType(ar.AsyncState, Socket)

        ' Complete sending the data to the remote device.
        Dim bytesSent As Integer = handler.EndSend(ar)
        Console.WriteLine("Sent {0} bytes to client.", bytesSent)

        handler.Shutdown(SocketShutdown.Both)
        handler.Close()
        ' Signal the main thread to continue.
        allDone.Set()
    End Sub 'SendCallback
End Class 'AsynchronousSocketListener

Gruß Micha