Avatar of rye004
rye004
Flag for United States of America asked on

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

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

C#Microsoft SQL Server 2008.NET Programming

Avatar of undefined
Last Comment
rye004

8/22/2022 - Mon
Zberteoc

If you execute that procedure directly in SQL server does it return results in timely manner?
rye004

ASKER
Yes it does.  SQL Server Management indicates it takes 0 seconds.
Zberteoc

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
rye004

ASKER
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.
ASKER CERTIFIED SOLUTION
psreloaded

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rye004

ASKER
I determined that this was due to my SQL database.  I created an index and worked performance, that seemed to do it.