petapoco-SQLServer模型增加註釋

来源:http://www.cnblogs.com/lxyctl/archive/2016/12/06/6137484.html
-Advertisement-
Play Games

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

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 簡介 此樣式基於bootstrap-3.3.0,樣式文件里的源碼行數都是指的這個版本.CSS源文件放到了Content文件夾下的bootstrap.css WPF樣式和CSS還是不太相同,所以有些內容實現上稍有出入,有些內容用法不太一樣,有些內容並沒有實現 但至少,一些概念,尺寸和取色,還是很好的借 ...
  • 上一篇我們將 "XAML" 大概做了個瞭解 ,這篇將繼續學習WPF數據綁定的相關內容 數據源與控制項的Binding 作為數據傳送UI的通道,通過 介面的 事件通知 數據屬性發生改變 通過Binding關聯UI控制項元素 控制項之間的Binding 我們也可以通過後臺C 代碼實現 統計文本字元長度 Bin ...
  • @echo offInstallutil.exe 程式目錄 F:\test\TestWindows.exe 服務程式目錄@sc start "服務名稱"@sc config "服務名稱" start= 啟動方式:AUTO@echo off@echo 服務安裝並啟動完成pause 示例: @echo ...
  • ...
  • 1.開發人員在火狐瀏覽器里經常使用的工具有Firebug,httprequester,restclient......火狐瀏覽器有一些強大的插件供開發人員使用!需要的可以在附加組件中擴展。 2.httprequester,也是可以在附加組件中獲得的,你只要輸入這個名詞,搜索安裝。 web開發人員一般 ...
  • Ext.NET 4.1 最新版本破解 今天在將Ext.NET 4.1版本的程式發佈到公網時居然要license(localhost和127.0.0.1不收費),而且一年$4999,突然間覺得這是什麼鬼,居然還收費!如圖: 大大的一個UNLICENSED! 網上搜索破解方法,好像都沒什麼用,唯一有啟發 ...
  • 1、重覆輸入一個數,判斷該數是否是質數,輸入q結束?質數的判斷用方法來實現bool IsPrime(int number) 1 static void Main(string[] args) 2 { 3 // 要求:重覆讓用戶輸入一個數,判斷該數是否是質數,輸入q結束? 質數的判斷用方法來實現boo ...
  • params參數練習 1 namespace Test 2 { 3 class Program 4 { 5 static void Main(string[] args) 6 { 7 //params 構造函數聲明數組,可變數組長度 8 UseParam1(4,2,3); 9 UserParam2( ...
一周排行
    -Advertisement-
    Play Games
  • 示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後,項目結構如下所示: MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread] 特性用於需要與 COM 組件交互的應用程式,尤其是依賴單線程模型(如 Windows Forms 應用程式)的組件。在 STA 模式下,線程擁有自己的消息迴圈,這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • 在WinForm中使用全局異常捕獲處理 在WinForm應用程式中,全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理,可以有效地捕獲並處理未預見的異常,從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • 前言 給大家推薦一款開源的 Winform 控制項庫,可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫,同時也提供了工具類庫、擴展 ...
  • 說明 該文章是屬於OverallAuth2.0系列文章,每周更新一篇該系列文章(從0到1完成系統開發)。 該系統文章,我會儘量說的非常詳細,做到不管新手、老手都能看懂。 說明:OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友,請關註我吧(*^▽^*) ...
  • 一、下載安裝 1.下載git 必須先下載並安裝git,再TortoiseGit下載安裝 git安裝參考教程:https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit,Git客戶端,32/6 ...
  • 前言 在項目開發過程中,理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼,還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程:Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 一:背景 1. 講故事 前段時間有位朋友找到我,說他的窗體程式在客戶這邊出現了卡死,讓我幫忙看下怎麼回事?dump也生成了,既然有dump了那就上 windbg 分析吧。 二:WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死,入口門檻很低,後續往下分析就不一定了,不管怎麼說先用 !clrsta ...
  • 前言 人工智慧時代,人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API,工具不僅易於集成,還具備高效處理能力。 本文將介紹一款如何利用這些API,為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...