C# - The SqlConnection Class

The SqlConnection class provides all the properties and methods needed to make a connection to a Microsoft SQL database.

Properties and Methods of the SqlConnection Class

Item Description
Properties
ConnectionString Contains the database connection string
ConnectionTimeout Contains the timeout for the connection in seconds
Database Contains the name of the database to connect to
DataSource Contains the name of the connected server
PacketSize Contains the size of packets used to communicate with the server
ServerVersion Contains the version of SQL that the server is running
State Contains the state of the connection
WorkstationId Contains the NetBIOS identifier of the machine hosting the Web form
Methods
BeginTransaction() Places the connection into a transaction and returns the newly created SqlTransaction object
ChangeDatabase() Enables the developer to change to a different database programmatically
Close() Closes the connection to the current database
CreateCommand() Returns a new command object
Open() Opens the connection to the database
SqlConnection.ConnectionString

Syntax

String ConnectionString

Description

The ConnectionString property contains the database connection string.

Example

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=localhost; 
            User Id=sa; Password=; Initial Catalog=northwind";
SqlConnection.ConnectionTimeout

Syntax

Int32 ConnectionTimeout

Description

ConnectionTimeout is a read-only property that contains the length of time in seconds before the connection times out. If you need to modify the length of time before the connection times out, change the Connection Timeout setting in your connection string. The default for this property is 30 seconds

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.ConnectionTimeout;
SqlConnection.Database

Syntax

String Database

Description

Database is a read-only property that contains the name of the connected database. To change databases, you need to use the ChangeDatabase() method or change the connection string.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.Database;
SqlConnection.DataSource

Syntax

String DataSource

Description

DataSource is a read-only property that contains the name of the data source you are connecting to. To change the data source, you need to modify your connection string.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.DataSource;
SqlConnection.PacketSize

Syntax

Int32 PacketSize

Description

PacketSize is a read-only property that contains the size of packets used in communication with the server.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.PacketSize;
SqlConnection.ServerVersion

Syntax

String ServerVersion

Description

ServerVersion is a read-only property that contains the version number of SQL server that the server is running.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.ServerVersion;
SqlConnection.State

Syntax

ConnectionState State

Description

The State property contains detailed information about the current state of the connection. It can contain the following values: Broken, Closed, Connecting, Executing, Fetching, and Open. These values can be found in the System.Data.ConnectionState namespace.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.State.ToString();
SqlConnection.WorkstationId

Syntax

String WorkstationId

Description

The WorkstationId property contains the NetBIOS name of the computer originating the SQL connection.

Example

SqlConnection conn = new SqlConnection();
msg.Text = conn.WorkstationId;
SqlConnection.BeginTransaction()

Syntax

SqlTransaction BeginTransaction( IsolationLevel iso )
SqlTransaction BeginTransaction()
SqlTransaction BeginTransaction( IsolationLevel iso, String transactionName )
SqlTransaction BeginTransaction( String transactionName)

Description

The BeginTransaction() method places the current connection under a transaction.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable, "RemoveProducts");
SqlConnection.ChangeDatabase()

Syntax

Boolean ChangeDatabase( String database )

Description

The ChangeDatabase() method enables a developer to change databases programmatically, without having to create a new connection object.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
          User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

conn.ChangeDatabase("test"); //Database now changed to 'test'
SqlConnection.Close()

Syntax

Void Close()

Description

The Close() method closes the connection to the database.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; 
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

conn.Close();
SqlConnection.CreateCommand()

Syntax

SqlCommand CreateCommand()

Description

The CreateCommand() method returns a new instance of a SqlCommand object.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Products";
SqlConnection.Open()

Syntax

Void Open()

Description

The Open() method opens the connection to the database by using the information provided in the ConnectionString property.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost;
         User Id=sa; Password=; Initial Catalog=northwind");
conn.Open();