Avatar of Sam T
Sam T
 asked on

Connection to the azure database timed out

Since moving data to azure, I have experienced many timeout errors regardless of sequence time. The application is the data collection app that uses insert and retrieve stored proc. After collecting roughly 200 to 300 records, I get this exception as shown in figure below.
1123.JPGI don't know if it is because I didn't make my functions to connect to the database asynchronous?  I am using C# and .NET framework. And for the database connection, I am using ADODB.net
.NET ProgrammingDatabasesC#Azure

Avatar of undefined
Last Comment
Sam T

8/22/2022 - Mon
slightwv (䄆 Netminder)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sam T

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.
slightwv (䄆 Netminder)

What is the time between transactions?
Sam T

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

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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

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.
Sam T

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();
         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; }
         {   return true; }
     catch (Exception ex)
         MessageBox.Show("Estiblish Remote Database Connection caused error " + ex.Message);
         return false;

Open 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;
          // 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;
          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;

Open 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?
slightwv (䄆 Netminder)

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:

Have you gone through Microsoft's troubleshooting guide:
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sam T


 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
catch{ //exception statement}
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.