• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

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();
    }
 
 
}

Open in new window

0
colinmacalpine
Asked:
colinmacalpine
1 Solution
 
crazymanCommented:
You could syncronise the updatelockers call, however im not sure exactly how this will affect your socket thread.

eg

private static volatile object syncRoot = new object();

public static void updatelockers(String socket,Int16 iteration)  /// note this is not safe for web - sql injection attack
       
        {
           lock(syncRoot)
{
        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");
}
    }


Or you could just add each request to a volatile list and have another thread process the list, this way you wont block the socket thread while the insert happens...
 

0
 
colinmacalpineAuthor Commented:
Ok like the sound of that - I'll try it and report back - understand the lock may be an issue if the updates are happening rapidly.. But I think the socket thread would just block itself until the database returned.. we will tryit and see,
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now