Import a filtered list of tables using a static list of table names

While the ImportDB method has a table filter string, it uses the LIKE wildcard syntax, and can be difficult to use when you have multiple disparate search patterns. In this sample, we give two options: either use a statically defined list of table names or access a list of table names from a database table.

For this sample to work, you MUST uncomment the line of code corresponding to the filter type you want to use.

<!-- Connection.biml -->
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="Source" ConnectionString="Provider=SQLNCLI10;Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" />
  </Connections>
</Biml>

<!-- Tables.biml -->
<# var sourceConnection = (AstDbConnectionNode)RootNode.Connections["Source"]; #>
<# var importedSchema = sourceConnection.ImportDB("", "", ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews); #>

<#
// 1. Uncomment the following line for a list retrieved from a database table
// var tableNamesToImport = ExternalDataAccess.GetDataTable(sourceConnection.ConnectionString, "SELECT TableName FROM TableNames").Rows.OfType<System.Data.DataRow>().Select(r => r[0].ToString());

// 2. Uncomment the following line for a static list
// var tableNamesToImport = new List<string>() { "Table1", "Table2", "Table3", "Table4", "Table5", "Table6" };
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <# foreach (var tableNode in importedSchema.TableNodes.Where(item => tableNamesToImport.Contains(item.Name)).OrderBy(item => item.Name)) { #>
    <!-- Do whatever you were planning to do with your now filtered and sorted list of table nodes -->
    <#=tableNode.GetBiml()#>
    <# } #>
  </Tables>
</Biml>

<!-- Use the following T-SQL DDL to create the database table to store table names -->
<!--
CREATE TABLE [dbo].[TableNames](
   [TableName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
-->