Export (0) Print
Expand All

SqlConnection Class

Represents an open connection to a SQL Server database. This class cannot be inherited.

For a list of all members of this type, see SqlConnection Members.

System.Object
   System.MarshalByRefObject
      System.ComponentModel.Component
         System.Data.SqlClient.SqlConnection

[Visual Basic]
NotInheritable Public Class SqlConnection
   Inherits Component
   Implements IDbConnection, ICloneable
[C#]
public sealed class SqlConnection : Component, IDbConnection,
   ICloneable
[C++]
public __gc __sealed class SqlConnection : public Component,
   IDbConnection, ICloneable
[JScript]
public class SqlConnection extends Component implements
   IDbConnection, ICloneable

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Remarks

A SqlConnection object represents a unique session to a SQL Server data source. In the case of a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used in conjunction with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. For all third-party SQL server products, as well as other OLE DB-supported data sources, use OleDbConnection.

When you create an instance of SqlConnection, all properties are set to their initial values. For a list of these values, see the SqlConnection constructor.

If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose.

Note   To deploy high-performance applications, you need to use connection pooling. When you use the .NET Framework Data Provider for SQL Server, you do not need to enable connection pooling because the provider manages this automatically, although you can modify some settings. For more information about using connection pooling with the .NET Framework Data Provider for SQL Server, see Connection Pooling for the .NET Framework Data Provider for SQL Server.

If a SqlException is generated by the method executing a SqlCommand, the SqlConnection remains open when the severity level is 19 or less. When the severity level is 20 or greater, the server usually closes the SqlConnection. However, the user can reopen the connection and continue.

An application that creates an instance of the SqlConnection object can require all direct and indirect callers to have adequate permission to the code by setting declarative or imperative security demands. SqlConnection makes security demands using the SqlClientPermission object. Users can verify that their code has adequate permissions by using the SqlClientPermissionAttribute object. Users and administrators can also use the Code Access Security Policy Tool (Caspol.exe) to modify security policy at the machine, user, and enterprise levels. For more information, see Securing Applications.

Note   If you are using Microsoft .NET Framework version 1.0, the FullTrust named permission set is required to connect to SQL Server using Open. This requirement does not apply if you are using .NET Framework version 1.1. For more information, see Requesting Permissions and Named Permission Sets.

For more information about handling warning and informational messages from the server, see Working with Connection Events.

Example

[Visual Basic, C#, C++] The following example creates a SqlCommand and a SqlConnection. The SqlConnection is opened and set as the Connection for the SqlCommand. The example then calls ExecuteNonQuery, and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a Transact-SQL INSERT statement.

[Visual Basic] 
Public Sub InsertRow(myConnectionString As String)
    ' If the connection string is null, use a default.
    If myConnectionString = "" Then
        myConnectionString = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"
    End If
    Dim myConnection As New SqlConnection(myConnectionString)
    Dim myInsertQuery As String = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"
    Dim myCommand As New SqlCommand(myInsertQuery)
    myCommand.Connection = myConnection
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myCommand.Connection.Close()
End Sub 'SelectSqlClientSrvRows

[C#] 
public void InsertRow(string myConnectionString) 
 {
    // If the connection string is null, use a default.
    if(myConnectionString == "") 
    {
       myConnectionString = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
    }
    SqlConnection myConnection = new SqlConnection(myConnectionString);
    string myInsertQuery = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";
    SqlCommand myCommand = new SqlCommand(myInsertQuery);
    myCommand.Connection = myConnection;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myCommand.Connection.Close();
 }

[C++] 
public:
void InsertRow(String* myConnectionString) 
 {
    // If the connection string is null, use a default.
    if(myConnectionString->Equals(S""))
    {
       myConnectionString = S"Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
    }
    SqlConnection* myConnection = new SqlConnection(myConnectionString);
    String* myInsertQuery = S"INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";
    SqlCommand* myCommand = new SqlCommand(myInsertQuery);
    myCommand->Connection = myConnection;
    myConnection->Open();
    myCommand->ExecuteNonQuery();
    myCommand->Connection->Close();
 }

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Namespace: System.Data.SqlClient

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

Assembly: System.Data (in System.Data.dll)

See Also

SqlConnection Members | System.Data.SqlClient Namespace | SqlDataAdapter | SqlCommand

Show:
© 2014 Microsoft