撰寫自訂連接管理員的程式碼

ConnectionString 屬性是重要的屬性，而且是自訂連接管理員特有的唯一屬性。連接管理員使用此屬性值，以連接至外部資料來源。如果您要結合數個其他的屬性 (例如伺服器名稱與資料庫名稱) 來建立連接字串，可以使用 Helper 函式組合字串，方法是以使用者提供的新值取代連接字串範本中的某些值。下列程式碼範例顯示 ConnectionString 屬性的實作，此屬性依賴 Helper 函式組合字串。

  ' Default values.
  Private _serverName As String = "(local)"
  Private _databaseName As String = "AdventureWorks2008R2"
  Private _connectionString As String = String.Empty

  Private Const CONNECTIONSTRING_TEMPLATE As String = _
    "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=SSPI"

  Public Property ServerName() As String
    Get
      Return _serverName
    End Get
    Set(ByVal value As String)
      _serverName = value
    End Set
  End Property

  Public Property DatabaseName() As String
    Get
      Return _databaseName
    End Get
    Set(ByVal value As String)
      _databaseName = value
    End Set
  End Property

  Public Overrides Property ConnectionString() As String
    Get
      UpdateConnectionString()
      Return _connectionString
    End Get
    Set(ByVal value As String)
      _connectionString = value
    End Set
  End Property

  Private Sub UpdateConnectionString()

    Dim temporaryString As String = CONNECTIONSTRING_TEMPLATE

    If Not String.IsNullOrEmpty(_serverName) Then
      temporaryString = temporaryString.Replace("<servername>", _serverName)
    End If
    If Not String.IsNullOrEmpty(_databaseName) Then
      temporaryString = temporaryString.Replace("<databasename>", _databaseName)
    End If

    _connectionString = temporaryString

  End Sub

    // Default values.
    private string _serverName = "(local)";
    private string _databaseName = "AdventureWorks2008R2";
    private string _connectionString = String.Empty;

    private const string CONNECTIONSTRING_TEMPLATE = "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=SSPI";

    public string ServerName
    {
      get
      {
        return _serverName;
      }
      set
      {
        _serverName = value;
      }
    }

    public string DatabaseName
    {
      get
      {
        return _databaseName;
      }
      set
      {
        _databaseName = value;
      }
    }

    public override string ConnectionString
    {
      get
      {
        UpdateConnectionString();
        return _connectionString;
      }
      set
      {
        _connectionString = value;
      }
    }

    private void UpdateConnectionString()
    {

      string temporaryString = CONNECTIONSTRING_TEMPLATE;

      if (!String.IsNullOrEmpty(_serverName))
      {
        temporaryString = temporaryString.Replace("<servername>", _serverName);
      }

      if (!String.IsNullOrEmpty(_databaseName))
      {
        temporaryString = temporaryString.Replace("<databasename>", _databaseName);
      }

      _connectionString = temporaryString;

    }

您覆寫 Validate 方法以確定已正確設定連接管理員。至少，您應該驗證連接字串的格式，並確定已為所有的引數提供值。必須等到連接管理員從 Validate 方法傳回 Success，執行才能繼續。

下列程式碼範例顯示 Validate 的實作，以確定使用者已指定連接的伺服器名稱。

  Public Overrides Function Validate(ByVal infoEvents As Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents) As Microsoft.SqlServer.Dts.Runtime.DTSExecResult

    If String.IsNullOrEmpty(_serverName) Then
      infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0)
      Return DTSExecResult.Failure
    Else
      Return DTSExecResult.Success
    End If

  End Function

    public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
    {

      if (String.IsNullOrEmpty(_serverName))
      {
        infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0);
        return DTSExecResult.Failure;
      }
      else
      {
        return DTSExecResult.Success;
      }

    }

通常，您不必實作連接管理員的自訂持續性。只有在物件的屬性使用複雜的資料類型時，才需要自訂持續性。如需詳細資訊，請參閱＜開發 Integration Services 的自訂物件＞。

您需要決定哪些類型的物件適用於 AcquireConnection 方法，從自訂連接管理員傳回。例如，檔案連接管理員只會傳回包含路徑與檔案名稱的字串，而 ADO.NET 連接管理員則會傳回已經開啟的 Managed 連接物件。OLE DB 連接管理員會傳回原生 OLE DB 連接物件，而此物件無法從 Managed 程式碼中加以使用。本主題中的程式碼片段是取自 SQL Server 連接管理員，它會傳回已開啟的 SqlConnection 物件。

連接管理員的使用者需要事先知道需要哪些類型的物件，這樣他們才能將傳回的物件轉換為適當的類型，並存取其方法與屬性。

  Public Overrides Function AcquireConnection(ByVal txn As Object) As Object

    Dim sqlConnection As New SqlConnection

    UpdateConnectionString()

    With sqlConnection
      .ConnectionString = _connectionString
      .Open()
    End With

    Return sqlConnection

  End Function

    public override object AcquireConnection(object txn)
    {

      SqlConnection sqlConnection = new SqlConnection();

      UpdateConnectionString();

      {
        sqlConnection.ConnectionString = _connectionString;
        sqlConnection.Open();
      }

      return sqlConnection;

    }

您在 ReleaseConnection 方法中所採取的動作，取決於從 AcquireConnection 方法傳回的物件類型。如果有開啟的連接物件，您應該關閉它並釋放其所使用的任何資源。如果 AcquireConnection 只傳回字串值，則不需要採取任何動作。

  Public Overrides Sub ReleaseConnection(ByVal connection As Object)

    Dim sqlConnection As SqlConnection

    sqlConnection = DirectCast(connection, SqlConnection)

    If sqlConnection.State <> ConnectionState.Closed Then
      sqlConnection.Close()
    End If

  End Sub

    public override void ReleaseConnection(object connection)
    {
      SqlConnection sqlConnection;
      sqlConnection = (SqlConnection)connection;
      if (sqlConnection.State != ConnectionState.Closed)
        sqlConnection.Close();
    }