SqlConnection SqlConnection SqlConnection SqlConnection Class

定義

表示對 SQL Server 資料庫的連線。Represents a connection to a SQL Server database. 這個類別無法被繼承。This class cannot be inherited.

public ref class SqlConnection sealed : System::Data::Common::DbConnection, ICloneable, IDisposable
public sealed class SqlConnection : System.Data.Common.DbConnection, ICloneable, IDisposable
type SqlConnection = class
    inherit DbConnection
    interface IDbConnection
    interface ICloneable
    interface IDisposable
Public NotInheritable Class SqlConnection
Inherits DbConnection
Implements ICloneable, IDisposable
繼承
實作

範例

下列範例會建立SqlCommandSqlConnectionThe following example creates a SqlCommand and a SqlConnection. SqlConnection已開啟並設定為ConnectionSqlCommandThe SqlConnection is opened and set as the Connection for the SqlCommand. 此範例接著會呼叫ExecuteNonQueryThe example then calls ExecuteNonQuery. 若要這麼做,ExecuteNonQuery連接字串和查詢字串傳遞至Transact-SQLTransact-SQLINSERT 陳述式。To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a Transact-SQLTransact-SQL INSERT statement. 使用的程式碼結束時自動關閉連接區塊。The connection is closed automatically when the code exits the using block.

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}
Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

備註

ASqlConnection物件表示 SQL Server 資料來源的唯一工作階段。A SqlConnection object represents a unique session to a SQL Server data source. 具有用戶端/伺服器資料庫系統,它就相當於伺服器的網路連線項目。With a client/server database system, it is equivalent to a network connection to the server. SqlConnection 可搭配使用SqlDataAdapterSqlCommand來提升效能,當連接到 Microsoft SQL Server 資料庫。SqlConnection is used together with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. 第三方的所有 SQL Server 產品和其他的 OLE DB 支援的資料來源,請使用OleDbConnectionFor all third-party SQL Server products and other OLE DB-supported data sources, use OleDbConnection.

當您建立的執行個體SqlConnection,所有屬性都設為其初始值。When you create an instance of SqlConnection, all properties are set to their initial values. 如需這些值的清單,請參閱SqlConnection建構函式。For a list of these values, see the SqlConnection constructor.

請參閱ConnectionString取得一份連接字串中的關鍵字。See ConnectionString for a list of the keywords in a connection string.

如果SqlConnection移出範圍,因此不會關閉。If the SqlConnection goes out of scope, it won't be closed. 因此,您必須明確地關閉連接呼叫CloseDisposeTherefore, you must explicitly close the connection by calling Close or Dispose. CloseDispose相同的功能。Close and Dispose are functionally equivalent. 如果連接共用值Pooling設定為trueyes,基礎連接傳回連接集區。If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. 相反地,如果Pooling設定為falseno,實際關閉基礎連線到伺服器。On the other hand, if Pooling is set to false or no, the underlying connection to the server is actually closed.

注意

從連接集區中擷取連接或將連接傳回連接集區時,系統不會在伺服器上引發登入和登出事件,因為當連接傳回連接集區時,連接實際上並未關閉。Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool, because the connection is not actually closed when it is returned to the connection pool. 如需詳細資訊,請參閱 SQL Server 連共用ADO.NET)For more information, see SQL Server Connection Pooling (ADO.NET).

若要確保一律會關閉連線,開啟 內部連接using封鎖,如下列程式碼片段所示。To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. 這麼做可確保程式碼結束該區塊時,會自動關閉連線。Doing so ensures that the connection is automatically closed when the code exits the block.

Using connection As New SqlConnection(connectionString)  
    connection.Open()  
    ' Do work here; connection closed on following line.  
End Using  
using (SqlConnection connection = new SqlConnection(connectionString))  
    {  
        connection.Open();  
        // Do work here; connection closed on following line.  
    }  

注意

若要部署高效能應用程式,您必須使用連接共用。To deploy high-performance applications, you must use connection pooling. 當您使用.NET Framework Data Provider for SQL Server 時,您不必啟用連接共用,因為提供者會自動管理雖然您可以修改某些設定。When you use the .NET Framework Data Provider for SQL Server, you do not have to enable connection pooling because the provider manages this automatically, although you can modify some settings. 如需詳細資訊,請參閱 SQL Server 連共用ADO.NET)For more information, see SQL Server Connection Pooling (ADO.NET).

