AFTable.LinkExternal Method (IDbDataAdapter, Boolean, SecureString, IDictionary(String, Object))

public void LinkExternal(
	IDbDataAdapter dataAdapter,
	bool impersonate,
	SecureString password,
	IDictionary<string, Object> parameters
)

// This example demonstrates how to link an existing external table to an AFTable using an ADODB.Recordset
// and three .NET data adapters (ODBC, OLEDB, and SQL).
// Note: This sample uses a sample MS Access 2010 database that can be downloaded from Microsoft at:
// http://office.microsoft.com/en-us/templates/northwind-sales-web-database-TC101114818.aspx

// This example will work with 64 bit Microsoft Office 2010 and SQL Server 2012 - some 
// modifications to connection strings will be necessary to work with other versions
// of Microsoft Office and Microsoft SQL Server or on 32 bit systems.

// Get the Database 
PISystems myPISystems = new PISystems();
AFDatabase myDB = myPISystems.DefaultPISystem.Databases.DefaultDatabase;

// ****************************************************
// Create an external Table using an OLEDB data adapter
// ****************************************************
AFTable myTable = myDB.Tables.Add("Employees OLEDB");
myTable.Description = "This is my employee table using OLEDB data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);

string oleDbCommand = "SELECT * FROM Employees";
OleDbConnection oleDbConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Northwind.accdb");
OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand, oleDbConnection.ConnectionString);

// Link to the External OLEDB data adapter 
myTable.LinkExternal(oleDbDataAdapter, true, null);
myTable.CheckIn();

// ***************************************************
// Create an external Table from Excel using an ODBC data adapter
// ***************************************************
myTable = myDB.Tables.Add("Employees ODBC");
myTable.Description = "This is my employee table from Excel using ODBC data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);

string odbcCommand = "SELECT * FROM A1:F10";
OdbcConnection odbcConnection = new OdbcConnection(@"DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\Employees.xlsx");
OdbcDataAdapter odbcDataAdapter = new OdbcDataAdapter(odbcCommand, odbcConnection.ConnectionString);

// Link to the External OLEDB data adapter 
myTable.LinkExternal(odbcDataAdapter, true, null);
myTable.CheckIn();

// *************************************************
// Create an external Table using a SQL data adapter
// *************************************************
myTable = myDB.Tables.Add("Employees SQL");
myTable.Description = "This is my employee table using SQL data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);

string sqlCommand = "SELECT * FROM Employees";
SqlConnection sqlConnection = new SqlConnection($"Server={SqlServerMachineName};Database=Northwind;User Id={SqlUserId};Password=<PASSWORD>;Trusted_Connection=False;");
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand, sqlConnection);

// Link to the External SQL data adapter 
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in SqlPassword)
{
    password.AppendChar(c);
}

password.MakeReadOnly();
myTable.LinkExternal(sqlDataAdapter, false, password);
myTable.CheckIn();

// Display the DataTable
StringBuilder line = new StringBuilder();
foreach (DataColumn col in myTable.Table.Columns)
{
    line.AppendFormat("{0}, ", col.ColumnName);
}
Console.WriteLine(line.ToString());
line.Clear();
foreach (DataRow row in myTable.Table.Rows)
{
    foreach (DataColumn col in myTable.Table.Columns)
    {
        line.AppendFormat("{0}, ", row[col.ColumnName]);
    }
    Console.WriteLine(line.ToString());
    line.Clear();
}

// The Table can be Refreshed Manually using AFTable.Refresh
myTable.Refresh();