Connection to the azure database timed out

At first, I connected to the SQL DB on my local computer and left it open. It executed function to call stored procedure on the database. I had no issue of this timed out error, I could insert or select record smoothly.  I migrated the database to Azure, got the connection time out exception after 200 transactions. And as you said, when I call each function to insert records on the database, I established/opened the connection but didn't close after I made transaction to the sql storedproc. Let me close the connection on each sql stroed proc invocation, and see if it is gonna solve the issue.

What is the time between transactions?

the time transaction between each insert of records are
2020-04-15 21:54:49.443, 2020-04-15 21:54:53.647

cloudrec.png
I have also shown on the picture below inside red loop.
I am opening and closing connection on each sqldatabase stored procedure function but after recording many number of records, it fails. I don't know what to pin point for this issue. Is it because I am using ADODDB.net, what if I transition it to ADO.NEt, somewhere I read that ADO.net is 3 to 4 times faster that ADODB.net . link http://codeclimber.net.nz/archive/2009/02/20/if-you-are-still-wondering-how-fast-is-adonet-vs/

If any expert is looking on my issue right now please let me know if switching from ADODB.net to ADO.net would be any beneficial or not? / IS there any other way that my azure database connection would be reliable?

If the transactions are that quick, shouldn't need to create new connections.  I was thinking they had a good amount of time between them and they might have been timing out.

I've flagged the questions as Neglected to see if it pulls in additional Experts.

To elaborate it: this is my function call to establish connection to the database

public static bool EstablishLocConnection(cDBCS cs)
 {
     //declare local variables
     string s1 = cs.Server;
     string s3 = cs.User;
     string s4 = cs.PW;
     string s2 = cs.Catalog;
     string StrConnectString = null;
     g_conn = new ADODB.Connection();
     try
     {
         StrConnectString = "Provider=SQLOLEDB;" + "Data Source= " + s1 + ";Initial Catalog=" + s2;
         g_conn.Open(StrConnectString, s3, s4);
         if ((g_conn.State) != Convert.ToInt32(ADODB.ObjectStateEnum.adStateOpen))
         {   return false; }
         else
         {   return true; }
     }
     catch (Exception ex)
     {
         MessageBox.Show("Estiblish Remote Database Connection caused error " + ex.Message);
         return false;
     }
 }

Select allOpen in new window

This is one of my insert function to store data to the database

public long InsertRotorSID(cPart r, ADODB.Connection objconn)
  {
      ADODB.Command objcmd = new ADODB.Command();
      objcmd.ActiveConnection = objconn;
      object objAffected;
      objcmd.CommandText = "usp_InsertRotorRecord";
      objcmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
      try
      {
          // Job Identification Number
          objcmd.Parameters.Append(objcmd.CreateParameter("P1", ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamInput, 0, r.JobID));
 
          // Equipment Line Number
          objcmd.Parameters.Append(objcmd.CreateParameter("P2", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0, r.Line));
          // Login User Name
          objcmd.Parameters.Append(objcmd.CreateParameter("P3", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 50, r.UserName));
          // Returned Rotor Serail Number
          objcmd.Parameters.Append(objcmd.CreateParameter("SN", ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamOutput));
          // Returned Database Generated Rotor Identification Number
          objcmd.Parameters.Append(objcmd.CreateParameter("RID", ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamOutput));
          objcmd.Execute(out objAffected);
          GO.PLC.SN = Convert.ToInt32(objcmd.Parameters["SN"].Value);
          GO.PLC.DBR = Convert.ToInt32(objcmd.Parameters["RID"].Value);
          objcmd = null;
          GDCO.gRDB_Conn.Close();
          return 0;
      }
      catch (Exception ex)
      {
          MessageBox.Show("Insert rotor serial number caused error " + ex.Message, "Insert New Rotor Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
          return 1;
      }
  }

Select allOpen in new window

Do I need to clean-up my source code, for example use the Using Keyword so that it properly dispose the object when I close the connection?

Any reason you aren't using the native driver and using ADO and OLE?

Not sure that is the issue of Azure your connections timing out but does seem a little old school.

Take a look here:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-dotnet-visual-studio


Have you gone through Microsoft's troubleshooting guide:
https://azure.microsoft.com/en-us/blog/sql-azure-connectivity-troubleshooting-guide/

 Looks like I didn't dispose the connection properly, so what I did was on my code blocks in try catch block, I added  .Close() inside the
try{//statements}
catch{ //exception statement}
finally{
connection.Close()
}
And, I did this for all my function that were using Azure and local SQL server. It ran smoothly and did almost 2000 transaction and I stopped it. I am going to increase the count of transaction and redo the software testing.

@slightwv I appreciate your help and reference. Yes, I am just reusing the old school but I realized that I need to use new ADO.net framework.