如果SqlException所執行的方法產生SqlCommand,則SqlConnection保持在開啟狀態時的嚴重性層級為 19 或更小。If a SqlException is generated by the method executing a SqlCommand, the SqlConnection remains open when the severity level is 19 or less. 伺服器時的嚴重性層級 20 或更高,通常會關閉SqlConnectionWhen the severity level is 20 or greater, the server ordinarily closes the SqlConnection. 但是,使用者可以再次開啟連線,然後繼續進行。However, the user can reopen the connection and continue.

建立的執行個體的應用程式SqlConnection物件可能需要的所有直接和間接呼叫者有足夠的權限的程式碼,藉由設定宣告式或命令式安全性要求。An application that creates an instance of the SqlConnection object can require all direct and indirect callers to have sufficient permission to the code by setting declarative or imperative security demands. SqlConnection 可使用的安全性要求SqlClientPermission物件。SqlConnection makes security demands using the SqlClientPermission object. 使用者可以確認他們的程式碼使用具有足夠的權限SqlClientPermissionAttribute物件。Users can verify that their code has sufficient permissions by using the SqlClientPermissionAttribute object. 使用者和系統管理員也可以使用Caspol.exe (程式碼存取安全性原則工具)修改電腦、 使用者和企業層級的安全性原則。Users and administrators can also use the Caspol.exe (Code Access Security Policy Tool) to modify security policy at the machine, user, and enterprise levels. 如需詳細資訊,請參閱 .NET 的安全性For more information, see Security in .NET. 如需示範如何使用安全性要求的範例,請參閱 < 程式碼存取安全性和 ADO.NETFor an example demonstrating how to use security demands, see Code Access Security and ADO.NET.

如需有關如何處理來自伺服器的警告和參考用訊息的詳細資訊,請參閱連線事件For more information about handling warning and informational messages from the server, see Connection Events. 如需有關 SQL Server 引擎錯誤與錯誤訊息的詳細資訊,請參閱 < Database Engine 事件和錯誤For more information about SQL Server engine errors and error messages, see Database Engine Events and Errors.

警告

您可以強制 TCP,而不是共用的記憶體。You can force TCP instead of shared memory. 您可以這麼前面加上 tcp: 連接中的伺服器名稱的字串,或者您可以使用 localhost。You can do that by prefixing tcp: to the server name in the connection string or you can use localhost.

建構函式

SqlConnection() SqlConnection() SqlConnection() SqlConnection()

初始化 SqlConnection 類別的新執行個體。Initializes a new instance of the SqlConnection class.

SqlConnection(String) SqlConnection(String) SqlConnection(String) SqlConnection(String)

指定包含連接字串的字串時,初始化 SqlConnection 類別的新執行個體。Initializes a new instance of the SqlConnection class when given a string that contains the connection string.

SqlConnection(String, SqlCredential) SqlConnection(String, SqlCredential) SqlConnection(String, SqlCredential) SqlConnection(String, SqlCredential)

指定不使用 Integrated Security = true 和包含使用者 ID 和密碼之 SqlCredential 物件的連接字串,初始化 SqlConnection 類別的新執行個體。Initializes a new instance of the SqlConnection class given a connection string, that does not use Integrated Security = true and a SqlCredential object that contains the user ID and password.

屬性

AccessToken AccessToken AccessToken AccessToken

取得或設定此連接的存取權杖。Gets or sets the access token for the connection.

CanRaiseEvents CanRaiseEvents CanRaiseEvents CanRaiseEvents

取得值,指出元件是否能引發事件。Gets a value indicating whether the component can raise an event.

(Inherited from Component)
ClientConnectionId ClientConnectionId ClientConnectionId ClientConnectionId

最近連線的連線 ID,無論連線是否成功。The connection ID of the most recent connection attempt, regardless of whether the attempt succeeded or failed.

ColumnEncryptionKeyCacheTtl ColumnEncryptionKeyCacheTtl ColumnEncryptionKeyCacheTtl ColumnEncryptionKeyCacheTtl

針對 Always Encrypted 功能,取得或設定資料行加密金鑰快取中資料行加密金鑰項目的存留時間。Gets or sets the time-to-live for column encryption key entries in the column encryption key cache for the Always Encrypted feature. 預設值是 2 小時。The default value is 2 hours. 0 表示完全沒有快取。0 means no caching at all.

ColumnEncryptionQueryMetadataCacheEnabled ColumnEncryptionQueryMetadataCacheEnabled ColumnEncryptionQueryMetadataCacheEnabled ColumnEncryptionQueryMetadataCacheEnabled

