After socket error, saved prodecude does not work

Question

After socket error, saved prodecude does not work

I am developing a Socket server using TCPListener running on Windows service, .NET Framework 4.6.1, Entity Framework 6.1.3 and C #. As a database, I use SQL Server Enterprise 2012 SP 2.

I am having problems with my database after I get a socket timeout on my socket client. Every time I get a socket timeout (or any other problem) on the client side, I get strange behavior in my database.

I have a socket server that uses a database stored procedure to update a table:

public TcpListenerServer(int serverPort)
{
    port = serverPort;
    connectionString =
        ConfigurationManager.ConnectionStrings["DbContext"].ConnectionString;

    tcpListenerServer = null;
    codesReceived = new CodesReceived();
    lockCodesUpdate = new System.Object();
    locked = false;
}

public void InitTCPServer()
{
    // Create server.
    IPEndPoint ipAddress = new IPEndPoint(IPAddress.Any, port);
    tcpListenerServer = new TcpListener(ipAddress);

    // Create cancellation token.
    tokenSource = new CancellationTokenSource();
    token = tokenSource.Token;

    // Start the service
    tcpListenerServer.Start(5);

    // Begin an asynchronous connection attemp
    tcpListenerServer.
        BeginAcceptTcpClient(new AsyncCallback(DoAcceptTcpClientCallback),
                             tcpListenerServer);
}

public void Stop()
{
    if (tcpListenerServer != null)
        tcpListenerServer.Stop();
}

private void DoAcceptTcpClientCallback(IAsyncResult ar)
{
    TcpClient client = null;

    try
    {
        // Get the listener that handles the client request.
        TcpListener listener = (TcpListener)ar.AsyncState;

        // End the operation
        client = listener.EndAcceptTcpClient(ar);

        // Handle the data received
        Task.Run(() => { HandleCodeReceived(client, token); }, token);

        // Begin a new asynchronous connection attemp
        tcpListenerServer.
            BeginAcceptTcpClient(new AsyncCallback(DoAcceptTcpClientCallback),
                                 tcpListenerServer);
    }
    catch (SocketException ex)
    {
        log.ErrorFormat("DoAcceptTcpClientCallback socket error: {0}, Message {1}", ex.ErrorCode, ex.Message);
        Stop();
    }
    catch (ObjectDisposedException)
    {
        return;
    }
}

private void HandleCodeReceived(TcpClient client, CancellationToken token)
{
    // client could be null or not be connected when it reach this method?
    if ((client != null) && (client.Connected))
    {
        try
        {
            // Read input message.
            NetworkStream netStream = client.GetStream();

            StreamReader reader = new StreamReader(netStream);
            string messageReceived = null;

            // The while do this: read ALL the lines sent.
            while ((messageReceived = reader.ReadLine()) != null)
            {
                // If we have a valid message
                if ((!string.IsNullOrWhiteSpace(messageReceived)) &&
                    (IsValid(messageReceived)))
                {
                    // Parse message received from client.
                    ClientMessage message =
                        new ClientMessage(messageReceived);

                    // First check if code is valid.
                    if ((string.IsNullOrWhiteSpace(message.Code)) ||
                        (message.Code.Equals("0")))
                    {
                        SendBackMessage(false, client);
                    }
                    else
                    {
                        ProcessCodeReceived(client, message);
                    }
                }
                else
                    SendBackMessage(false, client);
            }
        }
        catch (Exception ex)
        {
            log.Error(ex.Message);

            SendBackMessage(false, client);
        }
    }
}

private bool IsValid(string messageReceived)
{
    string[] cadenas = messageReceived.Split(":".ToCharArray());

    return ((cadenas != null) && (cadenas.Length == 8));
}

private void ProcessCodeReceived(TcpClient client, ClientMessage message)
{
    DateTime begin = DateTime.UtcNow;

    log.DebugFormat("ReadCode: {0} - Start", message.Code);

    StoredProcedureErrors result;
    result = ReadCode(message.Code, message.Nivel);

    if (result != StoredProcedureErrors.NoError)
        log.ErrorFormat(ReadCodeErrorMsg, result, message.Code);
    else
        codesReceived.Reset();

    DateTime end = DateTime.UtcNow;

    log.DebugFormat("ReadCode: {0} - End: {1} - {2}", message.Code, (end - begin).TotalMilliseconds, result);

    SendBackMessage(result == StoredProcedureErrors.NoError, client);
}

private void SendBackMessage(bool isCorrect, TcpClient client)
{
    if (isCorrect)
        client.GetStream().Write(ASCIIEncoding.UTF8.GetBytes(CorrectMsg + Environment.NewLine), 0, 4);
    else
        client.GetStream().Write(ASCIIEncoding.UTF8.GetBytes(IncorrectMsg + Environment.NewLine), 0, 4);
}

private StoredProcedureErrors ReadCode(
    string code,
    byte aggregationlevel)
{
    StoredProcedureErrors result =
        StoredProcedures.ReadCode(
            connectionString,
            code,
            aggregationlevel,
            UserName,
            Source,
            true);

    return result;
}

And the code that runs the stored procedure:

public static StoredProcedureErrors ReadCode(
    string connectionString,
    string code,
    byte codeLevel,
    string param1,
    string param2,
    bool isAuto)
{
    GenericRepository<Code> repository = null;
    LDbContext dbContext = new LDbContext(connectionString);

    repository = new GenericRepository<Code>(dbContext);

    string procName = "ReadCode";

    List<SqlParameter> parameters = null;
    SqlParameter helperCodeParam = null;

    SqlParameter codeParam = new SqlParameter();
    codeParam.ParameterName = "@code";
    codeParam.SqlDbType = System.Data.SqlDbType.NVarChar;
    codeParam.Size = 20;
    codeParam.Direction = System.Data.ParameterDirection.Input;
    codeParam.Value = code;

    [ ... ]

    parameters = new List<SqlParameter>();
    parameters.Add(codeParam);
    parameters.Add(aggregationLevelParam);
    parameters.Add(param1Param);
    parameters.Add(param2Param);
    parameters.Add(isAutoParam);

    if (helperCodeParam != null)
        parameters.Add(helperCodeParam);

    parameters.Add(returnValueParam);

    repository.Execute(procName, parameters);

    return (StoredProcedureErrors)Convert.ToInt32(returnValueParam.Value);
}

Method repository.Execute:

public void Execute(
    string storedProcedureName,
    IEnumerable<SqlParameter> parameters)
{
    SqlConnection connection = null;

    try
    {
        connection =
            dbContext.Database.Connection as SqlConnection;
        connection.Open();

        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = storedProcedureName;
        cmd.CommandType = CommandType.StoredProcedure;

        if (parameters != null)
            cmd.Parameters.AddRange(parameters.ToArray());

        cmd.Connection = connection;

        cmd.ExecuteNonQuery();
    }
    finally
    {
        if (connection != null)
            connection.Close();
    }
}