Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Use Connection State Property for SQL Server with SqlConnection Class – Querychat

In the article, we learn how to get the current connection state for SQL Server using the SqlConnection class. This article also provides an example of using the SqlConnection state property in VB code to check the connection state to the SQL Server database.

Information about connections, whether it is open or closed, will help us greatly when accessing the database. Although the information will be used very rarely, we can still get information from the connection properties in our application.

Connection state enumeration

A connection can be determined using the connection state property, which contains the enumeration value. Here is the VB code that represents the enumeration value of the connection state:

Public Enumeration ConnectionState

The returning values of the ConnectionState is an enumeration that can be used by the object properties. The member values of the connection state ​​will consist of as follows:

Member Value Description
Closed 0 Closed connection to the data source
Open 1 Open connection to the data source
Connecting 2 Connecting to the data source
Executing 4 Connection object runs a command
Fetching 8 Connection object retrieves data from the source
Broken 16 The connection to the data source is broken once the connection is opened and may be reopened.

Table 1. The members of the connection state values

SqlConnection class properties for SQL Server

The SqlConnection class represents a connection to the database of the SQL Server. Once the connection is configured, it can return information, one of which is the most current connection state information, whether open or closed. Here are the properties of the SqlConnection class object:

Property Name Description
Connection String The entry point is the connection to the data source by using the server name, user name, and password of the database.
Attributes Contains the connection object attributes
Default database Contains the default name of the database source
Provider Contains the provider name of the connections
Mode Contains the provider access permission of the provider
State Contains the most current connection state before connection opening or closing

Table 2. The properties of the SqlConnection object

Examples 

Below are steps to demonstrate how we get the current connection state using the SqlConnection class in VB code. The connection aims to access a SQL Server database named MyDB in the (local)\MySQLSERVER instance with a user id sa and password 123.

Step #1 A provider for SQL Server

SqlClient is a data provider that has a functionality to connect to the SQL Server database. We put the provider namespace at the beginning of our code as follows:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Step #2 Create a connection using SqlConnection

Two ways that we can use to create a connection, using a connection string in app.config or web.config file and the other one using the SqlConnection class. We can create a connection to the SQL Server database using the SqlConnection class as follows:

Private MyConnString As String = "data source=(local)\MYSQLSERVER;database=MyDB;uid=sa;password=123;connection timeout=0;Max Pool Size=32767;Pooling=True"

Private MySqlConn As New SqlConnection(MyConnString)

Step #3 Run and get the SqlConnection state

We open a connection with the MySqlConn.Open() method. An open or closed connection state will display a message, and when it is a broken connection, then it will close the connection using the MySqlConn.Close() method in the exclusion block. Please take a look at the following code:

Try

   MySqlConn.Open()

   If MySqlConn.State <> ConnectionState.Open Then

       MessageBox.Show("The current connection state is now open", "Information", MessageBoxButtons.OK)

   Else

       MessageBox.Show("The current connection state is now closed", "Information", MessageBoxButtons.OK)

   End If

Catch ex As Exception

   MySqlConn.Close()

End Try

By executing the above code, an open connection state will result as follows:

Figure 1. An open connection state

While the connection is closed, the state will result as follows:

Figure 2. A closed connection state

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc