MyException - 我的异常网
当前位置:我的异常网» SQL » petapoco-SQLServer模型增多注释

petapoco-SQLServer模型增多注释

www.MyException.Cn  网友分享于:2013-12-23  浏览:0次
petapoco-SQLServer模型增加注释

petapoco是个基于T4模板的轻量级ORM,好用效率高,具体介绍略了

获取注释基本原理是调用数据库::fn_listextendedproperty函数,获取扩展属性MS_Description

technet参考资料:sys.fn_listextendedproperty (Transact-SQL)

直接上代码

首先是PetaPoco.Core.ttinclude

106行原始:

...
public string SequenceName;
    public bool Ignore;
...

新建Description字段,这个是表的注释

...
    public string SequenceName;
    public bool Ignore;
    public string Description;
...

140行原始:

...
    public bool IsAutoIncrement;
    public bool Ignore;
...

新建Description字段,这个是列的注释

...
    public bool IsAutoIncrement;
    public bool Ignore;
    public string Description;
...

517行SqlServerSchemaReader.ReadSchema方法,原始:

...
foreach (var tbl in result)
        {
            tbl.Columns=LoadColumns(tbl);
                    
            // Mark the primary key
            string PrimaryKey=GetPK(tbl.Name);
            var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
            if(pkColumn!=null)
            {
                pkColumn.IsPK=true;
            }
        }
...

修改后,调用函数获取表的注释:

...
        foreach (var tbl in result)
        {
            using(var cmdDesc=_factory.CreateCommand())
            {
                cmdDesc.Connection=connection;
                cmdDesc.CommandText=TABLE_DESC_SQL;

                DbParameter p = null;

                p = cmdDesc.CreateParameter();
                p.ParameterName = "@schema";
                p.Value=tbl.Schema;
                cmdDesc.Parameters.Add(p);

                p = cmdDesc.CreateParameter();
                p.ParameterName = "@table";
                p.Value=tbl.Name;
                cmdDesc.Parameters.Add(p);

                using (var rdrDesc=cmdDesc.ExecuteReader())
                {
                    if(rdrDesc.Read())
                        tbl.Description=rdrDesc["value"].ToString();
                }
            }
...

572行插入新的代码,获取每一列的注释:

...
            foreach (var col in result)
            {
                using(var cmdDesc=_factory.CreateCommand())
                {
                    cmdDesc.Connection=_connection;
                    cmdDesc.CommandText=COLUMN_DESC_SQL;

                    DbParameter pDesc = null;

                    pDesc = cmdDesc.CreateParameter();
                    pDesc.ParameterName = "@schema";
                    pDesc.Value=tbl.Schema;
                    cmdDesc.Parameters.Add(pDesc);

                    pDesc = cmdDesc.CreateParameter();
                    pDesc.ParameterName = "@table";
                    pDesc.Value=tbl.Name;
                    cmdDesc.Parameters.Add(pDesc);

                    pDesc = cmdDesc.CreateParameter();
                    pDesc.ParameterName = "@column";
                    pDesc.Value=col.Name;
                    cmdDesc.Parameters.Add(pDesc);
                    using (var rdrDesc=cmdDesc.ExecuteReader())
                    {
                        if(rdrDesc.Read())
                            col.Description=rdrDesc["value"].ToString();
                    }
                }
            }
...

672行插入新的代码,存储调用函数的sql语句:

...
    const string TABLE_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, null, null)";
    const string COLUMN_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, 'column', @column)";
...

