How to calling a SQL connection method with two different function at a same?

Question

Actually I am trying to call my mysqlconnection function with two different function where one function running continuously in background with the help of TASK and another function run on button click. My code gives me an error when both function call mysqlconnection function at a same time.

Here is MYSQL connection function code:

public void executequeryy(CommandType type, string commandtext)
    {

        try
        {


            cmd.CommandText = commandtext;
            cmd.Connection = PublicClass.conn;
            if (type == CommandType.StoredProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
     
                PublicClass.conn.Open();
                cmd.ExecuteNonQuery();
                PublicClass.conn.Close();
        }
        catch (Exception ex)
        {
            PublicClass.conn.Close();

        }
    }

If anyone have an idea please tell me how can I call this connection method 2 or more times from multiple function at a same time.

Example code which are calling this mysql connection fucntion: continuous run function:

 public async void fillalldata()
 {
   while(true)
   {
     await Task.Run(() =>
     {
         OverallData();   //this function fill data in continuous mode in background.
         //await Task.Delay(2000);
     });
   }
 }

Here is the another example function which is call by buttonclick:

  public void getenvlpdata()
  {
         
            DbClass objdata = new DbClass();
            objdata.executequeryy(CommandType.Text, "insert into OverallData(SensorID,SampleTime,Temperature,TempUnit,AlarmEnabled,AlarmLevel) values('" + SerialNumber + "','" + SampleTime + "','" + OverallValue + "','" + Unit + "','" + AlarmEnabled + "','" + AlarmLevel + "')");
  

     }

You need to show how you are calling function. Like your button click and background you mention. Code helps identify the issue. — AliK
– AliK, Commented Mar 4, 2022 at 7:01

Marc Gravell · Accepted Answer · 2022-03-04 07:32:19Z

1

ADO.NET is not a thread-safe API; you can only talk to a single connection from a single logical flow at once; this could be a single thread, or a single async flow, as long as it properly awaits each call so that things are not overlapped. It appears that you have a single connection here (and possibly a single command, which is a terrible idea); this scenario is not allowed.

Use separate connections for the separate flows.

As a side note (but important):

parameters; do not concatenate data to create SQL
any time you type async void, you're probably doing something wrong

answered Mar 4, 2022 at 7:32

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kamlesh jha · Accepted Answer · 2022-03-04 09:44:26Z

Finally I got a solution to my problem. I have created a two different connection string. one for continuous running function and second one is for single run(after click on button). here is the example code for two connection string:

    //create connection for continuous running function..
    public static void CreateContinuousConnection() 
    {
        try
        {
            PublicClass.continuousconn.ConnectionString = "datasource = localhost;database=wivib_x;username= root; password=1234";

        }
        catch { }
    }



    //create connection for single running function.
    public static void CreateSingleConnection()
    {
        try
        {
            PublicClass.singleconn.ConnectionString = "datasource = localhost;database=wivib_x;username= root; password=1234";

        }
        catch { }
    }

Collectives™ on Stack Overflow

How to calling a SQL connection method with two different function at a same?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related