Coding a Custom Connection Manager

After you have created a class that inherits from the ConnectionManagerBase base class, and applied the DtsConnectionAttribute attribute to the class, you must override the implementation of the properties and methods of the base class to provide your custom functionality.

注意：
For working samples of custom connection managers, see Sql Server 自定义连接管理器示例 and Excel2 自定义连接管理器示例. Also, the code examples in this topic come from the Sql Server Custom Connection Manager sample.

Configuring the Connection Manager

Setting the ConnectionString Property

The ConnectionString property is an important property and the only property unique to a custom connection manager. The connection manager uses the value of this property to connect to the external data source. If you are combining several other properties, such as server name and database name, to create the connection string, you can use a helper function to assemble the string by replacing certain values in a connection string template with the new value supplied by the user.

  ' Default values.
  Private _serverName As String = "(local)"
  Private _databaseName As String = "AdventureWorks"
  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 = "AdventureWorks";
    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;

    }

Validating the Connection Manager

Override the Validate method to ensure that the connection manager has been configured correctly. At a minimum, you should validate the format of the connection string and ensure that values have been provided for all arguments. Execution cannot continue until the connection manager returns Success from the Validate method.

The following code example shows an implementation of Validate that ensures that the user has specified a server name for the connection.

  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;
      }

    }

Persisting the Connection Manager

Normally you do not need to implement custom persistence for a connection manager. Custom persistence is required only when the properties of an object use complex data types. For more information, see Developing Custom Objects for Integration Services.

Working with the External Data Source

The methods that support connecting to an external data source are the most important methods of a custom connection manager. The AcquireConnection and ReleaseConnection methods are called at various times during both design time and run time.

Acquiring the Connection

You need to decide what type of object it is appropriate for the AcquireConnection method to return from your custom connection manager. For example, a File connection manager returns only a string that contains a path and filename, whereas an ADO.NET connection manager returns a managed connection object that is already open. An OLE DB connection manager returns a native OLE DB connection object that cannot be used from managed code. The custom SQL Server connection manager, from which the code snippets in this topic are taken, returns an open SqlConnection object.

Users of your connection manager need to know in advance what type of object to expect, so that they can cast the returned object to the appropriate type and access its methods and properties.

  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;

    }

Releasing the Connection

The action that you take in the ReleaseConnection method depends on the type of object that you returned from the AcquireConnection method. If there is an open connection object, you should close it and to release any resources that it is using. If AcquireConnection returned only a string value, no action needs to be taken.

  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();
    }

请参阅

任务

Creating a Custom Connection Manager
Developing a User Interface for a Custom Connection Manager

帮助和信息

获取 SQL Server 2005 帮助