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:
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:
|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:
|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
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:
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.