取得或設定值,指出是否要針對根據已啟用 Always Encrypted 的資料庫執行的參數化查詢,啟用 (true) 或停用 (false) 查詢中繼資料快取。Gets or sets a value that indicates whether query metadata caching is enabled (true) or not (false) for parameterized queries running against Always Encrypted enabled databases. 預設值為 true。The default value is true.

ColumnEncryptionTrustedMasterKeyPaths ColumnEncryptionTrustedMasterKeyPaths ColumnEncryptionTrustedMasterKeyPaths ColumnEncryptionTrustedMasterKeyPaths

可讓您為資料庫伺服器設定受信任機碼路徑的清單。Allows you to set a list of trusted key paths for a database server. 如果在處理應用程式查詢時,驅動程式收到的機碼路徑不在清單上,查詢就會失敗。If while processing an application query the driver receives a key path that is not on the list, the query will fail. 這個屬性會提供額外的保護以對抗安全性攻擊,事關受危害的 SQL Server 提供虛構的機碼路徑,這有可能導致金鑰存放區認證洩漏。This property provides additional protection against security attacks that involve a compromised SQL Server providing fake key paths, which may lead to leaking key store credentials.

ConnectionString ConnectionString ConnectionString ConnectionString

取得或設定用來開啟 SQL Server 資料庫的字串。Gets or sets the string used to open a SQL Server database.

ConnectionTimeout ConnectionTimeout ConnectionTimeout ConnectionTimeout

取得在終止嘗試並產生錯誤前嘗試建立連接的等待時間。Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

Container Container Container Container

取得包含 IContainerComponentGets the IContainer that contains the Component.

(Inherited from Component)
Credential Credential Credential Credential

取得或設定這個連接的 SqlCredential 物件。Gets or sets the SqlCredential object for this connection.

Credentials Credentials Credentials Credentials
Database Database Database Database

取得目前資料庫或要在連接開啟之後使用的資料庫名稱。Gets the name of the current database or the database to be used after a connection is opened.

DataSource DataSource DataSource DataSource

取得要連線的 SQL Server 執行個體名稱。Gets the name of the instance of SQL Server to which to connect.

DesignMode DesignMode DesignMode DesignMode

取得值,指出 Component 目前是否處於設計模式。Gets a value that indicates whether the Component is currently in design mode.

(Inherited from Component)
Events Events Events Events

取得附加在這個 Component 上的事件處理常式清單。Gets the list of event handlers that are attached to this Component.

(Inherited from Component)
FireInfoMessageEventOnUserErrors FireInfoMessageEventOnUserErrors FireInfoMessageEventOnUserErrors FireInfoMessageEventOnUserErrors

取得或設定 FireInfoMessageEventOnUserErrors 屬性。Gets or sets the FireInfoMessageEventOnUserErrors property.

PacketSize PacketSize PacketSize PacketSize

取得用來與 SQL Server 執行個體通訊的網路封包大小 (位元組)。Gets the size (in bytes) of network packets used to communicate with an instance of SQL Server.

ServerVersion ServerVersion ServerVersion ServerVersion

取得字串,其中包含用戶端連線的 SQL Server 執行個體版本。Gets a string that contains the version of the instance of SQL Server to which the client is connected.

Site Site Site Site

取得或設定 ComponentISiteGets or sets the ISite of the Component.

(Inherited from Component)
State State State State

指出在連接上執行最近一次網路作業期間的 SqlConnection 狀態。Indicates the state of the SqlConnection during the most recent network operation performed on the connection.

StatisticsEnabled StatisticsEnabled StatisticsEnabled StatisticsEnabled

設定為 true 時,啟用目前連接的統計資料蒐集。When set to true, enables statistics gathering for the current connection.

WorkstationId WorkstationId WorkstationId WorkstationId

取得識別資料庫用戶端的字串。Gets a string that identifies the database client.

方法

BeginTransaction() BeginTransaction() BeginTransaction() BeginTransaction()

開始資料庫交易。Starts a database transaction.

BeginTransaction(IsolationLevel) BeginTransaction(IsolationLevel) BeginTransaction(IsolationLevel) BeginTransaction(IsolationLevel)

使用指定的隔離等級開始資料庫異動。Starts a database transaction with the specified isolation level.

BeginTransaction(IsolationLevel, String) BeginTransaction(IsolationLevel, String) BeginTransaction(IsolationLevel, String) BeginTransaction(IsolationLevel, String)

使用指定的隔離等級和交易名稱開始資料庫交易。Starts a database transaction with the specified isolation level and transaction name.

BeginTransaction(String) BeginTransaction(String) BeginTransaction(String) BeginTransaction(String)

使用指定的異動名稱開始資料庫異動。Starts a database transaction with the specified transaction name.

