1:  <#@ template language="C#v3.5" hostspecific="True" debug="True" #> 
   2:  <#@ output extension="cs" #>
   3:  <#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
   4:  <#@ assembly name="Microsoft.SqlServer.SqlEnum" #>
   5:  <#@ assembly name="Microsoft.SqlServer.Smo" #>
   6:  <#@ assembly name="System.Data" #>
   7:  <#@ assembly name="System.Core.dll" #>
   8:  <#@ import namespace="System.Linq" #>
   9:  <#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
  10:  <#@ import namespace="Microsoft.SqlServer.Management.Common" #>
  11:  <#@ import namespace="System.Data.SqlClient" #>
  12:  <#@ import namespace="System.Collections.Generic" #>
  13:  <#@ import namespace="System.Collections.Specialized" #>
  14:  <#
  15:  var ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;";
  16:  SqlConnection cn = new SqlConnection(ConnectionString);
  17:  var DatabaseName = cn.Database;
  18:  Server server = new Server(new ServerConnection(cn));
  19:  Database database = server.Databases[cn.Database];
  20:  database.Refresh();
  21:  #>
  22:  using System;
  23:  using System.Linq;
  24:  using NMigrations;
  25:  using NMigrations.Sql.SqlServer;
  26:   
  27:  namespace MyMigrations {    
  28:      [Migration(001)]
  29:      public class Migration001:IMigration
  30:          {        
  31:              public void Up(Database db) {
  32:  <#
  33:  var tables = ReorderTablesByForeignKeyDependencies(database.Tables);
  34:  foreach (Table table in tables)
  35:  {
  36:      var tableName = table.Name.Replace(" ", "_");
  37:      var cols = new List<Microsoft.SqlServer.Management.Smo.Column>();
  38:      for (int i = 0; i < table.Columns.Count; i++)
  39:      {
  40:          cols.Add(table.Columns[i]);
  41:      }
  42:   
  43:  #>      
  44:          var <#=      tableName #> = db.AddTable("<#=      table.Name#>");
  45:          <#
  46:        var compositeKeys = cols.Where(x => x.InPrimaryKey).Select(x => x.Name).ToArray();
  47:   
  48:        for (int i = 0; i < table.Columns.Count; i++)
  49:        {
  50:            var col = table.Columns[i]; #>
  51:              <#=      tableName #><#=      BuildColumn(col, compositeKeys.Length) #>;            
  52:          <#      }#>
  53:          <#
  54:          
  55:        if (compositeKeys.Length > 1)
  56:        {
  57:            var compKeyList = new List<string>();
  58:            foreach (var compKey in compositeKeys)
  59:            {
  60:                compKeyList.Add("\"" + compKey + "\"");
  61:            }
  62:              #>            
  63:                      <#=tableName #>.AddPrimaryKeyConstraint(new string[] {<#=      String.Join(",", compKeyList.ToArray()) #>});
  64:  <#      }#>
  65:   
  66:  <#
  67:        for (int i = 0; i < table.ForeignKeys.Count; i++)
  68:        {
  69:            var fk = table.ForeignKeys[i];
  70:            var fkCols = new System.Collections.Generic.List<string>();
  71:            var refCols = new System.Collections.Generic.List<string>();
  72:            foreach (ForeignKeyColumn fkCol in fk.Columns)
  73:            {
  74:                fkCols.Add("\"" + fkCol.Name + "\"");
  75:                refCols.Add("\"" + fkCol.ReferencedColumn + "\"");
  76:            }
  77:            var parsedColumnNames = String.Join(",", fkCols.ToArray());
  78:            var parsedRefColumnNames = String.Join(",", refCols.ToArray());        
  79:  #>
  80:                      <#=tableName #>.AddForeignKeyConstraint("<#=      fk.Name #>",new string[] {<#=      parsedColumnNames #>},"<#=      fk.ReferencedTable #>",new string[] {<#=      parsedRefColumnNames #>});
  81:  <#     }
  82:   
  83:        var indexes = new System.Collections.Generic.List<Index>();
  84:        for (int i = 0; i < table.Indexes.Count; i++)
  85:        {          
  86:            indexes.Add(table.Indexes[i]);
  87:        }
  88:   
  89:        foreach (var idx in indexes) {
  90:  #>
  91:          ///idx type=<#= idx.IndexKeyType #>
  92:  <#
  93:        if (idx.IndexKeyType.ToString() == "None")
  94:            {
  95:                var idxCols = new System.Collections.Generic.List<string>();
  96:                foreach (IndexedColumn idxCol in idx.IndexedColumns)
  97:                {
  98:                    idxCols.Add("\"" + idxCol.Name + "\"");
  99:                }
 100:                if (idx.IsUnique)
 101:                {                                        
 102:  #>            <#=      tableName #>.AddUniqueConstraint(new string[]{ <#=      String.Join(",", idxCols.ToArray()) #>});
 103:  <#
 104:                }
 105:                else
 106:                {
 107:  #>
 108:              <#=      tableName #>.AddIndex(new string[]{ <#=      String.Join(",", idxCols.ToArray()) #>});
 109:  <#                    
 110:                }
 111:            }
 112:        }
 113:  }
 114:  #>
 115:  }
 116:          public void Down(Database db){          
 117:          <#    tables.Reverse();
 118:                foreach(Table table in tables) { #>        
 119:          <#      if (!table.IsSystemObject) { #> db.DropTable("<#=table.Name #>");<#}
 120:              } #>
 121:              
 122:          }
 123:      }
 124:  }
 125:   
 126:  <# 
 127:  #>
 128:  <#+   public string BuildColumn(Microsoft.SqlServer.Management.Smo.Column col, int keyCount)
 129:         {
 130:             
 131:             string result = String.Format(".AddColumn(\"{0}\",{1}", col.Name, GetSqlType(col.DataType.SqlDataType.ToString()));
 132:             string suffix = "";
 133:             if (col.DataType.SqlDataType.ToString().ToLower().EndsWith("char")) result += String.Format(",{0}", col.DataType.MaximumLength);
 134:             if (!result.Contains(".Currency"))
 135:             {
 136:                 if (col.DataType.NumericPrecision > 0) result += String.Format(",{0}", col.DataType.NumericPrecision);
 137:                 if (col.DataType.NumericScale > 0) result += String.Format(",{0}", col.DataType.NumericScale);
 138:             }
 139:             result += ")";
 140:             if (!col.Nullable) suffix += ".NotNull()";
 141:             if (!(col.InPrimaryKey & col.IsForeignKey))
 142:             {
 143:                 
 144:                 if (col.InPrimaryKey && keyCount==1) suffix += ".PrimaryKey()";
 145:                 if (col.Identity) suffix += ".AutoIncrement(" + col.IdentitySeed + "," + col.IdentityIncrement + ")";
 146:             }
 147:   
 148:   
 149:             return result + suffix;
 150:   
 151:         }
 152:   
 153:         public string GetSqlType(string dataType)
 154:         {
 155:             var result = "SqlTypes.";
 156:             if (dataType == "Real") return result += "Double";
 157:             if (dataType == "Float") return result += "Double";
 158:             if (dataType == "Money") return result += "Currency";
 159:             if (dataType == "Numeric") return result += "Currency";
 160:             if (dataType == "SmallDateTime") return result += "DateTime";
 161:             if (dataType == "Bit") return result += "Boolean";
 162:             if (dataType == "Image") return result += "Binary";
 163:             if (dataType == "UniqueIdentifier") return result += "Guid";
 164:             if (dataType == "DateTimeOffset") return result += "TimeSpan";
 165:   
 166:             return result += dataType;
 167:   
 168:         }
 169:   
 170:          public List<Microsoft.SqlServer.Management.Smo.Table> ReorderTablesByForeignKeyDependencies(Microsoft.SqlServer.Management.Smo.TableCollection dbTables)
 171:          {
 172:              var tableOrder = new List<Microsoft.SqlServer.Management.Smo.Table>();
 173:              var tables = new List<Microsoft.SqlServer.Management.Smo.Table>();
 174:   
 175:              foreach (Microsoft.SqlServer.Management.Smo.Table table in dbTables)
 176:              {
 177:                  
 178:                  if (!table.IsSystemObject) tables.Add(table);
 179:              }
 180:   
 181:              foreach (var tbl in tables.Where(x => x.ForeignKeys.Count == 0).ToList())
 182:              {
 183:                  tableOrder.Add(tbl);
 184:                  tables.Remove(tbl);
 185:              }
 186:   
 187:              while (tables.Any())
 188:              {
 189:                  var namesInList = tableOrder.Select(x => x.Name).ToArray<string>();
 190:                  foreach (var tbl in tables.ToList())
 191:                  {
 192:                      bool checkFkAgainstList = true;
 193:                      for (int i = 0; i < tbl.ForeignKeys.Count; i++)
 194:                      {
 195:                          if (tbl.Name != tbl.ForeignKeys[i].ReferencedTable & !namesInList.Contains(tbl.ForeignKeys[i].ReferencedTable))
 196:                          {
 197:                              //Console.WriteLine(tbl.ForeignKeys[i].ReferencedTable);
 198:                              checkFkAgainstList = false;
 199:                              break;
 200:                          }
 201:                      }
 202:                      if (checkFkAgainstList)
 203:                      {
 204:                          tableOrder.Add(tbl);
 205:                          tables.Remove(tbl);
 206:                      }
 207:                  }
 208:              }
 209:              return tableOrder.ToList();
 210:          }        
 211:  #>