xp-dev.com Git - CPE_learningsite/blob - CPE/CPE.App/CPE.App.Api/OrmLite.Core.ttinclude

   1
   2 <#@ template language="C#" hostspecific="True" #>
   3 <#@ assembly name="EnvDTE" #>
   4 <#@ assembly name="System.Core.dll" #>
   5 <#@ assembly name="System.Data" #>
   6 <#@ assembly name="System.Xml" #>
   7 <#@ assembly name="System.Configuration" #>
   8 <#@ assembly name="System.Windows.Forms" #>
   9 <#@ import namespace="System.Collections.Generic" #>
  10 <#@ import namespace="System.Data" #>
  11 <#@ import namespace="System.Data.SqlClient" #>
  12 <#@ import namespace="System.Data.Common" #>
  13 <#@ import namespace="System.Diagnostics" #>
  14 <#@ import namespace="System.Globalization" #>
  15 <#@ import namespace="System.IO" #>
  16 <#@ import namespace="System.Linq" #>
  17 <#@ import namespace="System.Text" #>
  18 <#@ import namespace="System.Text.RegularExpressions" #>
  19 <#@ import namespace="System.Configuration" #>
  20 <#@ import namespace="System.Windows.Forms" #>
  21 <#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
  22 <#+
  23
  24 /*
  25  This is code is based on the T4 template from the PetaPoco project which in turn is based on the subsonic project.
  26
  27  -----------------------------------------------------------------------------------------
  28
  29  This template can read minimal schema information from the following databases:
  30
  31         * SQL Server
  32         * SQL Server CE
  33         * MySQL
  34         * PostGreSQL
  35         * Oracle
  36         * Sqlite
  37
  38  For connection and provider settings the template will look for the web.config or app.config file of the
  39  containing Visual Studio project.  It will not however read DbProvider settings from this file.
  40
  41  In order to work, the appropriate driver must be registered in the system machine.config file.  If you're
  42  using Visual Studio 2010 the file you want is here:
  43
  44         C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
  45
  46  After making changes to machine.config you will also need to restart Visual Studio.
  47
  48  Here's a typical set of entries that might help if you're stuck:
  49
  50         <system.data>
  51                 <DbProviderFactories>
  52                         <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  53                         <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  54                         <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  55                         <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  56                         <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
  57                         <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
  58                         <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.91, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
  59                 </DbProviderFactories>
  60         </system.data>
  61
  62  Also, the providers and their dependencies need to be installed to GAC.
  63
  64  Eg; this is how I installed the drivers for PostgreSQL
  65
  66          gacutil /i Npgsql.dll
  67          gacutil /i Mono.Security.dll
  68
  69  -----------------------------------------------------------------------------------------
  70
  71  SubSonic - http://subsonicproject.com
  72
  73  The contents of this file are subject to the New BSD
  74  License (the "License"); you may not use this file
  75  except in compliance with the License. You may obtain a copy of
  76  the License at http://www.opensource.org/licenses/bsd-license.php
  77
  78  Software distributed under the License is distributed on an
  79  "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
  80  implied. See the License for the specific language governing
  81  rights and limitations under the License.
  82 */
  83
  84 string ConnectionStringName = "";
  85 string Namespace = "";
  86 string ClassPrefix = "";
  87 string ClassSuffix = "";
  88 string SchemaName = null;
  89 bool IncludeViews = false;
  90 bool IncludeFunctions = false;
  91
  92 public class Table
  93 {
  94     public List<Column> Columns;
  95     public List<TableIndex> Indices;
  96     public List<FKey> FKeys;
  97     public string Name;
  98         public string Schema;
  99         public bool IsView;
 100         public bool IsFunction;
 101     public string CleanName;
 102     public string ClassName;
 103         public string SequenceName;
 104         public bool Ignore;
 105         public string SQL;
 106
 107     public Column PK
 108     {
 109         get
 110         {
 111             return this.Columns.SingleOrDefault(x=>x.IsPK);
 112         }
 113     }
 114
 115         public Column GetColumn(string columnName)
 116         {
 117                 return Columns.Single(x=>string.Compare(x.Name, columnName, true)==0);
 118         }
 119
 120         public Column this[string columnName]
 121         {
 122                 get
 123                 {
 124                         return GetColumn(columnName);
 125                 }
 126         }
 127
 128         public bool HasPK()
 129         {
 130                 return ((PK != null) && (string.IsNullOrEmpty(PK.Name) != true));
 131         }
 132     public TableIndex GetIndex(string indexName)
 133     {
 134         return Indices.Single(x=>string.Compare(x.Name, indexName, true)==0);
 135     }
 136 }
 137
 138 public class Column
 139 {
 140     public string Name;
 141     public string PropertyName;
 142     public string PropertyType;
 143     public bool IsPK;
 144     public bool IsNullable;
 145         public bool IsAutoIncrement;
 146         public bool IsComputed;
 147         public bool Ignore;
 148         public int Size;
 149         public int Precision;
 150         public string DefaultValue;
 151         public string ProperPropertyType
 152         {
 153                 get
 154                 {
 155                   if(IsNullable)
 156                   {
 157                         return PropertyType + CheckNullable(this);
 158                   }
 159                   return PropertyType;
 160                 }
 161         }
 162 }
 163
 164 public class Tables : List<Table>
 165 {
 166         public Tables()
 167         {
 168         }
 169
 170         public Table GetTable(string tableName)
 171         {
 172                 return this.Single(x=>string.Compare(x.Name, tableName, true)==0);
 173         }
 174
 175         public Table this[string tableName]
 176         {
 177                 get
 178                 {
 179                         return GetTable(tableName);
 180                 }
 181         }
 182
 183 }
 184
 185 public class IndexColumn
 186 {
 187     public string Name;
 188         public bool IsAsc;
 189 }
 190
 191 public class TableIndex
 192 {
 193     public string Name;
 194         public List<IndexColumn> IndexColumns;
 195         public bool IsUnique;
 196         public string SQL;
 197 }
 198
 199 public class FKey
 200 {
 201     public string ToTable;
 202         public string FromColumn;
 203         public string ToColumn;
 204 }
 205
 206 public class SP
 207 {
 208     public string Name;
 209     public string CleanName;
 210     public string ClassName;
 211     public string Schema;
 212     public string SchemaQualifiedName {get{return Schema+"."+Name;}}
 213     public List<SPParam> Parameters;
 214     public SP()
 215         {
 216         Parameters=new List<SPParam>();
 217     }
 218     public string ArgList
 219         {
 220         get
 221                 {
 222             StringBuilder sb=new StringBuilder();
 223             int loopCount=1;
 224             foreach(var par in Parameters)
 225             {
 226                 sb.AppendFormat("{0} {1}", par.SysType,par.CleanName);
 227                 if(loopCount<Parameters.Count)
 228                     sb.Append(",");
 229                 loopCount++;
 230             }
 231             return sb.ToString();
 232         }
 233         }
 234 }
 235
 236 public enum SPParamDir
 237 {
 238   OutDirection,
 239   InDirection,
 240   InAndOutDirection
 241 }
 242
 243 public class SPParam
 244 {
 245         public string Name;
 246     public string CleanName;
 247     public string SysType;
 248         public string NullableSysType;
 249     public string DbType;
 250     public SPParamDir Direction;
 251 }
 252
 253
 254 static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);
 255
 256 static string[] cs_keywords = { "abstract", "event", "new", "struct", "as", "explicit", "null",
 257          "switch", "base", "extern", "object", "this", "bool", "false", "operator", "throw",
 258          "break", "finally", "out", "true", "byte", "fixed", "override", "try", "case", "float",
 259          "params", "typeof", "catch", "for", "private", "uint", "char", "foreach", "protected",
 260          "ulong", "checked", "goto", "public", "unchecked", "class", "if", "readonly", "unsafe",
 261          "const", "implicit", "ref", "ushort", "continue", "in", "return", "using", "decimal",
 262          "int", "sbyte", "virtual", "default", "interface", "sealed", "volatile", "delegate",
 263          "internal", "short", "void", "do", "is", "sizeof", "while", "double", "lock",
 264          "stackalloc", "else", "long", "static", "enum", "namespace", "string" };
 265
 266 static Func<string, string> CleanUp = (str) =>
 267 {
 268         str = rxCleanUp.Replace(str, "_");
 269
 270         if (char.IsDigit(str[0]) || cs_keywords.Contains(str))
 271                 str = "@" + str;
 272
 273     return str;
 274 };
 275
 276 static string CheckNullable(Column col)
 277 {
 278     string result="";
 279     if(col.IsNullable &&
 280                 col.PropertyType !="byte[]" &&
 281                 col.PropertyType !="string" &&
 282                 col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
 283                 col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
 284                 )
 285         result="?";
 286     return result;
 287 }
 288
 289 string GetConnectionString(ref string connectionStringName, out string providerName)
 290 {
 291     var _CurrentProject = GetCurrentProject();
 292
 293         providerName=null;
 294
 295     string result="";
 296     ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
 297     configFile.ExeConfigFilename = GetConfigPath();
 298
 299     if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
 300         throw new ArgumentNullException("The project does not contain App.config or Web.config file.");
 301
 302
 303     var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
 304     var connSection=config.ConnectionStrings;
 305
 306     //if the connectionString is empty - which is the defauls
 307     //look for count-1 - this is the last connection string
 308     //and takes into account AppServices and LocalSqlServer
 309     if(string.IsNullOrEmpty(connectionStringName))
 310     {
 311         if(connSection.ConnectionStrings.Count>1)
 312         {
 313                         connectionStringName = connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].Name;
 314             result=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ConnectionString;
 315             providerName=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ProviderName;
 316         }
 317     }
 318     else
 319     {
 320         try
 321         {
 322             result=connSection.ConnectionStrings[connectionStringName].ConnectionString;
 323             providerName=connSection.ConnectionStrings[connectionStringName].ProviderName;
 324         }
 325         catch
 326         {
 327             result="There is no connection string name called '"+connectionStringName+"'";
 328         }
 329     }
 330
 331 //      if (String.IsNullOrEmpty(providerName))
 332 //              providerName="System.Data.SqlClient";
 333
 334     return result;
 335 }
 336
 337 string _connectionString="";
 338 string _providerName="";
 339
 340 void InitConnectionString()
 341 {
 342     if(String.IsNullOrEmpty(_connectionString))
 343     {
 344         _connectionString=GetConnectionString(ref ConnectionStringName, out _providerName);
 345
 346                 if(_connectionString.Contains("|DataDirectory|"))
 347                 {
 348                         //have to replace it
 349                         string dataFilePath=GetDataDirectory();
 350                         _connectionString=_connectionString.Replace("|DataDirectory|",dataFilePath);
 351                 }
 352         }
 353 }
 354
 355 public string ConnectionString
 356 {
 357     get
 358     {
 359                 InitConnectionString();
 360         return _connectionString;
 361     }
 362 }
 363
 364 public string ProviderName
 365 {
 366     get
 367     {
 368                 InitConnectionString();
 369         return _providerName;
 370     }
 371 }
 372
 373 public EnvDTE.Project GetCurrentProject()  {
 374
 375     IServiceProvider _ServiceProvider = (IServiceProvider)Host;
 376     if (_ServiceProvider == null)
 377         throw new Exception("Host property returned unexpected value (null)");
 378
 379     EnvDTE.DTE dte = (EnvDTE.DTE)_ServiceProvider.GetService(typeof(EnvDTE.DTE));
 380     if (dte == null)
 381         throw new Exception("Unable to retrieve EnvDTE.DTE");
 382
 383     Array activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
 384     if (activeSolutionProjects == null)
 385         throw new Exception("DTE.ActiveSolutionProjects returned null");
 386
 387     EnvDTE.Project dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
 388     if (dteProject == null)
 389         throw new Exception("DTE.ActiveSolutionProjects[0] returned null");
 390
 391     return dteProject;
 392
 393 }
 394
 395 private string GetProjectPath()
 396 {
 397     EnvDTE.Project project = GetCurrentProject();
 398     System.IO.FileInfo info = new System.IO.FileInfo(project.FullName);
 399     return info.Directory.FullName;
 400 }
 401
 402 private string GetConfigPath()
 403 {
 404     EnvDTE.Project project = GetCurrentProject();
 405     foreach (EnvDTE.ProjectItem item in project.ProjectItems)
 406     {
 407         // if it is the app.config file, then open it up
 408         if (item.Name.Equals("App.config",StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("Web.config",StringComparison.InvariantCultureIgnoreCase))
 409                         return GetProjectPath() + "\\" + item.Name;
 410     }
 411     return String.Empty;
 412 }
 413
 414 public string GetDataDirectory()
 415 {
 416     EnvDTE.Project project=GetCurrentProject();
 417     return System.IO.Path.GetDirectoryName(project.FileName)+"\\App_Data\\";
 418 }
 419
 420 static string zap_password(string connectionString)
 421 {
 422         var rx = new Regex("password=.*;", RegexOptions.Singleline | RegexOptions.Multiline | RegexOptions.IgnoreCase);
 423         return rx.Replace(connectionString, "password=**zapped**;");
 424 }
 425
 426
 427 public string GetColumnDefaultValue(Column col)
 428 {
 429         string sysType=string.Format("\"{0}\"",col.DefaultValue);
 430         switch (col.PropertyType.ToLower())
 431         {
 432                         case "long":
 433                         case "int":
 434                         case "double":
 435                         case "decimal":
 436                         case "bool":
 437                 sysType= col.DefaultValue.ToString().Replace("'","").Replace("\"","");
 438                 break;
 439             case "guid":
 440                 sysType=  string.Format("\"{0}\"",col.DefaultValue);
 441                  break;
 442             case "datetime":
 443                         {
 444                                 if (col.DefaultValue.ToLower() == "current_time" || col.DefaultValue.ToLower() == "current_date"|| col.DefaultValue.ToLower() == "current_timestamp")
 445                                         sysType=  "SystemMethods.CurrentDateTime";
 446                                 else
 447                         sysType=  "\"" +col.DefaultValue+"\"";
 448                 break;
 449                         }
 450         }
 451         return sysType;
 452 }
 453
 454 Tables LoadTables(bool makeSingular)
 455 {
 456         InitConnectionString();
 457
 458         WriteLine("// This file was automatically generated by the PetaPoco T4 Template");
 459         WriteLine("// Do not make changes directly to this file - edit the template instead");
 460         WriteLine("// ");
 461         WriteLine("// The following connection settings were used to generate this file");
 462         WriteLine("// ");
 463         WriteLine("//     Connection String Name: `{0}`", ConnectionStringName);
 464         WriteLine("//     Provider:               `{0}`", ProviderName);
 465         WriteLine("//     Connection String:      `{0}`", zap_password(ConnectionString));
 466         WriteLine("//     Schema:                 `{0}`", SchemaName);
 467         WriteLine("//     Include Views:          `{0}`", IncludeViews);
 468         WriteLine("");
 469
 470         DbProviderFactory _factory;
 471         try
 472         {
 473                 _factory = DbProviderFactories.GetFactory(ProviderName);
 474         }
 475         catch (Exception x)
 476         {
 477                 var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 478                 Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
 479                 WriteLine("");
 480                 WriteLine("// -----------------------------------------------------------------------------------------");
 481                 WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
 482                 WriteLine("// -----------------------------------------------------------------------------------------");
 483                 WriteLine("");
 484                 return new Tables();
 485         }
 486     WriteLine("//     Factory Name:          `{0}`", _factory.GetType().Name);
 487
 488         try
 489         {
 490                 Tables result;
 491                 using(var conn=_factory.CreateConnection())
 492                 {
 493                         conn.ConnectionString=ConnectionString;
 494                         conn.Open();
 495
 496                         SchemaReader reader=null;
 497
 498                         if (_factory.GetType().Name == "MySqlClientFactory")
 499                         {
 500                                 // MySql
 501                                 reader=new MySqlSchemaReader();
 502                         }
 503                         else if (_factory.GetType().Name == "SqlCeProviderFactory")
 504                         {
 505                                 // SQL CE
 506                                 reader=new SqlServerCeSchemaReader();
 507                         }
 508                         else if (_factory.GetType().Name == "NpgsqlFactory")
 509                         {
 510                                 // PostgreSQL
 511                                 reader=new PostGreSqlSchemaReader();
 512                         }
 513                         else if (_factory.GetType().Name == "OracleClientFactory")
 514                         {
 515                                 // Oracle
 516                                 reader=new OracleSchemaReader();
 517                         }
 518                         else if (_factory.GetType().Name == "SQLiteFactory")
 519                         {
 520                                 // Sqlite
 521                                 reader=new SqliteSchemaReader();
 522                         }
 523                         else
 524                         {
 525                                 // Assume SQL Server
 526                                 reader=new SqlServerSchemaReader();
 527                         }
 528
 529                         reader.outer=this;
 530                         result=reader.ReadSchema(conn, _factory);
 531
 532                         // Remove unrequired tables/views
 533                         for (int i=result.Count-1; i>=0; i--)
 534                         {
 535                                 if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
 536                                 {
 537                                         result.RemoveAt(i);
 538                                         continue;
 539                                 }
 540                                 if ((!IncludeViews && result[i].IsView) ||(!IncludeFunctions && result[i].IsFunction))
 541                                 {
 542                                         result.RemoveAt(i);
 543                                         continue;
 544                                 }
 545                         }
 546         }
 547
 548         var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
 549         foreach (var t in result)
 550         {
 551             if (!makeSingular)
 552             {
 553                 t.ClassName = t.CleanName;
 554             }
 555             t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
 556
 557             foreach (var c in t.Columns)
 558             {
 559                 c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
 560
 561                 // Make sure property name doesn't clash with class name
 562                 if (c.PropertyName == t.ClassName)
 563                     c.PropertyName = "_" + c.PropertyName;
 564             }
 565         }
 566
 567         return result;
 568
 569     }
 570     catch (Exception x)
 571     {
 572         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 573         Warning(string.Format("Failed to read database schema - {0}", error));
 574         WriteLine("");
 575         WriteLine("// -----------------------------------------------------------------------------------------");
 576         WriteLine("// Failed to read database schema - {0}", error);
 577         WriteLine("// -----------------------------------------------------------------------------------------");
 578         WriteLine("");
 579         return new Tables();
 580     }
 581
 582
 583 }
 584
 585 List<SP> SPsNotSupported(string providerName)
 586 {
 587         Warning("SP function creation is not supported for " + providerName);
 588         WriteLine("");
 589         WriteLine("// -----------------------------------------------------------------------------------------");
 590         WriteLine("// SP function creation is not supported for  `{0}`", providerName);
 591         WriteLine("// -----------------------------------------------------------------------------------------");
 592         return new List<SP>();
 593 }
 594
 595 List<SP> LoadSPs()
 596 {
 597         InitConnectionString();
 598
 599         WriteLine("// This file was automatically generated by the PetaPoco T4 Template");
 600         WriteLine("// Do not make changes directly to this file - edit the template instead");
 601         WriteLine("// ");
 602         WriteLine("// The following connection settings were used to generate this file");
 603         WriteLine("// ");
 604         WriteLine("//     Connection String Name: `{0}`", ConnectionStringName);
 605         WriteLine("//     Provider:               `{0}`", ProviderName);
 606         WriteLine("//     Connection String:      `{0}`", zap_password(ConnectionString));
 607         WriteLine("//     Schema:                 `{0}`", SchemaName);
 608         WriteLine("//     Include Views:          `{0}`", IncludeViews);
 609         WriteLine("");
 610
 611         DbProviderFactory _factory;
 612         try
 613         {
 614                 _factory = DbProviderFactories.GetFactory(ProviderName);
 615         }
 616         catch (Exception x)
 617         {
 618                 var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 619                 Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
 620                 WriteLine("");
 621                 WriteLine("// -----------------------------------------------------------------------------------------");
 622                 WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
 623                 WriteLine("// -----------------------------------------------------------------------------------------");
 624                 WriteLine("");
 625                 return new List<SP>();
 626         }
 627     WriteLine("//     Factory Name:          `{0}`", _factory.GetType().Name);
 628
 629         try
 630         {
 631                 List<SP> result;
 632                 using(var conn=_factory.CreateConnection())
 633                 {
 634                         conn.ConnectionString=ConnectionString;
 635                         conn.Open();
 636
 637                         SchemaReader reader=null;
 638
 639                         if (_factory.GetType().Name == "MySqlClientFactory")
 640                         {
 641                                 // MySql
 642                                 reader=new MySqlSchemaReader();
 643                                 return SPsNotSupported(ProviderName);
 644                         }
 645                         else if (_factory.GetType().Name == "SqlCeProviderFactory")
 646                         {
 647                                 // SQL CE
 648                                 reader=new SqlServerCeSchemaReader();
 649                                 return SPsNotSupported(ProviderName);
 650                         }
 651                         else if (_factory.GetType().Name == "NpgsqlFactory")
 652                         {
 653                                 // PostgreSQL
 654                                 reader=new PostGreSqlSchemaReader();
 655                                 return SPsNotSupported(ProviderName);
 656                         }
 657                         else if (_factory.GetType().Name == "OracleClientFactory")
 658                         {
 659                                 // Oracle
 660                                 reader=new OracleSchemaReader();
 661                                 return SPsNotSupported(ProviderName);
 662                         }
 663                         else if (_factory.GetType().Name == "SQLiteFactory")
 664                         {
 665                                 // Sqlite
 666                                 reader=new SqliteSchemaReader();
 667                                 return SPsNotSupported(ProviderName);
 668                         }
 669                         else
 670                         {
 671                                 // Assume SQL Server
 672                                 reader=new SqlServerSchemaReader();
 673                         }
 674
 675             reader.outer=this;
 676             result=reader.ReadSPList(conn, _factory);
 677             // Remove unrequired procedures
 678             for (int i=result.Count-1; i>=0; i--)
 679             {
 680                 if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
 681                 {
 682                     result.RemoveAt(i);
 683                     continue;
 684                 }
 685             }
 686         }
 687         return result;
 688     }
 689     catch (Exception x)
 690     {
 691         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 692         Warning(string.Format("Failed to read database schema - {0}", error));
 693         WriteLine("");
 694         WriteLine("// -----------------------------------------------------------------------------------------");
 695         WriteLine("// Failed to read database schema - {0}", error);
 696         WriteLine("// -----------------------------------------------------------------------------------------");
 697         WriteLine("");
 698         return new List<SP>();
 699     }
 700
 701
 702 }
 703
 704 public bool IsTableNameInList(string tableName, Tables tbls)
 705 {
 706         if (tbls == null)
 707                 return false;
 708         foreach(var tbItem in tbls)
 709         {
 710                 if (String.Equals(tbItem.Name,tableName,StringComparison.InvariantCultureIgnoreCase))
 711                 {
 712                         return true;
 713                 }
 714         }
 715         return false;
 716 }
 717
 718 public Table GetTableFromListByName(string tableName, Tables tbls)
 719 {
 720         if (tbls == null)
 721                 return null;
 722         foreach(var tbItem in tbls)
 723         {
 724                 if (String.Equals(tbItem.Name,tableName,StringComparison.InvariantCultureIgnoreCase))
 725                 {
 726                         return tbItem;
 727                 }
 728         }
 729         return null;
 730 }
 731
 732   void SaveOutput(string outputFileName)
 733   {
 734       string templateDirectory = Path.GetDirectoryName(Host.TemplateFile);
 735       string outputFilePath = Path.Combine(templateDirectory, outputFileName);
 736       File.WriteAllText(outputFilePath, this.GenerationEnvironment.ToString());
 737
 738       this.GenerationEnvironment.Remove(0, this.GenerationEnvironment.Length);
 739   }
 740
 741 abstract class SchemaReader
 742 {
 743         public abstract Tables ReadSchema(DbConnection connection, DbProviderFactory factory);
 744         public abstract List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory);
 745         public GeneratedTextTransformation outer;
 746         public void WriteLine(string o)
 747         {
 748                 outer.WriteLine(o);
 749         }
 750
 751 }
 752
 753 static int GetDatatypePrecision(string type)
 754 {
 755         int startPos = type.IndexOf(",");
 756         if (startPos < 0)
 757                 return -1;
 758         int endPos = type.IndexOf(")");
 759         if (endPos < 0)
 760                 return -1;
 761         string typePrecisionStr = type.Substring(startPos+1,endPos-startPos-1);
 762         int result = -1;
 763         if (int.TryParse(typePrecisionStr,out result))
 764                 return result;
 765         else
 766                 return -1;
 767 }
 768
 769 static int GetDatatypeSize(string type)
 770 {
 771         int startPos = type.IndexOf("(");
 772         if (startPos < 0)
 773                 return -1;
 774         int endPos = type.IndexOf(",");
 775         if (endPos < 0)
 776         {
 777                 endPos = type.IndexOf(")");
 778         }
 779         string typeSizeStr = type.Substring(startPos+1,endPos-startPos-1);
 780         int result = -1;
 781         if (int.TryParse(typeSizeStr,out result))
 782                 return result;
 783         else
 784                 return -1;
 785 }
 786
 787 // Edit here to get a method to read the proc
 788 class SqlServerSchemaReader : SchemaReader
 789 {
 790         // SchemaReader.ReadSchema
 791
 792
 793         public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
 794         {
 795                 var result=new Tables();
 796
 797                 _connection=connection;
 798                 _factory=factory;
 799
 800                 var cmd=_factory.CreateCommand();
 801                 cmd.Connection=connection;
 802                 cmd.CommandText=TABLE_SQL;
 803
 804                 //pull the tables in a reader
 805                 using(cmd)
 806                 {
 807
 808                         using (var rdr=cmd.ExecuteReader())
 809                         {
 810                                 while(rdr.Read())
 811                                 {
 812                                         Table tbl=new Table();
 813                                         tbl.Name=rdr["TABLE_NAME"].ToString();
 814                                         tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
 815                                         tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
 816                                         tbl.IsFunction=string.Compare(rdr["TABLE_TYPE"].ToString(), "TVF", true)==0;
 817                                         tbl.CleanName=CleanUp(tbl.Name);
 818                                         tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
 819
 820                                         result.Add(tbl);
 821                                 }
 822                         }
 823                 }
 824
 825                 foreach (var tbl in result)
 826                 {
 827                         tbl.Columns=LoadColumns(tbl);
 828
 829                         // Mark the primary key
 830                         string PrimaryKey=GetPK(tbl.Name);
 831                         var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
 832                         if(pkColumn!=null)
 833                         {
 834                                 pkColumn.IsPK=true;
 835                         }
 836                 }
 837
 838
 839                 return result;
 840         }
 841
 842         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
 843         {
 844                 var result=new List<SP>();
 845
 846                 _connection=connection;
 847                 _factory=factory;
 848
 849                 var cmd=_factory.CreateCommand();
 850                 cmd.Connection=connection;
 851                 cmd.CommandText=SP_NAMES_SQL;
 852
 853         //pull the tables in a reader
 854         using(cmd)
 855         {
 856             using (var rdr=cmd.ExecuteReader())
 857             {
 858                 while(rdr.Read())
 859                 {
 860                     SP sp=new SP();
 861                     sp.Name=rdr["sp_name"].ToString();
 862                     sp.Schema = rdr["schema_name"].ToString();
 863                     sp.CleanName=CleanUp(sp.Name);
 864                     sp.ClassName=Inflector.MakeSingular(sp.CleanName);
 865                     result.Add(sp);
 866                 }
 867             }
 868         }
 869         foreach (var sp in result)
 870         {
 871             sp.Parameters=LoadSPParams(sp);
 872         }
 873         return result;
 874     }
 875
 876     DbConnection _connection;
 877     DbProviderFactory _factory;
 878
 879         List<Column> LoadColumns(Table tbl)
 880         {
 881
 882                 using (var cmd=_factory.CreateCommand())
 883                 {
 884                         cmd.Connection=_connection;
 885                         cmd.CommandText=COLUMN_SQL;
 886
 887                         var p = cmd.CreateParameter();
 888                         p.ParameterName = "@tableName";
 889                         p.Value=tbl.Name;
 890                         cmd.Parameters.Add(p);
 891
 892                         p = cmd.CreateParameter();
 893                         p.ParameterName = "@schemaName";
 894                         p.Value=tbl.Schema;
 895                         cmd.Parameters.Add(p);
 896
 897                         var result=new List<Column>();
 898                         using (IDataReader rdr=cmd.ExecuteReader())
 899                         {
 900                                 while(rdr.Read())
 901                                 {
 902                                         Column col=new Column();
 903                                         col.Name=rdr["ColumnName"].ToString();
 904                                         col.PropertyName=CleanUp(col.Name);
 905                                         col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
 906                                         col.Size=GetDatatypeSize(rdr["DataType"].ToString());
 907                                         col.Precision=GetDatatypePrecision(rdr["DataType"].ToString());
 908                                         col.IsNullable=rdr["IsNullable"].ToString()=="YES";
 909                                         col.IsAutoIncrement=((int)rdr["IsIdentity"])==1;
 910                                         col.IsComputed=((int)rdr["IsComputed"])==1;
 911                                         result.Add(col);
 912                                 }
 913                         }
 914
 915                         return result;
 916                 }
 917         }
 918
 919         List<SPParam> LoadSPParams(SP sp)
 920         {
 921                 using (var cmd=_factory.CreateCommand())
 922                 {
 923                         cmd.Connection=_connection;
 924                         cmd.CommandText=SP_PARAMETERS_SQL;
 925
 926                         var p = cmd.CreateParameter();
 927                         p.ParameterName = "@spname";
 928                         p.Value=sp.Name;
 929                         cmd.Parameters.Add(p);
 930
 931                         var result=new List<SPParam>();
 932                         using (IDataReader rdr=cmd.ExecuteReader())
 933                         {
 934                                 while(rdr.Read())
 935                                 {
 936                                     if (rdr["IS_RESULT"].ToString().ToUpper() == "YES")
 937                                            continue;
 938                                         SPParam param=new SPParam();
 939                                         param.SysType=GetPropertyType(rdr["DATA_TYPE"].ToString());
 940                                         param.NullableSysType=GetNullablePropertyType(rdr["DATA_TYPE"].ToString());
 941                                         param.DbType=GetDbType(rdr["DATA_TYPE"].ToString()).ToString();
 942                                         param.Name=rdr["PARAMETER_NAME"].ToString().Replace("@","");
 943                                         param.CleanName=CleanUp(param.Name);
 944                                     if (rdr["PARAMETER_MODE"].ToString().ToUpper() == "OUT")
 945                                            param.Direction = SPParamDir.OutDirection;
 946                                         else if (rdr["PARAMETER_MODE"].ToString().ToUpper() == "IN")
 947                                            param.Direction = SPParamDir.InDirection;
 948                                         else
 949                                            param.Direction = SPParamDir.InAndOutDirection;
 950                                         result.Add(param);
 951                                 }
 952                         }
 953                         return result;
 954                 }
 955         }
 956
 957
 958     string GetPK(string table){
 959
 960         string sql=@"SELECT c.name AS ColumnName
 961                 FROM sys.indexes AS i
 962                 INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
 963                 INNER JOIN sys.objects AS o ON i.object_id = o.object_id
 964                 LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
 965                 WHERE (i.is_primary_key = 1) AND (o.name = @tableName)";
 966
 967                 using (var cmd=_factory.CreateCommand())
 968                 {
 969                         cmd.Connection=_connection;
 970                         cmd.CommandText=sql;
 971
 972                         var p = cmd.CreateParameter();
 973                         p.ParameterName = "@tableName";
 974                         p.Value=table;
 975                         cmd.Parameters.Add(p);
 976
 977                         var result = "";
 978                         DbDataReader reader = cmd.ExecuteReader();
 979                         try
 980                         {
 981                                 if (reader.Read())
 982                                 {
 983                                         result = reader[0].ToString();
 984                                         if (reader.Read())
 985                                         {
 986                                                 result = "";
 987                                         }
 988                                 }
 989                         }
 990                         finally
 991                         {
 992                                 // Always call Close when done reading.
 993                                 reader.Close();
 994                         }
 995                         return result;
 996                 }
 997         }
 998
 999         string GetPropertyType(string sqlType)
1000         {
1001                 string propertyType,dbType;
1002                 GetPropertyAndDbType(sqlType,out propertyType,out dbType);
1003                 return propertyType;
1004         }
1005
1006     string GetNullablePropertyType(string sqlType)
1007     {
1008         string value = GetPropertyType(sqlType);
1009         if (value.ToUpper() != "STRING" && value.ToUpper() != "BYTE[]")
1010             return value + "?";
1011         else
1012             return value;
1013     }
1014
1015         string GetDbType(string sqlType)
1016         {
1017                 string propertyType,dbType;
1018                 GetPropertyAndDbType(sqlType,out propertyType,out dbType);
1019                 return dbType;
1020         }
1021
1022
1023         void GetPropertyAndDbType(string sqlType, out string propertyType,out string dbType)
1024         {
1025                 string sysType="string";
1026                 string sysDbType="DbType.String";
1027                 switch (sqlType)
1028                 {
1029                         case "varchar":
1030                                 sysType = "string";
1031                                 sysDbType = "DbType.AnsiString";
1032                                 break;
1033                         case "bigint":
1034                                 sysType = "long";
1035                                 sysDbType = "DbType.Int64";
1036                                 break;
1037                         case "smallint":
1038                                 sysType= "short";
1039                                 sysDbType = "DbType.Int16";
1040                                 break;
1041                         case "int":
1042                                 sysType= "int";
1043                                 sysDbType = "DbType.Int32";
1044                                 break;
1045                         case "uniqueidentifier":
1046                                 sysType=  "Guid";
1047                                  sysDbType = "DbType.Guid";
1048                                  break;
1049                         case "smalldatetime":
1050                         case "datetime":
1051                         case "datetime2":
1052                         case "date":
1053                         case "time":
1054                                 sysType=  "DateTime";
1055                                 sysDbType = "DbType.DateTime";
1056                                 break;
1057                         case "datetimeoffset":
1058                                 sysType = "DateTimeOffset";
1059                                 sysDbType = "DbType.DateTimeOffset";
1060                                 break;
1061                         case "float":
1062                                 sysType="double";
1063                                 sysDbType = "DbType.Double";
1064                                 break;
1065                         case "real":
1066                                 sysType="float";
1067                                 sysDbType = "DbType.Double";
1068                                 break;
1069                         case "numeric":
1070                         case "smallmoney":
1071                         case "decimal":
1072                         case "money":
1073                                 sysType=  "decimal";
1074                                 sysDbType = "DbType.Decimal";
1075                                 break;
1076                         case "tinyint":
1077                                 sysType = "byte";
1078                                 sysDbType = "DbType.Byte";
1079                                 break;
1080                         case "bit":
1081                                 sysType=  "bool";
1082                                 sysDbType = "DbType.Boolean";
1083                                 break;
1084                         case "image":
1085                         case "binary":
1086                         case "varbinary":
1087                         case "timestamp":
1088                                 sysType=  "byte[]";
1089                                 sysDbType = "DbType.Binary";
1090                                 break;
1091                         case "geography":
1092                                 sysType = "Microsoft.SqlServer.Types.SqlGeography";
1093                                 sysDbType = "DbType.";
1094                                 break;
1095                         case "geometry":
1096                                 sysType = "Microsoft.SqlServer.Types.SqlGeometry";
1097                                 sysDbType = "DbType.";
1098                                 break;
1099                 }
1100                 propertyType = sysType;
1101                 dbType = sysDbType;
1102         }
1103
1104         string GetDBType(string sqlType)
1105         {
1106                 string sysType="string";
1107                 switch (sqlType)
1108                 {
1109                         case "bigint":
1110                                 sysType = "long";
1111                                 break;
1112                         case "smallint":
1113                                 sysType= "short";
1114                                 break;
1115                         case "int":
1116                                 sysType= "int";
1117                                 break;
1118                         case "uniqueidentifier":
1119                                 sysType=  "Guid";
1120                                  break;
1121                         case "smalldatetime":
1122                         case "datetime":
1123                         case "datetime2":
1124                         case "date":
1125                         case "time":
1126                                 sysType=  "DateTime";
1127                                   break;
1128                         case "datetimeoffset":
1129                                 sysType = "DateTimeOffset";
1130                                 break;
1131                         case "float":
1132                                 sysType="double";
1133                                 break;
1134                         case "real":
1135                                 sysType="float";
1136                                 break;
1137                         case "numeric":
1138                         case "smallmoney":
1139                         case "decimal":
1140                         case "money":
1141                                 sysType=  "decimal";
1142                                  break;
1143                         case "tinyint":
1144                                 sysType = "byte";
1145                                 break;
1146                         case "bit":
1147                                 sysType=  "bool";
1148                                    break;
1149                         case "image":
1150                         case "binary":
1151                         case "varbinary":
1152                         case "timestamp":
1153                                 sysType=  "byte[]";
1154                                  break;
1155                         case "geography":
1156                                 sysType = "Microsoft.SqlServer.Types.SqlGeography";
1157                                 break;
1158                         case "geometry":
1159                                 sysType = "Microsoft.SqlServer.Types.SqlGeometry";
1160                                 break;
1161                 }
1162                 return sysType;
1163         }
1164
1165
1166     const string TABLE_SQL=@"SELECT * FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
1167                                                                 UNION
1168                                                         SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, 'TVF' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' AND DATA_TYPE = 'TABLE'";
1169
1170     const string COLUMN_SQL=@"SELECT T.[Database] ,
1171                                                                            T.Owner ,
1172                                                                            T.TableName ,
1173                                                                            T.ColumnName ,
1174                                                                            T.OrdinalPosition ,
1175                                                                            T.DefaultSetting ,
1176                                                                            T.IsNullable ,
1177                                                                            T.DataType ,
1178                                                                            T.MaxLength ,
1179                                                                            T.DatePrecision ,
1180                                                                            T.IsIdentity ,
1181                                                                            T.IsComputed FROM (
1182                                                                 SELECT
1183                                                                                         TABLE_CATALOG AS [Database],
1184                                                                                         TABLE_SCHEMA AS Owner,
1185                                                                                         TABLE_NAME AS TableName,
1186                                                                                         COLUMN_NAME AS ColumnName,
1187                                                                                         ORDINAL_POSITION AS OrdinalPosition,
1188                                                                                         COLUMN_DEFAULT AS DefaultSetting,
1189                                                                                         IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
1190                                                                                         CHARACTER_MAXIMUM_LENGTH AS MaxLength,
1191                                                                                         DATETIME_PRECISION AS DatePrecision,
1192                                                                                         COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
1193                                                                                         COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
1194                                                                                 FROM  INFORMATION_SCHEMA.COLUMNS
1195                                                                                 WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA=@schemaName
1196                                                                                 --ORDER BY OrdinalPosition ASC
1197                                                                 UNION
1198                                                                 SELECT TABLE_CATALOG AS [Database],
1199                                                                                         TABLE_SCHEMA AS Owner,
1200                                                                                         TABLE_NAME AS TableName,
1201                                                                                         COLUMN_NAME AS ColumnName,
1202                                                                                         ORDINAL_POSITION AS OrdinalPosition,
1203                                                                                         COLUMN_DEFAULT AS DefaultSetting,
1204                                                                                         IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
1205                                                                                         CHARACTER_MAXIMUM_LENGTH AS MaxLength,
1206                                                                                         DATETIME_PRECISION AS DatePrecision,
1207                                                                                         COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
1208                                                                                         COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
1209                                                                 FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
1210                                                                 WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA=@schemaName
1211                                                                 ) T
1212                                                                 ORDER BY T.OrdinalPosition ASC";
1213
1214     const string SP_NAMES_SQL=@"SELECT  o.name AS sp_name, s.name AS schema_name
1215 FROM    sys.objects o
1216         INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
1217 WHERE   o.type = 'P'
1218         AND o.name NOT IN ( 'fn_diagramobjects', 'sp_alterdiagram',
1219                             'sp_creatediagram', 'sp_dropdiagram',
1220                             'sp_helpdiagramdefinition', 'sp_helpdiagrams',
1221                             'sp_renamediagram', 'sp_upgraddiagrams',
1222                             'sysdiagrams' )";
1223
1224
1225     const string SP_PARAMETERS_SQL=@"SELECT * from information_schema.PARAMETERS
1226                                 where SPECIFIC_NAME = @spname
1227                                 order by ORDINAL_POSITION";
1228
1229 }
1230
1231 class SqlServerCeSchemaReader : SchemaReader
1232 {
1233         // SchemaReader.ReadSchema
1234         public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1235         {
1236                 var result=new Tables();
1237
1238                 _connection=connection;
1239                 _factory=factory;
1240
1241                 var cmd=_factory.CreateCommand();
1242                 cmd.Connection=connection;
1243                 cmd.CommandText=TABLE_SQL;
1244
1245                 //pull the tables in a reader
1246                 using(cmd)
1247                 {
1248                         using (var rdr=cmd.ExecuteReader())
1249                         {
1250                                 while(rdr.Read())
1251                                 {
1252                                         Table tbl=new Table();
1253                                         tbl.Name=rdr["TABLE_NAME"].ToString();
1254                                         tbl.CleanName=CleanUp(tbl.Name);
1255                                         tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1256                                         tbl.Schema=null;
1257                                         tbl.IsView=false;
1258                                         result.Add(tbl);
1259                                 }
1260                         }
1261                 }
1262
1263                 foreach (var tbl in result)
1264                 {
1265                         tbl.Columns=LoadColumns(tbl);
1266
1267                         // Mark the primary key
1268                         string PrimaryKey=GetPK(tbl.Name);
1269                         var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
1270                         if(pkColumn!=null)
1271                                 pkColumn.IsPK=true;
1272                 }
1273
1274
1275                 return result;
1276         }
1277
1278         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
1279         {
1280                 return new List<SP>();
1281         }
1282
1283         DbConnection _connection;
1284         DbProviderFactory _factory;
1285
1286
1287         List<Column> LoadColumns(Table tbl)
1288         {
1289
1290                 using (var cmd=_factory.CreateCommand())
1291                 {
1292                         cmd.Connection=_connection;
1293                         cmd.CommandText=COLUMN_SQL;
1294
1295                         var p = cmd.CreateParameter();
1296                         p.ParameterName = "@tableName";
1297                         p.Value=tbl.Name;
1298                         cmd.Parameters.Add(p);
1299
1300                         var result=new List<Column>();
1301                         using (IDataReader rdr=cmd.ExecuteReader())
1302                         {
1303                                 while(rdr.Read())
1304                                 {
1305                                         Column col=new Column();
1306                                         col.Name=rdr["ColumnName"].ToString();
1307                                         col.PropertyName=CleanUp(col.Name);
1308                                         col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
1309                                         col.Size=GetDatatypeSize(rdr["DataType"].ToString());
1310                                         col.Precision=GetDatatypePrecision(rdr["DataType"].ToString());
1311                                         col.IsNullable=rdr["IsNullable"].ToString()=="YES";
1312                                         col.IsAutoIncrement=rdr["AUTOINC_INCREMENT"]!=DBNull.Value;
1313                                         result.Add(col);
1314                                 }
1315                         }
1316
1317                         return result;
1318                 }
1319         }
1320
1321         string GetPK(string table){
1322
1323                 string sql=@"SELECT KCU.COLUMN_NAME
1324                         FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
1325                         JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
1326                         ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
1327                         WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
1328                         AND KCU.TABLE_NAME=@tableName";
1329
1330                 using (var cmd=_factory.CreateCommand())
1331                 {
1332                         cmd.Connection=_connection;
1333                         cmd.CommandText=sql;
1334
1335                         var p = cmd.CreateParameter();
1336                         p.ParameterName = "@tableName";
1337                         p.Value=table;
1338                         cmd.Parameters.Add(p);
1339
1340                         var result = "";
1341                         DbDataReader reader = cmd.ExecuteReader();
1342                         try
1343                         {
1344                                 if (reader.Read())
1345                                 {
1346                                         result = reader[0].ToString();
1347                                         if (reader.Read())
1348                                         {
1349                                                 result = "";
1350                                         }
1351                                 }
1352                         }
1353                         finally
1354                         {
1355                                 // Always call Close when done reading.
1356                                 reader.Close();
1357                         }
1358                         return result;
1359                 }
1360         }
1361
1362         string GetPropertyType(string sqlType)
1363         {
1364                 string sysType="string";
1365                 switch (sqlType)
1366                 {
1367                         case "bigint":
1368                                 sysType = "long";
1369                                 break;
1370                         case "smallint":
1371                                 sysType= "short";
1372                                 break;
1373                         case "int":
1374                                 sysType= "int";
1375                                 break;
1376                         case "uniqueidentifier":
1377                                 sysType=  "Guid";
1378                                  break;
1379                         case "smalldatetime":
1380                         case "datetime":
1381                         case "date":
1382                         case "time":
1383                                 sysType=  "DateTime";
1384                                   break;
1385                         case "float":
1386                                 sysType="double";
1387                                 break;
1388                         case "real":
1389                                 sysType="float";
1390                                 break;
1391                         case "numeric":
1392                         case "smallmoney":
1393                         case "decimal":
1394                         case "money":
1395                                 sysType=  "decimal";
1396                                  break;
1397                         case "tinyint":
1398                                 sysType = "byte";
1399                                 break;
1400                         case "bit":
1401                                 sysType=  "bool";
1402                                    break;
1403                         case "image":
1404                         case "binary":
1405                         case "varbinary":
1406                         case "timestamp":
1407                                 sysType=  "byte[]";
1408                                  break;
1409                 }
1410                 return sysType;
1411         }
1412
1413
1414
1415         const string TABLE_SQL=@"SELECT *
1416                 FROM  INFORMATION_SCHEMA.TABLES
1417                 WHERE TABLE_TYPE='TABLE'";
1418
1419         const string COLUMN_SQL=@"SELECT
1420                         TABLE_CATALOG AS [Database],
1421                         TABLE_SCHEMA AS Owner,
1422                         TABLE_NAME AS TableName,
1423                         COLUMN_NAME AS ColumnName,
1424                         ORDINAL_POSITION AS OrdinalPosition,
1425                         COLUMN_DEFAULT AS DefaultSetting,
1426                         IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
1427                         AUTOINC_INCREMENT,
1428                         CHARACTER_MAXIMUM_LENGTH AS MaxLength,
1429                         DATETIME_PRECISION AS DatePrecision
1430                 FROM  INFORMATION_SCHEMA.COLUMNS
1431                 WHERE TABLE_NAME=@tableName
1432                 ORDER BY OrdinalPosition ASC";
1433
1434 }
1435
1436
1437 class PostGreSqlSchemaReader : SchemaReader
1438 {
1439         // SchemaReader.ReadSchema
1440         public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1441         {
1442                 var result=new Tables();
1443
1444                 _connection=connection;
1445                 _factory=factory;
1446
1447                 var cmd=_factory.CreateCommand();
1448                 cmd.Connection=connection;
1449                 cmd.CommandText=TABLE_SQL;
1450
1451                 //pull the tables in a reader
1452                 using(cmd)
1453                 {
1454                         using (var rdr=cmd.ExecuteReader())
1455                         {
1456                                 while(rdr.Read())
1457                                 {
1458                                         Table tbl=new Table();
1459                                         tbl.Name=rdr["table_name"].ToString();
1460                                         tbl.Schema=rdr["table_schema"].ToString();
1461                                         tbl.IsView=string.Compare(rdr["table_type"].ToString(), "View", true)==0;
1462                                         tbl.CleanName=CleanUp(tbl.Name);
1463                                         tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1464                                         result.Add(tbl);
1465                                 }
1466                         }
1467                 }
1468
1469                 foreach (var tbl in result)
1470                 {
1471                         tbl.Columns=LoadColumns(tbl);
1472
1473                         // Mark the primary key
1474                         string PrimaryKey=GetPK(tbl.Name);
1475                         var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
1476                         if(pkColumn!=null)
1477                                 pkColumn.IsPK=true;
1478                 }
1479
1480
1481                 return result;
1482         }
1483
1484         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
1485         {
1486                 return new List<SP>();
1487         }
1488
1489         DbConnection _connection;
1490         DbProviderFactory _factory;
1491
1492
1493         List<Column> LoadColumns(Table tbl)
1494         {
1495
1496                 using (var cmd=_factory.CreateCommand())
1497                 {
1498                         cmd.Connection=_connection;
1499                         cmd.CommandText=COLUMN_SQL;
1500
1501                         var p = cmd.CreateParameter();
1502                         p.ParameterName = "@tableName";
1503                         p.Value=tbl.Name;
1504                         cmd.Parameters.Add(p);
1505
1506                         var result=new List<Column>();
1507                         using (IDataReader rdr=cmd.ExecuteReader())
1508                         {
1509                                 while(rdr.Read())
1510                                 {
1511                                         Column col=new Column();
1512                                         col.Name=rdr["column_name"].ToString();
1513                                         col.PropertyName=CleanUp(col.Name);
1514                                         col.PropertyType=GetPropertyType(rdr["udt_name"].ToString());
1515                                         col.Size=GetDatatypeSize(rdr["udt_name"].ToString());
1516                                         col.Precision=GetDatatypePrecision(rdr["udt_name"].ToString());
1517                                         col.IsNullable=rdr["is_nullable"].ToString()=="YES";
1518                                         col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
1519                                         result.Add(col);
1520                                 }
1521                         }
1522
1523                         return result;
1524                 }
1525         }
1526
1527         string GetPK(string table){
1528
1529                 string sql=@"SELECT kcu.column_name
1530                         FROM information_schema.key_column_usage kcu
1531                         JOIN information_schema.table_constraints tc
1532                         ON kcu.constraint_name=tc.constraint_name
1533                         WHERE lower(tc.constraint_type)='primary key'
1534                         AND kcu.table_name=@tablename";
1535
1536                 using (var cmd=_factory.CreateCommand())
1537                 {
1538                         cmd.Connection=_connection;
1539                         cmd.CommandText=sql;
1540
1541                         var p = cmd.CreateParameter();
1542                         p.ParameterName = "@tableName";
1543                         p.Value=table;
1544                         cmd.Parameters.Add(p);
1545
1546                         var result = "";
1547                         DbDataReader reader = cmd.ExecuteReader();
1548                         try
1549                         {
1550                                 if (reader.Read())
1551                                 {
1552                                         result = reader[0].ToString();
1553                                         if (reader.Read())
1554                                         {
1555                                                 result = "";
1556                                         }
1557                                 }
1558                         }
1559                         finally
1560                         {
1561                                 // Always call Close when done reading.
1562                                 reader.Close();
1563                         }
1564                         return result;
1565                 }
1566         }
1567
1568         string GetPropertyType(string sqlType)
1569         {
1570                 switch (sqlType)
1571                 {
1572                         case "int8":
1573                         case "serial8":
1574                                 return "long";
1575
1576                         case "bool":
1577                                 return "bool";
1578
1579                         case "bytea     ":
1580                                 return "byte[]";
1581
1582                         case "float8":
1583                                 return "double";
1584
1585                         case "int4":
1586                         case "serial4":
1587                                 return "int";
1588
1589                         case "money     ":
1590                                 return "decimal";
1591
1592                         case "numeric":
1593                                 return "decimal";
1594
1595                         case "float4":
1596                                 return "float";
1597
1598                         case "int2":
1599                                 return "short";
1600
1601                         case "time":
1602                         case "timetz":
1603                         case "timestamp":
1604                         case "timestamptz":
1605                         case "date":
1606                                 return "DateTime";
1607
1608                         default:
1609                                 return "string";
1610                 }
1611         }
1612
1613
1614
1615         const string TABLE_SQL=@"
1616                         SELECT table_name, table_schema, table_type
1617                         FROM information_schema.tables
1618                         WHERE (table_type='BASE TABLE' OR table_type='VIEW')
1619                                 AND table_schema NOT IN ('pg_catalog', 'information_schema');
1620                         ";
1621
1622         const string COLUMN_SQL=@"
1623                         SELECT column_name, is_nullable, udt_name, column_default
1624                         FROM information_schema.columns
1625                         WHERE table_name=@tableName;
1626                         ";
1627
1628 }
1629
1630 class MySqlSchemaReader : SchemaReader
1631 {
1632         // SchemaReader.ReadSchema
1633         public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1634         {
1635                 var result=new Tables();
1636
1637
1638                 var cmd=factory.CreateCommand();
1639                 cmd.Connection=connection;
1640                 cmd.CommandText=TABLE_SQL;
1641
1642                 //pull the tables in a reader
1643                 using(cmd)
1644                 {
1645                         using (var rdr=cmd.ExecuteReader())
1646                         {
1647                                 while(rdr.Read())
1648                                 {
1649                                         Table tbl=new Table();
1650                                         tbl.Name=rdr["TABLE_NAME"].ToString();
1651                                         tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
1652                                         tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1653                                         tbl.CleanName=CleanUp(tbl.Name);
1654                                         tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1655                                         result.Add(tbl);
1656                                 }
1657                         }
1658                 }
1659
1660
1661         //this will return everything for the DB
1662         var schema  = connection.GetSchema("COLUMNS");
1663
1664         //loop again - but this time pull by table name
1665         foreach (var item in result)
1666         {
1667             item.Columns=new List<Column>();
1668
1669             //pull the columns from the schema
1670             var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
1671             foreach (var row in columns)
1672             {
1673                 Column col=new Column();
1674                 col.Name=row["COLUMN_NAME"].ToString();
1675                 col.PropertyName=CleanUp(col.Name);
1676                 col.PropertyType=GetPropertyType(row);
1677                                 col.Size=GetDatatypeSize(row["DATA_TYPE"].ToString());
1678                                 col.Precision=GetDatatypePrecision(row["DATA_TYPE"].ToString());
1679                 col.IsNullable=row["IS_NULLABLE"].ToString()=="YES";
1680                 col.IsPK=row["COLUMN_KEY"].ToString()=="PRI";
1681                                 col.IsAutoIncrement=row["extra"].ToString().ToLower().IndexOf("auto_increment")>=0;
1682
1683                 item.Columns.Add(col);
1684             }
1685         }
1686
1687         return result;
1688
1689         }
1690
1691         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
1692         {
1693                 return new List<SP>();
1694         }
1695
1696         static string GetPropertyType(DataRow row)
1697         {
1698                 bool bUnsigned = row["COLUMN_TYPE"].ToString().IndexOf("unsigned")>=0;
1699                 string propType="string";
1700                 switch (row["DATA_TYPE"].ToString())
1701                 {
1702                         case "bigint":
1703                                 propType= bUnsigned ? "ulong" : "long";
1704                                 break;
1705                         case "int":
1706                                 propType= bUnsigned ? "uint" : "int";
1707                                 break;
1708                         case "smallint":
1709                                 propType= bUnsigned ? "ushort" : "short";
1710                                 break;
1711                         case "guid":
1712                                 propType=  "Guid";
1713                                  break;
1714                         case "smalldatetime":
1715                         case "date":
1716                         case "datetime":
1717                         case "timestamp":
1718                                 propType=  "DateTime";
1719                                   break;
1720                         case "float":
1721                                 propType="float";
1722                                 break;
1723                         case "double":
1724                                 propType="double";
1725                                 break;
1726                         case "numeric":
1727                         case "smallmoney":
1728                         case "decimal":
1729                         case "money":
1730                                 propType=  "decimal";
1731                                  break;
1732                         case "bit":
1733                         case "bool":
1734                         case "boolean":
1735                                 propType=  "bool";
1736                                 break;
1737                         case "tinyint":
1738                                 propType =  bUnsigned ? "byte" : "sbyte";
1739                                 break;
1740                         case "image":
1741                         case "binary":
1742                         case "blob":
1743                         case "mediumblob":
1744                         case "longblob":
1745                         case "varbinary":
1746                                 propType=  "byte[]";
1747                                  break;
1748
1749                 }
1750                 return propType;
1751         }
1752
1753         const string TABLE_SQL=@"
1754                         SELECT *
1755                         FROM information_schema.tables
1756                         WHERE (table_type='BASE TABLE' OR table_type='VIEW')
1757                         ";
1758
1759 }
1760
1761 class OracleSchemaReader : SchemaReader
1762 {
1763         // SchemaReader.ReadSchema
1764         public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1765         {
1766                 var result=new Tables();
1767
1768                 _connection=connection;
1769                 _factory=factory;
1770
1771                 var cmd=_factory.CreateCommand();
1772                 cmd.Connection=connection;
1773                 cmd.CommandText=TABLE_SQL;
1774                 cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1775
1776                 //pull the tables in a reader
1777                 using(cmd)
1778                 {
1779
1780                         using (var rdr=cmd.ExecuteReader())
1781                         {
1782                                 while(rdr.Read())
1783                                 {
1784                                         Table tbl=new Table();
1785                                         tbl.Name=rdr["TABLE_NAME"].ToString();
1786                                         tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
1787                                         tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1788                                         tbl.CleanName=CleanUp(tbl.Name);
1789                                         tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1790                                         result.Add(tbl);
1791                                 }
1792                         }
1793                 }
1794
1795                 foreach (var tbl in result)
1796                 {
1797                         tbl.Columns=LoadColumns(tbl);
1798
1799                         // Mark the primary key
1800                         string PrimaryKey=GetPK(tbl.Name);
1801                         var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
1802                         if(pkColumn!=null)
1803                                 pkColumn.IsPK=true;
1804                 }
1805
1806
1807                 return result;
1808         }
1809
1810         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
1811         {
1812                 return new List<SP>();
1813         }
1814
1815         DbConnection _connection;
1816         DbProviderFactory _factory;
1817
1818
1819         List<Column> LoadColumns(Table tbl)
1820         {
1821
1822                 using (var cmd=_factory.CreateCommand())
1823                 {
1824                         cmd.Connection=_connection;
1825                         cmd.CommandText=COLUMN_SQL;
1826                         cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1827
1828                         var p = cmd.CreateParameter();
1829                         p.ParameterName = ":tableName";
1830                         p.Value=tbl.Name;
1831                         cmd.Parameters.Add(p);
1832
1833                         var result=new List<Column>();
1834                         using (IDataReader rdr=cmd.ExecuteReader())
1835                         {
1836                                 while(rdr.Read())
1837                                 {
1838                                         Column col=new Column();
1839                                         col.Name=rdr["ColumnName"].ToString();
1840                                         col.PropertyName=CleanUp(col.Name);
1841                                         col.PropertyType=GetPropertyType(rdr["DataType"].ToString(), (rdr["DataType"] == DBNull.Value ? null : rdr["DataType"].ToString()));
1842                                         col.Size=GetDatatypeSize(rdr["DataType"].ToString());
1843                                         col.Precision=GetDatatypePrecision(rdr["DataType"].ToString());
1844                                         col.IsNullable=rdr["IsNullable"].ToString()=="YES";
1845                     col.IsAutoIncrement=false;
1846                                         result.Add(col);
1847                                 }
1848                         }
1849
1850                         return result;
1851                 }
1852         }
1853
1854         string GetPK(string table){
1855
1856                 string sql=@"select column_name from USER_CONSTRAINTS uc
1857   inner join USER_CONS_COLUMNS ucc on uc.constraint_name = ucc.constraint_name
1858 where uc.constraint_type = 'P'
1859 and uc.table_name = upper(:tableName)
1860 and ucc.position = 1";
1861
1862                 using (var cmd=_factory.CreateCommand())
1863                 {
1864                         cmd.Connection=_connection;
1865                         cmd.CommandText=sql;
1866                         cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1867
1868                         var p = cmd.CreateParameter();
1869                         p.ParameterName = ":tableName";
1870                         p.Value=table;
1871                         cmd.Parameters.Add(p);
1872
1873                         var result = "";
1874                         DbDataReader reader = cmd.ExecuteReader();
1875                         try
1876                         {
1877                                 if (reader.Read())
1878                                 {
1879                                         result = reader[0].ToString();
1880                                         if (reader.Read())
1881                                         {
1882                                                 result = "";
1883                                         }
1884                                 }
1885                         }
1886                         finally
1887                         {
1888                                 // Always call Close when done reading.
1889                                 reader.Close();
1890                         }
1891                         return result;
1892                 }
1893         }
1894
1895         string GetPropertyType(string sqlType, string dataScale)
1896         {
1897                 string sysType="string";
1898                 switch (sqlType.ToLower())
1899                 {
1900                         case "bigint":
1901                                 sysType = "long";
1902                                 break;
1903                         case "smallint":
1904                                 sysType= "short";
1905                                 break;
1906                         case "int":
1907                                 sysType= "int";
1908                                 break;
1909                         case "uniqueidentifier":
1910                                 sysType=  "Guid";
1911                                  break;
1912                         case "smalldatetime":
1913                         case "datetime":
1914                         case "date":
1915                                 sysType=  "DateTime";
1916                                   break;
1917                         case "float":
1918                                 sysType="double";
1919                                 break;
1920                         case "real":
1921                         case "numeric":
1922                         case "smallmoney":
1923                         case "decimal":
1924                         case "money":
1925                         case "number":
1926                                 sysType=  "decimal";
1927                                  break;
1928                         case "tinyint":
1929                                 sysType = "byte";
1930                                 break;
1931                         case "bit":
1932                                 sysType=  "bool";
1933                                    break;
1934                         case "image":
1935                         case "binary":
1936                         case "varbinary":
1937                         case "timestamp":
1938                                 sysType=  "byte[]";
1939                                  break;
1940                 }
1941
1942                 if (sqlType == "number" && dataScale == "0")
1943                         return "long";
1944
1945                 return sysType;
1946         }
1947
1948
1949
1950         const string TABLE_SQL=@"select TABLE_NAME, 'Table' TABLE_TYPE, USER TABLE_SCHEMA
1951 from USER_TABLES
1952 union all
1953 select VIEW_NAME, 'View', USER
1954 from USER_VIEWS";
1955
1956
1957         const string COLUMN_SQL=@"select table_name TableName,
1958  column_name ColumnName,
1959  data_type DataType,
1960  data_scale DataScale,
1961  nullable IsNullable
1962  from USER_TAB_COLS utc
1963  where table_name = :tableName
1964  order by column_id";
1965
1966 }
1967
1968
1969 class SqliteSchemaReader : SchemaReader
1970 {
1971     // SchemaReader.ReadSchema
1972     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1973     {
1974         var result=new Tables();
1975         _connection=connection;
1976         _factory=factory;
1977         var cmd=_factory.CreateCommand();
1978         cmd.Connection=connection;
1979         cmd.CommandText=TABLE_SQL;
1980         //cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1981         //pull the tables in a reader
1982         using(cmd)
1983         {
1984             using (var rdr=cmd.ExecuteReader())
1985             {
1986                 while(rdr.Read())
1987                 {
1988                     Table tbl=new Table();
1989                     tbl.Name=rdr["name"].ToString();
1990                     tbl.Schema = "";
1991                     tbl.IsView=string.Compare(rdr["type"].ToString(), "view", true)==0;
1992                     tbl.CleanName=CleanUp(tbl.Name);
1993                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1994                     tbl.SQL = rdr["sql"].ToString();
1995                     result.Add(tbl);
1996                 }
1997             }
1998         }
1999         foreach (var tbl in result)
2000         {
2001             tbl.Columns=LoadColumns(tbl);
2002             tbl.Indices = LoadIndices(tbl.Name);
2003             tbl.FKeys = LoadFKeys(tbl.Name);
2004         }
2005         return result;
2006     }
2007
2008         public override List<SP> ReadSPList(DbConnection connection, DbProviderFactory factory)
2009         {
2010                 return new List<SP>();
2011         }
2012
2013     DbConnection _connection;
2014     DbProviderFactory _factory;
2015
2016     List<Column> LoadColumns(Table tbl)
2017     {
2018         using (var cmd=_factory.CreateCommand())
2019         {
2020             cmd.Connection=_connection;
2021             cmd.CommandText=string.Format(COLUMN_SQL,tbl.Name);
2022
2023             var result=new List<Column>();
2024             using (IDataReader rdr=cmd.ExecuteReader())
2025             {
2026                 while(rdr.Read())
2027                 {
2028                     Column col=new Column();
2029                     col.Name=rdr["name"].ToString();
2030                     col.PropertyName=CleanUp(col.Name);
2031                     col.PropertyType=GetPropertyType(rdr["type"].ToString(), (rdr["type"] == DBNull.Value ? null : rdr["type"].ToString()));
2032                                         col.Size=GetDatatypeSize(rdr["type"].ToString());
2033                                         col.Precision=GetDatatypePrecision(rdr["type"].ToString());
2034                     col.IsNullable=rdr["notnull"].ToString()=="0";
2035                     col.IsAutoIncrement=false;
2036                     col.IsPK=rdr["pk"].ToString()!="0";
2037                     if (col.IsPK)
2038                        col.IsAutoIncrement = tbl.SQL.ToUpper().Contains("AUTOINCREMENT");
2039                     else
2040                         col.IsAutoIncrement = false;
2041                     col.DefaultValue = rdr["dflt_value"] == DBNull.Value ? null : rdr["dflt_value"].ToString();
2042                     result.Add(col);
2043                 }
2044             }
2045             return result;
2046         }
2047     }
2048
2049     List<TableIndex> LoadIndices(string tableName)
2050     {
2051         var result=new List<TableIndex>();
2052         using (var cmd1=_factory.CreateCommand())
2053         {
2054             cmd1.Connection=_connection;
2055             cmd1.CommandText=string.Format(INDEX_SQL,tableName);
2056             using (IDataReader rdr1=cmd1.ExecuteReader())
2057             {
2058                 while(rdr1.Read())
2059                 {
2060                     TableIndex indx=new TableIndex();
2061                     indx.Name=rdr1["name"].ToString();
2062                     indx.SQL=rdr1["sql"].ToString();
2063                     indx.IndexColumns = new List<IndexColumn>();
2064                     indx.IsUnique = indx.SQL.ToUpper().Contains("UNIQUE");
2065                     using (var cmd2=_factory.CreateCommand())
2066                     {
2067                         cmd2.Connection=_connection;
2068                         cmd2.CommandText=string.Format(INDEX_INFO_SQL,indx.Name);
2069                         using (IDataReader rdr2=cmd2.ExecuteReader())
2070                         {
2071                             while(rdr2.Read())
2072                             {
2073                               IndexColumn col = new IndexColumn();
2074                               col.Name = rdr2["name"].ToString();
2075                               indx.IndexColumns.Add(col);
2076                             }
2077                         }
2078                     }
2079                     result.Add(indx);
2080                 }
2081             }
2082         }
2083         return result;
2084         }
2085
2086     List<FKey> LoadFKeys(string tblName)
2087     {
2088         using (var cmd=_factory.CreateCommand())
2089         {
2090             cmd.Connection=_connection;
2091             cmd.CommandText=string.Format(FKEY_INFO_SQL,tblName);
2092
2093             var result=new List<FKey>();
2094             using (IDataReader rdr=cmd.ExecuteReader())
2095             {
2096                 while(rdr.Read())
2097                 {
2098                     FKey key=new FKey();
2099                     key.ToTable=rdr["table"].ToString();
2100                     key.ToColumn=rdr["to"].ToString();
2101                     key.FromColumn=rdr["from"].ToString();
2102                     result.Add(key);
2103                 }
2104             }
2105             return result;
2106         }
2107     }
2108
2109
2110     string GetPropertyType(string sqlType, string dataScale)
2111     {
2112         string sysType="string";
2113         switch (sqlType.ToLower())
2114         {
2115                         case "integer":
2116             case "int":
2117             case "tinyint":
2118             case "smallint":
2119             case "mediumint":
2120             case "int2":
2121             case "int8":
2122                 sysType= "long";
2123                 break;
2124             case "bigint":
2125             case "unsigned big int":
2126                 sysType= "long";
2127                 break;
2128             case "uniqueidentifier":
2129                 sysType=  "Guid";
2130                  break;
2131             case "smalldatetime":
2132             case "datetime":
2133             case "date":
2134                 sysType=  "DateTime";
2135                   break;
2136             case "float":
2137             case "double precision":
2138             case "double":
2139                 sysType="double";
2140                 break;
2141             case "real":
2142             case "numeric":
2143             case "smallmoney":
2144             case "decimal":
2145             case "money":
2146             case "number":
2147                 sysType=  "decimal";
2148                  break;
2149             case "bit":
2150                 sysType=  "bool";
2151                    break;
2152             case "image":
2153             case "binary":
2154             case "varbinary":
2155             case "timestamp":
2156                 sysType=  "byte[]";
2157                  break;
2158         }
2159
2160         if (sqlType == "number" && dataScale == "0")
2161             return "long";
2162
2163         return sysType;
2164     }
2165
2166     const string TABLE_SQL=@"SELECT name, type , sql FROM sqlite_master WHERE type IN ('table','view') and name not in ('sqlite_sequence') ";
2167     const string COLUMN_SQL=@"pragma table_info({0})";
2168
2169     const string INDEX_SQL=@"SELECT name , sql  FROM sqlite_master WHERE type IN ('index') and lower(tbl_name) = lower('{0}')";
2170     const string INDEX_INFO_SQL=@"pragma index_info({0})";
2171
2172     const string FKEY_INFO_SQL=@"pragma foreign_key_list({0})";
2173
2174 }
2175
2176 /// <summary>
2177 /// Summary for the Inflector class
2178 /// </summary>
2179 public static class Inflector {
2180     private static readonly List<InflectorRule> _plurals = new List<InflectorRule>();
2181     private static readonly List<InflectorRule> _singulars = new List<InflectorRule>();
2182     private static readonly List<string> _uncountables = new List<string>();
2183
2184     /// <summary>
2185     /// Initializes the <see cref="Inflector"/> class.
2186     /// </summary>
2187     static Inflector() {
2188         AddPluralRule("$", "s");
2189         AddPluralRule("s$", "s");
2190         AddPluralRule("(ax|test)is$", "$1es");
2191         AddPluralRule("(octop|vir)us$", "$1i");
2192         AddPluralRule("(alias|status)$", "$1es");
2193         AddPluralRule("(bu)s$", "$1ses");
2194         AddPluralRule("(buffal|tomat)o$", "$1oes");
2195         AddPluralRule("([ti])um$", "$1a");
2196         AddPluralRule("sis$", "ses");
2197         AddPluralRule("(?:([^f])fe|([lr])f)$", "$1$2ves");
2198         AddPluralRule("(hive)$", "$1s");
2199         AddPluralRule("([^aeiouy]|qu)y$", "$1ies");
2200         AddPluralRule("(x|ch|ss|sh)$", "$1es");
2201         AddPluralRule("(matr|vert|ind)ix|ex$", "$1ices");
2202         AddPluralRule("([m|l])ouse$", "$1ice");
2203         AddPluralRule("^(ox)$", "$1en");
2204         AddPluralRule("(quiz)$", "$1zes");
2205
2206         AddSingularRule("s$", String.Empty);
2207         AddSingularRule("ss$", "ss");
2208         AddSingularRule("(n)ews$", "$1ews");
2209         AddSingularRule("([ti])a$", "$1um");
2210         AddSingularRule("((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$", "$1$2sis");
2211         AddSingularRule("(^analy)ses$", "$1sis");
2212         AddSingularRule("([^f])ves$", "$1fe");
2213         AddSingularRule("(hive)s$", "$1");
2214         AddSingularRule("(tive)s$", "$1");
2215         AddSingularRule("([lr])ves$", "$1f");
2216         AddSingularRule("([^aeiouy]|qu)ies$", "$1y");
2217         AddSingularRule("(s)eries$", "$1eries");
2218         AddSingularRule("(m)ovies$", "$1ovie");
2219         AddSingularRule("(x|ch|ss|sh)es$", "$1");
2220         AddSingularRule("([m|l])ice$", "$1ouse");
2221         AddSingularRule("(bus)es$", "$1");
2222         AddSingularRule("(o)es$", "$1");
2223         AddSingularRule("(shoe)s$", "$1");
2224         AddSingularRule("(cris|ax|test)es$", "$1is");
2225         AddSingularRule("(octop|vir)i$", "$1us");
2226         AddSingularRule("(alias|status)$", "$1");
2227         AddSingularRule("(alias|status)es$", "$1");
2228         AddSingularRule("^(ox)en", "$1");
2229         AddSingularRule("(vert|ind)ices$", "$1ex");
2230         AddSingularRule("(matr)ices$", "$1ix");
2231         AddSingularRule("(quiz)zes$", "$1");
2232
2233         AddIrregularRule("person", "people");
2234         AddIrregularRule("man", "men");
2235         AddIrregularRule("child", "children");
2236         AddIrregularRule("sex", "sexes");
2237         AddIrregularRule("tax", "taxes");
2238         AddIrregularRule("move", "moves");
2239
2240         AddUnknownCountRule("equipment");
2241         AddUnknownCountRule("information");
2242         AddUnknownCountRule("rice");
2243         AddUnknownCountRule("money");
2244         AddUnknownCountRule("species");
2245         AddUnknownCountRule("series");
2246         AddUnknownCountRule("fish");
2247         AddUnknownCountRule("sheep");
2248     }
2249
2250     /// <summary>
2251     /// Adds the irregular rule.
2252     /// </summary>
2253     /// <param name="singular">The singular.</param>
2254     /// <param name="plural">The plural.</param>
2255     private static void AddIrregularRule(string singular, string plural) {
2256         AddPluralRule(String.Concat("(", singular[0], ")", singular.Substring(1), "$"), String.Concat("$1", plural.Substring(1)));
2257         AddSingularRule(String.Concat("(", plural[0], ")", plural.Substring(1), "$"), String.Concat("$1", singular.Substring(1)));
2258     }
2259
2260     /// <summary>
2261     /// Adds the unknown count rule.
2262     /// </summary>
2263     /// <param name="word">The word.</param>
2264     private static void AddUnknownCountRule(string word) {
2265         _uncountables.Add(word.ToLower());
2266     }
2267
2268     /// <summary>
2269     /// Adds the plural rule.
2270     /// </summary>
2271     /// <param name="rule">The rule.</param>
2272     /// <param name="replacement">The replacement.</param>
2273     private static void AddPluralRule(string rule, string replacement) {
2274         _plurals.Add(new InflectorRule(rule, replacement));
2275     }
2276
2277     /// <summary>
2278     /// Adds the singular rule.
2279     /// </summary>
2280     /// <param name="rule">The rule.</param>
2281     /// <param name="replacement">The replacement.</param>
2282     private static void AddSingularRule(string rule, string replacement) {
2283         _singulars.Add(new InflectorRule(rule, replacement));
2284     }
2285
2286     /// <summary>
2287     /// Makes the plural.
2288     /// </summary>
2289     /// <param name="word">The word.</param>
2290     /// <returns></returns>
2291     public static string MakePlural(string word) {
2292         return ApplyRules(_plurals, word);
2293     }
2294
2295     /// <summary>
2296     /// Makes the singular.
2297     /// </summary>
2298     /// <param name="word">The word.</param>
2299     /// <returns></returns>
2300     public static string MakeSingular(string word) {
2301         return ApplyRules(_singulars, word);
2302     }
2303
2304     /// <summary>
2305     /// Applies the rules.
2306     /// </summary>
2307     /// <param name="rules">The rules.</param>
2308     /// <param name="word">The word.</param>
2309     /// <returns></returns>
2310     private static string ApplyRules(IList<InflectorRule> rules, string word) {
2311         string result = word;
2312         if (!_uncountables.Contains(word.ToLower())) {
2313             for (int i = rules.Count - 1; i >= 0; i--) {
2314                 string currentPass = rules[i].Apply(word);
2315                 if (currentPass != null) {
2316                     result = currentPass;
2317                     break;
2318                 }
2319             }
2320         }
2321         return result;
2322     }
2323
2324     /// <summary>
2325     /// Converts the string to title case.
2326     /// </summary>
2327     /// <param name="word">The word.</param>
2328     /// <returns></returns>
2329     public static string ToTitleCase(string word) {
2330         return Regex.Replace(ToHumanCase(AddUnderscores(word)), @"\b([a-z])",
2331             delegate(Match match) { return match.Captures[0].Value.ToUpper(); });
2332     }
2333
2334     /// <summary>
2335     /// Converts the string to human case.
2336     /// </summary>
2337     /// <param name="lowercaseAndUnderscoredWord">The lowercase and underscored word.</param>
2338     /// <returns></returns>
2339     public static string ToHumanCase(string lowercaseAndUnderscoredWord) {
2340         return MakeInitialCaps(Regex.Replace(lowercaseAndUnderscoredWord, @"_", " "));
2341     }
2342
2343     /// <summary>
2344     /// Adds the underscores.
2345     /// </summary>
2346     /// <param name="pascalCasedWord">The pascal cased word.</param>
2347     /// <returns></returns>
2348     public static string AddUnderscores(string pascalCasedWord) {
2349         return Regex.Replace(Regex.Replace(Regex.Replace(pascalCasedWord, @"([A-Z]+)([A-Z][a-z])", "$1_$2"), @"([a-z\d])([A-Z])", "$1_$2"), @"[-\s]", "_").ToLower();
2350     }
2351
2352     /// <summary>
2353     /// Makes the initial caps.
2354     /// </summary>
2355     /// <param name="word">The word.</param>
2356     /// <returns></returns>
2357     public static string MakeInitialCaps(string word) {
2358         return String.Concat(word.Substring(0, 1).ToUpper(), word.Substring(1).ToLower());
2359     }
2360
2361     /// <summary>
2362     /// Makes the initial lower case.
2363     /// </summary>
2364     /// <param name="word">The word.</param>
2365     /// <returns></returns>
2366     public static string MakeInitialLowerCase(string word) {
2367         return String.Concat(word.Substring(0, 1).ToLower(), word.Substring(1));
2368     }
2369
2370
2371     /// <summary>
2372     /// Determine whether the passed string is numeric, by attempting to parse it to a double
2373     /// </summary>
2374     /// <param name="str">The string to evaluated for numeric conversion</param>
2375     /// <returns>
2376     ///         <c>true</c> if the string can be converted to a number; otherwise, <c>false</c>.
2377     /// </returns>
2378     public static bool IsStringNumeric(string str) {
2379         double result;
2380         return (double.TryParse(str, NumberStyles.Float, NumberFormatInfo.CurrentInfo, out result));
2381     }
2382
2383     /// <summary>
2384     /// Adds the ordinal suffix.
2385     /// </summary>
2386     /// <param name="number">The number.</param>
2387     /// <returns></returns>
2388     public static string AddOrdinalSuffix(string number) {
2389         if (IsStringNumeric(number)) {
2390             int n = int.Parse(number);
2391             int nMod100 = n % 100;
2392
2393             if (nMod100 >= 11 && nMod100 <= 13)
2394                 return String.Concat(number, "th");
2395
2396             switch (n % 10) {
2397                 case 1:
2398                     return String.Concat(number, "st");
2399                 case 2:
2400                     return String.Concat(number, "nd");
2401                 case 3:
2402                     return String.Concat(number, "rd");
2403                 default:
2404                     return String.Concat(number, "th");
2405             }
2406         }
2407         return number;
2408     }
2409
2410     /// <summary>
2411     /// Converts the underscores to dashes.
2412     /// </summary>
2413     /// <param name="underscoredWord">The underscored word.</param>
2414     /// <returns></returns>
2415     public static string ConvertUnderscoresToDashes(string underscoredWord) {
2416         return underscoredWord.Replace('_', '-');
2417     }
2418
2419
2420     #region Nested type: InflectorRule
2421
2422     /// <summary>
2423     /// Summary for the InflectorRule class
2424     /// </summary>
2425     private class InflectorRule {
2426         /// <summary>
2427         ///
2428         /// </summary>
2429         public readonly Regex regex;
2430
2431         /// <summary>
2432         ///
2433         /// </summary>
2434         public readonly string replacement;
2435
2436         /// <summary>
2437         /// Initializes a new instance of the <see cref="InflectorRule"/> class.
2438         /// </summary>
2439         /// <param name="regexPattern">The regex pattern.</param>
2440         /// <param name="replacementText">The replacement text.</param>
2441         public InflectorRule(string regexPattern, string replacementText) {
2442             regex = new Regex(regexPattern, RegexOptions.IgnoreCase);
2443             replacement = replacementText;
2444         }
2445
2446         /// <summary>
2447         /// Applies the specified word.
2448         /// </summary>
2449         /// <param name="word">The word.</param>
2450         /// <returns></returns>
2451         public string Apply(string word) {
2452             if (!regex.IsMatch(word))
2453                 return null;
2454
2455             string replace = regex.Replace(word, replacement);
2456             if (word == word.ToUpper())
2457                 replace = replace.ToUpper();
2458
2459             return replace;
2460         }
2461     }
2462
2463     #endregion
2464 }
2465
2466 // https://raw.github.com/damieng/DamienGKit
2467 // http://damieng.com/blog/2009/11/06/multiple-outputs-from-t4-made-easy-revisited
2468
2469 // Manager class records the various blocks so it can split them up
2470 class Manager {
2471     private class Block {
2472         public String Name;
2473         public int Start, Length;
2474         public bool IncludeInDefault;
2475     }
2476
2477     private Block currentBlock;
2478     private List<Block> files = new List<Block>();
2479     private Block footer = new Block();
2480     private Block header = new Block();
2481     private ITextTemplatingEngineHost host;
2482     private StringBuilder template;
2483     protected List<String> generatedFileNames = new List<String>();
2484
2485     public static Manager Create(ITextTemplatingEngineHost host, StringBuilder template) {
2486         return (host is IServiceProvider) ? new VSManager(host, template) : new Manager(host, template);
2487     }
2488
2489     public void StartNewFile(String name) {
2490         if (name == null)
2491             throw new ArgumentNullException("name");
2492         CurrentBlock = new Block { Name = name };
2493     }
2494
2495     public void StartFooter(bool includeInDefault = true) {
2496         CurrentBlock = footer;
2497         footer.IncludeInDefault = includeInDefault;
2498     }
2499
2500     public void StartHeader(bool includeInDefault = true) {
2501         CurrentBlock = header;
2502         header.IncludeInDefault = includeInDefault;
2503     }
2504
2505     public void EndBlock() {
2506         if (CurrentBlock == null)
2507             return;
2508         CurrentBlock.Length = template.Length - CurrentBlock.Start;
2509         if (CurrentBlock != header && CurrentBlock != footer)
2510             files.Add(CurrentBlock);
2511         currentBlock = null;
2512     }
2513
2514     public virtual void Process(bool split, bool sync = true) {
2515         if (split) {
2516             EndBlock();
2517             String headerText = template.ToString(header.Start, header.Length);
2518             String footerText = template.ToString(footer.Start, footer.Length);
2519             String outputPath = Path.GetDirectoryName(host.TemplateFile);
2520             files.Reverse();
2521             if (!footer.IncludeInDefault)
2522                 template.Remove(footer.Start, footer.Length);
2523             foreach(Block block in files) {
2524                 String fileName = Path.Combine(outputPath, block.Name);
2525                 String content = headerText + template.ToString(block.Start, block.Length) + footerText;
2526                 generatedFileNames.Add(fileName);
2527                 CreateFile(fileName, content);
2528                 template.Remove(block.Start, block.Length);
2529             }
2530             if (!header.IncludeInDefault)
2531                 template.Remove(header.Start, header.Length);
2532         }
2533     }
2534
2535     protected virtual void CreateFile(String fileName, String content) {
2536         if (IsFileContentDifferent(fileName, content))
2537             File.WriteAllText(fileName, content);
2538     }
2539
2540     public virtual String GetCustomToolNamespace(String fileName) {
2541         return null;
2542     }
2543
2544     public virtual String DefaultProjectNamespace {
2545         get { return null; }
2546     }
2547
2548     protected bool IsFileContentDifferent(String fileName, String newContent) {
2549         return !(File.Exists(fileName) && File.ReadAllText(fileName) == newContent);
2550     }
2551
2552     private Manager(ITextTemplatingEngineHost host, StringBuilder template) {
2553         this.host = host;
2554         this.template = template;
2555     }
2556
2557     private Block CurrentBlock {
2558         get { return currentBlock; }
2559         set {
2560             if (CurrentBlock != null)
2561                 EndBlock();
2562             if (value != null)
2563                 value.Start = template.Length;
2564             currentBlock = value;
2565         }
2566     }
2567
2568     private class VSManager: Manager {
2569         private EnvDTE.ProjectItem templateProjectItem;
2570         private EnvDTE.DTE dte;
2571         private Action<String> checkOutAction;
2572         private Action<IEnumerable<String>> projectSyncAction;
2573
2574         public override String DefaultProjectNamespace {
2575             get {
2576                 return templateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString();
2577             }
2578         }
2579
2580         public override String GetCustomToolNamespace(string fileName) {
2581             return dte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString();
2582         }
2583
2584         public override void Process(bool split, bool sync) {
2585             if (templateProjectItem.ProjectItems == null)
2586                 return;
2587             base.Process(split, sync);
2588             if (sync)
2589                 projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames, null, null));
2590         }
2591
2592         protected override void CreateFile(String fileName, String content) {
2593             if (IsFileContentDifferent(fileName, content)) {
2594                 CheckoutFileIfRequired(fileName);
2595                 File.WriteAllText(fileName, content);
2596             }
2597         }
2598
2599         internal VSManager(ITextTemplatingEngineHost host, StringBuilder template)
2600             : base(host, template) {
2601             var hostServiceProvider = (IServiceProvider) host;
2602             if (hostServiceProvider == null)
2603                 throw new ArgumentNullException("Could not obtain IServiceProvider");
2604             dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE));
2605             if (dte == null)
2606                 throw new ArgumentNullException("Could not obtain DTE from host");
2607             templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);
2608             checkOutAction = (String fileName) => dte.SourceControl.CheckOutItem(fileName);
2609             projectSyncAction = (IEnumerable<String> keepFileNames) => ProjectSync(templateProjectItem, keepFileNames);
2610         }
2611
2612         private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String> keepFileNames) {
2613             var keepFileNameSet = new HashSet<String>(keepFileNames);
2614             var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>();
2615             var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames(0)) + ".";
2616             foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems)
2617                 projectFiles.Add(projectItem.get_FileNames(0), projectItem);
2618
2619             // Remove unused items from the project
2620             foreach(var pair in projectFiles)
2621                 if (!keepFileNames.Contains(pair.Key) && !(Path.GetFileNameWithoutExtension(pair.Key) + ".").StartsWith(originalFilePrefix))
2622                     pair.Value.Delete();
2623
2624             // Add missing files to the project
2625             foreach(String fileName in keepFileNameSet)
2626                 if (!projectFiles.ContainsKey(fileName))
2627                     templateProjectItem.ProjectItems.AddFromFile(fileName);
2628         }
2629
2630         private void CheckoutFileIfRequired(String fileName) {
2631             var sc = dte.SourceControl;
2632             if (sc != null && sc.IsItemUnderSCC(fileName) && !sc.IsItemCheckedOut(fileName))
2633                 checkOutAction.EndInvoke(checkOutAction.BeginInvoke(fileName, null, null));
2634         }
2635     }
2636 }
2637
2638 /*
2639     End of Manager.tt
2640 */
2641 #>