Connection at client database hangs application

Question

On my application I need to connect at client database(SqlServer) only to see if we can connect. These are my connection strings from web.config(the values are not that way, I´ve changed the ip, user and pwd)

<add name="ConnectionStringLibracom" connectionString="Data Source=192.168.1.45\SqlServer2008;Initial Catalog=xxx;user=xxx;pwd=xxx;Application Name=MES"
      providerName="System.Data.SqlClient" /> (MINE)
<add name="ConnectionStringMigplus" connectionString="Data Source=999.99.999.99;Initial Catalog=xxx;user=xxx;pwd=xxx"
          providerName="System.Data.SqlClient" /> (CLIENT)

but this piece of code hangs my entire application(when I say that it hangs, I mean that it dont let my application to connect to our DB). I´m executing it at Default.aspx on Load event:

protected void Page_Load(object sender, EventArgs e)
{
    if (!TestaIntegracaoErpMigplus())
    {
        lblMensagemIntegracao.Visible = true;
        Session["Integracao"] = false;
    }
    else
        Session["Integracao"] = true;
}

protected static bool TestaIntegracaoErpMigplus()
{
    string connectionStringMigplus = WebConfigurationManager.ConnectionStrings["ConnectionStringMigplus"].ConnectionString;
    bool ret = false;

    using (SqlConnection Conn = new SqlConnection(connectionStringMigplus))
    {
        try
        {
            Conn.Open();
            if (Conn.State == ConnectionState.Open)
            {
                ret = true;
            }
        }
        catch (SqlException)
        {
            ret = false;
        }
    }

    return ret;
}

@EDIT: The problem is not if I can connect to the server or not, the problem is: when I´m trying to connect to that db my asp.net website frozen to new requests at others page

Cling

Well catching the exception in that way is really a bad practice. How do you know what error the connection attempt is throwing at you? – Spae

Log the exception you're catching - there will be some info why you can't connect to the database. – Levant

on this method I´m assuming that: if it throw any kind of exception then I could not connect... but this doesn´t seem the problem that I pointed out with my question, sorry being rude. – Cling

I will edit my question, maybe it is not well explained. – Cling

@EDIT: The problem is not if I can connect to the server or not, the problem is: when I´m trying to connect to that db my asp.net website frozen to new requests at others page – Cling

you can try move connection to Task and use Wait function with timeout – Tuneful

@Cling One question it may sound stupid, when you say new request to other pages, Does that mean those pages were active when you try to connect with your Client's connection string ? Can you provide any info at which particular line your code hangs ?I mean another page code ? – Monroy

@Suraj Singh Yeah, they are active using AJAX and i´m doing some requests. The code Hangs at active pages and inactive ones ( I mean, if I go to Default.aspx, and then open a new tab to go to Help.aspx, Help will hang the loading until Default had terminated the method TestaIntegracaoErpMigplus()). Maybe it´s something about sql connection pool? I dont know... Another pagecode that hangs is... wow, didn´t notice it before: it´s on another sqlConnection, but using the other database. I´m not using any kind of singleton approach, so, i´m actually lost here. – Cling

@Cling I do not have full confidence but may be enabling MARS may help you MultipleActiveResultSets=true; . – Monroy

@Suraj Singh I will try it, but the thing is: I´m using two diferent ConnectionStrings, and they use different connections, right? So why is one connection hanging another? – Cling

@Cling possibly it depends on db provider – Tuneful

I suspect it hangs because it's a static method, so all pages call that method. Within that method, connection pooling takes place & that probably has locks to ensure it's thread safe, so first call blocks all subsequent calls. You could switch connection pooling off as well which may stop it blocking all threads. – Quintus

Answer 1

you don't need to check for ConnectionState

If the connection cannot be opened an exception is throw

Maybe this check is causing your issue

protected static bool TestaIntegracaoErpMigplus()
{
    bool ret = true;    

    try
    {
        string connectionStringMigplus = WebConfigurationManager.ConnectionStrings["ConnectionStringMigplus"].ConnectionString; 
        using (SqlConnection Conn = new SqlConnection(connectionStringMigplus))
        {
            Conn.Open();
        }
    }
    catch (Exception)
    {
        ret = false;
    }
    return ret;
}

-------------UPDATE------------------

Try to lower the connection timeout in the connection string:

<add name="ConnectionStringMigplus" connectionString="Data Source=999.99.999.99;Initial Catalog=xxx;user=xxx;pwd=xxx;Connection Timeout=5" providerName="System.Data.SqlClient" />

Ruben

@Cling lower the connection timeout – Ruben

Cant, because we have some expensive querys on that DB... or that´s just a timeout to CONNECT? – Cling

@Cling the time to wait while trying to establish a connection before terminating the attempt and generating an error. – Ruben

Answer 2

You can try move connection to Task and use Wait function with timeout, something like this

protected static bool TestaIntegracaoErpMigplus()
{
    string connectionStringMigplus = WebConfigurationManager.ConnectionStrings["ConnectionStringMigplus"].ConnectionString;
    var task = Task.Factory.StartNew<bool>(()=>{
        bool ret = true;
        using (SqlConnection Conn = new SqlConnection(connectionStringMigplus))
        {
            try
            {
                Conn.Open();
            }
            catch (SqlException)
            {
                ret = false;
            }
        }

        return ret;
    });

    if(task.Wait(/*your timeout in milliseconds*/)){
        return task.Result;
    }

    return false;
}

Tuneful

But it work just because I set the timeout, on the timeout time it still hangs the application... But it´s a nice workaround. – Cling

@Marciano.Andrade, yep, here you can specify only the maximum time it still hangs – Tuneful

This was a nice workaround, but I think that the other answer uses what was expected, a system way to lower the connect timeout method. Thank you very much! – Cling