CODEWITHSUNDEEP
c#asp.netsql-serversqlconnection
Marciano.Andrade
Marciano.Andrade

Reputation: 306

Connection at client database hangs application

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

Upvotes: 6

Views: 2178

Answers (2)

giammin
giammin

Reputation: 18958

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" />

Upvotes: 5

Grundy
Grundy

Reputation: 13381

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;
}

Upvotes: 4

Related Questions