How to check SQL Client-server Connectivity

Question

User-712926555 posted

I am using client server system.

I have one server and 7 client machine.

my connection string in client app.config

<add name="ServerConnection" connectionString="Data Source=Server-PC\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=False;User Id=xxx;Password=xxxxxxxxx;Connection Timeout=1;" providerName="System.Data.SqlClient"/>
<add name="ClientConnection" connectionString="Data Source=Client1\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=False;User Id=xxx;Password=xxxxxxxxx;" providerName="System.Data.SqlClient"/>

Here i am using Connection Timeout=1 to check the server connected or not.

In my aspx.cs

try
{
    using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[ServerConnection].ConnectionString))
    {
        connection.Open();

        // My Process Here from server
    }
}
catch (Exception ex)
{
    using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[ClientConnection].ConnectionString))
    {
        connection.Open();

        // My Process Here from server
    }
}

<div class="post-text" itemprop="text">

Is it correct way?

From this it takes long time to check server is connected or not. If it is not connected then catch part will execute the client machine sql.

It is very slow to check sql connection. any other way to check server connection?

Answer 1

User475983607 posted

I would add a appSetting element that defines the current location rather than relying on a failed SQL connection.

Answer 2

User-712926555 posted

I would add a appSetting element that defines the current location rather than relying on a failed SQL connection.

How to do? any code snippets?

Answer 3

User475983607 posted

Hisanth

How to do? any code snippets?

Simply read an appSetting node for the connection name.

https://odetocode.com/articles/345.aspx

This is pretty basic stuff.  Can you explain the problem yo are trying to solve?

In web apps the connection string is driven by the environment.

https://msdn.microsoft.com/en-us/library/dd465326(v=vs.110).aspx

Answer 4

User753101303 posted

Hi,

Another option could be perhaps to fetch the correct connection string based on the host name or the machine name (this is not a web app ?) or just configure the app with the connection you need.

On my side my very first impression is that you were trying to implement a kind of retry pattern. It's likeky best to be always as direct as possible ie here :

• you don't want to connect to a first machine and connect to another if it fails
• you want to connect to a different machine depending on where your code run
  
  So try to have your code doing exactly that.