How in ASP.net c# Make a single threaded database insert record class work from multithreaded application?

I have an application that spawns 50 threads for 50 potential incoming socket connections.
The connections are made and all is well - I can send data back and forth to the dfiierent external systems via sockets just great.

However I wanted to insert a record of every message received ( its from electronic locker systems ) into a SQL database for later analysis.

I didnt want to have to buy full SQl and also didnt want to have 50 datatbase updates running when really I thought one common one would do. I can hit the database 100 times a second no problem if only one incoming connection is active but whenever I connect on the second or subsequent socket it fails. Any ideas ?

Console output....................

Connected: 192.168.1.70:1166
Connected: 192.168.1.70:1167
0
0
before db update
before db update
Internal connection fatal error. 3
Routine exited
Invalid attempt to call Read when reader is closed. 3
Routine exited
A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. 1
Disconnected: 192.168.1.70:1166
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. 1
Disconnected: 192.168.1.70:1167
A first chance exception of type 'System.IO.IOException' occurred in System.dll
A first chance exception of type 'System.IO.IOException' occurred in System.dll

// Not a professional programmer so please excuse the poor layout below.
 
 
using System.Windows.Forms;
using System;
using System.Threading;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
class LockerTCPServer
{
    static TcpListener listener;
    const int LIMIT = 50; //50 concurrent clients
 
    public static void Main()
    {
        DataB.Connectsql();                      //open database
        listener = new TcpListener(80);    //listen for incominh socket connections on 80
        listener.Start();
                                                             // open a thread for every possible connection
        Console.WriteLine("Server mounted,listening to port 80");  
        for (int i = 0; i < LIMIT; i++)
        {
            Thread t = new Thread(new ThreadStart(Service));
            t.Start();
        }
    }
 
    public static void Service()   // note this is blocking code but not a prob as its in its own thread
        {
        while (true)
             {
            Socket soc = listener.AcceptSocket();
                Console.WriteLine("Connected: {0}", soc.RemoteEndPoint);
            try
             {
                Stream s = new NetworkStream(soc);
                StreamReader sr = new StreamReader(s);
                StreamWriter sw = new StreamWriter(s);
                sw.AutoFlush = true; // enable automatic flushing
                int cnt = 0;
                while (true)
              {
                    string name = sr.ReadLine();
                    if (name == "" || name == null) break;
                    Console.WriteLine(cnt);
                    DataB.updatelockers("Hellp", 22);//just put anything in db for now
                    cnt++;
              }
                s.Close();
            }
            catch (Exception Ex)
            {
                    Console.WriteLine(Ex.Message + " 1");
            }   
                    Console.WriteLine("Disconnected: {0}",soc.RemoteEndPoint);
 
            soc.Close();
        }
    }
}
 
class DataB
{
    private static SqlConnection datacon = new SqlConnection();
 
    public static void Connectsql()  
    {
        try
        {
           datacon.ConnectionString= "Data Source=COLINMAIN\\SQLEXPRESS; Initial Catalog=sockettestSQL; Integrated Security=True";
            datacon.Open();
            Console.WriteLine("Database Open");
        }
 
        catch (Exception Ex)
        {
            Console.WriteLine(Ex.Message + " 2" );
            Console.WriteLine(Ex.Message + "  " + Ex.Source + " 2");
        } 
    }
 
    public static void updatelockers(String socket,Int16 iteration)  /// note this is not safe for web - sql injection attack
       
        {
           
        try
        {
        String MyString = "INSERT INTO Table1 (socket, iteration) VALUES ('" + socket + "'," + iteration+") ";
        SqlCommand MyCmd = new SqlCommand(MyString, datacon);
        Console.WriteLine("before db update");
        MyCmd.ExecuteScalar();  
        Console.WriteLine("command executed");
        }
        catch (Exception Ex)
        {
            Console.WriteLine(Ex.Message + " 3" );
        }
        Console.WriteLine("Routine exited");
    }
 
 
    public static void Closesql()
    {
    datacon.Close();
    }
 
 
}

Select allOpen in new window