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
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
549 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
  • 3
  • 2
6 Comments
 
LVL 26

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 26

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 250 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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