?
Solved

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Posted on 2013-11-21
6
Medium Priority
?
623 Views
Last Modified: 2013-11-27
I have been having an issue connecting to a SQL database that I can’t seem to figure out.
I have created a very simple static function that checks a SQL database.  The database contains the NSRL (National Software Reference Library).  This is used to keep track of known system files.  It is useful when you are searching for user created content and you want to exclude known system files.

Originally I created this as a static class.  I then tested it using a console application.  Everything worked well.  I was able to scan every file on my computer and list which ones where known system files.

Now I am trying to implement this into my Windows Form application and I continue to get the error message below:

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

The error occurs on the following line:

command.ExecuteNonQuery();


I have checked my SQL connection string, at it is the same as in my test application.  My Windows Form and Test Console Application are also both running on the same machine.
I also went back and made this a non-static class and I get the same error message.  Below I have the code for my class.  I can’t seem to find the difference between how my Windows Form call this class as opposed to my test console application.

I have written all of this in C# 4.5 and SQL 2008 RC2

Any help would be greatly appreciated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;


namespace helper
{
    public class NSRLClass
    {

        public SqlConnection globalSQLConnection;

        public void startSqlConnection(string sqlConnectionString)
        {
            if (globalSQLConnection == null)
            {
                globalSQLConnection = new SqlConnection(sqlConnectionString);
                globalSQLConnection.Open();
            }

            if (globalSQLConnection == null)
            {
                globalSQLConnection = new SqlConnection(sqlConnectionString);
                globalSQLConnection.Open();
            }

        }

        public void closeSqlConnection()
        {
            if (globalSQLConnection != null)
            {
                globalSQLConnection.Close();
            }
        }

        public bool isInNSRL(string sqlConnectionString, string md5String, string fileSizeString, ref string OpSystemCodeString)
        {

            bool returnBool = false;

            if (globalSQLConnection == null)
            {
                globalSQLConnection = new SqlConnection(sqlConnectionString);
                globalSQLConnection.Open();
            }

            SqlConnection connection = new SqlConnection(sqlConnectionString);

            SqlCommand command = new SqlCommand("dbo.[pr_checkMd5AndSize]", globalSQLConnection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@md5", md5String);
            command.Parameters.AddWithValue("@filesize", fileSizeString);

            SqlParameter nameSqlParameter = new SqlParameter("@isInNsrl", SqlDbType.Int);
            nameSqlParameter.Direction = ParameterDirection.Output;
            command.Parameters.Add(nameSqlParameter);

            SqlParameter nameSqlParameter2 = new SqlParameter("@OpSystemCode", SqlDbType.VarChar, 50);
            nameSqlParameter2.Direction = ParameterDirection.Output;
            command.Parameters.Add(nameSqlParameter2);

            command.ExecuteNonQuery();

            OpSystemCodeString = nameSqlParameter2.Value.ToString();

            if (Convert.ToInt32(nameSqlParameter.Value.ToString()) == 0)
            {
                returnBool = false;
            }
            else
            {
                returnBool = true;
            }

            return returnBool;

        }

    }
}

Open in new window

0
Comment
Question by:rye004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39667115
If you execute that procedure directly in SQL server does it return results in timely manner?
0
 

Author Comment

by:rye004
ID: 39667132
Yes it does.  SQL Server Management indicates it takes 0 seconds.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39667147
Then the problem is in your C# code in teh way you execute the sp. If you will execute it as an SQL statement with:

sql+query="EXEC dbo.[pr_checkMd5AndSize] @md5='"+md5String+"', @filesize='"+fileSizeString+"'"

and then execute that sql_query

should work.

I am not sure why you add 2 more parameters to that procedure in your code.

As a note I am not very familiar with C# and that way to execute procedures.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:rye004
ID: 39667610
Thank you for getting back to me.  I have tried what you have stated, and it still does not work.  This just really has me stumped why a static console application will work and a windows form will not.

In regards to the other parameters:

@isInNsrl is used to indicate if the file is a known file type.  I realized now that I could have just used the default return property.

@OpSystemCode is used to tell you the type of file, if it is a known file being returned.
0
 
LVL 1

Accepted Solution

by:
psreloaded earned 1000 total points
ID: 39668177
Hi, you seem to have created two connection object and are using only one. Secondly, you seem to have opened the globalSQLConnection but never closed it. It should be avoided. Use the using construct instead which takes care of disposing the connection. I am not too sure whether thats the cause of this issue. Other than that the code looks pretty simple.

Did you run SQL profiler to check if the procedure is getting executed at all when called from the code?
0
 

Author Comment

by:rye004
ID: 39681951
I determined that this was due to my SQL database.  I created an index and worked performance, that seemed to do it.
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question