petapoco-SQLServer模型增加註釋_ZenDei技術網路在線

petapoco是個基於T4模板的輕量級ORM，好用效率高，具體介紹略了獲取註釋基本原理是調用資料庫::fn_listextendedproperty函數，獲取擴展屬性MS_Description technet參考資料：sys.fn_listextendedproperty (Transact- ...

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.Ign