ChangeDatabase(String) ChangeDatabase(String) ChangeDatabase(String) ChangeDatabase(String)

為開啟的 SqlConnection 變更目前的資料庫。Changes the current database for an open SqlConnection.

ChangePassword(String, SqlCredential, SecureString) ChangePassword(String, SqlCredential, SecureString) ChangePassword(String, SqlCredential, SecureString) ChangePassword(String, SqlCredential, SecureString)

變更 SqlCredential 物件中指定的使用者 SQL Server 密碼。Changes the SQL Server password for the user indicated in the SqlCredential object.

ChangePassword(String, String) ChangePassword(String, String) ChangePassword(String, String) ChangePassword(String, String)

將連接字串中指示的使用者 SQL Server 密碼變更成提供的新密碼。Changes the SQL Server password for the user indicated in the connection string to the supplied new password.

ClearAllPools() ClearAllPools() ClearAllPools() ClearAllPools()

清空連接集區。Empties the connection pool.

ClearPool(SqlConnection) ClearPool(SqlConnection) ClearPool(SqlConnection) ClearPool(SqlConnection)

清空與特定連接關聯的連接集區。Empties the connection pool associated with the specified connection.

Close() Close() Close() Close()

關閉與資料庫的連接。Closes the connection to the database. 這是關閉任何開啟連接的慣用方法。This is the preferred method of closing any open connection.

CreateCommand() CreateCommand() CreateCommand() CreateCommand()

建立並傳回與 SqlConnection 關聯的 SqlCommand 物件。Creates and returns a SqlCommand object associated with the SqlConnection.

CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type) CreateObjRef(Type)

建立包含所有相關資訊的物件,這些資訊是產生用來與遠端物件通訊的所需 Proxy。Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.

(Inherited from MarshalByRefObject)
Dispose() Dispose() Dispose() Dispose()

釋放 Component 所使用的所有資源。Releases all resources used by the Component.

(Inherited from Component)
Dispose(Boolean) Dispose(Boolean) Dispose(Boolean) Dispose(Boolean)

釋放 Component 所使用的 Unmanaged 資源,並選擇性地釋放 Managed 資源。Releases the unmanaged resources used by the Component and optionally releases the managed resources.

(Inherited from Component)
EnlistDistributedTransaction(ITransaction) EnlistDistributedTransaction(ITransaction) EnlistDistributedTransaction(ITransaction) EnlistDistributedTransaction(ITransaction)

將指定的異動登記為分散式異動。Enlists in the specified transaction as a distributed transaction.

EnlistTransaction(Transaction) EnlistTransaction(Transaction) EnlistTransaction(Transaction) EnlistTransaction(Transaction)

將指定的異動登記為分散式異動。Enlists in the specified transaction as a distributed transaction.

Equals(Object) Equals(Object) Equals(Object) Equals(Object)

判斷指定的物件是否等於目前的物件。Determines whether the specified object is equal to the current object.

(Inherited from Object)
GetHashCode() GetHashCode() GetHashCode() GetHashCode()

做為預設雜湊函式。Serves as the default hash function.

(Inherited from Object)
GetLifetimeService() GetLifetimeService() GetLifetimeService() GetLifetimeService()

擷取控制這個執行個體存留期 (Lifetime) 原則的目前存留期服務物件。Retrieves the current lifetime service object that controls the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
GetSchema() GetSchema() GetSchema() GetSchema()

傳回這個 SqlConnection 之資料來源的結構描述資訊。Returns schema information for the data source of this SqlConnection. 如需結構描述的詳細資訊,請參閱 SQL Server 結構描述集合For more information about scheme, see SQL Server Schema Collections.

GetSchema(String) GetSchema(String) GetSchema(String) GetSchema(String)

使用結構描述名稱的特定字串,傳回這個 SqlConnection 之資料來源的結構描述資訊。Returns schema information for the data source of this SqlConnection using the specified string for the schema name.

GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[]) GetSchema(String, String[])

使用結構描述名稱的特定字串和限制值的特定字串陣列,傳回這個 SqlConnection 的資料來源的結構描述資訊。Returns schema information for the data source of this SqlConnection using the specified string for the schema name and the specified string array for the restriction values.

GetService(Type) GetService(Type) GetService(Type) GetService(Type)

傳回表示 Component 或其 Container 所提供之服務的物件。Returns an object that represents a service provided by the Component or by its Container.

(Inherited from Component)
GetType() GetType() GetType() GetType()

取得目前執行個體的 TypeGets the Type of the current instance.

