AFTableConnection Class

[SerializableAttribute]
public sealed class AFTableConnection : AFObject, 
	IAFTransactable, IAFChangedEvent, IAFSecurable, IComparable<AFTableConnection>

// This example demonstrates how to link an existing external table to an AFTable with an
// AFTableConnection configured to use an ADODB.Recordset and three .NET data adapters (ODBC,
// OLEDB, and SQL). The link to the SQL Server table is configured to use parameters.
// 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 the parameters used in the table query
Dictionary<string, object> myParameters = new Dictionary<string, object>();
myParameters.Add("@City", "London");

// Create the password used when connecting to the external table
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in SqlPassword)
{
    password.AppendChar(c);
}

password.MakeReadOnly();

// ****************************************************
// Create a Table Connection using an OLEDB data adapter
// ****************************************************
AFTableConnection myConnection = myDB.TableConnections.Add("Employees OLEDB");
myConnection.Description = "This is my employee table connection using OLEDB data adapter.";
myConnection.ImpersonateUser = true;
myConnection.ExternalConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Northwind.accdb";
myConnection.ExternalType = typeof(OleDbDataAdapter).FullName;
myConnection.CheckIn();

// Create a Table and Link to the External Table using the OLEDB data adapter Table Connection
AFTable myTable = myDB.Tables.Add("Employees OLEDB");
myTable.Description = "This is my employee table using OLEDB data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);
myTable.LinkExternal(myConnection, "SELECT * FROM Employees", null);
myTable.CheckIn();

// ***************************************************
// Create a Table Connection from Excel using an ODBC data adapter
// ***************************************************
myConnection = myDB.TableConnections.Add("Employees ODBC");
myConnection.Description = "This is my employee table connection using ODBC data adapter.";
myConnection.ImpersonateUser = true;
myConnection.ExternalConnection = @"DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\Employees.xlsx";
myConnection.ExternalType = typeof(OdbcDataAdapter).FullName;

// Create a Table and Link to the External Table using the ODBC data adapter Table Connection
myTable = myDB.Tables.Add("Employees ODBC");
myTable.Description = "This is my employee table from Excel using ODBC data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);
myTable.LinkExternal(myConnection, "SELECT * FROM A1:F10", null);
myTable.CheckIn();

// *************************************************
// Create a Table Connection using a SQL data adapter
// *************************************************
myConnection = myDB.TableConnections.Add("Employees SQL");
myConnection.Description = "This is my employee table connection using SQL data adapter.";
myConnection.ExternalConnection = $"Server={SqlServerMachineName};Database=Northwind;User Id={SqlUserId};Password=<PASSWORD>;Trusted_Connection=False;";
myConnection.ExternalType = typeof(SqlDataAdapter).FullName;
myConnection.SecurePassword = password;

// Create a Table and Link to the External Table using the SQL data adapter Table Connection
myTable = myDB.Tables.Add("Employees SQL");
myTable.Description = "This is my employee table using SQL data adapter.";
myTable.CacheInterval = TimeSpan.FromMinutes(30);
myTable.LinkExternal(myConnection, "SELECT * FROM Employees WHERE [City] = @City", myParameters);
myTable.CheckIn();

// Display the DataTable using Parameters
myParameters = new Dictionary<string, object>();
myParameters.Add("@City", "Seattle");
DataTable table = myTable.GetTableWithParameters(myParameters);
StringBuilder line = new StringBuilder();
foreach (DataColumn col in table.Columns)
{
    line.AppendFormat("{0}, ", col.ColumnName);
}
Console.WriteLine(line.ToString());
line.Clear();
foreach (DataRow row in table.Rows)
{
    foreach (DataColumn col in 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();