Check if Database Connection is valid or not using C# and VB.Net in Windows Application

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Hi Everyone,

I have this code that I check if SQL Connection is valid and open or not, So what I need is to get just a message that the connection is not valid and not to stop all my app.

This is the error that I get in con.Open()

Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.) 

    Public Shared Sub SqlConnectionExample()
        Using con As New SqlConnection
            con.ConnectionString = "Data Source=######,1433;Network Library=DBMSSOCN;Initial Catalog=Expenses;User ID=##;Password=###;"
            con.Open()

            If con.State = ConnectionState.Open Then
                Console.WriteLine("SqlConnection Information:")
                Console.WriteLine("  Connection State = " & con.State)
                Console.WriteLine("  Connection String = " & con.ConnectionString)
                Console.WriteLine("  Database Source = " & con.DataSource)
                Console.WriteLine("  Database = " & con.Database)
                Console.WriteLine("  Server Version = " & con.ServerVersion)
                Console.WriteLine("  Workstation Id = " & con.WorkstationId)
                Console.WriteLine("  Timeout = " & con.ConnectionTimeout)
                Console.WriteLine("  Packet Size = " & con.PacketSize)
            Else

                Console.WriteLine("SqlConenction failed to open.")
                Console.WriteLine("  Connection State = " & con.State)
            End If
            con.Close()
        End Using

    End Sub

    Public Shared Sub OleDbConnectionExample()
        Using con As New OleDbConnection
            con.ConnectionString = "Provider = sqloledb;Data Source=#####,1433;Network Library=DBMSSOCN;Initial Catalog=Expenses;User ID=###;Password=####;"
            con.Open()
            If con.State = ConnectionState.Open Then
                Console.WriteLine("OleDbConnection Information:")
                Console.WriteLine("  Connection State = " & con.State)
                Console.WriteLine("  Connection String = " & con.ConnectionString)
                Console.WriteLine("  Database Source = " & con.DataSource)
                Console.WriteLine("  Database = " & con.Database)
                Console.WriteLine("  Server Version = " & con.ServerVersion)
                Console.WriteLine("  Timeout = " & con.ConnectionTimeout)
            Else
                Console.WriteLine("OleDbConnection failed to open.")
                Console.WriteLine("  Connection State = " & con.State)
            End If
            con.Close()
        End Using
    End Sub
    Public Shared Sub Main()
        SqlConnectionExample()
        OleDbConnectionExample()
    End Sub

Thank you Best Regards

You are viewing reply posted by: dharmendr 6 months ago.
Posted 6 months ago

Hi elvisidrizi1,

Use Try Catch. Check this example. Now please take its reference and correct your code.

Namespaces

C#

using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    CheckConnection();
}

public bool CheckConnection()
{
    string conString = "Data Source=.;Initial Catalog=Test;User ID=sa;Password=pass@123;";
    bool isValid = false;
    SqlConnection con = null;
    try
    {
        con = new SqlConnection(conString);
        con.Open();
        isValid = true;
    }
    catch (SqlException ex)
    {
        isValid = false;
        MessageBox.Show("Connection is not valid");
    }
    finally
    {
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
    }

    return isValid;
}

VB.Net

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    CheckConnection()
End Sub

Public Function CheckConnection() As Boolean
    Dim conString As String = "Data Source=.;Initial Catalog=Test;User ID=sa;Password=pass@123;"
    Dim isValid As Boolean = False
    Dim con As SqlConnection = Nothing
    Try
        con = New SqlConnection(conString)
        con.Open()
        isValid = True
    Catch ex As SqlException
        isValid = False
        MessageBox.Show("Connection is not valid")
    Finally
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
    End Try

    Return isValid
End Function