(Inherited from Object)
InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService() InitializeLifetimeService()

取得存留期服務物件,以控制這個執行個體的存留期原則。Obtains a lifetime service object to control the lifetime policy for this instance.

(Inherited from MarshalByRefObject)
MemberwiseClone() MemberwiseClone() MemberwiseClone() MemberwiseClone()

建立目前 Object 的淺層複本 (Shallow Copy)。Creates a shallow copy of the current Object.

(Inherited from Object)
MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean) MemberwiseClone(Boolean)

建立目前 MarshalByRefObject 物件的淺層複本。Creates a shallow copy of the current MarshalByRefObject object.

(Inherited from MarshalByRefObject)
Open() Open() Open() Open()

使用 ConnectionString 所指定的屬性設定,開啟資料庫連接。Opens a database connection with the property settings specified by the ConnectionString.

OpenAsync(CancellationToken) OpenAsync(CancellationToken) OpenAsync(CancellationToken) OpenAsync(CancellationToken)

非同步版本的 Open(),這個版本會透過 ConnectionString 所指定的屬性設定開啟資料庫連接。An asynchronous version of Open(), which opens a database connection with the property settings specified by the ConnectionString. 取消語彙基元可用於要求在連線逾時之前捨棄作業。The cancellation token can be used to request that the operation be abandoned before the connection timeout elapses. 例外狀況將經由傳回的工作傳播。Exceptions will be propagated via the returned Task. 如果超過連接逾時期限而未順利連線,傳回的工作會標示為失敗並發生例外狀況。If the connection timeout time elapses without successfully connecting, the returned Task will be marked as faulted with an Exception. 實作會傳回一項工作,而不需針對集區和非集區的連接封鎖呼叫的執行緒。The implementation returns a Task without blocking the calling thread for both pooled and non-pooled connections.

RegisterColumnEncryptionKeyStoreProviders(IDictionary<String,SqlColumnEncryptionKeyStoreProvider>) RegisterColumnEncryptionKeyStoreProviders(IDictionary<String,SqlColumnEncryptionKeyStoreProvider>) RegisterColumnEncryptionKeyStoreProviders(IDictionary<String,SqlColumnEncryptionKeyStoreProvider>) RegisterColumnEncryptionKeyStoreProviders(IDictionary<String,SqlColumnEncryptionKeyStoreProvider>)

註冊資料行加密金鑰存放區提供者。Registers the column encryption key store providers.

ResetStatistics() ResetStatistics() ResetStatistics() ResetStatistics()

如果啟用統計資料蒐集,則所有值都會重設為零。If statistics gathering is enabled, all values are reset to zero.

RetrieveStatistics() RetrieveStatistics() RetrieveStatistics() RetrieveStatistics()

呼叫方法時,傳回統計資料的名稱值配對集合。Returns a name value pair collection of statistics at the point in time the method is called.

ToString() ToString() ToString() ToString()

傳回任何包含 Component 名稱的 StringReturns a String containing the name of the Component, if any. 不應覆寫此方法。This method should not be overridden.

(Inherited from Component)

事件

Disposed Disposed Disposed Disposed

當此元件由 Dispose() 方法的呼叫處置時發生。Occurs when the component is disposed by a call to the Dispose() method.

(Inherited from Component)
InfoMessage InfoMessage InfoMessage InfoMessage

發生於 SQL Server 傳回警告或告知性訊息時。Occurs when SQL Server returns a warning or informational message.

StateChange StateChange StateChange StateChange

連接的狀態變更時發生。Occurs when the state of the connection changes.

明確介面實作

ICloneable.Clone() ICloneable.Clone() ICloneable.Clone() ICloneable.Clone()

建立目前執行個體複本的新物件。Creates a new object that is a copy of the current instance.

IDbConnection.BeginTransaction() IDbConnection.BeginTransaction() IDbConnection.BeginTransaction() IDbConnection.BeginTransaction()

開始資料庫異動。Begins a database transaction.

IDbConnection.BeginTransaction(IsolationLevel) IDbConnection.BeginTransaction(IsolationLevel) IDbConnection.BeginTransaction(IsolationLevel) IDbConnection.BeginTransaction(IsolationLevel)

使用指定的 IsolationLevel 值來開始資料庫交易。Begins a database transaction with the specified IsolationLevel value.

IDbConnection.CreateCommand() IDbConnection.CreateCommand() IDbConnection.CreateCommand() IDbConnection.CreateCommand()

建立並傳回與連接相關聯的 Command 物件。Creates and returns a Command object that is associated with the connection.

適用於

另請參閱