附完整PetaPoco.Core.ttinclude:

   1 <#@ template language="C#v3.5" hostspecific="True" #>
   2 <#@ assembly name="EnvDTE" #>
   3 <#@ assembly name="System.Core.dll" #>
   4 <#@ assembly name="System.Data" #>
   5 <#@ assembly name="System.Xml" #>
   6 <#@ assembly name="System.Configuration" #>
   7 <#@ assembly name="System.Windows.Forms" #>
   8 <#@ import namespace="System.Collections.Generic" #>
   9 <#@ import namespace="System.Data" #>
  10 <#@ import namespace="System.Data.SqlClient" #>
  11 <#@ import namespace="System.Data.Common" #>
  12 <#@ import namespace="System.Diagnostics" #>
  13 <#@ import namespace="System.Globalization" #>
  14 <#@ import namespace="System.IO" #>
  15 <#@ import namespace="System.Linq" #>
  16 <#@ import namespace="System.Text" #>
  17 <#@ import namespace="System.Text.RegularExpressions" #>
  18 <#@ import namespace="System.Configuration" #>
  19 <#@ import namespace="System.Windows.Forms" #>
  20 <#+
  21 
  22 /*
  23  This code is part of the PetaPoco project (http://www.toptensoftware.com/petapoco).
  24  It is based on the SubSonic T4 templates but has been considerably re-organized and reduced
  25  
  26  -----------------------------------------------------------------------------------------
  27 
  28  This template can read minimal schema information from the following databases:
  29 
  30     * SQL Server
  31     * SQL Server CE
  32     * MySQL
  33     * PostGreSQL
  34     * Oracle
  35 
  36  For connection and provider settings the template will look for the web.config or app.config file of the 
  37  containing Visual Studio project.  It will not however read DbProvider settings from this file.
  38 
  39  In order to work, the appropriate driver must be registered in the system machine.config file.  If you're
  40  using Visual Studio 2010 the file you want is here:
  41 
  42     C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
  43 
  44  After making changes to machine.config you will also need to restart Visual Studio.
  45 
  46  Here's a typical set of entries that might help if you're stuck:
  47 
  48     <system.data>
  49         <DbProviderFactories>
  50             <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"/>
  51             <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"/>
  52             <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"/>
  53             <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"/>
  54             <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"/>
  55             <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"/>
  56             <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" />
  57         </DbProviderFactories>
  58     </system.data>
  59 
  60  Also, the providers and their dependencies need to be installed to GAC.  
  61 
  62  Eg; this is how I installed the drivers for PostgreSQL
  63 
  64      gacutil /i Npgsql.dll
  65      gacutil /i Mono.Security.dll
  66 
  67  -----------------------------------------------------------------------------------------
  68  
  69  SubSonic - http://subsonicproject.com
  70  
  71  The contents of this file are subject to the New BSD
  72  License (the "License"); you may not use this file
  73  except in compliance with the License. You may obtain a copy of
  74  the License at http://www.opensource.org/licenses/bsd-license.php
  75  
  76  Software distributed under the License is distributed on an 
  77  "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
  78  implied. See the License for the specific language governing
  79  rights and limitations under the License.
  80 */
  81 
  82 string ConnectionStringName = "";
  83 string Namespace = "";
  84 string RepoName = "";
  85 string ClassPrefix = "";
  86 string ClassSuffix = "";
  87 string SchemaName = null;
  88 bool IncludeViews = false;
  89 bool GenerateOperations = false;
  90 bool GenerateCommon = true;
  91 bool GeneratePocos = true;
  92 bool ExplicitColumns = true;
  93 bool TrackModifiedColumns = false;
  94 string[] ExcludePrefix = new string[] {};
  95 
  96 
  97 public class Table
  98 {
  99     public List<Column> Columns;    
 100     public string Name;
 101     public string Schema;
 102     public bool IsView;
 103     public string CleanName;
 104     public string ClassName;
 105     public string SequenceName;
 106     public bool Ignore;
 107     public string Description;
 108 
 109     public Column PK
 110     {
 111         get
 112         {
 113             return this.Columns.SingleOrDefault(x=>x.IsPK);
 114         }
 115     }
 116 
 117     public Column GetColumn(string columnName)
 118     {
 119         return Columns.Single(x=>string.Compare(x.Name, columnName, true)==0);
 120     }
 121 
 122     public Column this[string columnName]
 123     {
 124         get
 125         {
 126             return GetColumn(columnName);
 127         }
 128     }
 129 
 130 }
 131 
 132 public class Column
 133 {
 134     public string Name;
 135     public string PropertyName;
 136     public string PropertyType;
 137     public bool IsPK;
 138     public bool IsNullable;
 139     public bool IsAutoIncrement;
 140     public bool Ignore;
 141     public string Description;
 142 }
 143 
 144 public class Tables : List<Table>
 145 {
 146     public Tables()
 147     {
 148     }
 149     
 150     public Table GetTable(string tableName)
 151     {
 152         return this.Single(x=>string.Compare(x.Name, tableName, true)==0);
 153     }
 154 
 155     public Table this[string tableName]
 156     {
 157         get
 158         {
 159             return GetTable(tableName);
 160         }
 161     }
 162 
 163 }
 164 
 165 
 166 static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);
 167 
 168 static string[] cs_keywords = { "abstract", "event", "new", "struct", "as", "explicit", "null", 
 169      "switch", "base", "extern", "object", "this", "bool", "false", "operator", "throw", 
 170      "break", "finally", "out", "true", "byte", "fixed", "override", "try", "case", "float", 
 171      "params", "typeof", "catch", "for", "private", "uint", "char", "foreach", "protected", 
 172      "ulong", "checked", "goto", "public", "unchecked", "class", "if", "readonly", "unsafe", 
 173      "const", "implicit", "ref", "ushort", "continue", "in", "return", "using", "decimal", 
 174      "int", "sbyte", "virtual", "default", "interface", "sealed", "volatile", "delegate", 
 175      "internal", "short", "void", "do", "is", "sizeof", "while", "double", "lock", 
 176      "stackalloc", "else", "long", "static", "enum", "namespace", "string" };
 177 
 178 static Func<string, string> CleanUp = (str) =>
 179 {
 180     str = rxCleanUp.Replace(str, "_");
 181 
 182     if (char.IsDigit(str[0]) || cs_keywords.Contains(str))
 183         str = "@" + str;
 184     
 185     return str;
 186 };
 187 
 188 string CheckNullable(Column col)
 189 {
 190     string result="";
 191     if(col.IsNullable && 
 192         col.PropertyType !="byte[]" && 
 193         col.PropertyType !="string" &&
 194         col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
 195         col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
 196         )
 197         result="?";
 198     return result;
 199 }
 200 
 201 string GetConnectionString(ref string connectionStringName, out string providerName)
 202 {
 203     var _CurrentProject = GetCurrentProject();
 204 
 205     providerName=null;
 206     
 207     string result="";
 208     ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
 209     configFile.ExeConfigFilename = GetConfigPath();
 210 
 211     if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
 212         throw new ArgumentNullException("The project does not contain App.config or Web.config file.");
 213     
 214     
 215     var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
 216     var connSection=config.ConnectionStrings;
 217 
 218     //if the connectionString is empty - which is the defauls
 219     //look for count-1 - this is the last connection string
 220     //and takes into account AppServices and LocalSqlServer
 221     if(string.IsNullOrEmpty(connectionStringName))
 222     {
 223         if(connSection.ConnectionStrings.Count>1)
 224         {
 225             connectionStringName = connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].Name;
 226             result=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ConnectionString;
 227             providerName=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ProviderName;
 228         }            
 229     }
 230     else
 231     {
 232         try
 233         {
 234             result=connSection.ConnectionStrings[connectionStringName].ConnectionString;
 235             providerName=connSection.ConnectionStrings[connectionStringName].ProviderName;
 236         }
 237         catch
 238         {
 239             result="There is no connection string name called '"+connectionStringName+"'";
 240         }
 241     }
 242 
 243 //    if (String.IsNullOrEmpty(providerName))
 244 //        providerName="System.Data.SqlClient";
 245     
 246     return result;
 247 }
 248 
 249 string _connectionString="";
 250 string _providerName="";
 251 
 252 void InitConnectionString()
 253 {
 254     if(String.IsNullOrEmpty(_connectionString))
 255     {
 256         _connectionString=GetConnectionString(ref ConnectionStringName, out _providerName);
 257 
 258         if(_connectionString.Contains("|DataDirectory|"))
 259         {
 260             //have to replace it
 261             string dataFilePath=GetDataDirectory();
 262             _connectionString=_connectionString.Replace("|DataDirectory|",dataFilePath);
 263         }    
 264     }
 265 }
 266 
 267 public string ConnectionString
 268 {
 269     get 
 270     {
 271         InitConnectionString();
 272         return _connectionString;
 273     }
 274 }
 275 
 276 public string ProviderName
 277 {
 278     get 
 279     {
 280         InitConnectionString();
 281         return _providerName;
 282     }
 283 }
 284 
 285 public EnvDTE.Project GetCurrentProject()  {
 286 
 287     IServiceProvider _ServiceProvider = (IServiceProvider)Host;
 288     if (_ServiceProvider == null)
 289         throw new Exception("Host property returned unexpected value (null)");
 290     
 291     EnvDTE.DTE dte = (EnvDTE.DTE)_ServiceProvider.GetService(typeof(EnvDTE.DTE));
 292     if (dte == null)
 293         throw new Exception("Unable to retrieve EnvDTE.DTE");
 294     
 295     Array activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
 296     if (activeSolutionProjects == null)
 297         throw new Exception("DTE.ActiveSolutionProjects returned null");
 298     
 299     EnvDTE.Project dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
 300     if (dteProject == null)
 301         throw new Exception("DTE.ActiveSolutionProjects[0] returned null");
 302     
 303     return dteProject;
 304 
 305 }
 306 
 307 private string GetProjectPath()
 308 {
 309     EnvDTE.Project project = GetCurrentProject();
 310     System.IO.FileInfo info = new System.IO.FileInfo(project.FullName);
 311     return info.Directory.FullName;
 312 }
 313 
 314 private string GetConfigPath()
 315 {
 316     EnvDTE.Project project = GetCurrentProject();
 317     foreach (EnvDTE.ProjectItem item in project.ProjectItems)
 318     {
 319         // if it is the app.config file, then open it up
 320         if (item.Name.Equals("App.config",StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("Web.config",StringComparison.InvariantCultureIgnoreCase))
 321             return GetProjectPath() + "\\" + item.Name;
 322     }
 323     return String.Empty;
 324 }
 325 
 326 public string GetDataDirectory()
 327 {
 328     EnvDTE.Project project=GetCurrentProject();
 329     return System.IO.Path.GetDirectoryName(project.FileName)+"\\App_Data\\";
 330 }
 331 
 332 static string zap_password(string connectionString)
 333 {
 334     var rx = new Regex("password=.*;", RegexOptions.Singleline | RegexOptions.Multiline | RegexOptions.IgnoreCase);
 335     return rx.Replace(connectionString, "password=**zapped**;");
 336 }
 337 
 338 
 339 
 340 Tables LoadTables()
 341 {
 342     InitConnectionString();
 343 
 344     WriteLine("// This file was automatically generated by the PetaPoco T4 Template");
 345     WriteLine("// Do not make changes directly to this file - edit the template instead");
 346     WriteLine("// ");
 347     WriteLine("// The following connection settings were used to generate this file");
 348     WriteLine("// ");
 349     WriteLine("//     Connection String Name: `{0}`", ConnectionStringName);
 350     WriteLine("//     Provider:               `{0}`", ProviderName);
 351     WriteLine("//     Connection String:      `{0}`", zap_password(ConnectionString));
 352     WriteLine("//     Schema:                 `{0}`", SchemaName);
 353     WriteLine("//     Include Views:          `{0}`", IncludeViews);
 354     WriteLine("");
 355 
 356     DbProviderFactory _factory;
 357     try
 358     {
 359         _factory = DbProviderFactories.GetFactory(ProviderName);
 360     }
 361     catch (Exception x)
 362     {
 363         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 364         Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
 365         WriteLine("");
 366         WriteLine("// -----------------------------------------------------------------------------------------");
 367         WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
 368         WriteLine("// -----------------------------------------------------------------------------------------");
 369         WriteLine("");
 370         return new Tables();
 371     }
 372 
 373     try
 374     {
 375         Tables result;
 376         using(var conn=_factory.CreateConnection())
 377         {
 378             conn.ConnectionString=ConnectionString;         
 379             conn.Open();
 380         
 381             SchemaReader reader=null;
 382         
 383             if (_factory.GetType().Name == "MySqlClientFactory")
 384             {
 385                 // MySql
 386                 reader=new MySqlSchemaReader();
 387             }
 388             else if (_factory.GetType().Name == "SqlCeProviderFactory")
 389             {
 390                 // SQL CE
 391                 reader=new SqlServerCeSchemaReader();
 392             }
 393             else if (_factory.GetType().Name == "NpgsqlFactory")
 394             {
 395                 // PostgreSQL
 396                 reader=new PostGreSqlSchemaReader();
 397             }
 398             else if (_factory.GetType().Name == "OracleClientFactory")
 399             {
 400                 // Oracle
 401                 reader=new OracleSchemaReader();
 402             }
 403             else
 404             {
 405                 // Assume SQL Server
 406                 reader=new SqlServerSchemaReader();
 407             }
 408 
 409             reader.outer=this;
 410             result=reader.ReadSchema(conn, _factory);
 411 
 412             // Remove unrequired tables/views
 413             for (int i=result.Count-1; i>=0; i--)
 414             {
 415                 if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
 416                 {
 417                     result.RemoveAt(i);
 418                     continue;
 419                 }
 420                 if (!IncludeViews && result[i].IsView)
 421                 {
 422                     result.RemoveAt(i);
 423                     continue;
 424                 }
 425                 if(StartsWithAny(result[i].ClassName, ExcludePrefix)) {
 426                     result.RemoveAt(i);
 427                     continue;
 428                 }
 429             }
 430 
 431             conn.Close();
 432 
 433 
 434             var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
 435             foreach (var t in result)
 436             {
 437                 t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
 438                 foreach (var c in t.Columns)
 439                 {
 440                     c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
 441 
 442                     // Make sure property name doesn't clash with class name
 443                     if (c.PropertyName == t.ClassName)
 444                         c.PropertyName = "_" + c.PropertyName;
 445                 }
 446             }
 447 
 448             return result;
 449         }
 450     }
 451     catch (Exception x)
 452     {
 453         var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
 454         Warning(string.Format("Failed to read database schema - {0}", error));
 455         WriteLine("");
 456         WriteLine("// -----------------------------------------------------------------------------------------");
 457         WriteLine("// Failed to read database schema - {0}", error);
 458         WriteLine("// -----------------------------------------------------------------------------------------");
 459         WriteLine("");
 460         return new Tables();
 461     }
 462 
 463         
 464 }
 465 
 466 bool StartsWithAny(string s, IEnumerable<string> items)
 467 {
 468     if (s == null)
 469         return false;
 470 
 471     return items.Any(i => s.StartsWith(i));
 472 }
 473 
 474 abstract class SchemaReader
 475 {
 476     public abstract Tables ReadSchema(DbConnection connection, DbProviderFactory factory);
 477     public GeneratedTextTransformation outer;
 478     public void WriteLine(string o)
 479     {
 480         outer.WriteLine(o);
 481     }
 482 
 483 }
 484 
 485 class SqlServerSchemaReader : SchemaReader
 486 {
 487     // SchemaReader.ReadSchema
 488     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
 489     {
 490         var result=new Tables();
 491         
 492         _connection=connection;
 493         _factory=factory;
 494 
 495         
 496 
 497         //pull the tables in a reader
 498         using(var cmd=_factory.CreateCommand())
 499         {
 500             cmd.Connection=connection;
 501             cmd.CommandText=TABLE_SQL;
 502             using (var rdr=cmd.ExecuteReader())
 503             {
 504                 while(rdr.Read())
 505                 {
 506                     Table tbl=new Table();
 507                     tbl.Name=rdr["TABLE_NAME"].ToString();
 508                     tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
 509                     tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
 510                     tbl.CleanName=CleanUp(tbl.Name);
 511                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
 512                     result.Add(tbl);
 513                 }
 514             }
 515         }
 516         foreach (var tbl in result)
 517         {
 518             using(var cmdDesc=_factory.CreateCommand())
 519             {
 520                 cmdDesc.Connection=connection;
 521                 cmdDesc.CommandText=TABLE_DESC_SQL;
 522 
 523                 DbParameter p = null;
 524 
 525                 p = cmdDesc.CreateParameter();
 526                 p.ParameterName = "@schema";
 527                 p.Value=tbl.Schema;
 528                 cmdDesc.Parameters.Add(p);
 529 
 530                 p = cmdDesc.CreateParameter();
 531                 p.ParameterName = "@table";
 532                 p.Value=tbl.Name;
 533                 cmdDesc.Parameters.Add(p);
 534 
 535                 using (var rdrDesc=cmdDesc.ExecuteReader())
 536                 {
 537                     if(rdrDesc.Read())
 538                         tbl.Description=rdrDesc["value"].ToString();
 539                 }
 540             }
 541 
 542             tbl.Columns=LoadColumns(tbl);
 543                     
 544             // Mark the primary key
 545             string PrimaryKey=GetPK(tbl.Name);
 546             var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
 547             if(pkColumn!=null)
 548             {
 549                 pkColumn.IsPK=true;
 550             }
 551         }
 552         
 553 
 554         return result;
 555     }
 556     
 557     DbConnection _connection;
 558     DbProviderFactory _factory;
 559     
 560 
 561     List<Column> LoadColumns(Table tbl)
 562     {
 563     
 564         using (var cmd=_factory.CreateCommand())
 565         {
 566             cmd.Connection=_connection;
 567             cmd.CommandText=COLUMN_SQL;
 568 
 569             var p = cmd.CreateParameter();
 570             p.ParameterName = "@tableName";
 571             p.Value=tbl.Name;
 572             cmd.Parameters.Add(p);
 573 
 574             p = cmd.CreateParameter();
 575             p.ParameterName = "@schemaName";
 576             p.Value=tbl.Schema;
 577             cmd.Parameters.Add(p);
 578 
 579             var result=new List<Column>();
 580             using (IDataReader rdr=cmd.ExecuteReader())
 581             {
 582                 while(rdr.Read())
 583                 {
 584                     Column col=new Column();
 585                     col.Name=rdr["ColumnName"].ToString();
 586                     col.PropertyName=CleanUp(col.Name);
 587                     col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
 588                     col.IsNullable=rdr["IsNullable"].ToString()=="YES";
 589                     col.IsAutoIncrement=((int)rdr["IsIdentity"])==1 || 
 590                             (!DBNull.Value.Equals(rdr["DefaultSetting"]) && ((string)rdr["DefaultSetting"] == "(newsequentialid())" ||
 591                             (string)rdr["DefaultSetting"] == "(newid())"));
 592                     result.Add(col);
 593                 }
 594             }
 595             foreach (var col in result)
 596             {
 597                 using(var cmdDesc=_factory.CreateCommand())
 598                 {
 599                     cmdDesc.Connection=_connection;
 600                     cmdDesc.CommandText=COLUMN_DESC_SQL;
 601 
 602                     DbParameter pDesc = null;
 603 
 604                     pDesc = cmdDesc.CreateParameter();
 605                     pDesc.ParameterName = "@schema";
 606                     pDesc.Value=tbl.Schema;
 607                     cmdDesc.Parameters.Add(pDesc);
 608 
 609                     pDesc = cmdDesc.CreateParameter();
 610                     pDesc.ParameterName = "@table";
 611                     pDesc.Value=tbl.Name;
 612                     cmdDesc.Parameters.Add(pDesc);
 613 
 614                     pDesc = cmdDesc.CreateParameter();
 615                     pDesc.ParameterName = "@column";
 616                     pDesc.Value=col.Name;
 617                     cmdDesc.Parameters.Add(pDesc);
 618                     using (var rdrDesc=cmdDesc.ExecuteReader())
 619                     {
 620                         if(rdrDesc.Read())
 621                             col.Description=rdrDesc["value"].ToString();
 622                     }
 623                 }
 624             }
 625             return result;
 626         }
 627     }
 628 
 629     string GetPK(string table){
 630         
 631         string sql=@"SELECT c.name AS ColumnName
 632                 FROM sys.indexes AS i 
 633                 INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id 
 634                 INNER JOIN sys.objects AS o ON i.object_id = o.object_id 
 635                 LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
 636                 WHERE (i.is_primary_key = 1) AND (o.name = @tableName)";
 637 
 638         using (var cmd=_factory.CreateCommand())
 639         {
 640             cmd.Connection=_connection;
 641             cmd.CommandText=sql;
 642 
 643             var p = cmd.CreateParameter();
 644             p.ParameterName = "@tableName";
 645             p.Value=table;
 646             cmd.Parameters.Add(p);
 647 
 648             var result=cmd.ExecuteScalar();
 649 
 650             if(result!=null)
 651                 return result.ToString();    
 652         }             
 653         
 654         return "";
 655     }
 656     
 657     string GetPropertyType(string sqlType)
 658     {
 659         string sysType="string";
 660         switch (sqlType) 
 661         {
 662             case "bigint":
 663                 sysType = "long";
 664                 break;
 665             case "smallint":
 666                 sysType= "short";
 667                 break;
 668             case "int":
 669                 sysType= "int";
 670                 break;
 671             case "uniqueidentifier":
 672                 sysType=  "Guid";
 673                  break;
 674             case "smalldatetime":
 675             case "datetime":
 676             case "datetime2":
 677             case "date":
 678             case "time":
 679                 sysType=  "DateTime";
 680                   break;
 681             case "datetimeoffset":
 682                 sysType = "DateTimeOffset";
 683                 break;
 684               case "float":
 685                 sysType="double";
 686                 break;
 687             case "real":
 688                 sysType="float";
 689                 break;
 690             case "numeric":
 691             case "smallmoney":
 692             case "decimal":
 693             case "money":
 694                 sysType=  "decimal";
 695                  break;
 696             case "tinyint":
 697                 sysType = "byte";
 698                 break;
 699             case "bit":
 700                 sysType=  "bool";
 701                    break;
 702             case "image":
 703             case "binary":
 704             case "varbinary":
 705             case "timestamp":
 706                 sysType=  "byte[]";
 707                  break;
 708             case "geography":
 709                 sysType = "Microsoft.SqlServer.Types.SqlGeography";
 710                 break;
 711             case "geometry":
 712                 sysType = "Microsoft.SqlServer.Types.SqlGeometry";
 713                 break;
 714         }
 715         return sysType;
 716     }
 717 
 718 
 719 
 720     const string TABLE_SQL=@"SELECT *
 721         FROM  INFORMATION_SCHEMA.TABLES
 722         WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
 723         ORDER BY TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME";
 724 
 725     const string TABLE_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, null, null)";
 726 
 727     const string COLUMN_SQL=@"SELECT 
 728             TABLE_CATALOG AS [Database],
 729             TABLE_SCHEMA AS Owner, 
 730             TABLE_NAME AS TableName, 
 731             COLUMN_NAME AS ColumnName, 
 732             ORDINAL_POSITION AS OrdinalPosition, 
 733             COLUMN_DEFAULT AS DefaultSetting, 
 734             IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
 735             CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
 736             DATETIME_PRECISION AS DatePrecision,
 737             COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
 738             COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
 739         FROM  INFORMATION_SCHEMA.COLUMNS
 740         WHERE [email protected] AND [email protected]
 741         ORDER BY OrdinalPosition ASC";
 742 
 743     const string COLUMN_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, 'column', @column)";
 744 }
 745 
 746 class SqlServerCeSchemaReader : SchemaReader
 747 {
 748     // SchemaReader.ReadSchema
 749     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
 750     {
 751         var result=new Tables();
 752         
 753         _connection=connection;
 754         _factory=factory;
 755 
 756         var cmd=_factory.CreateCommand();
 757         cmd.Connection=connection;
 758         cmd.CommandText=TABLE_SQL;
 759 
 760         //pull the tables in a reader
 761         using(cmd)
 762         {
 763             using (var rdr=cmd.ExecuteReader())
 764             {
 765                 while(rdr.Read())
 766                 {
 767                     Table tbl=new Table();
 768                     tbl.Name=rdr["TABLE_NAME"].ToString();
 769                     tbl.CleanName=CleanUp(tbl.Name);
 770                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
 771                     tbl.Schema=null;
 772                     tbl.IsView=false;
 773                     result.Add(tbl);
 774                 }
 775             }
 776         }
 777 
 778         foreach (var tbl in result)
 779         {
 780             tbl.Columns=LoadColumns(tbl);
 781                     
 782             // Mark the primary key
 783             string PrimaryKey=GetPK(tbl.Name);
 784             var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
 785             if(pkColumn!=null)
 786                 pkColumn.IsPK=true;
 787         }
 788         
 789 
 790         return result;
 791     }
 792     
 793     DbConnection _connection;
 794     DbProviderFactory _factory;
 795     
 796 
 797     List<Column> LoadColumns(Table tbl)
 798     {
 799     
 800         using (var cmd=_factory.CreateCommand())
 801         {
 802             cmd.Connection=_connection;
 803             cmd.CommandText=COLUMN_SQL;
 804 
 805             var p = cmd.CreateParameter();
 806             p.ParameterName = "@tableName";
 807             p.Value=tbl.Name;
 808             cmd.Parameters.Add(p);
 809 
 810             var result=new List<Column>();
 811             using (IDataReader rdr=cmd.ExecuteReader())
 812             {
 813                 while(rdr.Read())
 814                 {
 815                     Column col=new Column();
 816                     col.Name=rdr["ColumnName"].ToString();
 817                     col.PropertyName=CleanUp(col.Name);
 818                     col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
 819                     col.IsNullable=rdr["IsNullable"].ToString()=="YES";
 820                     col.IsAutoIncrement=rdr["AUTOINC_INCREMENT"]!=DBNull.Value;
 821                     result.Add(col);
 822                 }
 823             }
 824 
 825             return result;
 826         }
 827     }
 828 
 829     string GetPK(string table){
 830         
 831         string sql=@"SELECT KCU.COLUMN_NAME 
 832             FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
 833             JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 834             ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
 835             WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
 836             AND [email protected]";
 837 
 838         using (var cmd=_factory.CreateCommand())
 839         {
 840             cmd.Connection=_connection;
 841             cmd.CommandText=sql;
 842 
 843             var p = cmd.CreateParameter();
 844             p.ParameterName = "@tableName";
 845             p.Value=table;
 846             cmd.Parameters.Add(p);
 847 
 848             var result=cmd.ExecuteScalar();
 849 
 850             if(result!=null)
 851                 return result.ToString();    
 852         }             
 853         
 854         return "";
 855     }
 856     
 857     string GetPropertyType(string sqlType)
 858     {
 859         string sysType="string";
 860         switch (sqlType) 
 861         {
 862             case "bigint":
 863                 sysType = "long";
 864                 break;
 865             case "smallint":
 866                 sysType= "short";
 867                 break;
 868             case "int":
 869                 sysType= "int";
 870                 break;
 871             case "uniqueidentifier":
 872                 sysType=  "Guid";
 873                  break;
 874             case "smalldatetime":
 875             case "datetime":
 876             case "date":
 877             case "time":
 878                 sysType=  "DateTime";
 879                   break;
 880             case "float":
 881                 sysType="double";
 882                 break;
 883             case "real":
 884                 sysType="float";
 885                 break;
 886             case "numeric":
 887             case "smallmoney":
 888             case "decimal":
 889             case "money":
 890                 sysType=  "decimal";
 891                  break;
 892             case "tinyint":
 893                 sysType = "byte";
 894                 break;
 895             case "bit":
 896                 sysType=  "bool";
 897                    break;
 898             case "image":
 899             case "binary":
 900             case "varbinary":
 901             case "timestamp":
 902                 sysType=  "byte[]";
 903                  break;
 904         }
 905         return sysType;
 906     }
 907 
 908 
 909 
 910     const string TABLE_SQL=@"SELECT *
 911         FROM  INFORMATION_SCHEMA.TABLES
 912         WHERE TABLE_TYPE='TABLE'";
 913 
 914     const string COLUMN_SQL=@"SELECT 
 915             TABLE_CATALOG AS [Database],
 916             TABLE_SCHEMA AS Owner, 
 917             TABLE_NAME AS TableName, 
 918             COLUMN_NAME AS ColumnName, 
 919             ORDINAL_POSITION AS OrdinalPosition, 
 920             COLUMN_DEFAULT AS DefaultSetting, 
 921             IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
 922             AUTOINC_INCREMENT,
 923             CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
 924             DATETIME_PRECISION AS DatePrecision
 925         FROM  INFORMATION_SCHEMA.COLUMNS
 926         WHERE [email protected]
 927         ORDER BY OrdinalPosition ASC";
 928       
 929 }
 930 
 931 
 932 class PostGreSqlSchemaReader : SchemaReader
 933 {
 934     // SchemaReader.ReadSchema
 935     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
 936     {
 937         var result=new Tables();
 938         
 939         _connection=connection;
 940         _factory=factory;
 941 
 942         var cmd=_factory.CreateCommand();
 943         cmd.Connection=connection;
 944         cmd.CommandText=TABLE_SQL;
 945 
 946         //pull the tables in a reader
 947         using(cmd)
 948         {
 949             using (var rdr=cmd.ExecuteReader())
 950             {
 951                 while(rdr.Read())
 952                 {
 953                     Table tbl=new Table();
 954                     tbl.Name=rdr["table_name"].ToString();
 955                     tbl.Schema=rdr["table_schema"].ToString();
 956                     tbl.IsView=string.Compare(rdr["table_type"].ToString(), "View", true)==0;
 957                     tbl.CleanName=CleanUp(tbl.Name);
 958                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
 959                     result.Add(tbl);
 960                 }
 961             }
 962         }
 963 
 964         foreach (var tbl in result)
 965         {
 966             tbl.Columns=LoadColumns(tbl);
 967                     
 968             // Mark the primary key
 969             string PrimaryKey=GetPK(tbl.Name);
 970             var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
 971             if(pkColumn!=null)
 972                 pkColumn.IsPK=true;
 973         }
 974         
 975 
 976         return result;
 977     }
 978     
 979     DbConnection _connection;
 980     DbProviderFactory _factory;
 981     
 982 
 983     List<Column> LoadColumns(Table tbl)
 984     {
 985     
 986         using (var cmd=_factory.CreateCommand())
 987         {
 988             cmd.Connection=_connection;
 989             cmd.CommandText=COLUMN_SQL;
 990 
 991             var p = cmd.CreateParameter();
 992             p.ParameterName = "@tableName";
 993             p.Value=tbl.Name;
 994             cmd.Parameters.Add(p);
 995 
 996             var result=new List<Column>();
 997             using (IDataReader rdr=cmd.ExecuteReader())
 998             {
 999                 while(rdr.Read())
1000                 {
1001                     Column col=new Column();
1002                     col.Name=rdr["column_name"].ToString();
1003                     col.PropertyName=CleanUp(col.Name);
1004                     col.PropertyType=GetPropertyType(rdr["udt_name"].ToString());
1005                     col.IsNullable=rdr["is_nullable"].ToString()=="YES";
1006                     col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
1007                     result.Add(col);
1008                 }
1009             }
1010 
1011             return result;
1012         }
1013     }
1014 
1015     string GetPK(string table){
1016         
1017         string sql=@"SELECT kcu.column_name 
1018             FROM information_schema.key_column_usage kcu
1019             JOIN information_schema.table_constraints tc
1020             ON kcu.constraint_name=tc.constraint_name
1021             WHERE lower(tc.constraint_type)='primary key'
1022             AND [email protected]";
1023 
1024         using (var cmd=_factory.CreateCommand())
1025         {
1026             cmd.Connection=_connection;
1027             cmd.CommandText=sql;
1028 
1029             var p = cmd.CreateParameter();
1030             p.ParameterName = "@tableName";
1031             p.Value=table;
1032             cmd.Parameters.Add(p);
1033 
1034             var result=cmd.ExecuteScalar();
1035 
1036             if(result!=null)
1037                 return result.ToString();    
1038         }             
1039         
1040         return "";
1041     }
1042     
1043     string GetPropertyType(string sqlType)
1044     {
1045         switch (sqlType)
1046         {
1047             case "int8":
1048             case "serial8":    
1049                 return "long";
1050 
1051             case "bool":    
1052                 return "bool";
1053 
1054             case "bytea    ":    
1055                 return "byte[]";
1056 
1057             case "float8":    
1058                 return "double";
1059 
1060             case "int4":    
1061             case "serial4":    
1062                 return "int";
1063 
1064             case "money    ":    
1065                 return "decimal";
1066 
1067             case "numeric":    
1068                 return "decimal";
1069 
1070             case "float4":    
1071                 return "float";
1072 
1073             case "int2":    
1074                 return "short";
1075 
1076             case "time":
1077             case "timetz":
1078             case "timestamp":
1079             case "timestamptz":    
1080             case "date":    
1081                 return "DateTime";
1082 
1083             case "uuid":
1084                 return "Guid";
1085 
1086             default:
1087                 return "string";
1088         }
1089     }
1090 
1091 
1092 
1093     const string TABLE_SQL=@"
1094             SELECT table_name, table_schema, table_type
1095             FROM information_schema.tables 
1096             WHERE (table_type='BASE TABLE' OR table_type='VIEW')
1097                 AND table_schema NOT IN ('pg_catalog', 'information_schema');
1098             ";
1099 
1100     const string COLUMN_SQL=@"
1101             SELECT column_name, is_nullable, udt_name, column_default
1102             FROM information_schema.columns 
1103             WHERE [email protected];
1104             ";
1105     
1106 }
1107 
1108 class MySqlSchemaReader : SchemaReader
1109 {
1110     // SchemaReader.ReadSchema
1111     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1112     {
1113         var result=new Tables();
1114     
1115 
1116         var cmd=factory.CreateCommand();
1117         cmd.Connection=connection;
1118         cmd.CommandText=TABLE_SQL;
1119 
1120         //pull the tables in a reader
1121         using(cmd)
1122         {
1123             using (var rdr=cmd.ExecuteReader())
1124             {
1125                 while(rdr.Read())
1126                 {
1127                     Table tbl=new Table();
1128                     tbl.Name=rdr["TABLE_NAME"].ToString();
1129                     tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
1130                     tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1131                     tbl.CleanName=CleanUp(tbl.Name);
1132                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1133                     result.Add(tbl);
1134                 }
1135             }
1136         }
1137 
1138 
1139         //this will return everything for the DB
1140         var schema  = connection.GetSchema("COLUMNS");
1141 
1142         //loop again - but this time pull by table name
1143         foreach (var item in result) 
1144         {
1145             item.Columns=new List<Column>();
1146 
1147             //pull the columns from the schema
1148             var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
1149             foreach (var row in columns) 
1150             {
1151                 Column col=new Column();
1152                 col.Name=row["COLUMN_NAME"].ToString();
1153                 col.PropertyName=CleanUp(col.Name);
1154                 col.PropertyType=GetPropertyType(row);
1155                 col.IsNullable=row["IS_NULLABLE"].ToString()=="YES";
1156                 col.IsPK=row["COLUMN_KEY"].ToString()=="PRI";
1157                 col.IsAutoIncrement=row["extra"].ToString().ToLower().IndexOf("auto_increment")>=0;
1158 
1159                 item.Columns.Add(col);
1160             }
1161         }
1162         
1163         return result;
1164     
1165     }
1166 
1167     static string GetPropertyType(DataRow row)
1168     {
1169         bool bUnsigned = row["COLUMN_TYPE"].ToString().IndexOf("unsigned")>=0;
1170         string propType="string";
1171         switch (row["DATA_TYPE"].ToString()) 
1172         {
1173             case "bigint":
1174                 propType= bUnsigned ? "ulong" : "long";
1175                 break;
1176             case "int":
1177                 propType= bUnsigned ? "uint" : "int";
1178                 break;
1179             case "smallint":
1180                 propType= bUnsigned ? "ushort" : "short";
1181                 break;
1182             case "guid":
1183                 propType=  "Guid";
1184                  break;
1185             case "smalldatetime":
1186             case "date":
1187             case "datetime":
1188             case "timestamp":
1189                 propType=  "DateTime";
1190                   break;
1191             case "float":
1192                 propType="float";
1193                 break;
1194             case "double":
1195                 propType="double";
1196                 break;
1197             case "numeric":
1198             case "smallmoney":
1199             case "decimal":
1200             case "money":
1201                 propType=  "decimal";
1202                  break;
1203             case "bit":
1204             case "bool":
1205             case "boolean":
1206                 propType=  "bool";
1207                 break;
1208             case "tinyint":
1209                 propType =  bUnsigned ? "byte" : "sbyte";
1210                 break;
1211             case "image":
1212             case "binary":
1213             case "blob":
1214             case "mediumblob":
1215             case "longblob":
1216             case "varbinary":
1217                 propType=  "byte[]";
1218                  break;
1219                  
1220         }
1221         return propType;
1222     }
1223 
1224     const string TABLE_SQL=@"
1225             SELECT * 
1226             FROM information_schema.tables 
1227             WHERE (table_type='BASE TABLE' OR table_type='VIEW') AND TABLE_SCHEMA=DATABASE()
1228             ";
1229 
1230 }
1231 
1232 class OracleSchemaReader : SchemaReader
1233 {
1234     // SchemaReader.ReadSchema
1235     public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1236     {
1237         var result=new Tables();
1238         
1239         _connection=connection;
1240         _factory=factory;
1241 
1242         var cmd=_factory.CreateCommand();
1243         cmd.Connection=connection;
1244         cmd.CommandText=TABLE_SQL;
1245         cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1246         cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1247 
1248         //pull the tables in a reader
1249         using(cmd)
1250         {
1251 
1252             using (var rdr=cmd.ExecuteReader())
1253             {
1254                 while(rdr.Read())
1255                 {
1256                     Table tbl=new Table();
1257                     tbl.Name=rdr["TABLE_NAME"].ToString();
1258                     tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
1259                     tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1260                     tbl.CleanName=CleanUp(tbl.Name);
1261                     tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1262                     result.Add(tbl);
1263                 }
1264             }
1265         }
1266 
1267         foreach (var tbl in result)
1268         {
1269             tbl.Columns=LoadColumns(tbl);
1270                     
1271             // Mark the primary key
1272             string PrimaryKey=GetPK(tbl.Name);
1273             var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
1274             if(pkColumn!=null)
1275                 pkColumn.IsPK=true;
1276         }
1277         
1278 
1279         return result;
1280     }
1281     
1282     DbConnection _connection;
1283     DbProviderFactory _factory;
1284     
1285 
1286     List<Column> LoadColumns(Table tbl)
1287     {
1288     
1289         using (var cmd=_factory.CreateCommand())
1290         {
1291             cmd.Connection=_connection;
1292             cmd.CommandText=COLUMN_SQL;
1293             cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1294             cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1295 
1296             var p = cmd.CreateParameter();
1297             p.ParameterName = ":tableName";
1298             p.Value=tbl.Name;
1299             cmd.Parameters.Add(p);
1300 
1301             var result=new List<Column>();
1302             using (IDataReader rdr=cmd.ExecuteReader())
1303             {
1304                 while(rdr.Read())
1305                 {
1306                     Column col=new Column();
1307                     col.Name=rdr["ColumnName"].ToString();
1308                     col.PropertyName=CleanUp(col.Name);
1309                     col.PropertyType=GetPropertyType(rdr["DataType"].ToString(), (rdr["DataScale"] == DBNull.Value ? null : rdr["DataScale"].ToString()));
1310                     col.IsNullable = "YES".Equals(rdr["isnullable"].ToString()) || "Y".Equals(rdr["isnullable"].ToString());
1311                     col.IsAutoIncrement=true;
1312                     result.Add(col);
1313                 }
1314             }
1315 
1316             return result;
1317         }
1318     }
1319 
1320     string GetPK(string table){
1321         
1322         string sql=@"select column_name from USER_CONSTRAINTS uc
1323   inner join USER_CONS_COLUMNS ucc on uc.constraint_name = ucc.constraint_name
1324 where uc.constraint_type = 'P'
1325 and uc.table_name = upper(:tableName)
1326 and ucc.position = 1";
1327 
1328         using (var cmd=_factory.CreateCommand())
1329         {
1330             cmd.Connection=_connection;
1331             cmd.CommandText=sql;
1332             cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1333             cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1334 
1335             var p = cmd.CreateParameter();
1336             p.ParameterName = ":tableName";
1337             p.Value=table;
1338             cmd.Parameters.Add(p);
1339 
1340             var result=cmd.ExecuteScalar();
1341 
1342             if(result!=null)
1343                 return result.ToString();    
1344         }             
1345         
1346         return "";
1347     }
1348     
1349     string GetPropertyType(string sqlType, string dataScale)
1350     {
1351         string sysType="string";
1352         sqlType = sqlType.ToLower();
1353         switch (sqlType) 
1354         {
1355             case "bigint":
1356                 sysType = "long";
1357                 break;
1358             case "smallint":
1359                 sysType= "short";
1360                 break;
1361             case "int":
1362                 sysType= "int";
1363                 break;
1364             case "uniqueidentifier":
1365                 sysType=  "Guid";
1366                  break;
1367             case "smalldatetime":
1368             case "datetime":
1369             case "date":
1370                 sysType=  "DateTime";
1371                   break;
1372             case "float":
1373                 sysType="double";
1374                 break;
1375             case "real":
1376             case "numeric":
1377             case "smallmoney":
1378             case "decimal":
1379             case "money":
1380             case "number":
1381                 sysType=  "decimal";
1382                  break;
1383             case "tinyint":
1384                 sysType = "byte";
1385                 break;
1386             case "bit":
1387                 sysType=  "bool";
1388                    break;
1389             case "image":
1390             case "binary":
1391             case "varbinary":
1392             case "timestamp":
1393                 sysType=  "byte[]";
1394                  break;
1395         }
1396         
1397         if (sqlType == "number" && dataScale == "0")
1398             return "long";
1399         
1400         return sysType;
1401     }
1402 
1403 
1404 
1405     const string TABLE_SQL=@"select TABLE_NAME, 'Table' TABLE_TYPE, USER TABLE_SCHEMA
1406 from USER_TABLES
1407 union all
1408 select VIEW_NAME, 'View', USER
1409 from USER_VIEWS";
1410 
1411 
1412     const string COLUMN_SQL=@"select table_name TableName, 
1413  column_name ColumnName, 
1414  data_type DataType, 
1415  data_scale DataScale,
1416  nullable IsNullable
1417  from USER_TAB_COLS utc 
1418  where table_name = :tableName
1419  and virtual_column = 'NO'
1420  order by column_id";
1421       
1422 }
1423 
1424 
1425 
1426 
1427 /// <summary>
1428 /// Summary for the Inflector class
1429 /// </summary>
1430 public static class Inflector {
1431     private static readonly List<InflectorRule> _plurals = new List<InflectorRule>();
1432     private static readonly List<InflectorRule> _singulars = new List<InflectorRule>();
1433     private static readonly List<string> _uncountables = new List<string>();
1434 
1435     /// <summary>
1436     /// Initializes the <see cref="Inflector"/> class.
1437     /// </summary>
1438     static Inflector() {
1439         AddPluralRule("$", "s");
1440         AddPluralRule("s$", "s");
1441         AddPluralRule("(ax|test)is$", "$1es");
1442         AddPluralRule("(octop|vir)us$", "$1i");
1443         AddPluralRule("(alias|status)$", "$1es");
1444         AddPluralRule("(bu)s$", "$1ses");
1445         AddPluralRule("(buffal|tomat)o$", "$1oes");
1446         AddPluralRule("([ti])um$", "$1a");
1447         AddPluralRule("sis$", "ses");
1448         AddPluralRule("(?:([^f])fe|([lr])f)$", "$1$2ves");
1449         AddPluralRule("(hive)$", "$1s");
1450         AddPluralRule("([^aeiouy]|qu)y$", "$1ies");
1451         AddPluralRule("(x|ch|ss|sh)$", "$1es");
1452         AddPluralRule("(matr|vert|ind)ix|ex$", "$1ices");
1453         AddPluralRule("([m|l])ouse$", "$1ice");
1454         AddPluralRule("^(ox)$", "$1en");
1455         AddPluralRule("(quiz)$", "$1zes");
1456 
1457         AddSingularRule("s$", String.Empty);
1458         AddSingularRule("ss$", "ss");
1459         AddSingularRule("(n)ews$", "$1ews");
1460         AddSingularRule("([ti])a$", "$1um");
1461         AddSingularRule("((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$", "$1$2sis");
1462         AddSingularRule("(^analy)ses$", "$1sis");
1463         AddSingularRule("([^f])ves$", "$1fe");
1464         AddSingularRule("(hive)s$", "$1");
1465         AddSingularRule("(tive)s$", "$1");
1466         AddSingularRule("([lr])ves$", "$1f");
1467         AddSingularRule("([^aeiouy]|qu)ies$", "$1y");
1468         AddSingularRule("(s)eries$", "$1eries");
1469         AddSingularRule("(m)ovies$", "$1ovie");
1470         AddSingularRule("(x|ch|ss|sh)es$", "$1");
1471         AddSingularRule("([m|l])ice$", "$1ouse");
1472         AddSingularRule("(bus)es$", "$1");
1473         AddSingularRule("(o)es$", "$1");
1474         AddSingularRule("(shoe)s$", "$1");
1475         AddSingularRule("(cris|ax|test)es$", "$1is");
1476         AddSingularRule("(octop|vir)i$", "$1us");
1477         AddSingularRule("(alias|status)$", "$1");
1478         AddSingularRule("(alias|status)es$", "$1");
1479         AddSingularRule("^(ox)en", "$1");
1480         AddSingularRule("(vert|ind)ices$", "$1ex");
1481         AddSingularRule("(matr)ices$", "$1ix");
1482         AddSingularRule("(quiz)zes$", "$1");
1483 
1484         AddIrregularRule("person", "people");
1485         AddIrregularRule("man", "men");
1486         AddIrregularRule("child", "children");
1487         AddIrregularRule("sex", "sexes");
1488         AddIrregularRule("tax", "taxes");
1489         AddIrregularRule("move", "moves");
1490 
1491         AddUnknownCountRule("equipment");
1492         AddUnknownCountRule("information");
1493         AddUnknownCountRule("rice");
1494         AddUnknownCountRule("money");
1495         AddUnknownCountRule("species");
1496         AddUnknownCountRule("series");
1497         AddUnknownCountRule("fish");
1498         AddUnknownCountRule("sheep");
1499     }
1500 
1501     /// <summary>
1502     /// Adds the irregular rule.
1503     /// </summary>
1504     /// <param name="singular">The singular.</param>
1505     /// <param name="plural">The plural.</param>
1506     private static void AddIrregularRule(string singular, string plural) {
1507         AddPluralRule(String.Concat("(", singular[0], ")", singular.Substring(1), "$"), String.Concat("$1", plural.Substring(1)));
1508         AddSingularRule(String.Concat("(", plural[0], ")", plural.Substring(1), "$"), String.Concat("$1", singular.Substring(1)));
1509     }
1510 
1511     /// <summary>
1512     /// Adds the unknown count rule.
1513     /// </summary>
1514     /// <param name="word">The word.</param>
1515     private static void AddUnknownCountRule(string word) {
1516         _uncountables.Add(word.ToLower());
1517     }
1518 
1519     /// <summary>
1520     /// Adds the plural rule.
1521     /// </summary>
1522     /// <param name="rule">The rule.</param>
1523     /// <param name="replacement">The replacement.</param>
1524     private static void AddPluralRule(string rule, string replacement) {
1525         _plurals.Add(new InflectorRule(rule, replacement));
1526     }
1527 
1528     /// <summary>
1529     /// Adds the singular rule.
1530     /// </summary>
1531     /// <param name="rule">The rule.</param>
1532     /// <param name="replacement">The replacement.</param>
1533     private static void AddSingularRule(string rule, string replacement) {
1534         _singulars.Add(new InflectorRule(rule, replacement));
1535     }
1536 
1537     /// <summary>
1538     /// Makes the plural.
1539     /// </summary>
1540     /// <param name="word">The word.</param>
1541     /// <returns></returns>
1542     public static string MakePlural(string word) {
1543         return ApplyRules(_plurals, word);
1544     }
1545 
1546     /// <summary>
1547     /// Makes the singular.
1548     /// </summary>
1549     /// <param name="word">The word.</param>
1550     /// <returns></returns>
1551     public static string MakeSingular(string word) {
1552         return ApplyRules(_singulars, word);
1553     }
1554 
1555     /// <summary>
1556     /// Applies the rules.
1557     /// </summary>
1558     /// <param name="rules">The rules.</param>
1559     /// <param name="word">The word.</param>
1560     /// <returns></returns>
1561     private static string ApplyRules(IList<InflectorRule> rules, string word) {
1562         string result = word;
1563         if (!_uncountables.Contains(word.ToLower())) {
1564             for (int i = rules.Count - 1; i >= 0; i--) {
1565                 string currentPass = rules[i].Apply(word);
1566                 if (currentPass != null) {
1567                     result = currentPass;
1568                     break;
1569                 }
1570             }
1571         }
1572         return result;
1573     }
1574 
1575     /// <summary>
1576     /// Converts the string to title case.
1577     /// </summary>
1578     /// <param name="word">The word.</param>
1579     /// <returns></returns>
1580     public static string ToTitleCase(string word) {
1581         return Regex.Replace(ToHumanCase(AddUnderscores(word)), @"\b([a-z])",
1582             delegate(Match match) { return match.Captures[0].Value.ToUpper(); });
1583     }
1584 
1585     /// <summary>
1586     /// Converts the string to human case.
1587     /// </summary>
1588     /// <param name="lowercaseAndUnderscoredWord">The lowercase and underscored word.</param>
1589     /// <returns></returns>
1590     public static string ToHumanCase(string lowercaseAndUnderscoredWord) {
1591         return MakeInitialCaps(Regex.Replace(lowercaseAndUnderscoredWord, @"_", " "));
1592     }
1593 
1594 
1595     /// <summary>
1596     /// Adds the underscores.
1597     /// </summary>
1598     /// <param name="pascalCasedWord">The pascal cased word.</param>
1599     /// <returns></returns>
1600     public static string AddUnderscores(string pascalCasedWord) {
1601         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();
1602     }
1603 
1604     /// <summary>
1605     /// Makes the initial caps.
1606     /// </summary>
1607     /// <param name="word">The word.</param>
1608     /// <returns></returns>
1609     public static string MakeInitialCaps(string word) {
1610         return String.Concat(word.Substring(0, 1).ToUpper(), word.Substring(1).ToLower());
1611     }
1612 
1613     /// <summary>
1614     /// Makes the initial lower case.
1615     /// </summary>
1616     /// <param name="word">The word.</param>
1617     /// <returns></returns>
1618     public static string MakeInitialLowerCase(string word) {
1619         return String.Concat(word.Substring(0, 1).ToLower(), word.Substring(1));
1620     }
1621 
1622 
1623     /// <summary>
1624     /// Determine whether the passed string is numeric, by attempting to parse it to a double
1625     /// </summary>
1626     /// <param name="str">The string to evaluated for numeric conversion</param>
1627     /// <returns>
1628     ///     <c>true</c> if the string can be converted to a number; otherwise, <c>false</c>.
1629     /// </returns>
1630     public static bool IsStringNumeric(string str) {
1631         double result;
1632         return (double.TryParse(str, NumberStyles.Float, NumberFormatInfo.CurrentInfo, out result));
1633     }
1634 
1635     /// <summary>
1636     /// Adds the ordinal suffix.
1637     /// </summary>
1638     /// <param name="number">The number.</param>
1639     /// <returns></returns>
1640     public static string AddOrdinalSuffix(string number) {
1641         if (IsStringNumeric(number)) {
1642             int n = int.Parse(number);
1643             int nMod100 = n % 100;
1644 
1645             if (nMod100 >= 11 && nMod100 <= 13)
1646                 return String.Concat(number, "th");
1647 
1648             switch (n % 10) {
1649                 case 1:
1650                     return String.Concat(number, "st");
1651                 case 2:
1652                     return String.Concat(number, "nd");
1653                 case 3:
1654                     return String.Concat(number, "rd");
1655                 default:
1656                     return String.Concat(number, "th");
1657             }
1658         }
1659         return number;
1660     }
1661 
1662     /// <summary>
1663     /// Converts the underscores to dashes.
1664     /// </summary>
1665     /// <param name="underscoredWord">The underscored word.</param>
1666     /// <returns></returns>
1667     public static string ConvertUnderscoresToDashes(string underscoredWord) {
1668         return underscoredWord.Replace('_', '-');
1669     }
1670 
1671 
1672     #region Nested type: InflectorRule
1673 
1674     /// <summary>
1675     /// Summary for the InflectorRule class
1676     /// </summary>
1677     private class InflectorRule {
1678         /// <summary>
1679         /// 
1680         /// </summary>
1681         public readonly Regex regex;
1682 
1683         /// <summary>
1684         /// 
1685         /// </summary>
1686         public readonly string replacement;
1687 
1688         /// <summary>
1689         /// Initializes a new instance of the <see cref="InflectorRule"/> class.
1690         /// </summary>
1691         /// <param name="regexPattern">The regex pattern.</param>
1692         /// <param name="replacementText">The replacement text.</param>
1693         public InflectorRule(string regexPattern, string replacementText) {
1694             regex = new Regex(regexPattern, RegexOptions.IgnoreCase);
1695             replacement = replacementText;
1696         }
1697 
1698         /// <summary>
1699         /// Applies the specified word.
1700         /// </summary>
1701         /// <param name="word">The word.</param>
1702         /// <returns></returns>
1703         public string Apply(string word) {
1704             if (!regex.IsMatch(word))
1705                 return null;
1706 
1707             string replace = regex.Replace(word, replacement);
1708             if (word == word.ToUpper())
1709                 replace = replace.ToUpper();
1710 
1711             return replace;
1712         }
1713     }
1714 
1715     #endregion
1716 }
1717 
1718 #>
PetaPoco.Core.ttinclude

至此已经从数据库获取到了注释,下面需要将注释插入到T4模板中

修改PetaPoco.Generator.ttinclude

141行原始:

...
<# if (GeneratePocos) { #>
<#
foreach(Table tbl in from t in tables where !t.Ignore select t)
{
#>
    
<# if (string.IsNullOrEmpty(tbl.Schema)) { #>
    [TableName("<#=tbl.Name#>")]
...

添加表的Description注释:

...
<# if (GeneratePocos) { #>
<#
foreach(Table tbl in from t in tables where !t.Ignore select t)
{
#>
    /// <summary>
    /// <#=tbl.Description??""#>
    /// </summary>
    
<# if (string.IsNullOrEmpty(tbl.Schema)) { #>
    [TableName("<#=tbl.Name#>")]
...

167行原始:

...
    public partial class <#=tbl.ClassName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tbl.ClassName#>> <# } #> 
    {
<#
foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{
        // Column bindings
#>
<# if (TrackModifiedColumns) { #>
...

添加列的Description注释:

...
    public partial class <#=tbl.ClassName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tbl.ClassName#>> <# } #> 
    {
<#
foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{
        // Column bindings
#>
    /// <summary>
    /// <#=col.Description??""#>
    /// </summary>

<# if (TrackModifiedColumns) { #>
...

这样就改完了,打开database.tt按ctrl+s就能更新获取到的注释了

另外GetInstance居然不是单例方法,这简直没法忍,果断改掉:

PetaPoco.Generator.ttinclude,38行修改为:

...
        public static <#=RepoName#> GetInstance()
        {
            if (_instance!=null)
                return _instance;
                
            if (Factory!=null)
                return Factory.GetInstance();
            //else
            //    return new <#=RepoName#>();
            return _instance = new <#=RepoName#>();
        }
...

 

文章评论

程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员的鄙视链
程序员的鄙视链
Java程序员必看电影
Java程序员必看电影
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
代码女神横空出世
代码女神横空出世
如何成为一名黑客
如何成为一名黑客
那些争议最大的编程观点
那些争议最大的编程观点
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员都该阅读的书
程序员都该阅读的书
老程序员的下场
老程序员的下场
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
我是如何打败拖延症的
我是如何打败拖延症的
一个程序员的时间管理
一个程序员的时间管理
我的丈夫是个程序员
我的丈夫是个程序员
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
总结2014中国互联网十大段子
总结2014中国互联网十大段子
每天工作4小时的程序员
每天工作4小时的程序员
漫画:程序员的工作
漫画:程序员的工作
程序员应该关注的一些事儿
程序员应该关注的一些事儿
程序员和编码员之间的区别
程序员和编码员之间的区别
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
10个调试和排错的小建议
10个调试和排错的小建议
为什么程序员都是夜猫子
为什么程序员都是夜猫子
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
中美印日四国程序员比较
中美印日四国程序员比较
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
鲜为人知的编程真相
鲜为人知的编程真相
程序员必看的十大电影
程序员必看的十大电影
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
 程序员的样子
程序员的样子
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有